'gc current' waits on a SELECT statement!?
- From: PD Malik <pdthedba@xxxxxxxxx>
- To: Oracle-L Group <oracle-l@xxxxxxxxxxxxx>
- Date: Sun, 24 Jul 2011 23:11:40 +0100
Dear Experts,
My understanding is that the gc current waits only happen when a requester
is asking for the block in current mode i.e. to modify them (If it is
correct) then why would the following SELECT SQL wait on the gc current
waits pls :
SELECT B.LETTER_TYPE_ID, C.LETTER_REQUEST_ID
FROM
PER_LETTER_GEN_STATUSES A, PER_LETTER_TYPES B, PER_LETTER_REQUESTS C WHERE
A.ASSIGNMENT_STATUS_TYPE_ID = :B2 AND B.LETTER_TYPE_ID = A.LETTER_TYPE_ID
AND B.GENERATION_STATUS_TYPE = 'OTA_BOOKING' AND C.LETTER_TYPE_ID =
B.LETTER_TYPE_ID AND C.EVENT_ID= :B1 AND C.REQUEST_STATUS = 'PENDING' AND
C.AUTO_OR_MANUAL = 'AUTO' AND A.ENABLED_FLAG='Y'
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total
Waited
---------------------------------------- Waited ----------
------------
read by other session 37 0.00
0.01
gc buffer busy 65755 0.17
46.13
gc cr grant 2-way 6131 0.00
3.69
db file sequential read 6950 0.06
2.69
gc current block 2-way 6436 0.04
4.93
gc cr multi block request 19987 0.01
9.23
latch: cache buffers chains 1759 0.00
0.08
db file scattered read 4012 0.00
1.96
db file parallel read 251 0.05
0.21
latch: KCL gc element parent latch 23 0.00
0.01
gc current block congested 11 0.00
0.02
This SQL is the toppest contributor to our gc current wait events (due to
its number of executions).
I've also looked at and this SQL it doesn't fire any other recursive SQL
which cud explain these gc wait events plus looking at the block class (p3
of this wait event) the wait is all on data blocks (not on undo etc.) . Only
thing that I can think block cleanout but why wud this do that all the time
and in this magniture and is there any way to confirm that this is
happening.
These tables live in a ASSM/Locally managed tablespace as well.
Any help or tips will be appreciated.
Thanks.
Other related posts:
