RE: OEM query replacement

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

.

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