Re: Faster selecting tablespace fill level
Date: Thu, 25 Feb 2010 14:18:39 -0600
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.
On Thu, Feb 25, 2010 at 1:37 PM, Stephane Faroult <sfaroult_at_roughsea.com> wrote:
> 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).
-- Don Seiler http://seilerwerks.wordpress.com ultimate: http://www.mufc.us -- http://www.freelists.org/webpage/oracle-lReceived on Thu Feb 25 2010 - 14:18:39 CST