Oracle execution plan explanation (1)

Keywords: SQL Oracle

Understanding Oracle's execution plan is the first step in optimizing, so let's start with the example below.
Additional information below
1. Create test tables
  1. [sql] view plaincopy  
  2. SQL> create table t as select 1 id,object_name from dba_objects;    
  3.      
  4. Table created    
  5.      
  6. SQL> update t set id=99 where rownum=1;    
  7.      
  8. 1 row updated    
  9.      
  10. SQL> commit;    
  11.      
  12. Commit complete    
  13.      
  14. SQL> create index t_ind on t(id);    
  15.      
  16. Index created    
[sql] view plaincopy
SQL> create table t as select 1 id,object_name from dba_objects;  
   
Table created  
   
SQL> update t set id=99 where rownum=1;  
   
1 row updated  
   
SQL> commit;  
   
Commit complete  
   
SQL> create index t_ind on t(id);  
   
Index created

oracle Optimizer: RBO and CBO. Optimizer has abandoned RBO since oracle 10g. The following column shows what CBO is like
  1. [sql] view plaincopy  
  2. SQL>  select /*+dynamic_sampling(t 0) */* from t where id=1;    
  3.     
  4. 50819 rows selected.    
  5.     
  6.     
  7. Execution Plan    
  8. ----------------------------------------------------------    
  9. Plan hash value: 1376202287    
  10.     
  11. -------------------------------------------------------------------------------------    
  12. | Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |    
  13. -------------------------------------------------------------------------------------    
  14. |   0 | SELECT STATEMENT            |       |   195 | 15405 |    51   (0)| 00:00:01 |    
  15. |   1 |  TABLE ACCESS BY INDEX ROWID| T     |   195 | 15405 |    51   (0)| 00:00:01 |    
  16. |*  2 |   INDEX RANGE SCAN          | T_IND |    78 |       |    50   (0)| 00:00:01 |    
  17. -------------------------------------------------------------------------------------    
  18.     
  19. Predicate Information (identified by operation id):    
  20. ---------------------------------------------------   
  21.     
  22.    2 - access("ID"=1)     
[sql] view plaincopy
SQL>  select /*+dynamic_sampling(t 0) */* from t where id=1;  
  
50819 rows selected.  
  
  
Execution Plan  
----------------------------------------------------------  
Plan hash value: 1376202287  
  
-------------------------------------------------------------------------------------  
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |  
-------------------------------------------------------------------------------------  
|   0 | SELECT STATEMENT            |       |   195 | 15405 |    51   (0)| 00:00:01 |  
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |   195 | 15405 |    51   (0)| 00:00:01 |  
|*  2 |   INDEX RANGE SCAN          | T_IND |    78 |       |    50   (0)| 00:00:01 |  
-------------------------------------------------------------------------------------  
  
Predicate Information (identified by operation id):  
--------------------------------------------------- 
  
   2 - access("ID"=1)

The phenomena t-table has not been analyzed yet, suggesting that the purpose of /*+dynamic_sampling(t 0)*/* is to prevent CBO from obtaining the actual data in the table through dynamic sampling, where CBO can only guess the data in the table based on very limited information in the T-table (such as the number of extents in the table, the number of data blocks).You can see from the results that CBO guessed 195 tables with id=1, which is a very small value for the total number of tables, so CBO chose an index instead of a full table scan.
The actual situation is as follows:
  1. [sql] view plaincopy  
  2. SQL> select * from  t where id=1    
  3.   2  ;    
  4.     
  5. 50819 rows selected.    
  6.     
  7.     
  8. Execution Plan    
  9. ----------------------------------------------------------    
  10. Plan hash value: 1601196873    
  11.     
  12. --------------------------------------------------------------------------    
  13. | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |    
  14. --------------------------------------------------------------------------    
  15. |   0 | SELECT STATEMENT  |      | 49454 |  3815K|    67   (2)| 00:00:01 |    
  16. |*  1 |  TABLE ACCESS FULL| T    | 49454 |  3815K|    67   (2)| 00:00:01 |    
  17. --------------------------------------------------------------------------    
  18.     
  19. Predicate Information (identified by operation id):    
  20. ---------------------------------------------------    
  21.     
  22.    1 - filter("ID"=1)    
[sql] view plaincopy
SQL> select * from  t where id=1  
  2  ;  
  
50819 rows selected.  
  
  
Execution Plan  
----------------------------------------------------------  
Plan hash value: 1601196873  
  
--------------------------------------------------------------------------  
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |  
--------------------------------------------------------------------------  
|   0 | SELECT STATEMENT  |      | 49454 |  3815K|    67   (2)| 00:00:01 |  
|*  1 |  TABLE ACCESS FULL| T    | 49454 |  3815K|    67   (2)| 00:00:01 |  
--------------------------------------------------------------------------  
  
Predicate Information (identified by operation id):  
---------------------------------------------------  
  
   1 - filter("ID"=1)


Based on dynamic sampling, the CBO estimates the number of trips to 49454, which is very close to the actual number of 50820.Full table scan was selected.
* Let's collect some statistics

  1. [sql] view plaincopy  
  2. SQL> exec dbms_stats.gather_table_stats(user,'t',cascade => true);    
  3.     
  4. SQL> select * from  t where id=1;    
  5.     
  6. 50819 rows selected.    
  7.     
  8.     
  9. Execution Plan    
  10. ----------------------------------------------------------    
  11. Plan hash value: 1601196873    
  12.     
  13. --------------------------------------------------------------------------    
  14. | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |    
  15. --------------------------------------------------------------------------    
  16. |   0 | SELECT STATEMENT  |      | 50815 |  1339K|    67   (2)| 00:00:01 |    
  17. |*  1 |  TABLE ACCESS FULL| T    | 50815 |  1339K|    67   (2)| 00:00:01 |    
  18. --------------------------------------------------------------------------    
  19.     
  20. Predicate Information (identified by operation id):    
  21. ---------------------------------------------------    
  22.     
  23.    1 - filter("ID"=1)    
[sql] view plaincopy
SQL> exec dbms_stats.gather_table_stats(user,'t',cascade => true);  
  
