Re: What is impact of too many rows in fet$ and uet$

it doesn't have as much to with how many extents you have for a tablespace (although that may make this particular query slower),but how many extents you have for a given segment. In general, it doesn't really matter how many extents a particular segment has. One exception to this rule is when a
particular segment in a dictionary managed TS has an obscene number of extents (hundreds of thousands). When this happens, space management operations can come to a standstill. This because FET and UET are clustered together and
sized to assume that a segment will have around 5 extents. If a segment has more than that
number, the additional extents will be stored in another block. This isn't really that big of deal unless you get
a segment with a huge number of extents that chains thousands of blocks together. When this occers the RDBMS will have to read through all of the chained blocks to perform space management operations
on the segment. Worse yet, space management operations on dictionary managed TSs take out a ST enqueue that is global to the entire database (i.e. only one space management operation can go on at a time). Doing any kind of ddl on segments
with too many extents can be a nightmare. I had a table that took 45 minutes to drop but I have heard stories of databases that had to be put into single user mode and spent THREE DAYS dropping a table with a ton of extents (remember we are talking hundreds of thousands to millions here). Locally managed TSs don't have this feature. So if all those extents are evenly distributed to segments in that tablespace, you don't have a problem (aside from the slow dba_free_space query) but if all or most of those extents are in one segment, you may want to reorg it during a quiet time.     

"andrew" <calbloke_at_yahoo.com> wrote in message news:9256b7c4.0112041644.6cb6b38e_at_posting.google.com...

> I recently ran a query - Select * from user_free_space - which took a
> long time (approx. 7 minutes).
>
> We are using Dictionary managed tablespaces although we are on Oracle
> 8i release 8.1.6 . From what I have seen in this NG, the slowness is
> more than likely due to too many extents in fet$ and/or uet$. Just
> after I ran the query (the only query during my session ), I see the
> following from v$sesstat and v$statname:
>
> cluster scan block gets: 46090398
> cluster scan scans: 92009
>
> fet$ has approx 85000 rows - almost entirely in the TEMP tablspace
> (which is a TEMPORARY tablespace with initial and next extents of
> 20k).
>
> uet$ has approx. 310,000 rows - 250,000 of them in one tablespace
>
>
> So my first question is will this situation affect the performance of
> my database other than when I am doing queries against particular
> tables in the data dictionary ? I know that 250,000 extents in a
> single tablespace soundsis excessive and needs to be fixed , but that
> aside, the amount of rows in these tables (fet$ and uet$) related to
> the TEMP TS seem too high .
>
> My second question is what should be used as first and next extent
> sizes on a TEMPORARY TS ? Our DB has a SORT_AREA_SIZE of 5,000,000
> and 20k as the extent size for TEMP TS ?

-- 
Posted from d225s244.hotbank.com [63.83.225.244] 
via Mailgate.ORG Server - http://www.Mailgate.ORG