Hello there,
I have a table with a composite primary key over 3 columns (a,b,c). Now there are 2 typical queries running:
select * from my_table t where t.a = ? and t.b = ?
select * from my_table t where t.a = ? and t.b between ? and ? and t.c = ?
I notice that both queries share the same plan_hash_value in dba_hist_sql_plan. In both cases the optimizer is doing a range scan over my primary key index. But in the first case he is using 2 search_columns and makes a range scan over the third column. In the second query, he's using 3 search_columns making a range scan. Apparently, different values in the column "search_columns" does not make different hash values.
Now what worries me: We have some performance variations when querying for the exact 3 columns of the primary key. When I look at the dba_his_sql_plan I notice that even though we are querying for all 3 columns of a primary key / unique index, we experience a range scan. The data in dba_hist_sql_plan shows a value of "2" in search_columns. Now this wouldn't be troublesome because the last column has a low selectivity. But during the times we face bad performance, I fear that the optimizer is only using 1 search_column. Is there any way to figure that out?
I assume that if the optimizer decides to only use 1 search column and make a range scan over the rest, he would build an execution plan, calculate its hash value and then make the statistics available in dba_hist_sqlstat. For me there is no way to notice that he actually did something else (use 1 instead of 2 search columns) because it points to the same plan_hash_value.
Am I making this up?
No. The plan_hash_value depends on the shape of the plan. Not the predicates!
For example, all the following queries do a full table scan. So all the plans have the same hash value:
create table t (
x int,
y int,
z int
);
insert into t
select rownum, rownum*2, mod(rownum, 7) from dual
connect by level <= 100;
commit;
exec dbms_stats.gather_table_stats(user, 't');
set serveroutput off
select /*+ gather_plan_statistics */* from t;
select * from table(dbms_xplan.display_cursor(null, null, 'BASIC +PREDICATE'));
PLAN_TABLE_OUTPUT
EXPLAINED SQL STATEMENT:
------------------------
select /*+ gather_plan_statistics */* from t
Plan hash value: 2498539100
----------------------------------
| Id | Operation | Name |
----------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS FULL| T |
----------------------------------
select /*+ gather_plan_statistics */* from t
where x = 1;
select * from table(dbms_xplan.display_cursor(null, null, 'BASIC +PREDICATE'));
PLAN_TABLE_OUTPUT
EXPLAINED SQL STATEMENT:
------------------------
select /*+ gather_plan_statistics */* from t where x = 1
Plan hash value: 2498539100
----------------------------------
| Id | Operation | Name |
----------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | TABLE ACCESS FULL| T |
----------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("X"=1)
select /*+ gather_plan_statistics */* from t
where z = 2
and y between 4 and 128;
select * from table(dbms_xplan.display_cursor(null, null, 'BASIC +PREDICATE'));
PLAN_TABLE_OUTPUT
EXPLAINED SQL STATEMENT:
------------------------
select /*+ gather_plan_statistics */* from t where z = 2 and y
between 4 and 128
Plan hash value: 2498539100
----------------------------------
| Id | Operation | Name |
----------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | TABLE ACCESS FULL| T |
----------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("Z"=2 AND "Y"<=128 AND "Y">=4))
But clearly the performance between these could be very different. One returns all the rows (no where clause), another returns just one row (where x = 1)!
On to your issue. To see exactly which conditions Oracle is applying at which stage, get an execution plan.
Use the select * from table(dbms...) query I used above, ensuring you have the +PREDICATE option. This will show you which conditions were applied where. You'll probably also want to see how many rows Oracle processed at each step. Use "ALLSTATS LAST" instead of BASIC to do this:
select *
from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST +PREDICATE'));
For more about creating execution plans, read:
https://blogs.oracle.com/sql/entry/how_to_create_an_execution If you need further help, please post the plans from your queries.