Partition strangeness

  • From: "Kline.Michael" <Michael.Kline@xxxxxxxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Thu, 27 Jan 2005 18:05:59 -0500

Offered FWIW.
 

We had a partitioned table, and I added another year of monthly
partitions to it.

 

So it had 2003, 2004, and we're getting ready for 2005. I added 200501
to 200512.

 

We used the same routine to populate and it populates one partition at a
time and this takes roughly 20-30 minutes per month.

 

When we did all the months up to 200411, they ran as expected. When we
tried 200412, the last of the old partitions, it was over 12 hours. We
analyzed the partition, all the input tables, still got the same
results. And while it was the same routine with the month as a variable,
the explain plan was different.

 

Finally I said we need to buy the time to analyze the whole table, all
36 partitions as a whole. It's the only thing that was different. That
took almost 2-4 hours.

 

When that was done, 200412 ran fast, in about 7 minutes instead of 12
hours on the last test.

 

Kind of a strange one that hit us. 

 

I assumed the analyzing the whole table cause the "header" to be updated
with total partitions and low and high values. There seems to not be a
whole lot of anything else. Perhaps, just perhaps if we had analyzed
200501 to 200512, we may have got the same results, but normally we
analyze AFTER load.

 

The full table analyze just takes so long.

 

Anyone run into anything like this before an know why? Each partition is
about 600 to 900 meg.

 

It's Oracle 8.1.7.4 and HPUX on a fast SAN.

 

We've got a BUNCH of partitioned tables, but normally we add one month
at a time, load it and analyze. Never had any problems. This was a first
for us.

 

Michael Kline
Database Administration
SunTrust Technology Center
1030 Wilmer Avenue
Richmond, Virginia  23227
Outside 804.261.9446
STNet 643.9446

Cell 804.744.1545
 <mailto:michael.kline@xxxxxxxxxxxx> michael.kline@xxxxxxxxxxxx 
************************************************ 
The information transmitted is intended solely 
for the individual or entity to which it is  
addressed and may contain confidential and/or 
privileged material. Any review, retransmission, 
dissemination or other use of or taking action 
in reliance upon this information by persons or 
entities other than the intended recipient is 
prohibited. If you have received this email in 
error please contact the sender and delete the 
material from any computer. [ST:A234] 
************************************************ 


--
//www.freelists.org/webpage/oracle-l

Other related posts: