IT开放社区

查看 SQL 执行计划的几种方法

   我们需要优化 SQL 时,查看 SQL 执行计划是不可或缺的一步,本文总结生成 SQL 执行计划的几种方法。

   演示环境   

OS:RedHat 6.5

DB:oracle 11.2.0.4

   工具   

PL/SQL

DBMS_XPLAN

AUTOTRACE

SQL_TRACE 

   PL/SQL   

使用第三方工具 PL/SQL Developer,无疑是查看 SQL 的执行计划最快捷的一种方式。我们只需在 SQL 窗口按 F5 即可显示 SQL 的执行计划,这里就不做演示了。

但是,这种方式查看执行计划,其 SQL 并未真正在数据库中执行,有 Predicate Information(谓词信息),没有 Statistics Information(统计信息),所以,查看的执行计划,不一定是准确的执行计划

故,这种方式查看执行计划,适合在:SQL 测试、预审核以及在生产中 DML 语句的性能分析等场景时使用。

   DBMS_XPLAN   

1.DBMS_XPLAN.DISPLAY (显示计划表) 

PL/SQL 调用的即为此种方式。应用场景相同,这种方式显示计划表格和谓词信息。

语法:

DBMS_XPLAN.DISPLAY(
table_name    IN  VARCHAR2  DEFAULT  'PLAN_TABLE',
statement_id  IN  VARCHAR2  DEFAULT   NULL, 
format        IN  VARCHAR2  DEFAULT  'TYPICAL',
filter_preds  IN  VARCHAR2  DEFAULT   NULL);

  1.1 使用 EXPLAN PLAY FOR 解释计划

SQL> EXPLAIN PLAN FOR
  2  select user_id,username,default_tablespace,account_status,to_char(created,'yyyy-mm-dd hh24:mi:ss') CREATED from dba_users where account_status='OPEN';
Explained.

   1.2 使用 DBMS_XPLAN.DISPLAY 表格功能显示计划

SQL> SET LINESIZE 130
SQL> SET PAGESIZE 0
SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY);
Plan hash value: 3445749582
------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                         |    11 |  1309 |    21   (0)| 00:00:01 |
|*  1 |  HASH JOIN OUTER           |                         |    11 |  1309 |    21   (0)| 00:00:01 |
|*  2 |   HASH JOIN                |                         |    11 |   990 |    19   (0)| 00:00:01 |
|*  3 |    HASH JOIN               |                         |    11 |   968 |    17   (0)| 00:00:01 |
|*  4 |     HASH JOIN              |                         |    13 |  1040 |    15   (0)| 00:00:01 |
|*  5 |      HASH JOIN             |                         |    13 |   910 |    11   (0)| 00:00:01 |
|*  6 |       HASH JOIN            |                         |    13 |   871 |     7   (0)| 00:00:01 |
|   7 |        MERGE JOIN CARTESIAN|                         |     1 |    27 |     4   (0)| 00:00:01 |
|*  8 |         TABLE ACCESS FULL  | PROFILE$                |     1 |     8 |     2   (0)| 00:00:01 |
|   9 |         BUFFER SORT        |                         |     1 |    19 |     2   (0)| 00:00:01 |
|* 10 |          TABLE ACCESS FULL | USER_ASTATUS_MAP        |     1 |    19 |     2   (0)| 00:00:01 |
|* 11 |        TABLE ACCESS FULL   | USER$                   |    32 |  1280 |     3   (0)| 00:00:01 |
|  12 |       TABLE ACCESS FULL    | TS$                     |     6 |    18 |     4   (0)| 00:00:01 |
|  13 |      TABLE ACCESS FULL     | TS$                     |     6 |    60 |     4   (0)| 00:00:01 |
|* 14 |     TABLE ACCESS FULL      | PROFILE$                |     1 |     8 |     2   (0)| 00:00:01 |
|  15 |    TABLE ACCESS FULL       | PROFNAME$               |     1 |     2 |     2   (0)| 00:00:01 |
|* 16 |   TABLE ACCESS FULL        | RESOURCE_GROUP_MAPPING$ |     1 |    29 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("CGM"."VALUE"(+)="U"."NAME")
   2 - access("U"."RESOURCE$"="P"."PROFILE#")
   3 - access("U"."RESOURCE$"="PR"."PROFILE#")
   4 - access("U"."DATATS#"="DTS"."TS#")
   5 - access("U"."TEMPTS#"="TTS"."TS#")
   6 - access("U"."ASTATUS"="M"."STATUS#")
   8 - filter("DP"."RESOURCE#"=1 AND "DP"."TYPE#"=1 AND "DP"."PROFILE#"=0)
  10 - filter("M"."STATUS"='OPEN')
  11 - filter("U"."TYPE#"=1)
  14 - filter("PR"."RESOURCE#"=1 AND "PR"."TYPE#"=1)
  16 - filter("CGM"."ATTRIBUTE"(+)='ORACLE_USER' AND "CGM"."STATUS"(+)='ACTIVE')
