Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, jiang huang.

Asked: April 21, 2010 - 11:38 pm UTC

Last updated: April 22, 2010 - 9:15 am UTC

Version: 11.1.0

Viewed 1000+ times

You Asked

Hi Tom,

database is version 11.1.0

I follow your example in expert one-on-one oracle,plan stability chapter.

SQL> create table for_test as select empno,ename from scott.emp group by empno,ename;

Table created.

SQL> alter table for_test add constraint e_pk primary key(empno);

Table altered.
SQL> set autotrace on
SQL> set autotrace traceonly explain
SQL> select * from for_test where empno>0;

Execution Plan
----------------------------------------------------------
Plan hash value: 3571124506

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

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

2 - access("EMPNO">0)

Note
-----
- dynamic sampling used for this statement

SQL> set autotrace off
SQL> show parameter optimizer_mode

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode string ALL_ROWS
SQL> show parameter dyna

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling integer 2
SQL> analyze table for_test compute statisitcs;
analyze table for_test compute statisitcs
*
ERROR at line 1:
ORA-00905: missing keyword


SQL> analyze table for_test compute statistics;

Table analyzed.

SQL> set autotrace traceonly explain
SQL> select * from for_test where empno>0;

Execution Plan
----------------------------------------------------------
Plan hash value: 3571124506

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

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

2 - access("EMPNO">0)

SQL>

In your book, cbo is smart enough using full table scan. but here cbo chooses a index range scan. Why is the difference? Scott.emp is rather small,only 14 rows.

So i turn to dbms_stats.

SQL> analyze table for_test delete statistics;

Table analyzed.

SQL> exec dbms_stats.gather_table_stats(user,'FOR_TEST');

PL/SQL procedure successfully completed.

SQL> select * from for_test where empno>0;

Execution Plan
----------------------------------------------------------
Plan hash value: 3571124506

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

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

2 - access("EMPNO">0)

The same plan, so the second confusion is :

11g CBO still allows the statistics done by ANALYZE?

Appreciated if you can take some time to answer above.

and Tom said...

... 11g CBO still allows the statistics done by ANALYZE? ...


yes, there is a difference between obsolete/deprecated/not supported (all of which are true for analyze table compute statistics) and "removed".

The RBO (rule based optimizer) is still in there - but it is obsolete, deprecated, not supported. There are thousands of things still in there that are in that state.


the costs just ended up being "a tie" - either way was better than good enough.

ops$tkyte%ORA10GR2> create table t as select empno, ename from scott.emp group by empno, ename;

Table created.

ops$tkyte%ORA10GR2> alter table t add constraint t_pk primary key(empno);

Table altered.

ops$tkyte%ORA10GR2> set autotrace traceonly explain
ops$tkyte%ORA10GR2> select * from t where empno > 0;

Execution Plan
----------------------------------------------------------
Plan hash value: 3772518221

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

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

   2 - access("EMPNO">0)

Note
-----
   - dynamic sampling used for this statement

ops$tkyte%ORA10GR2> select /*+ full(t) */ * from t where empno > 0;

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

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    14 |   280 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |    14 |   280 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("EMPNO">0)

Note
-----
   - dynamic sampling used for this statement

ops$tkyte%ORA10GR2> exec dbms_stats.gather_table_stats( user, 'T' );

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> select * from t where empno > 0;

Execution Plan
----------------------------------------------------------
Plan hash value: 3772518221

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

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

   2 - access("EMPNO">0)

ops$tkyte%ORA10GR2> select /*+ full(t) */ * from t where empno > 0;

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

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    14 |   126 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |    14 |   126 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("EMPNO">0)

ops$tkyte%ORA10GR2> set autotrace off



one of the things about teeny tiny things - they are not entirely representative

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks!

jiang huang zheng, April 22, 2010 - 9:45 am UTC


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library