Oracle Scratchpad

August 26, 2009

Index Explosion 3

Filed under: Index Explosion,Indexing,Infrastructure,Troubleshooting — Jonathan Lewis @ 6:53 pm BST Aug 26,2009

At the end of part 2 of Index Explosion I left you with three questions: 

  • how can you set maxtrans on an index in 10g (and above)
  • what strategies exist for minimising the potential for damage if you actually have to face this problem
  • why haven’t I noticed the space-wasting phenomenon more often despite seeing indexes where it could have been happening

In this post I’ll address the maxtrans issue. If you try to set maxtrans on an index in 10g it simply doesn’t work, and here’s a quick cut-n-paste from SQL*Plus running to demonstrate the point:

SQL> create index ord_placed on orders(date_placed)
  2  tablespace test_8k
  3  initrans 9
  4  maxtrans 9
  5  /

Index created.

SQL> select ini_trans, max_trans
  2  from user_indexes
  3  where index_name = 'ORD_PLACED'
  4  /

---------- ----------
         9        255

 Of course if you’re still running 9i (or 8i), you’ll see max_trans showing the value you set, but 10g silently ignores your attempt to set maxtrans and leaves it at 255. If you really need to set maxtrans in 10g, and if you get approval from Oracle support, you can do something like the following (again, a cut-n-paste from SQL*Plus):

SQL> define m_maxt = 9
SQL> column     object_id       new_value m_objn
SQL> column     data_object_id  new_value m_objd
SQL> select
  2     object_id,
  3     data_object_id
  4  from
  5     user_objects
  6  where
  7     object_name = 'ORD_PLACED'
  8  and        object_type = 'INDEX'
  9  ;

---------- --------------
    100466         100466

1 row selected.

SQL> execute sys.fix_index_maxtrans( &m_objn , &m_objd, &m_maxt )

PL/SQL procedure successfully completed.

SQL> select ini_trans, max_trans
  2  from user_indexes
  3  where index_name = 'ORD_PLACED'
  4  /

---------- ----------
         9          9

1 row selected.

The trick (and the requirement to talk to Oracle support) is in the procedure sys.fix_index_maxtrans(), which I created in the sys schema as follows:

create or replace procedure fix_index_maxtrans (
	i_objn in number,
	i_objd in number,
	i_maxt in number

	execute immediate 'alter system flush shared_pool';
	execute immediate 'alter system flush shared_pool';

		maxtrans = i_maxt
		obj#	= i_objn
	and	dataobj# = i_objd

	execute immediate 'alter system flush shared_pool';
	execute immediate 'alter system flush shared_pool';


There is an oddity in this procedure – you’ll notice I’ve flushed the shared_pool twice before and after updating the data dictionary. The flushes are there in the first case because Oracle sometimes updates the data dictionary from the dictionary cache; I then repeat them because there is some evidence that some items in the library cache don’t get flushed properly the first time around in some releases of Oracle. The total number of flushes may be unnecessary – but this hack for maxtrans isn’t something that you have to do often – so you may be prepared to live with the disruption.

Once the maxtrans is set it seems to persist even across an index rebuild (if you ever feel the need to use the rebuild command on an index, that is) – but if you want to take advantage of this approach, I’ll leave you to test all the boundary conditions and special cases (e.g. partitioned indexes, etc.) for yourselves.

I’ll address the other two questions in later posts but, as a brief introdution to other options for addressing the issue, here’s a list of four (or possibly five, depending on your point of view) which may be appropriate.

  • Use a coalesce when necessary – possibly on a very regular basis
  • Create the index as a reverse-key index
  • Create the index as global hash partitioned index
  • Get rid of (or change) the index

Each option has its good points, bad points, advantages and limitations – which will be the topic of discussion in the next installment.

[Further reading on Index ITL Explosion]


  1. Jonathan,
    I didn’t get the reason for the 2 flushes of shared pool: You argued, in some versions it will not flush the library cache properly at the 1st time. But, you only want to avoid the dictionary cache to write back to ind$. How is this affected by library cache? I fear, your steps are too big and I missed the trace. Maybe you can add an additional guidepost for me?
    thank you,

    Comment by Martin Berger — August 28, 2009 @ 6:33 am BST Aug 28,2009 | Reply

    • Martin,

      Sorry, I was a little sloppy with language. The “flush shared_pool” command probably deserves to be changed to “flush sga_heap”, as it attempts to clear various structures in the sga heap, including the library cache and dictionary cache.

      Since the flush command doesn’t always do the job properly on the library cache, I take the pessimistic viewpoint that it might not work perfectly on the dictionary cache as well.

      It’s quite possible that none of the flushes is necessary, and a little more likely that just the first is sufficient.

      Comment by Jonathan Lewis — August 29, 2009 @ 3:08 pm BST Aug 29,2009 | Reply

  2. […] the Oracle Scratchpad, Jonathan Lewis has the third part of his Index Explosion series, this time answering the quesiton, “How can you set maxtrans on an index in 10g (and […]

    Pingback by Log Buffer #159: a Carnival of the Vanities for DBAs | Pythian Group Blog — August 28, 2009 @ 5:05 pm BST Aug 28,2009 | Reply

  3. […] maxtrans. (From 10g, Oracle has stared to ignore the setting of maxtrans, and this can cause some space wastage problems in indexes). The initial size of the ITL is set by initrans- with a minimum setting that is dependent on the […]

    Pingback by Glossary « Oracle Scratchpad — February 20, 2010 @ 6:50 pm BST Feb 20,2010 | Reply

RSS feed for comments on this post.

Leave a Reply

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

You are commenting using your 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 )

Google+ photo

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

Connecting to %s

Powered by