Oracle Index Tree Dump Visualisation
I’ve been working on a Processing project to visualise Oracle Index Tree Dumps. This is a quick example of the progress. It’s still very much a work in progress, so there are problems to address.
For the test table I’m just copying the dba_objects view into a table.
SQL> select count(*) from dba_objects; COUNT(*) ---------- 68913 SQL> create table test_o as select * from dba_objects; Table created. SQL> create index test_o_idx on test_o(object_id); Index created.
Next I get the treedump as follows:
SQL> select object_id 2 from dba_objects 3 where object_name = 'TEST_O_IDX'; OBJECT_ID ---------- 71227 SQL> alter session set events 'immediate trace name treedump level 71227'; Session altered.
Plugging the treedump into my visualisation tool (as I said, it’s a work in progress) gives this view. A very simple tree consisting of only a root node (red) and leaf nodes (clustered around the yellow core).
Now I delete 20000 rows from the test_o table:
SQL> delete from test_o where object_id between 40000 and 60000; 19565 rows deleted. SQL> alter session set events 'immediate trace name treedump level 71227';
Loading the new treedump shows us where leaf nodes have been deleted.