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)
as
select v.name, f.file#, ts.name,
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#
and fe.fenum = f.file#
union all
select
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
and hc.ktfbhctsn = ts.ts#
and fe.fenum = f.file#
/
Try it and let us know :D
Thanks
--
LSC
On 5/14/07, Shivaswamy Raghunath <shivaswamykr@xxxxxxxxx> wrote:
>
> 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
>
>
>
>