38 rows selected.

2. DBMS_XPLAN.DISPLAY_CURSOR (显示游标缓存中的任意游标执行计划)

DISPLAY_CURSOR 需要对以下固定视图具有 select 权限:V$SQL_PLAN,V$SESSION和V$SQL_PLAN_STATISTICS_ALL。

语法:

DBMS_XPLAN.DISPLAY_CURSOR(
sql_id          IN VARCHAR2 DEFAULT NULL,
cursor_child_no IN NUMBER   DEFAULT 0,
format          IN VARCHAR2 DEFAULT 'TYPICAL');

默认情况下, DISPLAY_CURSOR 格式化会话执行的最后一个 SQL 语句的执行计划。

SQL> select user_id,username,default_tablespace,account_status,to_char(created,'yyyy-mm-dd hh24:mi:ss') CREATED from dba_users where account_status='OPEN';
   USER_ID USERNAME             DEFAULT_TABLESPACE   ACCOUNT_ST CREATED
---------- -------------------- -------------------- ---------- -------------------
         5 SYSTEM               SYSTEM               OPEN       2018-08-02 10:40:53
         0 SYS                  SYSTEM               OPEN       2018-08-02 10:40:53
        14 DIP                  USERS                OPEN       2018-08-02 10:41:27
        85 IT                   USERS                OPEN       2018-08-05 06:40:48
        83 ENMO_AUDIT           USERS                OPEN       2018-08-02 12:11:16
        84 OGG                  OGG_TS               OPEN       2018-08-05 06:02:00
6 rows selected.
SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  fc0kpaz0u02vz, child number 2
-------------------------------------
select user_id,username,default_tablespace,account_status,to_char(create
d,'yyyy-mm-dd hh24:mi:ss') CREATED from dba_users where
account_status='OPEN'
Plan hash value: 3445749582
------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                         |       |       |    21 (100)|          |
|*  1 |  HASH JOIN OUTER           |                         |    11 |  1309 |    21   (0)| 00:00:01 |
|*  2 |   HASH JOIN                |                         |    11 |   990 |    19   (0)| 00:00:01 |
|*  3 |    HASH JOIN               |                         |    11 |   968 |    17   (0)| 00:00:01 |
|*  4 |     HASH JOIN              |                         |    13 |  1040 |    15   (0)| 00:00:01 |
|*  5 |      HASH JOIN             |                         |    13 |   910 |    11   (0)| 00:00:01 |
|*  6 |       HASH JOIN            |                         |    13 |   871 |     7   (0)| 00:00:01 |
|   7 |        MERGE JOIN CARTESIAN|                         |     1 |    27 |     4   (0)| 00:00:01 |
|*  8 |         TABLE ACCESS FULL  | PROFILE$                |     1 |     8 |     2   (0)| 00:00:01 |
|   9 |         BUFFER SORT        |                         |     1 |    19 |     2   (0)| 00:00:01 |
|* 10 |          TABLE ACCESS FULL | USER_ASTATUS_MAP        |     1 |    19 |     2   (0)| 00:00:01 |
|* 11 |        TABLE ACCESS FULL   | USER$                   |    32 |  1280 |     3   (0)| 00:00:01 |
|  12 |       TABLE ACCESS FULL    | TS$                     |     6 |    18 |     4   (0)| 00:00:01 |
|  13 |      TABLE ACCESS FULL     | TS$                     |     6 |    60 |     4   (0)| 00:00:01 |
|* 14 |     TABLE ACCESS FULL      | PROFILE$                |     1 |     8 |     2   (0)| 00:00:01 |
|  15 |    TABLE ACCESS FULL       | PROFNAME$               |     1 |     2 |     2   (0)| 00:00:01 |
|* 16 |   TABLE ACCESS FULL        | RESOURCE_GROUP_MAPPING$ |     1 |    29 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("CGM"."VALUE"="U"."NAME")
   2 - access("U"."RESOURCE$"="P"."PROFILE#")
   3 - access("U"."RESOURCE$"="PR"."PROFILE#")
   4 - access("U"."DATATS#"="DTS"."TS#")
   5 - access("U"."TEMPTS#"="TTS"."TS#")
   6 - access("U"."ASTATUS"="M"."STATUS#")
   8 - filter(("DP"."RESOURCE#"=1 AND "DP"."TYPE#"=1 AND "DP"."PROFILE#"=0))
  10 - filter("M"."STATUS"='OPEN')
  11 - filter("U"."TYPE#"=1)
  14 - filter(("PR"."RESOURCE#"=1 AND "PR"."TYPE#"=1))
  16 - filter(("CGM"."ATTRIBUTE"='ORACLE_USER' AND "CGM"."STATUS"='ACTIVE'))
