Wednesday, January 21, 2009

Optimize Oracle UNDO Parameters

Optimize Oracle UNDO Parameters
________________________________________
Overview
Starting in Oracle9i, rollback segments are re-named undo logs. Traditionally transaction undo information was stored in Rollback Segments until a commit or rollback statement was issued, at which point it was made available for overlaying.
Best of all, automatic undo management allows the DBA to specify how long undo information should be retained after commit, preventing "snapshot too old" errors on long running queries.
This is done by setting the UNDO_RETENTION parameter. The default is 900 seconds (5 minutes), and you can set this parameter to guarantee that Oracle keeps undo logs for extended periods of time.
Rather than having to define and manage rollback segments, you can simply define an Undo tablespace and let Oracle take care of the rest. Turning on automatic undo management is easy. All you need to do is create an undo tablespace and set UNDO_MANAGEMENT = AUTO.
However it is worth to tune the following important parameters
1. The size of the UNDO tablespace
2. The UNDO_RETENTION parameter
Calculate UNDO_RETENTION for given UNDO Tabespace
You can choose to allocate a specific size for the UNDO tablespace and then set the UNDO_RETENTION parameter to an optimal value according to the UNDO size and the database activity. If your disk space is limited and you do not want to allocate more space than necessary to the UNDO tablespace, this is the way to proceed. The following query will help you to optimize the UNDO_RETENTION parameter:

