一、启用autotrace前的准备工作
运行utlxplan.sql脚本创建PLAN_TABLE表(该脚本位于$ORACLE_HOME/rdbms/admin)
[oracle@oracle ~]$ cd $ORACLE_HOME/rdbms/admin
[oracle@oracle ~]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Aug 30 15:12:15 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> conn /as sysdba
Connected.
SQL> @utlxplan.sql
SQL> --u_plsql是创建的用户
SQL> grant all on plan_table to u_plsql;
SQL> exit;
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
运行plustrce.sql($ORACLE_HOME/sqlplus/admin)脚本创建plustrace角色
[oracle@oracle admin]$ cd $ORACLE_HOME/sqlplus/admin
[oracle@oracle ~]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Aug 30 15:12:15 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> conn /as sysdba
Connected.
SQL> @plustrce.sql
SQL> grant plustrace to u_plsql;
SQL> grant select any dictionary to u_plsql;
到此,开启autotrace的准备工作全部完成。
二、设置autotrace命令
语法:SET AUTOTRACE {OFF | ON | TRACEONLY} [EXPLAIN] [STATISTICS]
编号 命令 解释
-------- ----------------------------------- ----------------------------------
1 set autotrace off 此为默认值,即关闭autotrace
-------- ----------------------------------- ----------------------------------
2 set autotrace on 产生结果集和解释计划并列出统计
-------- ----------------------------------- ----------------------------------
3 set autotrace traceonly 显示解释计划和统计,但不产生结果集
-------- ----------------------------------- ----------------------------------
4 set autotrace on explain 产生结果集和解释计划
-------- ----------------------------------- ----------------------------------
5 set autotrace on statistics 产生结果集并列出统计
-------- ----------------------------------- ----------------------------------
6 set autotrace traceonly explain 仅产生解释计划
-------- ----------------------------------- ----------------------------------
7 set autotrace traceonly statistics 仅列出统计
-------- ----------------------------------- ----------------------------------
例如:
SQL> set autotrace traceonly explain
SQL> select * from t02;
Execution Plan
----------------------------------------------------------
Plan hash value: 2967456566
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 40 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| T02 | 2 | 40 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
SQL> set autotrace off
三、autotrace statistics常用列解释
序号 列名 解释
---- --------------- -------------------------------------------
1 db block gets 从buffer cache中读取的block的数量
---- --------------- -------------------------------------------
2 consistent gets 从buffer cache中读取的undo数据的block的数量
---- --------------- -------------------------------------------
3 physical reads 从磁盘读取的block的数量
---- --------------- -------------------------------------------
4 redo size DML生成的redo的大小
---- --------------- -------------------------------------------
5 sorts (memory) 在内存执行的排序量
---- --------------- -------------------------------------------
6 sorts (disk) 在磁盘上执行的排序量
---- --------------- -------------------------------------------
例如:
SQL> set autotrace traceonly statistics
SQL> select * from t02;
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
513 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
SQL> set autotrace off
运行utlxplan.sql脚本创建PLAN_TABLE表(该脚本位于$ORACLE_HOME/rdbms/admin)
[oracle@oracle ~]$ cd $ORACLE_HOME/rdbms/admin
[oracle@oracle ~]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Aug 30 15:12:15 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> conn /as sysdba
Connected.
SQL> @utlxplan.sql
SQL> --u_plsql是创建的用户
SQL> grant all on plan_table to u_plsql;
SQL> exit;
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
运行plustrce.sql($ORACLE_HOME/sqlplus/admin)脚本创建plustrace角色
[oracle@oracle admin]$ cd $ORACLE_HOME/sqlplus/admin
[oracle@oracle ~]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Aug 30 15:12:15 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> conn /as sysdba
Connected.
SQL> @plustrce.sql
SQL> grant plustrace to u_plsql;
SQL> grant select any dictionary to u_plsql;
到此,开启autotrace的准备工作全部完成。
二、设置autotrace命令
语法:SET AUTOTRACE {OFF | ON | TRACEONLY} [EXPLAIN] [STATISTICS]
编号 命令 解释
-------- ----------------------------------- ----------------------------------
1 set autotrace off 此为默认值,即关闭autotrace
-------- ----------------------------------- ----------------------------------
2 set autotrace on 产生结果集和解释计划并列出统计
-------- ----------------------------------- ----------------------------------
3 set autotrace traceonly 显示解释计划和统计,但不产生结果集
-------- ----------------------------------- ----------------------------------
4 set autotrace on explain 产生结果集和解释计划
-------- ----------------------------------- ----------------------------------
5 set autotrace on statistics 产生结果集并列出统计
-------- ----------------------------------- ----------------------------------
6 set autotrace traceonly explain 仅产生解释计划
-------- ----------------------------------- ----------------------------------
7 set autotrace traceonly statistics 仅列出统计
-------- ----------------------------------- ----------------------------------
例如:
SQL> set autotrace traceonly explain
SQL> select * from t02;
Execution Plan
----------------------------------------------------------
Plan hash value: 2967456566
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 40 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| T02 | 2 | 40 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
SQL> set autotrace off
三、autotrace statistics常用列解释
序号 列名 解释
---- --------------- -------------------------------------------
1 db block gets 从buffer cache中读取的block的数量
---- --------------- -------------------------------------------
2 consistent gets 从buffer cache中读取的undo数据的block的数量
---- --------------- -------------------------------------------
3 physical reads 从磁盘读取的block的数量
---- --------------- -------------------------------------------
4 redo size DML生成的redo的大小
---- --------------- -------------------------------------------
5 sorts (memory) 在内存执行的排序量
---- --------------- -------------------------------------------
6 sorts (disk) 在磁盘上执行的排序量
---- --------------- -------------------------------------------
例如:
SQL> set autotrace traceonly statistics
SQL> select * from t02;
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
513 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
SQL> set autotrace off