ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > Oracle > 微课sql优化(9)、如何获取执行计划

微课sql优化(9)、如何获取执行计划

原创 Oracle 作者：orastar 时间：2020-03-04 08:51:09 0 删除 编辑

一、获取执行计划方法总结

1. explain plan for获取；
2. set autotrace on ； 
3. statistics_level=all;
4. 通过(dbms_xplan.display_cursor/dbms_xplan.display_awr)输入sql_id参数直接获取
5. awrsqrpt.sql
6. 10046/10053 trace跟踪
7. v$sql_plan_statistics_all

二、获取执行计划示例

  • 1、explain plan for
  1. SQL >   set   line 200
  2. SQL >  explain plan  for
  3.   2  select  c . org_name , sum ( a . amt )   from  ht . c_cons c , ht . a_amt a
  4. where  c . cons_no = a . cons_no
  5. and  a . amt_ym = '201701'   and  c . cons_name = 'wang1706'
  6. group   by  c . org_name ;  3 4 5 

  8. Explained .
  9. SQL >   select   *   from   table ( dbms_xplan . display ) ;
  10. SQL >   /


  13. PLAN_TABLE_OUTPUT
  14. ----------------------------------------------------------------------------------------------------------------
  15. Plan hash  value :  1779151266


  18. -----------------------------------------------------------------------------------------------
  19. |  Id  |  Operation  |  Name  |   Rows   |  Bytes  |  Cost  ( % CPU ) |  Time  |
  20. -----------------------------------------------------------------------------------------------
  21. |  0  |   SELECT  STATEMENT  |   |  1  |  39  |  72  ( 2 ) |  00 : 00 : 01  |
  22. |  1  |  HASH  GROUP   BY   |   |  1  |  39  |  72  ( 2 ) |  00 : 00 : 01  |
  23. | *  2  |  HASH JOIN  |   |  3  |  117  |  71  ( 0 ) |  00 : 00 : 01  |
  24. |  3  |   TABLE   ACCESS   BY   INDEX   ROWID |  C_CONS  |  2  |  44  |  3  ( 0 ) |  00 : 00 : 01  |
  25. | *  4  |   INDEX  RANGE SCAN  |  IDX_CONS_NAME  |  2  |   |  1  ( 0 ) |  00 : 00 : 01  |
  26. | *  5  |   TABLE   ACCESS  FULL  |  A_AMT  |  10364  |  172K |  68  ( 0 ) |  00 : 00 : 01  |


  29. PLAN_TABLE_OUTPUT
  30. -----------------------------------------------------------------------------------------------------------------


  33. Predicate Information  ( identified   by  operation id ) :
  34. ---------------------------------------------------


  37.    2  -   access ( "C" . "CONS_NO" = "A" . "CONS_NO" )
  38.    4  -   access ( "C" . "CONS_NAME" = 'wang1706' )
  39.    5  -  filter ( "A" . "AMT_YM" = '201701' )
  40. 19  rows  selected .
仅使用数据字典评估执行计划，SQL语句未执行。
  • 结果解释
