Thanks, Cheng.
But it did not help either. It does the smae consistent gets and takes
same time.
On 5/15/07, LS Cheng < exriscer@xxxxxxxxx> wrote:
>
> 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
> >
> >
> >
> >
>
