Autotrace in SQLPLUS
About Autotrace
Autotrace facility is used to see the execution plans
and gives statistics for the SQL statements.
The report is generated after successful SQL DML statements.
It is useful for monitoring and tuning the performance of these statements.
Controlling the Report
You can control the report by setting the AUTOTRACE system variable.
SET AUTOTRACE OFF
- No AUTOTRACE report is generated. This is the default.
SET AUTOTRACE ON EXPLAIN
- The AUTOTRACE report shows only the optimizer execution path.
SET AUTOTRACE ON STATISTICS
- The AUTOTRACE report shows only the SQL statement execution statistics.
SET AUTOTRACE ON
- The AUTOTRACE report includes both the optimizer execution path and the SQL statement execution statistics.
SET AUTOTRACE TRACEONLY
- Like SET AUTOTRACE ON, but suppresses the printing of the user’s query output, if any.
Execution Plan
The Execution Plan shows the SQL optimizer’s query execution path.
Each line of the Execution Plan has a sequential line number. SQL*Plus also displays the line number of the parent operation.
The Execution Plan consists of four columns displayed in the following order:
Column Name Description
ID_PLUS_EXP Shows the line number of each execution step.
PARENT_ID_PLUS_EXP Shows the relationship between each step and its
parent. This column is useful for large reports.
PLAN_PLUS_EXP Shows each step of the report.
OBJECT_NODE_PLUS_EXP Shows the database links or parallel query servers
used.
Timing SQL Commands
——————————————————————————–
Oracle provides a TIMING command for measuring the running time of SQL commands.
Then, Oracle will automatically display the elapsed wall-clock time for each SQL command you run subsequently. Note that timing data may be affected by external factors such as system load, etc.
set timing on;
set timing off;
Procedure
Step 1: Connected as the SYS database user
Run the PLUSTRACE.script on the path:
Linux : $ORACLE_HOME/sqlplus/admin
Windows : C:\oraclexe\app\oracle\product\10.2.0\server\RDBMS\ADMIN\
SQL> conn sys as sysdba
Enter password:
Connected.
SQL> @$ORACLE_HOME/sqlplus/admin/plustrce.sql
– Automatically running script
SQL>
SQL> drop role plustrace;
Role dropped.
SQL> create role plustrace;
Role created.
SQL>
SQL> grant select on v_$sesstat to plustrace;
Grant succeeded.
SQL> grant select on v_$statname to plustrace;
Grant succeeded.
SQL> grant select on v_$mystat to plustrace;
Grant succeeded.
SQL> grant plustrace to dba with admin option;
Grant succeeded.
SQL>
SQL> set echo off
– End of script
Step 2: Run the UTLXPLAN.SQL script on the path: $ORACLE_HOME/rdbms/admin
SQL> @utlxplan
SQL> @$ORACLE_HOME/rdbms/admin/utlxplan.sql
Table created.
Step 3: Now, we can use the autorace facility by using the
“set autotrace on;” command.
SQL> set timing on
SQL> set autotrace on
SQL> create table t as select owner, object_name, object_id from all_objects;
Table created.
Elapsed: 00:00:03.29
SQL> select * from t;
OWNER OBJECT_NAME OBJECT_ID
—————————— —————————— ———-
SYS DUAL 258
PUBLIC DUAL 259
SYS SYSTEM_PRIVILEGE_MAP 311
PUBLIC SYSTEM_PRIVILEGE_MAP 313
SYS TABLE_PRIVILEGE_MAP 314
…
5293 rows selected.
Elapsed: 00:00:12.89
Execution Plan
———————————————————-
Plan hash value: 1601196873
————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————–
| 0 | SELECT STATEMENT | | 5293 | 242K| 8 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| T | 5293 | 242K| 8 (0)| 00:00:01 |
————————————————————————–
Note
—–
- dynamic sampling used for this statement
Statistics
———————————————————-
68 recursive calls
0 db block gets
412 consistent gets
24 physical reads
0 redo size
168080 bytes sent via SQL*Net to client
4248 bytes received via SQL*Net from client
354 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
5293 rows processed
References :
Donald K. Burleson Oracle Tips Tracing an SQL statement
http://www.remote-dba.cc/oracle_tips_tracing_sql.htm
Timing SQL Commands
http://infolab.stanford.edu/~ullman/fcdb/oracle/or-nonstandard.html
Autotrace in SQLPLUS
http://asktom.oracle.com/tkyte/article1/autotrace.html
Oracle performance analysis – Autotrace workshop
http://www.bhatipoglu.com/entry.asp?id=18
Leave a Reply