Analysis 1 : Explore Access Path

This is a 1st article in analysis series.....

In this series ,we will observe how oracle optimizer chooses different ACCESS PATH's based upon available information. Here I am using SH schema for demonstration.


First create T1 table from SALES, and dump some data, and add one extra unique row. 

SQL> conn sh/sh

Connected.

SQL> CREATE TABLE T1 AS SELECT * FROM SALES;

Table created.

SQL> INSERT INTO T1 SELECT * FROM SALES;

918843 rows created.

SQL> INSERT INTO T1 SELECT * FROM SALES;

918843 rows created.

SQL> INSERT INTO T1 SELECT * FROM SALES;

918843 rows created.

SQL> INSERT INTO T1 SELECT * FROM SALES;

918843 rows created.

SQL> insert into t1 values (0,0,sysdate,0,0,0,0);

1 row created.

SQL> commit;

Commit complete.

SQL> SELECT COUNT(*) FROM T1;

  COUNT(*)

----------

   4594216

set the environment, and flush shared pool and buffer cache before running query.

SQL> set timing on;
SQL> set autotrace traceonly
SQL> set linesize 200
SQL> set pagesize 500
SQL>
SQL> alter system flush shared_pool;
System altered.
Elapsed: 00:00:00.10
SQL> alter system flush buffer_cache;
System altered.
Elapsed: 00:00:42.28

Run query to fetch one record and observe the output. It is doing FTS to fetch one record, which is costly operation.

SQL> select * from t1 where prod_id=0;
Elapsed: 00:00:03.01
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   247 | 21489 |  6139   (2)| 00:01:14 |
|*  1 |  TABLE ACCESS FULL| T1   |   247 | 21489 |  6139   (2)| 00:01:14 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("PROD_ID"=0)
Note
-----
   - dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
        430  recursive calls
          0  db block gets
      22244  consistent gets
      22506  physical reads
          0  redo size
        970  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          5  sorts (memory)
          0  sorts (disk)
          1  rows processed
SQL>


Now lets add index on T1.prod_id. 

SQL> set timing off
SQL> set autotrace off
SQL> create index idx on t1(prod_id) compute statistics;
Index created.

Set environment, and flush shared pool and buffer cache before running the same query.
SQL> set timing on;
SQL> set autotrace traceonly
SQL> set linesize 200
SQL> set pagesize 500
SQL> alter system flush shared_pool;
System altered.
Elapsed: 00:00:00.57
SQL> alter system flush buffer_cache;
System altered.
Elapsed: 00:00:01.01

Now run same query and observe output..... 

SQL> select * from t1 where prod_id=0;
Elapsed: 00:00:02.32
Execution Plan
----------------------------------------------------------
Plan hash value: 997549881
---------------------------------------------------------------------------------

| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time     

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

|   0 | SELECT STATEMENT            |      |     1 |    87 |     4   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T1   |     1 |    87 |     4   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | IDX  |     1 |       |     3   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("PROD_ID"=0)

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

Statistics
----------------------------------------------------------
        551  recursive calls
          0  db block gets
        181  consistent gets
        234  physical reads
          0  redo size
        974  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
          1  rows processed
SQL>

Observation : Optimizer started using new index, doing INDEX RANGE SCAN instead of FTS and now cost is low, which is optimal solution for this problem.

-Asifkhan