Oracle Scratchpad

September 16, 2007

Index Rebuild

Filed under: Index Rebuilds,Indexing,Infrastructure,Troubleshooting — Jonathan Lewis @ 10:12 pm BST Sep 16,2007

From time to time I see people asking about Oracle error ORA-01410: invalid rowid. It’s a problem I’ve responded to a few times in newsgroups and presentations, but I realised recently that despite mentioning it casually for several years I’ve never written a specific note about it – so here it is.

There are probably several reasons why you might see Oracle error ORA-01410, including the obvious one that you’ve created a rowid in the front-end code that cannot correspond to a real rowid. However, a much more subtle, yet relatively frequent, cause of the error comes from the habit of using automatic programs to rebuild multiple indexes.

Although most indexes in Oracle can safely be left to keep themselves tidy and efficient some DBAs (possibly many DBAs) have been persuaded that rebuilding indexes regularly is a good thing. Other DBAs are a little more cautious and write programs that attempt to make a sensible decision about which indexes really do need to be rebuilt – and then report suspect indexes rather than rebuilding them.

Even if you fall into the latter group and do manage to identify the few indexes that can benefit from being rebuilt, be careful about letting the rebuilds take place automatically – the timing can be critical.

Consider this: if you rebuild an index (whether or not you use the online option) Oracle will copy the index contents from one location to another and, when the copy is complete, free up the space that held the original index contents.

What’s going to happen to a long running query that started running before you did your rebuild ? Surprisingly the query will carry on running using the “old” index. Oracle introduced a form of ‘cross-DDL read consistency’ many years ago to make partition maintenance operations possible, and that’s the feature that allows your query to carry on running.

But what’s going to happen if some other process now demands the space and puts something into it – for example by creating or extending a table or index. The next time your long-running query hits the “old” index it may find itself looking at a block that contains the wrong type of data for the wrong object id. And so you get Oracle error ORA-01410: invalid rowid.

The effect is easy to demonstrate if you start with a completely empty tablespace (preferably locally managed with uniform extents, though this isn’t an absolute necessity):

create table t1 as
select
	rownum	id,
	rpad(rownum,10)	v1,
	rpad('x',100)	v2
from
	all_objects
where
	rownum <= 10000
;

alter table t1 add constraint t1_pk primary key(id);

execute dbms_stats.gather_table_stats(user,'t1')

declare
	m_v1 varchar2(10);
begin
	for r in (select /*+ index(t1) */ v1 from t1 where id > 0)
	loop
		m_v1 := r.v1;
		dbms_lock.sleep(0.01);

	end loop;
end;
/

All I do is create an indexed table, then run a simple procedure that is forced to walk through the index very slowly.

Now start up a new session for the same user (and you have about 100 seconds to do this)  and execute the following statement:

alter index t1_pk rebuild online;
create table t2 as select * from t1;

The new table t2 should fill the hole left by rebuilding the index t1_pk, and your first session should crash moments later.

The behaviour is much less predictable on production systems, of course. In the real world a random process may simply crash at some random time in the overnight batch once every few weeks for no apparent reason.

Footnote: there is an alternative to ORA-01410 that can appear if you rebuild indexes while running large reporting jobs. If, for example,  your code executes “set transaction read only “ and then runs lots of separate SQL statements, a statement that needs an index that was rebuilt after the “set transaction” call will fail with “ORA-08176: consistent read failure; rollback data not available”.

Update (Jan 2013):

I noticed a relatively recent incoming referral on this item that shows the problem is still happening in 2012 – you might want to read it as an example of how the symptoms can appear.

 

[Further reading on rebuilding indexes]

