Oracle-L: RE: Direct Path Inserts and Partition Splits

From: MacGregor, Ian A. <ian_at_SLAC.Stanford.EDU>
Date: Wed, 18 Dec 2002 11:44:03 -0800
Message-ID: <F001.0051D616.20021218114403@fatcity.com>

The database version is 9.0.1.3. The OCI program attempts to obtain an exclusive lock on the table, and waits until the split operation is complete and the locks associated with the split are released allowing it to resume.

In the production system we will have partitions to hold ten minutes worth of data. The present table was created with two partitions one called first and the other bin. All inserts were to the bin partition. It was the bin partition which was split.

As I stated before the bin partition was split on December 4th into partitions for Nov 26, Nov 27, Nov 28, Nov 29, and Nov 30. Data for dates after November 30th remained in the bin partition, which also remained the insert partition. Partitions for Nov 26 and Nov 27 were moved to a different table and then dropped from the original The trouble was discovered when the user tried to do a count(*) against Nov 28 partition. All partitions left in the original table return an 8103 error when specifically named in the query. However data except for about 1.5 hours of the 28 November partition is accessible if the query is based on date ranges.

I did do a simple 10046 trace at Oracle's behest. The statement being traced failed with the 8103 error, however the trace itself recorded no problems. Nothing looks out of the ordinary at all concerning the obj$ table.

Oracle's proclamation is that its okay to split partitions which are involved in a pending DML operation. This partition an insert statement queued awaiting the release of the resource. Their concern was what if the split changed the partition to which the pending insert should go. In this case it would not have.

I have oscillated in my own mind, it's my first experience with partitioning, whether what we did would obviously cause dictionary corruption Certainly it would have been better to create the table with the necessary partitions in the first place. The production system will undergo significant partition management operations, just not against the partition which is receiving the data. I was really hoping for an " Of course what you did will cause dictionary corruption. What kind of DBA are you anyway?" response. I could then add it to my experience and tut-tut anyone who tried to do the same in the future. Your answer has only increased my unease about going forward.

Ian

-----Original Message-----
Sent: Wednesday, December 18, 2002 3:20 AM To: Multiple recipients of list ORACLE-L

Which version of Oracle ? 8.1 or 9.2 ?

It could make a difference because of
the 'free split' change in code that appeared in 9.2.

I don't recall the error I got - but some time back I was working on a partition exchange strategy and testing the effects of tablespace corruption in mid-exchange. One of the scenarios I built managed to leave me with a partition in a non-existent tablespace, but the partition could not be dropped because it didn't exist. Clearly some form of dictionary corruption on partition maintenance - so there are some oddities to be cleaned.

It might also be an idea to check of the OCI program manages to hold a cursor open
that should have been invalidated on the split. Is it possible that when suspended, the
program then pushed its current data set into a partition/data segment that simply did not exist in the data dictionary ?

Can you add a little detail about the structure of the PT and its missing elements. Do you have multiple partitions per day, with some giving 8103 when queried by name; or is it one partition per day, but queries which extend into the gap give the 8103 ?

Have you tried running with a level 4 on 10046 yet to see the actual dictionary accesses that Oracle attempts when looking for the critical data (probably have to bounce the database to get the best effects here); of checked the contents of the obj$, and related tables around the object numbers and data object numbers of the damaged bits to see if there is any data there that does not fit the pattern ?

I tend to disagree with your comment about splitting on the fly being silly, by the way - as soon as Oracle introduced MAXVALUE, they introduced an implicit requirement either for an unbreakable mechanism for doing a split in real-time, or for a mechanism for shunting data above the current high_value into a holding table. (Requiring end-user code to handle rogue data would otherwise introduce a significant overhead on processing times).

Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Coming soon a new one-day tutorial:
Cost Based Optimisation
(see http://www.jlcomp.demon.co.uk/tutorial.html )

Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____England______January 21/23

The CO-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html

-----Original Message-----
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Date: 17 December 2002 23:14

A program inserts via OCI DirectPath telemetry data into a time-based partition every 30 seconds, each insert acquiring an exclusive lock on the table. The users decided to further split the partition receiving the inserts by time. Each of the new partitions is stale, by that I mean their high values are all previous to the present data. The split was done on December 4th carved out partitions for November 26, 27, 28, 29, and 30. While the split is going on, the program buffered the next inserts. Once the split is done the program continues its inserts into the original partition. (During the split Oracle acquires locks which prevent data manipulation)

I realize that splitting a partition on the fly like this is a silly thing to do. It was never going to be part of the production system. However I was not sure if doing so would work. We tried it and the result may have been data dictionary corruption; i.e., ORA-8103 errors. Data in the November 26, November 27, about 22.5 hours of November 28, November 29, November 30 and the input partition remain visible. It's only the 1/5 hours of the November 28th partition which is inaccessible. In a way it's very much like a block corruption problem. Some additional information: all objects in the database are valid, all indexes are usable, besides there were no indexes involved in the split operation. DBV reports no problem with any of the database files.

Oracle support also blames it on the split activity. I am anxious because there is a big push to put this system into production. I need to be sure the corruption was caused by the split. We won't be that silly again. Unfortunately the corruption was not discovered immediately afterwards. The split as the cause is really just a guess. Has anyone else seen unexplained 8103 errors after partition management?

Ian MacGregor
Stanford Linear Accelerator Center
ian_at_slac.stanford.edu

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Jonathan Lewis
INET: jonathan_at_jlcomp.demon.co.uk

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: MacGregor, Ian A.
INET: ian_at_SLAC.Stanford.EDU

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------