I think its 5029334, if it does not help it might be something else then :-( Thanks -- LSC On 5/15/07, Shivaswamy Raghunath <shivaswamykr@xxxxxxxxx> wrote:
Forgot to ask you, do you have the bug number? On 5/15/07, Shivaswamy Raghunath <shivaswamykr@xxxxxxxxx> wrote: > > 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 > > > > > > > > > > > > > > >