10G – Optimize Oracle UNDO Parameters

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 The size of the UNDO tablespace 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.

11G – Read Only Table

Prior to 11G you can have read only tablespace but not tables but from 11G you can read only tables as well.

SQL>create table test as select * from dba_registry;

SQL>alter table test read only;

The new column of DBA_TABLES would tell you that whether table is read only or not.

You can make table read write with following command.

SQL>alter table test read write;

Once you put a table in a read-only mode, you can’t issue any DML statements such as update, insert, or delete. You also can’t issue a select for update statement involving a readonly table. You can issue DDL statements such as drop table and alter table on a read-only table, however. You can use the read-only feature to prevent changes to a table’s data during maintenance operations. You can perform maintenance operations on any of the indexes that you have defined on the read-only table, prior to changing their status to read-only. You can, of course, use this feature for security reasons, where you want to grant users the ability to read but not modify table data.

9I – 10G Oracle Database Architecture

• Oracle 9I Architecture – Simple
• Oracle 9I Architecture – Complex

11G – Invisible Indexes

Suppose you want to create huge index on some table but don’t want optimizer to change its plan based on new index then you need to use 11G’s new feature called invisible index.

11G allows you to create index which would be invisible to optimizer.

• How to create invisible index

SQL> create index idx on tbl1(name) invisible;
Index created.

You can also specify a tablespace while creating the invisible index.

SQL> create index idx2 on tbl2(name) invisible tablespace users;
Index created.

The index idx2 is stored to users tablespace as just a normal index.

• Making index invisible

SQL> alter index idx1 invisible;
Index altered.

• Making index visible

SQL> alter index idx1 visible;
Index altered.

You cann’t create two index on same column even if one being invisible.

If you want optimizer to use invisible index while deciding query plan then you need to set following parameter enabled.

SQL> alter session set optimized_use_invisible_indexes=true;

SQL> alter system set optimized_use_invisible_indexes=true;