HelloDBA [English]
搜索Internet 搜索 HelloDBABA
  Oracle技术站。email: fuyuncat@gmail.com  MSN: fuyuncat@hotmail.com   acoug  acoug 

一条语句帮你分析SQL的性能

[English]

作者: fuyuncat

来源: www.HelloDBA.com

日期: 2016-03-12 22:16:32

分享到  新浪微博 腾讯微博 人人网 i贴吧 开心网 豆瓣 淘宝 推特 Facebook GMail Blogger Orkut Google Bookmarks

 这是我用来分析SQL语句性能的一个脚本。它主要内容就是一条语句。它能显示SQL语句用于性能分析的以下信息。而显示的细节程度可以由控制项控制。

  • 监控到或捕捉到的绑定变量
  • SQL语句文本
  • 执行计划
  • 解析执行计划时窥视到的变量
  • 执行计划的纲要数据
  • 执行计划中的负载
  • 等待事件
  • 性能统计数据

示例:

SQL代码
  1. HelloDBA.COM> @showplan 8z91j441gu9n1  
  2. Usage: @showplan <SQL_ID> [Plan Hash Value] [Details: [+](B)inds|SQL (T)ext|(Pee(K)ed Binds|(P)lan|(O)utlines|Pre(D)icate|Plan (L)oading|(W)ait events|(S)tatistics]  
  3. Description: Show SQL Plan  
  4.   
  5.   
  6. SQL ID: 8z91j441gu9n1  
  7.   
  8. ------------- Last Monitored Binds --------------  
  9.   
  10. --SID: 258,16699  
  11. var ACCEPTDIS VARCHAR2(32)  
  12. var BENAME VARCHAR2(32)  
  13. var EENAME VARCHAR2(32)  
  14. var IMPLTYP VARCHAR2(32)  
  15. var PROFILETYP VARCHAR2(32)  
  16. var SQLTYP NUMBER  
  17. var TID NUMBER  
  18.   
  19. --SID: 258,16699  
  20. exec :ACCEPTDIS:='ACCEPTDISABLED';  
  21. exec :BENAME:='EXEC_42756';  
  22. exec :EENAME:='EXEC_43638';  
  23. exec :IMPLTYP:='IMPLEMENTATION';  
  24. exec :PROFILETYP:='SQL PROFILE';  
  25. exec :SQLTYP:=7  
  26. exec :TID:=29631  
  27.   
  28. --SID: 1790,51175  
  29. var ACCEPTDIS VARCHAR2(32)  
  30. var BENAME VARCHAR2(32)  
  31. var EENAME VARCHAR2(32)  
  32. var IMPLTYP VARCHAR2(32)  
  33. var PROFILETYP VARCHAR2(32)  
  34. var SQLTYP NUMBER  
  35. var TID NUMBER  
  36.   
  37. --SID: 1790,51175  
  38. exec :ACCEPTDIS:='ACCEPTDISABLED';  
  39. exec :BENAME:='EXEC_42756';  
  40. exec :EENAME:='EXEC_43638';  
  41. exec :IMPLTYP:='IMPLEMENTATION';  
  42. exec :PROFILETYP:='SQL PROFILE';  
  43. exec :SQLTYP:=7  
  44. exec :TID:=29631  
  45.   
  46.   
  47. ------------- SQL Text --------------  
  48.   
  49. SELECT /*+ leading(oe f r l) */  
  50.               /* STN_REPT_TOP_PROF */  
  51.               xmlelement(  
  52.                 "top_profiles",  
  53.                  xmlagg(xmlelement("obj_id", object_id)))  
  54.        FROM    (SELECT task_id, exec_name, exec_id, exec_start, object_id, sql_id,  
  55.                   parsing_schema, phv, obj_attr8  
  56.            FROM   (SELECT /*+ cardinality(o 5999) leading(e o) */  
  57.                           e.task_id task_id,  
  58.                           e.execution_name exec_name,  
  59.                           e.execution_id exec_id,  
  60.                           e.execution_start exec_start,  
  61.                           o.id object_id,  
  62.                           o.attr1 sql_id,  
  63.                           o.attr3 parsing_schema,  
  64.                           to_number(nvl(o.attr5, '0'))  phv,  
  65.                           nvl(o.attr8,0) obj_attr8,  
  66.                           row_number() over  
  67.                             (partition by o.attr1  
  68.                              order by     bitand(o.attr7, 32) asc,  
  69.                                           e.execution_start desc)  
  70.                             rn  
  71.                    FROM    (SELECT e.task_id, e.name execution_name, e.id execution_id,  
  72.                 e.exec_start execution_start, e.status status#  
  73.          FROM (SELECT  task_id,  
  74.                        min(execution_name) keep (dense_rank first order by  
  75.                          execution_start) bename,  
  76.                        max(execution_name) keep (dense_rank last order by  
  77.                          execution_start) eename,  
  78.                        min(execution_start) bestart,  
  79.                        max(execution_start) eestart  
  80.                FROM    (SELECT task_id, name execution_name,  
  81.                                exec_start execution_start  
  82.                         FROM   wri$_adv_executions  
  83.                         WHERE  task_id = :tid and  
  84.                                name in (:bename, :eename))  
  85.                GROUP BY task_id) r,  
  86.                wri$_adv_executions e  
  87.   
  88.   
  89.          WHERE e.task_id = r.task_id and  
  90.                e.exec_start >= bestart and  
  91.                e.exec_start <= eestart and  
  92.                e.status IN (3, 4) and  
  93.                (bename <> eename OR e.name = bename)) e  /* e */,  
  94.                           wri$_adv_objects o  
  95.                    WHERE  o.task_id = e.task_id AND  
  96.                           o.exec_name = e.execution_name AND  
  97.                           o.type = :sqltyp)  
  98.             WHERE rn = 1) oe  /* oe */,  
  99.               wri$_adv_findings f,  
  100.               wri$_adv_recommendations r,  
  101.               wri$_adv_rationale l  
  102.        WHERE  oe.task_id = f.task_id AND oe.exec_name = f.exec_name AND  
  103.               oe.object_id = f.obj_id AND f.task_id = r.task_id AND  
  104.               f.exec_name = r.exec_name AND f.id = r.finding_id AND  
  105.               l.task_id = r.task_id AND l.exec_name = r.exec_name AND  
  106.               l.rec_id = r.id AND  
  107.               r.type = :profiletyp AND l.type = :impltyp AND  
  108.               l.attr1 = :acceptdis AND  
  109.               NOT EXISTS (SELECT 1  
  110.                           FROM   dba_sql_profiles p  
  111.                           WHERE  p.task_id = r.task_id AND  
  112.                                  p.task_exec_name = r.exec_name AND  
  113.                                  p.task_obj_id = oe.object_id AND  
  114.                                  p.task_fnd_id = r.finding_id AND  
  115.                                  p.task_rec_id = r.id)  
  116.   
  117.   
  118. ------------- SQL Plan (Plan Hash Value:589376886) --------------  
  119.   
  120.      0   ( )SELECT STATEMENT Optimizer=ALL_ROWS  
  121.      1   (0) SORT (AGGREGATE)  
  122.      2   (1)  NESTED LOOPS (ANTI) (Cost=30 Card=11 rows Bytes=0/129)  
  123.      3   (2)   NESTED LOOPS (Cost=26 Card=11 rows Bytes=0/129)  
  124.      4   (3)    NESTED LOOPS (Cost=11 Card=159 rows Bytes=0/93)  
  125.      5   (4)     NESTED LOOPS (Cost=9 Card=608 rows Bytes=0/54)  
  126.     #6   (5)      VIEW (Cost=8 Card=268 rows Bytes=0/31)  
  127.     #7   (6)       WINDOW (SORT PUSHED RANK) (Cost=8 Card=489 rows Bytes=0/123)  
  128.      8   (7)        NESTED LOOPS  
  129.      9   (8)         NESTED LOOPS (Cost=7 Card=1667 rows Bytes=0/123)  
  130.     10   (9)          NESTED LOOPS (Cost=5 Card=33 rows Bytes=0/84)  
  131.     11  (10)           VIEW (Cost=3 Card=1 rows Bytes=0/57)  
  132.     12  (11)            SORT (GROUP BY) (Cost=3 Card=1 rows Bytes=0/24)  
  133.     13  (12)             TABLE ACCESS (BY INDEX ROWID) OF 'WRI$_ADV_EXECUTIONS' (TABLE) (Cost=3 Card=2 rows Bytes=0/24)  
  134.   *#14  (13)              INDEX (RANGE SCAN) OF 'WRI$_ADV_EXECS_PK' (INDEX (UNIQUE)) (Cost=2 Card=2 rows Bytes=0/)  
  135.    #15  (10)           TABLE ACCESS (BY INDEX ROWID) OF 'WRI$_ADV_EXECUTIONS' (TABLE) (Cost=2 Card=33 rows Bytes=0/27)  
  136.    *16  (15)            INDEX (RANGE SCAN) OF 'WRI$_ADV_EXECS_IDX_03' (INDEX) (Cost=1 Card=33 rows Bytes=0/)  
  137.    *17   (9)          INDEX (RANGE SCAN) OF 'WRI$_ADV_OBJECTS_IDX_01' (INDEX (UNIQUE)) (Cost=1 Card=1667 rows Bytes=0/)  
  138.    #18   (8)         TABLE ACCESS (BY INDEX ROWID) OF 'WRI$_ADV_OBJECTS' (TABLE) (Cost=2 Card=1634 rows Bytes=0/273)  
  139.    *19   (5)      INDEX (RANGE SCAN) OF 'WRI$_ADV_FINDINGS_IDX_02' (INDEX (UNIQUE)) (Cost=1 Card=608 rows Bytes=0/23)  
  140.    #20   (4)     TABLE ACCESS (BY INDEX ROWID) OF 'WRI$_ADV_RECOMMENDATIONS' (TABLE) (Cost=2 Card=159 rows Bytes=0/39)  
  141.    *21  (20)      INDEX (RANGE SCAN) OF 'WRI$_ADV_RECS_IDX_02' (INDEX (UNIQUE)) (Cost=1 Card=345 rows Bytes=0/)  
  142.    #22   (3)    TABLE ACCESS (BY INDEX ROWID) OF 'WRI$_ADV_RATIONALE' (TABLE) (Cost=15 Card=11 rows Bytes=0/36)  
  143.    *23  (22)     INDEX (RANGE SCAN) OF 'WRI$_ADV_RATIONALE_PK' (INDEX (UNIQUE)) (Cost=3 Card=27293304 rows Bytes=0/)  
  144.     24   (2)   VIEW PUSHED PREDICATE OF 'VW_SQ_1' (VIEW) (Cost=4 Card=0 rows Bytes=0/)  
  145.     25  (24)    NESTED LOOPS (Cost=4 Card=0 rows Bytes=0/240)  
  146.     26  (25)     NESTED LOOPS (Cost=4 Card=0 rows Bytes=0/204)  
  147.    *27  (26)      HASH JOIN (Cost=4 Card=0 rows Bytes=0/168)  
  148.    #28  (27)       TABLE ACCESS (BY INDEX ROWID) OF 'SQLOBJ$AUXDATA' (TABLE) (Cost=2 Card=0 rows Bytes=0/462)  
  149.    *29  (28)        INDEX (RANGE SCAN) OF 'I_SQLOBJ$AUXDATA_TASK' (INDEX) (Cost=1 Card=0 rows Bytes=0/)  
  150.   *#30  (27)       INDEX (SKIP SCAN) OF 'SQLOBJ$_PKEY' (INDEX (UNIQUE)) (Cost=1 Card=0 rows Bytes=0/322)  
  151.    *31  (26)      INDEX (UNIQUE SCAN) OF 'I_SQL$TEXT_PKEY' (INDEX (UNIQUE))  
  152.    *32  (25)     INDEX (UNIQUE SCAN) OF 'I_SQL$_PKEY' (INDEX (UNIQUE))  
  153.   
  154. ------------- Predicate Information (Plan Hash Value:589376886) --------------  
  155.   
  156.   6 Filter: "RN"=1  
  157.   7 Filter: ROW_NUMBER() OVER ( PARTITION BY "O"."ATTR1" ORDER BY BITAND("O"."ATTR7",32),INTERNAL_FUNCTION("E"."EXEC_START"DESC )<=1  
  158.  14 Access: "TASK_ID"=:TID  
  159.  14 Filter: ("NAME"=:BENAME OR "NAME"=:EENAME)  
  160.  15 Filter: (INTERNAL_FUNCTION("E"."STATUS"AND ("BENAME"<>"EENAME" OR "E"."NAME"="BENAME"))  
  161.  16 Access: "E"."TASK_ID"="R"."TASK_ID" AND "E"."EXEC_START">="BESTART" AND "E"."EXEC_START"<="EESTART"  
  162.  17 Access: "O"."TASK_ID"="E"."TASK_ID" AND "O"."EXEC_NAME"="E"."NAME"  
  163.  18 Filter: "O"."TYPE"=:SQLTYP  
  164.  19 Access: "TASK_ID"="F"."TASK_ID" AND "EXEC_NAME"="F"."EXEC_NAME" AND "OBJECT_ID"="F"."OBJ_ID"  
  165.  20 Filter: "R"."TYPE"=:PROFILETYP  
  166.  21 Access: "F"."TASK_ID"="R"."TASK_ID" AND "F"."EXEC_NAME"="R"."EXEC_NAME" AND "F"."ID"="R"."FINDING_ID"  
  167.  22 Filter: ("L"."TYPE"=:IMPLTYP AND "L"."ATTR1"=:ACCEPTDIS AND "L"."EXEC_NAME"="R"."EXEC_NAME" AND "L"."REC_ID"="R"."ID")  
  168.  23 Access: "L"."TASK_ID"="R"."TASK_ID"  
  169.  27 Access: "SO"."SIGNATURE"="AD"."SIGNATURE" AND "SO"."CATEGORY"="AD"."CATEGORY"  
  170.  28 Filter: "AD"."OBJ_TYPE"=1  
  171.  29 Access: "AD"."TASK_ID"="R"."TASK_ID" AND "AD"."TASK_EXEC_NAME"="R"."EXEC_NAME" AND "AD"."TASK_OBJ_ID"="OBJECT_ID" AND "AD"."TASK_FND_ID"="R"."FINDING_ID" AND "AD"."TASK_REC_ID"="R"."ID"  
  172.  30 Access: "SO"."OBJ_TYPE"=1  
  173.  30 Filter: "SO"."OBJ_TYPE"=1  
  174.  31 Access: "SO"."SIGNATURE"="ST"."SIGNATURE"  
  175.  32 Access: "SO"."SIGNATURE"="SQ"."SIGNATURE"  
  176.   
  177. ------------- Plan Loading (Plan Hash Value:589376886) --------------  
  178.   
  179.  22: TABLE ACCESS BY INDEX ROWID                       #############################################(89.47%)  
  180.  23: INDEX RANGE SCAN                                  #####(10.53%)  
  181.   
  182. ------------- Waits Events (Plan Hash Value:589376886) --------------  
  183.   
  184. ON CPU on SYS.WRI$_ADV_RECS_IDX_02(INDEX)                                  #####(9.65%)  
  185.   
  186. ------------- Statistics Data --------------  
  187.   
  188. Loads: 1  
  189. Load Versions: 1  
  190. First Load Time: 2014-03-12/22:46:24  
  191. Last Load Time: 2014-03-12/22:46:24  
  192. User Openings: 0  
  193. Parse Calls: 11  
  194. Executions: 11  
  195. Sorts(Average): 2  
  196. Fetches(Average): 1  
  197. Disk Reads(Average): .545  
  198. Buffer Gets(Average): 1416843.364  
  199. Elapsed Time(Average): 10.507 seconds  
  200. CPU Time(Average): 10.501 seconds  
  201. Run Time Memory(Average): .005M  
  202. PGA Size(Maximum): .015G  
  203. Temp Space(Maximum): 0G  

注意:这个版本是运行在11gR2上的。你如果要在其它版本运行,需要将含有哪些不存在的视图或字段的部分移除,如v$sql_monitor

点击此链接下载最新版本:http://www.HelloDBA.com/Download/showplan.zip

--- Fuyuncat ---

Top

Copyright ©2005,HelloDBA.Com 保留一切权利

申明
by fuyuncat