Using SQL*Plus Autotrace-Oracle Performance Tuning Tips at atoz help blog

Using SQL*Plus Autotrace

•If you’re using SQL*Plus you can take advantage of the  auto trace feature to have queries explained automatically.

 

•SQL*Plus will execute the query and display the execution plan following the results.

 

•If PLAN_TABLE not exists in schema use below script to create plan table

$ORACLE_HOME/rdbms/admin/utlxplan.sql

•E.g

  SQL> SET AUTOTRACE ON EXPLAIN

  SQL> SELECT animal_name FROM aquatic_animal

   ORDER BY animal_name;

ANIMAL_NAME

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

Batty

Bopper

Flipper

3 rows selected.

Execution Plan

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

0   SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=10

  Bytes=170)

10   SORT (ORDER BY) (Cost=3 Card=10 Bytes=170)

2      1   TABLE ACCESS (FULL) OF ‘AQUATIC_ANIMAL’ (Cost=1 Card=10   Bytes=170)

wSQL*Plus does execute the query. If a query generates a lot of I/O and consumes a lot of CPU, you won’t want to kick it off just to see the execution plan.

 

wIn that case use following :

  SQL> SET AUTOTRACE TRACEONLY EXPLAIN

wyou are through using autotrace, you can turn the feature off by issuing the

  SET AUTOTRACE OFF command.

Note : Provide your comments by clicking below options! Thanks ! :)

Tweet