query against dba_extents running very slow in 10G
I have an Oracle 10G warehouse database which is quite big in size. I was trying to find out how much space has been consumed in a specific datafile by summing up all the bytes in dba_extents for that file. I used the file_id of the file that is there in dba_data_files. Below is the query that I executed:
select sum(bytes) from dba_extents where file_id=34
This query is taking forever to come out. Even a simple count(*) on this table is taking forever to come out.
Is there anything wrong in my database? Is there any other way that I can get the same information?
Please let me know.
Have you looked on metalink for bugs?
What is the extent size for those tablespaces? Small extent size & large database could mean that there are just a HUGE number of extents to count.
May be due to HASH_JOIN problem.
Originally Posted by rudra_sinha
select /*+ RULE */ sum(bytes) from dba_extents where file_id=34
Note: RULE is deprecated, but you can still test.
Enable 10046 level 8 trace for both queries, and check the plan.
Last edited by tamilselvan; 10-11-2006 at 05:28 PM.
Click Here to Expand Forum to Full Width