45 rows selected.

游标执行计划,由于 SQL 语句已在数据库中执行过,所以,执行计划信息是准确的。有 Predicate Information,没有 Statistics Information。

在实际使用中,DISPLAY_CURSOR 函数,可以指定 SQL_ID 和 游标编号来查看执行计划:

SQL> select user_id,username,default_tablespace,account_status,to_char(created,'yyyy-mm-dd hh24:mi:ss') CREATED from dba_users where account_status='OPEN';
   USER_ID USERNAME             DEFAULT_TABLESPACE   ACCOUNT_ST CREATED
---------- -------------------- -------------------- ---------- -------------------
         5 SYSTEM               SYSTEM               OPEN       2018-08-02 10:40:53
         0 SYS                  SYSTEM               OPEN       2018-08-02 10:40:53
        14 DIP                  USERS                OPEN       2018-08-02 10:41:27
        85 IT                   USERS                OPEN       2018-08-05 06:40:48
        83 ENMO_AUDIT           USERS                OPEN       2018-08-02 12:11:16
        84 OGG                  OGG_TS               OPEN       2018-08-05 06:02:00
6 rows selected.
SQL> SELECT sql_id,child_number FROM v$sql WHERE sql_text LIKE'%dba_users%';
SQL_ID        CHILD_NUMBER
------------- ------------
fc0kpaz0u02vz            0
fc0kpaz0u02vz            1
fc0kpaz0u02vz            2
SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('fc0kpaz0u02vz',2));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  fc0kpaz0u02vz, child number 2
-------------------------------------
select user_id,username,default_tablespace,account_status,to_char(create
d,'yyyy-mm-dd hh24:mi:ss') CREATED from dba_users where
account_status='OPEN'
Plan hash value: 3445749582
------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                         |       |       |    21 (100)|          |
|*  1 |  HASH JOIN OUTER           |                         |    11 |  1309 |    21   (0)| 00:00:01 |
|*  2 |   HASH JOIN                |                         |    11 |   990 |    19   (0)| 00:00:01 |
|*  3 |    HASH JOIN               |                         |    11 |   968 |    17   (0)| 00:00:01 |
|*  4 |     HASH JOIN              |                         |    13 |  1040 |    15   (0)| 00:00:01 |
|*  5 |      HASH JOIN             |                         |    13 |   910 |    11   (0)| 00:00:01 |
|*  6 |       HASH JOIN            |                         |    13 |   871 |     7   (0)| 00:00:01 |
|   7 |        MERGE JOIN CARTESIAN|                         |     1 |    27 |     4   (0)| 00:00:01 |
|*  8 |         TABLE ACCESS FULL  | PROFILE$                |     1 |     8 |     2   (0)| 00:00:01 |
|   9 |         BUFFER SORT        |                         |     1 |    19 |     2   (0)| 00:00:01 |
|* 10 |          TABLE ACCESS FULL | USER_ASTATUS_MAP        |     1 |    19 |     2   (0)| 00:00:01 |
|* 11 |        TABLE ACCESS FULL   | USER$                   |    32 |  1280 |     3   (0)| 00:00:01 |
|  12 |       TABLE ACCESS FULL    | TS$                     |     6 |    18 |     4   (0)| 00:00:01 |
|  13 |      TABLE ACCESS FULL     | TS$                     |     6 |    60 |     4   (0)| 00:00:01 |
|* 14 |     TABLE ACCESS FULL      | PROFILE$                |     1 |     8 |     2   (0)| 00:00:01 |
|  15 |    TABLE ACCESS FULL       | PROFNAME$               |     1 |     2 |     2   (0)| 00:00:01 |
|* 16 |   TABLE ACCESS FULL        | RESOURCE_GROUP_MAPPING$ |     1 |    29 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("CGM"."VALUE"="U"."NAME")
   2 - access("U"."RESOURCE$"="P"."PROFILE#")
   3 - access("U"."RESOURCE$"="PR"."PROFILE#")
   4 - access("U"."DATATS#"="DTS"."TS#")
   5 - access("U"."TEMPTS#"="TTS"."TS#")
   6 - access("U"."ASTATUS"="M"."STATUS#")
   8 - filter(("DP"."RESOURCE#"=1 AND "DP"."TYPE#"=1 AND "DP"."PROFILE#"=0))
  10 - filter("M"."STATUS"='OPEN')
  11 - filter("U"."TYPE#"=1)
  14 - filter(("PR"."RESOURCE#"=1 AND "PR"."TYPE#"=1))
  16 - filter(("CGM"."ATTRIBUTE"='ORACLE_USER' AND "CGM"."STATUS"='ACTIVE'))
45 rows selected.

3. DBMS_XPLAN.DISPLAY_AWR(显示存储在AWR中的执行计划的内容)

使用 DISPLAY_AWR 功能需要对以下固定视图具有select权限 DBA_HIST_SQL_PLAN,DBA_HIST_SQLTEXT 和 V$DATABASE。

语法:

DBMS_XPLAN.DISPLAY_AWR( 
sql_id            IN      VARCHAR2,
plan_hash_value   IN      NUMBER   DEFAULT NULL,
db_id             IN      NUMBER   DEFAULT NULL,
format            IN      VARCHAR2 DEFAULT TYPICAL);

与 DISPLAY_CURSOR 相比,多了 db_id 的选项。没有谓词信息和统计信息。可在游标被刷出缓存的场景下使用。这种方式查看执行计划是准确的,但是,如果 SQL 未被抓取到 AWR 中,则查询不到结果。

SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_AWR('fc0kpaz0u02vz'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID fc0kpaz0u02vz
--------------------
select user_id,username,default_tablespace,account_status,to_char(create
d,'yyyy-mm-dd hh24:mi:ss') CREATED from dba_users where
account_status='OPEN'
Plan hash value: 3445749582
------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                         |       |       |    19 (100)|          |
|   1 |  HASH JOIN OUTER           |                         |    11 |  1309 |    19   (0)| 00:00:01 |
|   2 |   HASH JOIN                |                         |    11 |   990 |    17   (0)| 00:00:01 |
|   3 |    HASH JOIN               |                         |    11 |   968 |    15   (0)| 00:00:01 |
|   4 |     HASH JOIN              |                         |    13 |  1040 |    13   (0)| 00:00:01 |
|   5 |      HASH JOIN             |                         |    13 |   910 |    10   (0)| 00:00:01 |
|   6 |       HASH JOIN            |                         |    13 |   871 |     7   (0)| 00:00:01 |
|   7 |        MERGE JOIN CARTESIAN|                         |     1 |    27 |     4   (0)| 00:00:01 |
|   8 |         TABLE ACCESS FULL  | PROFILE$                |     1 |     8 |     2   (0)| 00:00:01 |
|   9 |         BUFFER SORT        |                         |     1 |    19 |     2   (0)| 00:00:01 |
|  10 |          TABLE ACCESS FULL | USER_ASTATUS_MAP        |     1 |    19 |     2   (0)| 00:00:01 |
|  11 |        TABLE ACCESS FULL   | USER$                   |    30 |  1200 |     3   (0)| 00:00:01 |
|  12 |       TABLE ACCESS FULL    | TS$                     |     5 |    15 |     3   (0)| 00:00:01 |
|  13 |      TABLE ACCESS FULL     | TS$                     |     5 |    50 |     3   (0)| 00:00:01 |
|  14 |     TABLE ACCESS FULL      | PROFILE$                |     1 |     8 |     2   (0)| 00:00:01 |
|  15 |    TABLE ACCESS FULL       | PROFNAME$               |     1 |     2 |     2   (0)| 00:00:01 |
|  16 |   TABLE ACCESS FULL        | RESOURCE_GROUP_MAPPING$ |     1 |    29 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
30 rows selected.

   AUTOTRACE   

SET AUTOTRACE OFF                                    -->  此为默认值,即关闭Autotrace 

SET AUTOTRACE ON                                     -->  同时输出执行结果以及统计信息和执行计划信息

SET AUTOTRACE ON EXPLAIN                      -->  只打开执行计划报表,显示命令结果,不显示统计信息

SET AUTOTRACE ON STATISTICS                  -->  只打开统计信息报表,显示命令结果,不显示执行计划

SET AUTOTRACE TRACEONLY                       -->  不显示命令的执行结果,显示执行计划和统计信息

SET AUTOTRACE TRACEONLY EXPLAIN        -->  只显示执行计划信息

SET AUTOTRACE TRACEONLY STATISTICS    -->  只显示统计信息

该方法查看执行计划,SQL 已在数据库执行,计划是准确的。但是,如果 SQL 中包含有绑定变量,那么用该方法所获取到的执行计划,也有可能不是真实的。

SQL> set autotrace on
SQL> select user_id,username,default_tablespace,account_status,to_char(created,'yyyy-mm-dd hh24:mi:ss') CREATED from dba_users where account_status='OPEN';
   USER_ID USERNAME             DEFAULT_TABLESPACE   ACCOUNT_ST CREATED
---------- -------------------- -------------------- ---------- -------------------
         5 SYSTEM               SYSTEM               OPEN       2018-08-02 10:40:53
         0 SYS                  SYSTEM               OPEN       2018-08-02 10:40:53
        14 DIP                  USERS                OPEN       2018-08-02 10:41:27
        85 IT                   USERS                OPEN       2018-08-05 06:40:48
        83 ENMO_AUDIT           USERS                OPEN       2018-08-02 12:11:16
        84 OGG                  OGG_TS               OPEN       2018-08-05 06:02:00
6 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3445749582
------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                         |    11 |  1309 |    21   (0)| 00:00:01 |
|*  1 |  HASH JOIN OUTER           |                         |    11 |  1309 |    21   (0)| 00:00:01 |
|*  2 |   HASH JOIN                |                         |    11 |   990 |    19   (0)| 00:00:01 |
|*  3 |    HASH JOIN               |                         |    11 |   968 |    17   (0)| 00:00:01 |
|*  4 |     HASH JOIN              |                         |    13 |  1040 |    15   (0)| 00:00:01 |
|*  5 |      HASH JOIN             |                         |    13 |   910 |    11   (0)| 00:00:01 |
|*  6 |       HASH JOIN            |                         |    13 |   871 |     7   (0)| 00:00:01 |
|   7 |        MERGE JOIN CARTESIAN|                         |     1 |    27 |     4   (0)| 00:00:01 |
|*  8 |         TABLE ACCESS FULL  | PROFILE$                |     1 |     8 |     2   (0)| 00:00:01 |
|   9 |         BUFFER SORT        |                         |     1 |    19 |     2   (0)| 00:00:01 |
|* 10 |          TABLE ACCESS FULL | USER_ASTATUS_MAP        |     1 |    19 |     2   (0)| 00:00:01 |
|* 11 |        TABLE ACCESS FULL   | USER$                   |    32 |  1280 |     3   (0)| 00:00:01 |
|  12 |       TABLE ACCESS FULL    | TS$                     |     6 |    18 |     4   (0)| 00:00:01 |
|  13 |      TABLE ACCESS FULL     | TS$                     |     6 |    60 |     4   (0)| 00:00:01 |
|* 14 |     TABLE ACCESS FULL      | PROFILE$                |     1 |     8 |     2   (0)| 00:00:01 |
|  15 |    TABLE ACCESS FULL       | PROFNAME$               |     1 |     2 |     2   (0)| 00:00:01 |
|* 16 |   TABLE ACCESS FULL        | RESOURCE_GROUP_MAPPING$ |     1 |    29 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("CGM"."VALUE"(+)="U"."NAME")
   2 - access("U"."RESOURCE$"="P"."PROFILE#")
   3 - access("U"."RESOURCE$"="PR"."PROFILE#")
   4 - access("U"."DATATS#"="DTS"."TS#")
   5 - access("U"."TEMPTS#"="TTS"."TS#")
   6 - access("U"."ASTATUS"="M"."STATUS#")
   8 - filter("DP"."RESOURCE#"=1 AND "DP"."TYPE#"=1 AND "DP"."PROFILE#"=0)
  10 - filter("M"."STATUS"='OPEN')
  11 - filter("U"."TYPE#"=1)
  14 - filter("PR"."RESOURCE#"=1 AND "PR"."TYPE#"=1)
  16 - filter("CGM"."ATTRIBUTE"(+)='ORACLE_USER' AND "CGM"."STATUS"(+)='ACTIVE')
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         33  consistent gets
          0  physical reads
          0  redo size
       1095  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          6  rows processed
SQL> set autotrace off

   SQL_TRACE     

1. 开启 session 级别 SQL_TRACE 跟踪

SQL> alter session set sql_trace=true;
Session altered.

2. 执行测试 SQL

SQL> col DEFAULT_TABLESPACE for a20
SQL> col USERNAME for a20
SQL> col ACCOUNT_STATUS for a10
SQL> select user_id,username,default_tablespace,account_status,to_char(created,'yyyy-mm-dd hh24:mi:ss') CREATED from dba_users where account_status='OPEN';
   USER_ID USERNAME             DEFAULT_TABLESPACE   ACCOUNT_ST CREATED
---------- -------------------- -------------------- ---------- -------------------
         5 SYSTEM               SYSTEM               OPEN       2018-08-02 10:40:53
         0 SYS                  SYSTEM               OPEN       2018-08-02 10:40:53
        14 DIP                  USERS                OPEN       2018-08-02 10:41:27
        85 IT                   USERS                OPEN       2018-08-05 06:40:48
        83 ENMO_AUDIT           USERS                OPEN       2018-08-02 12:11:16
        84 OGG                  OGG_TS               OPEN       2018-08-05 06:02:00
6 rows selected.

3. 关闭 SQL_TRACE 跟踪

SQL> alter session set sql_trace=false;
Session altered.

4. 查看生成的跟踪文件位置

SQL> select value from v$diag_info where name like 'Default Trace File';
VALUE
------------------------------------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/itkf/itkf/trace/itkf_ora_10023.trc

5. 由于 SQL_TRACE 生成的跟踪文件可读性差,可通过 Oracle 自带的工具 Tkprof 处理原始跟踪文件,以增加其可读性

[oracle@oracle11g ~]$ tkprof /u01/app/oracle/diag/rdbms/itkf/itkf/trace/itkf_ora_10023.trc
output = sql_trace_test.txt
TKPROF: Release 11.2.0.4.0 - Development on Mon Aug 6 17:42:32 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

6. 查看跟踪文件输出的执行计划信息

[oracle@oracle11g ~]$ more sql_trace_test.txt
TKPROF: Release 11.2.0.4.0 - Development on Mon Aug 6 17:42:32 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Trace file: /u01/app/oracle/diag/rdbms/itkf/itkf/trace/itkf_ora_10023.trc
Sort options: default
********************************************************************************
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: fc0kpaz0u02vz Plan Hash: 3445749582
select user_id,username,default_tablespace,account_status,to_char(created,
  'yyyy-mm-dd hh24:mi:ss') CREATED 
from dba_users where account_status='OPEN'
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         33          0           6
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0         33          0           6
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         6          6          6  HASH JOIN OUTER (cr=33 pr=0 pw=0 time=2003 us cost=21 size=1309 card=11)
         6          6          6   HASH JOIN  (cr=30 pr=0 pw=0 time=1723 us cost=19 size=990 card=11)
         6          6          6    HASH JOIN  (cr=28 pr=0 pw=0 time=1570 us cost=17 size=968 card=11)
         6          6          6     HASH JOIN  (cr=26 pr=0 pw=0 time=1435 us cost=15 size=1040 card=13)
         6          6          6      HASH JOIN  (cr=18 pr=0 pw=0 time=790 us cost=11 size=910 card=13)
         6          6          6       HASH JOIN  (cr=10 pr=0 pw=0 time=494 us cost=7 size=871 card=13)
         1          1          1        MERGE JOIN CARTESIAN (cr=4 pr=0 pw=0 time=191 us cost=4 size=27 card=1)
         1          1          1         TABLE ACCESS FULL PROFILE$ (cr=2 pr=0 pw=0 time=138 us cost=2 size=8 card=1)
         1          1          1         BUFFER SORT (cr=2 pr=0 pw=0 time=48 us cost=2 size=19 card=1)
         1          1          1          TABLE ACCESS FULL USER_ASTATUS_MAP (cr=2 pr=0 pw=0 time=18 us cost=2 size=19 card=1)
        32         32         32        TABLE ACCESS FULL USER$ (cr=6 pr=0 pw=0 time=72 us cost=3 size=1280 card=32)
         6          6          6       TABLE ACCESS FULL TS$ (cr=8 pr=0 pw=0 time=38 us cost=4 size=18 card=6)
         6          6          6      TABLE ACCESS FULL TS$ (cr=8 pr=0 pw=0 time=14 us cost=4 size=60 card=6)
         1          1          1     TABLE ACCESS FULL PROFILE$ (cr=2 pr=0 pw=0 time=6 us cost=2 size=8 card=1)
         1          1          1    TABLE ACCESS FULL PROFNAME$ (cr=2 pr=0 pw=0 time=16 us cost=2 size=2 card=1)
         2          2          2   TABLE ACCESS FULL RESOURCE_GROUP_MAPPING$ (cr=3 pr=0 pw=0 time=13 us cost=2 size=29 card=1)
********************************************************************************
Trace file: /u01/app/oracle/diag/rdbms/itkf/itkf/trace/itkf_ora_10023.trc
Trace file compatibility: 11.1.0.7
Sort options: default
       1  session in tracefile.
       3  user  SQL statements in trace file.
       0  internal SQL statements in trace file.
       3  SQL statements in trace file.
       3  unique SQL statements in trace file.
      64  lines in trace file.
     141  elapsed seconds in trace file.

从以上输出可以看出,这种方式获取执行计划,相对比较繁琐。此外,10046 事件也可生成与 SQL_TRACE 类似的执行计划,这里不做演示了。

---如有补充请留言---

---Good Luck---

发表评论:

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。

网站分类
最新文章
    随机文章
      站点信息
      • 文章总数:113
      • 页面总数:2
      • 分类总数:3
      • 标签总数:1
      • 评论总数:0
      • 浏览总数:45518
      左邻右舍

      BlogPowerBy Z-BlogPHP 1.5 Zero ;Theme By 爱墙纸

      IT开放社区:京ICP备13044647号.初创于Oracle DBA实战班.邮箱:admin@itkaifang.com

      分享:

      支付宝

      微信