SQL> select * from  t where id=1;  
  
50819 rows selected.  
  
  
Execution Plan  
----------------------------------------------------------  
Plan hash value: 1601196873  
  
--------------------------------------------------------------------------  
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |  
--------------------------------------------------------------------------  
|   0 | SELECT STATEMENT  |      | 50815 |  1339K|    67   (2)| 00:00:01 |  
|*  1 |  TABLE ACCESS FULL| T    | 50815 |  1339K|    67   (2)| 00:00:01 |  
--------------------------------------------------------------------------  
  
Predicate Information (identified by operation id):  
---------------------------------------------------  
  
   1 - filter("ID"=1)


The number of rows scanned is now 50815.

If we update all IDs to 99 look at it.

  1. [sql] view plaincopy  
  2. SQL> update t set id=99;    
  3.      
  4. 50820 rows updated    
  5.     
  6. SQL> select * from  t where id=99;    
  7.     
  8.     
  9. Execution Plan    
  10. ----------------------------------------------------------    
  11. Plan hash value: 1376202287    
  12.     
  13. -------------------------------------------------------------------------------------    
  14. | Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |    
  15. -------------------------------------------------------------------------------------    
  16. |   0 | SELECT STATEMENT            |       |     1 |    27 |     2   (0)| 00:00:01 |    
  17. |   1 |  TABLE ACCESS BY INDEX ROWID| T     |     1 |    27 |     2   (0)| 00:00:01 |    
  18. |*  2 |   INDEX RANGE SCAN          | T_IND |     1 |       |     1   (0)| 00:00:01 |    
  19. -------------------------------------------------------------------------------------    
  20.     
  21. Predicate Information (identified by operation id):    
  22. ---------------------------------------------------    
  23.     
  24.    2 - access("ID"=99)    
[sql] view plaincopy
SQL> update t set id=99;  
   
50820 rows updated  
  
SQL> select * from  t where id=99;  
  
  
Execution Plan  
----------------------------------------------------------  
Plan hash value: 1376202287  
  
-------------------------------------------------------------------------------------  
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |  
-------------------------------------------------------------------------------------  
|   0 | SELECT STATEMENT            |       |     1 |    27 |     2   (0)| 00:00:01 |  
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |     1 |    27 |     2   (0)| 00:00:01 |  
|*  2 |   INDEX RANGE SCAN          | T_IND |     1 |       |     1   (0)| 00:00:01 |  
-------------------------------------------------------------------------------------  
  
Predicate Information (identified by operation id):  
---------------------------------------------------  
  
   2 - access("ID"=99)


CBO does not know whether the data analyzed in the table is previous information because the table has not been analyzed.We can see that the Rows value is 1, that is, there is only one value of ID=99 in the CBO artificial table T, and all the choices are still indexed.

We collect a handful of statistics.

  1. [sql] view plaincopy  
  2. SQL> exec dbms_stats.gather_table_stats(user,'t',cascade => true);    
  3.      
  4. PL/SQL procedure successfully completed    
  5.     
  6. SQL> select * from  t where id=99;    
  7.     
  8. 50820 rows selected.    
  9.     
  10.     
  11. Execution Plan    
  12. ----------------------------------------------------------    
  13. Plan hash value: 1601196873    
  14.     
  15. --------------------------------------------------------------------------    
  16. | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |    
  17. --------------------------------------------------------------------------    
  18. |   0 | SELECT STATEMENT  |      | 50815 |  1339K|    67   (2)| 00:00:01 |    
  19. |*  1 |  TABLE ACCESS FULL| T    | 50815 |  1339K|    67   (2)| 00:00:01 |    
  20. --------------------------------------------------------------------------    
  21.     
  22. Predicate Information (identified by operation id):    
  23. ---------------------------------------------------    
  24.     
  25.    1 - filter("ID"=99)    
[sql] view plaincopy
SQL> exec dbms_stats.gather_table_stats(user,'t',cascade => true);  
   
PL/SQL procedure successfully completed  
  
SQL> select * from  t where id=99;  
  
50820 rows selected.  
  
  
Execution Plan  
----------------------------------------------------------  
Plan hash value: 1601196873  
  
--------------------------------------------------------------------------  
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |  
--------------------------------------------------------------------------  
|   0 | SELECT STATEMENT  |      | 50815 |  1339K|    67   (2)| 00:00:01 |  
|*  1 |  TABLE ACCESS FULL| T    | 50815 |  1339K|    67   (2)| 00:00:01 |  
--------------------------------------------------------------------------  
  
Predicate Information (identified by operation id):  
---------------------------------------------------  
  
   1 - filter("ID"=99)

Additional content above, starting officially below

1. Execution plan of sql

Create Test Table
  1. [sql] view plaincopy  
  2. SQL> create table t1(id int,name varchar2(1000));    
  3.      
  4. Table created    
  5.      
  6. SQL> create table t2(id int,name varchar2(1000));    
  7.      
  8. Table created    
  9.      
  10. SQL> create index ind_t1 on t1(id);    
  11.      
  12. Index created    
  13.      
  14. SQL> create index ind_t2 on t2(id);    
  15.      
  16. Index created    
  17.      
  18. SQL> create index ind_t2_name on t2(name);    
  19.      
  20. Index created    
  21.      
  22. SQL> insert into t1 select  a.OBJECT_ID,a.OBJECT_NAME from all_objects a;    
  23.      
  24. 50206 rows inserted    
  25.     
  26. SQL> insert into t2 select  a.OBJECT_ID,a.OBJECT_NAME from all_objects a where rownum<=20;    
  27.      
  28. 20 rows inserted    
  29.      
  30. SQL> commit;    
  31.      
  32. Commit complete    
  33.      
  34. SQL> exec dbms_stats.gather_table_stats(user,'t1',cascade => true);    
  35.      
  36. PL/SQL procedure successfully completed    
  37.     
  38. SQL> exec dbms_stats.gather_table_stats(user,'t2',cascade => true);    
  39.      
  40. PL/SQL procedure successfully completed    
