Oracle Scratchpad

November 27, 2006

Object IDs

Filed under: Infrastructure — Jonathan Lewis @ 4:16 pm BST Nov 27,2006

If you’ve read my note on free buffer waits you will have seen a comment that, in x$bh, objects with obj (data object id) values in the region of 4 billion were undo segments. There is another numbering anomaly you need to be aware of. 

Objects with obj value in the region of N * power(2,22) – i.e. multiples of roughly 4 million, are the physical instantiation of global temporary tables (GTTs) and their indexes. Oracle cannot use a real data_object_id as there isn’t one and several sessions may be using their own private copies of the same global temporary table definition).

For global temporary tables and their indexes obj is derived from the file id and block number of the start of the extent used to hold the data. Specifically, the obj value is power(2,22) * tempfile id + block number.

This leads to a worrying thought. I have already seen sites which regularly, and frequently, do DDL against partitioned tables; and a couple of sites I have visited are already using data_object_ids in the range of 1.2M to 1.8M – what happens when a real data_object_id hits the critical power(2,22) limit ? Is there any code in the buffer handler that will automatically treat it as a temporary object ?

12 Comments »

  1. My client is at 2.7 million, so this is a very interesting and relevant topic :)

    >> Is there any code in the buffer handler that will automatically treat it as a temporary object ?

    Can you speculate as to possible signs/symptoms of that, Jonathan? I’m inclined to do some pre-emptive testing, if possible.

    Comment by David Aldridge — November 27, 2006 @ 7:02 pm BST Nov 27,2006 | Reply

  2. David, nothing concrete to go on; it was just a thought that crossed my mind when I saw the (relatively) low obj values.

    I’d watch out for things like early dumping from the buffer cache, unexpected direct path writes, data dictionary corruption due to object id overflow, data loss on session termination.

    Primarily, in your position I’d contact Oracle Support right away and ask them what happens when your data_object_ids hit the 4M mark.

    Comment by Jonathan Lewis — November 27, 2006 @ 10:09 pm BST Nov 27,2006 | Reply

  3. Ok, that would be fun….

    I setup script with truncate and it will run for about 34 hours before it will reach data_object_id > 4194304. I’ll post my findings here :)

    Comment by Alexander Fatkulin — November 28, 2006 @ 12:45 am BST Nov 28,2006 | Reply

  4. >> Primarily, in your position I’d contact Oracle Support right away and ask them what happens when your data_object_ids hit the 4M mark.

    Done. I’ll report back on Oracle’s feedback. Thanks Jonathan.

    Comment by David Aldridge — November 28, 2006 @ 2:59 pm BST Nov 28,2006 | Reply

  5. Hey David, Jonathan if you hear of any responses from Oracle can you post it on either of your blogs?

    tnx.

    Comment by Dave Best — November 28, 2006 @ 4:46 pm BST Nov 28,2006 | Reply

  6. Sorry to post twice but check note 262391.1 on metalink which mentions the max number of objects is 4294967295.. Or have I misunderstood the issue?

    Comment by Dave Best — November 28, 2006 @ 4:53 pm BST Nov 28,2006 | Reply

  7. Dave,
    The maximum number of objects may indeed be ca. 4 billion, but the problem is that in x$bh the (data_)object_id of a temporary object can be in the region of 4 million (and multiples thereof); which means that a real object could collide with a temporary object on its object id.

    In fact, having had a little time to think about it, one critical case in 10g that may cause an issue is that x$bh has a new linked list structure which links buffer headers for the same object number so that a truncate or drop command can locate clean buffers for an object in the buffer cache and mark the buffers as free.

    So what’s going to happen if a real object and a temporary object have the same obj value ? Might the buffers for a real object get linked to the buffers for a temporary object with the result that when a session ends (and releases the temporary object) all the buffers for the real object are marked as free at the same time that the buffers for the temporary object are freed ?

    Comment by Jonathan Lewis — November 28, 2006 @ 10:48 pm BST Nov 28,2006 | Reply

  8. Dave, yes I’ll post back with Oracle Support’s recommendations.

    Comment by David Aldridge — November 29, 2006 @ 3:38 am BST Nov 29,2006 | Reply

  9. that will be fun!
    SQL> select count(*), max(DATA_OBJECT_ID) from dba_objects;

    COUNT(*) MAX(DATA_OBJECT_ID)
    ———— ——————-
    28370 2958553

    Comment by dba — December 1, 2006 @ 1:14 pm BST Dec 1,2006 | Reply

  10. Well, i hadn’t notice any problems at all so far…

    I was able to reach data_object_id of 4197129 with permanent table segment. And even crossed real object_id with GTT’s object_id (table segment data_object_id was the same as for GTT temporary object_id). No problems – not any different from any “normal” table.

    There is flags column in sys.obj$. Second bit indicates temporary table (then set). So i believe Oracle server code checks that.

    P.S. Don’t ask me to reach power(2,32) and try to became an undo segment.

    Comment by Alexander Fatkulin — December 2, 2006 @ 6:32 am BST Dec 2,2006 | Reply

  11. Alexander, that’s one step in the right direction – now did you test what happens when you optimise a statement where there is a histogram on the last column of the table, just as another session drops a GTT with the same data object id ;-)
    Alas, one of the problems of addressing a question like the data object id collision is that you don’t know how many different bits of code have to be tested. Still, it’s nice to know that the whole system doesn’t collapse the moment you hit a critical number. Thanks for doing the test.

    Comment by Jonathan Lewis — December 3, 2006 @ 9:06 pm BST Dec 3,2006 | Reply

  12. Just a quick update to say “no news on the SR” … had a request for clarification, and the status of the request is currently “Work in Progress”.

    Comment by David Aldridge — December 13, 2006 @ 5:26 pm BST Dec 13,2006 | 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,905 other followers