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

From: Riyaj Shamsudeen <rshamsud_at_jcpenney.com>
Date: Mon, 17 Apr 2006 13:20:22 -0500
Message-ID: <4443DC66.2030504@jcpenney.com>

('binary' encoding is not supported, stored as-is)

Hi Paul

    Precisely what we encountered with temp tablespace groups and we were trying to reduce contention too. SQLs that you have listed here caused excessive CPU issue during our 10g performance test and we stopped using temp tablespace groups.

Thanks
Riyaj

Paul Drake wrote:
> On 4/12/06, *Paul Drake* <bdbafh_at_gmail.com <mailto:bdbafh_at_gmail.com>>
> wrote:
>
> 10.1.0.4 <http://10.1.0.4>, 10.1.0.4 <http://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 <http://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
>

The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. If the reader of this message is not the intended recipient, you are hereby notified that your access is unauthorized, and any review, dissemination, distribution or copying of this message including any attachments is strictly prohibited. If you are not the intended recipient, please contact the sender and delete the material from any computer.

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

Received on Mon Apr 17 2006 - 13:20:22 CDT