=================================================
start : 13-MAR-15 02.00.23.885 PM
end : 13-MAR-15 03.00.48.168 PM
snapid from 107851 to 107853
=================================================
********************************************************
list sql with buffer gets per execution > 100000
********************************************************
!!!! ########## Expensive SQL found ####### !!!!
instance : 1
sql_id : drdbm833ack3c
Buffer get : 260358
Execs : 1
BG/exec : 260358
Gets/row : 700
SQL TEXT :
SELECT p.rec_id, p.CREATE_DATE,
p.ordered_zzzyyy_type,p.qualified_zzzyyy_type,p.req_type,p.c_transid,p.status,p.
ERR_CODE,p.ERR_DESC,p.SVC_STATE,p.IS_DOWNGRADED,p.ETF_WAIVE_FLAG FROM
BL_XYZ_CHNG_PKG p WHERE p.req_type='R' AND p.status || ''='IN' AND
p.jrs_indicator IN ('1','2','3') and p.c_transid IN (SELECT MAX(C_TRANSID) FROM
BL_XYZ_CHNG_PKG GROUP BY rec_id)
...
After checking this sql, I've realized this may be a classical example where using analytic function can save resource.Original one -- Full table Scan on the same table twice 259k gets
SQL> SELECT p.rec_id,
2 p.CREATE_DATE,
3 p.ordered_zzzyyy_type,
4 p.qualified_zzzyyy_type,
5 p.req_type ,
6 p.c_transid,p.status,
7 p.ERR_CODE,p.ERR_DESC,p.SVC_STATE,p.IS_DOWNGRADED,p.ETF_WAIVE_FLAG
8 FROM
9 xyzu.BL_XYZ_CHNG_PKG p
10 WHERE p.req_type='R'
11 AND p.status || ''='IN'
12 AND p.jrs_indicator IN ('1','2','3')
13 and p.c_transid IN
14 (SELECT MAX(C_TRANSID) FROM BL_XYZ_CHNG_PKG GROUP BY rec_id)
15 ;
242 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3808252951
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1613 | 127K| | 83112 (3)| 00:16:38 |
|* 1 | HASH JOIN | | 1613 | 127K| | 83112 (3)| 00:16:38 |
|* 2 | TABLE ACCESS FULL | BL_XYZ_CHNG_PKG | 1613 | 107K| | 25463 (3)| 00:05:06 |
| 3 | VIEW | VW_NSO_1 | 6443K| 79M| | 57578 (2)| 00:11:31 |
| 4 | HASH GROUP BY | | 6443K| 86M| 148M| 57578 (2)| 00:11:31 |
| 5 | TABLE ACCESS FULL| BL_XYZ_CHNG_PKG | 6459K| 86M| | 25154 (2)| 00:05:02 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("P"."C_TRANSID"="$nso_col_1")
2 - filter("P"."REQ_TYPE"='R' AND "P"."STATUS"||''='IN' AND ("P"."JRS_INDICATOR"='1'
OR "P"."JRS_INDICATOR"='2' OR "P"."JRS_INDICATOR"='3'))
Statistics
----------------------------------------------------------
95 recursive calls
0 db block gets
259618 consistent gets
267154 physical reads
0 redo size
10348 bytes sent via SQL*Net to client
261 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
242 rows processed
Rewrite with rank() over partition by construct - one Full table scan with 129k
SQL> SELECT rec_id, 2 CREATE_DATE, 3 ordered_zzzyyy_type, 4 qualified_zzzyyy_type, 5 req_type , 6 c_transid,status, 7 ERR_CODE,ERR_DESC,SVC_STATE,IS_DOWNGRADED,ETF_WAIVE_FLAG 8 from 9 ( 10 SELECT p.rec_id, 11 p.CREATE_DATE, 12 p.ordered_zzzyyy_type, 13 p.qualified_zzzyyy_type, 14 p.req_type , 15 p.c_transid,p.status, 16 p.ERR_CODE,p.ERR_DESC,p.SVC_STATE,p.IS_DOWNGRADED,p.ETF_WAIVE_FLAG, 17 rank() over ( partition by rec_id order by c_transid desc ) rank 18 FROM 19 xyzu.BL_XYZ_CHNG_PKG p 20 WHERE p.req_type='R' 21 AND p.status || ''='IN' 22 AND p.jrs_indicator IN ('1','2','3') 23 ) A 24 where rank=1; 242 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 3870177004 -------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1613 | 546K| 25464 (3)| 00:05:06 | |* 1 | VIEW | | 1613 | 546K| 25464 (3)| 00:05:06 | |* 2 | WINDOW SORT PUSHED RANK| | 1613 | 107K| 25464 (3)| 00:05:06 | |* 3 | TABLE ACCESS FULL | BL_XYZ_CHNG_PKG | 1613 | 107K| 25463 (3)| 00:05:06 | -------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("RANK"=1) 2 - filter(RANK() OVER ( PARTITION BY "REC_ID" ORDER BY INTERNAL_FUNCTION("C_TRANSID") DESC )<=1) 3 - filter("P"."REQ_TYPE"='R' AND "P"."STATUS"||''='IN' AND ("P"."JRS_INDICATOR"='1' OR "P"."JRS_INDICATOR"='2' OR "P"."JRS_INDICATOR"='3')) Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 129827 consistent gets 127794 physical reads 0 redo size 10184 bytes sent via SQL*Net to client 261 bytes received via SQL*Net from client 4 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 242 rows processed
