注意；*autotrace功能只能在SQL*PLUS里使用
声明:本文为转载,如果有侵犯知识版权，请通知本人，本人将即刻停止侵权行为:
网址；http://blog.csdn.net/huanghui22/article/details/1326227
https://netfiles.uiuc.edu/jstrode/www/oraplus/plus_set_autotrace.html
http://www.cnblogs.com/chinhr/archive/2009/03/14/1412105.html
http://www.linuxidc.com/Linux/2012-06/62802.htm
http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_9010.htm#i2061796
http://docs.oracle.com/cd/B19306_01/server.102/b14357/ch8.htm#i1037226
explan plan的各个参数的解释:
http://docs.oracle.com/cd/B19306_01/server.102/b14211/ex_plan.htm
SET AUTOTRACE
Displays a report on the execution of successful SQL DML statements (SELECT, INSERT, UPDATE or DELETE).
Prerequisites:
| ♦ Must have PLUSTRCE role. Run $ORACLE_HOME/sqlplus/admin/plustrce.sql as SYS to create. |
| ♦ Must have PLAN_TABLE in user schema. Run $ORACLE_HOME/rdbms/admin/utlxplan.sql To set up for use by non-dba users: create public synonym plan_table for system.PLAN_TABLE and GRANT all on plan_table to public or have them set up a PLAN_TABLE in their own schema. |
| Syntax: | SET AUTOTRACE { OFF | ON | TRACEONLY } [EXPLAIN] [STATISTICS] |
| SET AUTOT OFF | ||
| No AUTOTRACE report is generated. This is the default. | ||
| SET AUTOT ON | ||
ON displays a trace report. Same as SET AUTOT ON EXP STAT The AUTOTRACE report includes data queried, the optimizer execution path and the SQL statement execution statistics. |
||
| SET AUTOT ON EXPLAIN | ||
| EXPLAIN shows the query execution path by performing an EXPLAIN PLAN. The AUTOTRACE report includes the data queried and only the optimizer execution path | ||
| SET AUTOT ON STATISTICS | ||
| Displays SQL statement statistics. The AUTOTRACE report includes the data queried and the SQL statement execution statistics. | ||
| SET AUTOT ON EXPLAIN STATISTICS | ||
| SET AUTOT TRACE | ||
TRACE is the same as ON but supresses the queried data. Same as entering SET AUTOT TRACE EXP STAT. The AUTOTRACE report includes data queried, the optimizer execution path and the SQL statement execution statistics. |
||
| SET AUTOT TRACE EXPLAIN | ||
| EXPLAIN shows the query execution path by performing an EXPLAIN PLAN. The AUTOTRACE report show the SQL statement execution statistics. | ||
| SET AUTOT TRACE STATISTICS | ||
| STATISTICS displays SQL statement statistics. The AUTOTRACE report show the SQL statement execution statistics. | ||
| SET AUTOT TRACE EXPLAIN STATISTICS | ||
http://download-west.oracle.com/docs/cd/A97630_01/server.920/a90842/ch9.htm#1006527 | Related Commands: | |
| EXPLAIN PLAN | |
| SHOW TIMING | Shows the value of a SQLPlus system variable(s) or current SQLPlus environment. |
| CLEAR TIMING | |
| SET TIMING | Reports the elapsed time to execute every query in milliseconds or 1/10 of a second. |
| Related Views: | |
| V$TIMER | |
| V$SESSION | Lists session information for each current session. |
| Related Packages: | |
| DBMS_UTILITY.GET_TIME |
执行计划:
执行计划的结果:
physical reads 物理读——执行SQL的过程中，从硬盘上读取的数据块个数
redo size 重做数——执行SQL的过程中，产生的重做日志的大小
bytes set via sql*net to client 通过sql*net发送给客户端的字节数
bytes received via sql*net from client 通过sql*net接受客户端的字节数
sorts(memory) 在内存中发生的排序
sorts(disk) 不能在内存中发生的排序，需要硬盘来协助
rows processed 结果的记录数
|
项目 |
含义 |
备注 |
|
recursive calls |
递归调用SQL的个数；Oracle在执行这个SQL的时候，有时候会生成很多额外的SQL语句，这个就成为递归调用； |
|
|
db block gets |
逻辑读，从数据buffer cache中读取；进行current模式读取； |
|
|
consistent gets |
逻辑读，进行一致读模式读取； |
|
|
physical reads |
物理读成本； |
|
|
redo size |
产生重做日志大小 |
|
|
bytes sent via SQL*Net to client |
利用sql*net传入到client的字节数； |
|
|
bytes received via SQL*Net from client |
利用sql*net传出client的字节数； |
|
|
SQL*Net roundtrips to/from client |
|
|
|
sorts (memory) |
内存中排序空间使用； |
|
|
sorts (disk) |
物理存储中排序空间使用； |
如果memory空间使用不足，是会使用disk的空间的； |