SQL Execution Plan

Getting an Execution Plan report

SET AUTOTRACE ON ; Reporting only on the optimizer execution plan and SQL statement execution stats.

SET AUTOTRACE ON EXP ; Only active on execution plans and not on query results.

SET AUTOTRACE ON STAT ; Report on statistical information.

SET AUTOTRACE ON TRACEONLY ; Execution plan and statistics.

SET AUTOTRACE ON EXPLAIN ; Reporting only on the optimizer execution plan.

To use "SET AUTOTRACE ON" it may be necessary to run the following script (as a sys user)

@/u01/app/oracle/product/10.2.0/db_1/sqlplus/admin/plustrce.sql
grant plustrace to omar;

1 2	@/u01/app/oracle/product/10.2.0/db_1/sqlplus/admin/plustrce.sql grant plustrace to omar;

as username omar

@/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/utlxplan.sql

1	@/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/utlxplan.sql

The following error output could be due to user privileges. As a solution, temporarily grant privileges and later revoke them.

SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report

1 2	SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled SP2-0611: Error enabling STATISTICS report

Example

set timing on;
 set autotrace traceonly explain;
 select * from omar.emp;
Elapsed: 00:00:00.02
Execution Plan
--------------------------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------

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

--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 30000 | 761K| 55 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EMP | 30000 | 761K| 55 (0)| 00:00:01 |

set timing on;

set autotrace traceonly explain;

select * from omar.emp;

Elapsed: 00:00:00.02

Execution Plan

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

Plan hash value: 3956160932

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

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

| 0 | SELECT STATEMENT | | 30000 | 761K| 55 (0)| 00:00:01 |

| 1 | TABLE ACCESS FULL| EMP | 30000 | 761K| 55 (0)| 00:00:01 |

Changes made using queries or changes made in the indexes take effect after clearing the shared pool and the buffer cache.

alter system flush shared_pool;
alter system flush buffer_cache;

1 2	alter system flush shared_pool; alter system flush buffer_cache;

(A feature that was introduced with release 10G, empties the buffer cache within the SGA)

It’s necessary use the bind variable to make effective use of the Library Cache.
The following query doesn’t use a bind variable.

select count(*) from emp
where personel_id = 5

1 2	select count(*) from emp where personel_id = 5

A query that does use a bind variable.

select count(*) from emp
where personel_id = :x1

1 2	select count(*) from emp where personel_id = :x1

THe following syntax shows how to use a bind variable with SQL*Plus.

variable x1 number;
variable y1 varchar2;

execute :x1 := 5;
execute :y1 := istanbul;

variable x1 number;

variable y1 varchar2;

execute :x1 := 5;

execute :y1 := istanbul;