使用showplan.sql分析sql Performance
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了使用showplan.sql分析sql Performance,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含4723字,纯文字阅读大概需要7分钟。
内容图文
![使用showplan.sql分析sql Performance](/upload/InfoBanner/zyjiaocheng/474/03059060f57943b4bbc212945accabe8.jpg)
在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
标签:version 2.0 sql_id pac mem ash bin www bsp
本文系统来源:http://www.cnblogs.com/guilingyang/p/6274475.html
内容总结
以上是互联网集市为您收集整理的使用showplan.sql分析sql Performance全部内容,希望文章能够帮你解决使用showplan.sql分析sql Performance所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。