I have been fighting cardinality feedback issues all week, and I wanted to document an example of cardinality feedback in action.
First take build my test case, it can be found here
drop user testcard cascade;
create user testcard identified by testcard;
grant dba to testcard;
create TABLE testcard.object_sample1 as select * from dba_objects where 1=2;
create TABLE testcard.object_sample2 as select * from dba_objects where 1=2;
Insert into testcard.object_sample1 (select * from dba_objects where owner in ('SYS','TESTCARD') );
Insert into testcard.object_sample1 (select * from testcard.object_sample1 );
Insert into testcard.object_sample1 (select * from testcard.object_sample1 );
Insert into testcard.object_sample2 (select * from dba_objects where owner in ('SYS','TESTCARD') );
create index testcard.idx1_object_sample2 on testcard.object_sample2(owner);
create index testcard.idx1_object_sample1 on testcard.object_sample1(owner);
EXECUTE DBMS_STATS.GATHER_TABLE_STATS( ownname => 'TESTCARD', tabname => 'OBJECT_SAMPLE1', method_opt => 'FOR ALL COLUMNS SIZE 1',granularity => 'ALL',cascade => TRUE,degree => DBMS_STATS.DEFAULT_DEGREE);
EXECUTE DBMS_STATS.GATHER_TABLE_STATS( ownname => 'TESTCARD', tabname => 'OBJECT_SAMPLE2', method_opt => 'FOR ALL COLUMNS SIZE 1',granularity => 'ALL',cascade => TRUE,degree => DBMS_STATS.DEFAULT_DEGREE);
Now lets take a look a what we have
select owner,count(1) from testcard.object_sample1 group by owner;
OWNER COUNT(1)
------------------------------ ----------
TESTCARD 8
SYS 126620
select owner,count(1) from testcard.object_sample2 group by owner;
OWNER COUNT(1)
------------------------------ ----------
TESTCARD 2
SYS 31655
Now here is the query
select * from testcard.object_sample1 a,
testcard.object_sample2 b
where a.owner='SYS' and a.owner is not null and a.object_type='SSSSS'
and b.owner='SYS' and b.object_type=a.object_type and a.owner=b.owner
Notice the "is not null" clause, and notice the cardinality. Finally notice I did not create a histogram for the columns. if you run this query for owner of 'TESTCARD', it should return 2 rows, and if you run it for 'SYS' it should scan 126,00+ rows.
Well the developers are running it for 'TESTCARD', and it is doing a full table scan. They want to use the index for OWNER. They find a cheat, and rewrite the query. This fools the optimize into thinking the index is a better bet since a function is used (the decode function)..
Below is the query with the "is not null" and the "decode" and you can see the 2 different paths.
"A.OWNER IS NOT NULL"
EXPLAIN PLAN
SET STATEMENT_ID = 'abc' FOR
select * from testcard.object_sample1 a,
testcard.object_sample2 b
where a.owner='SYS' and a.owner is not null and a.object_type='SSSSS'
and b.owner='SYS' and b.object_type=a.object_type and a.owner=b.owner;
set pagesize 25
set linesize 121
set long 1000000
col xplan format a100
SELECT dbms_xplan.display_plan(statement_id => 'abc') AS XPLAN
FROM dual;
Plan Hash Value : 3876965040
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 294519 | 56547648 | 650 | 00:00:08 |
| * 1 | HASH JOIN | | 294519 | 56547648 | 650 | 00:00:08 |
| * 2 | TABLE ACCESS FULL | OBJECT_SAMPLE2 | 384 | 36864 | 136 | 00:00:02 |
| * 3 | TABLE ACCESS FULL | OBJECT_SAMPLE1 | 1535 | 147360 | 513 | 00:00:07 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 1 - access("B"."OBJECT_TYPE"="A"."OBJECT_TYPE" AND "A"."OWNER"="B"."OWNER")
* 2 - filter("B"."OBJECT_TYPE"='SSSSS' AND "B"."OWNER"='SYS')
* 3 - filter("A"."OBJECT_TYPE"='SSSSS' AND "A"."OWNER"='SYS')
"DECODE(A.OWNER,NULL,1,2)=2"
EXPLAIN PLAN
SET STATEMENT_ID = 'cde' FOR
select * from testcard.object_sample1 a,
testcard.object_sample2 b
where a.owner='SYS' and decode(a.owner,null,1,2)=2 and a.object_type='SSSSS'
and b.owner='SYS' and b.object_type=a.object_type and a.owner=b.owner ;
set pagesize 25
set linesize 121
set long 1000000
col xplan format a100
SELECT dbms_xplan.display_plan(statement_id => 'cde') AS XPLAN
FROM dual;
----------------------------------------------------------------------------------------------------
Plan Hash Value : 2829654189
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2945 | 565440 | 279 | 00:00:04 |
| * 1 | HASH JOIN | | 2945 | 565440 | 279 | 00:00:04 |
| * 2 | TABLE ACCESS BY INDEX ROWID | OBJECT_SAMPLE1 | 15 | 1440 | 142 | 00:00:02 |
| * 3 | INDEX RANGE SCAN | IDX1_OBJECT_SAMPLE1 | 629 | | 133 | 00:00:02 |
| * 4 | TABLE ACCESS FULL | OBJECT_SAMPLE2 | 384 | 36864 | 136 | 00:00:02 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 1 - access("B"."OBJECT_TYPE"="A"."OBJECT_TYPE" AND "A"."OWNER"="B"."OWNER")
* 2 - filter("A"."OBJECT_TYPE"='SSSSS')
* 3 - access("A"."OWNER"='SYS')
* 3 - filter(DECODE("A"."OWNER",NULL,1,2)=2)
* 4 - filter("B"."OBJECT_TYPE"='SSSSS' AND "B"."OWNER"='SYS')
But cardinality feedback kicks in, when the query is executed with an owner of 'SYS'.
Below is the output of the display_cursor.. Notice the 2 plans, and notice that cardinality feedback was used to change the plan from an index lookup to FTS again.
select * from testcard.object_sample1 a,testcard.object_sample2 b where a.owner='SYS' and decode(a.owner,null,1,2)=2 and a.object_type='SSSSS' and b.owner='SYS' and b.object_type=a.object_type and a.owner=b.owner;
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------
SQL_ID 91ugxuy8jc3g4, child number 0
-------------------------------------
select * from testcard.object_sample1 a,
testcard.object_sample2 b where a.owner='SYS' and
decode(a.owner,null,1,2)=2 and a.object_type='SSSSS' and
b.owner='SYS' and b.object_type=a.object_type and a.owner=b.owner
Plan hash value: 2829654189
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 279 (100)| |
|* 1 | HASH JOIN | | 2945 | 552K| 279 (1)| 00:00:04 |
|* 2 | TABLE ACCESS BY INDEX ROWID| OBJECT_SAMPLE1 | 15 | 1440 | 142 (1)| 00:00:02 |
|* 3 | INDEX RANGE SCAN | IDX1_OBJECT_SAMPLE1 | 629 | | 133 (1)| 00:00:02 |
|* 4 | TABLE ACCESS FULL | OBJECT_SAMPLE2 | 384 | 36864 | 136 (0)| 00:00:02 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------
1 - access("B"."OBJECT_TYPE"="A"."OBJECT_TYPE" AND "A"."OWNER"="B"."OWNER")
2 - filter("A"."OBJECT_TYPE"='SSSSS')
3 - access("A"."OWNER"='SYS')
filter(DECODE("A"."OWNER",NULL,1,2)=2)
4 - filter(("B"."OBJECT_TYPE"='SSSSS' AND "B"."OWNER"='SYS'))
select * from testcard.object_sample1 a,testcard.object_sample2 b where a.owner='SYS' and decode(a.owner,null,1,2)=2 and a.object_type='SSSSS' and b.owner='SYS' and b.object_type=a.object_type and a.owner=b.owner;
select * from table(dbms_xplan.display_cursor('07prx7pkch6tr',null,'typical +peeked_binds'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------
SQL_ID 91ugxuy8jc3g4, child number 0
-------------------------------------
select * from testcard.object_sample1 a,
testcard.object_sample2 b where a.owner='SYS' and
decode(a.owner,null,1,2)=2 and a.object_type='SSSSS' and
b.owner='SYS' and b.object_type=a.object_type and a.owner=b.owner
Plan hash value: 2829654189
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 279 (100)| |
|* 1 | HASH JOIN | | 2945 | 552K| 279 (1)| 00:00:04 |
|* 2 | TABLE ACCESS BY INDEX ROWID| OBJECT_SAMPLE1 | 15 | 1440 | 142 (1)| 00:00:02 |
|* 3 | INDEX RANGE SCAN | IDX1_OBJECT_SAMPLE1 | 629 | | 133 (1)| 00:00:02 |
|* 4 | TABLE ACCESS FULL | OBJECT_SAMPLE2 | 384 | 36864 | 136 (0)| 00:00:02 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------
1 - access("B"."OBJECT_TYPE"="A"."OBJECT_TYPE" AND "A"."OWNER"="B"."OWNER")
2 - filter("A"."OBJECT_TYPE"='SSSSS')
3 - access("A"."OWNER"='SYS')
filter(DECODE("A"."OWNER",NULL,1,2)=2)
4 - filter(("B"."OBJECT_TYPE"='SSSSS' AND "B"."OWNER"='SYS'))
SQL_ID 91ugxuy8jc3g4, child number 1
-------------------------------------
select * from testcard.object_sample1 a,
testcard.object_sample2 b where a.owner='SYS' and
decode(a.owner,null,1,2)=2 and a.object_type='SSSSS' and
b.owner='SYS' and b.object_type=a.object_type and a.owner=b.owner
Plan hash value: 1738482415
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 650 (100)| |
|* 1 | HASH JOIN | | 2945 | 552K| 650 (1)| 00:00:08 |
|* 2 | TABLE ACCESS FULL| OBJECT_SAMPLE1 | 15 | 1440 | 513 (1)| 00:00:07 |
|* 3 | TABLE ACCESS FULL| OBJECT_SAMPLE2 | 384 | 36864 | 136 (0)| 00:00:02 |
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("B"."OBJECT_TYPE"="A"."OBJECT_TYPE" AND
"A"."OWNER"="B"."OWNER")
2 - filter(("A"."OBJECT_TYPE"='SSSSS' AND "A"."OWNER"='SYS' AND
DECODE("A"."OWNER",NULL,1,2)=2))
3 - filter(("B"."OBJECT_TYPE"='SSSSS' AND "B"."OWNER"='SYS'))
Note
-----
- cardinality feedback used for this statement
NOTE : I've played further, and this is what I could find. Anyone correct if any of these are wrong..
) The v$sqlstats only showed one plan_hash_value even though the plan changed.
2) The plan didn't change immediately if I execute one after another.. When I put in a sleep, then the plan changes (must be some sort of delay before cardinality feedback kicks in).
3) Once locked into the new plan, it stays with it.
4) Adaptive Cursor sharing only kicks in if a histogram is used, or if the bind values fall outside the high/low values.
5) Oracle avoids using plans that are suspect (i.e. in my example with decode).
6) Oracle tends to use cardinality feedback more when the plan is "suspect" because of some sort of rough estimation.
7) Using dynamic sampling causes Oracle to use cardinality feedback more.
Cardinality feedback seems to be more of a weighting thing. Oracle uses tries to look at the "situation" and the less likely the estimate is reliable, the more likely it is to use cardinality feedback to choose another plan.
No comments:
Post a Comment