Re: Partitioning with merge statement
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 23 Feb 2007 08:48:35 -0000
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@xxxxxxxxxxxxx>
To: "oracle-l digest users" <oracle-l@xxxxxxxxxxxxx>
Sent: Friday, February 23, 2007 8:03 AM
Subject: Partitioning with merge statement
From: Sherrie.Kubis@xxxxxxxxxxxxxxxxxx
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?
--
//www.freelists.org/webpage/oracle-l
Other related posts:
