Wednesday, September 21, 2011

Oracle Database Appliance

I have spent the day on an oracle call, and reading all the subsequent tweets that follow.  I think the best way to describe the appliance is that it is NOT a mini-exadata, but it is a simple rac appliance.

My impression is that it is a nice product for the small to mid market, but those us working with the bigger toys I don't see the gain.

I know, I've spent more days than I care to remember schooling the SA's on how to set up an interconnect, and ensure that all the IP's are correct.  I've worked with Storage administrators on how to present the disks, and make them available to ASM, and I've worked with networking on the ranges of IP's I need for scan, interconnect, etc. etc.  I'm sure you get the picture.

I also think that people like me that work in a big organization and have a team to handle these tasks, are probably going huh ? what is this? 

Personally, I don't see the big deal in this.. I see lots of dissadvantages.

  • These Appliances cannot be clustered. What they have in them is all they will ever have in them.
  • The 2 database nodes have 96g of memory, not a lot in today server sizes..
  • There is no storage software like the exadata. No HCC, no offloading, no infiniband
  • This is local disk in the appliance, meaning no cloning, storage virtualization, etc.
  • The interconnect is 1ge, not infiniband.
  • You CANNOT hook up fiber to this server, ever.
  • It runs OEL, NOT redhat linux.. the differences are getting greater over time.
  • This is a closed system with specific patch sets that need to be maintained to a short list of acceptable patches.
I know for a small, to midsize, the ideal of creating a new rac system in 2 hours is thing of beauty, but for bigger companies, there isn't a lot there.

Especially without the Exadata candy filling (infiniband, HCC, offloading, storage indexes).

I still think virtualiation is the direction, and this is a step in the opposite direction.  There may be a few takers, but I think companies will realize that virtualization is a better direction than a single closed appliance.

We will see.. just some thoughts.

Saturday, September 17, 2011

Delphix, Solaris and ZFS

I was inspired to write a new blog posting after seeing a great presentation on Delphix.  Delphix is an extremely interesting product to help with the managing of many copies of an oracle database.  This product goes hand-in-hand with a virtualization product like VMWARE. What VMWare is servers, Delphix is to storage.

First I want to babble on a bit.. Why ? Because this is my blog.

I have used Solaris for many years and I am a big fan of Sparc/Solaris, and everything that it became over the years.  I know in the last couple of years, it has kind of fallen down, and Linux x86 has been growing leaps and bounds.  The most impressive piece of Solaris however has been ZFS.  ZFS offers snapshotting, cloning, lots of fantastic options built into the OS.  All very easy for the DBA.

I was very excited when ACFS came out (anyone remember).  It was the coolest thing going.  It looked a lot like ZFS built on top of ASM.   It had the potential to become a fantastic tool.  I was really, really, really hoping that down the road ACFS, and ZFS would combine, and replace the cooked file system with an Oracle File System that Rocked.    Well in case you didn't know ACFS has morphed into the "Cloud File System" or Cloud FS.  This is a licensed product used for the sharing of directories in a "cloud" environment.  Also, if you didn't know, ACFS won't run on the Exadata.  2 nails in the Coffin of this fantastic vision that I had in my head.

I was devastated for a while.

Why the background ? Because Delphix picked up where this vision died.  They offer the ability to do snapshots, along with deduplication, and compression to boot.

Delphix was founded by Jedidiah Yueh, who founded Avamar (dedup software that was sold to EMC),  The company also has  Karthik Rau, ad VP of Products who worked on VMware.  Lots of very bright people that were already involved in the founding technology.

Delphix makes the creation of database copies, easy, and it save space to boot.  I will post the presentation that I saw once I can link to it.

Definitely something worth checking out, and to watch for in the infrastructure Arsenal.

Wednesday, September 14, 2011

My quest for a performance warehouse

I have written on this before, and at openworld last year I've talked to some of the presenters about this.

My goal is to to create a performance data warehouse.  What does this mean ?

I want to take all the AWR data from all the databases in the corporate infrastructure, and create one giant repository. 

Well I just got one step closer.

I have a repository server set up already. It is just an 11.2.0.2 database with lots of space (about 3 tb), running on a Linux blade server.

My first test was to take all the AWR data from an 10g database we just retired running on HPUX. Since this database was moved to Linux (cross endian), transportable tablespaces was used, and all the AWR data was not brought over.

First step.. Get the data out of original source.

In order to do that you can use awrextr.sql in the $ORACLE_HOME/rdbms/admin directory.  I noticed (and found from other blogs) that this just calls sys.dbms_swrf_internal.awr_extract.

So 

STEPS on SOURCE.

  1. Create user "awrextract" identified by xxxxx
  2. grant connect ot awrextract
  3. grant execute on sys.dbms_swrf_internal to awrextract
  4. Login as awrextract
  5. execute awr_extract procedure
exec sys.dbms_swrf_internal.awr_extract(dmpfile=>'awr_data',dmpdir=>'tmp_dir',bid=>9920,eid=>9925);

Notes
  • The procedure automagically puts a .dmp on the dump file name
  • You need to know the start and end snapshot you want to export
  • The dmpdir needs to be a precreated directory and it has to be a cooked filesystem (non-asm).  I found that it also puts the logfile here, and that's why it has to be cooked.
  • You cannot use dbms_file_transfer, especially going across endian types
 6. copy the file to my repository server

STEPS on the Destination




  1.  Create schema to import the data into
  2. Import the file in
DBMS_SWRF_INTERNAL.AWR_LOAD ( SCHNAME => 'AWR', dmpfile => 'awr_data', dmpdir => 'TMP_DIR');

  3.  Once imported move the awr data to sys catalogs

DBMS_SWRF_INTERNAL.MOVE_TO_AWR ( SCHNAME => 'AWR');

FINALLY

All this worked flawlessly, and then I tried to run COE_PROFILE.

