How to find the number of extents in the database

In my 10g environment your command runs in about 5 seconds -- clearly you have some other issues here.  The most obvious bullets are to ensure you have analyzed both your system tables and your schema tables to ensure CBO efficiency.  I trust you have reviewed your alert and other trace logs for errors.  Also, that your tablespaces were resized in your migration to the recommended specs.

How may we help further?

The reason for the time difference between 8 and 10, is the difference between dictionary managed and locally managed tablespaces.

With dictionary managed tablespaces, which is probably what you have in the 8 database, all the information you need is in the data dictionary.  With locally managed tablespaces, which is what you have in the 10 database, each datafile needs to be touched to find the information.

I have run across this as well, and the only solution I have come up with is to create a temporary table that is the results of the DBA_EXTENTS view (i.e. create table ext_tmp as select * from dba_extents) then index it the way you need it.  This is especially usefull and a huge time saver if you need to run multiple queries against the view.

What information are you really looking for?  If it is number of extents per object, then DBA_SEGMENTS should hold that information.  It probably is not going to be faster than DBA_EXTENTS, but if you create your temporary table off of DBA_SEGMENTS, then it will be much smaller and faster to look up data.

johnsone,  I agree LMT's will be slower to query than DMT's  for exactly the reason you specify.

However, I don't see how that relates to an upgrade?

You can still have DMT's in 10g.  I'm the unfortunate recipient of an 8i db upgraded to 10g and it still has DMT's everywhere.  Merely doing the ugprade won't change them.

However,  "hopefully" all of the 10g tablespaces are LMT's, and "hopefully" they were LMT's in 8i too.
The slowdown in querying extent info is unfortunate, but everything else about them is so much better.

And I agree,  if you'll be doing lots of queries for extent info,  then consider a materialized view on DBA_SEGMENTS and/or DBA_EXTENTS.

Do we know how the upgrade was done?  Were the DMTs converted to LMTs during the upgrade (this is the recommended approach).  Also, depending on exact versions, direct upgrade from 8 to 10 is not possible and recreating the database then using exp/imp would be the only way.

No,  and I'm not disputing anything you said.  It just seemed like a jump to me to declare LMT's to be the problem without more info.

I'll wager you're probably right though.  :)

Thanks for the input!  Let me clarify some things
In Oracle 8 we were in DMT now in Oracle 10 we are LMT.
The upgrade was a full EXPORT of 8 and an import into 10.

sdstuber - your command ;

SELECT /*+ RULE */MAX (EXTENT_ID),SEGMENT_NAME FROM DBA_EXTENTS
WHERE OWNER LIKE 'OWNER_NAME%' AND EXTENT_ID >0 GROUP BY
SEGMENT_NAME ORDER BY MAX (EXTENT_ID) DESC;

 ran in under 1 minute.

any other thoughts?

you probably do need to collect stats on the sys tables then.

the cost optimizer will run more efficiently then and you won't have to force RULE.

LMT's will still be slower than DMT's though, for exactly the reasons johnsone detailed so nicely.

Even with statistics be aware that if you are running the query many times, it may be faster to go the temp table or materialized view route.

I have a database that is just under 1T, and if you run it once, it is managable, but any more than that it is not.  A temp table with indexing is much faster.

thx

glad we could help

You might want to consider a split with johnsone, he had good input on this as well that seemed relevant to your particular situation.