What is impact of too many rows in fet$ and uet$ 
Author Message
 What is impact of too many rows in fet$ and uet$

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 ?



Sun, 23 May 2004 08:44:26 GMT
 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 {*filter*} 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.


Quote:
> 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.***.com/


Sun, 23 May 2004 10:15:13 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. Role of sys.uet$ and sys.fet$ in space management

2. Oracle 8 fet$ table questions

3. SYS.UET$ mutating table

4. SYS.UET$ table error

5. LOCKING ON THE UET$ TABLE

6. i am facing a problem with row level locking

7. Which row am I in?

8. DataGrid- What row am I in ??

9. I am getting this message when i am tring to export or import anything using

10. error ORA-01855: AM/A.M. or PM/P.M. required

11. Busy Day = Slowdown from 12 AM - 5 AM


 
Powered by phpBB® Forum Software