Oracle Database Discussions

Hi ,

I have a query with very very high CPU Waits.I gathered SQL Report and found that buffer gets for that query are very very high.
My first thought was it is because of hot blocks.Refered metalink Note 163424.1 and found that Hot Block issue will be reflected or checked through latch activity in AWR.In My AWR latch activity is alright and there were no sleeps for any latches.
Now why this many Buffer Gets(14,955.67 per execution) even it is using index for one table and for one full table scan it is fectching only 175 bytes (showed in explain plan) and when there is No latch contention.Followin are the details of that SQL.Table siaxe is around 1GB Only.

Plan Statistics
% Total DB Time is the Elapsed Time of the SQL statement divided into the Total Database Time multiplied by 100
Stat Name Statement Total Per Execution % Snap Total
Elapsed Time (ms) 3,371,238 72.96 91.04
CPU Time (ms) 3,310,138 71.64 93.89
Executions 46,204
Buffer Gets 691,011,737 14,955.67 95.95
Disk Reads 1 0.00 0.00
Parse Calls 0 0.00 0.00
Rows 48 0.00
User I/O Wait Time (ms) 8
Cluster Wait Time (ms) 0
Application Wait Time (ms) 0
Concurrency Wait Time (ms) 11
Invalidations 0
Version Count 1
Sharable Mem(KB) 27

Back to Plan 1(PHV: 2110432156)
Back to Top


Execution Plan
Id Operation Name Rows Bytes Cost (%CPU) Time
0 SELECT STATEMENT 4 (100)
1 SORT GROUP BY NOSORT 1 200 4 (0) 00:00:01
2 NESTED LOOPS 1 200 4 (0) 00:00:01
3 NESTED LOOPS 1 181 3 (0) 00:00:01
4 INDEX UNIQUE SCAN XPKLOAN_FACILITY_DETAILS 1 6 1 (0) 00:00:01
5 TABLE ACCESS FULL T_ICL_LOAN_CFLW_DETL 1 175 2 (0) 00:00:01
6 TABLE ACCESS BY INDEX ROWID T_ICL_LOAN_TRANS_DETL 1 19 1 (0) 00:00:01
7 INDEX UNIQUE SCAN XPKLOAN_TRANSACTION_DETAILS 1 0 (0)

Back to Plan 1(PHV: 2110432156)
Back to Top



Full SQL Text
SQL Id SQL Text
7xjb1604h8j88 SELECT NVL(SUM(T_ICL_LOAN_CFLW_DETL.CFLW_AMT), 0) AS CFLW_AMT1 FROM T_ICL_LOAN_CFLW_DETL, T_ICL_LOAN_TRANS_DETL, T_ICL_LOAN_FCLTY_DETL WHERE T_ICL_LOAN_FCLTY_DETL.CONT_NBR_ID = T_ICL_LOAN_TRANS_DETL.CONT_NBR_ID AND T_ICL_LOAN_TRANS_DETL.TRANS_NBR_ID = T_ICL_LOAN_CFLW_DETL.TRANS_NBR_ID AND T_ICL_LOAN_TRANS_DETL.TRANS_NBR_ID = T_ICL_LOAN_CFLW_DETL.AFFECT_NBR AND TRANS_KIND_NM IN ('Due Amount Carry Forward') AND CFLW_TYPE_NM IN ('Principal', 'Amortization') AND T_ICL_LOAN_TRANS_DETL.CONT_NBR_ID=:B2 AND T_ICL_LOAN_CFLW_DETL.CFLW_PYMT_DATE < :B1 GROUP BY T_ICL_LOAN_FCLTY_DETL.CONT_NBR_ID


Thanks
Pramod