Oracle Execution Plan--oracle How to enable execution plan

Source: Internet
Author: User
Tags sorts sqlplus

Autotrace is an sql*plus feature that automatically tracks the generation of an execution plan for SQL statements and provides statistics about the processing of the statement. Sql*plus Autotrace can be used instead of SQL Trace, the benefit of autotrace is that you do not have to format the trace file, and it automatically displays the execution plan for the SQL statement. However, Autotrace parses and executes statements, while explain plan parses only statements. Using autotrace does not produce trace files.

1 Enable Autotrace

1.1 with SYS user, run Utlxplan.sql

Sqlplus/as SYSDBA

sql> @ $ORACLE _home/rdbms/admin/utlxplan.sql

1.2 Creating Plustrace roles by executing plustrce.sql scripts

sql> @ $ORACLE _home/sqlplus/admin/plustrce.sql

1.3 Licensing

Grant the Plustrace role to the current user (a user without DBA role), or grant plustrace to public;

2 Setting the Autotrace command

Serial number

Command

Explain

1

SET AUTOTRACE OFF

This is the default value, which is to turn off Autotrace

2

SET AUTOTRACE on

Generating result sets and interpreting plans and listing statistics

3

SET AUTOTRACE on EXPLAIN

Show result set and interpret schedule do not show statistics

4

SET AUTOTRACE traceonly

Show interpreting schedules and statistics, although executing the statement but you will not see the result set

5

SET AUTOTRACE traceonly STATISTICS

Show Statistics only

Eg:set AUTOTRACE on, set timing on, alter session SET Time_statistics=true;

The following is for reference only

3 Autotrace The meaning of the columns of the implementation plan

Serial number

Column Name

Explain

1

Id_plus_exp

Line number of each step

2

Parent_id_plus_exp

The level number of the parent for each step

3

Plan_plus_exp

Every step of the actual

4

Object_node_plus_exp

Dblink or parallel queries are used

4 Autotrace Statistics Common column interpretation

Serial number

Column Name

Explain

1

DB Block gets

The number of blocks read from the buffer cache

2

Consistent gets

Number of block of undo data read from buffer cache

3

Physical Reads

The number of blocks read from disk

4

Redo Size

The size of the redo generated by DML

5

Sorts (memory)

The sort amount that is executed in memory

6

Sorts (disk)

The amount of sorting performed on the disk

Related Article

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.