[sql] view plaincopy
SQL> create table t1(id int,name varchar2(1000));  
   
Table created  
   
SQL> create table t2(id int,name varchar2(1000));  
   
Table created  
   
SQL> create index ind_t1 on t1(id);  
   
Index created  
   
SQL> create index ind_t2 on t2(id);  
   
Index created  
   
SQL> create index ind_t2_name on t2(name);  
   
Index created  
   
SQL> insert into t1 select  a.OBJECT_ID,a.OBJECT_NAME from all_objects a;  
   
50206 rows inserted  
  
SQL> insert into t2 select  a.OBJECT_ID,a.OBJECT_NAME from all_objects a where rownum<=20;  
   
20 rows inserted  
   
SQL> commit;  
   
Commit complete  
   
SQL> exec dbms_stats.gather_table_stats(user,'t1',cascade => true);  
   
PL/SQL procedure successfully completed  
  
SQL> exec dbms_stats.gather_table_stats(user,'t2',cascade => true);  
   
PL/SQL procedure successfully completed


2. Generate an execution plan
  1. [sql] view plaincopy  
  2. SQL> select * from t1,t2 where t1.id= t2.id;    
  3.     
  4. 20 rows selected.    
  5.     
  6.     
  7. Execution Plan    
  8. ----------------------------------------------------------    
  9. Plan hash value: 828990364    
  10.     
  11. --------------------------------------------------------------------------------------    
  12. | Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |    
  13. --------------------------------------------------------------------------------------    
  14. |   0 | SELECT STATEMENT            |        |    20 |   780 |    43   (0)| 00:00:01 |    
  15. |   1 |  TABLE ACCESS BY INDEX ROWID| T1     |     1 |    28 |     2   (0)| 00:00:01 |    
  16. |   2 |   NESTED LOOPS              |        |    20 |   780 |    43   (0)| 00:00:01 |    
  17. |   3 |    TABLE ACCESS FULL        | T2     |    20 |   220 |     3   (0)| 00:00:01 |    
  18. |*  4 |    INDEX RANGE SCAN         | IND_T1 |     1 |       |     1   (0)| 00:00:01 |    
  19. --------------------------------------------------------------------------------------    
  20.     
  21. Predicate Information (identified by operation id):    
  22. ---------------------------------------------------    
  23.     
  24.    4 - access("T1"."ID"="T2"."ID")    
  25.     
  26.     
  27. Statistics    
  28. ----------------------------------------------------------    
  29.           1  recursive calls    
  30.           0  db block gets    
  31.          37  consistent gets    
  32.           0  physical reads    
  33.           0  redo size    
  34.        1452  bytes sent via SQL*Net to client    
  35.         503  bytes received via SQL*Net from client    
  36.           3  SQL*Net roundtrips to/from client    
  37.           0  sorts (memory)    
  38.           0  sorts (disk)    
  39.          20  rows processed    
[sql] view plaincopy
SQL> select * from t1,t2 where t1.id= t2.id;  
  
20 rows selected.  
  
  
Execution Plan  
----------------------------------------------------------  
Plan hash value: 828990364  
  
--------------------------------------------------------------------------------------  
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |  
--------------------------------------------------------------------------------------  
|   0 | SELECT STATEMENT            |        |    20 |   780 |    43   (0)| 00:00:01 |  
|   1 |  TABLE ACCESS BY INDEX ROWID| T1     |     1 |    28 |     2   (0)| 00:00:01 |  
|   2 |   NESTED LOOPS              |        |    20 |   780 |    43   (0)| 00:00:01 |  
|   3 |    TABLE ACCESS FULL        | T2     |    20 |   220 |     3   (0)| 00:00:01 |  
|*  4 |    INDEX RANGE SCAN         | IND_T1 |     1 |       |     1   (0)| 00:00:01 |  
--------------------------------------------------------------------------------------  
  
Predicate Information (identified by operation id):  
---------------------------------------------------  
  
   4 - access("T1"."ID"="T2"."ID")  
  
  
Statistics  
----------------------------------------------------------  
          1  recursive calls  
          0  db block gets  
         37  consistent gets  
          0  physical reads  
          0  redo size  
       1452  bytes sent via SQL*Net to client  
        503  bytes received via SQL*Net from client  
          3  SQL*Net roundtrips to/from client  
          0  sorts (memory)  
          0  sorts (disk)  
         20  rows processed


When looking at the execution plan, we first read from the line with the largest indentation, which is the first step to be executed.In the execution plan, id=3 and id=4 are the first to be executed.
  1. [sql] view plaincopy  
  2. |   3 |    TABLE ACCESS FULL        | T2     |    20 |   220 |     3   (0)| 00:00:01 |    
  3. |*  4 |    INDEX RANGE SCAN         | IND_T1 |     1 |       |     1   (0)| 00:00:01 |    
[sql] view plaincopy
|   3 |    TABLE ACCESS FULL        | T2     |    20 |   220 |     3   (0)| 00:00:01 |  
|*  4 |    INDEX RANGE SCAN         | IND_T1 |     1 |       |     1   (0)| 00:00:01 |

The indentation of the two lines is the same, and the top line is executed first, where id=3
  1. [sql] view plaincopy  
  2. |   3 |    TABLE ACCESS FULL        | T2     |    20 |   220 |     3   (0)| 00:00:01 |   
[sql] view plaincopy
|   3 |    TABLE ACCESS FULL        | T2     |    20 |   220 |     3   (0)| 00:00:01 |

Choose row number id=2 for the next indentation and join the table using NESTED LOOPS.
  1. [sql] view plaincopy  
  2. |   2 |   NESTED LOOPS              |        |    20 |   780 |    43   (0)| 00:00:01 |   
[sql] view plaincopy
|   2 |   NESTED LOOPS              |        |    20 |   780 |    43   (0)| 00:00:01 |
  
* Then id=1, scan the table in TABLE ACCESS BY INDEX ROWID
  1. [sql] view plaincopy  
  2. |   1 |  TABLE ACCESS BY INDEX ROWID| T1     |     1 |    28 |     2   (0)| 00:00:01 |  
