Re: Partitioning with merge statement
- From: Sherrie.Kubis@xxxxxxxxxxxxxxxxxx
- To: Mindaugas Navickas <mnavickas@xxxxxxxxx>
- Date: Thu, 22 Feb 2007 17:07:59 -0500
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@xxxxxxxxxxxxxxxx
http://www.watermatters.org
-------------------------------------------------------------------------
Mindaugas
Navickas
<mnavickas@yahoo. To
com> Sherrie.Kubis@xxxxxxxxxxxxxxxxxx,
oracle-l@xxxxxxxxxxxxx
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):
1. ORA-600 [kokbcvb1]
2. 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@xxxxxxxxxxxxxxxxxx" <Sherrie.Kubis@xxxxxxxxxxxxxxxxxx>
To: oracle-l@xxxxxxxxxxxxx
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?
------------------------------------------------------------------------
Sherrie Kubis
Oracle DBA Supervisor
Information Resources Department
Application Systems Section
Southwest Florida Water Management District
352-796-7211 x4033
mailto:Sherrie.Kubis@xxxxxxxxxxxxxxxx
http://www.watermatters.org
-------------------------------------------------------------------------
-----------------------------------------
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.
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