字段     描述
ID    执行计划中每一个操作(行)的标识符。如果数字前面带有*号，意味着将在随后提供这行包含的谓词信息
Operation    执行的操作，也叫做行源操作。
Name    操作的对象
查询优化器评 估
Rows(E-Rows)    评估中操作返回的记录条数
Bytes(E-Bytes)    评估中操作返回的记录字节数
TempSpc    评估中操作使用的临时空间大小
Cost(%CPU)    评估中操作的开销。在括号中列出了CPU开销的百分比。注意这些值是通过执行计划 计算出来的。即，你操作的开销包含子操作的开销
Time    评估中执行操作需要的时间(HH:MM:SS)
分 区
Pstart    访问的第一个分区
Pstop    访问的最后一个分区
并行和分布式处 理
Inst    在分布式操作中，指操作使用的数据库链名称
TQ    在并行操作中，用于属线程间通信的表队列
IN-OUT    并行或分布式操作间的关系
PQ Distrib    在并行操作中，生产者为发送数据给消费者进行的分配
运行进统 计
Starts    指定操作执行的次数
A-Rows    操作返回的真实记录数
A-Time    操作执行的真实时间
I/O统 计
Buffers    执行期间进行的逻辑读操作数量
Reads    执行期间进行的物理读操作数量
Writes    执行期间进行的物理写操作数量
内存使用统 计
0Mem    最优执行所需内存的评估值
1Mem    一次通过(one-pass：使用最少的磁盘操作，大部分操作在内存中执行)执行所需内存的评估值
0/1/M    最优/一次通过/多次通过(multipass: 工作区域设置太小，存在大量磁盘操作)模式操作执行次数
Used-Mem    最后一次执行时操作使用的内存量
Used-Tmp    最后一次执行时操作使用的临时空间大小。
Max-Tmp    操作使用的最大临时空间大小
  • 2. set autotrace on
  1. select   c . org_name , sum ( a . amt )   from   ht . c_cons c , ht . a_amt a
  2. where  c . cons_no = a . cons_no
  3. and  a . amt_ym = '201701'   and  c . cons_name = 'wang1706'
  4. group   by  c . org_name ;
  5. SQL >   set  autotrace  on
  6. SQL >   select  c . org_name , sum ( a . amt )   from  ht . c_cons c , ht . a_amt a
  7. where  c . cons_no = a . cons_no
  8. and  a . amt_ym = '201701'   and  c . cons_name = 'wang1706'
  9. group   by  c . org_name ;  2 3 4 

  11. ORG_NAME
  12. ------------------------------------------------------
  13. guangdong
  14.       9986

  16. Execution Plan
  17. ----------------------------------------------------------
  18. Plan hash  value :  1779151266


  21. -----------------------------------------------------------------------------------------------
  22. |  Id  |  Operation  |  Name  |   Rows   |  Bytes  |  Cost  ( % CPU ) |  Time  |
  23. -----------------------------------------------------------------------------------------------
  24. |  0  |   SELECT  STATEMENT  |   |  1  |  39  |  72  ( 2 ) |  00 : 00 : 01  |
  25. |  1  |  HASH  GROUP   BY   |   |  1  |  39  |  72  ( 2 ) |  00 : 00 : 01  |
  26. | *  2  |  HASH JOIN  |   |  3  |  117  |  71  ( 0 ) |  00 : 00 : 01  |
  27. |  3  |   TABLE   ACCESS   BY   INDEX   ROWID |  C_CONS  |  2  |  44  |  3  ( 0 ) |  00 : 00 : 01  |
  28. | *  4  |   INDEX  RANGE SCAN  |  IDX_CONS_NAME  |  2  |   |  1  ( 0 ) |  00 : 00 : 01  |
  29. | *  5  |   TABLE   ACCESS  FULL  |  A_AMT  |  10364  |  172K |  68  ( 0 ) |  00 : 00 : 01  |
  30. -----------------------------------------------------------------------------------------------


  33. Predicate Information  ( identified   by  operation id ) :
  34. ---------------------------------------------------


  37.    2  -   access ( "C" . "CONS_NO" = "A" . "CONS_NO" )
  38.    4  -   access ( "C" . "CONS_NAME" = 'wang1706' )
  39.    5  -  filter ( "A" . "AMT_YM" = '201701' )




  44. Statistics
  45. ----------------------------------------------------------
  46.    1 recursive calls
  47.    0 db block gets
  48.  250 consistent gets
  49.    0 physical reads
  50.    0 redo  size
  51.  609 bytes sent via SQL * Net  to  client
  52.  523 bytes received via SQL * Net  from  client
  53.    2 SQL * Net roundtrips  to / from  client
  54.    0 sorts  ( memory )
  55.    0 sorts  ( disk )
  56.    1  rows  processed
