分析SQL语句的执行计划

分析SQL语句的执行计划

分析SQL语句的执行计划的方式有很多种，这里简单列举下，可能有些遗漏：

1.EXPLAIN

 使用explain plan for [sql statement]分析SQL语句的执行计划

 使用@?/rdbms/admin/utlxplp.sql; or select plan_table_output from table(dbms_xplan.display());

 两者都可以查看分析出来的执行计划。

示列： SQL> select instance_name from gv$instance;   INSTANCE_NAME ---------------- szscdb1 szscdb2 SQL> conn hr/hr Connected. SQL> create table t                   ------创建一张表T   2  as select * from all_objects;   Table created.   SQL> EXPLAIN PLAN FOR SELECT * FROM T WHERE OBJECT_ID=100;  ---分析SQL语句的执行计划   Explained.   SQL> select plan_table_output from table(dbms_xplan.display());  ----显示SQL语句的执行计划   PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Plan hash value: 1601196873   -------------------------------------------------------------------------- | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     | -------------------------------------------------------------------------- |   0 | SELECT STATEMENT  |      |     9 |  1422 |   229   (1)| 00:00:03 | |*  1 |  TABLE ACCESS FULL| T    |     9 |  1422 |   229   (1)| 00:00:03 | --------------------------------------------------------------------------   Predicate Information (identified by operation id): ---------------------------------------------------   PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------      1 - filter("OBJECT_ID"=100)   Note -----    - dynamic sampling used for this statement (level=2)   17 rows selected.   SQL> create index t_object_id on t(object_id);  ----创建索引列   Index created.   SQL> set linesize 100 SQL> EXPLAIN PLAN FOR SELECT * FROM T WHERE OBJECT_ID=100; ----再次分析SQL语句的执行计划   Explained.   SQL> select plan_table_output from table(dbms_xplan.display());   PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------- Plan hash value: 691867800   ------------------------------------------------------------------------------------------- | Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     | ------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT            |             |     1 |   158 |     2   (0)| 00:00:01 | |   1 |  TABLE ACCESS BY INDEX ROWID| T           |     1 |   158 |     2   (0)| 00:00:01 | |*  2 |   INDEX RANGE SCAN          | T_OBJECT_ID |     1 |       |     1   (0)| 00:00:01 | -------------------------------------------------------------------------------------------   Predicate Information (identified by operation id):   PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------- ---------------------------------------------------      2 - access("OBJECT_ID"=100)   Note -----    - dynamic sampling used for this statement (level=2)   18 rows selected.

 

2. SQL*Trace和Tkprof

开启数据库的SQL*Trace，一般作用范围为session级别。

使用Tkprof工具来分析产生的Trace文件。

