Re: Partitioning with merge statement

Sherrie,
did you check if the 'select' part of the query runs fine (or produces the same ora-600)?

rgds

On 2/22/07, Sherrie.Kubis_at_swfwmd.state.fl.us < Sherrie.Kubis_at_swfwmd.state.fl.us> wrote:
>
> 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):
>
> 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_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?
>
>
>
>
> ------------------------------------------------------------------------
> 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
> -------------------------------------------------------------------------
>
> -----------------------------------------
> 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
>
>
>

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