Automatic Segment Space Management

Automatic Segment Space Management

Prior to 9i, you had to manage space manually by the PCTFREE, PCTUSED, and FREELIST GROUPS parameters. 9i makes these parameters obsolete because its automatic segment space management uses bitmaps instead of freelists.

To use this feature, the tablespace must be locally-managed and you must use the SEGMENT SPACE MANAGEMENT clause:

CREATE TABLESPACE my_ts
DATAFILE ‘d:\oracle\oradata\orcl\test.dbf’ size 10M
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO ;

View DBA_TABLESPACES lists whether segment space management is AUTO or MANUAL. Use the new DBMS_SPACE package procedure SPACE_USAGE to view information about free blocks in automatically space-managed (ASM) segments.

Note that the ALTER TABLE command does not update the bitmap blocks (BMBs) if you use it to update PCTFREE for ASM segments. Run the new procedure DBMS_REPAIR.SEGMENT_FIX_STATUS to remedy this problem.

Memory Management

Memory Management

9i can now dynamically manage the sizing of many of its internal memory work areas. Set the new initialization parameter PGA_AGGREGATE_TARGET to a value indicating total memory for the PGA’s *AREA parameters, then you can set new parameter WORKAREA_SIZE_POLICY to AUTO. The default value for PGA_AGGREGATE_TARGET is 0 – you must set it to some other value first, prior to changing WORKAREA_SIZE_POLICY from MANUAL to AUTO, or else Oracle gives you an error. Both parameters are dynamic so you can set them via the ALTER SYSTEM command if desired.

9i allows you to dynamically re-size the Systems Global Area (SGA), without having to shutdown and restart Oracle. Be sure you have set the new initialization parameter SGA_MAX_SIZE to the maximum SGA size you’ll ever want before doing this.

You can get feedback from Oracle about the use of the buffer cache and advice on sizing the buffer cache by:

1. Set DB_CACHE_ADVICE to ON by:

ALTER SYSTEM SET DB_CACHE_ADVICE=ON;

2. Query performance view V$DB_CACHE_ADVICE for the results

Bitmap Join Indexes

Bitmap Join Indexes

Oracle8i supported bitmap indexes on a single table. 9i adds bitmap join indexes, built on two tables, to optimize access for a specific join condition:

CREATE BITMAP INDEX my_bit_join_ix
ON emp_table (e.job)
FROM emp_table e, dept_table d
WHERE e.deptno = d.deptno ;

Restrictions:

• Can not reference an IOT, temporary table, or a self-join
• Only one table can be updated concurrently by different transactions
• Parallel DML is only supported on the fact table, and the columns in the index must all be columns of the dimension tables
• Join must be on primary key or unique-constraint key, and, if the key is composite, the entire key must be used

Automated Undo Management (AUM)

Automated Undo Management (AUM)

9i’s new feature Automated Undo Management (AUM) relieves you of the traditional, labor-intensive task of sizing and managing rollbacks. To use AUM, create a tablespace that will be used for rollbacks (the UNDO tablespace). Then start the instance with these two new 9i initialization parameters set to:

UNDO_MANAGEMENT = AUTO

UNDO_TABLESPACE = undo_tablespace_name

Once an instance is started with AUM, you can not and do not create or manage rollback segments manually. You can switch to another UNDO tablespace whenever you want, but you can not drop an UNDO tablespace while it has active transactions.

Online Table Redefinition

Online Table Redefinition

9i allows you to redefine a table online. Users can issue DML against the table during most of the online redefinition. For online table redefinition, the table:

• Must have a primary key, and source and target tables for redefinition must have the same primary key column
• Can not contain BFILE or LONG columns, user-defined types, or be part of a cluster or be an overflow table for an IOT
• Can not belong to SYS or SYSTEM schemas, or have materialized views or materialized view logs defined on it

Here is the process of online table redefinition:

1. Run the Oracle procedure DBMS_REDEFINITION.CAN_REDEF_TABLE to verify that the table meets the above conditions and can be redefined online
2. Create an interim table with the same characteristics and in the same schema as the table to redefine
3. Run DBMS_REDEFINITION.START_REDEF_TABLE to start the redefinition process
4. Create indexes, constraints, triggers, and grants with different names on the interim table. DISABLE all constraints (Oracle automatically ENABLEs them for you later).
5. If a large number of DML updates are going against the original table, sync it with the interim table by running DBMS_REDEFINITION.SYNC_INTERIM_TABLE.
6. Complete the redefinition by running DBMS_REDEFINITION.FINISH_REDEF_TABLE. The table is briefly locked in exclusive mode as the original table is redefined with all the characteristics, indexes, constraints, triggers, and grants that you defined on the interim table. Constraints are automatically enabled.

