Set the autotrace of Sql*plus

Source: Internet
Author: User

AUTOTRACE is a tool in Sql*plus that shows the interpretation plan for the query being executed (explain plan) and the
Resources. The Autotrace tool is used extensively in this book.
There are more than one way to configure Autotrace, the following are the methods I use:
(1) CD [Oracle_home]/rdbms/admin;
(2) as System login Sql*plus;
(3) running @utlxplan;
(4) Run create public synonym plan_table for plan_table;
(5) Run grant all on plan_table to public.


If you prefer, you can replace public with a user in grant to public. By placing the plan_table as public,
Anyone can use sql*plus for tracking (which in my opinion is not a bad thing). This way, you don't need to install each user
Own schedule. Another option is to run @utlxplan separately in each of the modes that you want to use Autotrace.


The next step is to create and grant the Plustrace role:
(1) CD [Oracle_home]/sqlplus/admin;
(2) as SYS or SYSDBA login sql*plus;
(3) running @plustrce;
(4) Run grant Plustrace to public.
Again, if you wish, you can replace the public in the grant command with each user.


About Autotrace
You will automatically get a autotrace report that may list the execution path used by the SQL optimizer, and the execution statistics of the statement
Information. This report is generated when SQL DML (that is, select, DELETE, UPDATE, MERGE, and insert) statements are executed successfully. It
is helpful for monitoring and tuning the performance of these statements.

Control reports
You can control this report by setting the AUTOTRACE system variable:

? Set AUTOTRACE OFF: The AUTOTRACE report is not generated, which is the default setting.
? The SET AUTOTRACE on Explain:autotrace report shows only the optimizer execution path.
? The SET AUTOTRACE on Statistics:autotrace report displays only the execution statistics for the SQL statement.
? The SET AUTOTRACE on:autotrace report includes both the optimizer execution path and the execution statistics of the SQL statement.
? Set AUTOTRACE traceonly: This is similar to set AUTOTRACE on, but does not display the user's query output (if any).

The Art of oracle9i10g programming

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.