Space, Object, and Transaction Management in Oracl

This article describes the new features related to space, object and transaction management in Oracle 10g.

Automatic Undo Retention Tuning

Oracle 10g automatically tunes undo retention to reduce the chances of "snapshot too old" errors during long-running queries. The UNDO_RETENTION parameter is used to set a low retention time threshold which the system will attempt to achieve. In the event of any undo space constraints the system will prioritize DML operations over undo retention meaning the low threshold may not be achieved. If the undo retention threshold must be guaranteed, even at the expense of DML operations, the RETENTION GUARANTEE clause can be set against the undo tablespace during or after creation:

-- Reset the undo low threshold.
ALTER SYSTEM SET UNDO_RETENTION = 2400;

-- Guarantee the minimum threshold is maintained.
ALTER TABLESPACE undotbs1 RETENTION GUARANTEE;

SELECT tablespace_name, retention FROM dba_tablespaces;

TABLESPACE_NAME                RETENTION
------------------------------ -----------
SYSTEM                         NOT APPLY
UNDOTBS1                       GUARANTEE
SYSAUX                         NOT APPLY
TEMP                           NOT APPLY
USERS                          NOT APPLY

5 rows selected.

-- Switch back to the default mode.
ALTER TABLESPACE undotbs1 RETENTION NOGUARANTEE;

TABLESPACE_NAME                RETENTION
------------------------------ -----------
SYSTEM                         NOT APPLY
UNDOTBS1                       NOGUARANTEE
SYSAUX                         NOT APPLY
TEMP                           NOT APPLY
USERS                          NOT APPLY

5 rows selected.

As the name suggests, the NOT APPLY value is assigned to non-undo tablespaces for which this functionality does not apply.

Undo Advisor

The Undo Advisor in Enterprise Manager (Home > Advisor Central > Undo Management > Undo Advisor) provides recommendations for undo configuration. The Undo Advisor is also accessible from PL/SQL using the DBMS_ADVISOR package, but none of the reporting procedures support this advisor so you must access the recommendations from the DBA_ADVISOR_% views manually:

SELECT MIN(snap_id), MAX(snap_id) FROM dba_hist_snapshot;

MIN(SNAP_ID) MAX(SNAP_ID)
------------ ------------
         884         1052

1 row selected.

DECLARE
  l_task_name  VARCHAR2(30) := '884_1052_AWR_SNAPSHOT_UNDO';
  l_object_id  NUMBER;
BEGIN
  -- Create an ADDM task.
  DBMS_ADVISOR.create_task (
    advisor_name      => 'Undo Advisor',
    task_name         => l_task_name,
    task_desc         => 'Undo Advisor Task');

  DBMS_ADVISOR.create_object (
    task_name   => l_task_name,
    object_type => 'UNDO_TBS',
    attr1       => NULL, 
    attr2       => NULL, 
    attr3       => NULL, 
    attr4       => 'null',
    attr5       => NULL,
    object_id   => l_object_id);

  -- Set the target object.
  DBMS_ADVISOR.set_task_parameter (
    task_name => l_task_name,
    parameter => 'TARGET_OBJECTS',
    value     => l_object_id);

  -- Set the start and end snapshots.
  DBMS_ADVISOR.set_task_parameter (
    task_name => l_task_name,
    parameter => 'START_SNAPSHOT',
    value     => 884);

  DBMS_ADVISOR.set_task_parameter (
    task_name => l_task_name,
    parameter => 'END_SNAPSHOT',
    value     => 1052);

  -- Execute the task.
  DBMS_ADVISOR.execute_task(task_name => l_task_name);
END;
/

Segment Advisor

The segment advisor performs analysis on the fragmentation of specified tablespaces, segments or objects and makes recommendations on how space can be reclaimed. The advisor is accessible from Enterprise Manager (Home > Advisor Central > Segment Advisor) or from PL/SQL by using the DBMS_ADVISOR package:

DECLARE
  l_object_id  NUMBER;
BEGIN
  -- Create a segment advisor task for the SCOTT.EMP table.
  DBMS_ADVISOR.create_task (
    advisor_name      => 'Segment Advisor',
    task_name         => 'EMP_SEGMENT_ADVISOR',
    task_desc         => 'Segment Advisor For EMP');

  DBMS_ADVISOR.create_object (
    task_name   => 'EMP_SEGMENT_ADVISOR',
    object_type => 'TABLE',
    attr1       => 'SCOTT', 
    attr2       => 'EMP', 
    attr3       => NULL, 
    attr4       => 'null',
    attr5       => NULL,
    object_id   => l_object_id);

  DBMS_ADVISOR.set_task_parameter (
    task_name => 'EMP_SEGMENT_ADVISOR',
    parameter => 'RECOMMEND_ALL',
    value     => 'TRUE');

  DBMS_ADVISOR.execute_task(task_name => 'EMP_SEGMENT_ADVISOR');

  -- Create a segment advisor task for the USERS tablespace.
  DBMS_ADVISOR.create_task (
    advisor_name      => 'Segment Advisor',
    task_name         => 'USERS_SEGMENT_ADVISOR',
    task_desc         => 'Segment Advisor For USERS');

  DBMS_ADVISOR.create_object (
    task_name   => 'USERS_SEGMENT_ADVISOR',
    object_type => 'TABLESPACE',
    attr1       => 'USERS', 
    attr2       => NULL, 
    attr3       => NULL, 
    attr4       => 'null',
    attr5       => NULL,
    object_id   => l_object_id);

  DBMS_ADVISOR.set_task_parameter (
    task_name => 'USERS_SEGMENT_ADVISOR',
    parameter => 'RECOMMEND_ALL',
    value     => 'TRUE');

  DBMS_ADVISOR.execute_task(task_name => 'USERS_SEGMENT_ADVISOR');
END;
/ 

-- Display the findings.
SET LINESIZE 250
COLUMN task_name FORMAT A20
COLUMN object_type FORMAT A20
COLUMN schema FORMAT A20
COLUMN object_name FORMAT A30
COLUMN object_name FORMAT A30
COLUMN message FORMAT A40
COLUMN more_info FORMAT A40

SELECT f.task_name,
       f.impact,
       o.type AS object_type,
       o.attr1 AS schema,
       o.attr2 AS object_name,
       f.message,
       f.more_info
FROM   dba_advisor_findings f
       JOIN dba_advisor_objects o ON f.object_id = o.object_id AND f.task_name = o.task_name
WHERE  f.task_name IN ('EMP_SEGMENT_ADVISOR', 'USERS_SEGMENT_ADVISOR')
ORDER BY f.task_name, f.impact DESC;

Online Segment Shrink

Based on the recommendations from the segment advisor you can recover space from specific objects using one of the variations of the ALTER TABLE ... SHRINK SPACE command:

-- Enable row movement.
ALTER TABLE scott.emp ENABLE ROW MOVEMENT;

-- Recover space and amend the high water mark (HWM).
ALTER TABLE scott.emp SHRINK SPACE;

-- Recover space, but don't amend the high water mark (HWM).
ALTER TABLE scott.emp SHRINK SPACE COMPACT;

-- Recover space for the object and all dependant objects.
ALTER TABLE scott.emp SHRINK SPACE CASCADE;

The shrink is accomplished by moving rows between blocks, hence the requirement for row movement to be enabled for the shrink to take place. This can cause problem with ROWID based triggers. The shrinking process is only available for objects in tablespaces with automatic segment-space management enabled.

The COMPACT option allows the shrink operation to be broken into two stages. First the rows are moved using the COMPACT option but the HWM is not adjusted so no parsed SQL statements are invalidated. The HWM can be adjusted at a later date by reissuing the statement without the COMPACT option. At this point any depenedant SQL statements will need to be reparsed.

New Segment Resource Estimation

Additions to the DBMS_SPACE package allow segment sizes to be estimated before they are actually created, enabling the DBA to organize disk space in advance:

SET SERVEROUTPUT ON
DECLARE
  l_ddl          VARCHAR2(500);
  l_used_bytes   NUMBER;
  l_alloc_bytes  NUMBER;
BEGIN
  -- Estimate the size of a new table on the USERS tablespace.
  DBMS_SPACE.create_table_cost (
    tablespace_name => 'USERS',
    avg_row_size    => 106,
    row_count       => 1000000,
    pct_free        => 10,
    used_bytes      => l_used_bytes,
    alloc_bytes     => l_alloc_bytes);
    
  DBMS_OUTPUT.put_line ('new table (TS=USERS): used=' || l_used_bytes ||
                        ' bytes  allocated=' || l_alloc_bytes || ' bytes');

  -- Estimate the size of a new index.
  l_ddl := 'CREATE INDEX scott.emp_idx_1 ON scott.emp(job, mgr, hiredate)';
  
  DBMS_SPACE.create_index_cost (
    ddl         => l_ddl,
    used_bytes  => l_used_bytes,
    alloc_bytes => l_alloc_bytes);
    
  DBMS_OUTPUT.put_line ('scott.emp_idx_1     : used=' || l_used_bytes ||
                        ' bytes  allocated=' || l_alloc_bytes || ' bytes');
END;
/

new table (TS=USERS): used=124125184 bytes  allocated=125829120 bytes
scott.emp_idx_1     : used=280 bytes  allocated=65536 bytes

PL/SQL procedure successfully completed.

The OBJECT_GROWTH_TREND pipelined function uses information from the AWR to display information about growth trends for specific objects:

COLUMN timepoint FORMAT A30

SELECT *
FROM   TABLE(DBMS_SPACE.object_growth_trend ('SCOTT','EMP','TABLE'))
ORDER BY timepoint;

TIMEPOINT                      SPACE_USAGE SPACE_ALLOC QUALITY
------------------------------ ----------- ----------- --------------------
11-APR-04 02.55.14.116000 PM          6372       65536 INTERPOLATED
12-APR-04 02.55.14.116000 PM          6372       65536 INTERPOLATED
13-APR-04 02.55.14.116000 PM          6372       65536 INTERPOLATED
...
13-MAY-04 02.55.14.116000 PM          6372       65536 PROJECTED
14-MAY-04 02.55.14.116000 PM          6372       65536 PROJECTED
15-MAY-04 02.55.14.116000 PM          6372       65536 PROJECTED
16-MAY-04 02.55.14.116000 PM          6372       65536 PROJECTED

36 rows selected.

The QUALITY column indicates the quality of the output as follows:

• GOOD - The data for the timepoint relates to data within the AWR repository with a timestamp withinn 10% of the interval.
• INTERPOLATED - The data for this timepoint did not meet the GOOD criteria but was based on data gathered before and after the timepoint.
• PROJECTED - The timepoint is in the future, so the data is estimated based on previous growth statistics.

For further information see:

• DBMS_ADVISOR
• DBMS_SPACE