[sql] view plaincopy
|   1 |  TABLE ACCESS BY INDEX ROWID| T1     |     1 |    28 |     2   (0)| 00:00:01 |
 
Last is id=0
  1. [sql] view plaincopy  
  2. |   0 | SELECT STATEMENT            |        |    20 |   780 |    43   (0)| 00:00:01 |   
[sql] view plaincopy
|   0 | SELECT STATEMENT            |        |    20 |   780 |    43   (0)| 00:00:01 |

We translated into roughly the following languages:
Read from the first row of the t2 table to see if each row meets the following criteria:
"T1"."ID"="T2"."ID"
If one line is taken out and the entire t2 table is scanned, this process is called NESTED LOOPS
* When the entire t2 table is scanned, a result set is generated, which is an index set of IND_T1, and oracle then finds the corresponding record in the T1 table based on the rowid on the index key value, which is the step: TABLE ACCESS BY INDEX ROWID
* Then return the result: SELECT STATEMENT
The ID column is id=3->id=4->id=2->id=1->id=0
Let's take another look at what each row in the table means:
1) Operation column: the content of the current operation.
2) Rows column: is the cardinality of the current operation, Oracle estimates the return result set of the current operation.
3) Cost (%CPU): A value (cost) computed by Oracle to account for the cost of sql execution.
4) Time column: Oracle estimates the time of the current operation.

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

   4 - access("T1"."ID"="T2"."ID")

There is a difference between access and filter, where access indicates that the value of the condition of this predicate will affect the access path to the data (generally for the index), and filter only acts as a filter.
For instance
  1. [sql] view plaincopy  
  2. SQL> select * from t1 where t1.name='AA';    
  3.     
  4. no rows selected    
  5.     
  6.     
  7. Execution Plan    
  8. ----------------------------------------------------------    
  9. Plan hash value: 3617692013    
  10.     
  11. --------------------------------------------------------------------------    
  12. | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |    
  13. --------------------------------------------------------------------------    
  14. |   0 | SELECT STATEMENT  |      |     2 |    56 |    69   (2)| 00:00:01 |    
  15. |*  1 |  TABLE ACCESS FULL| T1   |     2 |    56 |    69   (2)| 00:00:01 |    
  16. --------------------------------------------------------------------------    
  17.     
  18. Predicate Information (identified by operation id):    
  19. ---------------------------------------------------    
  20.     
  21.    1 - filter("T1"."NAME"='AA')    
[sql] view plaincopy
SQL> select * from t1 where t1.name='AA';  
  
no rows selected  
  
  
Execution Plan  
----------------------------------------------------------  
Plan hash value: 3617692013  
  
--------------------------------------------------------------------------  
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |  
--------------------------------------------------------------------------  
|   0 | SELECT STATEMENT  |      |     2 |    56 |    69   (2)| 00:00:01 |  
|*  1 |  TABLE ACCESS FULL| T1   |     2 |    56 |    69   (2)| 00:00:01 |  
--------------------------------------------------------------------------  
  
Predicate Information (identified by operation id):  
---------------------------------------------------  
  
   1 - filter("T1"."NAME"='AA')


You see.
Let's take a closer look at what's inside Operation
  1. [sql] view plaincopy   
[sql] view plaincopy

a. Table access method
1.Full Table Scan (FTS) Full Table Scan
In a FTS operation, the whole table is read up to the high water mark (HWM). The HWM marks the last block in the table that has ever had data written to it. If you have deleted all the rows then you will still read up to the HWM. Truncate resets the HWM back to the start of the table. FTS uses multiblock i/o to read the blocks from disk. 
--The full table scan mode reads the data to the table's high water level (HWM is the last data block the table ever expanded), and the reading speed depends on the Oracle initialization parameter db_block_multiblock_read_count (I think it should be translated as follows: FTS scan will raise the table's usage to the high water level (HWM),HWM identifies the last data block written to the table, if you DELETE all data tables with DELETEStill at high water level (HWM), table regression is only possible with TRUNCATE, FTS uses multiple IO to read data blocks from disk) Query Plan
------------------------------------ 
  1. SELECT STATEMENT [CHOOSE] Cost=1    
  2. **INDEX UNIQUE SCAN EMP_I1   
SELECT STATEMENT [CHOOSE] Cost=1  
**INDEX UNIQUE SCAN EMP_I1

--If you find the data you want in the index, you won't visit the table anymore
2.Index Lookup Index Scan
There are 5 methods of index lookup:   
1) index unique scan -- index unique scan
Method for looking up a single key value via a unique index. always returns a single value, You must supply AT LEAST the leading column of the index to access data via the index.
eg:
  1. SQL> select empno,ename from emp where empno=10  
  2. SQL> select empno,ename from emp where empno=10;    
  3.     
  4. no rows selected    
  5.     
  6.     
  7. Execution Plan    
  8. ----------------------------------------------------------    
  9. Plan hash value: 2949544139    
  10.     
  11. --------------------------------------------------------------------------------------    
  12. | Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |    
  13. --------------------------------------------------------------------------------------    
  14. |   0 | SELECT STATEMENT            |        |     1 |    20 |     1   (0)| 00:00:01 |    
  15. |   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    20 |     1   (0)| 00:00:01 |    
  16. |*  2 |   INDEX UNIQUE SCAN         | PK_EMP |     1 |       |     1   (0)| 00:00:01 |    
  17. --------------------------------------------------------------------------------------    
  18.     
  19. Predicate Information (identified by operation id):    
  20. ---------------------------------------------------    
  21.     
  22.    2 - access("EMPNO"=10)    
  23.     
  24.     
  25. Statistics    
  26. ----------------------------------------------------------    
  27.          24  recursive calls    
  28.           0  db block gets    
  29.           3  consistent gets    
  30.           0  physical reads    
  31.           0  redo size    
  32.         385  bytes sent via SQL*Net to client    
  33.         481  bytes received via SQL*Net from client    
  34.           1  SQL*Net roundtrips to/from client    
  35.           0  sorts (memory)    
  36.           0  sorts (disk)    
  37.           0  rows processed   
