28 | 03 | 2024
Latest Articles
Popular Articles

SQL Tuning

An introduction to SQL Tuning : Basic Optimizer Operations

User Rating:  / 0
PoorBest 

An introduction to SQL Tuning : Basic Optimizer Operations.



Setup SQL Tuning sample schema


1. Full Table scan
2. Index Fast Full scans
3. Index Full scans
4. Index unique scan
5. Index unique scan + table access by index rowid
6. Index range scan
7. Index range scan + table access by index rowid
8. Index min/max scans

Full table scans.


When we need all the table data.



SQL> explain plan for
2  select * from t_order_or;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
Plan hash value: 3547894390

----------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes |   Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |  1000K|   229M|  6905     (1)| 00:01:23 |
|   1 |  TABLE ACCESS FULL| T_ORDER_OR |  1000K|   229M|  6905     (1)| 00:01:23 |
----------------------------------------------------------------------------------

8 rows selected.



A full table scan is also chosen when we need almost all the table data.


SQL> explain plan for select * from t_order_or where or_pk between 1000 and 1000000;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
Plan hash value: 3547894390

----------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes |   Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |   999K|   229M|  6910     (1)| 00:01:23 |
|*  1 |  TABLE ACCESS FULL| T_ORDER_OR |   999K|   229M|  6910     (1)| 00:01:23 |
----------------------------------------------------------------------------------

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

1 - filter("OR_PK">=1000 AND "OR_PK"<=1000000)

13 rows selected.

 

Index Fast Full scans.

 

We notice very often an index fast full scan operation

1. in case of a select count(*) operation.

2. in case when all the data we need is in a not null indexed column ( the optimizer knows then that index access is enough to retrieve all the data )

 


SQL> explain plan for
2  select count(*) from t_order_or;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
Plan hash value: 2064660193

------------------------------------------------------------------------
| Id  | Operation             | Name  | Rows  |  Cost (%CPU)| Time     |
------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |       |     1 |   517    (2)| 00:00:07 |
|   1 |  SORT AGGREGATE       |       |     1 |             |          |
|   2 |   INDEX FAST FULL SCAN| OR_PK |  1000K|   517    (2)| 00:00:07 |
------------------------------------------------------------------------



SQL> explain plan for
2  select or_pk from t_order_or;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
Plan hash value: 68814747

--------------------------------------------------------------------------------------
| Id  | Operation            |   Name  |   Rows  |   Bytes |   Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |         |    1000K|    4882K|     517   (2)| 00:00:07 |
|   1 |  INDEX FAST FULL SCAN|   OR_PK |    1000K|    4882K|     517   (2)| 00:00:07 |
--------------------------------------------------------------------------------------

8 rows selected.



Index full scans.

 

The index full scan returns a sorted result set and is a single block read
The index fast full scan is a scattered multiple block read but does not return a sorted result


SQL> explain plan for select or_pk from t_order_or order by or_pk;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
Plan hash value: 1715142137

--------------------------------------------------------------------------
| Id  | Operation        | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |       |  1000K|  4882K|  1888   (1)| 00:00:23 |
|   1 |  INDEX FULL SCAN | OR_PK |  1000K|  4882K|  1888   (1)| 00:00:23 |
--------------------------------------------------------------------------

8 rows selected.


Index unique scan

A unique index is chosen to get 1 single row and when a unique index is available.

SQL> explain plan for select or_pk from t_order_or where or_pk = 1;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
Plan hash value: 1714521065

---------------------------------------------------------------------------
| Id  | Operation         | Name  |Rows  |Bytes |Cost (%CPU)|Time      |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |    1 |    5 |    2   (0)| 00:00:01 |
|*  1 |  INDEX UNIQUE SCAN| OR_PK |    1 |    5 |    2   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

1 - access("OR_PK"=1)

13 rows selected.

 

Index unique scan and additional table lookup is needed when not all the data is in the index leaf block.



SQL> explain plan for select * from t_order_or where or_pk = 1;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
Plan hash value: 2536313175

------------------------------------------------------------------------------------------
| Id  | Operation                   |   Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |     1 |   241 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T_ORDER_OR |     1 |   241 |     3   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         |  OR_PK     |     1 |       |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

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

2 - access("OR_PK"=1)

14 rows selected.

 

The index unique scan is also seen with the below inlist operation

 

SQL> explain plan for select * from t_order_or where or_pk in (1,2,3);

Explained.

SQL>  select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
Plan hash value: 3265349895

------------------------------------------------------------------------------------------
| Id  | Operation                    | Name       | Rows | Bytes | Cost (%CPU)|Time      |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |            |    3 |   723 |     6   (0)| 00:00:01 |
|   1 |  INLIST ITERATOR             |            |      |       |            |      |
|   2 |   TABLE ACCESS BY INDEX ROWID| T_ORDER_OR |    3 |   723 |     6   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN         | OR_PK      |    3 |       |     5   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

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

3 - access("OR_PK"=1 OR "OR_PK"=2 OR "OR_PK"=3)

15 rows selected.

 


Index range scan


Non unique index or unique index to get more than 1 row



SQL> explain plan for select or_pk from t_order_or where or_pk between 1000 and 10000;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
Plan hash value: 740011865

--------------------------------------------------------------------------
| Id  | Operation        | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |       |  9002 | 45010 |    19   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| OR_PK |  9002 | 45010 |    19   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

1 - access("OR_PK">=1000 AND "OR_PK"<=10000)

13 rows selected.


Index range scan and additional table lookup is needed when not all the data is in the index leaf block(s)


SQL> explain plan for select * from t_order_or where or_pk between 1000 and 10000;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
Plan hash value: 2527191937

------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |  9002 |  2118K|   245   (0)| 00:00:03 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T_ORDER_OR |  9002 |  2118K|   245   (0)| 00:00:03 |
|*  2 |   INDEX RANGE SCAN          |  OR_PK     |  9002 |       |    19   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

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

2 - access("OR_PK">=1000 AND "OR_PK"<=10000)

14 rows selected.



The index range scan is a single block read and returns a sorted result, thus the order by clause is without additional cost  ( compare example above and below )


SQL> explain plan for select * from t_order_or where or_pk between 1000 and 10000 order by or_pk;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
Plan hash value: 2527191937

------------------------------------------------------------------------------------------
| Id  | Operation                   |   Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |  9002 |  2118K|   245   (0)| 00:00:03 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T_ORDER_OR |  9002 |  2118K|   245   (0)| 00:00:03 |
|*  2 |   INDEX RANGE SCAN          |  OR_PK     |  9002 |       |    19   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

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

2 - access("OR_PK">=1000 AND "OR_PK"<=10000)

14 rows selected.



When more data ( huge range ) needs to be fetched than the single block index range scan becomes more expensive than the multible block index fast full scan, the optimizer chooses for that index fast full scan.


SQL> explain plan for select or_pk from t_order_or where or_pk between 1000 and 1000000;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
Plan hash value: 68814747

--------------------------------------------------------------------------------------
| Id  | Operation            |   Name  |   Rows  |   Bytes |   Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |         |     999K|    4877K|     522   (3)| 00:00:07 |
|*  1 |  INDEX FAST FULL SCAN|   OR_PK |     999K|    4877K|     522   (3)| 00:00:07 |
--------------------------------------------------------------------------------------

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

1 - filter("OR_PK">=1000 AND "OR_PK"<=1000000)

13 rows selected.



When more data ( huge range ) needs to be fetched and IF we need a sorted result the optimizer can prefer again a single block index range scan. ( index range scan can become cheaper than the index fast full scan )



SQL> explain plan for select * from t_order_or where or_pk between 1000 and 1000000 order by or_pk;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
Plan hash value: 2527191937

------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |   999K|   229M| 26887   (1)| 00:05:23 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T_ORDER_OR |   999K|   229M| 26887   (1)| 00:05:23 |
|*  2 |   INDEX RANGE SCAN          | OR_PK      |   999K|       |  1887   (1)| 00:00:23 |
------------------------------------------------------------------------------------------

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

2 - access("OR_PK">=1000 AND "OR_PK"<=1000000)

14 rows selected.



Index MIN/MAX scans


SQL> explain plan for select min(or_pk) from t_order_or;

Explained.


SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
Plan hash value: 2579623908

------------------------------------------------------------------------------------
| Id  | Operation                  | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |       |     1 |     5 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE            |       |     1 |     5 |            |          |
|   2 |   INDEX FULL SCAN (MIN/MAX)| OR_PK |     1 |     5 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------

9 rows selected.

SQL> explain plan for select max(or_pk) from t_order_or;

Explained.

SQL>  select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
Plan hash value: 2579623908

------------------------------------------------------------------------------------
| Id  | Operation                  | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |       |     1 |     5 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE            |       |     1 |     5 |            |          |
|   2 |   INDEX FULL SCAN (MIN/MAX)| OR_PK |     1 |     5 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------

9 rows selected.