-
UNDO space used
I know that my UNDO size will not shrink as long as there are active
transactions within the tablespace.
Is there a query I can use to see how long a transaction as been inactive
so it can be released from the UNDO tablespace.
Here is my setup for UNDO:
*.undo_management='AUTO'
*.undo_retention=5400
*.undo_tablespace='UNDOTBS01
What I am trying to determine is at what time/(s) I will get back space
in the UNDO TS.
-
More along the lines of "expired" is what you're looking for, and you may be able to swag the release time based on the retention period.
http://download.oracle.com/docs/cd/E...htm#REFRN23299
-
Originally Posted by BeefStu
*.undo_management='AUTO'
*.undo_retention=5400
*.undo_tablespace='UNDOTBS01
What I am trying to determine is at what time/(s) I will get back space in the UNDO TS.
Exactly after undo_retention for specific transaction completes, in this case 5400 seconds.
Monitoring of space size on UNDOTBS should be based on HWM for desired undo_retention - nothing else matters.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
I have a count of 46,126 that are "UNEXPIRED" and a count of "22" that are marked "EXPIRED" and a count of "0" transactions that are active.
I am looking at my UNDO TS and I see there is NO free space within it.
I know that "UNEXPIRED" transactions may be over-written if needed.
My questions are:
1) How do I find out when the unexpired transactions will be marked expired
or do I never have to worry about that since they will be over-written
because there are no ACTIVE transactions?
2) Will I ever see free space within my UNDO TS again or will it always
be full and space will be taken when needed.
-
Originally Posted by BeefStu
1) How do I find out when the unexpired transactions will be marked expired or do I never have to worry about that since they will be over-written because there are no ACTIVE transactions?
2) Will I ever see free space within my UNDO TS again or will it always be full and space will be taken when needed.
Let me start with #2. Over time Oracle may decrease UNDOTBS HWM and certainly after a shudown/startup you will see UNDOTBS HWM reset.
In regards to #1... do not over-engineer the issue. Only two things matter being a) undo_retention and b) space availability.
Query below shows you the top five queries ordered by elapsed time, if you want to ensure no query gets ORA-01555 just set undo_retention higher than the higher number you see...
Code:
select * from(
select
begin_time,
maxquerylen,
undoblks
from
V$UNDOSTAT
where
begin_time > sysdate-(24/24)
order by
maxquerylen desc
)where
rownum < 6;
Please notice you may have bad performing queries in the system, if you see queries with too large of a elapsed time it is better to investigate further and eventually fine tune them.
Query below shows you errors caused by lack of UNDO space...
Code:
select * from(
select
begin_time,
end_time,
undoblks,
txncount,
maxconcurrency as maxcon,
nospaceerrcnt as ERRORS
from
V$UNDOSTAT
where
begin_time > sysdate-(24/24)
order by
ERRORS desc, undoblks desc
)where
rownum < 6;
...if you see ERRORS > 0 then additional UNDO space may be needed.
Hope this points you in the right direction.
Last edited by PAVB; 12-27-2010 at 05:07 PM.
Reason: typo
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
thanks for the great explanation
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|