On 4/12/06, Paul Drake <bdbafh@(protected)> 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 (See ORA-00001.ora-code.com): unique constraint (PERFSTAT.STATS$SEG_STAT_PK) violated > ORA-06512 (See ORA-06512.ora-code.com): at "PERFSTAT"."STATSPACK". line 2654 > ORA-06512 (See ORA-06512.ora-code.com): at "PERFSTAT"."STATSPACK". line 4516 > ORA-06512 (See ORA-06512.ora-code.com): at "PERFSTAT"."STATSPACK". line 91 > ORA-06512 (See ORA-06512.ora-code.com): 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 (See ORA-1.ora-code.com). :) > > 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
On 4/12/06, <b class="gmail_sendername">Paul Drake</b> <<a href="mailto :bdbafh@(protected)">bdbafh@(protected)</a>> wrote:<div><span class="gmail_quote" ></span><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;"> <div style="direction: ltr;"><a href="http://10.1.0.4" target="_blank" onclick= "return top.js.OpenExtLink(window,event,this)">10.1.0.4</a>, <a href="http://10 .1.0.4" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)"> 10.1.0.4</a> patch 10 standard edition<br>w2k adv svr sp4<br><br>I'm wondering if anyone has seen this type of error before?<br><br>Had contention on a single tempfile in a temp tablespace. <br>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.<br>I attempted to leverage a tablespace group for the load to be balanced across multiple temp tablespaces' tempfiles on different mount points. <br>As no potentially good deed goes unpunished, this arrangement threw an error when attempting a level 7 statspack snapshot:<br><br>ERROR at line 1:<br >ORA-00001 (See ORA-00001.ora-code.com): unique constraint (PERFSTAT.STATS$SEG_STAT_PK) violated <br> ORA-06512 (See ORA-06512.ora-code.com): at "PERFSTAT"."STATSPACK". line 2654<br>ORA -06512: at "PERFSTAT"."STATSPACK". line 4516<br>ORA-06512 (See ORA-06512.ora-code.com): at "PERFSTAT"."STATSPACK". line 91<br>ORA-06512 (See ORA-06512.ora-code.com): at line 1 <br><br>The cons columns are<br>(snap_id, dbid, instance_number, dataobj#, obj# )<br><br>The offending statement is an insert statement.<br><br>Funny thing is that at line 2712 of the package, there is a comment regarding avoiding ORA-1 (See ORA-1.ora-code.com). :) <br><br>I'm not going to file an SR on this now, but if its still around with the <a href="http://10.1.0.5" target="_blank" onclick="return top.js .OpenExtLink(window,event,this)">10.1.0.5</a> patchset in place I may do so later. <br></div><div style="direction: ltr;"><span class="sg"><br>Paul<br>
</span></div></blockquote></div><br>This appears to me to be overhead os using a temporary tablespace group in 10g R1:<br clear="all"><br><span style="font -family: courier new,monospace;">   ; % Total Old </span><br style="font-family: courier new,monospace;"><span style="font-family : courier new,monospace;"> Parse Calls Executions Parses Hash Value</span><br style="font-family: courier new,monospace;"><span style="font-family: courier new,monospace;"> -- ---- ---- -- ---- ---- -- ----- -- ---- --</span><br style="font-family: courier new,monospace;"><span style="font-family: courier new,monospace;">  ; 108,193 108,194 45.85 693993892</span><br style="font-family: courier new,monospace ;"> <span style="font-family: courier new,monospace;">select min(bitmapped) from ts $ where dflmaxext =:1 and bitand(fl</span><br style="font-family: courier new ,monospace;"><span style="font-family: courier new,monospace;">ags,1024) =1024 <br><br></span><div dir="ltr" align="left"><span class="546474417-17042006"> <font face="Courier New"> CPU Elapsd Old<br> Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value<br>-- ---- ---- -- -- ---- ---- -- ---- ------ -- --- -- ----- -- ------ -- ---- --<br> 3,354,014 108,194 31.0 46.5 32.58 32.60 693993892<br >select min(bitmapped) from ts$ where dflmaxext =:1 and bitand(fl<br>ags,1024) =1024<br><br><br></font></span></div >Has anyone come across this before?<br>I am very much tempted to bag the use of temporary tablespace groups - "the count is no balls and two strikes" . <br><br>Paul<br><br>
