Oracle Scratchpad

May 21, 2007


Filed under: Oracle,Troubleshooting — Jonathan Lewis @ 9:49 pm BST May 21,2007

How do you discover the source of a problem ? It’s not an easy thing to teach, but here’s a recent example someone sent me that may give you a clue. This is a question that came to me by email:

How can an index btree_space be greater than dba_segment.bytes (or dba_segment.blocks * blocks_size). Isn’t dba_segment.bytes the total space allocated for the segment ?

SQL> analyze index EPIMART.INDIVIDUAL_BN_COM_0_B_I1 validate structure; 
SQL> select btree_space from index_stats;            


SQL>  select bytes from dba_segments 
  2  where segment_name = 'INDIVIDUAL_BN_COM_0_B_I1' 
  3  and owner = 'EPIMART' and segment_type = 'INDEX';            


There is clearly something a little odd going on here – but (as Greg House M.D. says): “the patient knows something he’s not telling us.” So I asked for the complete rows from index_stats, dba_segments and dba_indexes.

In this case, we have a clear discrepancy between two numbers that should be the same, so we need to check the entire content of the two relevant rows, to see if either contains an internal contradiction. We also need all the details about the index definition just in case the creation of the index – compression, partitioning etc. – gives us a clue.

This is what came back (although I’ve stripped the output back to just the few relevant columns shown). I wasn’t sent the details from dba_indexes:

SQL> analyze index EPIMART.INDIVIDUAL_BN_COM_0_B_I1 validate structure; 
SQL> select {columns} from index_stats;          

---------- ---------- ---------- ----------- 
     39936      39727      16188   643992776        

SQL> select {columns} from dba_segments 
  2  where segment_name = 'INDIVIDUAL_BN_COM_0_B_I1' 
  3  and segment_type = 'INDEX' 
  4  and owner = 'EPIMART';          

---------- ---------- ---------- -------------- ----------- ------------ 
 148897792       9088        624        1048576     1048576            0 

You’ll notice that in index_stats the blocks and lf_blocks are self-consistent, and with a block size of 16KB (indicated by the lf_blk_len) the blocks and btree_space are self-consistent – so index_stats is (probably) showing good information.

Looking at dba_segments, we can see 1MB uniform extent sizing (initial_extent, next_extent, pct_increase) with a total of 624 extents. That’s 624MB – which is consistent with the btree_space in index_stats.

But the bytes and blocks shown in dba_segments are not consistent with the extents, even though they are consistent with each other (which is not surprising as the view defines the bytes as blocks * blocksize). Somehow, something is setting the block count incorrectly in the table underlying dba_segments.

So I sent a note back to the originator pointing out where the error had to be, and a few days later they sent me a reply to say that they had used Metalink to discover bug 4771672 (fixed in, and 11g) which says:

“If an index is created in parallel, the number of extents is reported differently on DBA_EXTENTS and DBA_SEGMENTS. This is happening only for larger indexes.”


Use DBA_EXTENTS to calculate number of blocks used.

Use TABLESPACE_FIX_SEGMENT_EXTBLKS after creating an index in parallel to correct the dictionary.

If they had sent me the information I had originally asked for (i.e. dba_indexes as well) it’s possible that I would have spotted a connection between the degree of parallelism and the error in the block counts – I did wonder briefly if they had created this index parallel four (after seeing the Metalink note) but I never got around to asking.

It’s surprising how much “extra” information you have to ask for to be able to answer even “quick questions”.


  1. Jonathan:

    It seems (yet) another case of DBA_SEGMENTS and DBA_EXTENTS showing different numbers.

    I tend to examine spaces out of DBA_EXTENTS instead of DBA_SEGMENTS. I’ve noticed the latter is more accurated in many cases (am I wrong?)



    BTW: I’m a fan of House M.D. too ;-)

    Comment by carlosal — May 22, 2007 @ 7:57 am BST May 22,2007 | Reply

  2. Carlos, it’s not really something I do often enough to have realised there was a problem. If I do need to look at space usage, I probably check for the last_analyzed date on dba_tables and then check the blocks figure.

    Comment by Jonathan Lewis — May 22, 2007 @ 8:39 pm BST May 22,2007 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Powered by