怎么获取某个sql语句的执行计划系列二

如何获取某个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();

怎么获取某个sql语句的执行计划系列二

相关推荐