SQL> select empno,ename from emp where empno=10
SQL> select empno,ename from emp where empno=10;  
  
no rows selected  
  
  
Execution Plan  
----------------------------------------------------------  
Plan hash value: 2949544139  
  
--------------------------------------------------------------------------------------  
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |  
--------------------------------------------------------------------------------------  
|   0 | SELECT STATEMENT            |        |     1 |    20 |     1   (0)| 00:00:01 |  
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    20 |     1   (0)| 00:00:01 |  
|*  2 |   INDEX UNIQUE SCAN         | PK_EMP |     1 |       |     1   (0)| 00:00:01 |  
--------------------------------------------------------------------------------------  
  
Predicate Information (identified by operation id):  
---------------------------------------------------  
  
   2 - access("EMPNO"=10)  
  
  
Statistics  
----------------------------------------------------------  
         24  recursive calls  
          0  db block gets  
          3  consistent gets  
          0  physical reads  
          0  redo size  
        385  bytes sent via SQL*Net to client  
        481  bytes received via SQL*Net from client  
          1  SQL*Net roundtrips to/from client  
          0  sorts (memory)  
          0  sorts (disk)  
          0  rows processed


2) index range scan -- index local scan
Index range scan is a method for accessing a range values of a particular column. AT LEAST the leading column of the index must be supplied to access data via the index. Can be used for range operations (e.g. >  
  1. SQL> select empno from emp where EMPNO>=7902;    
  2.     
  3.     
  4. Execution Plan    
  5. ----------------------------------------------------------    
  6. Plan hash value: 1567865628    
  7.     
  8. ---------------------------------------------------------------------------    
  9. | Id  | Operation        | Name   | Rows  | Bytes | Cost (%CPU)| Time     |    
  10. ---------------------------------------------------------------------------    
  11. |   0 | SELECT STATEMENT |        |     2 |    26 |     2   (0)| 00:00:01 |    
  12. |*  1 |  INDEX RANGE SCAN| PK_EMP |     2 |    26 |     2   (0)| 00:00:01 |    
  13. ---------------------------------------------------------------------------    
  14.     
  15. Predicate Information (identified by operation id):    
  16. ---------------------------------------------------    
  17.     
  18.    1 - access("EMPNO">=7902)    
  19.     
  20. Note    
  21. -----    
  22.    - dynamic sampling used for this statement    
  23.     
  24.     
  25. Statistics    
  26. ----------------------------------------------------------    
  27.           0  recursive calls    
  28.           0  db block gets    
  29.           2  consistent gets    
  30.           0  physical reads    
  31.           0  redo size    
  32.         569  bytes sent via SQL*Net to client    
  33.         492  bytes received via SQL*Net from client    
  34.           2  SQL*Net roundtrips to/from client    
  35.           0  sorts (memory)    
  36.           0  sorts (disk)    
  37.           2  rows processed    
SQL> select empno from emp where EMPNO>=7902;  
  
  
Execution Plan  
----------------------------------------------------------  
Plan hash value: 1567865628  
  
---------------------------------------------------------------------------  
| Id  | Operation        | Name   | Rows  | Bytes | Cost (%CPU)| Time     |  
---------------------------------------------------------------------------  
|   0 | SELECT STATEMENT |        |     2 |    26 |     2   (0)| 00:00:01 |  
|*  1 |  INDEX RANGE SCAN| PK_EMP |     2 |    26 |     2   (0)| 00:00:01 |  
---------------------------------------------------------------------------  
  
Predicate Information (identified by operation id):  
---------------------------------------------------  
  
   1 - access("EMPNO">=7902)  
  
Note  
-----  
   - dynamic sampling used for this statement  
  
  
Statistics  
----------------------------------------------------------  
          0  recursive calls  
          0  db block gets  
          2  consistent gets  
          0  physical reads  
          0  redo size  
        569  bytes sent via SQL*Net to client  
        492  bytes received via SQL*Net from client  
          2  SQL*Net roundtrips to/from client  
          0  sorts (memory)  
          0  sorts (disk)  
          2  rows processed

3) index full scan -- index global scan
Full index scans are only available in the CBO as otherwise we are unable to determine whether a full scan would be a good idea or not. We choose an index Full Scan when we have statistics that indicate that it is going to be more efficient than a Full table 
scan and a sort. For example we may do a Full index scan when we do an unbounded scan of an index and want the data to be ordered in the index order. 
  1. SQL> select empno from emp order by empno;    
  2.     
  3. 14 rows selected.    
  4.     
  5.     
  6. Execution Plan    
  7. ----------------------------------------------------------    
  8. Plan hash value: 179099197    
  9.     
  10. ---------------------------------------------------------------------------    
  11. | Id  | Operation        | Name   | Rows  | Bytes | Cost (%CPU)| Time     |    
  12. ---------------------------------------------------------------------------    
  13. |   0 | SELECT STATEMENT |        |    14 |   182 |     2   (0)| 00:00:01 |    
  14. |   1 |  INDEX FULL SCAN | PK_EMP |    14 |   182 |     2   (0)| 00:00:01 |    
  15. ---------------------------------------------------------------------------    
  16.     
  17. Note    
  18. -----    
  19.    - dynamic sampling used for this statement    
  20.     
  21.     
  22. Statistics    
  23. ----------------------------------------------------------    
  24.           4  recursive calls    
  25.           0  db block gets    
  26.          11  consistent gets    
  27.           0  physical reads    
  28.           0  redo size    
  29.         676  bytes sent via SQL*Net to client    
  30.         492  bytes received via SQL*Net from client    
  31.           2  SQL*Net roundtrips to/from client    
  32.           0  sorts (memory)    
  33.           0  sorts (disk)    
  34.          14  rows processed   
SQL> select empno from emp order by empno;  
  
14 rows selected.  
  
  
Execution Plan  
----------------------------------------------------------  
Plan hash value: 179099197  
  
---------------------------------------------------------------------------  
| Id  | Operation        | Name   | Rows  | Bytes | Cost (%CPU)| Time     |  
---------------------------------------------------------------------------  
|   0 | SELECT STATEMENT |        |    14 |   182 |     2   (0)| 00:00:01 |  
|   1 |  INDEX FULL SCAN | PK_EMP |    14 |   182 |     2   (0)| 00:00:01 |  
---------------------------------------------------------------------------  
  
