*********************************************************** ----1：获取“刚刚”的执行计划display_cursor *********************************************************** Explain plan命令在Oracle中，可以对后面的SQL语句进行直接的解析，将执行计划保存在一个plan_table的中间表中。之后通过dbms_xplan包的方法进行获取。 select count(*) from t1; --查询v$sql视图，找到该语句的sql_id(前提是你要查询的sql语句还在shared pool)： select sql_id from v$sql where sql_text='select count(*) from t1'; SQL_ID ------------- 5bc0v4my7dvr5 --调用dbms_xplan包，查看该语句执行时的实现执行计划： select * from table(dbms_xplan.display_cursor('5bc0v4my7dvr5')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- SQL_ID 5bc0v4my7dvr5, child number 0 ------------------------------------- select count(*) from t1 Plan hash value: 3724264953 ------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | ------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | 74 (100)| | | 1 | SORT AGGREGATE | | 1 | | | PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- | 2 | TABLE ACCESS FULL| T1 | 100K| 74 (2)| 00:00:01 | ------------------------------------------------------------------- Note ----- - SQL plan baseline SQL_PLAN_f4251dfwsquh4616acf47 used for this statement 已选择18行。 /************* 直接调用display_cursor，不指定sql_id，就可以将刚刚当前会话执行的SQL命令执行计划从library cache中查询出来。 注意：display_cursor也支持format参数，可以进行详细执行计划信息的抽取。 只能在sqlplus或者sqlplusw上使用。如果是TOAD、PL/SQL develop等其它三方工具，可能调用不能正常使用。 ***********/ *********************************************************** 2：explain plan for *********************************************************** ---案例1、显示简单的计划 explain plan for select count(*) from t1; 查看结果： select * from table(dbms_xplan.display()); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Plan hash value: 3724264953 ------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | ------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 74 (2)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | TABLE ACCESS FULL| T1 | 100K| 74 (2)| 00:00:01 | ------------------------------------------------------------------- Note PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- ----- - SQL plan baseline "SQL_PLAN_f4251dfwsquh4616acf47" used for this statement 已选择13行。 --案例2、显示详细执行计划信息 explain plan for select count(*) from t1; 查看结果： select * from table(dbms_xplan.display(null,null,'advanced')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Plan hash value: 3724264953 ------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | ------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 74 (2)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | TABLE ACCESS FULL| T1 | 100K| 74 (2)| 00:00:01 | ------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- ------------------------------------------------------------- 1 - SEL$1 2 - SEL$1 / T1@SEL$1 Outline Data ------------- /*+ BEGIN_OUTLINE_DATA FULL(@"SEL$1" "T1"@"SEL$1") PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- OUTLINE_LEAF(@"SEL$1") ALL_ROWS DB_VERSION('11.2.0.3') OPTIMIZER_FEATURES_ENABLE('11.2.0.3') IGNORE_OPTIM_EMBEDDED_HINTS END_OUTLINE_DATA */ Column Projection Information (identified by operation id): ----------------------------------------------------------- PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- 1 - (#keys=0) COUNT(*)[22] Note ----- - SQL plan baseline "SQL_PLAN_f4251dfwsquh4616acf47" used for this statement 已选择38行。 SQL> /**** Explain plan for使用起来非常顺手，特别是可以支持第三方开发工具中使用，但是explain plan在使用的时候，有些 小问题，需要我们注意： 1、explain plan for是单纯对SQL语句进行优化器分析，获取并产生到的执行计划。 这个过程中，并没有真正执行。所以，生成的执行计划有时候会可能有问题，而且进行统计的信息情况没有 autotrace的准确度高； 2、explain plan for由于只是对执行计划进行估计。所以在有绑定变量的SQL时，生成的执行计划并不准确； *****/ *********************************************************** 3、autotrace工具使用 *********************************************************** *************3.1 配置AUTOTRACE 配置AUTOTRACE 的方法不止一种，以下是我采用的方法： 1、 （1）cd [ORACLE_HOME]/sqlplus/admin； （2）作为SYS 或SYSDBA 登录SQL*Plus； （3）运行@/plustrce.sql； （4）运行GRANT PLUSTRACE TO PUBLIC。 如果愿意，可以把GRANT TO PUBLIC 中的PUBLIC 替换为某个用户。 通过将PLAN_TABLE置为public，任何人都可以使用SQL*Plus 进行跟踪。这么一来，就不需要每个用户都安装自己的计划表 --配置如下：想配置scott下使用autotrace --1.查看scott是否具有权限 C:\>sqlplus scott/tiger@orcl SQL> show user; USER 为 "SCOTT" SQL> set autotrace on; SP2-0618: 无法找到会话标识符。启用检查 PLUSTRACE 角色 SP2-0611: 启用 STATISTICS 报告时出错 --2.作为SYS 或SYSDBA 登录SQL*Plus运行@/plustrce.sql SQL> show user; USER 为 "SYS" SQL> @E:\app\Administrator\product\11.2.0\dbhome_1\sqlplus\admin\plustrce.sql 返回：---------------------------------- SQL> drop role plustrace; drop role plustrace * 第 1 行出现错误: ORA-01919: 角色 'PLUSTRACE' 不存在 SQL> create role plustrace; 角色已创建。 SQL> SQL> grant select on v_$sesstat to plustrace; 授权成功。 SQL> grant select on v_$statname to plustrace; 授权成功。 SQL> grant select on v_$mystat to plustrace; 授权成功。 SQL> grant plustrace to dba with admin option; 授权成功。 SQL> SQL> set echo off ------------------------------------- --3.授权给scott账户 SQL> grant plustrace to scott; 授权成功。 --4.scott账户验证是否具有权限 SQL> show user; USER 为 "SCOTT" SQL> set autotrace on; SQL> set autotrace traceonly; SQL> set timing on; *************3.2 autotrace工具使用 SQL> set autotrace 用法: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]] --用法1：查看执行计划、统计信息并且返回sql结果集 set autotrace on; select count(*) from t; COUNT(*) ---------- 50295 已用时间: 00: 00: 00.01 执行计划 ---------------------------------------------------------- Plan hash value: 2966233522 ------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | ------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 159 (2)| 00:00:02 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | TABLE ACCESS FULL| T | 50295 | 159 (2)| 00:00:02 | ------------------------------------------------------------------- 统计信息 ---------------------------------------------------------- 0 recursive calls 0 db block gets 0 consistent gets 0 physical reads 0 redo size 509 bytes sent via SQL*Net to client 211 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 1 rows processed ---方法2：查看执行计划、统计信息不返回sql结果集： SQL> set autotrace traceonly; SQL> select * from t2; 已选择402344行。 执行计划 ---------------------------------------------------------- Plan hash value: 1513984157 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 402K| 33M| 1240 (3)| 00:00:15 | | 1 | TABLE ACCESS FULL| T2 | 402K| 33M| 1240 (3)| 00:00:15 | -------------------------------------------------------------------------- 统计信息 ---------------------------------------------------------- 0 recursive calls 0 db block gets 0 consistent gets 0 physical reads 0 redo size 911 bytes sent via SQL*Net to client 190 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 402344 rows processed ---方法3：只看执行计划不返回sql结果集： QL> set autotrace traceonly explain; SQL> select * from t; 已用时间: 00: 00: 00.01 执行计划 ---------------------------------------------------------- Plan hash value: 1601196873 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 50295 | 4273K| 161 (3)| 00:00:02 | | 1 | TABLE ACCESS FULL| T | 50295 | 4273K| 161 (3)| 00:00:02 | ---方法4：只看统计信息不返回sql结果集： SQL> set autotrace traceonly statistics; SQL> select * from t; 已选择50295行。 统计信息 ---------------------------------------------------------- 0 recursive calls 0 db block gets 0 consistent gets 0 physical reads 0 redo size 911 bytes sent via SQL*Net to client 189 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 50295 rows processed *********************************************************** 4、SQL_TRACE会话跟踪 *********************************************************** 当SQL语句出现性能问题时，我们可以用SQL_TRACE来跟踪SQL的执行情况，通过跟踪，我们可以了解一条SQL或者PL/SQL包的运行情况，SQL_TRACE命令会将SQL执行的整个过程输出到一个trace文件中，我们可以读这个trace 文件来了解在这个SQL执行过程中Oracle 都做了哪些操作。 可以通过sql命令启动SQL_TRACE，或者在初始化参数里面。 SQL>alter session set sql_trace=true; 或者 SQL> alter database set sql_trace=true; 这两条命令的区别： 在session级别设置，只对当前session进行跟踪，在实例级别，会对实例上所有的SQL做跟踪，这种方式跟踪的SQL太多， 代价是非常大的，所有很少用。 如果是在初始化文件里面设置，只需要在参数文件里添加一个sql_trace 参数即可。 示例： 1.确定当前的trace文件。 1.1 通过设置trace 文件标识 SQL> alter session set tracefile_identifier='ocpyang'; 会话已更改。 设置标识的目的就是方便我们查找生成的trace文件。我们只需要在trace目录查找文件名里带有标识的文件即可。 在Oracle 10g中，SQL_TRACE生成的trace文件默认路劲是$ORACLE_BASE/admin/SID/udump. 到了11g，trace 默认路径在：$ORACLE_BASE/diag/rdbms/orcl/orcl/trace目录下. 1.2直接用如下SQL直接查出，当前的trace文件名。 SELECT d.VALUE || '/' || LOWER (RTRIM (i.INSTANCE, CHR (0))) || '_ora_' || p.spid || '.trc' AS "trace_file_name" FROM (SELECT p.spid FROM v$mystat m, v$session s, v$process p WHERE m.statistic# = 1 AND s.SID = m.SID AND p.addr = s.paddr ) p, (SELECT t.INSTANCE FROM v$thread t, v$parameter v WHERE v.NAME = 'thread' AND (v.VALUE = 0 OR t.thread# = TO_NUMBER (v.VALUE)) ) i, (SELECT VALUE FROM v$parameter WHERE NAME = 'user_dump_dest' ) d; trace_file_name -------------------------------------------------------------------------------- E:\app\Administrator\diag\rdbms\orcl\orcl\trace/orcl_ora_68612.trc 2. 启动SQL_TRACE SQL> alter session set sql_trace=true; 会话已更改。 3. 进行相关事务操作 SQL> select * from t1; 4.关闭SQL_TRACE SQL> alter session set sql_trace=false; 会话已更改。 注意，这里是显示的关闭SQL_TRACE，在session级别，也可以直接退出SQLPLUS来终止SQL_TRACE。 *********************************************************** 5、10046跟踪 *********************************************************** 10046 事件主要用来跟踪SQL语句，它并不是ORACLE 官方提供给用户的命令，在官方文档上也找不到事件的说明信息。 但是用的却比较多，因为10046事件获取SQL的信息比SQL_TRACE 更多。 更有利于我们对SQL的判断。 10046事件说明10046事件是Oracle提供的内部事件，是对SQL_TRACE的增强. 10046事件可以设置以下四个级别: 1 - 启用标准的SQL_TRACE功能,等价于sql_trace 4 - Level 1 加上绑定值(bind values) 8 - Level 1 + 等待事件跟踪 12 - Level 1 + Level 4 + Level 8 类似sql_trace，10046事件可以在全局设置，也可以在session级设置。 开启10046事件 1.对当前session 使用10046事件 SQL>alter session set events ‘10046 trace name context forever, level 12’; --启动10046事件 执行相关事务 SQL>alter session set events ‘10046 trace name context off’; -- 关闭10046事件 2.对其他的会话进行跟踪 2.1用SQL_TRACE跟踪 SQL> select sid,serial# from v$session where SID=267; SID SERIAL# ---------- ---------- 267 996 SQL> execute dbms_system.set_sql_trace_in_session(267,996,true); -- 启动SQL_TRACE PL/SQL 过程已成功完成。 SQL> execute dbms_system.set_sql_trace_in_session(267,996,false); -- 关闭SQL_TRACE PL/SQL 过程已成功完成。 2.2 使用10046 事件跟踪 SQL> exec dbms_monitor.session_trace_enable(267,996,waits=>true,binds=>true); -- 启动trace PL/SQL 过程已成功完成。 SQL> exec dbms_monitor.session_trace_disable(267,996); -- 关闭trace PL/SQL 过程已成功完成。 注意： 如果一条SQL语句中包含了通过DBLINK进行的数据操作，我们想对这条SQL进行trace跟踪，在本地只能够 trace到本地执行的SQL信息，而对于远程的SQL语句，由于它运行在远端的数据库上，我们要获得它的信息， 需要到远端的数据库上，找到运行这条SQL语句的session，然后对它做Trace。 另外，这条SQL语句的执行 计划也只能从远端数据库上捕获到。 总之，当SQL语句操作出现性能问题时，我们可以用SQL_TRACE 或者10046事件进行跟踪是最合适的。 如果是数据库整体性能下降，就需要使用statspack或者AWR对数据库进行分析。