10g R1, temp tablespace group, level 7 statspack snap - cons stats$seg_stat_ 2006-04-17 - By Riyaj Shamsudeen
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@(protected) <mailto:bdbafh@(protected)>>
> 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 (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 <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
>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
Hi Paul<br>
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.<br>
<br>
Thanks<br>
Riyaj<br>
<br>
Paul Drake wrote:
<blockquote
cite="mid910046b40604171106y669ad90cu7fa8763f182ee847@(protected)"
type="cite">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 (See ORA-06512.ora-code.com): 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>
</blockquote>
<br>
</body>
</html>
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.
|
|
