Treedump – 2

In an earlier article about investigating the state of an index in detail I supplied a piece of SQL that would analyse an index (no, not using the Analyze command) and summarise the number of entries in each leaf block that currently held any entries at all. Here’s a sample of the type of output it produced:

ROWS_PER_BLOCK     BLOCKS TOTAL_ROWS RUNNING_TOTAL
-------------- ---------- ---------- -------------
             1       8706       8706          8706
             2       4830       9660         13536
             3       2953       8859         16489
             4       1648       6592         18137
             5        997       4985         19134
             6        628       3768         19762
             7        300       2100         20062
             8        162       1296         20224
             9         87        783         20311
            10         52        520         20363
...
           227        100      22700         31871
           228        111      25308         31982
           229         84      19236         32066
           230         97      22310         32163
           231         77      17787         32240
           232         61      14152         32301
           233         54      12582         32355
           234        529     123786         32884
               ---------- ----------
sum                 32884    1887595

This is useful of course as it tells you a lot about the way that space is used in the index – but it only tells you how many blocks are in any given state, it doesn’t tell you whereabouts in the index those blocks are. It would be really nice if you could “draw a picture” of the index, showing where the gaps were and where it was densely packed.

I was thinking about this a few weeks ago and suddenly realised that it was a simple problem. Oracle gives you exactly the tools you need. I was travelling back from a client site to London and the  train ride was only an hour long so it gave me just enough time to generate a quick test to demonstrate the principle. ***

We start with an article on the treedump command, which I published a little while ago. This dumps a tracefile describing your index, one line per block of the index in correct index order.

----- begin tree dump
branch: 0x40035a 4195162 (0: nrow: 3, level: 2)
   branch: 0x40e2c0 4252352 (-1: nrow: 312, level: 1)
      leaf: 0x40035b 4195163 (-1: nrow: 240 rrow: 240)
      leaf: 0x40215f 4202847 (0: nrow: 218 rrow: 218)
      leaf: 0x40035c 4195164 (1: nrow: 444 rrow: 444)
      leaf: 0x40035d 4195165 (2: nrow: 444 rrow: 444)
       ... etc ...

This is the key to the problem; all you have to do is read the trace file and you’ve got all you need to know – except that it’s one line per leaf block, and that’s a lot of reading for a large index and that will make it hard to spot interesting patterns.

But it’s a trace file – and we can read trace files from SQL by making use of external tables; [update: but see this note for a more modern option for reading trace fles] and we can collapse data down to a more reasonable size by using analytic functions like the ntile() function. So here’s a little script that puts a few simple pieces together and comes up with a fairly useful tool.

rem
rem     Script:         read_treedump.sql
rem     Dated:          Feb 2010
rem     Author:         Jonathan Lewis
rem     Purpose:        Using external files to analyze an index.
rem
rem     Last tested:
rem             10.2.0.3
rem     Not tested
rem             11.2.0.1
rem             11.1.0.7
rem              9.2.0.8
rem     Not relevant
rem              8.1.7.4        -- no external tables
rem
rem     Notes:
rem     See read_trace.sql for notes on reading trace files.
rem
rem     Required Privileges:
rem     grant create any directory to the user.
rem

connect test_user/test

start setenv
set timing off

execute dbms_random.seed(0)

set echo on
drop table t1;
set echo off

begin
        begin           execute immediate 'purge recyclebin';
        exception       when others then null;
        end;

        begin
                dbms_stats.set_system_stats('MBRC',8);
                dbms_stats.set_system_stats('MREADTIM',26);
                dbms_stats.set_system_stats('SREADTIM',12);
                dbms_stats.set_system_stats('CPUSPEED',800);
        exception
                when others then null;
        end;

        begin           execute immediate 'begin dbms_stats.delete_system_stats; end;';
        exception       when others then null;
        end;

        begin           execute immediate 'alter session set "_optimizer_cost_model"=io';
        exception       when others then null;
        end;
end;
/

--      ------------------------
--      Create a table and index
--      ------------------------

define m_size=100000

create table t1
nologging
as
with generator as (
        select  --+ materialize
                rownum id
        from dual
        connect by
                rownum <= 10000
)
select
        rownum                  id,
        lpad(rownum,10,'0')     vc_id
from
        generator       v1,
        generator       v2
where
        rownum <= &m_size
;

create index t1_i1 on t1(vc_id) nologging;

--      ---------------------------------------------------------
--      Create a "FIFO" pattern of space in the index leaf blocks
--      ---------------------------------------------------------

delete from t1
where
        id between 1 and 0.5 * &m_size
and     mod(id,100) != 0
;

commit;

delete from t1
where
        id between  0.5 * &m_size and 0.75 * &m_size
and     mod(id,50) != 0
;

commit;

delete from t1
where
        id between  0.75 * &m_size and 0.875 * &m_size
and     mod(id,25) != 0
;

commit;

delete from t1
where
        id between  0.875 * &m_size and 0.9375 * &m_size
and     mod(id,12) != 0
;

commit;