23 Comments »

  1. Jonathan

    Very nicely written !

    Cheers :)

    Sidhu

    Comment by Sidhu — September 17, 2007 @ 1:45 am BST Sep 17,2007 | Reply

  2. This one just hit the spot, Jonathan.
    Quite timely!

    Comment by Noons — September 17, 2007 @ 2:20 am BST Sep 17,2007 | Reply

  3. Is the new ‘cross-DDL read consistency’ the same feature that allows a select to continue after the table (or partition) on which it is running has been dropped (or exchanged) ?
    It is an interesting thought that the recycle bin in 10g makes less likely that the dropped segment is overwritten soon – even if I don’t think that it will protect the index rebuild.

    Comment by Alberto Dell'Era — September 17, 2007 @ 12:34 pm BST Sep 17,2007 | Reply

  4. @Jonathan

    strange!

    I would expected that Oracle placed a lock on the that table/partition, so no-one can drop/alter/move it.

    More, if I’m rebuilding the index “online”, then I will expected that Oracle is “self consistent” (i.e. Oracle build a separate copy of the index before “switching” the original with the rebuild one).

    Well, a “strange” thing indeed! ^_^

    Comment by Antonio — September 17, 2007 @ 3:18 pm BST Sep 17,2007 | Reply

  5. [...] unknown wrote an interesting post today onHere’s a quick excerptFrom time to time I see people asking about Oracle error ORA-01410: invalid rowid. It’sa problem I’ve responded to a few times in newsgroups and presentations, but I recently realised that despite mentioning it casually for several … [...]

    Pingback by Mike’s Musings » Index Rebuild — September 17, 2007 @ 3:35 pm BST Sep 17,2007 | Reply

  6. @Antonio

    I believe, Oracle tries to avoid this kind of lock for the same reason why there is multi version read consistency: increased throughput. That’s one of the features that sets Oracle apart from other DB products.

    Also, Oracle *is* self consistent and it *does* the switch as you request. But from what I understand the ORA-01410 is similar in nature to ORA-01555 (snapshot too old), i.e. Oracle tries to do the job as good as possible but if resources are exhausted you get an error.

    Comment by Robert — September 19, 2007 @ 8:41 am BST Sep 19,2007 | Reply

  7. @Robert

    got your point.

    But…here we are seeing a “consistent read failure” (ORA-08176) and a invalid rowid (ORA-01410). It sound like: Oracle says: OMG! where the rows gone?

    So, we got read consistent problems: Oracle rebuild the index and generate new rowids (only for the index probably), so the running session (for..loop) find itself with a bunch of old refs (i.e. no more usable ones).

    I think is a bit different than “snapshot too old”, it’s more like: who moved my cheese? ^_^

    Comment by Antonio — September 19, 2007 @ 10:13 am BST Sep 19,2007 | Reply

  8. Antonio,

    I don’t understand the requirment about locking a table/partition so it can’t be moved. I don’t move a table. (Although I do copy the t1 table as an easy way of filling a hole in the tablespace – but the choice of thing to copy has nothing to do with the issue).

    When rebuilding (online, or “offline”) Oracle is self-consistent – the new version of the index is not made available until it is complete, the old version of the index is not decommissioned until the new index is available.

    The problem occurs because some processes are allowed to believe that a decommissioned index is still usable and try to keep on running.

    Comment by Jonathan Lewis — September 19, 2007 @ 11:29 am BST Sep 19,2007 | Reply

  9. @Jonathan

    The requirement about locking is to avoid the problem that arise here: if someone is using an object, I can not move/rebuild (“steal” :) ) that object.

    You say: the problem arise because some processes believe that the index is still OK, they believe to a “lie”.

    I say: Oracle doesn’t track that someone else is still using an object and so I should not modify it.

    Probably is “only” a design issue. That’s all!

    Comment by Antonio — September 19, 2007 @ 11:51 am BST Sep 19,2007 | Reply

  10. Antonio,
    Try this:
    Session 1: Create the table t1 and index.
    Session 2: Set transaction read only;
    Session 1: Rebuild the index and create the second table.
    Session 2: Query table t1 through the index.

    Session 2 crashes with ORA-08176.

    It wasn’t using the index when the rebuild started – so there’s no clue for session 1 that the index should not be rebuilt.

    Comment by Jonathan Lewis — September 19, 2007 @ 1:02 pm BST Sep 19,2007 | Reply

  11. Jonathan,

    Nice summary, and a very nice demo/test case.

    I’ve never run across an ORA-8176 before, and my first thought was “Huh? Why not ORA-1555?” But after reading the output of ‘oerr ora 8176′, it’s fairly self-explanatory. 1555 means the rollback has been overwritten, 8176 means the required rollback never existed.

    -Mark

    Comment by Mark Bobak — September 20, 2007 @ 4:26 am BST Sep 20,2007 | Reply

  12. @Jonathan

    O_O

    I was thinking of rebuild while someone is reading.

    The new example leave me with no words.

    Comment by Antonio — September 20, 2007 @ 12:22 pm BST Sep 20,2007 | Reply

  13. Thanks Jonathan for your article. It pretty much sums up the problems that i’m having in production, because of a software that rotates partitions everyday and rebuilds indexes while other jobs are running queries on the same db. It looks like there’s no perfect solution for this problem.

    Comment by Ana — November 16, 2007 @ 11:12 am BST Nov 16,2007 | Reply

  14. Ana,

    Yours is a case where you could look at the option of having two tablespaces for the ‘rotating’ index partitions. Start with the indexes in tablespaceA, rebuild to tablespaceB one day, then back to tablespaceA the next.

    Unfortunately you won’t be able to move all indexes instantaneously, so you will still run the risk of one index extending into the space just left by a other index – but the strategy should reduce the probability of getting the ORA-01410.

    The trouble is, the code can be a bit messy, and if it’s third-party it may be impossible to get it changed.

    Comment by Jonathan Lewis — November 21, 2007 @ 7:38 am BST Nov 21,2007 | Reply

  15. [...] Jonathan Lewis @ 9:12 am UTC Feb 9,2008 I’ve written a few notes about the pros and cons of index rebuilds in the past. A comment on Richard Foote’s blog describes a bug in 10g (reminiscent of an old [...]

    Pingback by Index Rebuild 10g « Oracle Scratchpad — February 9, 2008 @ 9:12 am BST Feb 9,2008 | Reply

  16. [...] A little-known threat from index rebuilds [...]

    Pingback by Index Efficiency « Oracle Scratchpad — May 30, 2008 @ 5:09 pm BST May 30,2008 | Reply

  17. [...] Index Rebuild [...]

    Pingback by Index analysis « Oracle Scratchpad — October 9, 2008 @ 11:41 am BST Oct 9,2008 | Reply

  18. Excellent article !!!
    You are absolutely bang on Target. Thank you very much.

    -Dillip Nayak

    Comment by Dillip Nayak — July 12, 2011 @ 4:39 pm BST Jul 12,2011 | Reply

  19. Sir,
    I daily reads your blog as I reads docs. If you allow me to give a little request, if possible can you please add “See also” for all your blog entries with co-related links, so that newbie readers like me, can found it much more helpful. Please ignore this request, if someone have already posted and/or not possible.

    Best Regards
    Girish Sharma

    Comment by Girish Sharma — January 24, 2012 @ 10:40 am BST Jan 24,2012 | Reply

    • Girish,
      If you check the top line of each entry you will see (in red) a list of categories for the entry. If you pick a category that is the closest match to the particular feature of the blog item that interests you then you will get a list of the related items that I have written. I usually put a “fold line” in each item a few weeks after writing it so that this listing give you the first few lines from each item.

      Comment by Jonathan Lewis — January 26, 2012 @ 11:13 pm BST Jan 26,2012 | Reply

  20. Sir,
    Thank you very much for your reply.

    Best Regards
    Girish Sharma

    Comment by Girish Sharma — January 28, 2012 @ 1:18 pm BST Jan 28,2012 | Reply

  21. Dear Jonathan,

    Interesting post as always.
    I checked out the ORA-08176 at the URL I have given.

    Kind Regards,

    Andrew

    Comment by Andrew Reid — April 9, 2013 @ 7:45 pm BST Apr 9,2013 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 3,877 other followers