How to use Autotrace in Oracle

1, installation

Run script with sys user Utlxplan.sql

The script to build this table is: (UNIX: $ORACLE _home/rdbms/admin, Windows:%oracle_home%\rdbms\admin) ultxplan.sql.

Sql> connect sys/sys@colm2 as SYSDBA;

Sql> @c:\oracle\ora92\rdbms\admin\utlxplan.sql;

sql> create public synonym plan_table for plan_table; --Establish synonyms (9i)

Sql> Grant all on plan_table to public; --Authorize all users

To create a role plustrace in the database, run the script plustrce.sql with the SYS user, which is created in the directory (UNIX: $ORACLE _home/sqlplus/admin, Windows:%oracle_ Home%\sqlplus\admin);

Sql> @c:\oracle\ora92\sqlplus\admin\plustrce.sql;

Then the character Plustrace is granted to the user who needs autotrace;

Sql>grant plustrace to public;

After the above steps are set, you can use Autotrace in Sql*plus.

2. Use

It's easy to use, just use a single command.

Sql>set autotrace on;

*autotrace function can only be used in Sql*plus

3, some other methods of use

(1) The total execution time of the statement is obtained in the Sqlplus

Sql> set timing on;

(2) Show only execution plan--(will execute the statement to get the result)

Sql>set Autotrace on explain

Like what:

Sql> Select COUNT (*) from test;

COUNT (*)

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

4

Execution Plan

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

0 SELECT statement ptimitzer=choose (cost=3 card=1)

1 0 Sort (aggregate)

2 1 partition range (All)

3 2 Table access (full) ' T_test ' (cost=3 card=900)