语法解释：
SQL> set autot
用法: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
方括号内的字母都可以省略。
set autot on ----执行SQL 并且显示执行计划和统计信息
set autot trace ----执行SQL 但不显示运行结果，显示执行计划和统计信息
set autot trace exp ----如果SELECT 就不执行SQL(dml 执行)，只显示执行计划
set autot trace stat ----执行SQL，只显示统计信息
结果解释
1、DB Block Gets（当前请求的块数目）
2、Consistent Gets（数据请求总数在回滚段Buffer中的数据一致性读所需要的数据块）
3、physical reads 物理读——执行SQL的过程中，从硬盘上读取的数据块个数
4、redo size      重做数——执行SQL的过程中，产生的重做日志的大小
5、bytes set via sql*net to client  通过sql*net发送给客户端的字节数
6、bytes received via sql*net from client  通过sql*net接受客户端的字节数
7、sorts(memory)  在内存中发生的排序
8、sorts(disk)    不能在内存中发生的排序，需要硬盘来协助
9、rows processed 结果的记录数 
  • 3. statistics_level=all
  1. set   line 200
  2. set  heading  off
  3. alter   session   set  statistics_level = all ;
  4. select  c . org_name , sum ( a . amt )   from  ht . c_cons c , ht . a_amt a
  5. where  c . cons_no = a . cons_no
  6. and  a . amt_ym = '201701'   and  c . cons_name = 'wang1706'
  7. group   by  c . org_name ;
  8. select   *   from   table ( DBMS_XPLAN . DISPLAY_CURSOR ( null , null , 'allstats last' ) ) ;
  9. Plan hash  value :  1779151266


  12. ------------------------------------------------------------------------------------------------------------------------------------
  13. |  Id  |  Operation  ? ? ? ? ? ? ? ? ? ? ? |  Name  |  Starts  |  E - Rows   |  A - Rows   |  A - Time  |  Buffers  |  OMem  |  1Mem  |  Used - Mem  |
  14. ------------------------------------------------------------------------------------------------------------------------------------
  15. |  0  |   SELECT  STATEMENT  ? ? ? ? ? ? |   |  1  |   |  1  | 00 : 00 : 00 . 01  |  250  |   |   |   |
  16. |  1  |  HASH  GROUP   BY   ? ? ? ? ? ? ? |   |  1  |  1  |  1  | 00 : 00 : 00 . 01  |  250  |  1126K |  1126K |  499K  ( 0 ) |
  17. | *  2  |  HASH JOIN  ? ? ? ? ? ? ? ? ? |   |  1  |  3  |  1  | 00 : 00 : 00 . 01  |  250  |  1483K |  1483K |  679K  ( 0 ) |
  18. |  3  |   TABLE   ACCESS   BY   INDEX   ROWID ? |  C_CONS  |  1  |  2  |  1  | 00 : 00 : 00 . 01  |  3  |   |   |   |
  19. | *  4  |   INDEX  RANGE SCAN  ? ? ? ? |  IDX_CONS_NAME  |  1  |  2  |  1  | 00 : 00 : 00 . 01  |  2  |   |   |   |
  20. | *  5  |   TABLE   ACCESS  FULL  ? ? ? ? |  A_AMT  |  1  |  10364  |  9998  | 00 : 00 : 00 . 01  |  247  |   |   |   |
  21. ------------------------------------------------------------------------------------------------------------------------------------


  24. Predicate Information  ( identified   by  operation id ) :
  25. ---------------------------------------------------


  28.    2  -   access ( "C" . "CONS_NO" = "A" . "CONS_NO" )
  29.    4  -   access ( "C" . "CONS_NAME" = 'wang1706' )
  30.    5  -  filter ( "A" . "AMT_YM" = '201701' )
结果解释：
Starts表示这个操作执行了几次
E-Rows表示优化器估算的行数
A-Rows 表示实际的行数
A-Time 表示这个操作执行的时间(累加的)
Buffers 表示逻辑度(累加的)
  • 4. 通过(dbms_xplan.display_cursor/dbms_xplan.display_awr)输入sql_id参数直接获取
