Re: Is it possible to tune this SQL statement?

frodo wrote:
> Is it possible to tune this statement without having to use a rule
> hint?
>
> select 'DATABASENAME: ORA_UNDO_SPACE: UNDO_AU: '||round(f.undo_au)||',
> UNDO_SIZE: '||round(g.undo_size)|| ' MB used in
> %:'||round((f.undo_au/g.undo_size) * 100,2)||' %. Please check!' from
> (select nvl( sum(sumbytes), 0 ) undo_au from (select status,
> sum(bytes)/1024/1024 sumbytes, count(*) from dba_undo_extents group by
> status ) sumundo
> where status in ('ACTIVE','UNEXPIRED')) f,
> (SELECT sum((VDF.bytes)/1024/1024) undo_size
> FROM v$datafile VDF, v$tablespace VTS, dba_tablespaces DTS
> WHERE DTS.status = 'ONLINE'
> AND DTS.contents = 'UNDO'
> AND VTS.ts# = VDF.ts#
> AND DTS.tablespace_name = VTS.name) g
> where (f.undo_au/g.undo_size) > 0.7;

Highly unlikely though you might get some improvement by replacing dba_tablespaces with the underlying physical table.

Then of course there is the collection of statistics on the data dictionary using DBMS_STATS but I'd not expect much given that you are puling from two v_$ objects.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)