IF you didn't know COE_PROFILE.sql  is part of sqlt and allows you to transfer profiles from one database to another by creating a script to build the profile.. It all works from my repository.

I will write about this again, but I am physched to finally get this off the ground.






Monday, September 12, 2011

Thursday, August 18, 2011

Hyperthreading

I am am working in standing up a cluster of 2 database servers that look an aweful lot like an exadata 2-8 (only without the infiniband)..  It is a 2 node cluster of 8 socket, 8 core processrs with 256g of memory.

We started with a standard configuration and hyperthreading was turned on.. This means that Oracle sees the 64 cpu, as 128 cpus.  We were getting periodic reboots every Saturday morning.. The culprit was finally found as the analyze.. Why ??? because we have a 2 node rac, with 128 CPU's per, the degree of parallelization chosen was 512 !  With an analyze running with 512  parallel processors we were running out of memory.. The final diagnoses was to change parallel_max_servers to 64 to cut back on the parallel servers utilized. The second recommendation from Oracle was to turn off Hyperthreading.

We all said Huh ?  Hyperthreading gives you 2x the throughput because it doubles your CPU's right ???

Well lets' see.

I took my 2 node cluster and split it in 1/2  First half (A) is non-hyperthreaded, second half (B) is hyperthreaded.

I then took swingbench and threw a workload at it with lots of logical I/O.


Here is the benchmark from (A)   non-hyperthreaded

Notice we are getting 2062 transactions per minute.



 OK.. Lets see what happens with (B) hyperthreading.

Notice we are getting 1,685 transactions per minute. 


LESS wow lets look at our AWR compare of both nodes (same period) and see what the database says.


Notice  that the gets per execution match for both nodes, and the rows processed. The difference is in the exec time(ms) per exectuion, CPU time and executions.

IT is faster non-hyperthreaded.   The CPU's are doing more work with hypertrheading off, and pushing more work through.

OK... I've shown that for a non-saturating workload (under 64 active processes) on a 64 CPU box, hyperthreading off is faster.

Now let's saturate !!

First hyperthreading off.

Notice I can get the throughput up to 7,489 transactions per minute.

Now lets check the second node with hyperthreading on.

Notice we are getting 6552.. Darn !!


It looks like hypthreading isn't helping us eithor !! 

 
Now lets check the AWR for the 2 nodes.


Notice that once again the rows processed match up and the gets/execution match.

The number of executions is greater, the execution time is less, and the CPU time is way less for for hyperthreading off.


For a Logical I/O intensive workload, it looks like Hyperthreading is not as efficient.

I would love to hear any other experiences/opinions.

I just wanted to add on to this a thought as to why hypertrheading is such a big deal... Virtualization.  With 2x as many "cpus", you can fine tune the CPU a lot more.. You can carve up the server into smaller pieces.








Monday, August 1, 2011

Cardinality Feedback detailed example

Well, I have often written about cardinality feedback, and I have been striving to find out more detail on how it works, how to determine why it was used, and how to determine when it was used.

I have ran through a series of tests that answers a lot of these questions.  There is some information available, but  it isn't complete.  Here a couple of good starting points..


They were even able to recreate some examples. Well here is my example.

First, my dataset. I used the zipcode database you can find here.  I used a sqldr script you can find here, to load a table structure you can find here.

First I loaded the data (about 74,000 rows), created a couple of indexes and ran a query.

here are the indexes
 CREATE INDEX "SYSTEM"."INDEX1" ON "SYSTEM"."TEST_DATA"
    (  "ZIPCODE" )
    TABLESPACE "SYSTEM" ;
  
 CREATE INDEX "SYSTEM"."INDEX2" ON "SYSTEM"."TEST_DATA"
    (  "CITY" )
     TABLESPACE "SYSTEM" ;
  
 CREATE INDEX "SYSTEM"."INDEX3" ON "SYSTEM"."TEST_DATA"
    (  "COUNTY" )
     TABLESPACE "SYSTEM" ;  

select /* colorme */ * from test_data where city='ROCHESTER' and county='MONROE';



There are individual indexes on both city and county, and there is a relationship between the 2 items.  This query will return 44 rows of data.

First output is the information from v$sql_shared_cursor.  Notice the column "use_feedback_stats".  I am also supplying the information from v$sqlstats for this query.  Here is the output after the first execution.

v$SQL_SHARED_CURSOR
SQL_ID CHILD_NUMBER USE_FEEDBACK_STATS ------------- ---------------------- ------------------ 33x0cazrmgrgy 0 Y
V$SQLSTATS
SQL_ID PLAN_HASH_VALUE PARSE_CALLS BUFFER_GETS ROWS_PROCESSED ------------- ---------------------- ---------------------- ---------------------- ---------------------- 33x0cazrmgrgy 757026858 1 551 44

Notice that the column "use_feedback_stats" is set to 'Y" indicating that the query is marked for cardinality feedback to determine if the first execution is accurate enough.  I also ran a 10053 trace.  You can see from the output the query is rewritten to be executed with dynamic sampling.

SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false') NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),0), NVL(SUM(C2),0), NVL(SUM(C3),0), NVL(SUM(C4),0) 
FROM 
(SELECT /*+ IGNORE_WHERE_CLAUSE NO_PARALLEL("TEST_DATA") FULL("TEST_DATA") NO_PARALLEL_INDEX("TEST_DATA") */ 1 AS C1, 
CASE WHEN "TEST_DATA"."CITY"='ROCHESTER' AND "TEST_DATA"."COUNTY"='MONROE' THEN 1 ELSE 0 END AS C2, 
CASE WHEN "TEST_DATA"."COUNTY"='MONROE' THEN 1 ELSE 0 END AS C3, 
CASE WHEN "TEST_DATA"."CITY"='ROCHESTER' THEN 1 ELSE 0 END AS C4 
FROM "BGRENN"."TEST_DATA" SAMPLE BLOCK (4.972376 , 1) SEED (1) "TEST_DATA") SAMPLESUB