Note that if any problem occurs during the redefinition process, you must run DBMS_REDEFINITION.ABORT_REDEF_TABLE to terminate the redefinition process.

Rebuilding Indexes Online

New Online Operations

Rebuilding Indexes Online

8i introduced the online building of indexes. 9i extends this feature to the online rebuilding of reverse key indexes, function-based indexes, and key-compressed indexes on tables and index-organized tables (IOTs). 9i does not support online index rebuilding for bitmap indexes or partitioned local and global indexes.

While the index is being rebuilt online, users can issue DML against the table but not DDL. Changes that occur during the index rebuild are internally documented by Oracle in an IOT. The syntax to rebuild an index online is:

ALTER INDEX my_index REBUILD ONLINE ;

Managing Index Organized Tables (IOTs) Online

You can now coalesce IOTs online. The coalesce operation defragments the index tree. Do this by:

ALTER TABLE my_iot_table COALESCE ;

You can now create secondary indexes for IOTs while they are in use:

CREATE INDEX my_iot_secondary_index
ON my_iot_table (column_to_index) ONLINE ;

You can also rebuild the IOT’s secondary index while it is online:

ALTER INDEX my_iot_secondary_index REBUILD ONLINE ;

As the index becomes stale, you may update the logical ROWIDs used in the IOT’s secondary index by this statement:

ALTER INDEX my_iot_secondary_index UPDATE BLOCK REFERENCES ;

If you store a non-key column in the primary key B-tree index of an IOT, it reduces the density of the index rows in the leaf blocks. Specify an overflow segment in a move operation to address this problem:

ALTER TABLE my_iot_table MOVE ONLINE
TABLESPACE my_ts
OVERFLOW TABLESPACE my_overflow_ts ;

Fast-Start Time-Based Recovery Limit

Fast-Start Time-Based Recovery Limit

You can specify the target mean time to recover (MTTR) for instance recovery by setting initialization parameter FAST_START_MTTR_TARGET to a value between 0 and 3,600 seconds. Set it dynamically by:

ALTER SYSTEM SET FAST_START_MTTR_TARGET = number_of_seconds ;

A lower value results in more frequent checkpointing by Oracle but a faster instance recovery. You no longer set values for initialization parameters FAST_START_IO_TARGET and LOG_CHECKPOINT_INTERVAL when using this new feature. Oracle uses FAST_START_MTTR_TARGET to calculate and set these values internally. If you do code them, they override the values Oracle would otherwise calculate based on FAST_START_MTTR_TARGET. 9i makes obsolete the initialization parameter DB_BLOCK_MAX_DIRTY_TARGET.

Default Temporary Tablespaces

Default Temporary Tablespaces

If you CREATE USER and forget to include a TEMPORARY TABLESPACE clause, Oracle uses the SYSTEM tablespace for that user’s sorts. This hurts performance. 9i addresses this by allowing you to specify a system-wide default temporary tablespace. Specify the DEFAULT TEMPORARY TABLESPACE on the CREATE DATABASE statement. Or, define the new temporary tablespace by the CREATE TEMPORARY TABLESPACE statement, and make it the default by running:

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE default_temp_ts ;

My Database Has an Archiver Error Whatever Shall I Do?

When the database is complaining that the disk is full due to an archiver error, and there is no-one to get to do the work for you then you can follow the following steps to clear the problem down.

1. df -h
This shows you the mount points and their sizes and usage.

2. rman target /
This connects you to the rman (Recovery MANager) repository - in this case the database controlfile.

3. For a quick win try....
- crosscheck backup;
- crosscheck archivelog all;
- delete noprompt expired;
- delete noprompt obsolete recovery window of 1 days;

4. list archivelog all;
This shows you a list of all the remaining archivelogs on the system. If this list continues beyond a couple of screenfuls or the dates are for more that two days ago then you have candidates for deletion.

5. delete noprompt archivelog until time 'sysdate-2';
Removes archivelogs older than two days.

6. delete noprompt expired;
Removes any exired objects;

7. exit;
Leaves rman.

In enterprise manager the same commands are available in point and click in the maintenance tab under the maintain current backups entry.