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.
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
---------------------------------------------------------------------------------
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
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
