SQL Optimazition

Cost Based Optimizer (CBO)

This method is used if internal statistics are present. The CBO checks several possible execution plans and selects the one with the lowest cost, where cost relates to system resources. Cost-based optimization uses statistics stored with database objects to help evaluate which set of indexes and joins to follow. As long as you keep the statistics current, the optimizer would usually choose the fastest path possible. There are also some comments called optimizer hints. In Oracle 10g by default Cost Base Optimizer is used.

 
select value from v$parameter where name='optimizer_mode'

By default optimizer_mode set to CHOOSE, which means Cost Base Optimization.
 

Plan Table

The explain plan process stores data in the PLAN_TABLE.

 

SQL> CONN sys/password AS SYSDBA
Connected
SQL> @$ORACLE_HOME/rdbms/admin/utlxplan.sql
SQL> GRANT ALL ON sys.plan_table TO public;

AUTOTRACE Parameter to view the SQL Execution Plane

SET TIMING ON
SET AUTOTRACE ON

SELECT e.ename, d.dname
FROM   scott.emp e, scott.dept d
WHERE  e.deptno = d.deptno
  AND  e.ename  = 'KING';

Autotrace Enabled
Shows the execution plan as well as statistics of the statement.
ENAME      DNAME         
---------- --------------
KING       ACCOUNTING    

1 rows selected

Plan hash value: 351108634

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |     1 |    22 |     4   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |         |     1 |    22 |     4   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL          | EMP     |     1 |     9 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    13 |     1   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

   2 - filter("E"."ENAME"='KING')
   4 - access("E"."DEPTNO"="D"."DEPTNO")

   Statistics
-----------------------------------------------------------
               0  recursive calls
               0  db block gets
               9  consistent gets
               0  physical reads
               0  redo size
             800  bytes sent via SQL*Net to client
             414  bytes received via SQL*Net from client
               4  SQL*Net roundtrips to/from client
               1  sorts (memory)
               0  sorts (disk)
180ms elapsed

 

Here Cost (%CPU) is (4+4+3+1+0) 12

 

View Execution Plan or EXPLAIN PLAN

EXPLAIN PLAN FOR
  SELECT e.ename, d.dname
  FROM   scott.emp e, scott.dept d
  WHERE  e.deptno = d.deptno
  AND  e.ename  = 'KING';

EXPLAIN PLAN succeeded.

Also get the output from utlxpls.sql or utlxplp.sql

Source : @$ORACLE_HOME/rdbms/admin/utlxpls.sql
Source : @$ORACLE_HOME/rdbms/admin/utlxplp.sql

We use the DBMS_XPLAN.DISPLAY function to display the execution plan

SET LINESIZE 130
SET PAGESIZE 0
SELECT *
FROM   TABLE(DBMS_XPLAN.DISPLAY);
 

Rule Based Optimizer (RBO)

This method is used if the server has no internal statistics relating to the objects referenced by the statement. This method is no longer recommended by Oracle and will be duplicated in future releases.

 

To change the optimization mode

Alter session set OPTIMIZER_MODE = RULE

SET TIMING ON
SET AUTOTRACE ON

SELECT e.ename, d.dname
FROM   scott.emp e, scott.dept d
WHERE  e.deptno = d.deptno
  AND  e.ename  = 'KING';

Autotrace Enabled
Shows the execution plan as well as statistics of the statement.
ENAME      DNAME          
---------- --------------
KING       ACCOUNTING    

1 rows selected

Plan hash value: 351108634

------------------------------------------------
| Id  | Operation                    | Name    |
------------------------------------------------
|   0 | SELECT STATEMENT             |         |
|   1 |  NESTED LOOPS                |         |
|*  2 |   TABLE ACCESS FULL          | EMP     |
|   3 |   TABLE ACCESS BY INDEX ROWID| DEPT    |
|*  4 |    INDEX UNIQUE SCAN         | PK_DEPT |
------------------------------------------------

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

   2 - filter("E"."ENAME"='KING')
   4 - access("E"."DEPTNO"="D"."DEPTNO")

Note
-----
   - rule based optimizer used (consider using cbo)

   Statistics
-----------------------------------------------------------
               0  recursive calls
               0  db block gets
               9  consistent gets
               0  physical reads
               0  redo size
             801  bytes sent via SQL*Net to client
             402  bytes received via SQL*Net from client
               4  SQL*Net roundtrips to/from client
               1  sorts (memory)
               0  sorts (disk)
290ms elapsed