1

I am using below query to fetch the memory occupied by table.

SELECT SUM(bytes), SUM(bytes)/1024/1024 MB
    FROM dba_extents
    WHERE owner = :Owner
    AND segment_name = :table_name;

It took 986 seconds (16 minutes) to print the size of 2 MB file.

What's wrong with this query?

Is there any better query which provides same data very fast?

miracle_the_V
  • 1,006
  • 1
  • 14
  • 31
Ravindra babu
  • 37,698
  • 11
  • 250
  • 211

2 Answers2

3

Slow access to the DBA_EXTENTS seems to be a common issue. Here's a quote:

The main performance problem here however is that the values of the BYTES, BLOCKS & EXTENTS columns of the DBA/USER/ALL_SEGMENTS and DBA/USER/ALL_EXTENTS views are not stored in the Data Dictionary for segments that reside in Locally Managed Tablespaces. Instead they must be obtained by accessing the Segment Header Blocks for all segments processed in the query. These extra block accesses cause the slow performance. So with increasing number of database files and segments the query performance in this case can potentially degrade. Furthermore Oracle in certain circumstance does not cache properly those segment header blocks, thus forcing reading them from disk even at the next query run – so no caching happening. Do not be surprised if I say that a query against dba_segments can trigger even at the background a few more procedures from DBMS_SPACE_ADMIN package to get correct segment sizing information.

Link also contains a solution for this, which I can't apply and test because I have no database of my own now. But I believe you've got the idea.

miracle_the_V
  • 1,006
  • 1
  • 14
  • 31
2
SELECT SUM(bytes), SUM(bytes)/1024/1024 MB
FROM DBA_SEGMENTS
WHERE owner = :Owner
AND segment_name = :table_name;

There is far less segments than extents but it's strange. How many extents has your table?

And just FYI this is just non-partitioned table data. And if the table has indexes, LOBs or object types it's not the entire space it requires.

Husqvik
  • 5,669
  • 1
  • 19
  • 29