FAQ
Hey all,

Since we've added a datafile, our "Tablespace Full" event in OEM has been
hammering the DB. Waits go high on Controlfile I/O when the following is
running (no doubt from the v$datafile scan):

select sum(f.bytes)/:"SYS_B_0", floor(sum(f.bytes)/:"SYS_B_1")
from sys.dba_free_space f, v$datafile d
where f.file_id = d.file# and d.status in (:"SYS_B_2", :"SYS_B_3") and
tablespace_name = :"SYS_B_4"
group by tablespace_name

Anyone have a direct replacement query for this that performs better? We're
on 8.1.7.4, but I wouldn't want to limit the query to this DB version. 8i
or above would be nice.

BTW, we're using CURSOR_SHARING=FORCE, thus the SYS binds...

TIA!

Rich

Rich Jesse System/Database Administrator
rich.jesse_at_qtiworld.com Quad/Tech International, Sussex, WI USA

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jesse, Rich
INET: Rich.Jesse_at_qtiworld.com

Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).

Search Discussions

  • Arup Nanda at Feb 19, 2003 at 7:14 pm
    Rich,

    Was your sys schema accidentally analyzed? It may pay to drop stats on all
    the sys objects using BOTH

    (1) analyze
    and (2) dbms_stats

    HTH

    Arup
    ----- Original Message -----
    To: "Multiple recipients of list ORACLE-L"
    Sent: Wednesday, February 19, 2003 11:03 AM
    Hey all,

    Since we've added a datafile, our "Tablespace Full" event in OEM has been
    hammering the DB. Waits go high on Controlfile I/O when the following is
    running (no doubt from the v$datafile scan):

    select sum(f.bytes)/:"SYS_B_0", floor(sum(f.bytes)/:"SYS_B_1")
    from sys.dba_free_space f, v$datafile d
    where f.file_id = d.file# and d.status in (:"SYS_B_2", :"SYS_B_3") and
    tablespace_name = :"SYS_B_4"
    group by tablespace_name

    Anyone have a direct replacement query for this that performs better? We're
    on 8.1.7.4, but I wouldn't want to limit the query to this DB version. 8i
    or above would be nice.

    BTW, we're using CURSOR_SHARING=FORCE, thus the SYS binds...


    TIA!
    Rich

    Rich Jesse System/Database Administrator
    rich.jesse_at_qtiworld.com Quad/Tech International, Sussex, WI USA
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Jesse, Rich
    INET: Rich.Jesse_at_qtiworld.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Arup Nanda
    INET: orarup_at_hotmail.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Jesse, Rich at Feb 19, 2003 at 9:04 pm
    Thanks but there's no stats on those. As I said, the controlfile I/O is
    what hits high, which would seem to indicate that v$datafile is the problem.

    Thanks,
    Rich

    Rich Jesse System/Database Administrator
    rich.jesse_at_qtiworld.com Quad/Tech International, Sussex, WI USA

    -----Original Message-----
    Sent: Wednesday, February 19, 2003 1:14 PM
    To: Multiple recipients of list ORACLE-L

    Rich,

    Was your sys schema accidentally analyzed? It may pay to drop stats on all
    the sys objects using BOTH

    (1) analyze
    and (2) dbms_stats

    HTH

    Arup
    ----- Original Message -----
    To: "Multiple recipients of list ORACLE-L"
    Sent: Wednesday, February 19, 2003 11:03 AM
    Hey all,

    Since we've added a datafile, our "Tablespace Full" event in OEM has been
    hammering the DB. Waits go high on Controlfile I/O when the following is
    running (no doubt from the v$datafile scan):

    select sum(f.bytes)/:"SYS_B_0", floor(sum(f.bytes)/:"SYS_B_1")
    from sys.dba_free_space f, v$datafile d
    where f.file_id = d.file# and d.status in (:"SYS_B_2", :"SYS_B_3") and
    tablespace_name = :"SYS_B_4"
    group by tablespace_name

    Anyone have a direct replacement query for this that performs better? We're
    on 8.1.7.4, but I wouldn't want to limit the query to this DB version. 8i
    or above would be nice.

    BTW, we're using CURSOR_SHARING=FORCE, thus the SYS binds...


    TIA!
    Rich
    .

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Jesse, Rich
    INET: Rich.Jesse_at_qtiworld.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedFeb 19, '03 at 4:03p
activeFeb 19, '03 at 9:04p
posts3
users2
websiteoracle.com

2 users in discussion

Jesse, Rich: 2 posts Arup Nanda: 1 post

People

Translate

site design / logo © 2022 Grokbase