如何获取某个sql语句的执行计划系列二
这里介绍另一种较为简单的方法使用SQL*Plus AUTOTRACE可以轻松捕获sql的执行计划
set autotarce on /ENables autotracing SQL statements
OFF /Disables autotracing SQL statements
TRACE[ONLY] /Enables autotracing SQL statements and suppresses statement output
on EXPLAIN /Displays execution plans but does not display statistics
on STATISTICS /Displays statistics but does not display execution plans
确定是否已经启用 autotrace
SHOW AUTOTRACE
eg:
SQL> set autotrace on
SQL> select * from dual;
D
-
X
执行计划
----------------------
Plan hash value: 272002086
--------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 |
--------------------------------------
统计信息
----------------------
0 recursive calls
0 db block gets ------------当前逻辑i/o的数量
3 consistent gets --------------从buffer cache 中读的数据块数
0 physical reads --------------从磁盘读的数据块数
0 redo size ---------DML语句产生的redo 的数量
404 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) ------------在磁盘上的temp 空间的排序数
1 rows processed
note：DB block gets are reads of the current blocks in the buffer cache.
Sorts should be performed in memory rather than on disk。
使用dbms_monitor pl/sql包来启用sql跟踪
1》 For your current session:------当前会话级别
SQL> EXEC dbms_monitor.session_trace_enable;
eg：
SQL> EXECUTE dbms_monitor.session_trace_enable
2> (session_id, serial_id,
3> waits => TRUE, binds => TRUE );
禁用sql trace ：SQL> EXEC dbms_monitor. session_trace_disable;
SQL> EXECUTE dbms_session.set_sql_trace(true);
禁用sql trace：SQL> EXECUTE dbms_sessi on.set_sql_trace(false);
2》For any session: ----任何会话度可以跟踪
SQL> EXECUTE dbms_monitor.session_trace_enable
2 (session_id , serial_id, waits, binds );
禁用sql trace：SQL> EXECUTE dbms_monit or.session_trace_disable (session_id , serial_id);
3》For instance-wide tracing: -------实例级别
SQL> EXEC dbms_monitor.database_trace_enable();
注意基于instance-wide tracing级别的trace 会产生大量日志，也会影响系统性能
禁用sql trace： SQL> EXEC dbms_monitor. database_trace_disable();