Analyzing segment content by block type

Occasionally one might want to know what a segment is made of in terms of block types. For example, you notice that the number of blocks in an index segment is somewhat larger than the number of branch and leaf blocks, and wonder what kind of blocks accounts for the difference. The only way to do this is by dumping index blocks (e.g. as described in Richard Foote’s blog here). Dumping blocks is easy, but analyzing them — not so much. Sure, there exists a plethora of tools that allow to parse text from the OS side (awk, perl, sed and whatnot), but this leads to usual problems: OS access, scripting skills, certain platforms may not have the scripting tool you’re most comfortable with, and even more importantly: scripts cannot do cool stuff that Oracle can (like joining data to other data) . Fortunately, those difficulties can be circumvented by using regexp + external files as I already posted in my blog here. This time, I’d like to show how this technique can be adjusted for index block dumps.

First off, we need to dump all blocks from the segment of interest.

begin
   for rec in (select file_id, block_id start_block, block_id + blocks - 1 end_block from dba_extents where segment_name = '&segname' and owner = '&ownname') loop
       execute immediate 'alter system dump datafile ' || rec.file_id || ' block min ' || rec.start_block || ' block max ' || rec.end_block;
   end loop;
end;
/

That’s it — all segment blocks are now dumped to the trace file (keep in mind that dumped blocks can occupy much larger size than the original segment — e.g. in one of my tests a dump of a 13MB segment took almost 200MB).

All that is left to be done is to create an external table on the top of it, which is just as easy. I do it with the sqlplus script below which runs without any input parameters (works fine on my Windows + Oracle 11g/12c but might require some tweaking on other setups):

column path new_value path
column filename new_value filename

select value path from v$diag_info where name = 'Diag Trace';

select substr(value, (select length(value)+2 filenamestartpos from v$diag_info where name = 'Diag Trace')) filename from v$diag_info where name = 'Default Trace File';

CREATE DIRECTORY TRACEDIR AS '&path';
 
CREATE TABLE RAWTRACEFILE ( TEXT VARCHAR2(4000)) 
  ORGANIZATION EXTERNAL ( 
    DEFAULT DIRECTORY "TRACEDIR" 
    ACCESS PARAMETERS ( 
          RECORDS DELIMITED BY '\n' characterset 'utf8'
    )
    LOCATION ( '&filename')
  );

The finishing touch: query the newly created table:

select block_type, count(*)
from
(
  select regexp_replace(str, '.* type: (.*)$', '\1') block_type
  from
  (
    select text str from rawtracefile where text like '%type:%'
  )
)
group by block_type
order by count(*) desc

and see something like:

0x06=trans data                     1567
0x00=unknown                        69
0x20=FIRST LEVEL BITMAP BLOCK       26
0x21=SECOND LEVEL BITMAP BLOCK      1
0x23=PAGETABLE SEGMENT HEADER       1

i.e. apart from 1567 branch and leaf blocks, there is 69 unformatted (new) blocks, 1 segment header block and 27 blocks contain information about the segment free space (extent bitmap). The last number seems surprisingly high to me — something to get obsessed about for the next few hours. :)

It’s very easy to extrapolate this approach by using a slightly different query (e.g. to examine effects of delayed block cleanout in bulk etc.).

So, to sum up: a few minutes of time, a few lines of code, and you get yourself some entertaining information about what’s inside a database segment!