Oracle Database Discussions

Hi all,

I have a question about Oracle 11g Statistics and the way Oracle optimizer chose the Access Path.

In the example below:
1. without Stats, Oracle use dynamic-sampling and it choose the right Plan. Index-Access for the row with ID=1 (just 1 rows has this value), and Full-Scan for the remain rows with ID=99 (all the remaining rows)

2. With Stats manually gathered, Oracle choose the wrong Access Path. And even I use hint to force Optimizer use Index Acess-Path, and it used but the Cost for Index-Access Path with Stats is higher than Index-Access-Path without stats.

Please exlain this case.



SQL> drop table t purge;

Table dropped.

SQL> drop table big_table purge;

Table dropped.

SQL>
SQL> create table big_table (x number, y number);

Table created.

SQL>
SQL> create table t as select 99 id, a.* from all_objects a;

Table created.

SQL>
SQL> update t set id = 1 where rownum = 1;

1 row updated.

SQL> commit;

Commit complete.

SQL>
SQL> create index t_idx on t(id);

Index created.

SQL>
SQL>
SQL>
SQL> set autotrace traceonly explain
SQL> select * from t where id = 1;

Execution Plan
----------------------------------------------------------
Plan hash value: 470836197

*===> witout Stats, Cost is small *

-------------------------------------------------------------------------------------
*| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |*
-------------------------------------------------------------------------------------
*| 0 | SELECT STATEMENT | | 1 | 171 | 2 (0)| 00:00:01 |*
*| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 171 | 2 (0)| 00:00:01 |*
**|* 2 | INDEX RANGE SCAN | T_IDX | 1 | | 1 (0)| 00:00:01 |**
-------------------------------------------------------------------------------------

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

2 - access("ID"=1)

Note
-----
- dynamic sampling used for this statement (level=2)

SQL>
SQL>
SQL> select * from t where id = 99;

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 70435 | 11M| 238 (1)| 00:00:03 |
|* 1 | TABLE ACCESS FULL| T | 70435 | 11M| 238 (1)| 00:00:03 |
--------------------------------------------------------------------------

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

1 - filter("ID"=99)

Note
-----
- dynamic sampling used for this statement (level=2)

SQL>
SQL> set autotrace off
SQL>
SQL>
SQL> begin
2 dbms_stats.gather_table_stats
3 ( user, 'T',
4 method_opt => 'for all indexed columns',
5 cascade => TRUE );
6 end;
7 /

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> set autotrace traceonly explain
SQL> select * from t where id = 1;

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 35786 | 3494K| 238 (1)| 00:00:03 |
|* 1 | TABLE ACCESS FULL| T | 35786 | 3494K| 238 (1)| 00:00:03 |
--------------------------------------------------------------------------

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

1 - filter("ID"=1)

SQL>
SQL>
SQL> select * from t where id = 99;

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 71565 | 6988K| 238 (1)| 00:00:03 |
|* 1 | TABLE ACCESS FULL| T | 71565 | 6988K| 238 (1)| 00:00:03 |
--------------------------------------------------------------------------

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

1 - filter("ID"=99)

SQL>
SQL> select /*+ index(t t_idx) */ * from t where id=1;

Execution Plan
----------------------------------------------------------
Plan hash value: 470836197

*===> With Stats, Cost is higher than without stats*

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 35786 | 3494K| 596 (1)| 00:00:08 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 35786 | 3494K| 596 (1)| 00:00:08 |
|* 2 | INDEX RANGE SCAN | T_IDX | 35786 | | 70 (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

2 - access("ID"=1)

SQL>
SQL> set autotrace off











=====================
CODE
=====================
set echo on
set linesize 200


drop table t purge;
drop table big_table purge;

create table big_table (x number, y number);

create table t as select 99 id, a.* from all_objects a;

update t set id = 1 where rownum = 1;
commit;

create index t_idx on t(id);



set autotrace traceonly explain
select * from t where id = 1;


select * from t where id = 99;

set autotrace off


begin
dbms_stats.gather_table_stats
( user, 'T',
method_opt => 'for all indexed columns',
cascade => TRUE );
end;
/


set autotrace traceonly explain
select * from t where id = 1;


select * from t where id = 99;

select /*+ index(t t_idx) */ * from t where id=1;

set autotrace off