当我们需要优化 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---