Friday, May 22, 2009

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 |
| 2 | TABLE ACCESS BY INDEX ROWID| TEST1 | 2 | 202 | 4 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | I_TEST1_1 | 2 | | 3 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | I_TEST2_2 | 69450 | 339K| 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------

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

3 - access("OBJECT_NAME"='TEST1')
4 - access("OBJECT_ID"="OBJECT_ID")


How is it possible to get the following different plans for the same statement in the same database with the same settings and using the same script:

Plan 2a (10.2.x):

Plan hash value: 2706897970

------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 99 | 53 (4)| 00:00:01 |
|* 1 | HASH JOIN SEMI | | 1 | 99 | 53 (4)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| TEST1 | 1 | 86 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | I_TEST1_1 | 1 | | 1 (0)| 00:00:01 |
| 4 | VIEW | VW_NSO_1 | 12685 | 161K| 50 (2)| 00:00:01 |
| 5 | TABLE ACCESS FULL | TEST2 | 12685 | 63425 | 50 (2)| 00:00:01 |
------------------------------------------------------------------------------------------

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

1 - access("OBJECT_ID"="$nso_col_1")
3 - access("OBJECT_NAME"='TEST1')


Plan 2b (11.1):

Plan hash value: 1356524942

------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 210 | 6 (0)| 00:00:01 |
| 1 | NESTED LOOPS SEMI | | 2 | 210 | 6 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| TEST1 | 2 | 202 | 4 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | I_TEST1_1 | 2 | | 3 (0)| 00:00:01 |
| 4 | VIEW PUSHED PREDICATE | VW_NSO_1 | 1 | 4 | 1 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | I_TEST2_2 | 1 | 5 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------

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

3 - access("OBJECT_NAME"='TEST1')
5 - access("OBJECT_ID"="OBJECT_ID")


Note 1: It's assumed that everything is equal that is (normally) considered by the cost based optimizer: Object statistics, System statistics, segment sizes, the actual data and optimizer related settings including all "underscore" parameters. It can be reproduced on the same database.

Hint: The solution should not be applied to a production system.

Note 2: And no, Timur, you're not allowed to participate, since you already know the answer!

1 comment:

Gasparotto Nicolas said...

It is an interesting one.
However, if it was needed, one more reason do not use SYS user for user objects :-)