Home » RDBMS Server » Performance Tuning » Undo used by Batch Job (Oracle 9.2.0.6.0 on HP-UX)
Undo used by Batch Job [message #440612] Tue, 26 January 2010 03:42 Go to next message
chetanaZ
Messages: 132
Registered: October 2009
Location: UK
Senior Member
Hi,

We have a batch job running for 4 hours in the evening - 18:30 till 22:30

If there are no database activities on the database after 22:30 till next day morning,

Is there a way to find out how much undo was used by this batch job previous evening?

meaning on 28/01/2010 morning can we find out how much undo was used by batch job which ran on the evening of 27/01/2010?

Thanks and Regards,
Chetana
Re: Undo used by Batch Job [message #440615 is a reply to message #440612] Tue, 26 January 2010 03:53 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> desc v$undostat
 Name                             Null?    Type
 -------------------------------- -------- ----------------------
 BEGIN_TIME                                DATE
 END_TIME                                  DATE
 UNDOTSN                                   NUMBER
 UNDOBLKS                                  NUMBER
 TXNCOUNT                                  NUMBER
 MAXQUERYLEN                               NUMBER
 MAXQUERYID                                VARCHAR2(13)
 MAXCONCURRENCY                            NUMBER
 UNXPSTEALCNT                              NUMBER
 UNXPBLKRELCNT                             NUMBER
 UNXPBLKREUCNT                             NUMBER
 EXPSTEALCNT                               NUMBER
 EXPBLKRELCNT                              NUMBER
 EXPBLKREUCNT                              NUMBER
 SSOLDERRCNT                               NUMBER
 NOSPACEERRCNT                             NUMBER
 ACTIVEBLKS                                NUMBER
 UNEXPIREDBLKS                             NUMBER
 EXPIREDBLKS                               NUMBER
 TUNED_UNDORETENTION                       NUMBER

Regards
Michel
Re: Undo used by Batch Job [message #440618 is a reply to message #440612] Tue, 26 January 2010 04:36 Go to previous messageGo to next message
chetanaZ
Messages: 132
Registered: October 2009
Location: UK
Senior Member
Hi Michel,

Many Thanks for the quick reply

Meaning if I execute following query on 28/01/2010 morning, I will get the undo (size), generated on 27/01/2010 evening?

select u.undoblks*8192/1024/1024 undosize_mb from v$undostat u
where 
u.begin_time>to_date('27-01-2010 18:30:00','dd-mm-yyyy hh24:mi:ss') and
    u.end_time<to_date('27-01-2010 22:30:00','dd-mm-yyyy hh24:mi:ss');


Also I assume I will need to give begin_time and end_time to match exactly with entries in v$undostat which are closer to my required begin/end_time. Right?

Thanks and Regards,
Chetana
Re: Undo used by Batch Job [message #440624 is a reply to message #440618] Tue, 26 January 2010 05:27 Go to previous message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Just try it to see what you get.
Refer to Database Reference to get the description of the view and extract the information you want.

Regards
Michel
Previous Topic: Query performance problem
Next Topic: v$pgastat value
Goto Forum:
  


Current Time: Fri Sep 30 00:27:35 CDT 2016

Total time taken to generate the page: 0.07949 seconds