"control file sequential read" on RAC

From: Shivaswamy Raghunath <shivaswamykr_at_gmail.com>
Date: Mon, 14 May 2007 10:42:39 -0400
Message-ID: <1e52ad820705140742i3a7e789fr532679b0440f6e34@mail.gmail.com>

Hello.

We have a pl/sql script which we run regularly in the database to monitor tablesapce usage and to generate email notification on our regular (non-RAC) database. This script when run on RAC database takes way too long to complete.(30+ minutes) whicle it completes in under a couple of minutes on the regular database.

I have identified the SQl and the associated event it is waiting on. Here is the extract from the Level 12 trace;

SELECT NVL(SUM(MAXBYTES-BYTES),0)
FROM
DBA_DATA_FILES WHERE FILE_ID IN (SELECT FILE_ID FROM DBA_DATA_FILES WHERE TABLESPACE_NAME=:B1 AND MAXBYTES <> 0)

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ----------

Parse        1      0.00       0.00          0          0
0           0
Execute    394      0.16       0.13          0          4
0           0
Fetch      394    525.73    1725.44          0    1592569     317564

394
------- ------ -------- ---------- ---------- ---------- ----------

total 789 525.89 1725.58 0 1592573 317564 394

Misses in library cache during parse: 1
Misses in library cache during execute: 1 Optimizer mode: ALL_ROWS
Parsing user id: 65 (recursive depth: 1)

Elapsed times include waiting on following events:

  Event waited on                             Times   Max. Wait  Total

Waited

Waited ----------
library cache lock 2 0.00 0.00 control file sequential read 3984128 0.01 1377.06 row cache lock 403 0.00 0.04 gc current block 3-way 1 0.00 0.00 gc current block 2-way 3 0.00 0.00

While I can try to dig in more in to the corresponding contril file on the ASM disk, I tend to believe this is because of some unexpected behavior on the part of Oracle.

BTW, we are on (Linux) ASM. Generally speaking I have not observed any major issues so far on IO related issues. DB is on 10.2.0.2

Can any of you throw some light on this?

Thanks,
Shiva

--

http://www.freelists.org/webpage/oracle-l Received on Mon May 14 2007 - 09:42:39 CDT