Re: "control file sequential read" on RAC

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
> > >
> > >
> > >
> > >
> >
>

Other related posts: