Analyze SQL Performance using Showplan.sql

Find a tool for analyzing SQL performance on the HELLODBA website-showplan, record

Showplan.sql Download Path: http://www.HelloDBA.com/Download/showplan.zip

The way to use this is to call the tool and pass in sql_id as a parameter.

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 ([email protected]!-31, ' yyyy-mm-dd ') from" SYS "." DUAL "" A6 ") and" A5 "." STATUS "<> ' 3 ')" A2 "," Hlbrhis_ship "" A1 "WHERE" A1 "." CLM_MFDT ">= (select To_char ([email protected]!-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/)
Ten (6) index (RANGE SCAN) of ' Pk_wip_code ' (Index (UNIQUE)) (cost=6 card=786 rows bytes=0/15820)
One (3) PARTITION RANGE (ITERATOR) (cost=154 card=153566712 rows bytes=0/)
(one) INDEX (RANGE SCAN) of ' HLBRHIS_SHIP_CLM_MFDT ' (INDEX) (cost=154 card=153566712 rows bytes=18735104/)
FAST DUAL (cost=2 card=1 rows bytes=0/)
(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 (. 2%)
11:partition RANGE ITERATOR (. 1%)

-------------Waits Events (Plan Hash value:564968535)--------------

On the CPU on PHBLWDA1. Hlbrhis_ship (TABLE PARTITION) ######################################### (82.0 8%)
On the CPU on PHBLWDA1. HLBRHIS_SHIP_CLM_MFDT (INDEX PARTITION) ######## (16.24%)
DB file sequential read on PHBLWDA1. Hlbrhis_ship (TABLE PARTITION) # (1.06%)
On the 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) (. 1%)
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>

From Http://www.hellodba.com/reader.php?ID=218&lang=EN's [one single SQL helps your to analyze the performance of a SQL]

Analyze SQL Performance using Showplan.sql