Home » RDBMS Server » Server Administration » UNDO size increasing at very high speed
UNDO size increasing at very high speed [message #253297] Mon, 23 July 2007 07:28 Go to next message
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 #253313 is a reply to message #253297] Mon, 23 July 2007 08:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read and follow How to format your posts and How to get a quick answer to your question: TIPS AND TRICKS
Make sure that lines of code do not exceed 80 or 100 characters when you format.
Please always post your Oracle version (4 decimals).

This script is not a valid SQL statement.

Regards
Michel
Re: UNDO size increasing at very high speed [message #253326 is a reply to message #253313] Mon, 23 July 2007 09:23 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
Whats the rate of activities on the server?means DML..
Re: UNDO size increasing at very high speed [message #253446 is a reply to message #253297] Tue, 24 July 2007 00:44 Go to previous messageGo to next message
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 #253447 is a reply to message #253446] Tue, 24 July 2007 00:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It also depends on the version you still did not give.

Regards
Michel
Re: UNDO size increasing at very high speed [message #254131 is a reply to message #253447] Wed, 25 July 2007 18:06 Go to previous message
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)
Previous Topic: adding online redo logs
Next Topic: datablock corruption, when you get time
Goto Forum:
  


Current Time: Wed Dec 07 04:58:43 CST 2016

Total time taken to generate the page: 0.09763 seconds