select * from table(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id'));
select * from table(DBMS_XPLAN.DISPLAY_AWR('&sql_id'));
  • 5、awrsqrpt.sql
SQL> @?/rdbms/admin/awrsqrpt.sql
Enter value for begin_snap: 110
Begin Snapshot Id specified: 110
Enter value for end_snap: 112
End   Snapshot Id specified: 112
Specify the SQL Id
~~~~~~~~~~~~~~~~~~
Enter value for sql_id: 
  • 6. 10046/10053 trace跟踪
  1. oradebug setmypid
  2. oradebug unlimit
  3. select  c . org_name , sum ( a . amt )   from  ht . c_cons c , ht . a_amt a
  4. where  c . cons_no = a . cons_no
  5. and  a . amt_ym = '201701'   and  c . cons_name = 'wang1706'
  6. group   by  c . org_name ;
  7. oradebug session_event 10046 trace name context forever  , level  12
  8. oradebug event 10046 trace name context  off
  9. oradebug tracefile_name
  10. [ oracle @ sndb  ~ ] $  tkprof  / u01 / app / oracle / diag / rdbms / sndb1 / sndb1 / trace / sndb1_ora_29017 . trc  / home / oracle / sndb1_ora_29017 . trc
  11. call  count  cpu elapsed disk query  current   rows
  12. ------- ------ -------- ---------- ---------- ---------- ---------- ----------
  13. Parse 1 0 . 00 0 . 00 0 0 0 0
  14. Execute  1 0 . 00 0 . 00 0 0 0 0
  15. Fetch 2 0 . 02 0 . 02 0 250 0 1
  16. ------- ------ -------- ---------- ---------- ---------- ---------- ----------
  17. total 4 0 . 02 0 . 02 0 250 0 1


  20. Misses  in  library cache during parse :  0
  21. Optimizer  mode :  ALL_ROWS
  22. Parsing  user  id :  SYS
  23. Number   of  plan statistics captured :  1


  26. Rows   ( 1st )   Rows   ( avg )   Rows   ( max )   Row  Source Operation
  27. ---------- ---------- ---------- ---------------------------------------------------
  28.          1 1 1 HASH  GROUP   BY   ( cr = 250 pr = 0 pw = 0 time = 24310 us cost = 72  size = 39 card = 1 )
  29.          1 1 1 HASH JOIN  ( cr = 250 pr = 0 pw = 0 time = 23827 us cost = 71  size = 117 card = 3 )
  30.          1 1 1  TABLE   ACCESS   BY   INDEX   ROWID  C_CONS  ( cr = 3 pr = 0 pw = 0 time = 113 us cost = 3  size = 44 card = 2 )
  31.          1 1 1  INDEX  RANGE SCAN IDX_CONS_NAME  ( cr = 2 pr = 0 pw = 0 time = 91 us cost = 1  size = 0 card = 2 ) ( object  id 87459 )
  32.       9998 9998 9998  TABLE   ACCESS  FULL A_AMT  ( cr = 247 pr = 0 pw = 0 time = 11812 us cost = 68  size = 176188 card = 10364 )




  37. Elapsed times include waiting  on  following events :
  38.   Event waited  on  Times  Max .  Wait Total Waited
  39.    ---------------------------------------- Waited ---------- ------------
  40.   SQL * Net message  to  client 2 0 . 00 0 . 00
  41.   SQL * Net message  from  client 2 0 . 00 0 . 00
  • 7. v$sql_plan_statistics_all
  1. select   '| Operation |Object Name | Rows | Bytes| Cost |'
  2. as   "Explain Plan in library cache:"   from  dual
  3. union   all
  4. select   rpad ( '| ' | | substr ( lpad ( ' ' , 1 * ( depth - 1 ) ) | | operation | |
  5.         decode ( options ,   null , '' , ' ' | | options ) ,  1 ,  35 ) ,  36 ,   ' ' ) | | '|' | |
  6.         rpad ( decode ( id ,  0 ,   '----------------------------' ,
  7.         substr ( decode ( substr ( object_name ,  1 ,  7 ) ,   'SYS_LE_' ,   null ,  object_name )
  8.         | | ' ' , 1 ,  30 ) ) ,  31 ,   ' ' ) | | '|' | |   lpad ( decode ( cardinality , null , ' ' ,
  9.         decode ( sign ( cardinality - 1000 ) ,   - 1 ,  cardinality | | ' ' ,
  10.         decode ( sign ( cardinality - 1000000 ) ,   - 1 ,   trunc ( cardinality / 1000 ) | | 'K' ,
  11.         decode ( sign ( cardinality - 1000000000 ) ,   - 1 ,   trunc ( cardinality / 1000000 ) | | 'M' ,
  12.         trunc ( cardinality / 1000000000 ) | | 'G' ) ) ) ) ,  7 ,   ' ' )   | |   '|'   | |
  13.         lpad ( decode ( bytes , null , ' ' ,
  14.         decode ( sign ( bytes - 1024 ) ,   - 1 ,  bytes | | ' ' ,
  15.         decode ( sign ( bytes - 1048576 ) ,   - 1 ,   trunc ( bytes / 1024 ) | | 'K' ,
  16.         decode ( sign ( bytes - 1073741824 ) ,   - 1 ,   trunc ( bytes / 1048576 ) | | 'M' ,
  17.         trunc ( bytes / 1073741824 ) | | 'G' ) ) ) ) ,  6 ,   ' ' )   | |   '|'   | |
  18.         lpad ( decode ( cost , null , ' ' ,   decode ( sign ( cost - 10000000 ) ,   - 1 ,  cost | | ' ' ,
  19.         decode ( sign ( cost - 1000000000 ) ,   - 1 ,   trunc ( cost / 1000000 ) | | 'M' ,
  20.         trunc ( cost / 1000000000 ) | | 'G' ) ) ) ,  8 ,   ' ' )   | |   '|'   as   "Explain plan"
  21.    from  v$sql_plan_statistics_all sp
  22.   where  sp . hash_value = & hash_value ;

3、适用场景

1、sql执行时间较短：statistics_level=all;
2、sql执行时间较长：explain plan for获取
3、获取实际运行的执行计划：通过(dbms_xplan.display_cursor/dbms_xplan.display_awr)输入sql_id参数直接获取
4、获取一段时间内实际运行情况：awrsqrpt.sql


来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/31442014/viewspace-2678260/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： 微课sql优化(8)、统计信息收集(6)-统计信息查询
下一篇： 微课sql优化(10)、关于数据访问方法
请登录后发表评论 登录
全部评论
orastar
专家姓名：惠星星
作者简介：惠星星，现就职于云和恩墨，持有OCP 10g、OCP 11g、OCM 11g证书，并有长达8年电力行业业务维护、数据库维护服务经验，擅长Oracle数据库性能优化、故障处理及Oracle internal研究。

注册时间：2017-03-08

  • 博文量
    171
  • 访问量
    429763

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593