Re: Faster selecting tablespace fill level

From: Don Seiler <don_at_seiler.us>
Date: Thu, 25 Feb 2010 14:18:39 -0600
Message-ID: <94e86aed1002251218p27333822r5ca0e3e0d1f1671e_at_mail.gmail.com>

Also good to ensure that you have the latest version of a DBA view. Some of them aren't updated automatically. I know from personal experience that the catblock.sql views (dba_locks, dba_blockers, etc.) are not automatically udpated when you upgrade the database, and one of the views had a major query re-write that drastically improved performance.

So, compare the DDL of the view in your database with the scripts in ?/rdbms/admin/ to make sure that you have the latest incarnations available for your version.

Don.

On Thu, Feb 25, 2010 at 1:37 PM, Stephane Faroult <sfaroult_at_roughsea.com> wrote:
> Martin,
>
>    Generally speaking, whenever you have performance isues with DBA
> views, the best thing to do is to check in the catalog script how they
> were defined, peek into sql.bsq, which is fortunately very well
> commented, to get a better understanding of the underlying tables, and
> to rewrite your queries from scratch. The problem with DBA_ views is
> that they are mega joins, which becomes a problem when you join one DBA
> view to another (usually on unindexed columns) or aggregate them
> enthusiastically. In your case, start with SYS.SEG$, aggregate by ts#
> and join on SYS.TS$ when you have all your figures, not before, and you
> should be happy with performance.
>
> Stephane Faroult
> RoughSea Ltd <http://www.roughsea.com>
> RoughSea Channel on Youtube <http://www.youtube.com/user/roughsealtd>
>
>
> Martin Klier wrote:
>> Dear list,
>>
>> is there a way to get the results from the select below, in a more
>> peaceful way?
>>
>> An execution plan on that piece of SQL opens a glance into performance
>> issues deep and deeper. (My 10g DB calculates cost of over 12k, and 530k
>> buffer gets).
>>
>>
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 
Don Seiler
http://seilerwerks.wordpress.com
ultimate: http://www.mufc.us
--
http://www.freelists.org/webpage/oracle-l

Received on Thu Feb 25 2010 - 14:18:39 CST