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


Sunday, July 24, 2011

dbreplay vs SPA (cage match).


DBreplay vs SPA (SQL Profile Analyzer)




DBreplay
– This product is utilized through the following steps



  • Capture a production workload during a critical time. All Statements are captured and stored.


  • Copy (or mount), the capture files on your “replay” server.


  • Preprocess the workload


  • Restore your database, up to the point in time of the capture (the capture can give you the exact SCN needed).


  • Replay the workload on the copy of production


  • Compare the replay to the original capture statistics (through AWR). You can also compare different runs to each other.


Pros


  • The entire workload is replayed with the same data set as your source.


  • GTT (global temporary tables), are primed properly


  • The workload is same as production (top executed sql are executed multiple times).


  • Cardinality feedback, and other optimizer pieces that may change with executions should be seen.


Cons


  • Capturing a production workload, and preparing a copy of production is a huge effort.


  • This only tests workload that occurs during the capture. If there are different workload windows (i.e. batch vs online), you must capture the
    different workloads and restore to match.


  • You cannot touch sql, and anything that part of the capture. If you receive errors, (like ora-4031) you must correct the errors before continuing.



SPA (SQL Profile Analyzer)
– This product is

utilized through the following steps.



  • A sql tuning set is created on the source system (this contains the sql, the plans, and the execution statistics.


  • The sql tuning set is exported from old imported into new system.


  • A SPA job is created that will either compare the statistics with the original execution, or compare statistics by executing on both servers
    (through database links).


  • Information on the sql statements is reported on, and prioritized by the effect on the workload.


PROS


  • You don’t need to have the database synched up, as long as it is somewhat representative.


  • You can re-execute against the source system to compare executions.


  • A report is created comparing each sql execution


CONS


  • Does not work with GTT (global temporary tables), since they cannot be primed to run this process


  • The database test set most likely does not match source, so you need to interpret most of the data.


  • You do not see the interaction of SQL statements.



Conclusion – DBReplay is the preferred tool to test performance differences when making an infrastructure change. If GTT’s are utilized, then DBReplay
is the only tool can really give you useful data. If DBReplay cannot be used, or to supplement dbreplay, SPA is also a useful tool. You can also use
SPA to better tune specific sql (utilize DBReplay to identify sql, and SPA to deep dive them).






Monday, July 11, 2011

DBreplay why are you messing with my sequences ??

Well,
  I have been playing with dbreplay, and trying to re-run a production workload.  In order to capture a production workload, I don't have the luxury of bouncing the database.  I start the capture, and do a restore of production that is "roughly right".  A point in time recovery close to that point.

Well I've been fighting an issue for the last couple of days.  I run a replay, and I find a lot of the my sessions are getting hung up, and I have sequences that have starting values BEFORE the value they were set to at the beginning.  At first I thought the flashback somehow messed up my sequences, but now I know it is the replay.

Part of what the "prepare client" process does is this sql step

 SELECT MAX(FIRST_VALUE), MAX(LAST_VALUE), MIN(FIRST_VALUE), MIN(LAST_VALUE), SEQ_BOW, SEQ_NAME, INCREMENT_BY, CYCLE_FLAG, CACHE_SIZE 
  
 FROM WRR$_REPLAY_SEQ_DATA R, DBA_SEQUENCES S 
  
 WHERE R.SEQ_BOW = S.SEQUENCE_OWNER 
  
 AND R.SEQ_NAME = S.SEQUENCE_NAME 
  
 GROUP BY SEQ_NAME ,SEQ_BNM, SEQ_BOW, INCREMENT_BY, CYCLE_FLAG, CACHE_SIZE  

This step looks to see if the current value of the sequence is out of synch with the value when the capture was done.. If so it resynchs the sequences. Sweet eh ? but maybe not what you wanted.. This is what is actually being executed (when you run a trace).

 SELECT "OE"."CHANGE_TRACKING_SEQ".NEXTVAL FROM dual
  
 ALTER SEQUENCE "OE"."CHANGE_TRACKING_SEQ" MAXVALUE 1E27 MINVALUE -1E26 INCREMENT BY -789390 NOCACHE
  
 SELECT "OE"."CHANGE_TRACKING_SEQ".NEXTVAL FROM dual
  
 ALTER SEQUENCE "OE"."CHANGE_TRACKING_SEQ" INCREMENT BY 1 NOCACHE  

As you can see it takes the sequences, alters it to subract on the next call, executes the nextval, then resets with an alter. Pretty slick, but very hard to find.

Thank goodness for Logminer to help point me in the right direction as to who was altering my sequence.

Here is some additional advice. To check to see what sequences have been "reset" look at the last_ddl time from dba_objects. Capture the list of what's changed, and then flashback and get the originall ddl.

My process is now prepare clients, reset sequences, then release the clients.

Search words. database replay sequences out of order reset

Friday, July 8, 2011

Flashback and Sequences

I just ran into a situation using flashback and dbreplay. 

See my next post on DBReplay. this was the culprit.

Saturday, June 18, 2011

Exadata presentations

Well, I'm finally getting my exadata presentations up on my blog.  I will give a synopsis of it so you can decide if you want to look through them.. I also included a some screenshots that are meaningless without some background.

Hardware  (download)

My conclusion on hardware is that the exadata is merely off the shelf hardware.  Yes putting together the hardware in the exadata configuration (with storage cells, and infiniband) does greatly improve performance over most "normal" configurations that use arrays (like Hitachi, IBM, EMC, etc. etc.) over Fiber.

You can build your own server/storage that is even faster using SSD over infininband and you can customize it to your needs balancing the storage and database for YOUR NEEDS.

Software (download)

This is where the solution shines.  As I said above you can build the hardware yourself, but the gain is in the software.. In my test case I took a 276gb table with 1.7billion rows, and scanned it in under a second by combining HCC, storage indexes and flashcache.  Pretty incredible (it only used 6gb of disk space too).

However, the more your application is OLTP like (so it is less likely to HCC compressed, and scanned) the less gain you see with this solution.

Enjoy !

Sunday, June 5, 2011

indexing HCC partititions

I have been playing with what happens when you index HCC compressed data .  If you follow the recommended strategy, you will partition your history tables, and end up with some data uncompressed, some OLTP compressed, and some in different stages of HCC compressed.  What if you use an index lookup that spans all these levels of compression ?

First I took a table that started at 176g of data (uncompressed).

OLTP compression took it down to 76G

HCC query compression took it down to 6G.

Now I indexed the HCC copy.. My index (on a single column) ended up being 40g. 

Now I queried the data using the index.  First execution was longer than querying the unindexed data (with storage indexes working their magic).  Second index is much, much faster.

The lesson I learned is that HCC really helps with storage, but start indexing the HCC data, and you end up using a lot more storage.  Also unindexed lookups can be faster than indexed (until they hit the SGA).

Interesting information to help plan what happens when I go to query across all flavors of compression.