Apply AutoTrace Report in SQL * Plus
Author: Liuying Bo
Time: 2004-1-12
Mail: liuyingbo@126.com, please correct
Reprint, please indicate the source and the author
In SQL * Plus, when you successfully perform a DML statement, such as SELECT, DELETE, UPDATE, INSERT, you can automatically get a report through the execution statistics of SQL optimizer and statements. This report is very useful for performance monitoring and tuning of DML statements. This report is the AutoTrace report to this article.
Configuring the autotrace report
You can configure AutoTrace reports through the following autotrace system variables. As shown in the table:
Table Autotrace Settings
Autotrace Setting
Result
Set autotrace off
You cannot get an AutoTrace report. This is the default.
Set autotrace on explain
Just display the AutoTrace report for the optimizer execution plan
Set autotrace on statistics
Just display the autotrace report for the statistical result of the SQL statement
Set autotrace on
AutoTrace report included in the above two
Set autotrace traceonly
Similar to Set Autotrace ON, all statistics and data are in, but not printing
In fact, us usually should be set autotrace on, set autotrace off, who will care more about a little AUTOTRACE report! J
Install AutoTrace Report (Setups Required for the Autotrace Report)
To use this feature, the user must be given the PLUSTRACE role, and the PLUSTRACE role requires DBA to give it. In addition, the user must create a Plan_Table table.
I. First create a PLUSTRACE role and assign DBA:
Connect Sys / Sys's Password As Sysdba
@ $ Oracle_home / sqlplus / admin / plustrce.sql
Ii. Employment is limited to users
Connect / as sysdba
Grant Plustrace to User (pre-adhered user name);
This way, you can set the display or not of the autotrace report under this user.
Two aspects involved in the AutoTrace report
I. Executive plan for SQL statement
The execution plan is the Query execution order of the SQL optimizer execute statement, and each line will have a line number, this line number is continuous.
II. SQL statement database statistics
Database statistics are system resources required by the execution of the execution of the server record, mainly including the content of the table below.
Database statistic name
Description
recursive calls Number of recursive calls generated at both the user and system level. Oracle maintains tables used for internal processing. When Oracle needs to make a change to these tables, it internally generates an internal SQL statement, which in turn generates a recursive call. db block gets Number of times a CURRENT block was requested. consistent gets Number of times a consistent read was requested for a block. physical reads Total number of data blocks read from disk. This number equals the value of "physical reads direct" plus all reads into buffer cache. redo size Total amount of redo generated in bytes. bytes sent via SQL * Net to client Total number of bytes sent to the client from the foreground processes. bytes received via SQL * Net from client Total number of bytes received from The Client ORACLE NET. SQL * NET ROUNDTRIPS TO / FROM Clom Clom Clom Clom Clom Clom Clom Clom Clism Total Number of Oracle Net Messages Sent to and Received from The Client. sorts (Memory) Number of Sort Operations That Were Performed Completely in Memory and Did Not Require Any Disk Writes. Sorts (Disk) Number of Sort Operations That Required At Least One Disk Write. Rows Processed Number of Rows Processed During The Operation. Simple Usage:
SQL> Show User
User is "sys"
(Note: The current SYSDBA user)
(Note: Create a Plustrace role and assign DBA)
SQL> Start $ Oracle_Home / Sqlplus / Admin / Plustrce.sql
(Note: START is equivalent to @)
SQL> Drop Role Plustrace;
Drop role plustrace
*
Error At Line 1:
ORA-01919: Role 'Plustrace' Does Not Exist
SQL> CREATE ROLE PLUSTRACE;
Role created.
SQL>
SQL> Grant SELECT ON V_ $ SESSSTAT TO PLUSTRACE;
Grant succeeded.
SQL> Grant SELECT ON V_ $ STATNAME to PLUSTRACE;
Grant succeeded.sql>
SQL> Grant SELECT ON V_ $ Session to Plustrace;
Grant succeeded.
SQL> Grant Plustrace to DBA with admin/
Grant succeeded.
SQL>
SQL> SET Echo Off
(Note: Assignment Plustrace gives the required user, the user is BILL)
SQL> Grant Plustrace to Bill;
Grant succeeded.
(Note: Next, use Bill users to test autotrace
SQL> Connect Bill / Bill's Password;
Connected.
SQL> Show User
User is "Bill"
(Note: In order to see some, create a test table test, then check the autotrace report)
SQL> CREATE TABLE TEST (ID Number (1));
Table created.
SQL> INSERT INTO TEST VALUES (1);
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> commit;
COMMIT COMPLETE.
SQL> SELECT *.
Id
------------
1
1
1
1
SQL> Set autotrace on
SQL> SELECT *.
Id
------------
1
1
1
1
Execution Plan
-------------------------------------------------- ------------
0 Select Statement Optimizer = Choose
1 0 Table Access (Full) of 'Test'
Statistics
-------------------------------------------------- ------------
0 Recursive Calls
0 DB Block Get
4 consistent gets
0 Physical READS
0 redo size
547 Bytes Sent Via SQL * Net to Client
655 BYtes Received Via SQL * Net from Client
2 SQL * NET ROUNDTRIPS TO / FROM Client
0 Sorts (Memory)
0 Sorts (Disk)
4 Rows Processed
Above we can see that when you set the set autotrace ON, perform the corresponding DML statement, there will be the corresponding autotrace report. Of course, our setting is the session level. In this way, we can analyze and tune the DML statements we executed according to the AutoTrace report!
reference:
Oracle's official document (OTN.Oracle.com)
