Configure AutoTrace to monitor SQL Execution plans in Oracle

Obtaining execution plans and execution costs and writing the optimal SQL statements are required by each Oracle Developer and dba. Dbarole by default after Oracle11g

Obtaining execution plans and execution costs and writing the optimal SQL statements are required by each Oracle Developer and dba. After Oracle 11g, the default role is dba role.

Obtaining execution plans and execution costs and writing the optimal SQL statements are required by each Oracle Developer and dba.

After Oracle 11g is passed, auto trace is automatically enabled for dba role by default?

Configure autotrace:

Enter $ ORACLE_HOME/rdbms/admin

Go to sqlplus,

Run:

SQL> @ utlxplan

Create an analysis table. For ease of operation, we can create a synonym

SQL> create public synonym plan_table for plan_table;

We can grant this table permission to the users we want to give, or public so that anyone can use SQL * plus process tracking. In this way, no user needs to follow their own schedule.

SQL> grant all on plan to public;

Create the role of plustrace

Enter % ORACLE_HOME %/sqlplus/admin (linux, unix is $ ORACLE_HOME/sqlplus/admin)

Run

SQL> @ plustrce. SQL

We can also grant the permission to public.

SQL> grant plustrace to public;

Autotrace operations:

After the installation is successful, an autotrace report is automatically generated. We can use a parameter to display the execution time:

SQL> set timing on

Autotrace is disabled by default, and its detailed startup is shown below:

Set autotrace off: No AUTOTRACE report is generated. This is the default setting.
Set autotrace on explain: the AUTOTRACE report only displays the optimizer execution path.
Set autotrace on statistics: The AUTOTRACE report only displays the execution STATISTICS of SQL statements.
Set autotrace on: The AUTOTRACE report includes both the optimizer execution path and SQL statement execution statistics.
Set autotrace traceonly: similar to set autotrace on, but the user's query output is not displayed.

This is an execution plan after autotrace is enabled.

SQL> insert into t values (2, '2 ');

One row has been created.

Used time: 00: 00: 00.00

Execution Plan
----------------------------------------------------------

-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
-------------------------------------------------------------------------
| 0 | insert statement | 1 | 100 | 1 (0) | 00:00:01 |
-------------------------------------------------------------------------

Statistics
----------------------------------------------------------
1 recursive cballs
1 db block gets
1 consistent gets
0 physical reads
308 redo size
669 bytes sent via SQL * Net to client
564 bytes encoded ed via SQL * Net from client
4 SQL * Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed

The following is an analysis of the statistics in the execution plan:

Project

Description

Remarks

Recursive cballs

Number of SQL statements that are recursively called. Oracle sometimes generates many additional SQL statements when executing this SQL statement, which becomes a recursive call;

Db block gets

Logical read, read from data buffer cache; read in current mode;

Consistent gets

Logical read: reads data in consistent read mode;

Physical reads

Physical read cost;

Redo size

Size of redo logs generated

Bytes sent via SQL * Net to client

The number of bytes passed into the client using SQL * net;

Bytes encoded ed via SQL * Net from client

The number of bytes that SQL * net sends to the client;

SQL * Net roundtrips to/from client

Sorts (memory)

Memory sorting space usage;

Sorts (disk)

Usage of sorting space in physical storage;

If the memory space is insufficient, the disk space will be used;

Rows processed

Automee is a frequently used performance evaluation tool that can help solve many problems.

,