首页 > 代码库 > 使用showplan.sql分析sql Performance
使用showplan.sql分析sql Performance
在HelloDBA网站找到一个分析sql性能的工具—showplan，记录一下
showplan.sql下载路径：http://www.HelloDBA.com/Download/showplan.zip
使用方式就是调用该工具，传入SQL_ID作为参数。
SQL> @/dmp/showplan.sql 26xj87b2f8g6u
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: 26xj87b2f8g6u
------------- SQL Text --------------
SELECT TRIM("A1"."WORK_ORDER"),COUNT(*) FROM (SELECT DISTINCT "A5"."WORDER_ID" "WORDER_ID" FROM "WIP_WORK_ORDER" "A5","WIP_CODE" "A4" WHERE "A5"."NEXT_PFCD"="A4"."CODE_EXT"(+) AND "A4"."CODE_CATE"(+)=‘ERPP‘ AND "A5"."PLAN_ST_DT">= (SELECT TO_CHAR(SYSDATE@!-31,‘yyyy-mm-dd‘) FROM "SYS"."DUAL" "A6") AND "A5"."STATUS"<>‘3‘) "A2","HLBRHIS_SHIP" "A1" WHERE "A1"."CLM_MFDT">= (SELECT TO_CHAR(SYSDATE@!-31,‘yyyy-mm-dd‘) FROM "SYS"."DUAL" "A3") AND "A2"."WORDER_ID"=TRIM("A1"."WORK_ORDER") GROUP BY "A1"."WORK_ORDER"
------------- SQL Plan (Plan Hash Value:564968535; Parsed by schema:) --------------
0 ( )SELECT STATEMENT
1 (0) HASH (GROUP BY) (Cost=4256 Card=65 rows Bytes=0/82)
2 (1) NESTED LOOPS (Cost=4255 Card=133150 rows Bytes=0/82)
3 (2) NESTED LOOPS (Cost=4255 Card=153566712 rows Bytes=0/82)
4 (3) VIEW (Cost=178 Card=102 rows Bytes=0/13)
5 (4) HASH (UNIQUE) (Cost=178 Card=102 rows Bytes=0/60)
6 (5) HASH JOIN (OUTER) (Cost=177 Card=102 rows Bytes=0/240)
7 (6) TABLE ACCESS (BY INDEX ROWID) OF ‘WIP_WORK_ORDER‘ (TABLE) (Cost=171 Card=102 rows Bytes=0/160)
8 (7) INDEX (RANGE SCAN) OF ‘IDX1_WIP_WORK_ORDER‘ (INDEX) (Cost=3 Card=729 rows Bytes=0/)
9 (8) FAST DUAL (Cost=2 Card=1 rows Bytes=0/)
10 (6) INDEX (RANGE SCAN) OF ‘PK_WIP_CODE‘ (INDEX (UNIQUE)) (Cost=6 Card=786 rows Bytes=0/15820)
11 (3) PARTITION RANGE (ITERATOR) (Cost=154 Card=153566712 rows Bytes=0/)
12 (11) INDEX (RANGE SCAN) OF ‘HLBRHIS_SHIP_CLM_MFDT‘ (INDEX) (Cost=154 Card=153566712 rows Bytes=18735104/)
13 (12) FAST DUAL (Cost=2 Card=1 rows Bytes=0/)
14 (2) TABLE ACCESS (BY LOCAL INDEX ROWID) OF ‘HLBRHIS_SHIP‘ (TABLE) (Cost=4077 Card=133150 rows Bytes=377257984/56)
------------- Plan Loading (Plan Hash Value:564968535) --------------
14: TABLE ACCESS BY LOCAL INDEX ROWID ########################################(80.6%)
12: INDEX RANGE SCAN #####(10.01%)
2: NESTED LOOPS #####(9.36%)
1: HASH GROUP BY (.02%)
11: PARTITION RANGE ITERATOR (.01%)
------------- Waits Events (Plan Hash Value:564968535) --------------
ON CPU on PHBLWDA1.HLBRHIS_SHIP(TABLE PARTITION) #########################################(82.08%)
ON CPU on PHBLWDA1.HLBRHIS_SHIP_CLM_MFDT(INDEX PARTITION) ########(16.24%)
db file sequential read on PHBLWDA1.HLBRHIS_SHIP(TABLE PARTITION) #(1.06%)
ON CPU on PHBLWDA1.WIP_WORK_ORDER(TABLE) (.49%)
db file sequential read on PHBLWDA1.HLBRHIS_SHIP_CLM_MFDT(INDEX PARTITION) (.11%)
db file scattered read on PHBLWDA1.HLBRHIS_SHIP(TABLE PARTITION) (.01%)
db file parallel read on PHBLWDA1.HLBRHIS_SHIP(TABLE PARTITION) (0%)
free buffer waits on PHBLWDA1.HLBRHIS_SHIP(TABLE PARTITION) (0%)
------------- Statistics Data (Plan Hash Value:564968535)--------------
Loads: 28
Load Versions: 4
First Load Time: 2017-01-07/13:13:49
Last Load Time: 2017-01-10/01:33:57
User Openings: 0
Parse Calls: 312
Executions: 312
Sorts(Average): 0
Fetches(Average): 2
Disk Reads(Average): 124719.76
Buffer Gets(Average): 11172207.862
Elapsed Time(Average): 202.694 seconds
CPU Time(Average): 200.596 seconds
Run Time Memory(Average): 0M
PGA Size(Maximum): .009G
Temp Space(Maximum): 0G
SQL>
來自於http://www.hellodba.com/reader.php?ID=218&lang=EN的[One single SQL helps you to analyze the performance of a SQL]
使用showplan.sql分析sql Performance