Re: OEM query replacement

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" <ORACLE-L_at_fatcity.com> 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).