Re: DBA_SEGMENTS QUERY
- From: Tanel Põder <tanel.poder.003@xxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 23 Apr 2004 00:30:56 +0300
Hi!
> Did Kevin Loney tell you the reason and how to change it,just curious ?
>
> One of the reasons why a query against dba_Segments would be slower is
becos
> of Locally Managed tablespace! becos the information about extents is not
> readily cached in data dictionary like dictionary managed tablespaces .
>
I don't have a 8i handy here, but in 9.2, the dba_segments view executes
dbms_space_admin.segment_number_extents once and
dbms_space_admin.segment_number_blocks functions twice for every row
returned from it's underlying view sys_dba_segs which is based on seg$.
SEG$ columns blocks and extents aren't used for storing the number of
extents and blocks of a segment residing in LMT tablespace anymore. So now,
when allocating an extent, we don't have to do a costly recursive
transaction on data dictionary anymore, we just update the segment header
block directly.
When selecting blocks, bytes and extents from dba_segments, above mentioned
dbms_space_admin functions will go and read the extent and block amounts for
each segment directly from segment header. The bad thing is that these
functions always do a physical read on the header block - no caching here!
So, when selecting all columns from dba_segments, at least 3 physical IOs
have to be done for each row returned - meaning for each segment reported!
The dba_extents view does something similar for LMT tablespaces, possibly
causing excessive PIOs, when too unrestricted queries are ran against it.
So how to improve performance?
Don't query them (or these problematic columns) unless you really have to!
If your monitoring system is configured to do a full fetch on them every 5
minutes, then this is probably overkill.
If you really want to query dba_segments, then you might be able to get rid
of one third of PIOs by rewriting the view in that way that
dbms_space_admin.segment_number_blocks wouldn't be executed twice for every
row, once is actually enough. Oracle executes it the first time to get the
number of blocks for segment and the second time to get number of bytes for
the segment - which is basically getting number of blocks and multiplying it
with the block size of corresponding tablespace, which you could do without
regetting the block count from segment header again.
Tanel.
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
