Re: Partitioning with merge statement

Sherrie,

Since you no longer have the original environment, you're going to have to do some guessing on this one.

The most probable root cause is a change of execution path.

A merge statement operates at an outer join - and you can choose whether this should be a hash, nl, or merge - and you can put in a hint to the merge command to select which one you want to see.

You mention in a later post that the non-partitioned version works faster than the partitioned version, and that when you disable pruning you don't crash. Both statements would be consistent with the execution plan changing because the numbers have changed.

Check the execution plan of the non-partitioned merge, and hint the partitioned one to use the same mechanism as the non-partitioned one - if it isn't already doing so.

I would also run with sql-trace enabled to see what Oracle is doing about its pruning subquery in case that gives you any clues.

Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

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

• Original Message ----- From: "FreeLists Mailing List Manager" <ecartis_at_freelists.org> To: "oracle-l digest users" <oracle-l_at_freelists.org> Sent: Friday, February 23, 2007 8:03 AM

> Subject: Partitioning with merge statement
> From: Sherrie.Kubis_at_swfwmd.state.fl.us
> Date: Thu, 22 Feb 2007 09:32:04 -0500
>
> OS: HP-UX 11.11
> Oracle 9.2.0.8 (recently upgraded from 9.2.0.5)
>
> Partitioned table: approx. 72 million rows
> 19 range partitions,
> 76 has subpartitions
>
> Our partitioned table is updated nightly with a MERGE/UPDATE statement, and
> this always
> worked well in 9.2.0.5. When we went to 9.2.0.8 we sometimes (not
> consistently) got a
> ORA-600 [kokbcvb1]. The consistent part is that updates that used to take
> seconds now take
> 20 minutes or longer. Recently, the ORA-00600 became consistent.
>
> We're working (since November!) with Oracle support on this, but it seems
> to be going nowhere.
>
> Has anyone else had any problems with Partitioned tables in 9.2.0.8?
>

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