Note  
-----  
   - dynamic sampling used for this statement  
  
  
Statistics  
----------------------------------------------------------  
          4  recursive calls  
          0  db block gets  
         11  consistent gets  
          0  physical reads  
          0  redo size  
        676  bytes sent via SQL*Net to client  
        492  bytes received via SQL*Net from client  
          2  SQL*Net roundtrips to/from client  
          0  sorts (memory)  
          0  sorts (disk)  
         14  rows processed

4) index fast full scan -- Fast global scan of the index, often without order by
Scans all the block in the index, Rows are not returned in sorted order, Introduced in 7.3 and requires V733_PLANS_ENABLED=TRUE and CBO, may be hinted using INDEX_FFS hint, uses multiblock i/o, can be executed in parallel, can be used to access second column 
of concatenated indexes. This is because we are selecting all of the index. 
  1. SQL> select empno from emp;    
  2.     
  3. 14 rows selected.    
  4.     
  5.     
  6. Execution Plan    
  7. ----------------------------------------------------------    
  8. Plan hash value: 366039554    
  9.     
  10. -------------------------------------------------------------------------------    
  11. | Id  | Operation            | Name   | Rows  | Bytes | Cost (%CPU)| Time     |    
  12. -------------------------------------------------------------------------------    
  13. |   0 | SELECT STATEMENT     |        |    14 |   182 |     2   (0)| 00:00:01 |    
  14. |   1 |  INDEX FAST FULL SCAN| PK_EMP |    14 |   182 |     2   (0)| 00:00:01 |    
  15. -------------------------------------------------------------------------------    
  16.     
  17. Note    
  18. -----    
  19.    - dynamic sampling used for this statement    
  20.     
  21.     
  22. Statistics    
  23. ----------------------------------------------------------    
  24.           4  recursive calls    
  25.           0  db block gets    
  26.          13  consistent gets    
  27.           0  physical reads    
  28.           0  redo size    
  29.         676  bytes sent via SQL*Net to client    
  30.         492  bytes received via SQL*Net from client    
  31.           2  SQL*Net roundtrips to/from client    
  32.           0  sorts (memory)    
  33.           0  sorts (disk)    
  34.          14  rows processed   
SQL> select empno from emp;  
  
14 rows selected.  
  
  
Execution Plan  
----------------------------------------------------------  
Plan hash value: 366039554  
  
-------------------------------------------------------------------------------  
| Id  | Operation            | Name   | Rows  | Bytes | Cost (%CPU)| Time     |  
-------------------------------------------------------------------------------  
|   0 | SELECT STATEMENT     |        |    14 |   182 |     2   (0)| 00:00:01 |  
|   1 |  INDEX FAST FULL SCAN| PK_EMP |    14 |   182 |     2   (0)| 00:00:01 |  
-------------------------------------------------------------------------------  
  
Note  
-----  
   - dynamic sampling used for this statement  
  
  
Statistics  
----------------------------------------------------------  
          4  recursive calls  
          0  db block gets  
         13  consistent gets  
          0  physical reads  
          0  redo size  
        676  bytes sent via SQL*Net to client  
        492  bytes received via SQL*Net from client  
          2  SQL*Net roundtrips to/from client  
          0  sorts (memory)  
          0  sorts (disk)  
         14  rows processed

5) index skip scan -- index skip scan, where conditional column is often the leading column of a non-index
Index skip scan finds rows even if the column is not the leading column of a concatenated index. It skips the first column(s) during the search. 
  1. SQL> create index i_emp on emp(empno, ename);    
  2.     
  3. Index created.    
  4. SQL> select /*+ index_ss(emp i_emp)*/ job from emp where ename='SMITH';    
  5.     
  6.     
  7. Execution Plan    
  8. ----------------------------------------------------------    
  9. Plan hash value: 98078853    
  10.     
  11. -------------------------------------------------------------------------------------    
  12. | Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |    
  13. -------------------------------------------------------------------------------------    
  14. |   0 | SELECT STATEMENT            |       |     1 |    13 |     5   (0)| 00:00:01 |    
  15. |   1 |  TABLE ACCESS BY INDEX ROWID| EMP   |     1 |    13 |     5   (0)| 00:00:01 |    
  16. |*  2 |   INDEX SKIP SCAN           | I_EMP |     1 |       |     4   (0)| 00:00:01 |    
  17. -------------------------------------------------------------------------------------    
  18.     
  19. Predicate Information (identified by operation id):    
  20. ---------------------------------------------------    
  21.     
  22.    2 - access("ENAME"='SMITH')    
  23.        filter("ENAME"='SMITH')    
  24.     
  25. Note    
  26. -----    
  27.    - dynamic sampling used for this statement    
  28.     
  29.     
  30. Statistics    
  31. ----------------------------------------------------------    
  32.           5  recursive calls    
  33.           0  db block gets    
  34.          11  consistent gets    
  35.           0  physical reads    
  36.           0  redo size    
  37.         513  bytes sent via SQL*Net to client    
  38.         492  bytes received via SQL*Net from client    
  39.           2  SQL*Net roundtrips to/from client    
  40.           0  sorts (memory)    
  41.           0  sorts (disk)    
  42.           1  rows processed    
SQL> create index i_emp on emp(empno, ename);  
  
Index created.  
SQL> select /*+ index_ss(emp i_emp)*/ job from emp where ename='SMITH';  
  
  
Execution Plan  
----------------------------------------------------------  
Plan hash value: 98078853  
  
-------------------------------------------------------------------------------------  
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |  
-------------------------------------------------------------------------------------  
|   0 | SELECT STATEMENT            |       |     1 |    13 |     5   (0)| 00:00:01 |  
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP   |     1 |    13 |     5   (0)| 00:00:01 |  
|*  2 |   INDEX SKIP SCAN           | I_EMP |     1 |       |     4   (0)| 00:00:01 |  
-------------------------------------------------------------------------------------  
  
