Re: 10g R1, temp tablespace group, level 7 statspack snap - cons stats$seg_stat_pk violated

From: Paul Drake <bdbafh_at_gmail.com>
Date: Mon, 17 Apr 2006 14:06:53 -0400
Message-ID: <910046b40604171106y669ad90cu7fa8763f182ee847@mail.gmail.com>

On 4/12/06, Paul Drake <bdbafh_at_gmail.com> wrote:
>
> 10.1.0.4, 10.1.0.4 patch 10 standard edition
> w2k adv svr sp4
>
> I'm wondering if anyone has seen this type of error before?
>
> Had contention on a single tempfile in a temp tablespace.
> Only one tempfile out of 4 was being used (one temp tablespace with 1
> tempfile on each of 4 mount points) which was resulting in contention.
> I attempted to leverage a tablespace group for the load to be balanced
> across multiple temp tablespaces' tempfiles on different mount points.
> As no potentially good deed goes unpunished, this arrangement threw an
> error when attempting a level 7 statspack snapshot:
>
> ERROR at line 1:
> ORA-00001: unique constraint (PERFSTAT.STATS$SEG_STAT_PK) violated
> ORA-06512: at "PERFSTAT"."STATSPACK". line 2654
> ORA-06512: at "PERFSTAT"."STATSPACK". line 4516
> ORA-06512: at "PERFSTAT"."STATSPACK". line 91
> ORA-06512: at line 1
>
> The cons columns are
> (snap_id, dbid, instance_number, dataobj#, obj#)
>
> The offending statement is an insert statement.
>
> Funny thing is that at line 2712 of the package, there is a comment
> regarding avoiding ORA-1. :)
>
> I'm not going to file an SR on this now, but if its still around with the
> 10.1.0.5 patchset in place I may do so later.
>
> Paul
>

This appears to me to be overhead os using a temporary tablespace group in 10g R1:

                           % Total    Old

 Parse Calls Executions Parses Hash Value ------------ ------------ -------- ----------

     108,193 108,194 45.85 693993892 select min(bitmapped) from ts$ where dflmaxext =:1 and bitand(fl ags,1024) =1024

                                                     CPU      Elapsd     Old

  Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- ---------

---

      3,354,014 108,194 31.0 46.5 32.58 32.60 693993892
select min(bitmapped) from ts$ where dflmaxext =:1 and bitand(fl ags,1024) =1024

Has anyone come across this before?
I am very much tempted to bag the use of temporary tablespace groups - "the count is no balls and two strikes".

Paul

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

Received on Mon Apr 17 2006 - 13:06:53 CDT