Ten minute partitions sound painful ! (That's as close as I can get to "what kind of a DBA are you" ;)
Two attendant questions - How many partitions will you end up with in the table ? Large numbers, especially when locally indexed, can result in dictionary thrashing. Also, depending on the nature of the queries, the overheads may far outweigh any anticipated performance benefits. What's the splitting strategy - split every 10 minutes, or split (say) once every 24 hours to break off the last day, then split that day into 10 minute slices ? Remember that every time you split a partition you invalidate dependent objects - which includes all cursors - so the impact on any otherwise sharable SQL is bad. Remember too that every time you split, Oracle renumber every partition above the split point - and every time you drop a partition you renumber every partition about the drop point. Your description has just reminded me of one bizarre accident I got when stressing partitioned IOTs in Oracle 8.1.6 - I couldn't reproduce it, but I managed to get a partition that APPEARED to belong to the table I wanted it in, but (like you) if I queried it by name it didn't exist in the table, but if I queried the main table with a suitable range of values the data appeared. Somehow the data dictionary knew it belonged to two different tables in two different schemas - so a direct query wouldn't find it, but a data-based query visited it without checking its ownership. You could try flushing the shared pool before doing the trace - if the dictionary cache holds all the defining information, a trace file may not show the cause of such an error; but if the defining data has to be reloaded into the dictionary cache, then you may spot the FETCH that fails. 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 <[EMAIL PROTECTED]> Date: 18 December 2002 20:25 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 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
