ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 分析SQL语句的执行计划

分析SQL语句的执行计划

原创 Linux操作系统 作者:DBA_oracle_java 时间:2013-07-24 12:55:00 0 删除 编辑

分析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*TraceTkprof

开启数据库的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; 只查看统计信息.

注:autotraceexplain区别,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/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 11gR2 RAC+DG配置
请登录后发表评论 登录
全部评论

注册时间:2013-07-02

  • 博文量
    31
  • 访问量
    117117