FAQ
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

Search Discussions

  • Alex Gorbachev at May 15, 2007 at 12:28 pm
    Seems like it's inefficient execution plan.
    I know that Oracle might go crazy joining dictionary views and V$
    views. Can you try to avoid sub-query there? Could be that oracle
    "optimized" you select differently for RAC.
    On 5/14/07, Shivaswamy Raghunath 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.
    --
    Best regards,
    Alex Gorbachev

    http://www.oracloid.com
    --
    http://www.freelists.org/webpage/oracle-l
  • Norman Dunbar at May 15, 2007 at 1:49 pm
    Afternoon all,
    "Alex Gorbachev" 05/15/07 01:28PM >>>
    Seems like it's inefficient execution plan.
    I know that Oracle might go crazy joining dictionary views and V$
    views.
    I've been told by Oracle trainers that joining a V$ view to an physical
    table or other dictionary tyope objkect is fraught with danger because
    :

    the V$ stuff is memory based and not protected by read consistency
    the V$ stuff changes frequently

    When you join V$ to others, each time the V$ changes, Oracle starts the
    query again. It only finishes when it manages to join all of V$ with
    whatever without a change occurring part way through.

    I used to join V$SESSION_WAIT to DBA_OBJECTS and performance was
    abysmal :o(

    Cheers,
    Norman.

    Norman Dunbar.
    Contract Oracle DBA.
    Rivers House, Leeds.

    Internal : 7 28 2051
    External : 0113 231 2051

    Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.

    We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.

    We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.

    If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk
  • Jared Still at May 15, 2007 at 6:09 pm

    On 5/15/07, Norman Dunbar wrote:
    "Alex Gorbachev" 05/15/07 01:28PM >>>
    Seems like it's inefficient execution plan.
    I know that Oracle might go crazy joining dictionary views and V$
    views.
    I've been told by Oracle trainers that joining a V$ view to an physical
    table or other dictionary tyope objkect is fraught with danger because:

    * the V$ stuff is memory based and not protected by read consistency
    * the V$ stuff changes frequently

    When you join V$ to others, each time the V$ changes, Oracle starts the
    query again. It only finishes when it manages to join all of V$ with
    whatever without a change occurring part way through.
    Though I have in the past run into difficulty with v$ views in joins, I've
    never before heard this bit.

    Being something of a skeptic, I would probably need to be convinced
    with some evidence. :)

    The 'ordered' hint has worked well for me in the past when joining V$
    views.

    For instance:

    1 select /*+ ordered */

    2 --b.kaddr,
    3 c.sid,
    4 c.username,

    ...
    155 from

    156 v$lock b
    157,v$session c
    158,sys.user$ u
    159,sys.obj$ o
    160,( select * from sys.dba_waiters) lock_blocker
    161,( select * from sys.dba_waiters) lock_waiter

    162 where
    163 b.sid = c.sid
    164 and u.user# = c.user#
    165 and o.obj#(+) = b.id1
    166 and lock_blocker.waiting_session(+) = c.sid
    167 and lock_waiter.holding_session(+) = c.sid
    168 and c.username != 'SYS'
    169 --order by kaddr, lockwait
    170* order by b.sid, object

    Without the ordered hint this query can be really slow.
    Been awhile since I tested it though. I run it regularly on 9i
    and 10g, but the ordered hint was added for 8i.

    Alex G. mentioned the use of subquery factoring (WITH clause).

    That makes queries easier to read and offers the ability to materialize
    the subquery, which might be a good thing.

    I recently ran into a problem with it however. When using subquery
    factoring with a somewhat complex bit of SQL, the optimizer would
    not come up with a favorable execution plan. The MATERIALIZE and
    INLINE hints returned the same plans.

    The query would only work in a reasonable time when re-written
    without subquery factoring.

    It would be interesting to see if SF would help your query.

    --
    Jared Still
    Certifiable Oracle DBA and Part Time Perl Evangelist

    --
    http://www.freelists.org/webpage/oracle-l
  • Shivaswamy Raghunath at May 15, 2007 at 6:51 pm
    Thanks Jared for pointing out.

    Alex G.'s query works fine, just as the RULE hint. :)
    On 5/15/07, Jared Still wrote:
    On 5/15/07, Norman Dunbar wrote:

    "Alex Gorbachev" 05/15/07 01:28PM >>>
    Seems like it's inefficient execution plan.
    I know that Oracle might go crazy joining dictionary views and V$
    views.
    I've been told by Oracle trainers that joining a V$ view to an physical
    table or other dictionary tyope objkect is fraught with danger because:

    * the V$ stuff is memory based and not protected by read consistency
    * the V$ stuff changes frequently

    When you join V$ to others, each time the V$ changes, Oracle starts the
    query again. It only finishes when it manages to join all of V$ with
    whatever without a change occurring part way through.
    Though I have in the past run into difficulty with v$ views in joins, I've
    never before heard this bit.

    Being something of a skeptic, I would probably need to be convinced
    with some evidence. :)

    The 'ordered' hint has worked well for me in the past when joining V$
    views.

    For instance:

    1 select /*+ ordered */
    2 --b.kaddr,
    3 c.sid,
    4 c.username,
    ...
    155 from
    156 v$lock b
    157,v$session c
    158,sys.user$ u
    159,sys.obj$ o
    160,( select * from sys.dba_waiters) lock_blocker
    161,( select * from sys.dba_waiters) lock_waiter
    162 where
    163 b.sid = c.sid
    164 and u.user# = c.user#
    165 and o.obj#(+) = b.id1
    166 and lock_blocker.waiting_session(+) = c.sid
    167 and lock_waiter.holding_session(+) = c.sid
    168 and c.username != 'SYS'
    169 --order by kaddr, lockwait
    170* order by b.sid, object

    Without the ordered hint this query can be really slow.
    Been awhile since I tested it though. I run it regularly on 9i
    and 10g, but the ordered hint was added for 8i.

    Alex G. mentioned the use of subquery factoring (WITH clause).

    That makes queries easier to read and offers the ability to materialize
    the subquery, which might be a good thing.

    I recently ran into a problem with it however. When using subquery
    factoring with a somewhat complex bit of SQL, the optimizer would
    not come up with a favorable execution plan. The MATERIALIZE and
    INLINE hints returned the same plans.

    The query would only work in a reasonable time when re-written
    without subquery factoring.

    It would be interesting to see if SF would help your query.


    --
    Jared Still
    Certifiable Oracle DBA and Part Time Perl Evangelist
    --
    http://www.freelists.org/webpage/oracle-l
  • Alex Gorbachev at May 15, 2007 at 2:16 pm
    One way would be to use WITH clause and make Oracle instantiating
    temporary tables that are then joined.
    On 5/15/07, Norman Dunbar wrote:
    I've been told by Oracle trainers that joining a V$ view to an physical
    table or other dictionary tyope objkect is fraught with danger because
    --
    Best regards,
    Alex Gorbachev

    http://www.oracloid.com
    --
    http://www.freelists.org/webpage/oracle-l
  • Shivaswamy Raghunath at May 15, 2007 at 2:29 pm
    When something does not work, we hear all those caveats, right? :)

    How about this extract for the same query, with a hint, on the same
    database.? 1725 to 7 sec?!

    SELECT NVL(SUM(MAXBYTES),0)

    FROM

    DBA_DATA_FILES WHERE TABLESPACE_NAME=:B1 AND AUTOEXTENSIBLE ='YES'

    call count cpu elapsed disk query current
    rows
    ------- ------ -------- ---------- ---------- ---------- ----------

    Parse 1 0.00 0.00 0 0
    0 0
    Execute 394 0.10 0.10 0 0
    0 0
    Fetch 394 4.41 7.08 0 3156 158782

    394
    ------- ------ -------- ---------- ---------- ---------- ----------

    total 789 4.52 7.19 0 3156 158782
    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 ----------

    control file sequential read 8288 0.00
    3.01

    I used a RULE hint, yes on 10.2 database.

    I found that, x$kccfe which is used in the defenition of dba_data_files (Now
    in 10.2 to give the ONLINE_STATUS Of the data file) is causing the issue.
    But I have not yet gone in to the root cause.

    Meanwhile, if you have more insight do let me know.
    On 5/15/07, Alex Gorbachev wrote:

    One way would be to use WITH clause and make Oracle instantiating
    temporary tables that are then joined.
    On 5/15/07, Norman Dunbar wrote:
    I've been told by Oracle trainers that joining a V$ view to an physical
    table or other dictionary tyope objkect is fraught with danger because
    --
    Best regards,
    Alex Gorbachev

    http://www.oracloid.com
    --
    http://www.freelists.org/webpage/oracle-l
  • Alexander Fatkulin at May 15, 2007 at 2:46 pm
    Have you collected stats on the fixed objects?

    dbms_stats.gather_fixed_objects_stats
    I found that, x$kccfe which is used in the defenition of
    dba_data_files (Now in 10.2 to give the >ONLINE_STATUS Of the data
    file) is causing the issue. But I have not yet gone in to the root
    cause.

    Meanwhile, if you have more insight do let me know.
    --
    Alexander Fatkulin
    --
    http://www.freelists.org/webpage/oracle-l
  • Shivaswamy Raghunath at May 15, 2007 at 2:51 pm
    Fixed table stats are collected.
    On 5/15/07, Alexander Fatkulin wrote:

    Have you collected stats on the fixed objects?

    dbms_stats.gather_fixed_objects_stats
    I found that, x$kccfe which is used in the defenition of
    dba_data_files (Now in 10.2 to give the >ONLINE_STATUS Of the data
    file) is causing the issue. But I have not yet gone in to the root
    cause.

    Meanwhile, if you have more insight do let me know.

    --
    Alexander Fatkulin
    --
    http://www.freelists.org/webpage/oracle-l
  • Alexander Fatkulin at May 15, 2007 at 2:38 pm
    Why not just do

    select NVL(SUM(MAXBYTES-BYTES),0)

    from DBA_DATA_FILES
    where TABLESPACE_NAME=:B1
    and MAXBYTES <> 0

    instead? You don't need a subquery here?...
    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)
    --
    Alexander Fatkulin
    --
    http://www.freelists.org/webpage/oracle-l
  • LS Cheng at May 15, 2007 at 7:40 pm
    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 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


    --
    http://www.freelists.org/webpage/oracle-l
  • Shivaswamy Raghunath at May 15, 2007 at 8:04 pm
    Thanks, Cheng.

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


    --
    http://www.freelists.org/webpage/oracle-l
  • Shivaswamy Raghunath at May 15, 2007 at 8:04 pm
    Forgot to ask you, do you have the bug number?
    On 5/15/07, Shivaswamy Raghunath 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_at_gmail.com> 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_at_gmail.com> 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


    --
    http://www.freelists.org/webpage/oracle-l
  • LS Cheng at May 15, 2007 at 11:10 pm
    I think its 5029334, if it does not help it might be something else then :-(

    Thanks

    --
    LSC
    On 5/15/07, Shivaswamy Raghunath wrote:

    Forgot to ask you, do you have the bug number?
    On 5/15/07, Shivaswamy Raghunath 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_at_gmail.com> 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_at_gmail.com> 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


    --
    http://www.freelists.org/webpage/oracle-l
  • Karla, Srini \(HAS-SAT\) at May 18, 2007 at 7:38 pm
    All,

    Any one has apps financials and projects setup documents. I have
    installed Apps 10.5.10.2 from media, every thing is working fine. Now I
    want implement the AR, AP GL, OP modules, Please send me step-by-step
    implementation documents for these modules.



    Thanks in advance.



    Thanks,
    Srini Karla
    Sr. Database Administrator,
  • Jared Still at May 18, 2007 at 9:12 pm

    On 5/18/07, Karla, Srini (HAS-SAT) wrote:
    All,

    Any one has apps financials and projects setup documents. I have
    installed Apps 10.5.10.2 from media, every thing is working fine. Now I
    want implement the AR, AP GL, OP modules, Please send me step-by-step
    implementation documents for these modules.
    All implementation steps will be found at the following URL:
    *

    http://tinyurl.com/394ub3

    *

    --
    Jared Still
    Certifiable Oracle DBA and Part Time Perl Evangelist

    --
    http://www.freelists.org/webpage/oracle-l

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedMay 14, '07 at 2:42p
activeMay 18, '07 at 9:12p
posts16
users7
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase