Re: Partitioning with merge statement

From: <Sherrie.Kubis_at_swfwmd.state.fl.us>
Date: Thu, 22 Feb 2007 17:07:59 -0500
Message-ID: <OF31A257FB.E038E243-ON8525728A.00793C7D-8525728A.0079945C@swfwmd.state.fl.us>

Thanks. We already tried the Star transform, it didn't help, but the one thing that did help was turning pruning off. However, this essentially makes all of our partitions throughout the database unused, so we can't do that. It can't be set at the application level either, and we really want to make use of the partitions anyway.

I wish we had the old environment, but with such large databases, we can't have duplicate environments.

It's interested to note that the same query against the same data that is not partitioned is much faster than the partitioned one. It should be the other way around.

Sherrie Kubis
Oracle DBA Supervisor
Information Resources Department
Application Systems Section
Southwest Florida Water Management District 352-796-7211 x4033
mailto:Sherrie.Kubis_at_watermatters.org
http://www.watermatters.org

                                                                           
             Mindaugas                                                     
             Navickas                                                      
             <mnavickas_at_yahoo.                                          To 
             com>                      Sherrie.Kubis_at_swfwmd.state.fl.us,   
                                       oracle-l_at_freelists.org              
             02/22/2007 04:51                                           cc 
             PM                                                            
                                                                   Subject 
                                       Re: Partitioning with merge         
                                       statement

Sherrie,

I fould separate your issue into 2 (i do not know - they might be related):

ORA-600 [kokbcvb1]
updates that used to take seconds now take 20 minutes or longer.

For #1 I would suggest to look into Bug #3646263 (which suggests a workaround
set star_transformation_enabled=temp_disable ) For #2 looking into what session waiting on might help to come to resolution. If you have access to old environment (9.2.0.5), you can verify what execution path was and how it differ from current. I did not experienced this issue myself, however I came across this bug description when I was evaluating migration impact from 9.2.0.5 to 9.2.0.8.

Regards
Mindaugas Navickas

Original Message ---- From: "Sherrie.Kubis_at_swfwmd.state.fl.us" <Sherrie.Kubis_at_swfwmd.state.fl.us> To: oracle-l_at_freelists.org Sent: Thursday, February 22, 2007 9:32:04 AM Subject: Partitioning with merge statement

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?

IMPORTANT NOTICE: All E-mail sent to this address are public record and archived. The Southwest Florida Water Management District does not allow use of District equipment and E-mail facilities for non-District business purposes.
--

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

--

http://www.freelists.org/webpage/oracle-l Received on Thu Feb 22 2007 - 16:07:59 CST