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
On 4/12/06, *Paul Drake* <bdbafh@xxxxxxxxx <mailto:bdbafh@xxxxxxxxx>> 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.
