Search

Top 60 Oracle Blogs

Recent comments

CBO

Optimizer cleverness

At present I'm quite busy and therefore don't have much time to spent on writing blog notes, but I couldn't resist to publish this small and simple test case.

Often you can read (mostly unqualified) rants in various places and forums about the Cost Based Optimizer how stupid, unpredictable etc. it seems to be.

So I think it's time to demonstrate how clever the optimizer sometimes can be.

Consider the following setup:

drop table t_opt_clever purge;

-- Use PCTFREE 99 so that only one row per (leaf) block
-- This can tell us how many "rows" had to be inspected
-- by checking the number of (leaf) blocks accessed
-- Unfortunately Oracle (usually) doesn't provide the information
-- how many rows have been accessed in the execution plan,
-- but only how many rows are returned by an operation
create table t_opt_clever (
id not null constraint pk_opt_clever primary key,
col1 not null,
col2 not null,
col3 not null,
col4 not null,
col5 not null,
filler
)
pctfree 99
pctused 1
as
select
level as id
, round(dbms_random.value(0, 200)) as col1
, round(dbms_random.value(0, 400)) as col2
, case
when level <= 666
then 'FIRST_BUCKET'
when level <= 833
then 'SECOND_BUCKET'
when level <= 1000
then 'THIRD_BUCKET'
end as col3
, round(dbms_random.value(0, 600)) as col4
, round(dbms_random.value(0, 800)) as col5
, rpad('x', 100, 'x') as filler
from
dual
connect by
level <= 1000;

create index idx_opt_clever1 on t_opt_clever (col5, col1, col4, col2) pctfree 99 compute statistics;

create index idx_opt_clever2 on t_opt_clever (col5, col1, col3, col4, col2) pctfree 99 compute statistics;

exec dbms_stats.gather_table_stats(null, 'T_OPT_CLEVER')

-- scale the table and index by factor 1000
exec dbms_stats.set_table_stats(null, 'T_OPT_CLEVER', numrows => 1000000, numblks => 30000)

exec dbms_stats.set_index_stats(null, 'PK_OPT_CLEVER', numrows=> 1000000, numlblks => 2000, numdist=>1000000, clstfct => 100000, indlevel => 3)

exec dbms_stats.set_index_stats(null, 'IDX_OPT_CLEVER1', numrows=> 1000000, numlblks => 14000, numdist=>1000000, clstfct => 1000000, indlevel => 3)

exec dbms_stats.set_index_stats(null, 'IDX_OPT_CLEVER2', numrows=> 1000000, numlblks => 16000, numdist=>1000000, clstfct => 1000000, indlevel => 3)

Basically this simulates a 1,000,000 rows table with two suboptimal indexes given the following Top 100 query:

-- Now which index can be efficiently used by the optimizer?
select
*
from (
select
*
from
t_opt_clever
where
col3 = 'FIRST_BUCKET'
order by
col3, col5, col1, col4, col2
)
where
rownum <= 100;

Now what do you think, can one of these indexes efficiently be used by the optimizer, and if yes, which one?

At first sight both indexes can't be used to satisfy the requested sort order to avoid a costly full scan of data and a corresponding SORT ORDER BY (STOPKEY) operation, and can't be used efficiently to filter the data because the filter predicate is not among the leading columns.

Let's check the result:

SQL> select * from table(dbms_xplan.display_cursor(null, null, '+COST ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID fz6vky8n5a3xq, child number 0
-------------------------------------
select * from ( select * from t_opt_clever where
col3 = 'FIRST_BUCKET' order by col3, col5, col1, col4, col2 ) where
rownum <= 100

Plan hash value: 4203008252

---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | Reads |
---------------------------------------------------------------------------------------------------------------------------------
|* 1 | COUNT STOPKEY | | 1 | | | 100 |00:00:00.29 | 256 | 100 |
| 2 | VIEW | | 1 | 101 | 109 (0)| 100 |00:00:00.29 | 256 | 100 |
| 3 | TABLE ACCESS BY INDEX ROWID| T_OPT_CLEVER | 1 | 333K| 109 (0)| 100 |00:00:00.29 | 256 | 100 |
|* 4 | INDEX FULL SCAN | IDX_OPT_CLEVER2 | 1 | 101 | 8 (0)| 100 |00:00:00.01 | 156 | 0 |
---------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(ROWNUM<=100)
4 - access("COL3"='FIRST_BUCKET')
filter("COL3"='FIRST_BUCKET')

24 rows selected.

That is quite interesting, the index IDX_OPT_CLEVER2 is used and no SORT ORDER BY operation can be found in the execution plan, although the index doesn't match the requested sort order. And here comes the cleverness of the optimizer: It recognizes that due to the filter predicate on COL3 this index can actually be used to satisfy the sort order because it is not relevant for the resulting order since COL3 will always be the constant value of the filter predicate. And the same applies to IDX_OPT_CLEVER1, by the way.

But IDX_OPT_CLEVER2 is more efficient than using IDX_OPT_CLEVER1 because the filter predicate can be evaluated on the index data already eliminating some of the rows before visiting the table. Depending on the clustering factor this can make a significant difference to the cost of the operation, since random row accesses to table rows potentially require to access a different block per row.

This can be seen when forcing the usage of IDX_OPT_CLEVER1:

SQL> select * from table(dbms_xplan.display_cursor(null, null, '+COST ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 5tgmgfvyyx6z6, child number 0
-------------------------------------
select * from ( select /*+ index(t_opt_clever idx_opt_clever1) */ * from
t_opt_clever where col3 = 'FIRST_BUCKET' order by col3,
col5, col1, col4, col2 ) where rownum <= 100

Plan hash value: 678132971

---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | Reads |
---------------------------------------------------------------------------------------------------------------------------------
|* 1 | COUNT STOPKEY | | 1 | | | 100 |00:00:00.20 | 310 | 54 |
| 2 | VIEW | | 1 | 101 | 312 (1)| 100 |00:00:00.20 | 310 | 54 |
|* 3 | TABLE ACCESS BY INDEX ROWID| T_OPT_CLEVER | 1 | 101 | 312 (1)| 100 |00:00:00.20 | 310 | 54 |
| 4 | INDEX FULL SCAN | IDX_OPT_CLEVER1 | 1 | 1000K| 8 (0)| 154 |00:00:00.01 | 156 | 0 |
---------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(ROWNUM<=100)
3 - filter("COL3"='FIRST_BUCKET')

23 rows selected.

Two things can be seen here:

1. The optimizer is again smart and is able to avoid the SORT ORDER BY operation, because the index IDX_OPT_CLEVER1 can also be used to return in the data in the requested order, again because COL3 is constant.

2. Using IDX_OPT_CLEVER1 is less efficient because more table rows have to be visited to apply the filter predicate.

The fact that the indexes can only be used efficiently under this special circumstance can be verified by changing the filter predicate so that COL3 can have more than a single value and therefore it's no longer possible to avoid an ORDER BY operation:

-- Change the filter predicate and force index
select
*
from (
select /*+ index(t_opt_clever idx_opt_clever2) */
*
from
t_opt_clever
where
col3 in ('FIRST_BUCKET', 'SECOND_BUCKET')
order by
col5, col1, col4, col2
)
where
rownum <= 100;
SQL> select * from table(dbms_xplan.display_cursor(null, null, '+COST ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID axr6u0yvdk50f, child number 0
-------------------------------------
select * from ( select /*+ index(t_opt_clever idx_opt_clever2) */ * from
t_opt_clever where col3 in ('FIRST_BUCKET', 'SECOND_BUCKET') order by col3, col5, col1,
col4, col2 ) where rownum <= 100

Plan hash value: 2229390605

----------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------
|* 1 | COUNT STOPKEY | | 1 | | | 100 |00:00:00.02 | 1835 | | | |
| 2 | VIEW | | 1 | 666K| 703K (1)| 100 |00:00:00.02 | 1835 | | | |
|* 3 | SORT ORDER BY STOPKEY | | 1 | 666K| 703K (1)| 100 |00:00:00.02 | 1835 | 20480 | 20480 |18432 (0)|
| 4 | TABLE ACCESS BY INDEX ROWID| T_OPT_CLEVER | 1 | 666K| 683K (1)| 833 |00:00:00.01 | 1835 | | | |
|* 5 | INDEX FULL SCAN | IDX_OPT_CLEVER2 | 1 | 666K| 16100 (1)| 833 |00:00:00.01 | 1002 | | | |
----------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(ROWNUM<=100)
3 - filter(ROWNUM<=100)
5 - filter(("COL3"='FIRST_BUCKET' OR "COL3"='SECOND_BUCKET'))

25 rows selected.

Without the index hint the optimizer chooses a full table scan. Forcing e.g. the index IDX_OPT_CLEVER2 shows that indeed all rows had to be processed first and additionally a sort operation was necessary.

So it's interesting to note that the optimizer recognizes special cases where single value predicates allow an index usage that otherwise wouldn't be possible. This is a nice move, since it allows to perform above query in quite an efficient manner although the setup is suboptimal (e.g. a different index with COL3 as leading column or an appropriate IOT could be more suitable, depending on what else is done with the table). Under these (simulated) circumstances this optimization makes quite a difference compared to the otherwise only possible full table scan operation of a 30,000 blocks table.

By the way, above results could be reproduced on 10.2.0.4 and 11.1.0.7 Win32 using default system statistics and an 8KB LMT MSSM tablespace.

PLAN_HASH_VALUE - How equal (and stable?) are your execution plans - part 1

Oracle provides in recent releases the PLAN_HASH_VALUE information, which according to the documentation, is the following:

"Numerical representation of the SQL plan for the cursor. Comparing one PLAN_HASH_VALUE to another easily identifies whether or not two plans are the same (rather than comparing the two plans line by line)."

So according to the documentation the PLAN_HASH_VALUE can be used as a shortcut to quickly and easily determine if two execution plans are the "same".

I think that the statement "the two execution plans are the same" suggests that execution plans having the same PLAN_HASH_VALUE yield the same or at least similar performance at runtime. An interesting point that I would like to cover here in the following test cases.

This raises the interesting question, what exactly is the PLAN_HASH_VALUE based upon? Obviously it is a hash value calculated using the execution plan information as input. Apart from the fact that hash values are in theory always subject to potential hash collisions, which means that two different inputs can lead to the same hash value, the more interesting question is, which attributes of the execution plan are used as input?

Having this information at hand allows us to get a better understanding if two execution plans with the same PLAN_HASH_VALUE actually have to a yield similar execution profile (which Oracle doesn't say but I assume is a common assumption - or may be misconception?).

Let's start with a simple example to determine what makes the PLAN_HASH_VALUE different. All results shown below come from an 11.1.0.7 Win32 instance with a 8KB default block size, a MSSM 8KB LMT tablespace and default system statistics.

SQL>
SQL> drop table plan_hash_value_test1 purge;

Table dropped.

SQL>
SQL> drop table plan_hash_value_test2 purge;

Table dropped.

SQL>
SQL> drop table plan_hash_value_test3 purge;

Table dropped.

SQL>
SQL> drop user test_user_plan_hash_value cascade;

User dropped.

SQL>
SQL> create user test_user_plan_hash_value
2 identified by test_user_plan_hash_value
3 default tablespace test_8k
4 quota unlimited on test_8k;

User created.

SQL>
SQL> create table plan_hash_value_test1
2 as
3 select
4 id as id1
5 , mod(id, 2) as id2
6 , rpad('x', 20) as small_vc
7 from
8 (
9 select
10 level as id
11 from
12 dual
13 connect by
14 level <= 1000
15 );

Table created.

SQL>
SQL> exec dbms_stats.gather_table_stats(null, 'plan_hash_value_test1')

PL/SQL procedure successfully completed.

SQL>
SQL> create index plan_hash_value_test1_idx1 on plan_hash_value_test1 (id1, id2);

Index created.

SQL>
SQL> alter system flush shared_pool;

System altered.

SQL>
SQL> alter system flush shared_pool;

System altered.

SQL>
SQL> select /*+ find_me
2 index(a, plan_hash_value_test1_idx1) */
3 *
4 from
5 plan_hash_value_test1 a
6 where
7 id1 = 1
8 and rownum <= 1;

ID1 ID2 SMALL_VC
---------- ---------- --------------------
1 1 x

SQL>
SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID 3u4sfg1kzqtct, child number 0
-------------------------------------
select /*+ find_me index(a, plan_hash_value_test1_idx1) */
* from plan_hash_value_test1 a where id1 = 1
and rownum <= 1

Plan hash value: 2655295642

-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| PLAN_HASH_VALUE_TEST1 | 1 | 27 | 3 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | PLAN_HASH_VALUE_TEST1_IDX1 | 1 | | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(ROWNUM<=1)
3 - access("ID1"=1)

23 rows selected.

SQL>
SQL> select
2 object#, object_owner, object_name
3 from
4 v$sql_plan
5 where
6 operation = 'INDEX'
7 and sql_id = (select
8 sql_id
9 from
10 v$sql
11 where
12 sql_text like 'select /*+ find_me%'
13 );

OBJECT# OBJECT_OWNER OBJECT_NAME
---------- ------------------------------ ------------------------------
76267 CBO_TEST PLAN_HASH_VALUE_TEST1_IDX1

SQL>
SQL> pause

SQL>
SQL> alter system flush shared_pool;

System altered.

SQL>
SQL> alter system flush shared_pool;

System altered.

SQL>
SQL> drop index plan_hash_value_test1_idx1;

Index dropped.

SQL>
SQL> create index test_user_plan_hash_value.plan_hash_value_test1_idx1 on plan_hash_value_test1 (id2, id1);

Index created.

SQL>
SQL> exec dbms_stats.set_index_stats('test_user_plan_hash_value', 'plan_hash_value_test1_idx1', numdist=>0, numrows=>0)

PL/SQL procedure successfully completed.

SQL>
SQL> select /*+ find_me
2 index(a, plan_hash_value_test1_idx1) */
3 *
4 from
5 plan_hash_value_test1 a
6 where
7 id2 = 1
8 and rownum <= 2;

ID1 ID2 SMALL_VC
---------- ---------- --------------------
1 1 x
3 1 x

SQL>
SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID dsjuqc6fkfffj, child number 0
-------------------------------------
select /*+ find_me index(a, plan_hash_value_test1_idx1) */
* from plan_hash_value_test1 a where id2 = 1
and rownum <= 2

Plan hash value: 2655295642

-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| PLAN_HASH_VALUE_TEST1 | 2 | 54 | 3 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | PLAN_HASH_VALUE_TEST1_IDX1 | 1 | | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(ROWNUM<=2)
3 - access("ID2"=1)

23 rows selected.

SQL>
SQL> select
2 object#, object_owner, object_name
3 from
4 v$sql_plan
5 where
6 operation = 'INDEX'
7 and sql_id = (select
8 sql_id
9 from
10 v$sql
11 where
12 sql_text like 'select /*+ find_me%'
13 );

OBJECT# OBJECT_OWNER OBJECT_NAME
---------- ------------------------------ ------------------------------
76268 TEST_USER_PLAN_HASH_VALUE PLAN_HASH_VALUE_TEST1_IDX1

SQL>
SQL> pause

SQL>
SQL> drop index test_user_plan_hash_value.plan_hash_value_test1_idx1;

Index dropped.

SQL>
SQL> create index plan_hash_value_test1_idx1 on plan_hash_value_test1 (id2, id1);

Index created.

SQL>
SQL> select /*+ find_me
2 index(a, plan_hash_value_test1_idx1) */
3 *
4 from
5 plan_hash_value_test1 a
6 where
7 id2 = 1
8 and rownum <= 2;

ID1 ID2 SMALL_VC
---------- ---------- --------------------
1 1 x
3 1 x

SQL>
SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID dsjuqc6fkfffj, child number 0
-------------------------------------
select /*+ find_me index(a, plan_hash_value_test1_idx1) */
* from plan_hash_value_test1 a where id2 = 1
and rownum <= 2

Plan hash value: 2655295642

-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| PLAN_HASH_VALUE_TEST1 | 2 | 54 | 3 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | PLAN_HASH_VALUE_TEST1_IDX1 | 500 | | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(ROWNUM<=2)
3 - access("ID2"=1)

23 rows selected.

SQL>
SQL> select
2 object#, object_owner, object_name
3 from
4 v$sql_plan
5 where
6 operation = 'INDEX'
7 and sql_id = (select
8 sql_id
9 from
10 v$sql
11 where
12 sql_text like 'select /*+ find_me%'
13 );

OBJECT# OBJECT_OWNER OBJECT_NAME
---------- ------------------------------ ------------------------------
76269 CBO_TEST PLAN_HASH_VALUE_TEST1_IDX1

SQL>

Note the subtle differences: We have an index on a simple table, but these are actually three different objects, once owned by a different user and once with a different definition, but all three statements get the same PLAN_HASH_VALUE.

So this simple example already raises some of the most important points:

- The OBJECT_ID of an object obviously doesn't get used for the PLAN_HASH_VALUE calculation. Although present in the V$SQL_PLAN view, checking the PLAN_TABLE definition suggests why: It's not part of the PLAN_TABLE definition. This means that e.g. replacing an index with a different one but keeping the name will result in the same PLAN_HASH_VALUE if the operations and their order remain the same, like demonstrated (Although I have to admit that doing so might be called bad practice).

- The owner of an object doesn't get used either. Why this is so, remains unclear, since it seems to be part of all related object definitions (OBJECT_OWNER attribute in PLAN_TABLE/V$SQL_PLAN), but might be an very important point if you have multiple schemas with objects of the same name but representing different data or data volume. As you can see from the example the PLAN_HASH_VALUE is the same but the number of rows estimated is different due to the different underlying object (and its intentionally manipulated statistics).

- One of the most crucial aspects is also demonstrated: The FILTER_PREDICATES and ACCESS_PREDICATES information is NOT part of the PLAN_HASH_VALUE. This means that two executions plans can have the same PLAN_HASH_VALUE but behave significantly differently at actual execution time.

- What also is obvious from this example is that the ROWS and BYTES information is not used as part of the PLAN_HASH_VALUE. More on this later.

Let's move on to the next example:

SQL>
SQL> variable b2 number
SQL>
SQL> exec :b2 := 1;

PL/SQL procedure successfully completed.

SQL>
SQL> select /*+ find_me
2 index(a, plan_hash_value_test1_idx1) */
3 *
4 from
5 plan_hash_value_test1 a
6 where
7 id2 = :b2
8 and rownum <= 1;

ID1 ID2 SMALL_VC
---------- ---------- --------------------
1 1 x

SQL>
SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID 4y6b2pu1hzs3h, child number 0
-------------------------------------
select /*+ find_me index(a, plan_hash_value_test1_idx1) */
* from plan_hash_value_test1 a where id2 = :b2
and rownum <= 1

Plan hash value: 2655295642

-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| PLAN_HASH_VALUE_TEST1 | 1 | 27 | 3 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | PLAN_HASH_VALUE_TEST1_IDX1 | 500 | | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(ROWNUM<=1)
3 - access("ID2"=:B2)

23 rows selected.

SQL>
SQL> variable b2 varchar2(20)
SQL>
SQL> exec :b2 := '1';

PL/SQL procedure successfully completed.

SQL>
SQL> select /*+ find_me
2 index(a, plan_hash_value_test1_idx1) */
3 *
4 from
5 plan_hash_value_test1 a
6 where
7 id2 = :b2
8 and rownum <= 1;

ID1 ID2 SMALL_VC
---------- ---------- --------------------
1 1 x

SQL>
SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID 4y6b2pu1hzs3h, child number 1
-------------------------------------
select /*+ find_me index(a, plan_hash_value_test1_idx1) */
* from plan_hash_value_test1 a where id2 = :b2
and rownum <= 1

Plan hash value: 2655295642

-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| PLAN_HASH_VALUE_TEST1 | 1 | 27 | 3 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | PLAN_HASH_VALUE_TEST1_IDX1 | 500 | | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(ROWNUM<=1)
3 - access("ID2"=TO_NUMBER(:B2))

23 rows selected.

SQL>

Whereas the previous examples mainly used different SQLs (and therefore had different SQL_HASH_VALUEs or SQL_IDs) this example uses the same SQL and demonstrates the following:

- Bad application behaviour (in this case different types of bind variables) or other reasons can lead to unshared cursors, i.e. multiple child cursors for the same SQL (not actually the main topic here)

- But although the different cursors have different access predicates (in this case the implicit type conversion), as already shown, different access predicates don't lead to different PLAN_HASH_VALUEs

For this particular statement the difference in the predicates very likely doesn't represent a threat, but there are more subtle cases where these differences can lead to significant changes in behaviour. Possible reasons are different order of the predicate evaluation (e.g. when having system statistics enabled, which is the default from 10g on) which can make a significant difference in CPU usage or general resource consumption depending on the actual cost of the predicate evaluation (e.g. a costly PL/SQL function call), or even the evaluation of (filter) predicates at different steps of the same execution plan which could lead to significant differences in the number of rows generated by each operation step of the execution plan, and therefore make an execution plan much more inefficient due to the larger number of rows processed.

What about the actual estimates associated with the particular operations of an execution plan?

SQL>
SQL> explain plan for
2 select /*+ full(a) */
3 *
4 from
5 plan_hash_value_test1 a
6 where
7 id2 = 1;

Explained.

SQL>
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3758120161

-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 500 | 13500 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| PLAN_HASH_VALUE_TEST1 | 500 | 13500 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("ID2"=1)

13 rows selected.

SQL>
SQL> exec dbms_stats.set_table_stats(null, 'plan_hash_value_test1', numrows=>100000, numblks=>10000)

PL/SQL procedure successfully completed.

SQL>
SQL> explain plan for
2 select /*+ full(a) */
3 *
4 from
5 plan_hash_value_test1 a
6 where
7 id2 = 1;

Explained.

SQL>
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3758120161

-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50000 | 1318K| 2733 (1)| 00:00:33 |
|* 1 | TABLE ACCESS FULL| PLAN_HASH_VALUE_TEST1 | 50000 | 1318K| 2733 (1)| 00:00:33 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("ID2"=1)

13 rows selected.

SQL>

It's obvious that none of the ROWS, BYTES, COST nor derived information like TIME in the later releases are used to calculate the PLAN_HASH_VALUE. So again this makes clear that the same PLAN_HASH_VALUE of two statements doesn't say anything about the similarity of the runtime performance.

Another example that demonstrates this point with a slightly more complex plan:

SQL>
SQL> exec dbms_stats.gather_table_stats(null, 'plan_hash_value_test1')

PL/SQL procedure successfully completed.

SQL>
SQL> create table plan_hash_value_test2
2 as
3 select
4 id as id1
5 , mod(id, 2) as id2
6 , rpad('x', 20) as small_vc
7 from
8 (
9 select
10 level as id
11 from
12 dual
13 connect by
14 level <= 1000
15 );

Table created.

SQL>
SQL> exec dbms_stats.gather_table_stats(null, 'plan_hash_value_test2')

PL/SQL procedure successfully completed.

SQL>
SQL> create index plan_hash_value_test2_idx1 on plan_hash_value_test2 (id2, id1);

Index created.

SQL>
SQL> explain plan for
2 select /*+
3 use_nl(a, b)
4 index(b)
5 leading(a)
6 */
7 *
8 from
9 plan_hash_value_test1 a
10 , plan_hash_value_test2 b
11 where
12 a.id1 = b.id1
13 and a.id2 = b.id2
14 and a.id1 between 1 and 10;

Explained.

SQL>
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 458854847

-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 486 | 23 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 9 | 486 | 23 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | PLAN_HASH_VALUE_TEST1 | 10 | 270 | 3 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | PLAN_HASH_VALUE_TEST2_IDX1 | 1 | | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| PLAN_HASH_VALUE_TEST2 | 1 | 27 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - filter("A"."ID1"<=10 AND "A"."ID1">=1)
4 - access("A"."ID2"="B"."ID2" AND "A"."ID1"="B"."ID1")
filter("B"."ID1"<=10 AND "B"."ID1">=1)

19 rows selected.

SQL>
SQL> explain plan for
2 select /*+
3 use_nl(a, b)
4 index(b)
5 leading(a)
6 */
7 *
8 from
9 plan_hash_value_test1 a
10 , plan_hash_value_test2 b
11 where
12 a.id2 = b.id2
13 and a.id1 between 1 and 10;

Explained.

SQL>
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 458854847

-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5005 | 263K| 74 (2)| 00:00:01 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 5005 | 263K| 74 (2)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | PLAN_HASH_VALUE_TEST1 | 10 | 270 | 3 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | PLAN_HASH_VALUE_TEST2_IDX1 | 500 | | 2 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| PLAN_HASH_VALUE_TEST2 | 500 | 13500 | 7 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - filter("A"."ID1"<=10 AND "A"."ID1">=1)
4 - access("A"."ID2"="B"."ID2")

18 rows selected.

SQL>

Notice how one statement performs an effective join using appropriate join predicates whereas the other one generates duplicate records. Again, since the filter and access predicates are not evaluated, these two plans get the same PLAN_HASH_VALUE, although we can say that one of them is potentially suboptimal (and usually would result in a significantly different execution plan which in turn would have different PLAN_HASH_VALUEs but there are certainly more complex scenarios where the optimizer goes wrong for whatever reason resulting in such potentially inefficient execution plans).

What about extended execution plan information like partitioning and parallel execution?

Let's first address partitioning:

SQL> create table plan_hash_value_test3
2 (
3 invoice_no number,
4 sale_year integer not null,
5 sale_month integer not null,
6 sale_day integer not null
7 )
8 partition by range (invoice_no)
9 (
10 partition part_001 values less than (100),
11 partition part_002 values less than (400),
12 partition part_003 values less than (800),
13 partition part_004 values less than (maxvalue)
14 );

Table created.

SQL>
SQL> exec dbms_random.seed(0)

PL/SQL procedure successfully completed.

SQL>
SQL> insert into plan_hash_value_test3 (
2 invoice_no,
3 sale_year,
4 sale_month,
5 sale_day
6 )
7 select rownum,
8 2000 + round(dbms_random.value(0, 8)) as sale_year,
9 trunc(dbms_random.value(1, 13)) as sale_month,
10 trunc(dbms_random.value(1, 29)) as sale_day
11 from dual
12 connect by level <= 1000;

1000 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> exec dbms_stats.gather_table_stats(null, 'plan_hash_value_test3')

PL/SQL procedure successfully completed.

SQL>
SQL> exec dbms_stats.set_table_stats(null, 'plan_hash_value_test3', partname=>'part_002', numblks=>100000)

PL/SQL procedure successfully completed.

SQL>
SQL> select
2 partition_name
3 , blocks
4 from
5 user_tab_statistics
6 where
7 table_name = 'PLAN_HASH_VALUE_TEST3';

PARTITION_NAME BLOCKS
------------------------------ ----------
5
PART_001 1
PART_002 100000
PART_003 2
PART_004 1

SQL>
SQL> explain plan for
2 select
3 *
4 from
5 plan_hash_value_test3
6 where
7 invoice_no < 100;

Explained.

SQL>
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 4079248530

----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 99 | 1188 | 2 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE| | 99 | 1188 | 2 (0)| 00:00:01 | 1 | 1 |
| 2 | TABLE ACCESS FULL | PLAN_HASH_VALUE_TEST3 | 99 | 1188 | 2 (0)| 00:00:01 | 1 | 1 |
----------------------------------------------------------------------------------------------------------------

9 rows selected.

SQL>
SQL> explain plan for
2 select
3 *
4 from
5 plan_hash_value_test3
6 where
7 invoice_no >= 100 and invoice_no < 400;

Explained.

SQL>
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 4079248530

----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 300 | 3900 | 27256 (1)| 00:05:28 | | |
| 1 | PARTITION RANGE SINGLE| | 300 | 3900 | 27256 (1)| 00:05:28 | 2 | 2 |
| 2 | TABLE ACCESS FULL | PLAN_HASH_VALUE_TEST3 | 300 | 3900 | 27256 (1)| 00:05:28 | 2 | 2 |
----------------------------------------------------------------------------------------------------------------

9 rows selected.

SQL>
SQL> variable b1 number
SQL> variable b2 number
SQL>
SQL> exec :b1 := 0;

PL/SQL procedure successfully completed.

SQL> exec :b2 := 100;

PL/SQL procedure successfully completed.

SQL>
SQL> select
2 count(*)
3 from
4 plan_hash_value_test3
5 where
6 invoice_no >= :b1 and invoice_no < :b2;

COUNT(*)
----------
99

SQL>
SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID 23m1jk6dbsvbx, child number 0
-------------------------------------
select count(*) from plan_hash_value_test3 where
invoice_no >= :b1 and invoice_no < :b2

Plan hash value: 2447373661

--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| | | |
| 1 | SORT AGGREGATE | | 1 | 3 | | | | |
|* 2 | FILTER | | | | | | | |
| 3 | PARTITION RANGE ITERATOR| | 99 | 297 | 2 (0)| 00:00:01 | KEY | KEY |
|* 4 | TABLE ACCESS FULL | PLAN_HASH_VALUE_TEST3 | 99 | 297 | 2 (0)| 00:00:01 | KEY | KEY |
--------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter(:B1<:B2)
4 - filter(("INVOICE_NO">=:B1 AND "INVOICE_NO"<:B2))

23 rows selected.

SQL>
SQL> alter system flush shared_pool;

System altered.

SQL>
SQL> alter system flush shared_pool;

System altered.

SQL>
SQL> exec :b1 := 100;

PL/SQL procedure successfully completed.

SQL> exec :b2 := 400;

PL/SQL procedure successfully completed.

SQL>
SQL> select
2 count(*)
3 from
4 plan_hash_value_test3
5 where
6 invoice_no >= :b1 and invoice_no < :b2;

COUNT(*)
----------
300

SQL>
SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID 23m1jk6dbsvbx, child number 0
-------------------------------------
select count(*) from plan_hash_value_test3 where
invoice_no >= :b1 and invoice_no < :b2

Plan hash value: 2447373661

--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 27256 (100)| | | |
| 1 | SORT AGGREGATE | | 1 | 4 | | | | |
|* 2 | FILTER | | | | | | | |
| 3 | PARTITION RANGE ITERATOR| | 300 | 1200 | 27256 (1)| 00:05:28 | KEY | KEY |
|* 4 | TABLE ACCESS FULL | PLAN_HASH_VALUE_TEST3 | 300 | 1200 | 27256 (1)| 00:05:28 | KEY | KEY |
--------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter(:B1<:B2)
4 - filter(("INVOICE_NO">=:B1 AND "INVOICE_NO"<:B2))

23 rows selected.

SQL>

The PSTART and PSTOP information is not used either for the PLAN_HASH_VALUE, so different execution plans accessing different partitions of the same object might get the same PLAN_HASH_VALUE, but again the runtime performance might be dramatically different.

By the way above example demonstrates that the CBO peeks at the binds and uses then the partition level statistics of the corresponding partition defined by the bind values (in case the bind values prune to a single partition), although the execution plan might be executed with different bind values actually accessing different partitions at runtime.

What about parallel execution?

SQL>
SQL> explain plan for
2 select /*+
3 full(a)
4 full(b)
5 use_hash(a, b)
6 parallel(a)
7 parallel(b)
8 pq_distribute(b, none, broadcast)
9 */
10 *
11 from
12 plan_hash_value_test1 a
13 , plan_hash_value_test2 b
14 where
15 a.id1 = b.id1
16 and a.id2 = b.id2
17 and a.id1 between 1 and 10;

Explained.

SQL>
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1365899609

-------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 486 | 5 (20)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 9 | 486 | 5 (20)| 00:00:01 | Q1,01 | P->S | QC (RAND) |
|* 3 | HASH JOIN | | 9 | 486 | 5 (20)| 00:00:01 | Q1,01 | PCWP | |
| 4 | PX BLOCK ITERATOR | | 10 | 270 | 2 (0)| 00:00:01 | Q1,01 | PCWC | |
|* 5 | TABLE ACCESS FULL | PLAN_HASH_VALUE_TEST1 | 10 | 270 | 2 (0)| 00:00:01 | Q1,01 | PCWP | |
| 6 | BUFFER SORT | | | | | | Q1,01 | PCWC | |
| 7 | PX RECEIVE | | 10 | 270 | 2 (0)| 00:00:01 | Q1,01 | PCWP | |
| 8 | PX SEND BROADCAST | :TQ10000 | 10 | 270 | 2 (0)| 00:00:01 | Q1,00 | P->P | BROADCAST |
| 9 | PX BLOCK ITERATOR | | 10 | 270 | 2 (0)| 00:00:01 | Q1,00 | PCWC | |
|* 10 | TABLE ACCESS FULL| PLAN_HASH_VALUE_TEST2 | 10 | 270 | 2 (0)| 00:00:01 | Q1,00 | PCWP | |
-------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("A"."ID1"="B"."ID1" AND "A"."ID2"="B"."ID2")
5 - filter("A"."ID1"<=10 AND "A"."ID1">=1)
10 - filter("B"."ID1"<=10 AND "B"."ID1">=1)

24 rows selected.

SQL>
SQL> explain plan for
2 select /*+
3 full(a)
4 full(b)
5 use_hash(a, b)
6 parallel(a)
7 parallel(b)
8 pq_distribute(b, broadcast, none)
9 */
10 *
11 from
12 plan_hash_value_test1 a
13 , plan_hash_value_test2 b
14 where
15 a.id1 = b.id1
16 and a.id2 = b.id2
17 and a.id1 between 1 and 10;

Explained.

SQL>
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3903716067

------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 486 | 5 (20)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 9 | 486 | 5 (20)| 00:00:01 | Q1,01 | P->S | QC (RAND) |
|* 3 | HASH JOIN | | 9 | 486 | 5 (20)| 00:00:01 | Q1,01 | PCWP | |
| 4 | PX RECEIVE | | 10 | 270 | 2 (0)| 00:00:01 | Q1,01 | PCWP | |
| 5 | PX SEND BROADCAST | :TQ10000 | 10 | 270 | 2 (0)| 00:00:01 | Q1,00 | P->P | BROADCAST |
| 6 | PX BLOCK ITERATOR | | 10 | 270 | 2 (0)| 00:00:01 | Q1,00 | PCWC | |
|* 7 | TABLE ACCESS FULL| PLAN_HASH_VALUE_TEST1 | 10 | 270 | 2 (0)| 00:00:01 | Q1,00 | PCWP | |
| 8 | PX BLOCK ITERATOR | | 10 | 270 | 2 (0)| 00:00:01 | Q1,01 | PCWC | |
|* 9 | TABLE ACCESS FULL | PLAN_HASH_VALUE_TEST2 | 10 | 270 | 2 (0)| 00:00:01 | Q1,01 | PCWP | |
------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("A"."ID1"="B"."ID1" AND "A"."ID2"="B"."ID2")
7 - filter("A"."ID1"<=10 AND "A"."ID1">=1)
9 - filter("B"."ID1"<=10 AND "B"."ID1">=1)

23 rows selected.

SQL>
SQL> explain plan for
2 select /*+
3 full(a)
4 full(b)
5 use_hash(a, b)
6 parallel(a)
7 parallel(b)
8 pq_distribute(b, hash, hash)
9 */
10 *
11 from
12 plan_hash_value_test1 a
13 , plan_hash_value_test2 b
14 where
15 a.id1 = b.id1
16 and a.id2 = b.id2
17 and a.id1 between 1 and 10;

Explained.

SQL>
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 904614956

------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 486 | 5 (20)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10002 | 9 | 486 | 5 (20)| 00:00:01 | Q1,02 | P->S | QC (RAND) |
|* 3 | HASH JOIN BUFFERED | | 9 | 486 | 5 (20)| 00:00:01 | Q1,02 | PCWP | |
| 4 | PX RECEIVE | | 10 | 270 | 2 (0)| 00:00:01 | Q1,02 | PCWP | |
| 5 | PX SEND HASH | :TQ10000 | 10 | 270 | 2 (0)| 00:00:01 | Q1,00 | P->P | HASH |
| 6 | PX BLOCK ITERATOR | | 10 | 270 | 2 (0)| 00:00:01 | Q1,00 | PCWC | |
|* 7 | TABLE ACCESS FULL| PLAN_HASH_VALUE_TEST1 | 10 | 270 | 2 (0)| 00:00:01 | Q1,00 | PCWP | |
| 8 | PX RECEIVE | | 10 | 270 | 2 (0)| 00:00:01 | Q1,02 | PCWP | |
| 9 | PX SEND HASH | :TQ10001 | 10 | 270 | 2 (0)| 00:00:01 | Q1,01 | P->P | HASH |
| 10 | PX BLOCK ITERATOR | | 10 | 270 | 2 (0)| 00:00:01 | Q1,01 | PCWC | |
|* 11 | TABLE ACCESS FULL| PLAN_HASH_VALUE_TEST2 | 10 | 270 | 2 (0)| 00:00:01 | Q1,01 | PCWP | |
------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("A"."ID1"="B"."ID1" AND "A"."ID2"="B"."ID2")
7 - filter("A"."ID1"<=10 AND "A"."ID1">=1)
11 - filter("B"."ID1"<=10 AND "B"."ID1">=1)

25 rows selected.

SQL>

The example uses different parallel distribution options for the same execution plan. In 10g and later this is reflected in different operations (like PX SEND BROADCAST) and this suggests that the PLAN_HASH_VALUEs are going to be different due to the different operations.

Running a similar test case on 9.2.0.8 shows it seems that actually some of the attributes related to parallel execution are also used to calculate the PLAN_HASH_VALUE:

SQL>
SQL> alter system flush shared_pool;

System altered.

SQL>
SQL> alter system flush shared_pool;

System altered.

SQL>
SQL> explain plan for
2 select /*+ find_me
3 full(a)
4 full(b)
5 use_hash(a, b)
6 parallel(a)
7 parallel(b)
8 pq_distribute(b, none, broadcast)
9 */
10 *
11 from
12 plan_hash_value_test1 a
13 , plan_hash_value_test2 b
14 where
15 a.id1 = b.id1
16 and a.id2 = b.id2
17 and a.id1 between 1 and 10;

Explained.

SQL>
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 270 | 6 (34)| | | |
|* 1 | HASH JOIN | | 5 | 270 | 6 (34)| 55,01 | P->S | QC (RAND) |
|* 2 | TABLE ACCESS FULL | PLAN_HASH_VALUE_TEST1 | 10 | 270 | 3 (34)| 55,01 | PCWP | |
|* 3 | TABLE ACCESS FULL | PLAN_HASH_VALUE_TEST2 | 10 | 270 | 3 (34)| 55,00 | P->P | BROADCAST |
-----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("A"."ID1"="B"."ID1" AND "A"."ID2"="B"."ID2")
2 - filter("A"."ID1"<=10 AND "A"."ID1">=1)
3 - filter("B"."ID1"<=10 AND "B"."ID1">=1)

16 rows selected.

SQL>
SQL> rollback;

Rollback complete.

SQL>
SQL> select /*+ find_me
2 full(a)
3 full(b)
4 use_hash(a, b)
5 parallel(a)
6 parallel(b)
7 pq_distribute(b, none, broadcast)
8 */
9 *
10 from
11 plan_hash_value_test1 a
12 , plan_hash_value_test2 b
13 where
14 a.id1 = b.id1
15 and a.id2 = b.id2
16 and a.id1 between 1 and 10;

ID1 ID2 SMALL_VC ID1 ID2 SMALL_VC
---------- ---------- -------------------- ---------- ---------- --------------------
1 1 x 1 1 x
2 0 x 2 0 x
3 1 x 3 1 x
4 0 x 4 0 x
5 1 x 5 1 x
6 0 x 6 0 x
7 1 x 7 1 x
8 0 x 8 0 x
9 1 x 9 1 x
10 0 x 10 0 x

10 rows selected.

SQL>
SQL> select
2 plan_hash_value
3 , hash_value
4 from
5 v$sql
6 where
7 sql_text like 'select /*+ find_me%';

PLAN_HASH_VALUE HASH_VALUE
--------------- ----------
1709875781 508205717

SQL>
SQL> alter system flush shared_pool;

System altered.

SQL>
SQL> alter system flush shared_pool;

System altered.

SQL>
SQL> explain plan for
2 select /*+ find_me
3 full(a)
4 full(b)
5 use_hash(a, b)
6 parallel(a)
7 parallel(b)
8 pq_distribute(b, broadcast, none)
9 */
10 *
11 from
12 plan_hash_value_test1 a
13 , plan_hash_value_test2 b
14 where
15 a.id1 = b.id1
16 and a.id2 = b.id2
17 and a.id1 between 1 and 10;

Explained.

SQL>
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 270 | 6 (34)| | | |
|* 1 | HASH JOIN | | 5 | 270 | 6 (34)| 57,01 | P->S | QC (RAND) |
|* 2 | TABLE ACCESS FULL | PLAN_HASH_VALUE_TEST1 | 10 | 270 | 3 (34)| 57,00 | P->P | BROADCAST |
|* 3 | TABLE ACCESS FULL | PLAN_HASH_VALUE_TEST2 | 10 | 270 | 3 (34)| 57,01 | PCWP | |
-----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("A"."ID1"="B"."ID1" AND "A"."ID2"="B"."ID2")
2 - filter("A"."ID1"<=10 AND "A"."ID1">=1)
3 - filter("B"."ID1"<=10 AND "B"."ID1">=1)

16 rows selected.

SQL>
SQL> rollback;

Rollback complete.

SQL>
SQL> select /*+ find_me
2 full(a)
3 full(b)
4 use_hash(a, b)
5 parallel(a)
6 parallel(b)
7 pq_distribute(b, broadcast, none)
8 */
9 *
10 from
11 plan_hash_value_test1 a
12 , plan_hash_value_test2 b
13 where
14 a.id1 = b.id1
15 and a.id2 = b.id2
16 and a.id1 between 1 and 10;

ID1 ID2 SMALL_VC ID1 ID2 SMALL_VC
---------- ---------- -------------------- ---------- ---------- --------------------
1 1 x 1 1 x
2 0 x 2 0 x
3 1 x 3 1 x
4 0 x 4 0 x
5 1 x 5 1 x
6 0 x 6 0 x
7 1 x 7 1 x
8 0 x 8 0 x
9 1 x 9 1 x
10 0 x 10 0 x

10 rows selected.

SQL>
SQL> select
2 plan_hash_value
3 , hash_value
4 from
5 v$sql
6 where
7 sql_text like 'select /*+ find_me%';

PLAN_HASH_VALUE HASH_VALUE
--------------- ----------
441284116 3983849749

SQL>
SQL> alter system flush shared_pool;

System altered.

SQL>
SQL> alter system flush shared_pool;

System altered.

SQL>
SQL> explain plan for
2 select /*+ find_me
3 full(a)
4 full(b)
5 use_hash(a, b)
6 parallel(a)
7 parallel(b)
8 pq_distribute(b, hash, hash)
9 */
10 *
11 from
12 plan_hash_value_test1 a
13 , plan_hash_value_test2 b
14 where
15 a.id1 = b.id1
16 and a.id2 = b.id2
17 and a.id1 between 1 and 10;

Explained.

SQL>
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 270 | 6 (34)| | | |
|* 1 | HASH JOIN | | 5 | 270 | 6 (34)| 59,02 | P->S | QC (RAND) |
|* 2 | TABLE ACCESS FULL | PLAN_HASH_VALUE_TEST1 | 10 | 270 | 3 (34)| 59,00 | P->P | HASH |
|* 3 | TABLE ACCESS FULL | PLAN_HASH_VALUE_TEST2 | 10 | 270 | 3 (34)| 59,01 | P->P | HASH |
-----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("A"."ID1"="B"."ID1" AND "A"."ID2"="B"."ID2")
2 - filter("A"."ID1"<=10 AND "A"."ID1">=1)
3 - filter("B"."ID1"<=10 AND "B"."ID1">=1)

16 rows selected.

SQL>
SQL> rollback;

Rollback complete.

SQL>
SQL> select /*+ find_me
2 full(a)
3 full(b)
4 use_hash(a, b)
5 parallel(a)
6 parallel(b)
7 pq_distribute(b, hash, hash)
8 */
9 *
10 from
11 plan_hash_value_test1 a
12 , plan_hash_value_test2 b
13 where
14 a.id1 = b.id1
15 and a.id2 = b.id2
16 and a.id1 between 1 and 10;

ID1 ID2 SMALL_VC ID1 ID2 SMALL_VC
---------- ---------- -------------------- ---------- ---------- --------------------
1 1 x 1 1 x
3 1 x 3 1 x
5 1 x 5 1 x
8 0 x 8 0 x
10 0 x 10 0 x
2 0 x 2 0 x
4 0 x 4 0 x
6 0 x 6 0 x
7 1 x 7 1 x
9 1 x 9 1 x

10 rows selected.

SQL>
SQL> select
2 plan_hash_value
3 , hash_value
4 from
5 v$sql
6 where
7 sql_text like 'select /*+ find_me%';

PLAN_HASH_VALUE HASH_VALUE
--------------- ----------
353704519 1797852549

SQL>

Although the main operations and their order of execution stays the same, the calculated PLAN_HASH_VALUE is different in all three cases.

So in summary the following conclusions can be made:

- The same PLAN_HASH_VALUE is merely an indicator that the same operations on objects of the same name are performed in the same order.

- It tells nothing about the similarity of the expected runtime performance of the execution plan, due to various reasons as demonstrated. The most significant information that is not covered by the PLAN_HASH_VALUE are the filter and access predicates, but there are other attributes, too, that are not part of the hash value calculation.

- Still it's very likely that for the same SQL statement and the same unchanged underlying data the same PLAN_HASH_VALUE indicates similar expected runtime performance, except for some particular cases where the execution plan itself already contains "conditional" execution paths which might be taken by the runtime engine and therefore lead to significantly different runtimes. Some of these examples have already been covered by Jonathan Lewis: Conditional Plan and Hierarchical Queries in some versions of Oracle.

In the second part I'll demonstrate a possible approach how to calculate your own PLAN_HASH_VALUE that covers some of the information omitted if you suspect that you might have encountered a scenario where the same PLAN_HASH_VALUE suggests similar runtime performance but there are significant differences in the execution plans that you would like to have covered by the hash value calculation to detect these plan changes easily.

Dynamic sampling and partitioned tables

------------------------------------------------------------------------------

Update January 2010: A thread on OTN mentioned this blog post and another, actually contradicting blog post by Asif Momen.

So why are these two blog posts coming to different conclusions regarding Dynamic Sampling and partitions with missing statistics?

This is the good thing with documented test cases - I reproduced what Asif has done and found out that the significant difference between these two test cases is the existence of global level statistics.

In my test case below, I have explicitly gathered statistics only on partition level, and there are no statistics on global level/table (which can be seen from the output of the query against user_tab_statistics below).

Asif has actually gathered statistics on global/table level which can be seen from his blog post.

So the conclusion seems to be: If you prune to a single partition, but this partition has no statistics, then Dynamic sampling will be used if no global/table level statistics are available. If global/table level statistics are available, the optimizer won't perform dynamic sampling and revert to these global/table level statistics instead.

Oddly this obviously doesn't apply to the subpartition/partition level case: Repeating a similar setup with subpartitions having no statistics, but statistics on partition level are available, Dynamic Sampling still was used (tested on 11.1.0.7 Win32).

------------------------------------------------------------------------------

User objects created in the SYS schema and the (cost based) optimizer

The answer - as already disclosed by Nicolas Gasparotto - to the question that I asked here in the "Weekend Quiz" is to run the script as SYS user, and then run the query shown against these objects in the SYS schema (tested against 10g XE, 10.2.0.4 and 11.1.0.7 on Win32).

Note: It's not recommended to create any non-SYS objects in the SYS schema and you should only perform this (if at all) in a test database.

All this came up in this recent OTN forum thread where it became obvious that the issue can only be reproduced if the objects are owned by SYS.

There are two interesting points to derive from this (apart from the obvious that one should not create any user objects in the SYS schema):

1. The optimizer seems to treat objects owned by SYS differently, in particular regarding the transformations applied. Note that the crucial point is not that the query is executed as SYS user, but that the objects are owned by the SYS user. Granting appropriate privileges to a non-SYS user on the objects owned by SYS allows to reproduce the issue even with a non-SYS user.

2. It's something to remind if there is the need to understand a performance issue with a recursive dictionary query performed on SYS-owned objects. Although you obviously can't influence the SQL generated by Oracle itself it might help to understand the issue and take appropriate steps to rectify the issue.

Oh, by the way, have I already mentioned that it's really a bad idea to create user objects in the SYS schema?

Understanding the different modes of System Statistics aka. CPU Costing and the effects of multiple blocksizes - part 4

Back to part 3

Using objects residing in multiple blocksizes

I've already mentioned it several times on my blog but I would like to take the chance here again to stress the point that the cost based optimizer does a bad job when it comes to calculating costs for full table scans of objects residing in non-default block sizes. It really looks like that this feature has been introduced to support transportable tablespaces but it obviously hasn't been tested very thoroughly when it comes to cost calculation.

Each of the different modes has its deficiencies when dealing with objects in non-default blocksizes. The somehow odd thing is that the traditional I/O costing does the best job, and all system statistics based calculations are utterly wrong.

Traditional I/O based costing

The traditional I/O based costing simply scales the MBRC up or down according to the non-default blocksize to come to the same I/O read request size. So if you e.g. have a MBRC of 8 and a default blocksize of 8KB and now calculate the cost for an object residing in a 2KB tablespace, the MBRC will be multiplied 4, which results in a MBRC of 32. The I/O cost will be different although due to the different adjustment used with the higher MBRC setting. The adjusted MBRC for 32 is 16.39 whereas the adjusted MBRC for 8 is 6.59, so the calculated cost for the full table scan of the object residing in the 2KB tablespace will be higher. Likewise the same happens when using an object in a 16KB non-default tablespace. The MBRC will be reduced accordingly to 4 to get the same I/O read size again. Since adjusted MBRC for MBRC = 4 is 4.17, the cost calculated will actually be less for the object residing the 16KB tablespace.

Weekend quiz

Quiz questions seem to be in nowadays, so here's one for you regarding the cost based optimizer:

Given this simple script (Run this on any 10.2.x (including XE) or 11.1.0.6/7):

create table test1
as
select * from dba_objects;

create table test2
as
select * from dba_objects;

create index i_test1_1 on test1(object_name);

create index i_test1_2 on test1(object_id);

create index i_test2_1 on test2(object_name);

create index i_test2_2 on test2(object_id);

exec dbms_stats.gather_table_stats(null, 'TEST1', estimate_percent=>null, method_opt=>'for all columns size 1');

exec dbms_stats.gather_table_stats(null, 'TEST2', estimate_percent=>null, method_opt=>'for all columns size 1');

And this simple query:

explain plan for
select
*
from
test1
where
object_name='TEST1'
and object_id in (
select /*+ unnest */
object_id
from
test2
);

with this plan:

Plan 1 (the obvious one):

Plan hash value: 2107173885

------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 212 | 6 (0)| 00:00:01 |
| 1 | NESTED LOOPS SEMI | | 2 | 212 | 6 (0)| 00:00:01 |

Understanding the different modes of System Statistics aka. CPU Costing and the effects of multiple blocksizes - part 3

Back to part 2 Forward to part 4

System statistics in 9i

In 10g the CPU costing mode is enabled by default and is supported by the default NOWORKLOAD system statistics.

But you can use system statistics already in 9i, although you have to enable them explicitly.

Oracle 9i is out of support, but I guess there are still a lot of systems out there that are using the 9.2 release, therefore I find it worth to mention what you can do in 9i with system statistics. This can be helpful if you consider to test your application already in the 9i environment with system statistics before upgrading to 10g.

In most descriptions about 9i and system statistics only WORKLOAD system statistics are mentioned, but 9i also supports NOWORKLOAD system statistics, although not in the same full flavour as 10g does.

You can activate CPU costing in 9i by running DBMS_STATS.GATHER_SYSTEM_STATS('NOWORKLOAD'), but this seems to work differently than in 10g and later.

Whereas in 10g and later this actually measures the IOSEEKTIM and IOTFRSPEED values, this seems to activate in 9i something that is comparable with the default NOWORKLOAD system statistics of 10g.

The SYS.AUX_STATS$ does not show any actual values, but tests revealed that 9i (at least 9.2.0.8) in that case seems to measure the CPU speed (in 10g this is the CPUSPEEDNW value) and uses the same default values for IOSEEKTIM and IOTFRSPEED as 10g does (10ms and 4096 bytes/ms resp.).

Running some tests showed that you arrive at the same I/O cost as you do in 10g with the default NOWORKLOAD system statistics.

exec dbms_stats.delete_system_stats;

Understanding the different modes of System Statistics aka. CPU Costing and the effects of multiple blocksizes - part 2

Back to part 1 Forward to part 3

Before heading on to the remaining modes of system statistics, let's summarize what has been observed in part 1 regarding the default NOWORKLOAD system statistics in 10g and later. The following table shows what the test case from the previous post demonstrated:

Table 1: 8KB MSSM locally managed tablespace 10,000 blocks table segment
default NOWORKLOAD system statistics:

MBRC|SREADTIM|MREADTIM|MREADTIM/SREADTIM|NOCPU cost|adjusted MBRC|CPU cost|CPU/NOCPU cost
----|--------|--------|-----------------|----------|-------------|--------|--------------
8 |12 | 26 | 2.16 |1,518 | 6.59 |2,709 |1.78
16 |12 | 42 | 3.5 | 962 |10.39 |2,188 |2.27
32 |12 | 74 | 6.16 | 610 |16.39 |1,928 |3.16
64 |12 |138 |11.5 | 387 |25.84 |1,798 |4.64
128 |12 |266 |22.16 | 245 |40.82 |1,732 |7.07

If you happen to have a 16KB default blocksize the results would look like the following. Note that the table is now only 5,000 blocks in size, and the SREADTIM is now a bit longer (10+16384/4096=14ms instead of 10+8192/4096=12ms) therefore the 16KB blocksize calculation makes the full table scan look a bit cheaper to the optimizer when using the default NOWORKLOAD system statistics.

Table 2: 16KB MSSM locally managed tablespace 5,000 blocks table segment
default NOWORKLOAD system statistics:

MBRC|SREADTIM|MREADTIM|MREADTIM/SREADTIM|NOCPU cost|adjusted MBRC|CPU cost|CPU/NOCPU cost

Understanding the different modes of System Statistics aka. CPU Costing and the effects of multiple blocksizes - part 1

Forward to part 2

This is the first part of a series of posts that cover one of the fundamentals of the cost based optimizer in 9i and later. Understanding how the different system statistics modes work is crucial in making the most out of the cost based optimizer, therefore I'll attempt to provide some detailed explanations and samples about the formulas and arithmetics used. Finally I'll show (again) that using multiple block sizes for "tuning" purposes is a bad idea in general, along with detailed examples why I think this is so.

One of the deficiencies of the traditional I/O based costing was that it simply counted the number of I/O requests making no differentation between single-block I/O and multi-block I/O.

System statistics were introduced in Oracle 9i to allow the cost based optimizer to take into account that single-block I/Os and multi-block I/Os should be treated differently in terms of costing and to include a CPU component in the cost calculation.

The system statistics tell the cost based optimizer (CBO) among other things the time it takes to perform a single block read request and a multi-block read request. Given this information the optimizer ought to be able to come to estimates that better fit the particular environment where the database is running on and additionally use an appropriate costing for multi-block read requests that usually take longer than single block read requests. Given the information about the time it takes to perform the read requests the cost calculated can be turned into a time estimate.

The cost calculated with system statistics is still expressed in the same units as with traditional I/O based costing, which is in units of single-block read requests.

Optimizer partition oddities, part 2: List partitioning

Back to part 1

Some time ago on the OTN forum the following table layout was part of a discussion regarding performance issues and it revealed an interesting anomaly regarding list partition pruning:

If you're using list partitioning with partitions that use multiple values that map to a single list partition then the optimizer obviously uses a questionable approach when you're using multiple values on the partition key to prune to a single partition.

Consider the following table layout:

CREATE TABLE XYZ
(
TICKER VARCHAR2(22 BYTE) NOT NULL,
EXCH_CODE VARCHAR2(25 BYTE) NOT NULL,
ID_ISIN VARCHAR2(12 BYTE),
HIGH_52WEEK NUMBER(28,10),
LOW_52WEEK NUMBER(28,10),
PX_OPEN NUMBER(28,10),
PX_HIGH NUMBER(28,10),
BLOOMBERG_FILE_SOURCE VARCHAR2(100 BYTE),
LATEST_VERSION_FLAG CHAR(1 BYTE)
)
PARTITION BY LIST (EXCH_CODE)
(
PARTITION BBO_ASIA VALUES ('SL','IS','SP','JF','JN','PK','KP','VM','JS','IN','TB','KQ','JP','NV','JJ','MK','HK','IJ','JT','TT','PA','CS','JX',
'IB','AU','FS','VN','NZ','KS','PM','CH','BD','JQ','VH','CG','JO')
,
PARTITION BBO_NAMR VALUES ('UO','US','UN','PQ','TR','UD','UP','TX','UL','UB','UU','UX','UT','TN','UQ','UR','UW','UV','TA','CT','CV','UC','CJ','UA','UM','CN','UF','CF')
,
PARTITION BBO_LAMR VALUES ('AR','BS','AC','CR','EG','EK','VB','BN','EQ','PE','AF','CX','KY','CC','MM','BM','TP','BV','BH','UY','BZ','ED','VC','VS','BO','CI','CB','PP','BA','JA','CE')
,
PARTITION BBO_EURO VALUES (DEFAULT)
);

I'm now going to populate that table using this sample data: