Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> 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 ? Received on Tue Dec 04 2001 - 18:44:26 CST