Because these following queries use the V$UNDOSTAT statistics, run the queries only after the database has been running with UNDO for a significant and representative time!
Actual Undo Size
SELECT SUM(a.bytes) "UNDO_SIZE"
FROM v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND b.name = c.tablespace_name
AND a.ts# = b.ts#;
UNDO_SIZE
----------
209715200
Undo Blocks per Second
SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
"UNDO_BLOCK_PER_SEC"
FROM v$undostat;
UNDO_BLOCK_PER_SEC
------------------
3.12166667
DB Block Size
SELECT TO_NUMBER(value) "DB_BLOCK_SIZE [KByte]"
FROM v$parameter
WHERE name = 'db_block_size';
DB_BLOCK_SIZE [Byte]
--------------------
4096
Optimal Undo Retention
209'715'200 / (3.12166667 * 4'096) = 16'401 [Sec]
Using Inline Views, you can do all in one query!
SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
ROUND((d.undo_size / (to_number(f.value) *
g.undo_block_per_sec))) "OPTIMAL UNDO RETENTION [Sec]"
FROM (
SELECT SUM(a.bytes) undo_size
FROM v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND b.name = c.tablespace_name
AND a.ts# = b.ts#
) d,
v$parameter e,
v$parameter f,
(
SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
undo_block_per_sec
FROM v$undostat
) g
WHERE e.name = 'undo_retention'
AND f.name = 'db_block_size'
/
ACTUAL UNDO SIZE [MByte]
------------------------
200

UNDO RETENTION [Sec]
--------------------
10800

OPTIMAL UNDO RETENTION [Sec]
----------------------------
16401
Calculate Needed UNDO Size for given Database Activity
If you are not limited by disk space, then it would be better to choose the UNDO_RETENTION time that is best for you (for FLASHBACK, etc.). Allocate the appropriate size to the UNDO tablespace according to the database activity:

Again, all in one query:
SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
(TO_NUMBER(e.value) * TO_NUMBER(f.value) *
g.undo_block_per_sec) / (1024*1024)
"NEEDED UNDO SIZE [MByte]"
FROM (
SELECT SUM(a.bytes) undo_size
FROM v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND b.name = c.tablespace_name
AND a.ts# = b.ts#
) d,
v$parameter e,
v$parameter f,
(
SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
undo_block_per_sec
FROM v$undostat
) g
WHERE e.name = 'undo_retention'
AND f.name = 'db_block_size'
/
ACTUAL UNDO SIZE [MByte]
------------------------
200
UNDO RETENTION [Sec]
--------------------
10800
NEEDED UNDO SIZE [MByte]
------------------------
131.695313
The previous query may return a "NEEDED UNDO SIZE" that is less than the "ACTUAL UNDO SIZE". If this is the case, you may be wasting space. You can choose to resize your UNDO tablespace to a lesser value or increase your UNDO_RETENTION parameter to use the additional space.

select * from v$undostat order by end_time;

alter system set undo_retention=3600 scope=both ;

Generate Script To Grant Select Privileg To User On All Tables

SELECT 'GRANT select on OWNER_NAME.'|| view_name||' to USER_NAME ;' FROM dba_views WHERE owner = 'OWNER_NAME'

SELECT 'GRANT select on OWNER_NAME.'|| table_name||' to USER_NAME ;' FROM dba_tables WHERE owner = 'OWNER_NAME'

SELECT 'GRANT execute on OWNER_NAME.'||object_name||' to USER_NAME ;' FROM dba_objects where owner='OWNER_NAME'and object_type = 'PACKAGE'

Generate script to export all tables in database

Select 'exp system/manager@database_name buffer=102400 feedback=10000 statistics=none grants=y FILE=d:\backup\'|| s.Owner|| '_' || s.Segment_Name||'.DMP LOG= d:\backup\'|| s.Owner|| '_' || s.Segment_Name||'.LOG TABLES=' || s.Owner|| '.' || s.Segment_Name
From Sys.Dba_Segments s, Sys.Dba_Data_Files f
Where f.Tablespace_Name = s.Tablespace_Name And f.File_Id = s.Header_File
And Segment_Type In ('TABLE PARTITION','TABLE')
And Owner In ('owner_name')
Group By s.Tablespace_Name, s.Owner, s.Segment_Name, s.Segment_Type, f.File_Name
Order By Round(Sum(s.Bytes) / 1024 / 1024, 2) Desc

To Export Dmp File From Database

1-export schima
exp "system/manager@database_name" file="file_name.dmp" log="file_name.log" owner=owner_name STATISTICS=NONE ;
2-export table
exp system/manager@database_name STATISTICS=NONE file=file_name.dmp log=file_name.log TABLES=(owner_name.table_name);

To Import Dmp File To Database

imp system/manager@database_name fromuser=user_name touser=user_name grants=y file='file_name.DMP' log='file_name.log' commit=y ignore=y

imp system/manager@database file='file_name.DMP' log='file_name.log' full=y ignore=y commit=y

To Know The Tables Size In The Database

select s.owner, round(sum(s.bytes) / 1024 / 1024, 2) size_in_m
from sys.dba_segments s, sys.dba_data_files f
where f.tablespace_name = s.tablespace_name
and f.file_id = s.header_file
and s.tablespace_name not in ('SYS','SYSTEM')
-- A good thing to check is for objects in the SYSTEM tablespace that have s.owner <> 'SYS'
group by s.owner
Order by s.owner

To Generate Analyzed Script for tables and index

select 'execute dbms_stats.gather_table_stats('||''''||owner|| ''','''||''||table_name||''''|| ',estimate_percent=>dbms_stats.auto_sample_size,cascade => TRUE);'
from dba_tables
where OWNER not in ('SYS', 'SYSTEM')
group by owner, table_name
having sum(decode(nvl(NUM_ROWS,9999), 9999,1,0)) > 0


Select Distinct 'execute dbms_stats.gather_index_stats('||''''||owner|| ''','''||''||index_name||''''|| ');'
from dba_indexes
where OWNER not in ('SYS', 'SYSTEM')
group by owner, index_name
having sum(decode(nvl(NUM_ROWS,9999), 9999,1,0)) > 0;


Select Distinct 'execute dbms_stats.gather_table_stats('||''''||table_owner|| ''','''||''||table_name||''''|| ',estimate_percent=>dbms_stats.auto_sample_size,cascade => TRUE);'
from dba_tab_partitions
where table_OWNER not in ('SYS', 'SYSTEM')
group by table_owner, table_name
having sum(decode(nvl(NUM_ROWS,9999), 9999,1,0)) > 0;