How to calculate UNDO table space optimal size in oracle database?

Here is the list of steps to follow to get optimal UNDO table space.

Undo Space = UNDO_RETENTION in seconds * undo blocks for each second * block_size of the database/TBS

lets say if undo retention 1 hr 60*60 seconds
undo block usage per second 300
undo block size 8k 8192

so optimal undo size=60*60 *300*8192/1024/124=843 MB



To check the block_size on database


SELECT TO_NUMBER(value) as DB_BLOCK_SIZE   FROM v$parameter WHERE name = 'db_block_size';

find the number of undo block per second;

SELECT MAX(undoblks/((end_time-begin_time)*3600*24)) as UNDO_BLOCK_PER_SECOND   FROM v$undostat;

To check the undo retention of tablespaces.

select name,value FROM v$parameter WHERE name='undo_retention';

scrip to check the Progress of the SQL Tuning Advisor job in oracle database?

You can check the execution progress of the SQL Tuning Advisor in the V$ADVISOR_PROGRESS view.


SELECT sofar, totalwork FROM V$ADVISOR_PROGRESS
WHERE user_name = <USER_NAME>' AND task_name = '<sql_tuning_task_NAME>';

what are the input source for SQL tunning advisor in oracle database?

SQL tuning advisor inputs:


1.addm
2.awr
3.cursor cache
4.STS(SQL tuning Sets)

what are the statements we can apply SQL profiles in oracle database?

SQL Profiles apply to the following statement types:


SELECT statements
UPDATE statements
INSERT statements (only with a SELECT clause)
DELETE statements
CREATE TABLE statements (only with the AS SELECT clause)
MERGE statements (the update or insert operations)

what is the pre-request for converting standby database to max protection & max availability?

Here is the pre-request for converting standby database to Max.availability & Max Protection

1)need to create Standby Redo log
2)DB  Flashback would be turned ON
3)Log Transport mode should be SYNC

script to find out wait event objects in oracle database.

Steps to get the wait event object name

select event,p1,p2,p3 from V$session_wait where
sid in (select sid from v$session where username='BALA');

where P1--file# P2 block number


define __FILE = &1
define __BLOCK = &2

select segment_name from dba_extents where file_id = &__FILE
and &__BLOCK between block_id and block_id + blocks - 1 and rownum = 1 ;

set echo on