query against dba_extents running very slow in 10G
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: query against dba_extents running very slow in 10G

  1. #1
    Join Date
    Oct 2006
    Posts
    1

    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.

    Regards,

    Rudra

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Have you looked on metalink for bugs?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    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.

  4. #4
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Quote Originally Posted by 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
    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
    Last edited by tamilselvan; 10-11-2006 at 05:28 PM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width