Some Exciting Oracle Advisory Utilities you should not miss!!

 Author: H.Tonguç Yilmaz

Page: 1 2

Every new release of Oracle has brought something new and exciting for its users. The advisory utilities introduced by Oracle are extremely important for the Oracle database administrators. Oracle has been continuously investing on its advisories and with the passage of time the Oracle advisory utilities such as memory, SQL Access and Tuning and segment advisors etc have became an important part of our lives. In this article I will discuss some less popular yet exciting Oracle advisory utilities of Oracle 10g and Oracle 11g.

Oracle V$SESSION_LONGOPS View:

Oracle 10g provides improved rollback monitoring. In Oracle 10g if the transaction rolls back and it takes more than six seconds then the event is recorded in the view V$SESSION_LONGOPS. You can use Oracle V$SESSION_LONGOPS view to estimate the time taken by monitored rollback process to finish.

SELECT TIME_REMAINING, SOFAR/TOTALWORK*100 PCT
FROM V$SESSION_LONGOPS
WHERE SID = 8
AND OPNAME ='Transaction Rollback'

Oracle Undo Advisor:

The amount of undo data retained is dependent on the size of your undo tablespace. The space is acquired automatically by Oracle If your tablespace is set to auto-extend. However if auto-extend parameter is disabled then you have to manually set such value that your undo tablespace has enough space to retain undo data. In such scenarios the undo retention is set to maximum possible value by Oracle for that tablespace size.

Oracle Undo Advisor is a powerful utility that help you sizing the undo tablespace correctly. Oracle Undo Advisor can be used to analyze a number of situations and help you determine an appropriate undo tablespace size for different values of maximum undo retention. You can use Oracle Undo Advisor to set the low threshold value of the undo retention period for any Oracle Flashback requirements.

Oracle Redo Log Tuning Advisor:

Oracle Redo Log Tuning Advisor is a new feature introduced in Oracle 10g that helps you tune the redo logfile size as specified by column optimal_logfile_size of v$instance_recovery.

The behavior of archiver processes and database writers is dependent on the redo log sizes and therefore the size of the redo log files can greatly influence the performance. In general practice setting larger redo log files can help you gain better performance but it must be balanced out with the expected recovery time. However if your log files are under-sized then it can increase the checkpoint activity and increase CPU usage. It is recommended to switch the logs at most once after every 15 to 20 minutes.

Oracle redo log tuning advisory feature requires you to set the parameter fast_start_mttr_target so that the advisory could take effect and populate the column optimal_logfile_size. If you set fast_start_mttr_target parameter to small value such that a recovery can not be made in specified time frame then target_mttr field of v$instance_recovery contains the effective mttr target. Similarly if you set fast_start_mttr_target value so high that even in worst-case scenarios the recovery would not take that long then target_mrrt field contains the estimated mttr in worst-case scenarios. Continued...

Page: 1 2

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   Oracle Date Formats
   Avoiding Rollback Generation in Bulk Delete!!
   Some Great Tips on Oracle 11g Encrypted Tablespaces!!
   Testing Database Security