A-A+

查看sql执行计划的方法

2013年02月22日 Database, PreformanceTuning 评论 1 条 阅读 2,599 次

对于oracle dba来说,其中最多的时间是放在数据库优化上的,数据库优化也是衡量一个dba价值的重要方面,这个也在很多刚入门的dba去面试dba职位时被问及的最多并且也是必不可少的一个问题。而对于数据库优化,基本是对sql的优化,而要优化一条sql,则获取sql的执行计划便是第一步。

下边整理了7种oracle常用的获取sql执行计划的途径,包括获取当前sql的执行计划,获取sql的历史执行计划等。希望能帮助到大家。

(一)使用explain for 解释sql的执行计划,通过dbms_xplan()函数查看解释后的执行计划
注意:该方法并没有真正的执行指定的sql,而是将指定的sql进行了解释而已,并且在library cache中存在的该sql将是explain for select…
而不是指定的sql。

示例如下:


SQL> explain plan for 
  2  select * from scott.emp e where e.deptno in (select deptno from scott.dept); 

Explained.

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3074306753

------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |    14 |   560 |     3   (0)| 00:00:01 |
|   1 |  NESTED LOOPS      |         |    14 |   560 |     3   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP     |    14 |   518 |     3   (0)| 00:00:01 |
|*  3 |   INDEX UNIQUE SCAN| PK_DEPT |     1 |     3 |     0   (0)| 00:00:01 |
------------------------------------------------------------------------------


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("E"."DEPTNO"="DEPTNO")

15 rows selected.

(二)使用dbms_xplan.display_cursor查看真实执行计划(10G以上)

使用这个函数可以显示内存中一个或者多个游标的执行计划。

语法:
Select * from table(dbms_xplan.display_cursor('hash_value','child_number','advanced'));

注意:如果第一个和第二个参数未设置,则会返回内存中最近的一个sql的执行计划,第三个参数用来设置格式输出
如下:

SQL> select /*+gather_plan_statistics*/ * from scott.emp e where e.deptno in (select deptno from scott.dept) and rownum<3;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  ah916jr1wmqbg, child number 0
-------------------------------------
select /*+gather_plan_statistics*/ * from scott.emp e where e.deptno in
(select deptno from scott.dept) and rownum<3

Plan hash value: 653357053

-------------------------------------------------------------------------------
| Id  | Operation           | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |         |       |       |     2 (100)|          |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|*  1 |  COUNT STOPKEY      |         |       |       |            |          |
|   2 |   NESTED LOOPS      |         |     2 |    80 |     2   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| EMP     |     2 |    74 |     2   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN| PK_DEPT |     1 |     3 |     0   (0)|          |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<3)
   4 - access("E"."DEPTNO"="DEPTNO")

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------


23 rows selected.

可以通过设置第一个和第二个参数显示指定的sql的真实执行计划
示例:

SQL> set line 200
SQL> select /*yallonking*/ * from scott.emp e where e.deptno in (select deptno from scott.dept) and rownum<3;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30

SQL> col SQL_TEXT for a70
SQL> select sql_id,hash_value,child_number,sql_text from v$sql where sql_text like '%yallonking%';

SQL_ID        HASH_VALUE CHILD_NUMBER SQL_TEXT
------------- ---------- ------------ ----------------------------------------------------------------------
abcarhr7w5g8b 3485646091            0 select /*yallonking*/ * from scott.emp e where e.deptno in (select dep
                                      tno from scott.dept) and rownum<3

d625bbv7utkq6 3484207814            0 select sql_id,hash_value,child_number,sql_text from v$sql where sql_te
                                      xt like '%yallonking%'

dymhjyqqc7x7z 2898523391            0 select hash_value,child_number,sql_text from v$sql where sql_text like
                                       '%yallonking%'

注意:此处人为执行了一个sql(该sql加了注释,便于后边识别),然后从找到该sql的hash_value和子游标号(3485646091 0),此处也可以看到该游标是父游标,如果该sql在不同的环境下执行,将会产生不同的version count,这样的话子游标在该处会有多个。

然后进行设置函数dbms_xplan.display_cursor()的第一个和第二个参数查看指定的sql的执行计划。

使用格式化参数BASIC LAST ALLSTATS

SQL> select * from table(dbms_xplan.display_cursor(3485646091,0,'BASIC LAST ALLSTATS'));   

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select /*yallonking*/ * from scott.emp e where e.deptno in (select
deptno from scott.dept) and rownum<3

