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@xxxxxxxxxxxx> 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). >> >> > > > -- > //www.freelists.org/webpage/oracle-l > > > -- Don Seiler http://seilerwerks.wordpress.com ultimate: http://www.mufc.us -- //www.freelists.org/webpage/oracle-l