| Incredible TEMPorary tablespace growth [message #56407] |
Thu, 27 March 2003 04:37  |
Suraj
Messages: 38
Registered: April 2002
|
Member |
|
|
Hi all,
Running Oracle 8.1.7.3 on HP-UX B.11.00 U 9000/800 (tb) we created a new temporary tablespace following an astonomical usage of the old.
The following command was used for creating the new tablespace
Create tablespace TEMP2 datafile '/u09/oratemp/temp02.dbf'
size 1000M autoextend ON next 1M maxsize 10000M
default storage(initial 64K next 64K maxextents unlimited pctincrease 0) temporary;
We observed/monitored the datafile temp02.dbf over a period of time and noticed a growth rate of about 20M in every one minute. This continued until it got to the maximum size specified.
We also nocied that it is affecting the performance of the system as users's job now take more than usual time to complete.
Below is a section of the alert.log of the Instance at some time of the observation
Wed Mar 26 21:55:35 2003
Thread 1 advanced to log sequence 408530
Current log# 2 seq# 408530 mem# 0: /u01/oracle/oradata/jde/redo02.log
Wed Mar 26 21:56:17 2003
Thread 1 advanced to log sequence 408531
Current log# 3 seq# 408531 mem# 0: /u01/oracle/oradata/jde/redo03.log
Wed Mar 26 21:56:37 2003
ORA-1652: unable to extend temp segment by 8 in tablespace TEMP2
Thu Mar 27 07:53:15 2003
Thread 1 advanced to log sequence 408532
Current log# 1 seq# 408532 mem# 0: /u01/oracle/oradata/jde/redo01.log
Thu Mar 27 07:56:05 2003
Thread 1 advanced to log sequence 408533
Current log# 2 seq# 408533 mem# 0: /u01/oracle/oradata/jde/redo02.log
Thu Mar 27 08:00:32 2003
Thread 1 advanced to log sequence 408534
Current log# 3 seq# 408534 mem# 0: /u01/oracle/oradata/jde/redo03.log
Thu Mar 27 08:02:59 2003
Thread 1 advanced to log sequence 408535
Current log# 1 seq# 408535 mem# 0: /u01/oracle/oradata/jde/redo01.log
Thu Mar 27 08:05:28 2003
Thread 1 advanced to log sequence 408536
Current log# 2 seq# 408536 mem# 0: /u01/oracle/oradata/jde/redo02.log
ORA-1652: unable to extend temp segment by 8 in tablespace TEMP2 occured only at 21:56:37 2003 and has not appeared again in the log up till the time of this message.
We cannot just continue to increase the size due to the usage.
What could be responsible for the unusal growth in TEMP tablespace usage?
What is the effect if it is left as it were on the Database if left with no more increase?
A query of the V$sysstat gives the follwing.
1 select statistic#, name, class, value
2 from v$sysstat
3* where name like 'sort%'
SQL>
SQL> /
STATISTIC# NAME CLASS VALUE
---------- -------------------- ---------- ----------
188 sorts (memory) 64 84453
189 sorts (disk) 64 27
190 sorts (rows) 64 1412130
Any help would be appreciated on the before the total crash of the instance
Thanks
Suraj
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