Plan hash value: 653357053

------------------------------------------------
| Id  | Operation           | Name    | E-Rows |
------------------------------------------------
|*  1 |  COUNT STOPKEY      |         |        |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   2 |   NESTED LOOPS      |         |      2 |
|   3 |    TABLE ACCESS FULL| EMP     |      2 |
|*  4 |    INDEX UNIQUE SCAN| PK_DEPT |      1 |
------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<3)
   4 - access("E"."DEPTNO"="DEPTNO")


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Note
-----
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level


28 rows selected.

使用格式化参数advanced

SQL> select * from table(dbms_xplan.display_cursor(3485646091,0,'advanced'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
HASH_VALUE  3485646091, child number 0
--------------------------------------
select /*yallonking*/ * from scott.emp e where e.deptno in (select
deptno from scott.dept) and rownum<3

Plan hash value: 653357053

-------------------------------------------------------------------------------
| Id  | Operation           | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |         |       |       |     2 (100)|          |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|*  1 |  COUNT STOPKEY      |         |       |       |            |          |
|   2 |   NESTED LOOPS      |         |     2 |    80 |     2   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| EMP     |     2 |    74 |     2   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN| PK_DEPT |     1 |     3 |     0   (0)|          |
-------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$5DA710D3
   3 - SEL$5DA710D3 / E@SEL$1

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   4 - SEL$5DA710D3 / DEPT@SEL$2

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$5DA710D3")

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      UNNEST(@"SEL$2")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$2")
      FULL(@"SEL$5DA710D3" "E"@"SEL$1")
      INDEX(@"SEL$5DA710D3" "DEPT"@"SEL$2" ("DEPT"."DEPTNO"))
      LEADING(@"SEL$5DA710D3" "E"@"SEL$1" "DEPT"@"SEL$2")
      USE_NL(@"SEL$5DA710D3" "DEPT"@"SEL$2")
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------

   1 - filter(ROWNUM<3)
   4 - access("E"."DEPTNO"="DEPTNO")

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "E"."EMPNO"[NUMBER,22], "E"."ENAME"[VARCHAR2,10],
       "E"."JOB"[VARCHAR2,9], "E"."MGR"[NUMBER,22], "E"."HIREDATE"[DATE,7],
       "E"."SAL"[NUMBER,22], "E"."COMM"[NUMBER,22], "E"."DEPTNO"[NUMBER,22]

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   2 - "E"."EMPNO"[NUMBER,22], "E"."ENAME"[VARCHAR2,10],
       "E"."JOB"[VARCHAR2,9], "E"."MGR"[NUMBER,22], "E"."HIREDATE"[DATE,7],
       "E"."SAL"[NUMBER,22], "E"."COMM"[NUMBER,22], "E"."DEPTNO"[NUMBER,22]
   3 - "E"."EMPNO"[NUMBER,22], "E"."ENAME"[VARCHAR2,10],
       "E"."JOB"[VARCHAR2,9], "E"."MGR"[NUMBER,22], "E"."HIREDATE"[DATE,7],
       "E"."SAL"[NUMBER,22], "E"."COMM"[NUMBER,22], "E"."DEPTNO"[NUMBER,22]


62 rows selected.

使用hint gather_plan_statistics结合函数dbms_xplan.display_cursor()输出含有统计信息的详细的sql的执行计划

SQL> alter system flush shared_pool;

System altered.

SQL> select /*+gather_plan_statistics*/ * from scott.emp e where e.deptno in (select deptno from scott.dept) and rownum<3;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30

SQL> select sql_id,hash_value,child_number,sql_text from v$sql where sql_text like '%scott%';

SQL_ID        HASH_VALUE CHILD_NUMBER SQL_TEXT
------------- ---------- ------------ --------------------------------------------------------------------------------
g071m9pbmk8a0 1463361856            0 select sql_id,hash_value,child_number,sql_text from v$sql where sql_text like '%
                                      scott%'

ah916jr1wmqbg 3284785519            0 select /*+gather_plan_statistics*/ * from scott.emp e where e.deptno in (select
                                      deptno from scott.dept) and rownum<3


SQL> select * from table(dbms_xplan.display_cursor(3284785519,0,'BASIC LAST ALLSTATS'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select /*+gather_plan_statistics*/ * from scott.emp e where e.deptno in (select
deptno from scott.dept) and rownum<3

Plan hash value: 653357053

-----------------------------------------------------------------------------------------
| Id  | Operation           | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------
|*  1 |  COUNT STOPKEY      |         |      1 |        |      2 |00:00:00.01 |      10 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   2 |   NESTED LOOPS      |         |      1 |      2 |      2 |00:00:00.01 |      10 |
|   3 |    TABLE ACCESS FULL| EMP     |      1 |      2 |      2 |00:00:00.01 |       8 |
|*  4 |    INDEX UNIQUE SCAN| PK_DEPT |      2 |      1 |      2 |00:00:00.01 |       2 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<3)
   4 - access("E"."DEPTNO"="DEPTNO")


22 rows selected.

(三)使用dbms_xplan.display_awr()函数查看存储在awr中的历史执行计划

首先找个历史的sql_id

SQL> set line 300
SQL> select * from dba_hist_sqltext where rownum<3 and COMMAND_TYPE=3;

      DBID SQL_ID        SQL_TEXT                                                                         COMMAND_TYPE
---------- ------------- -------------------------------------------------------------------------------- ------------
4093928674 byaarkxm6b0r1 SELECT /*+ use_nl(u,o,t) */ NVL(T.DEGREE,1) FROM USER$ U,OBJ$ O,TAB$ T WHERE U.N            3
4093928674 4qfx5ur8a90ym select u.name,o.name from tab$ t,user$ u,obj$ o where t.bobj#=:1 and t.obj#=o.ob            3

然后使用dbms_xplan.display_awr()函数查看历史执行计划

SQL> select * from table(dbms_xplan.display_awr('4qfx5ur8a90ym'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 4qfx5ur8a90ym
--------------------
select u.name,o.name from tab$ t,user$ u,obj$ o where t.bobj#=:1 and
t.obj#=o.obj# and o.owner#=u.user#

Plan hash value: 1863015984

-----------------------------------------------------------------------------------------
| Id  | Operation                     | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |         |       |       |     6 (100)|          |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   1 |  NESTED LOOPS                 |         |     2 |    96 |     6   (0)| 00:00:01 |
|   2 |   NESTED LOOPS                |         |     2 |    62 |     4   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| TAB$    |     2 |    12 |     2   (0)| 00:00:01 |
|   4 |     INDEX RANGE SCAN          | I_TAB1  |     1 |       |     1   (0)| 00:00:01 |
|   5 |    TABLE ACCESS BY INDEX ROWID| OBJ$    |     1 |    25 |     1   (0)| 00:00:01 |
|   6 |     INDEX UNIQUE SCAN         | I_OBJ1  |     1 |       |     0   (0)|          |
|   7 |   TABLE ACCESS CLUSTER        | USER$   |     1 |    17 |     1   (0)| 00:00:01 |
|   8 |    INDEX UNIQUE SCAN          | I_USER# |     1 |       |     0   (0)|          |
-----------------------------------------------------------------------------------------


21 rows selected.

(四)使用$ORACLE_HOME/rdbms/admin/awrsqrpt.sql 生成指定sql_id的历史执行计划

SQL> @?/rdbms/admin/awrsqrpt.sql

Current Instance
~~~~~~~~~~~~~~~~

   DB Id    DB Name      Inst Num Instance
----------- ------------ -------- ------------
 4093928674 ORA10GR2            1 ora10gr2


Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
Enter value for report_type: 

Type Specified:  html


Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   DB Id     Inst Num DB Name      Instance     Host
------------ -------- ------------ ------------ ------------
* 4093928674        1 ORA10GR2     ora10gr2     rhl5.5
* 4093928674        1 ORA10GR2     ora10gr2     ora10gr2
* 4093928674        1 ORA10GR2     ora10gr2     ora11gr2

Using 4093928674 for database Id
Using          1 for instance number


Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed.  Pressing <return> without
specifying a number lists all completed snapshots.


Enter value for num_days: 

Listing all Completed Snapshots

                                                        Snap
Instance     DB Name        Snap Id    Snap Started    Level
------------ ------------ --------- ------------------ -----
ora10gr2     ORA10GR2            18 21 Feb 2013 19:42      1
                                 19 21 Feb 2013 21:00      1
                                 20 21 Feb 2013 22:00      1



Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 18
Begin Snapshot Id specified: 18

Enter value for end_snap: 20
End   Snapshot Id specified: 20




Specify the SQL Id
~~~~~~~~~~~~~~~~~~
Enter value for sql_id: 393c9swv9f3gt
… …
以下省略

注意:该处需要指定问题sql的sql_id,而sql_id可以通过awr报告或者addm等事先获取。

(五)通过客户端sql*plus自带的autotrace工具自动生成sql执行计划

首先,需要创建plustrace角色并给用户赋予相关的plustrace角色

SQL> grant connect,resource,select any table to test;

Grant succeeded.

SQL> conn test/test
Connected.
SQL> set autotrace traceonly
SP2-0618: Cannot find the Session Identifier.  Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report
SQL> show user
USER is "SYS"
SQL> @?/sqlplus/admin/plustrce.sql
SQL> 
SQL> drop role plustrace;
drop role plustrace
          *
ERROR at line 1:
ORA-01919: role 'PLUSTRACE' does not exist


SQL> create role plustrace;

Role created.

SQL> 
SQL> grant select on v_$sesstat to plustrace;

Grant succeeded.

SQL> grant select on v_$statname to plustrace;

Grant succeeded.

SQL> grant select on v_$mystat to plustrace;

Grant succeeded.

SQL> grant plustrace to dba with admin option;

Grant succeeded.

SQL> 
SQL> set echo off
SQL> grant plustrace to test;

Grant succeeded.

SQL> conn test/test
Connected.
SQL> set autotrace traceonly
SQL> select * from scott.emp e where e.deptno in (select deptno from scott.dept) and rownum<3;


Execution Plan
----------------------------------------------------------
Plan hash value: 653357053

-------------------------------------------------------------------------------
| Id  | Operation           | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |         |     2 |    80 |     2   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY      |         |       |       |            |          |
|   2 |   NESTED LOOPS      |         |     2 |    80 |     2   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| EMP     |     2 |    74 |     2   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN| PK_DEPT |     1 |     3 |     0   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<3)
   4 - access("E"."DEPTNO"="DEPTNO")


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         10  consistent gets
          0  physical reads
          0  redo size
        898  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)
          2  rows processed


(六)通过用户的sql_trace的dump文件查看用户sql的执行计划

首先,便于查找当前用户的trace文件,先标示该用户的trace文件。

SQL> alter session set tracefile_identifier='yallonking';

Session altered.

SQL> set line 200              

打开用户的sql_trace并收集sql执行的信息后关闭sql_trace

SQL> alter session set sql_trace=true;

Session altered.

SQL> select * from scott.emp e where e.deptno in (select deptno from scott.dept) and rownum<3;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30

SQL> alter session set sql_trace=false;

Session altered.

查找用户trace文件

SQL> !ls -ltr /u01/app/oracle/admin/ora10gr2/udump/*yallonking*
-rw-r----- 1 oracle oinstall   70 Feb 21 23:15 /u01/app/oracle/admin/ora10gr2/udump/ora10gr2_ora_9540_yallonking.trc
-rw-r----- 1 oracle oinstall 2003 Feb 21 23:16 /u01/app/oracle/admin/ora10gr2/udump/ora10gr2_ora_9647_yallonking.trc
-rw-r----- 1 oracle oinstall 2146 Feb 21 23:17 /u01/app/oracle/admin/ora10gr2/udump/ora10gr2_ora_9655_yallonking.trc

查看用户trace文件中的执行计划

SQL> !vi /u01/app/oracle/admin/ora10gr2/udump/ora10gr2_ora_9655_yallonking.trc

/u01/app/oracle/admin/ora10gr2/udump/ora10gr2_ora_9655_yallonking.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1
System name:    Linux
Node name:      ora10gr2
Release:        2.6.18-194.el5
Version:        #1 SMP Tue Mar 16 21:52:43 EDT 2010
Machine:        i686
Instance name: ora10gr2
Redo thread mounted by this instance: 1
Oracle process number: 15
Unix process pid: 9655, image: oracle@ora10gr2 (TNS V1-V3)

*** 2013-02-21 23:17:11.037
*** SERVICE NAME:(SYS$USERS) 2013-02-21 23:17:11.037
*** SESSION ID:(159.38) 2013-02-21 23:17:11.037
=====================
PARSING IN CURSOR #1 len=32 dep=0 uid=0 oct=42 lid=0 tim=1329606866247139 hv=1569151342 ad='3e3f7fbc'
alter session set sql_trace=true
END OF STMT
EXEC #1:c=1000,e=674,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1329606866247129
=====================
PARSING IN CURSOR #1 len=88 dep=0 uid=0 oct=3 lid=0 tim=1329606871299685 hv=747907697 ad='43f3aef4'
select * from scott.emp e where e.deptno in (select deptno from scott.dept) and rownum<3
END OF STMT
PARSE #1:c=1000,e=1402,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1329606871299673
EXEC #1:c=0,e=268,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1329606871300323
FETCH #1:c=1999,e=1761,p=0,cr=8,cu=0,mis=0,r=1,dep=0,og=1,tim=1329606871302309
FETCH #1:c=0,e=58,p=0,cr=2,cu=0,mis=0,r=1,dep=0,og=1,tim=1329606871304230
STAT #1 id=1 cnt=2 pid=0 pos=1 obj=0 op='COUNT STOPKEY (cr=10 pr=0 pw=0 time=1756 us)'
STAT #1 id=2 cnt=2 pid=1 pos=1 obj=0 op='NESTED LOOPS  (cr=10 pr=0 pw=0 time=1711 us)'
STAT #1 id=3 cnt=2 pid=2 pos=1 obj=51148 op='TABLE ACCESS FULL EMP (cr=8 pr=0 pw=0 time=1091 us)'
STAT #1 id=4 cnt=2 pid=2 pos=2 obj=51147 op='INDEX UNIQUE SCAN PK_DEPT (cr=2 pr=0 pw=0 time=637 us)'
*** 2013-02-21 23:17:28.830
=====================
PARSING IN CURSOR #1 len=33 dep=0 uid=0 oct=42 lid=0 tim=1329606883623491 hv=525901419 ad='43c4d108'
alter session set sql_trace=false
END OF STMT
PARSE #1:c=10999,e=10341,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1329606883623481
EXEC #1:c=999,e=588,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1329606883624361

(七)使用_optimizer_trace或者10053跟踪事件生成sql的执行计划

SQL> set line 200 
SQL> alter session set "_optimizer_trace"=all;

Session altered.

SQL> select * from scott.emp e where e.deptno in (select deptno from scott.dept) and rownum<4;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30

SQL> alter session set "_optimizer_trace"=none;

Session altered.

查看当前会话的trace文件位置

SQL> SELECT a.VALUE
  2  || b.symbol
  3  || c.instance_name
  4  || '_ora_'
  5  || d.spid
  6  || '.trc' trace_file
  7  FROM (SELECT VALUE
  8  FROM v$parameter
  9  WHERE NAME = 'user_dump_dest') a,
 10  (SELECT SUBSTR (VALUE, -6, 1) symbol
 11  FROM v$parameter
 12  WHERE NAME = 'user_dump_dest') b,
 13  (SELECT instance_name
 14  FROM v$instance) c,
 15  (SELECT spid
 16  FROM v$session s, v$process p, v$mystat m
 17  WHERE s.paddr = p.addr AND s.SID = m.SID AND m.statistic# = 0) d
 18  /

TRACE_FILE
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/admin/ora10gr2/udump/ora10gr2_ora_9759.trc

查看该trace文件中的部分

… …
sql_id=16v398fw5h696.
Current SQL statement for this session:
select * from scott.emp e where e.deptno in (select deptno from scott.dept) and rownum<4

============
Plan Table
============
---------------------------------------+-----------------------------------+
| Id  | Operation            | Name    | Rows  | Bytes | Cost  | Time      |
---------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT     |         |       |       |     2 |           |
| 1   |  COUNT STOPKEY       |         |       |       |       |           |
| 2   |   NESTED LOOPS       |         |     3 |   120 |     2 |  00:00:01 |
| 3   |    TABLE ACCESS FULL | EMP     |     3 |   111 |     2 |  00:00:01 |
| 4   |    INDEX UNIQUE SCAN | PK_DEPT |     1 |     3 |     0 |           |
---------------------------------------+-----------------------------------+
Predicate Information:
----------------------
1 - filter(ROWNUM<4)
4 - access("E"."DEPTNO"="DEPTNO")
… …

以上便是常用的查看一个sql的执行计划的方法,且均在oracle10g版本实验通过。

1 条留言  访客:0 条  博主:0 条

  1. liuyun

    写的很详尽,也很实用

给我留言

Copyright © YallonKing 保留所有权利.   Theme  Ality

用户登录

分享到: