Hi,
We have a 10.2.0.4.0 2.node RAC database on Windows 2003 (all 64-bit).
Looking at the "top 5 timed events" section of the AWR reports (always for 1 hour), we always see "CPU time" as the number one event (due to our application, some issues hopefully being looked at right now by developers..) but recently I am seeing "control file sequential read" as the number two event, with 3,574,633 waits and time of 831s. I was hoping to find out what was causing this high number of waits. I started by trying to find a particular query that experienced this wait often, so I ran this SQL:
select sql_id, count(*)
from dba_hist_active_sess_history
where event_id = (select event_id from v$event_name where name = 'control file sequential read')
group by sql_id
order by 2 desc ;
As I was hoping for, the top sql_id returned truly stands out, with a count of 14,182 (the next sql_id has a count of 68). This is the sql text for this id:
WITH unit AS(
SELECT UNIQUE S.unit_id
FROM STOCK S, HOLDER H
WHERE H.H_ID = S.H_ID
AND H.LOC_ID = :B2
AND S.PROD_ID = :B1
)
SELECT DECODE((SELECT COUNT(*) FROM unit), 1, unit_ID, NULL)
FROM unit
WHERE ROWNUM = 1
;
(Ok, slightly strange code, but I've already got them to change it.)
My question is:
Why/what does this code have to read from the control file?
Regards,
Ados
PS - I also checked the block number in p2 of dba_hist_active_sess_history for this sql_id and event_id, and it is always one of 5 blocks in the controlfile. I dumped the controlfile, but don't see anything interesting (although admittedly, this is the first time I've dumped a controlfile so have no idea really what to do!).