[English]
作者:
fuyuncat
来源:
www.HelloDBA.com
这是我用来分析SQL语句性能的一个脚本。它主要内容就是一条语句。它能显示SQL语句用于性能分析的以下信息。而显示的细节程度可以由控制项控制。
- 监控到或捕捉到的绑定变量
- SQL语句文本
- 执行计划
- 解析执行计划时窥视到的变量
- 执行计划的纲要数据
- 执行计划中的负载
- 等待事件
- 性能统计数据
示例:
SQL代码
- HelloDBA.COM> @showplan 8z91j441gu9n1
- 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]
- Description: Show SQL Plan
- SQL ID: 8z91j441gu9n1
- ------------- Last Monitored Binds --------------
- --SID: 258,16699
- var ACCEPTDIS VARCHAR2(32)
- var BENAME VARCHAR2(32)
- var EENAME VARCHAR2(32)
- var IMPLTYP VARCHAR2(32)
- var PROFILETYP VARCHAR2(32)
- var SQLTYP NUMBER
- var TID NUMBER
- --SID: 258,16699
- exec :ACCEPTDIS:='ACCEPTDISABLED';
- exec :BENAME:='EXEC_42756';
- exec :EENAME:='EXEC_43638';
- exec :IMPLTYP:='IMPLEMENTATION';
- exec :PROFILETYP:='SQL PROFILE';
- exec :SQLTYP:=7
- exec :TID:=29631
- --SID: 1790,51175
- var ACCEPTDIS VARCHAR2(32)
- var BENAME VARCHAR2(32)
- var EENAME VARCHAR2(32)
- var IMPLTYP VARCHAR2(32)
- var PROFILETYP VARCHAR2(32)
- var SQLTYP NUMBER
- var TID NUMBER
- --SID: 1790,51175
- exec :ACCEPTDIS:='ACCEPTDISABLED';
- exec :BENAME:='EXEC_42756';
- exec :EENAME:='EXEC_43638';
- exec :IMPLTYP:='IMPLEMENTATION';
- exec :PROFILETYP:='SQL PROFILE';
- exec :SQLTYP:=7
- exec :TID:=29631
- ------------- SQL Text --------------
- SELECT /*+ leading(oe f r l) */
- /* STN_REPT_TOP_PROF */
- xmlelement(
- "top_profiles",
- xmlagg(xmlelement("obj_id", object_id)))
- FROM (SELECT task_id, exec_name, exec_id, exec_start, object_id, sql_id,
- parsing_schema, phv, obj_attr8
- FROM (SELECT /*+ cardinality(o 5999) leading(e o) */
- e.task_id task_id,
- e.execution_name exec_name,
- e.execution_id exec_id,
- e.execution_start exec_start,
- o.id object_id,
- o.attr1 sql_id,
- o.attr3 parsing_schema,
- to_number(nvl(o.attr5, '0')) phv,
- nvl(o.attr8,0) obj_attr8,
- row_number() over
- (partition by o.attr1
- order by bitand(o.attr7, 32) asc,
- e.execution_start desc)
- rn
- FROM (SELECT e.task_id, e.name execution_name, e.id execution_id,
- e.exec_start execution_start, e.status status#
- FROM (SELECT task_id,
- min(execution_name) keep (dense_rank first order by
- execution_start) bename,
- max(execution_name) keep (dense_rank last order by
- execution_start) eename,
- min(execution_start) bestart,
- max(execution_start) eestart
- FROM (SELECT task_id, name execution_name,
- exec_start execution_start
- FROM wri$_adv_executions
- WHERE task_id = :tid and
- name in (:bename, :eename))
- GROUP BY task_id) r,
- wri$_adv_executions e
- WHERE e.task_id = r.task_id and
- e.exec_start >= bestart and
- e.exec_start <= eestart and
- e.status IN (3, 4) and
- (bename <> eename OR e.name = bename)) e /* e */,
- wri$_adv_objects o
- WHERE o.task_id = e.task_id AND
- o.exec_name = e.execution_name AND
- o.type = :sqltyp)
- WHERE rn = 1) oe /* oe */,
- wri$_adv_findings f,
- wri$_adv_recommendations r,
- wri$_adv_rationale l
- WHERE oe.task_id = f.task_id AND oe.exec_name = f.exec_name AND
- oe.object_id = f.obj_id AND f.task_id = r.task_id AND
- f.exec_name = r.exec_name AND f.id = r.finding_id AND
- l.task_id = r.task_id AND l.exec_name = r.exec_name AND
- l.rec_id = r.id AND
- r.type = :profiletyp AND l.type = :impltyp AND
- l.attr1 = :acceptdis AND
- NOT EXISTS (SELECT 1
- FROM dba_sql_profiles p
- WHERE p.task_id = r.task_id AND
- p.task_exec_name = r.exec_name AND
- p.task_obj_id = oe.object_id AND
- p.task_fnd_id = r.finding_id AND
- p.task_rec_id = r.id)
- ------------- SQL Plan (Plan Hash Value:589376886) --------------
- 0 ( )SELECT STATEMENT Optimizer=ALL_ROWS
- 1 (0) SORT (AGGREGATE)
- 2 (1) NESTED LOOPS (ANTI) (Cost=30 Card=11 rows Bytes=0/129)
- 3 (2) NESTED LOOPS (Cost=26 Card=11 rows Bytes=0/129)
- 4 (3) NESTED LOOPS (Cost=11 Card=159 rows Bytes=0/93)
- 5 (4) NESTED LOOPS (Cost=9 Card=608 rows Bytes=0/54)
- #6 (5) VIEW (Cost=8 Card=268 rows Bytes=0/31)
- #7 (6) WINDOW (SORT PUSHED RANK) (Cost=8 Card=489 rows Bytes=0/123)
- 8 (7) NESTED LOOPS
- 9 (8) NESTED LOOPS (Cost=7 Card=1667 rows Bytes=0/123)
- 10 (9) NESTED LOOPS (Cost=5 Card=33 rows Bytes=0/84)
- 11 (10) VIEW (Cost=3 Card=1 rows Bytes=0/57)
- 12 (11) SORT (GROUP BY) (Cost=3 Card=1 rows Bytes=0/24)
- 13 (12) TABLE ACCESS (BY INDEX ROWID) OF 'WRI$_ADV_EXECUTIONS' (TABLE) (Cost=3 Card=2 rows Bytes=0/24)
- *#14 (13) INDEX (RANGE SCAN) OF 'WRI$_ADV_EXECS_PK' (INDEX (UNIQUE)) (Cost=2 Card=2 rows Bytes=0/)
- #15 (10) TABLE ACCESS (BY INDEX ROWID) OF 'WRI$_ADV_EXECUTIONS' (TABLE) (Cost=2 Card=33 rows Bytes=0/27)
- *16 (15) INDEX (RANGE SCAN) OF 'WRI$_ADV_EXECS_IDX_03' (INDEX) (Cost=1 Card=33 rows Bytes=0/)
- *17 (9) INDEX (RANGE SCAN) OF 'WRI$_ADV_OBJECTS_IDX_01' (INDEX (UNIQUE)) (Cost=1 Card=1667 rows Bytes=0/)
- #18 (8) TABLE ACCESS (BY INDEX ROWID) OF 'WRI$_ADV_OBJECTS' (TABLE) (Cost=2 Card=1634 rows Bytes=0/273)
- *19 (5) INDEX (RANGE SCAN) OF 'WRI$_ADV_FINDINGS_IDX_02' (INDEX (UNIQUE)) (Cost=1 Card=608 rows Bytes=0/23)
- #20 (4) TABLE ACCESS (BY INDEX ROWID) OF 'WRI$_ADV_RECOMMENDATIONS' (TABLE) (Cost=2 Card=159 rows Bytes=0/39)
- *21 (20) INDEX (RANGE SCAN) OF 'WRI$_ADV_RECS_IDX_02' (INDEX (UNIQUE)) (Cost=1 Card=345 rows Bytes=0/)
- #22 (3) TABLE ACCESS (BY INDEX ROWID) OF 'WRI$_ADV_RATIONALE' (TABLE) (Cost=15 Card=11 rows Bytes=0/36)
- *23 (22) INDEX (RANGE SCAN) OF 'WRI$_ADV_RATIONALE_PK' (INDEX (UNIQUE)) (Cost=3 Card=27293304 rows Bytes=0/)
- 24 (2) VIEW PUSHED PREDICATE OF 'VW_SQ_1' (VIEW) (Cost=4 Card=0 rows Bytes=0/)
- 25 (24) NESTED LOOPS (Cost=4 Card=0 rows Bytes=0/240)
- 26 (25) NESTED LOOPS (Cost=4 Card=0 rows Bytes=0/204)
- *27 (26) HASH JOIN (Cost=4 Card=0 rows Bytes=0/168)
- #28 (27) TABLE ACCESS (BY INDEX ROWID) OF 'SQLOBJ$AUXDATA' (TABLE) (Cost=2 Card=0 rows Bytes=0/462)
- *29 (28) INDEX (RANGE SCAN) OF 'I_SQLOBJ$AUXDATA_TASK' (INDEX) (Cost=1 Card=0 rows Bytes=0/)
- *#30 (27) INDEX (SKIP SCAN) OF 'SQLOBJ$_PKEY' (INDEX (UNIQUE)) (Cost=1 Card=0 rows Bytes=0/322)
- *31 (26) INDEX (UNIQUE SCAN) OF 'I_SQL$TEXT_PKEY' (INDEX (UNIQUE))
- *32 (25) INDEX (UNIQUE SCAN) OF 'I_SQL$_PKEY' (INDEX (UNIQUE))
- ------------- Predicate Information (Plan Hash Value:589376886) --------------
- 6 Filter: "RN"=1
- 7 Filter: ROW_NUMBER() OVER ( PARTITION BY "O"."ATTR1" ORDER BY BITAND("O"."ATTR7",32),INTERNAL_FUNCTION("E"."EXEC_START") DESC )<=1
- 14 Access: "TASK_ID"=:TID
- 14 Filter: ("NAME"=:BENAME OR "NAME"=:EENAME)
- 15 Filter: (INTERNAL_FUNCTION("E"."STATUS") AND ("BENAME"<>"EENAME" OR "E"."NAME"="BENAME"))
- 16 Access: "E"."TASK_ID"="R"."TASK_ID" AND "E"."EXEC_START">="BESTART" AND "E"."EXEC_START"<="EESTART"
- 17 Access: "O"."TASK_ID"="E"."TASK_ID" AND "O"."EXEC_NAME"="E"."NAME"
- 18 Filter: "O"."TYPE"=:SQLTYP
- 19 Access: "TASK_ID"="F"."TASK_ID" AND "EXEC_NAME"="F"."EXEC_NAME" AND "OBJECT_ID"="F"."OBJ_ID"
- 20 Filter: "R"."TYPE"=:PROFILETYP
- 21 Access: "F"."TASK_ID"="R"."TASK_ID" AND "F"."EXEC_NAME"="R"."EXEC_NAME" AND "F"."ID"="R"."FINDING_ID"
- 22 Filter: ("L"."TYPE"=:IMPLTYP AND "L"."ATTR1"=:ACCEPTDIS AND "L"."EXEC_NAME"="R"."EXEC_NAME" AND "L"."REC_ID"="R"."ID")
- 23 Access: "L"."TASK_ID"="R"."TASK_ID"
- 27 Access: "SO"."SIGNATURE"="AD"."SIGNATURE" AND "SO"."CATEGORY"="AD"."CATEGORY"
- 28 Filter: "AD"."OBJ_TYPE"=1
- 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"
- 30 Access: "SO"."OBJ_TYPE"=1
- 30 Filter: "SO"."OBJ_TYPE"=1
- 31 Access: "SO"."SIGNATURE"="ST"."SIGNATURE"
- 32 Access: "SO"."SIGNATURE"="SQ"."SIGNATURE"
- ------------- Plan Loading (Plan Hash Value:589376886) --------------
- 22: TABLE ACCESS BY INDEX ROWID #############################################(89.47%)
- 23: INDEX RANGE SCAN #####(10.53%)
- ------------- Waits Events (Plan Hash Value:589376886) --------------
- ON CPU on SYS.WRI$_ADV_RECS_IDX_02(INDEX) #####(9.65%)
- ------------- Statistics Data --------------
- Loads: 1
- Load Versions: 1
- First Load Time: 2014-03-12/22:46:24
- Last Load Time: 2014-03-12/22:46:24
- User Openings: 0
- Parse Calls: 11
- Executions: 11
- Sorts(Average): 2
- Fetches(Average): 1
- Disk Reads(Average): .545
- Buffer Gets(Average): 1416843.364
- Elapsed Time(Average): 10.507 seconds
- CPU Time(Average): 10.501 seconds
- Run Time Memory(Average): .005M
- PGA Size(Maximum): .015G
- Temp Space(Maximum): 0G
注意:这个版本是运行在11gR2上的。你如果要在其它版本运行,需要将含有哪些不存在的视图或字段的部分移除,如v$sql_monitor
点击此链接下载最新版本:http://www.HelloDBA.com/Download/showplan.zip
--- Fuyuncat ---