SELECT s.USERNAME,S.SID,P.SPID,S.SERIAL#,S.MACHINE,p.PROGRAM FROM GV$SESSION s,GV$process p WHERE S.PADDR=P.ADDR AND s.USERNAME IS NOT NULL; ---查询用户的SID,SERIAL# USERNAME     SID         SPID     SERIAL#    MACHINE                    PROGRAM --------- ---------- ------------------ ----------               ---------- HR                  161       26114        139        WORKGROUP\GAUGHUIZHOU-PC   oracle@rac2   ###启动指定会话session级别的sql_trace ###需要注意的是，必须在对应的节点使用sys用户执行该语句，并且当被监控session执行了SQL语句之后才会产生trace文件。 execute dbms_system.set_sql_trace_in_session(sid,serial#,true);  -- 启动SQL_TRACE SQL> execute dbms_system.set_sql_trace_in_session(161,139,true);   PL/SQL procedure successfully completed.   ###关闭指定会话session级别的sql_trace SQL> execute dbms_system.set_sql_trace_in_session(161,139,false);   PL/SQL procedure successfully completed.   #查找该trace文件 路径为：SQL> show parameter user_dump_dest   NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ user_dump_dest                       string      /u02/app/oracle/diag/rdbms/szs                                                  cdb/szscdb1/trace ###trace文件的名称为 InstanceNmae_ora_spid.trc; szscdb2_ora_26114.trc     使用Tkprof格式化trace文件 [oracle@rac2 trace]# tkprof Usage: tkprof tracefile outputfile [explain= ] [table= ]               [print= ] [insert= ] [sys= ] [sort= ]   table=schema.tablename   Use 'schema.tablename' with 'explain=' option.   explain=user/password    Connect to ORACLE and issue EXPLAIN PLAN.   print=integer    List only the first 'integer' SQL statements.   aggregate=yes|no   insert=filename  List SQL statements and data inside INSERT statements.   sys=no           TKPROF does not list SQL statements run as user SYS.   record=filename  Record non-recursive statements found in the trace file.   waits=yes|no     Record summary for any wait events found in the trace file.   sort=option      Set of zero or more of the following sort options:     prscnt  number of times parse was called     prscpu  cpu time parsing     prsela  elapsed time parsing     prsdsk  number of disk reads during parse     prsqry  number of buffers for consistent read during parse     prscu   number of buffers for current read during parse     prsmis  number of misses in library cache during parse     execnt  number of execute was called     execpu  cpu time spent executing     exeela  elapsed time executing     exedsk  number of disk reads during execute     exeqry  number of buffers for consistent read during execute     execu   number of buffers for current read during execute     exerow  number of rows processed during execute     exemis  number of library cache misses during execute     fchcnt  number of times fetch was called     fchcpu  cpu time spent fetching     fchela  elapsed time fetching     fchdsk  number of disk reads during fetch     fchqry  number of buffers for consistent read during fetch     fchcu   number of buffers for current read during fetch     fchrow  number of rows fetched userid  userid of user that parsed the cursor   [oracle@rac2 trace]# tkprof szscdb2_ora_26114.trc /tmp/szscdb2_ora_26114.out sys=no explain=system/oracle sort=execpu print=3   含义： szscdb2_ora_26114.trc       需要分析trace文件 /tmp/szscdb2_ora_26114.out  产生分析trace结果 sys=no                    表示不分析sys用户执行sql语句 explain=system/oracle       表示连接到system用户，并执行计划分析 sort=execpu               表示按CPU消耗值对sql语句排序 print=3                   表示只分析前3条语句，本语句只分析最耗资源前3条语句.   ##查看trace文件分析出来的信息 oracle@rac2:/u02/app/oracle/diag/rdbms/szscdb/szscdb2/trace>cat /tmp/szscdb2_ora_26114.out   TKPROF: Release 11.2.0.3.0 - Development on Wed Jul 24 11:05:17 2013   Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.   Trace file: szscdb2_ora_26114.trc Sort options: execpu  ******************************************************************************** count    = number of times OCI procedure was executed cpu      = cpu time in seconds executing elapsed  = elapsed time in seconds executing disk     = number of physical reads of buffers from disk query    = number of buffers gotten for consistent read current  = number of buffers gotten in current mode (usually for update) rows     = number of rows processed by the fetch or execute call ********************************************************************************   SQL ID: 1hws8f0zpw6b9 Plan Hash: 3136739096   delete from t     call     count       cpu    elapsed       disk      query    current        rows ------- ------  -------- ---------- ---------- ---------- ----------  ---------- Parse        1      0.00       0.00          0          0          0           0 Execute      1      0.96       1.71        820        139      63989       56343 Fetch        0      0.00       0.00          0          0          0           0 ------- ------  -------- ---------- ---------- ---------- ----------  ---------- total        2      0.96       1.72        820        139      63989       56343   Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 84  (HR) Number of plan statistics captured: 1   Rows (1st) Rows (avg) Rows (max)  Row Source Operation ---------- ---------- ----------  ---------------------------------------------------          0          0          0  DELETE  T (cr=165 pr=820 pw=0 time=1722116 us)      56343      56343      56343   INDEX FULL SCAN T_OBJECT_ID (cr=126 pr=0 pw=0 time=34773 us cost=127 size=281715 card=56343)(object id 80473)     Rows     Execution Plan -------  ---------------------------------------------------       0  DELETE STATEMENT   MODE: ALL_ROWS       0   DELETE OF 'T'   56343    TABLE ACCESS (FULL) OF 'T' (TABLE)   ********************************************************************************   SQL ID: cyzznbykb509s Plan Hash: 392851318   select count(*) from  t     call     count       cpu    elapsed       disk      query    current        rows ------- ------  -------- ---------- ---------- ---------- ----------  ---------- Parse        1      0.00       0.00          0          0          0           0 Execute      1      0.00       0.00          0          0          0           0 Fetch        2      0.00       0.00          0        132          0           1 ------- ------  -------- ---------- ---------- ---------- ----------  ---------- total        4      0.00       0.00          0        132          0           1   Misses in library cache during parse: 0 Optimizer mode: ALL_ROWS Parsing user id: 84  (HR) Number of plan statistics captured: 1   Rows (1st) Rows (avg) Rows (max)  Row Source Operation ---------- ---------- ----------  ---------------------------------------------------          1          1          1  SORT AGGREGATE (cr=132 pr=0 pw=0 time=8057 us)      56343      56343      56343   INDEX FAST FULL SCAN T_OBJECT_ID (cr=132 pr=0 pw=0 time=9387 us cost=36 size=0 card=56343)(object id 80473)     Rows     Execution Plan -------  ---------------------------------------------------       0  SELECT STATEMENT   MODE: ALL_ROWS       1   SORT (AGGREGATE)   56343    TABLE ACCESS (FULL) OF 'T' (TABLE)         ********************************************************************************   OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS   call     count       cpu    elapsed       disk      query    current        rows ------- ------  -------- ---------- ---------- ---------- ----------  ---------- Parse        4      0.00       0.01          0          0          0           0 Execute      4      0.96       1.72        820        139      63990       56343 Fetch      115      0.01       0.08         28        381          0        1676 ------- ------  -------- ---------- ---------- ---------- ----------  ---------- total      123      0.99       1.81        848        520      63990       58019   Misses in library cache during parse: 2     OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS   call     count       cpu    elapsed       disk      query    current        rows ------- ------  -------- ---------- ---------- ---------- ----------  ---------- Parse       15      0.00       0.00          0          0          0           0 Execute     29      0.00       0.01          0          0          0           0 Fetch       42      0.00       0.00          0         76          0          28 ------- ------  -------- ---------- ---------- ---------- ----------  ---------- total       86      0.01       0.01          0         76          0          28   Misses in library cache during parse: 2 Misses in library cache during execute: 2       4  user  SQL statements in session.     5  internal SQL statements in session.     9  SQL statements in session.     2  statements EXPLAINed in this session. ******************************************************************************** Trace file: szscdb2_ora_26114.trc Trace file compatibility: 11.1.0.7 Sort options: execpu         1  session in tracefile.        4  user  SQL statements in trace file.        5  internal SQL statements in trace file.        9  SQL statements in trace file.        7  unique SQL statements in trace file.        2  SQL statements EXPLAINed using schema:            SYSTEM.prof$plan_table              Default table was used.              Table was created.              Table was dropped.      353  lines in trace file.      400  elapsed seconds in trace file.

 

