10g R1, temp tablespace group, level 7 statspack snap - cons stats$seg_stat_ 2006-04-17 - By Paul Drake
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>
|
|