Here is the output from explain plan

 PLAN_TABLE_OUTPUT                                                    
  
 -------------------------------------------------------------------------------------------------------------------------
   Plan hash value: 757026858                                                
   -----------------------------------------------------------------------------------------                
   | Id | Operation          | Name   | Rows | Bytes | Cost (%CPU)| Time   |                
   -----------------------------------------------------------------------------------------                
   |  0 | SELECT STATEMENT      |      |   8 | 1272 |   3  (0)| 00:00:01 |                
   |* 1 | TABLE ACCESS BY INDEX ROWID| TEST_DATA |   8 | 1272 |   3  (0)| 00:00:01 |                
   |* 2 |  INDEX RANGE SCAN     | INDEX2  |  121 |    |   1  (0)| 00:00:01 |                
   -----------------------------------------------------------------------------------------                
   Predicate Information (identified by operation id):                                   
   ---------------------------------------------------                                  
     1 - filter("COUNTY"='MONROE')                                             
     2 - access("CITY"='ROCHESTER')                                            
  
 Note                                                           
   -----                                                          
     - dynamic sampling used for this statement (level=2)                                 
  
Second output is the information from v$sql_shared_cursor (again).  Notice the column "use_feedback_stats" is set to 'N'
SQL_ID        CHILD_NUMBER           USE_FEEDBACK_STATS 
------------- ---------------------- ------------------ 
33x0cazrmgrgy 0                      Y                  
33x0cazrmgrgy 1                      N                  

SQL_ID        PLAN_HASH_VALUE        PARSE_CALLS            BUFFER_GETS            ROWS_PROCESSED         
------------- ---------------------- ---------------------- ---------------------- ---------------------- 
33x0cazrmgrgy 757026858              2                      572                    88                     



I ran a 10053 trace again (as before).  You can see in this case the results from the first execution (with 44 rows), is passed into the second execution using the hint "opt_estimate". You can also see what parts of the query are utilizing cardinality feedback to change the query.


SELECT /*+ OPT_ESTIMATE (TABLE "TEST_DATA" ROWS=44.000000 ) OPT_ESTIMATE (INDEX_SCAN "TEST_DATA" "INDEX2" MIN=69.000000 ) 
OPT_ESTIMATE (INDEX_FILTER "TEST_DATA" "INDEX2" ROWS=69.000000 ) */ 
"TEST_DATA"."ZIPCODE" "ZIPCODE",
"TEST_DATA"."LAT" "LAT",
"TEST_DATA"."CITY" "CITY",
"TEST_DATA"."STATE" "STATE",
"TEST_DATA"."COUNTY" "COUNTY",
"TEST_DATA"."TYPE" "TYPE",
"TEST_DATA"."PREFERRED" "PREFERRED",
"TEST_DATA"."WORLDREGION" "WORLDREGION",
"TEST_DATA"."COUNTRY" "COUNTRY",
"TEST_DATA"."LOCATIONTEXT" "LOCATIONTEXT",
"TEST_DATA"."LOCATION" "LOCATION",
"TEST_DATA"."POPULATION" "POPULATION",
"TEST_DATA"."HOUSINGUNITS" "HOUSINGUNITS"
,"TEST_DATA"."INCOME" "INCOME",
"TEST_DATA"."LANDAREA",
"LANDAREATEST_DATA"."WATERAREA" "WATERAREA",
"TEST_DATA"."DECOMMISIONED" "DECOMMISIONED",
"TEST_DATA"."MILITARYRESTRICTIONCODES" "MILITARYRESTRICTIONCODES" 
FROM "BGRENN"."TEST_DATA" "TEST_DATA" 
WHERE "TEST_DATA"."CITY"='ROCHESTER' AND "TEST_DATA"."COUNTY"='MONROE'


I ran this query until the v$sql_shared_cursor came back clean, and showed a "n" indicating that dynamic feedback was no longer being considered.  There was information from 2 hard parses in the 10053 file.
SQL_ID        CHILD_NUMBER           USE_FEEDBACK_STATS 
------------- ---------------------- ------------------ 
33x0cazrmgrgy 0                      Y                  
33x0cazrmgrgy 1                      N                  

SQL_ID        PLAN_HASH_VALUE        PARSE_CALLS            BUFFER_GETS            ROWS_PROCESSED         
------------- ---------------------- ---------------------- ---------------------- ---------------------- 
33x0cazrmgrgy 757026858              3                      593                    132                    


This answered my first question. How do you tell what caused the cardinality feedback ? You run a 10053, and find the OPT_ESTIMATE cardinality hints that adjust the cardinility and reparse the query.


/***************************************/

For my second test, I used the same result set, and ran statistics against the data.  After running the query from the first test, I found that cardinality was not being utilzed, so I changed my query.



select /* colorme */ * from test_data where city='ROCHESTER' and county='MONROE1' and state='NY';

This returns no rows of data, but the optimizer can't figure this out.

First execution you can see that cardinality feedback was marked for the statement, and notice the plan_hash_value of 7570268858.  In this example I've also included the output from v$SQLSTATS_PLAN_HASH. This information was added after reading the post from Lisa G.

SQL_ID        CHILD_NUMBER           USE_FEEDBACK_STATS 
------------- ---------------------- ------------------ 
88jhv4mryj58p 0                      Y                  

V$SQLSTATS

SQL_ID        PLAN_HASH_VALUE        PARSE_CALLS            BUFFER_GETS            ROWS_PROCESSED         
------------- ---------------------- ---------------------- ---------------------- ---------------------- 
88jhv4mryj58p 757026858              1                      146                    0                      

v$SQLSTATS_PLAN_HASH

SQL_ID        PLAN_HASH_VALUE        PARSE_CALLS            BUFFER_GETS            ROWS_PROCESSED         
------------- ---------------------- ---------------------- ---------------------- ---------------------- 
88jhv4mryj58p 757026858              1                      146                    0                      



Here is the output from the 10053 trace.


SELECT 
    "TEST_DATA"."ZIPCODE" "ZIPCODE",
    "TEST_DATA"."LAT" "LAT",
    "TEST_DATA"."CITY" "CITY",
    "TEST_DATA"."STATE" "STATE",
    "TEST_DATA"."COUNTY" "COUNTY",
    "TEST_DATA"."TYPE" "TYPE",
    "TEST_DATA"."PREFERRED" "PREFERRED",
    "TEST_DATA"."WORLDREGION" "WORLDREGION",
    "TEST_DATA"."COUNTRY" "COUNTRY",
    "TEST_DATA"."LOCATIONTEXT" "LOCATIONTEXT",
    "TEST_DATA"."LOCATION" "LOCATION",
    "TEST_DATA"."POPULATION" "POPULATION",
    "TEST_DATA"."HOUSINGUNITS" "HOUSINGUNITS",
    "TEST_DATA"."INCOME" "INCOME",
    "TEST_DATA"."LANDAREA",
    "LANDAREATEST_DATA"."WATERAREA" "WATERAREA",
    "TEST_DATA"."DECOMMISIONED" "DECOMMISIONED",
    "TEST_DATA"."MILITARYRESTRICTIONCODES" "MILITARYRESTRICTIONCODES" 
FROM "BGRENN"."TEST_DATA" "TEST_DATA" 
WHERE "TEST_DATA"."CITY"='ROCHESTER' AND "TEST_DATA"."COUNTY"='MONROE1'
Here is the first plan

 PLAN_TABLE_OUTPUT                                                    
  
 -------------------------------------------------------------------------------------------------------------------------
  
 Plan hash value: 757026858                                                
  
 -----------------------------------------------------------------------------------------                
  
 | Id | Operation          | Name   | Rows | Bytes | Cost (%CPU)| Time   |                
 -----------------------------------------------------------------------------------------                
 |  0 | SELECT STATEMENT      |      |   1 |  120 |   3  (0)| 00:00:01 |                
 |* 1 | TABLE ACCESS BY INDEX ROWID| TEST_DATA |   1 |  120 |   3  (0)| 00:00:01 |                
 |* 2 |  INDEX RANGE SCAN     | INDEX2  |   2 |    |   1  (0)| 00:00:01 |                
 -----------------------------------------------------------------------------------------                
 Predicate Information (identified by operation id):                                   
 ---------------------------------------------------                                   
   1 - filter("COUNTY"='MONROE1' AND "STATE"='NY')                                    
   2 - access("CITY"='ROCHESTER')                                            
  
Second execution, notice that cardinality feedback will not be used on subsequent executions.

SQL_ID        CHILD_NUMBER           USE_FEEDBACK_STATS 
------------- ---------------------- ------------------ 
88jhv4mryj58p 0                      Y                  
88jhv4mryj58p 1                      N                  

V$SQLSTATS

SQL_ID        PLAN_HASH_VALUE        PARSE_CALLS            BUFFER_GETS            ROWS_PROCESSED         
------------- ---------------------- ---------------------- ---------------------- ---------------------- 
88jhv4mryj58p 2704719303             2                      148                    0                      

v$SQLSTATS_PLAN_HASH

SQL_ID        PLAN_HASH_VALUE        PARSE_CALLS            BUFFER_GETS            ROWS_PROCESSED         
------------- ---------------------- ---------------------- ---------------------- ---------------------- 
88jhv4mryj58p 2704719303             1                      2                      0                      
88jhv4mryj58p 757026858              1                      146                    0                      


Here is the output from the 10053 trace showing the feedback from the first execution using the OPT_ESTIMATE hint again.

SELECT /*+ OPT_ESTIMATE (INDEX_SCAN "TEST_DATA" "INDEX2" MIN=69.000000 ) 
           OPT_ESTIMATE (INDEX_FILTER "TEST_DATA" "INDEX2" ROWS=69.000000 ) */ 
    "TEST_DATA"."ZIPCODE" "ZIPCODE",
    "TEST_DATA"."LAT" "LAT",
    "TEST_DATA"."CITY" "CITY",
    "TEST_DATA"."STATE" "STATE",
    "TEST_DATA"."COUNTY" "COUNTY",
    "TEST_DATA"."TYPE" "TYPE",
    "TEST_DATA"."PREFERRED" "PREFERRED",
    "TEST_DATA"."WORLDREGION" "WORLDREGION",
    "TEST_DATA"."COUNTRY" "COUNTRY",
    "TEST_DATA"."LOCATIONTEXT" "LOCATIONTEXT",
    "TEST_DATA"."LOCATION" "LOCATION",
    "TEST_DATA"."POPULATION" "POPULATION",
    "TEST_DATA"."HOUSINGUNITS" "HOUSINGUNITS",
    "TEST_DATA"."INCOME" "INCOME",
    "TEST_DATA"."LANDAREA",
    "LANDAREATEST_DATA"."WATERAREA" "WATERAREA",
    "TEST_DATA"."DECOMMISIONED" "DECOMMISIONED",
    "TEST_DATA"."MILITARYRESTRICTIONCODES" "MILITARYRESTRICTIONCODES" 
FROM "BGRENN"."TEST_DATA" "TEST_DATA" 
WHERE "TEST_DATA"."CITY"='ROCHESTER' AND "TEST_DATA"."COUNTY"='MONROE1'
But notice something else... notice the plan has value changed from 757026858 to 2704719303.  Fine right ? Cardinality feedback did it's thing, but also notice this is the output from v$sqlstats.  Huh ?? The history for executions of the plan (1 for the first plan, and 1 for the second), got summarized into the second plan.

here is the second plan
 Plan hash value: 2704719303                                                     
  
 -----------------------------------------------------------------------------------------                      
 | Id | Operation          | Name   | Rows | Bytes | Cost (%CPU)| Time   |                      
 -----------------------------------------------------------------------------------------                      
 |  0 | SELECT STATEMENT      |      |    |    |   6 (100)|     |                      
 |* 1 | TABLE ACCESS BY INDEX ROWID| TEST_DATA |   1 |  120 |   6  (0)| 00:00:01 |                      
 |* 2 |  INDEX RANGE SCAN     | INDEX3  |  38 |    |   1  (0)| 00:00:01 |                      
 -----------------------------------------------------------------------------------------                      
  
 Predicate Information (identified by operation id):                                         
 ---------------------------------------------------  
   1 - filter("CITY"='ROCHESTER')                                                  
   2 - access("COUNTY"='MONROE1')                                                  
 PLAN_TABLE_OUTPUT                                                          
 ------------------------------------------------------------------------------------------------------------------------------------
  
 Note                                                                
 -----                                                                
   - cardinality feedback used for this statement         
What does all this mean ????

1) the 10053 trace will reveal where cardinality feedback was used by finding the OPT_ESTIMATE hint.
2) the v$sql_session_cursor view will show you where cardinality feedback was used.
3) There is a new view for looking at the multiple plans created for cardinality feedback called v$SQLSTATS_PLAN_HASH.

And in looking at the DBA_HIST_SQLSTAT, I do see both plans.

SQL_ID        PLAN_HASH_VALUE        PARSE_CALLS_DELTA      BUFFER_GETS_DELTA      ROWS_PROCESSED_DELTA   
------------- ---------------------- ---------------------- ---------------------- ---------------------- 
88jhv4mryj58p 757026858              1                      146                    0                      
88jhv4mryj58p 2704719303             1                      2                      0                      


Another observation I will would make is that when you multiple columns (and you aren't using a composite index), oracle is likely to utilyze cardinality feedback to ensure the cardinality is correct.

Finally, I did try the same example with bind variables (instead of literals).  I found that Cardinality Feedback did not kick in for my example.  Bind variables must tell the optimizer to pick a "generically" better plan, and not look around.

This is all on version 11.2.0.2


I have included a query I'm using to point out where cardinality feedback has been utilized, and sorted the queries by the affect they have on performance, so I can assess the gains (or losses) associated with the use of this feature. Overall my databases have show remarkable improvement, and those fiew cases where there is a regression, the sql_id is highligted so I can create a profile.


I have to note that this query has become invaluable to identify the top queries affected by cardinality feedback.  I have used it to identify where extended stats may help, and I have been able to determine, that cardinality feedback is indeed a good thing most of the time.  A lot of this detail is lost in the AWR reports.





select  query1.sql_id,
query1.plan_hash_value phv1,
query2.plan_hash_value phv2, 
query1.executions exec1,
query2.executions exec2,
trunc((query1.avg_elapsed_time)/1000000,2) aet,
trunc(( query2.avg_elapsed_time)/1000000,2) aet_card,
trunc(( query1.avg_elapsed_time - query2.avg_elapsed_time)/1000000,2) aet_diff,
abs(trunc(( (query1.avg_elapsed_time - query2.avg_elapsed_time)* query2.executions)/1000000,2)) abs_diff,
trunc( ((query1.avg_elapsed_time - query2.avg_elapsed_time) * query2.executions)/1000000,2) tot_diff,
trunc( ((query1.avg_elapsed_time - query2.avg_elapsed_time)/ query1.avg_elapsed_time) * 100,1) card_feedback_perc_difference,query1.sql_text
  from 
(select sql_id,plan_hash_value,last_active_time,executions,(rows_processed/executions) rows_processed,
(elapsed_time/executions) avg_elapsed_time,(cpu_time/executions) avg_cpu_time,(buffer_gets/executions) avg_buffer_gets,
sql_text from V$SQLSTATS_PLAN_HASH a
where exists (select plan_hash_value  from V$SQLSTATS_PLAN_HASH b  where a.sql_id=b.sql_id and a.plan_hash_value <> b.plan_hash_value and executions > 0)
and executions > 0
order by sql_id,last_active_time) query1,
(select sql_id,plan_hash_value,last_active_time,executions,(rows_processed/executions) rows_processed,
(elapsed_time/executions) avg_elapsed_time,(cpu_time/executions) avg_cpu_time,(buffer_gets/executions) avg_buffer_gets,
sql_text from V$SQLSTATS_PLAN_HASH a
where exists (select plan_hash_value  from V$SQLSTATS_PLAN_HASH b  where a.sql_id=b.sql_id and a.plan_hash_value <> b.plan_hash_value and executions > 0)
and executions > 0
order by sql_id,last_active_time) query2
where query1.sql_id=query2.sql_id
and query1.last_active_time < query2.last_active_time 
and exists (select 1 from V$SQL_SHARED_CURSOR c where query1.sql_id=c.sql_id and c.use_feedback_stats='Y') 
--and query1.avg_elapsed_time < query2.avg_elapsed_time
order by abs_diff desc

Monday, July 25, 2011

My 2 favorite queries

For finding out what happened with queries, here are my 2 favorite queries..

For searching the awr and previous executions
select * from table(dbms_xplan.display_awr('3cmh637q9msjs',null,null,'advanced +peeked_binds'));

For looking through the cursor cache
select * from table(dbms_xplan.display_cursor('3cmh637q9msjs',null,'advanced +peeked_binds'));


The output looks something like this...

line 8: SQLPLUS Command Skipped: set linesize 132
line 9: SQLPLUS Command Skipped: set pagesize 0
PLAN_TABLE_OUTPUT                                                                                                                                                                                                                                                                                            
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ 
SQL_ID 3j9yx7t5abcyg                                                                                                                                                                                                                                                                                         
--------------------                                                                                                                                                                                                                                                                                         
/* OracleOEM */  SELECT m.tablespace_name,        m.used_percent,                                                                                                                                                                                                                                            
   (m.tablespace_size - m.used_space)*t.block_size/1024/1024 mb_free                                                                                                                                                                                                                                         
FROM  dba_tablespace_usage_metrics m, dba_tablespaces t, v$parameter p                                                                                                                                                                                                                                       
WHERE p.name='statistics_level' and p.value!='BASIC'    AND                                                                                                                                                                                                                                                  
t.tablespace_name = m.tablespace_name                                                                                                                                                                                                                                                                                    
                                                                                                                                                                                                                                                                                                             
Plan hash value: 125441316                                                                                                                                                                                                                                                                                   
                                                                                                                                                                                                                                                                                                             
--------------------------------------------------------------------------------------------------------------------------------------------------                                                                                                                                                           
| Id  | Operation                            | Name                         | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |                                                                                                                                                           
--------------------------------------------------------------------------------------------------------------------------------------------------                                                                                                                                                           
|   0 | SELECT STATEMENT                     |                              |       |       |    19 (100)|          |        |      |            |                                                                                                                                                           
|   1 |  NESTED LOOPS                        |                              |     1 |  2209 |    19  (43)| 00:00:01 |        |      |            |                                                                                                                                                           
|   2 |   NESTED LOOPS                       |                              |     1 |  2196 |    19  (43)| 00:00:01 |        |      |            |                                                                                                                                                           
|   3 |    MERGE JOIN CARTESIAN              |                              |     1 |  2171 |    18  (45)| 00:00:01 |        |      |            |                                                                                                                                                           
|   4 |     HASH JOIN                        |                              |     1 |  2115 |     1 (100)| 00:00:01 |        |      |            |                                                                                                                                                           
|   5 |      FIXED TABLE FULL                | X$KSPPI                      |     1 |    81 |     0   (0)|          |        |      |            |                                                                                                                                                           
|   6 |      FIXED TABLE FULL                | X$KSPPCV                     |     5 | 10170 |     0   (0)|          |        |      |            |                                                                                                                                                           
|   7 |     BUFFER SORT                      |                              |    82 |  4592 |    18  (45)| 00:00:01 |        |      |            |                                                                                                                                                           
|   8 |      VIEW                            | DBA_TABLESPACE_USAGE_METRICS |    82 |  4592 |    17  (42)| 00:00:01 |        |      |            |                                                                                                                                                           
|   9 |       SORT UNIQUE                    |                              |    82 |  7594 |    17  (53)| 00:00:01 |        |      |            |                                                                                                                                                           
|  10 |        UNION-ALL                     |                              |       |       |            |          |        |      |            |                                                                                                                                                           
|  11 |         MERGE JOIN                   |                              |    80 |  5360 |     9  (12)| 00:00:01 |        |      |            |                                                                                                                                                           
|  12 |          TABLE ACCESS CLUSTER        | TS$                          |    31 |   868 |     8   (0)| 00:00:01 |        |      |            |                                                                                                                                                           
|  13 |           INDEX FULL SCAN            | I_TS#                        |     1 |       |     1   (0)| 00:00:01 |        |      |            |                                                                                                                                                           
|  14 |          SORT JOIN                   |                              |   100 |  3900 |     1 (100)| 00:00:01 |        |      |            |                                                                                                                                                           
|  15 |           FIXED TABLE FULL           | X$KTTETS                     |   100 |  3900 |     0   (0)|          |        |      |            |                                                                                                                                                           
|  16 |         HASH GROUP BY                |                              |     1 |    90 |     3  (67)| 00:00:01 |        |      |            |                                                                                                                                                           
|  17 |          NESTED LOOPS                |                              |     1 |    90 |     1   (0)| 00:00:01 |        |      |            |                                                                                                                                                           
|  18 |           FIXED TABLE FULL           | X$KTTEFINFO                  |     1 |    65 |     0   (0)|          |        |      |            |                                                                                                                                                           
|  19 |           TABLE ACCESS CLUSTER       | TS$                          |     1 |    25 |     1   (0)| 00:00:01 |        |      |            |                                                                                                                                                           
|  20 |            INDEX UNIQUE SCAN         | I_TS#                        |     1 |       |     0   (0)|          |        |      |            |                                                                                                                                                           
|  21 |         HASH GROUP BY                |                              |     1 |  2144 |     4  (75)| 00:00:01 |        |      |            |                                                                                                                                                           
|  22 |          NESTED LOOPS                |                              |       |       |            |          |        |      |            |                                                                                                                                                           
|  23 |           NESTED LOOPS               |                              |     1 |  2144 |     2  (50)| 00:00:01 |        |      |            |                                                                                                                                                           
|  24 |            HASH JOIN                 |                              |     1 |  2122 |     1 (100)| 00:00:01 |        |      |            |                                                                                                                                                           
|  25 |             PX COORDINATOR           |                              |     1 |    65 |     0   (0)|          |        |      |            |                                                                                                                                                           
|  26 |              PX SEND QC (RANDOM)     | :TQ10000                     |     1 |    65 |     0   (0)|          |  Q1,00 | P->S | QC (RAND)  |                                                                                                                                                           
|  27 |               VIEW                   | GV$FILESPACE_USAGE           |       |       |            |          |  Q1,00 | PCWP |            |                                                                                                                                                           
|  28 |                FIXED TABLE FULL      | X$KTTEFINFO                  |     1 |    65 |     0   (0)|          |  Q1,00 | PCWP |            |                                                                                                                                                           
|  29 |             PX COORDINATOR           |                              |     1 |  2057 |     1 (100)| 00:00:01 |        |      |            |                                                                                                                                                           
|  30 |              PX SEND QC (RANDOM)     | :TQ20000                     |     1 |  2115 |     1 (100)| 00:00:01 |  Q2,00 | P->S | QC (RAND)  |                                                                                                                                                           
|  31 |               VIEW                   | GV$PARAMETER                 |       |       |            |          |  Q2,00 | PCWP |            |                                                                                                                                                           
|  32 |                HASH JOIN             |                              |     1 |  2115 |     1 (100)| 00:00:01 |  Q2,00 | PCWP |            |                                                                                                                                                           
|  33 |                 FIXED TABLE FULL     | X$KSPPI                      |     1 |    81 |     0   (0)|          |  Q2,00 | PCWP |            |                                                                                                                                                           
|  34 |                 FIXED TABLE FULL     | X$KSPPCV                     |   100 |   198K|     0   (0)|          |  Q2,00 | PCWP |            |                                                                                                                                                           
|  35 |            INDEX UNIQUE SCAN         | I_TS1                        |     1 |       |     0   (0)|          |        |      |            |                                                                                                                                                           
|  36 |           TABLE ACCESS BY INDEX ROWID| TS$                          |     1 |    22 |     1   (0)| 00:00:01 |        |      |            |                                                                                                                                                           
|  37 |    TABLE ACCESS BY INDEX ROWID       | TS$                          |     1 |    25 |     1   (0)| 00:00:01 |        |      |            |                                                                                                                                                           
|  38 |     INDEX UNIQUE SCAN                | I_TS1                        |     1 |       |     0   (0)|          |        |      |            |                                                                                                                                                           
|  39 |   FIXED TABLE FIXED INDEX            | X$KCFISTSA (ind:1)           |     3 |    39 |     0   (0)|          |        |      |            |                                                                                                                                                           
--------------------------------------------------------------------------------------------------------------------------------------------------                                                                                                                                                           
                                                                                                                                                                                                                                                                                                             
Query Block Name / Object Alias (identified by operation id):                                                                                                                                                                                                                                                
-------------------------------------------------------------                                                                                                                                                                                                                                                
                                                                                                                                                                                                                                                                                                             
   1 - SEL$CBAA6355                                                                                                                                                                                                                                                                                          
   5 - SEL$CBAA6355 / X@SEL$12                                                                                                                                                                                                                                                                               
   6 - SEL$CBAA6355 / Y@SEL$12                                                                                                                                                                                                                                                                               
   8 - SET$1        / M@SEL$1                                                                                                                                                                                                                                                                                
   9 - SET$1                                                                                                                                                                                                                                                                                                 
  11 - SEL$2                                                                                                                                                                                                                                                                                                 
  12 - SEL$2        / T@SEL$2                                                                                                                                                                                                                                                                                
  13 - SEL$2        / T@SEL$2                                                                                                                                                                                                                                                                                
  15 - SEL$2        / TSTAT@SEL$2                                                                                                                                                                                                                                                                            
  16 - SEL$C8360722                                                                                                                                                                                                                                                                                          
  18 - SEL$C8360722 / X$KTTEFINFO@SEL$5                                                                                                                                                                                                                                                                      
  19 - SEL$C8360722 / T@SEL$3                                                                                                                                                                                                                                                                                
  20 - SEL$C8360722 / T@SEL$3                                                                                                                                                                                                                                                                                
  21 - SEL$6                                                                                                                                                                                                                                                                                                 
  25 - SEL$7        / F@SEL$6                                                                                                                                                                                                                                                                                
  28 - SEL$7        / X$KTTEFINFO@SEL$7                                                                                                                                                                                                                                                                      
  29 - SEL$8        / PARAM@SEL$6                                                                                                                                                                                                                                                                            
  32 - SEL$8                                                                                                                                                                                                                                                                                                 
  33 - SEL$8        / X@SEL$8                                                                                                                                                                                                                                                                                
  34 - SEL$8        / Y@SEL$8                                                                                                                                                                                                                                                                                
  35 - SEL$6        / T@SEL$6                                                                                                                                                                                                                                                                                
  36 - SEL$6        / T@SEL$6                                                                                                                                                                                                                                                                                
  37 - SEL$CBAA6355 / TS@SEL$9                                                                                                                                                                                                                                                                               
  38 - SEL$CBAA6355 / TS@SEL$9                                                                                                                                                                                                                                                                               
  39 - SEL$CBAA6355 / TSATTR@SEL$9                                                                                                                                                                                                                                                                           
                                                                                                                                                                                                                                                                                                             
Outline Data                                                                                                                                                                                                                                                                                                 
-------------                                                                                                                                                                                                                                                                                                
                                                                                                                                                                                                                                                                                                             
  /*+                                                                                                                                                                                                                                                                                                        
      BEGIN_OUTLINE_DATA                                                                                                                                                                                                                                                                                     
      IGNORE_OPTIM_EMBEDDED_HINTS                                                                                                                                                                                                                                                                            
      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')                                                                                                                                                                                                                                                                  
      DB_VERSION('11.2.0.1')                                                                                                                                                                                                                                                                                 
      OPT_PARAM('_optimizer_fast_pred_transitivity' 'false')                                                                                                                                                                                                                                                 
      ALL_ROWS                                                                                                                                                                                                                                                                                               
      OUTLINE_LEAF(@"SEL$2")                                                                                                                                                                                                                                                                                 
      OUTLINE_LEAF(@"SEL$C8360722")                                                                                                                                                                                                                                                                          
      MERGE(@"SEL$7286615E")                                                                                                                                                                                                                                                                                 
      OUTLINE_LEAF(@"SEL$7")                                                                                                                                                                                                                                                                                 
      OUTLINE_LEAF(@"SEL$8")                                                                                                                                                                                                                                                                                 
      OUTLINE_LEAF(@"SEL$6")                                                                                                                                                                                                                                                                                 
      OUTLINE_LEAF(@"SET$1")                                                                                                                                                                                                                                                                                 
      OUTLINE_LEAF(@"SEL$CBAA6355")                                                                                                                                                                                                                                                                          
      MERGE(@"SEL$9")                                                                                                                                                                                                                                                                                        
      MERGE(@"SEL$E3DF9B48")                                                                                                                                                                                                                                                                                 
      OUTLINE(@"SEL$3")                                                                                                                                                                                                                                                                                      
      OUTLINE(@"SEL$7286615E")                                                                                                                                                                                                                                                                               
      MERGE(@"SEL$5")                                                                                                                                                                                                                                                                                        
      OUTLINE(@"SEL$1")                                                                                                                                                                                                                                                                                      
      OUTLINE(@"SEL$9")                                                                                                                                                                                                                                                                                      
      OUTLINE(@"SEL$E3DF9B48")                                                                                                                                                                                                                                                                               
      MERGE(@"SEL$42DFC41A")                                                                                                                                                                                                                                                                                 
      OUTLINE(@"SEL$4")                                                                                                                                                                                                                                                                                      
      OUTLINE(@"SEL$5")                                                                                                                                                                                                                                                                                      
      OUTLINE(@"SEL$10")                                                                                                                                                                                                                                                                                     
      OUTLINE(@"SEL$42DFC41A")                                                                                                                                                                                                                                                                               
      MERGE(@"SEL$12")                                                                                                                                                                                                                                                                                       
      OUTLINE(@"SEL$11")                                                                                                                                                                                                                                                                                     
      OUTLINE(@"SEL$12")                                                                                                                                                                                                                                                                                     
      FULL(@"SEL$CBAA6355" "X"@"SEL$12")                                                                                                                                                                                                                                                                     
      FULL(@"SEL$CBAA6355" "Y"@"SEL$12")                                                                                                                                                                                                                                                                     
      NO_ACCESS(@"SEL$CBAA6355" "M"@"SEL$1")                                                                                                                                                                                                                                                                 
      INDEX_RS_ASC(@"SEL$CBAA6355" "TS"@"SEL$9" ("TS$"."NAME"))                                                                                                                                                                                                                                              
      FULL(@"SEL$CBAA6355" "TSATTR"@"SEL$9")                                                                                                                                                                                                                                                                 
      LEADING(@"SEL$CBAA6355" "X"@"SEL$12" "Y"@"SEL$12" "M"@"SEL$1" "TS"@"SEL$9" "TSATTR"@"SEL$9")                                                                                                                                                                                                           
      USE_HASH(@"SEL$CBAA6355" "Y"@"SEL$12")                                                                                                                                                                                                                                                                 
      USE_MERGE_CARTESIAN(@"SEL$CBAA6355" "M"@"SEL$1")                                                                                                                                                                                                                                                       
      USE_NL(@"SEL$CBAA6355" "TS"@"SEL$9")                                                                                                                                                                                                                                                                   
      USE_NL(@"SEL$CBAA6355" "TSATTR"@"SEL$9")                                                                                                                                                                                                                                                               
      NO_ACCESS(@"SEL$6" "F"@"SEL$6")                                                                                                                                                                                                                                                                        
      NO_ACCESS(@"SEL$6" "PARAM"@"SEL$6")                                                                                                                                                                                                                                                                    
      INDEX(@"SEL$6" "T"@"SEL$6" ("TS$"."NAME"))                                                                                                                                                                                                                                                             
      LEADING(@"SEL$6" "F"@"SEL$6" "PARAM"@"SEL$6" "T"@"SEL$6")                                                                                                                                                                                                                                              
      USE_HASH(@"SEL$6" "PARAM"@"SEL$6")                                                                                                                                                                                                                                                                     
      USE_NL(@"SEL$6" "T"@"SEL$6")                                                                                                                                                                                                                                                                           
      NLJ_BATCHING(@"SEL$6" "T"@"SEL$6")                                                                                                                                                                                                                                                                     
      USE_HASH_AGGREGATION(@"SEL$6")                                                                                                                                                                                                                                                                         
      FULL(@"SEL$C8360722" "X$KTTEFINFO"@"SEL$5")                                                                                                                                                                                                                                                            
      INDEX(@"SEL$C8360722" "T"@"SEL$3" "I_TS#")                                                                                                                                                                                                                                                             
      LEADING(@"SEL$C8360722" "X$KTTEFINFO"@"SEL$5" "T"@"SEL$3")                                                                                                                                                                                                                                             
      USE_NL(@"SEL$C8360722" "T"@"SEL$3")                                                                                                                                                                                                                                                                    
      USE_HASH_AGGREGATION(@"SEL$C8360722")                                                                                                                                                                                                                                                                  
      INDEX(@"SEL$2" "T"@"SEL$2" "I_TS#")                                                                                                                                                                                                                                                                    
      FULL(@"SEL$2" "TSTAT"@"SEL$2")                                                                                                                                                                                                                                                                         
      LEADING(@"SEL$2" "T"@"SEL$2" "TSTAT"@"SEL$2")                                                                                                                                                                                                                                                          
      USE_MERGE(@"SEL$2" "TSTAT"@"SEL$2")                                                                                                                                                                                                                                                                    
      PX_JOIN_FILTER(@"SEL$2" "TSTAT"@"SEL$2")                                                                                                                                                                                                                                                               
      FULL(@"SEL$7" "X$KTTEFINFO"@"SEL$7")                                                                                                                                                                                                                                                                   
      FULL(@"SEL$8" "X"@"SEL$8")                                                                                                                                                                                                                                                                             
      FULL(@"SEL$8" "Y"@"SEL$8")                                                                                                                                                                                                                                                                             
      LEADING(@"SEL$8" "X"@"SEL$8" "Y"@"SEL$8")                                                                                                                                                                                                                                                              
      USE_HASH(@"SEL$8" "Y"@"SEL$8")                                                                                                                                                                                                                                                                         
      END_OUTLINE_DATA                                                                                                                                                                                                                                                                                       
  */                                                                                                                                                                                                                                                                                                         
                                                                                                                                                                                                                                                                                                             
Note                                                                                                                                                                                                                                                                                                         
-----                                                                                                                                                                                                                                                                                                        
   - statement not queuable: gv$ statement                                                                                                                                                                                                                                                                   
                                                                                                                                                                                                                                                                                                             

158 rows selected