Partition strangeness

From: Kline.Michael <Michael.Kline_at_SunTrust.com>
Date: Thu, 27 Jan 2005 18:05:59 -0500
Message-ID: <52C70FF150F49E479DAF59C68A27149DAFCB61@va016a0e2.corp.suntrust.com>

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_at_suntrust.com> michael.kline_at_suntrust.com

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]

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

Received on Thu Jan 27 2005 - 18:08:43 CST