Predicate Information (identified by operation id):  
---------------------------------------------------  
  
   2 - access("ENAME"='SMITH')  
       filter("ENAME"='SMITH')  
  
Note  
-----  
   - dynamic sampling used for this statement  
  
  
Statistics  
----------------------------------------------------------  
          5  recursive calls  
          0  db block gets  
         11  consistent gets  
          0  physical reads  
          0  redo size  
        513  bytes sent via SQL*Net to client  
        492  bytes received via SQL*Net from client  
          2  SQL*Net roundtrips to/from client  
          0  sorts (memory)  
          0  sorts (disk)  
          1  rows processed

3.Rowid Physical ID Scan
This is the quickest access method available.Oracle retrieves the specified block and extracts the rows it is interested in.  
--Rowid scan is the fastest way to access data
  1. SQL> select * from emp where rowid='AAAjFUAAEAAABZ1AAM';    
  2.     
  3.     
  4. Execution Plan    
  5. ----------------------------------------------------------    
  6. Plan hash value: 1116584662    
  7.     
  8. -----------------------------------------------------------------------------------    
  9. | Id  | Operation                  | Name | Rows  | Bytes | Cost (%CPU)| Time     |    
  10. -----------------------------------------------------------------------------------    
  11. |   0 | SELECT STATEMENT           |      |     1 |    99 |     1   (0)| 00:00:01 |    
  12. |   1 |  TABLE ACCESS BY USER ROWID| EMP  |     1 |    99 |     1   (0)| 00:00:01 |    
  13. -----------------------------------------------------------------------------------    
  14.     
  15.     
  16. Statistics    
  17. ----------------------------------------------------------    
  18.           1  recursive calls    
  19.           0  db block gets    
  20.           1  consistent gets    
  21.           0  physical reads    
  22.           0  redo size    
  23.         983  bytes sent via SQL*Net to client    
  24.         492  bytes received via SQL*Net from client    
  25.           2  SQL*Net roundtrips to/from client    
  26.           0  sorts (memory)    
  27.           0  sorts (disk)    
  28.           1  rows processed    
SQL> select * from emp where rowid='AAAjFUAAEAAABZ1AAM';  
  
  
Execution Plan  
----------------------------------------------------------  
Plan hash value: 1116584662  
  
-----------------------------------------------------------------------------------  
| Id  | Operation                  | Name | Rows  | Bytes | Cost (%CPU)| Time     |  
-----------------------------------------------------------------------------------  
|   0 | SELECT STATEMENT           |      |     1 |    99 |     1   (0)| 00:00:01 |  
|   1 |  TABLE ACCESS BY USER ROWID| EMP  |     1 |    99 |     1   (0)| 00:00:01 |  
-----------------------------------------------------------------------------------  
  
  
Statistics  
----------------------------------------------------------  
          1  recursive calls  
          0  db block gets  
          1  consistent gets  
          0  physical reads  
          0  redo size  
        983  bytes sent via SQL*Net to client  
        492  bytes received via SQL*Net from client  
          2  SQL*Net roundtrips to/from client  
          0  sorts (memory)  
          0  sorts (disk)  
          1  rows processed

 
b. Operators
1.sort -- Sort, resource intensive
There are a number of different operations that promote sorts: 
(1)order by clauses (2)group by (3)sort merge join - These three result in a sort operation

2.filter -- Filters, such as not in, min functions, etc. are easily generated
Has a number of different meanings, used to indicate partition elimination, may also indicate an actual filter step where one row source is filtering, another, functions such as min may introduce filter steps into query plans.

3.view -- Views, mostly generated from inline views (possibly deep into view base tables)
When a view cannot be merged into the main query you will often see a projection view operation. This indicates that the 'view' will be selected from directly as opposed to being broken down into joins on the base tables. A number of constructs make a view 
non mergeable. Inline views are also non mergeable. 

  1. SQL> select ename,tot from emp,(select empno,sum(empno) tot from emp group by empno) tmp where emp.empno = tmp.empno;    
  2.     
  3. 14 rows selected.    
  4.     
  5.     
  6. Execution Plan    
  7. ----------------------------------------------------------    
  8. Plan hash value: 138960760    
  9.     
  10. -----------------------------------------------------------------------------------------    
  11. | Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    
  12. -----------------------------------------------------------------------------------------    
  13. |   0 | SELECT STATEMENT             |          |    14 |   644 |     4  (25)| 00:00:01 |    
  14. |   1 |  MERGE JOIN                  |          |    14 |   644 |     4  (25)| 00:00:01 |    
  15. |   2 |   TABLE ACCESS BY INDEX ROWID| EMP      |    14 |   280 |     2   (0)| 00:00:01 |    
  16. |   3 |    INDEX FULL SCAN           | PK_EMPNO |    14 |       |     1   (0)| 00:00:01 |    
  17. |*  4 |   SORT JOIN                  |          |    14 |   364 |     2  (50)| 00:00:01 |    
  18. |   5 |    VIEW                      |          |    14 |   364 |     1   (0)| 00:00:01 |    
  19. |   6 |     HASH GROUP BY            |          |    14 |   182 |     1   (0)| 00:00:01 |    
  20. |   7 |      INDEX FULL SCAN         | PK_EMPNO |    14 |   182 |     1   (0)| 00:00:01 |    
  21. -----------------------------------------------------------------------------------------    
  22.     
  23. Predicate Information (identified by operation id):    
  24. ---------------------------------------------------    
  25.     
  26.    4 - access("EMP"."EMPNO"="TMP"."EMPNO")    
  27.        filter("EMP"."EMPNO"="TMP"."EMPNO")    
  28.     
  29. Note    
  30. -----    
  31.    - dynamic sampling used for this statement    
  32.     
  33.     
  34. Statistics    
  35. ----------------------------------------------------------    
  36.          43  recursive calls    
  37.           0  db block gets    
  38.          61  consistent gets    
  39.           0  physical reads    
  40.           0  redo size    
  41.         821  bytes sent via SQL*Net to client    
  42.         492  bytes received via SQL*Net from client    
  43.           2  SQL*Net roundtrips to/from client    
  44.           5  sorts (memory)    
  45.           0  sorts (disk)    
  46.          14  rows processed    
SQL> select ename,tot from emp,(select empno,sum(empno) tot from emp group by empno) tmp where emp.empno = tmp.empno;  
  
14 rows selected.  
  
  
Execution Plan  
----------------------------------------------------------  
Plan hash value: 138960760  
  
-----------------------------------------------------------------------------------------  
| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |  
-----------------------------------------------------------------------------------------  
|   0 | SELECT STATEMENT             |          |    14 |   644 |     4  (25)| 00:00:01 |  
|   1 |  MERGE JOIN                  |          |    14 |   644 |     4  (25)| 00:00:01 |  
|   2 |   TABLE ACCESS BY INDEX ROWID| EMP      |    14 |   280 |     2   (0)| 00:00:01 |  
|   3 |    INDEX FULL SCAN           | PK_EMPNO |    14 |       |     1   (0)| 00:00:01 |  
|*  4 |   SORT JOIN                  |          |    14 |   364 |     2  (50)| 00:00:01 |  
|   5 |    VIEW                      |          |    14 |   364 |     1   (0)| 00:00:01 |  
|   6 |     HASH GROUP BY            |          |    14 |   182 |     1   (0)| 00:00:01 |  
|   7 |      INDEX FULL SCAN         | PK_EMPNO |    14 |   182 |     1   (0)| 00:00:01 |  
-----------------------------------------------------------------------------------------  
  
Predicate Information (identified by operation id):  
---------------------------------------------------  
  
   4 - access("EMP"."EMPNO"="TMP"."EMPNO")  
       filter("EMP"."EMPNO"="TMP"."EMPNO")  
  
Note  
-----  
   - dynamic sampling used for this statement  
  
  
Statistics  
----------------------------------------------------------  
         43  recursive calls  
          0  db block gets  
         61  consistent gets  
          0  physical reads  
          0  redo size  
        821  bytes sent via SQL*Net to client  
        492  bytes received via SQL*Net from client  
          2  SQL*Net roundtrips to/from client  
          5  sorts (memory)  
          0  sorts (disk)  
         14  rows processed


4.partition view -- Partition view
Partition views are a legacy technology that were superceded by the partitioning option. This section of the article is provided as reference for such legacy systems.
3. Let's look at the statistics section again
  1. SQL> set autotrace traceonly;    
  2. SQL> select count(*) from emp;    
  3.     
  4.     
  5. Execution Plan    
  6. ----------------------------------------------------------    
  7. Plan hash value: 2083865914    
  8.     
  9. -------------------------------------------------------------------    
  10. | Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |    
  11. -------------------------------------------------------------------    
  12. |   0 | SELECT STATEMENT   |      |     1 |     3   (0)| 00:00:01 |    
  13. |   1 |  SORT AGGREGATE    |      |     1 |            |          |    
  14. |   2 |   TABLE ACCESS FULL| EMP  |    14 |     3   (0)| 00:00:01 |    
  15. -------------------------------------------------------------------    
  16.     
  17. Note    
  18. -----    
  19.    - dynamic sampling used for this statement    
  20.     
  21. Statistics    
  22. ----------------------------------------------------------    
  23.           5  recursive calls  (Home Calls)    
  24.           0  db block gets  (Number of blocks read from disk,That is, throughupdate/delete/select for updateNumber of Reads)    
  25.          15  consistent gets (Number of blocks read from memory,That is, by not takingfor updateOfselect Number of Reads)    
  26.           0  physical reads (Physical Read - Number of data blocks read from disk,Generally speaking'consistent gets' + 'db block gets')    
  27.           0  redo size (Redo Number - Execute SQL The size of the redo log generated during the process)    
  28.         515  bytes sent via SQL*Net to client    
  29.         492  bytes received via SQL*Net from client    
  30.           2  SQL*Net roundtrips to/from client    
  31.           0  sorts (memory) (Sorting occurring in memory)    
  32.           0  sorts (disk) (Sorting that occurs on the hard disk)    
  33.           1  rows processed    
SQL> set autotrace traceonly;  
SQL> select count(*) from emp;  
  
  
Execution Plan  
----------------------------------------------------------  
Plan hash value: 2083865914  
  
-------------------------------------------------------------------  
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |  
-------------------------------------------------------------------  
|   0 | SELECT STATEMENT   |      |     1 |     3   (0)| 00:00:01 |  
|   1 |  SORT AGGREGATE    |      |     1 |            |          |  
|   2 |   TABLE ACCESS FULL| EMP  |    14 |     3   (0)| 00:00:01 |  
-------------------------------------------------------------------  
  
Note  
-----  
   - dynamic sampling used for this statement  
  
Statistics  
----------------------------------------------------------  
          5  recursive calls  (Home Calls)  
          0  db block gets  (Number of blocks read from disk,That is, through update/delete/select for update Number of Reads)  
         15  consistent gets (Number of blocks read from memory,That is, by not taking for update Of select Number of Reads)  
          0  physical reads (Physical Read - Number of data blocks read from disk,Generally speaking'consistent gets' + 'db block gets')  
          0  redo size (Redo Number - Execute SQL The size of the redo log generated during the process)  
        515  bytes sent via SQL*Net to client  
        492  bytes received via SQL*Net from client  
          2  SQL*Net roundtrips to/from client  
          0  sorts (memory) (Sorting occurring in memory)  
          0  sorts (disk) (Sorting that occurs on the hard disk)  
          1  rows processed

Description: Cost=(Single block I/O cost+ Multiblock I/O cost+; CPU cost)/sreadtim

Sequence Number Column Name explain
1 db block gets Number of block s read from buffer cache
2 consistent gets Number of block s of undo data read from buffer cache
3 physical reads Number of block s read from disk
4 redo siz Size of redo generated by DML
5 sorts (memory) Sort amount executed in memory
6 sorts (disk) The amount of sorting performed on disk

Posted by wpt394 on Fri, 24 May 2019 10:10:29 -0700

Hot Keywords

©2024 Programmer Group