query against dba_extents running very slow in 10G

[rudra_sinha]

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.

Regards,

Rudra

[slimdave]

Have you looked on metalink for bugs?

[jhmartin]

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.

[tamilselvan]

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.

Regards,

Rudra

May be due to HASH_JOIN problem.

Try this:
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.

Tamil