在我 2014 年总结的 SQL Tuning 基础概述中, 其实已经介绍了一些查看 SQL 执行计划的方法, 但是不够系统和全面, 所以本次 SQL 优化专题, 就首先要系统的介绍一下查看 SQL 执行计划的方法
本文示例 SQL 为:
- --set lines 1000 pages 1000
- select a.empno, a.ename, b.dname, a.job, a.sal from emp a, dept b where a.deptno = b.deptno and empno = 7788;
- 1.explain plan for
- 2.set autotrace
- 3.dbms_xplan
- 3.1 dbms_xplan.display_cursor(null,null,'allstats last')
- 3.2 dbms_xplan.display_cursor('&sql_id',null,'advanced')
- 3.3 dbms_xplan.display_awr('&sql_id')
- 4.awrsqrpt
- 5.10046 event
- reference
- 1.explain plan for SQL;
通过 explain plan for 命令查看 SQL 的执行计划, 这种方法 SQL 并不真实执行, 一般适用于上线前的 SQL 预审, 尤其对 DML 语句, 由于 SQL 不执行, 不用担心对生产数据造成影响这种方法查看的执行计划有 Predicate Information, 无 Statistics, 查看到的执行计划不一定真实, 第三方工具 PL/SQL Developer 中 F5 查看执行计划也是调用的这个方法
使用 explain plan for 命令查看 SQL 的执行计划:
- explain plan for
- select a.empno, a.ename, b.dname, a.job, a.sal from emp a, dept b where a.deptno = b.deptno and empno = 7788;
- select * from table(dbms_xplan.display);
示例如下:
- JINGYU@jyzhao1>explain plan for
- 2 select a.empno, a.ename, b.dname, a.job, a.sal from emp a, dept b where a.deptno = b.deptno and empno = 7788;
- Explained.
- Elapsed: 00:00:00.01
- JINGYU@jyzhao1>select * from table(dbms_xplan.display);
- PLAN_TABLE_OUTPUT
- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
- Plan hash value: 1123238657
- ---------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- ---------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 1 | 74 | 6 (0)| 00:00:01 |
- |* 1 | HASH JOIN | | 1 | 74 | 6 (0)| 00:00:01 |
- |* 2 | TABLE ACCESS FULL| EMP | 1 | 52 | 3 (0)| 00:00:01 |
- | 3 | TABLE ACCESS FULL| DEPT | 4 | 88 | 3 (0)| 00:00:01 |
- ---------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 1 - access("A"."DEPTNO"="B"."DEPTNO")
- 2 - filter("EMPNO"=7788)
- Note
- -----
- - dynamic sampling used for this statement (level=2)
- 20 rows selected.
- Elapsed: 00:00:00.03
- JINGYU@jyzhao1>
- 2.set autotrace
通过 set autotrace 查看 SQL 的执行计划, 这种方法 SQL 真实执行 (除 set autot trace exp 针对 select 语句也不执行, 但对 DML 语句还是会执行的), 这种方法查看的执行计划有 Predicate Information, 有 Statistics, 查看的执行计划是准确的但由于 SQL 需要真实执行, 所以对于有绑定变量值的 SQL, 还需要输入对应的变量值才可以执行, 比较麻烦
使用 set autotrace 查看 SQL 的执行计划:
- set autotrace on
- select a.empno, a.ename, b.dname, a.job, a.sal from emp a, dept b where a.deptno = b.deptno and empno = 7788;
- set autotrace off
示例如下:
- JINGYU@jyzhao1>set autotrace on
- JINGYU@jyzhao1>select a.empno, a.ename, b.dname, a.job, a.sal from emp a, dept b where a.deptno = b.deptno and empno = 7788;
- EMPNO ENAME DNAME JOB SAL
- ---------- ---------- -------------- --------- ----------
- 7788 SCOTT RESEARCH ANALYST 3000
- Elapsed: 00:00:00.01
- Execution Plan
- ----------------------------------------------------------
- Plan hash value: 1123238657
- ---------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- ---------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 1 | 74 | 6 (0)| 00:00:01 |
- |* 1 | HASH JOIN | | 1 | 74 | 6 (0)| 00:00:01 |
- |* 2 | TABLE ACCESS FULL| EMP | 1 | 52 | 3 (0)| 00:00:01 |
- | 3 | TABLE ACCESS FULL| DEPT | 4 | 88 | 3 (0)| 00:00:01 |
- ---------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 1 - access("A"."DEPTNO"="B"."DEPTNO")
- 2 - filter("EMPNO"=7788)
- Note
- -----
- - dynamic sampling used for this statement (level=2)
- Statistics
- ----------------------------------------------------------
- 7 recursive calls
- 0 db block gets
- 15 consistent gets
- 0 physical reads
- 0 redo size
- 814 bytes sent via SQL*Net to client
- 520 bytes received via SQL*Net from client
- 2 SQL*Net roundtrips to/from client
- 0 sorts (memory)
- 0 sorts (disk)
- 1 rows processed
- JINGYU@jyzhao1>set autotrace off
- JINGYU@jyzhao1>
- 3.dbms_xplan
dbms_xplan 下面有很多函数可以调用, 其中最常用的是 display_cursor 和 display_awr 函数, 下面依次介绍
3.1 dbms_xplan.display_cursor(null,null,'allstats last')
通过 dbms_xplan.display_cursor(null,null,'allstats last') 查看 SQL 的执行计划, SQL 会真实执行 (对应的缺点不再赘述), 这种方法查看的执行计划有 Predicate Information, 无 Statistics, 查看的执行计划是准确的, 并且有每一步真实处理行数和时间
使用 dbms_xplan.display_cursor(null,null,'allstats last') 查看 SQL 的执行计划:
-- 需要确认 statistics_level 设置为 ALL, 否则 SQL 语句第一个关键字后就需要加上 /*+ gather_plan_statistics */, 我习惯用前者
- show parameter statistics_level(一般数据库默认是 TYPICAL)
- alter session set statistics_level = ALL;
- select a.empno, a.ename, b.dname, a.job, a.sal from emp a, dept b where a.deptno = b.deptno and empno = 7788;
- select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
示例如下:
- JINGYU@jyzhao1>alter session set statistics_level = ALL;
- Session altered.
- JINGYU@jyzhao1>select a.empno, a.ename, b.dname, a.job, a.sal from emp a, dept b where a.deptno = b.deptno and empno = 7788;
- EMPNO ENAME DNAME JOB SAL
- ---------- ---------- -------------- --------- ----------
- 7788 SCOTT RESEARCH ANALYST 3000
- JINGYU@jyzhao1>select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
- PLAN_TABLE_OUTPUT
- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
- SQL_ID 39dv3d8jkzyuw, child number 1
- -------------------------------------
- select a.empno, a.ename, b.dname, a.job, a.sal from emp a, dept b where
- a.deptno = b.deptno and empno = 7788
- Plan hash value: 1123238657
- ----------------------------------------------------------------------------------------------------------------
- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
- ----------------------------------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 7 | | | |
- |* 1 | HASH JOIN | | 1 | 1 | 1 |00:00:00.01 | 7 | 1214K| 1214K| 762K (0)|
- |* 2 | TABLE ACCESS FULL| EMP | 1 | 1 | 1 |00:00:00.01 | 3 | | | |
- | 3 | TABLE ACCESS FULL| DEPT | 1 | 4 | 4 |00:00:00.01 | 4 | | | |
- ----------------------------------------------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 1 - access("A"."DEPTNO"="B"."DEPTNO")
- 2 - filter("EMPNO"=7788)
- Note
- -----
- - dynamic sampling used for this statement (level=2)
- 26 rows selected.
- JINGYU@jyzhao1>
- 3.2 dbms_xplan.display_cursor('&sql_id',null,'advanced')
通过 dbms_xplan.display_cursor('&sql_id',null,'advanced') 查看 SQL 的执行计划, 这种方法查看的执行计划有 Predicate Information, 无 Statistics,SQL 是数据库中之前真实执行过的, 对应执行计划是之前 SQL 真实执行过的执行计划, 是准确的使用这种方法的前提是需要 SQL 的 cursor 没有被刷出 shared pool, 否则查不到结果
使用 dbms_xplan.display_cursor('&sql_id',null,'advanced') 查看 SQL 的执行计划:
-- 查询 sql_id
- select sql_id, sql_text from v$sql where sql_text like 'select a.empno%';
- 39dv3d8jkzyuw
-- 根据查询的 sql_id 查看执行计划
select * from table(dbms_xplan.display_cursor('&sql_id',null,'advanced'));
-- 附: 函数 DISPLAY_CURSOR 的参数说明
- FUNCTION DISPLAY_CURSOR RETURNS DBMS_XPLAN_TYPE_TABLE
- Argument Name Type In/Out Default?
- ------------------------------ ----------------------- ------ --------
- SQL_ID VARCHAR2 IN DEFAULT
- CURSOR_CHILD_NO NUMBER(38) IN DEFAULT
- FORMAT VARCHAR2 IN DEFAULT
示例如下:
- JINGYU@jyzhao1>select * from table(dbms_xplan.display_cursor('&sql_id',null,'advanced'));
- Enter value for sql_id: 39dv3d8jkzyuw
- old 1: select * from table(dbms_xplan.display_cursor('&sql_id',null,'advanced'))
- new 1: select * from table(dbms_xplan.display_cursor('39dv3d8jkzyuw',null,'advanced'))
- PLAN_TABLE_OUTPUT
- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
- SQL_ID 39dv3d8jkzyuw, child number 0
- -------------------------------------
- select a.empno, a.ename, b.dname, a.job, a.sal from emp a, dept b where
- a.deptno = b.deptno and empno = 7788
- Plan hash value: 1123238657
- ---------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- ---------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | | | 6 (100)| |
- |* 1 | HASH JOIN | | 1 | 74 | 6 (0)| 00:00:01 |
- |* 2 | TABLE ACCESS FULL| EMP | 1 | 52 | 3 (0)| 00:00:01 |
- | 3 | TABLE ACCESS FULL| DEPT | 4 | 88 | 3 (0)| 00:00:01 |
- ---------------------------------------------------------------------------
- Query Block Name / Object Alias (identified by operation id):
- -------------------------------------------------------------
- 1 - SEL$1
- 2 - SEL$1 / A@SEL$1
- 3 - SEL$1 / B@SEL$1
- Outline Data
- -------------
- /*+
- BEGIN_OUTLINE_DATA
- IGNORE_OPTIM_EMBEDDED_HINTS
- OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
- DB_VERSION('11.2.0.4')
- ALL_ROWS
- OUTLINE_LEAF(@"SEL$1")
- FULL(@"SEL$1" "A"@"SEL$1")
- FULL(@"SEL$1" "B"@"SEL$1")
- LEADING(@"SEL$1" "A"@"SEL$1" "B"@"SEL$1")
- USE_HASH(@"SEL$1" "B"@"SEL$1")
- END_OUTLINE_DATA
- */
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 1 - access("A"."DEPTNO"="B"."DEPTNO")
- 2 - filter("EMPNO"=7788)
- Column Projection Information (identified by operation id):
- -----------------------------------------------------------
- 1 - (#keys=1) "EMPNO"[NUMBER,22], "A"."ENAME"[VARCHAR2,10],
- "A"."JOB"[VARCHAR2,9], "A"."SAL"[NUMBER,22], "B"."DNAME"[VARCHAR2,14]
- 2 - "EMPNO"[NUMBER,22], "A"."ENAME"[VARCHAR2,10],
- "A"."JOB"[VARCHAR2,9], "A"."SAL"[NUMBER,22], "A"."DEPTNO"[NUMBER,22]
- 3 - "B"."DEPTNO"[NUMBER,22], "B"."DNAME"[VARCHAR2,14]
- Note
- -----
- - dynamic sampling used for this statement (level=2)
- SQL_ID 39dv3d8jkzyuw, child number 1
- -------------------------------------
- select a.empno, a.ename, b.dname, a.job, a.sal from emp a, dept b where
- a.deptno = b.deptno and empno = 7788
- Plan hash value: 1123238657
- ---------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- ---------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | | | 6 (100)| |
- |* 1 | HASH JOIN | | 1 | 74 | 6 (0)| 00:00:01 |
- |* 2 | TABLE ACCESS FULL| EMP | 1 | 52 | 3 (0)| 00:00:01 |
- | 3 | TABLE ACCESS FULL| DEPT | 4 | 88 | 3 (0)| 00:00:01 |
- ---------------------------------------------------------------------------
- Query Block Name / Object Alias (identified by operation id):
- -------------------------------------------------------------
- 1 - SEL$1
- 2 - SEL$1 / A@SEL$1
- 3 - SEL$1 / B@SEL$1
- Outline Data
- -------------
- /*+
- BEGIN_OUTLINE_DATA
- IGNORE_OPTIM_EMBEDDED_HINTS
- OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
- DB_VERSION('11.2.0.4')
- ALL_ROWS
- OUTLINE_LEAF(@"SEL$1")
- FULL(@"SEL$1" "A"@"SEL$1")
- FULL(@"SEL$1" "B"@"SEL$1")
- LEADING(@"SEL$1" "A"@"SEL$1" "B"@"SEL$1")
- USE_HASH(@"SEL$1" "B"@"SEL$1")
- END_OUTLINE_DATA
- */
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 1 - access("A"."DEPTNO"="B"."DEPTNO")
- 2 - filter("EMPNO"=7788)
- Column Projection Information (identified by operation id):
- -----------------------------------------------------------
- 1 - (#keys=1) "EMPNO"[NUMBER,22], "A"."ENAME"[VARCHAR2,10],
- "A"."JOB"[VARCHAR2,9], "A"."SAL"[NUMBER,22], "B"."DNAME"[VARCHAR2,14]
- 2 - "EMPNO"[NUMBER,22], "A"."ENAME"[VARCHAR2,10],
- "A"."JOB"[VARCHAR2,9], "A"."SAL"[NUMBER,22], "A"."DEPTNO"[NUMBER,22]
- 3 - "B"."DEPTNO"[NUMBER,22], "B"."DNAME"[VARCHAR2,14]
- Note
- -----
- - dynamic sampling used for this statement (level=2)
- SQL_ID 39dv3d8jkzyuw, child number 2
- -------------------------------------
- select a.empno, a.ename, b.dname, a.job, a.sal from emp a, dept b where
- a.deptno = b.deptno and empno = 7788
- Plan hash value: 1123238657
- ---------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- ---------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | | | 6 (100)| |
- |* 1 | HASH JOIN | | 1 | 74 | 6 (0)| 00:00:01 |
- |* 2 | TABLE ACCESS FULL| EMP | 1 | 52 | 3 (0)| 00:00:01 |
- | 3 | TABLE ACCESS FULL| DEPT | 4 | 88 | 3 (0)| 00:00:01 |
- ---------------------------------------------------------------------------
- Query Block Name / Object Alias (identified by operation id):
- -------------------------------------------------------------
- 1 - SEL$1
- 2 - SEL$1 / A@SEL$1
- 3 - SEL$1 / B@SEL$1
- Outline Data
- -------------
- /*+
- BEGIN_OUTLINE_DATA
- IGNORE_OPTIM_EMBEDDED_HINTS
- OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
- DB_VERSION('11.2.0.4')
- ALL_ROWS
- OUTLINE_LEAF(@"SEL$1")
- FULL(@"SEL$1" "A"@"SEL$1")
- FULL(@"SEL$1" "B"@"SEL$1")
- LEADING(@"SEL$1" "A"@"SEL$1" "B"@"SEL$1")
- USE_HASH(@"SEL$1" "B"@"SEL$1")
- END_OUTLINE_DATA
- */
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 1 - access("A"."DEPTNO"="B"."DEPTNO")
- 2 - filter("EMPNO"=7788)
- Column Projection Information (identified by operation id):
- -----------------------------------------------------------
- 1 - (#keys=1) "EMPNO"[NUMBER,22], "A"."ENAME"[VARCHAR2,10],
- "A"."JOB"[VARCHAR2,9], "A"."SAL"[NUMBER,22], "B"."DNAME"[VARCHAR2,14]
- 2 - "EMPNO"[NUMBER,22], "A"."ENAME"[VARCHAR2,10],
- "A"."JOB"[VARCHAR2,9], "A"."SAL"[NUMBER,22], "A"."DEPTNO"[NUMBER,22]
- 3 - "B"."DEPTNO"[NUMBER,22], "B"."DNAME"[VARCHAR2,14]
- Note
- -----
- - dynamic sampling used for this statement (level=2)
- 177 rows selected.
- JINGYU@jyzhao1>
由于上面没有指定 CURSOR_CHILD_NO, 所以全部都会显示出来, 当然也可以指定某个 CURSOR_CHILD_NO, 比如说:
select * from table(dbms_xplan.display_cursor('&sql_id',0,'advanced'));
这样结果就只会显示 CURSOR_CHILD_NO=0 的执行计划
3.3 dbms_xplan.display_awr('&sql_id')
通过 dbms_xplan.display_awr('&sql_id') 查看 SQL 的执行计划, 这种方法查看的执行计划无 Predicate Information, 无 Statistics, 查看的执行计划是准确的如果对应 SQL 没有被抓取到 AWR 中, 就查不到结果
使用 dbms_xplan.display_awr('&sql_id') 查看 SQL 的执行计划:
-- 查询 sql_id
39dv3d8jkzyuw
-- 根据查询的 sql_id 查看执行计划
select * from table(dbms_xplan.display_awr('&sql_id'));
-- 附: 函数 DISPLAY_AWR 的参数说明
- FUNCTION DISPLAY_AWR RETURNS DBMS_XPLAN_TYPE_TABLE
- Argument Name Type In/Out Default?
- ------------------------------ ----------------------- ------ --------
- SQL_ID VARCHAR2 IN
- PLAN_HASH_VALUE NUMBER(38) IN DEFAULT
- DB_ID NUMBER(38) IN DEFAULT
- FORMAT VARCHAR2 IN DEFAULT
示例如下:
- JINGYU@jyzhao1>select * from table(dbms_xplan.display_awr('&sql_id'));
- Enter value for sql_id: 39dv3d8jkzyuw
- PLAN_TABLE_OUTPUT
- ------------------------------------------------------------------------------
- SQL_ID 39dv3d8jkzyuw
- --------------------
- select a.empno, a.ename, b.dname, a.job, a.sal from emp a, dept b where
- a.deptno = b.deptno and empno = 7788
- Plan hash value: 1123238657
- ---------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- ---------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | | | 6 (100)| |
- | 1 | HASH JOIN | | 1 | 74 | 6 (0)| 00:00:01 |
- | 2 | TABLE ACCESS FULL| EMP | 1 | 52 | 3 (0)| 00:00:01 |
- | 3 | TABLE ACCESS FULL| DEPT | 4 | 88 | 3 (0)| 00:00:01 |
- ---------------------------------------------------------------------------
- Note
- -----
- - dynamic sampling used for this statement (level=2)
- 20 rows selected.
- JINGYU@jyzhao1>
也可以加参数显示更多的信息, 比如可以显示出 Outline Data, 但没有 Predicate Information, 没有 Statistics:
- JINGYU@jyzhao1>select * from table(dbms_xplan.display_awr('&sql_id',NULL,NULL,'advanced'));
- Enter value for sql_id: 39dv3d8jkzyuw
- PLAN_TABLE_OUTPUT
- ------------------------------------------------------------------------------
- SQL_ID 39dv3d8jkzyuw
- --------------------
- select a.empno, a.ename, b.dname, a.job, a.sal from emp a, dept b where
- a.deptno = b.deptno and empno = 7788
- Plan hash value: 1123238657
- ---------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- ---------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | | | 6 (100)| |
- | 1 | HASH JOIN | | 1 | 74 | 6 (0)| 00:00:01 |
- | 2 | TABLE ACCESS FULL| EMP | 1 | 52 | 3 (0)| 00:00:01 |
- | 3 | TABLE ACCESS FULL| DEPT | 4 | 88 | 3 (0)| 00:00:01 |
- ---------------------------------------------------------------------------
- Query Block Name / Object Alias (identified by operation id):
- -------------------------------------------------------------
- 1 - SEL$1
- 2 - SEL$1 / A@SEL$1
- 3 - SEL$1 / B@SEL$1
- Outline Data
- -------------
- /*+
- BEGIN_OUTLINE_DATA
- IGNORE_OPTIM_EMBEDDED_HINTS
- OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
- DB_VERSION('11.2.0.4')
- ALL_ROWS
- OUTLINE_LEAF(@"SEL$1")
- FULL(@"SEL$1" "A"@"SEL$1")
- FULL(@"SEL$1" "B"@"SEL$1")
- LEADING(@"SEL$1" "A"@"SEL$1" "B"@"SEL$1")
- USE_HASH(@"SEL$1" "B"@"SEL$1")
- END_OUTLINE_DATA
- */
- Note
- -----
- - dynamic sampling used for this statement (level=2)
- 44 rows selected.
- JINGYU@jyzhao1>
- 4.awrsqrpt
通过 awrsqrpt 查看 SQL 的执行计划, 这种方式是导出 SQL 的 awr 报告, 这种方法查看的执行计划无 Predicate Information, 但有 Statistics, 并且同时包含 SQL 单次和整体的统计信息, 查看的执行计划是准确的
使用 awrsqrpt 查看 SQL 的执行计划:
- @?/rdbms/admin/awrsqrpt
- Specify the Begin and End Snapshot Ids
- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
- Enter value for begin_snap: 240
- Begin Snapshot Id specified: 240
- Enter value for end_snap: 244
- End Snapshot Id specified: 244
- Specify the SQL Id
- ~~~~~~~~~~~~~~~~~~
- Enter value for sql_id: 39dv3d8jkzyuw
- SQL ID specified: 39dv3d8jkzyuw
- ...
- Report written to awrsqlrpt_1_240_244.html
这种执行计划的显示结果如下图:
5.10046 event
通过 10046 event 的跟踪文件查看 SQL 的执行计划, 这种方法查看的执行计划是准确的, 语句中函数, 递归调用都会被详细列出, 但没有谓词信息, 而且这种方法获取执行计划比较麻烦
使用 10046 event 查看 SQL 的执行计划:
- alter session set events '10046 trace name context forever, level 12';
- select a.empno, a.ename, b.dname, a.job, a.sal from emp a, dept b where a.deptno = b.deptno and empno = 7788;
- alter session set events '10046 trace name context off';
-- 使用 tkprof 美化 10046 event 的跟踪文件, 使其更易读:
tkprof jyzhao1_ora_14139.trc jyzhao1_ora_14139.txt sys=no
示例如下:
- JINGYU@jyzhao1>alter session set events '10046 trace name context forever, level 12';
- Session altered.
- JINGYU@jyzhao1>select a.empno, a.ename, b.dname, a.job, a.sal from emp a, dept b where a.deptno = b.deptno and empno = 7788;
- EMPNO ENAME DNAME JOB SAL
- ---------- ---------- -------------- --------- ----------
- 7788 SCOTT RESEARCH ANALYST 3000
- JINGYU@jyzhao1>alter session set events '10046 trace name context off';
- Session altered.
-- 使用 tkprof 美化 10046 event 的跟踪文件相关内容如下:
- SQL ID: 39dv3d8jkzyuw Plan Hash: 1123238657
- select a.empno, a.ename, b.dname, a.job, a.sal
- from
- emp a, dept b where a.deptno = b.deptno and empno = 7788
- call count cpu elapsed disk query current rows
- ------- ------ -------- ---------- ---------- ---------- ---------- ----------
- Parse 1 0.01 0.02 0 8 0 0
- Execute 1 0.00 0.00 0 0 0 0
- Fetch 2 0.00 0.00 0 7 0 1
- ------- ------ -------- ---------- ---------- ---------- ---------- ----------
- total 4 0.01 0.02 0 15 0 1
- Misses in library cache during parse: 1
- Optimizer mode: ALL_ROWS
- Parsing user id: 91
- Number of plan statistics captured: 1
- Rows (1st) Rows (avg) Rows (max) Row Source Operation
- ---------- ---------- ---------- ---------------------------------------------------
- 1 1 1 HASH JOIN (cr=7 pr=0 pw=0 time=274 us cost=6 size=74 card=1)
- 1 1 1 TABLE ACCESS FULL EMP (cr=3 pr=0 pw=0 time=67 us cost=3 size=52 card=1)
- 4 4 4 TABLE ACCESS FULL DEPT (cr=4 pr=0 pw=0 time=15 us cost=3 size=88 card=4)
- Elapsed times include waiting on following events:
- Event waited on Times Max. Wait Total Waited
- ---------------------------------------- Waited ---------- ------------
- SQL*Net message to client 2 0.00 0.00
- SQL*Net message from client 2 6.55 6.56
- ********************************************************************************
关于阅读这个 tkprof 美化后文件的方法可以参考 MOS 文档:
- TKProf Interpretation (9i and above) (文档 ID 760786.1)
- reference
关于这个 Oracle 之 SQL 优化专题的系列, 缘起是听了公司 SQL 优化专家团队的分享内容, 自己想通过实际操作理解并逐渐测试完善, 在我个人之前的职业生涯规划中一直都偏重于运维方向的 DBA, 对 SQL 优化方面了解的很少, 兴趣也不高, 是 SQL 优化专家团队的这次分享让我觉得 SQL 优化也是很有趣的, 所以下决心从今天起单独开一个专题, 自己也能逐渐系统的学习相关知识并记录下来初步考虑该系列后续内容同时还会参考崔华的基于 Oracle 的 SQL 优化一书最后感谢 SQL 优化专家团队的 leader 勇哥和其团队所有成员, 是你们的这次分享让我有了做这个专题的动力
来源: https://www.cnblogs.com/jyzhao/p/8688464.html