Re: 10g R1, temp tablespace group, level 7 statspack snap - cons stats$seg_stat_pk violated
- From: "Paul Drake" <bdbafh@xxxxxxxxx>
- To: "Oracle-L@Freelists" <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 17 Apr 2006 14:06:53 -0400
On 4/12/06, Paul Drake <bdbafh@xxxxxxxxx> 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
Other related posts:
