Re: "control file sequential read" on RAC

  • From: "LS Cheng" <exriscer@xxxxxxxxx>
  • To: shivaswamykr@xxxxxxxxx
  • Date: Tue, 15 May 2007 21:40:26 +0200

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: