| UNDO size increasing at very high speed [message #253297] |
Mon, 23 July 2007 07:28  |
puneetsachar
Messages: 97
Registered: March 2005
Location: New Delhi, India
|
Member |
|
|
hi
My undo tablespace is increasing at very high speed, it has increased to 17 GB in last 5-6 days.
We are on uat and lot user are testing but what could be reason why the undo is increasing at such a high speed.
Please advice what do i need to check, do i need to ask my users/developers to look into their codes again.
Please find the sql stm which i use to monitor the undo, may be some issue in that.
SELECT F.TABLESPACE_NAME,
TO_CHAR ((T.TOTAL_SPACE - F.FREE_SPACE),'999,999') "USED (MB)",
TO_CHAR (F.FREE_SPACE, '999,999') "FREE (MB)",
TO_CHAR (T.TOTAL_SPACE, '999,999') "TOTAL (MB)",
TO_CHAR ((ROUND ((F.FREE_SPACE/T.TOTAL_SPACE)*100)),'999')||' %' PER_FREE
FROM (
SELECT TABLESPACE_NAME,
ROUND (SUM (BLOCKS*(SELECT VALUE/1024
FROM V\$PARAMETER
WHERE NAME = 'db_block_size')/1024)
) FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME
) F,
(
SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES/1048576)) TOTAL_SPACE
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME
) T
WHERE F.TABLESPACE_NAME = T.TABLESPACE_NAME
AND (ROUND ((F.FREE_SPACE/T.TOTAL_SPACE)*100)) < 10;
this script issue me alert then any tablespace having less than 10% space.
Please advice
thanks
Puneet
|
|
|
|
|
|
|
|
| Re: UNDO size increasing at very high speed [message #253446 is a reply to message #253297] |
Tue, 24 July 2007 00:44  |
puneetsachar
Messages: 97
Registered: March 2005
Location: New Delhi, India
|
Member |
|
|
hi
Yeah there is high dml as we are doing uat testing.
The same testing was done on CRP but there we have not come across such high increase.
The retention parameter is set to 900, which i beleive is default.
Is the increase is due to fact the user are not commiting the data in b/w rather than doing commit after large inserts.
Please find other results,
1) only one record in V$tranction, dated 23 july
2) SELECT s.sid, s.serial#, s.username, s.program, t.used_ublk, t.used_urec FROM v$session s, v$transaction t
WHERE s.taddr = t.addr ORDER BY 5 desc, 6 desc, 1, 2, 3, 4;
fired 3 times in span of 10 minutes
1 run -> fetch record
SID SERIAL# USERNAME
---------- ---------- ------------------------------
PROGRAM USED_UBLK USED_UREC
------------------------------------------------ ---------- ----------
173 7416 APPS
119 7245
367 21522 SYS
oracle@oracrp (J000) 1 5
535 20448 APPS
1 1
b. second run) --> no rows
c. Third run ) --> no rows selected
thanks
|
|
|
|
|
|
| Re: UNDO size increasing at very high speed [message #254131 is a reply to message #253447] |
Wed, 25 July 2007 18:06 |
andrew again
Messages: 2577
Registered: March 2000
|
Senior Member |
|
|
A common cause for this type of behavior is having auto-extend enabled for the tablespace, and someone runs an unreasonably large query (like a cartesean join).
You can look for offending selects if they are still cached...
select * from v$sql
where disk_reads > (select max(disk_reads)/2 from v$sql) |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
