| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: "control file sequential read" on RAC
There is a bug related to space management views in 10gR2, fixed in 10.2.0.4
.
I am not sure if it is related to your problem, but for example querying dba_extents takes ages in 10gR2, when RULE hint is added it runs in seconds instead of minutes, consistent gets down from 100 million gets to 10000.
This patch suppose to fix these issues related to space management views, 5029334, basically replacing catspace.sql.
The view definition for dba_data_files is as follows:
create or replace view DBA_DATA_FILES
(FILE_NAME, FILE_ID, TABLESPACE_NAME,
BYTES, BLOCKS, STATUS, RELATIVE_FNO, AUTOEXTENSIBLE,
MAXBYTES, MAXBLOCKS, INCREMENT_BY, USER_BYTES, USER_BLOCKS,
ONLINE_STATUS)
ts.blocksize * f.blocks, f.blocks,
decode(f.status$, 1, 'INVALID', 2, 'AVAILABLE', 'UNDEFINED'),
f.relfile#, decode(f.inc, 0, 'NO', 'YES'),
ts.blocksize * f.maxextend, f.maxextend, f.inc,
ts.blocksize * (f.blocks - 1), f.blocks - 1,
decode(fe.fetsn, 0, decode(bitand(fe.festa, 2), 0, 'SYSOFF',
'SYSTEM'),
decode(bitand(fe.festa, 18), 0, 'OFFLINE', 2, 'ONLINE', 'RECOVER'))
from sys.file$ f, sys.ts$ ts, sys.v$dbfile v, x$kccfe fe
where v.file# = f.file# and f.spare1 is NULL and f.ts# = ts.ts#
v.name,f.file#, ts.name,
decode(hc.ktfbhccval, 0, ts.blocksize * hc.ktfbhcsz, NULL),
decode(hc.ktfbhccval, 0, hc.ktfbhcsz, NULL),
decode(f.status$, 1, 'INVALID', 2, 'AVAILABLE', 'UNDEFINED'),
f.relfile#,
decode(hc.ktfbhccval, 0, decode(hc.ktfbhcinc, 0, 'NO', 'YES'), NULL),
decode(hc.ktfbhccval, 0, ts.blocksize * hc.ktfbhcmaxsz, NULL),
decode(hc.ktfbhccval, 0, hc.ktfbhcmaxsz, NULL),
decode(hc.ktfbhccval, 0, hc.ktfbhcinc, NULL),
decode(hc.ktfbhccval, 0, hc.ktfbhcusz * ts.blocksize, NULL),
decode(hc.ktfbhccval, 0, hc.ktfbhcusz, NULL),
decode(fe.fetsn, 0, decode(bitand(fe.festa, 2), 0, 'SYSOFF',
'SYSTEM'),
decode(bitand(fe.festa, 18), 0, 'OFFLINE', 2, 'ONLINE', 'RECOVER'))
from sys.v$dbfile v, sys.file$ f, sys.x$ktfbhc hc, sys.ts$ ts, x$kccfe fe
where v.file# = f.file# and f.spare1 is NOT NULL and v.file# = hc.ktfbhcafno
Try it and let us know :D
Thanks
-- LSC On 5/14/07, Shivaswamy Raghunath <shivaswamykr_at_gmail.com> wrote:Received on Tue May 15 2007 - 14:40:26 CDT
>
> 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
 |
 |