delete from t1
where
        id between  0.9375 * &m_size and 0.96875 * &m_size
and     mod(id,6) != 0
;

commit;

--      -------------------
--      Dump the index tree
--      -------------------

column  object_id new_value m_object_id

select
        object_id
from
        user_objects
where
        object_name = 'T1_I1'
;

alter session set events 'immediate trace name treedump level &m_object_id';

--      -------------------------------
--      Now sort out the trace filename
--      It's easier in 11g
--      -------------------------------

column spid new_value m_spid

select
        trim(pro.spid)  spid
from
        v$process       pro,
        v$session       ses
where
        pro.addr = ses.paddr
and     ses.sid  = (
                select  sid
                from    v$mystat
                where   rownum = 1
        )
;

define m_filename = 'd10g_ora_&m_spid..trc'
prompt Trace file name: &m_filename

--      ------------------------------------------
--      Set up an external table to read the trace
--      ------------------------------------------

define m_file_dir = 'c:\oracle\admin\d10g\udump'
define m_log_dir  = 'c:\working';

create or replace directory ext_tab as '&m_file_dir';
create or replace directory log_dir as '&m_log_dir';

drop table ext;

create table ext(text_line varchar(4000))
organization external
(
        type oracle_loader
        default directory ext_tab
        access parameters (
                records delimited by newline
                characterset us7ascii
                discardfile     log_dir:'read_trace_dis.txt'
                badfile         log_dir:'read_trace_bad.txt'
                logfile         log_dir:'read_trace_log.txt'
                fields
                        rtrim
                        reject rows with all null fields
                (
                        text_line       (1:4000)        char
                )
        )
        location ('&m_filename')
)       reject limit unlimited
;

--      -----------------------------------------------------
--
--      Read the treedump in order, selecting only lines with
--      "leaf" in them.
--      branch: 0x140020a 20972042 (0: nrow: 307, level: 1)
--         leaf: 0x140020b 20972043 (-1: nrow: 326 rrow: 3)
--         leaf: 0x140020c 20972044 (0: nrow: 326 rrow: 3)
--
--      Use instr() to get the text following the last ": ",
--      and select rownum to get an ordering on the lines.
--
--      Use substr() and length() to trim off the final ")"
--      then turn the result into a number and select ntile()
--      to collect the numbers into ordered batches.
--
--      Finally sum the batches - I've used 50 tiles because
--      that's a page length of numbers, you could use 100 or
--      more if you selected the result into a spreadsheet to
--      graph the results.
--
--      -----------------------------------------------------

spool read_treedump

set pagesize  60

select
        section,
        sum(leaf_row_count)     row_count
from
        (
        select
                ntile(50) over (order by rn)    section,
                to_number(
                        substr(
                                text_line,
                                1,
                                length(text_line)-1
                        )
                )       leaf_row_count
        from
                (
                select
                        rownum rn,
                        substr(
                                text_line,
                                instr(text_line,':',-1) + 2
                        )                       text_line
                from
                        ext
                where
                        instr(text_line,'leaf') != 0
                )
        )
group by
        section
order by
        section
;

spool off

If you feel like making more of it, you could probably turn it into a pipe-lined function taking the index schema and name as an input, together with the number of tiles, and returning the data set. You’d need to customise the code to identify the trace file location, and probably have a fixed setup for directories rather than creating them on the fly in the function. Once you’ve done this you could then set up a spreadsheet with a ‘select from table_function()’ call and pull the data straight into a chart.

Here’s the output I got from this demonstration case. As you can see, it shows that the index starts with a great long tail that is thinly populated and that a large fraction of the data is packed into the last 10% of the index, with most of that being in the right-hand 4% of the index.

   SECTION  ROW_COUNT
---------- ----------
         1         22
         2         23
         3         23
         4         23
         5         23
         6         22
         7         23
         8         20
         9         19
        10         20
        11         19
        12         20
        13         20
        14         19
        15         20
        16         19
        17         20
        18         19
        19         20
        20         20
        21         19
        22         20
        23         19
        24         20
        25         31
        26         39
        27         39
        28         40
        29         39
        30         39
        31         39
        32         39
        33         39
        34         39
        35         39
        36         39
        37         40
        38         71
        39         78
        40         78
        41         79
        42         78
        43         78
        44        115
        45        163
        46        163
        47        201
        48        326
        49       1368
        50       1874
           ----------
sum              5665

Reminder: a treedump walks the index in order, reading one block at a time – this can be a lot of work and take a lot of time. There are also versions where it does a full symbolic block dump for every leaf in the index, so test it on a very small index before trying to dump a large index.

*** Footnote:

Like many of the scripts I’ve published on this blog, the SQL shown here is “good enough” for me to use because I know how it works and I won’t have to use it often. It’s not intended to be a bullet-proof program in a “productised” state.

Update (Dec 2010)

(See comment 7 below) each line of the treedump is about 75 bytes long – so the whole trace file will be in the region of (75 * leaf_blocks / 1048576)MB in size. Make sure that you can dump a trace file that size before you start.  Check the parameter max_dump_file_size.