3.AUTOTRACE

该功能是sqlplus工具中自带的

SQL> set autotrace

Usage: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]

 

set autotrace on

set timing on;

set autotrace traceonly;  不看SQL执行结果，只看语句执行计划、统计信息.

set autotrace traceonly explain; 只查看执行计划.

set autotrace traceonly statistics; 只查看统计信息.

注：autotrace和explain区别，explain分析的sql语句并没有执行，而autotrace是真正执行之后的执行计划.

SQL> set autotrace on SQL> select * from hr.t;   no rows selected     Execution Plan ---------------------------------------------------------- Plan hash value: 1601196873   -------------------------------------------------------------------------- | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     | -------------------------------------------------------------------------- |   0 | SELECT STATEMENT  |      | 56343 |  5392K|   229   (1)| 00:00:03 | |   1 |  TABLE ACCESS FULL| T    | 56343 |  5392K|   229   (1)| 00:00:03 | --------------------------------------------------------------------------     Statistics ----------------------------------------------------------           1  recursive calls           0  db block gets         817  consistent gets           1  physical reads           0  redo size        1343  bytes sent via SQL*Net to client         513  bytes received via SQL*Net from client           1  SQL*Net roundtrips to/from client           0  sorts (memory)           0  sorts (disk)           0  rows processed

 

4.10046事件

注:该部分参考文档：http://blog.csdn.net/tianlesoftware/article/details/5857023

SQL_TRACE (10046), TKProf and Explain Plan - Overview Reference (Doc ID 199081.1)

10046 事件按照收集信息内容，可以分成4个级别：

                Level 1： 等同于SQL_TRACE 的功能

                Level 4： 在Level 1的基础上增加收集绑定变量的信息

                Level 8： 在Level 1 的基础上增加等待事件的信息

                Level 12：等同于Level 4+Level 8, 即同时收集绑定变量信息和等待事件信息。

alter session set events ‘10046 trace name context forever, level 12’;  ----在当前会话开启10046事件

alter session set events ‘10046 trace name context off’; ---关闭10046事件

 

exec dbms_monitor.session_trace_enable(sid,serial#,waits=>true,binds=>true);  ---开启其他会话10046事件

exec dbms_monitor.session_trace_disable(267,996);  --关闭其他会话10046事件

 

SQL> exec dbms_monitor.session_trace_enable(161,141,waits=>true,binds=>true);      PL/SQL procedure successfully completed.     ###查看分析出来的trace文件信息 TKPROF: Release 11.2.0.3.0 - Development on Wed Jul 24 13:00:26 2013   Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.   Trace file: szscdb1_ora_27326.trc Sort options: execpu ******************************************************************************** count    = number of times OCI procedure was executed cpu      = cpu time in seconds executing elapsed  = elapsed time in seconds executing disk     = number of physical reads of buffers from disk query    = number of buffers gotten for consistent read current  = number of buffers gotten in current mode (usually for update) rows     = number of rows processed by the fetch or execute call ********************************************************************************   SQL ID: g3trt1pva7247 Plan Hash: 1833546154   select * from  employees where employee_id=100     call     count       cpu    elapsed       disk      query    current        rows ------- ------  -------- ---------- ---------- ---------- ----------  ---------- Parse        1      0.02       0.04          0          0          0           0 Execute      1      0.00       0.00          0          0          0           0 Fetch        2      0.00       0.01          2          2          0           1 ------- ------  -------- ---------- ---------- ---------- ----------  ---------- total        4      0.02       0.06          2          2          0           1   Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 84  (HR) Number of plan statistics captured: 1   Rows (1st) Rows (avg) Rows (max)  Row Source Operation ---------- ---------- ----------  ---------------------------------------------------          1          1          1  TABLE ACCESS BY INDEX ROWID EMPLOYEES (cr=2 pr=2 pw=0 time=14754 us cost=1 size=69 card=1)          1          1          1   INDEX UNIQUE SCAN EMP_EMP_ID_PK (cr=1 pr=1 pw=0 time=6878 us cost=0 size=0 card=1)(object id 76368)   error during execute of EXPLAIN PLAN statement ORA-00942: table or view does not exist   parse error offset: 85   Elapsed times include waiting on following events:   Event waited on                             Times   Max. Wait  Total Waited   ----------------------------------------   Waited  ----------  ------------   row cache lock                                 26        0.00          0.00   SQL*Net message to client                       2        0.00          0.00   Disk file operations I/O                        1        0.00          0.00   db file sequential read                         2        0.00          0.01   SQL*Net message from client                     2        0.02          0.03 ********************************************************************************

 QQ交流群:300392987

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26169542/viewspace-767009/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26169542/viewspace-767009/