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)
grant plustrace to omar;
as username omar
The following error output could be due to user privileges. As a solution, temporarily grant privileges and later revoke them.
SP2-0611: Error enabling STATISTICS report
SQL> set autotrace traceonly explain;
SQL> select * from omar.emp;
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 |
Changes made using queries or changes made in the indexes take effect after clearing the shared pool and the buffer cache.
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.
where personel_id = 5
A query that does use a bind variable.
where personel_id = :x1
THe following syntax shows how to use a bind variable with SQL*Plus.
variable y1 varchar2;
execute :x1 := 5;
execute :y1 := istanbul;