Gestión de espacio, objetos y transacciones en Oracle Database 10g

Este artículo describe las nuevas funciones relacionadas con la gestión de espacios, objetos y transacciones en Oracle 10g.

  • Sintonización automática de retención de deshacer
  • Asesor de deshacer
  • Asesor de segmento
  • Reducción de segmento online
  • Estimación de recursos de nuevos segmentos

Sintonización automática de retención de deshacer

Oracle 10g ajusta automáticamente la retención de deshacer para reducir las posibilidades de errores de «snapshot too old» durante consultas de larga ejecución. El parámetro UNDO_RETENTION se utiliza para establecer un umbral de tiempo de retención bajo que el sistema intentará alcanzar. En el caso de cualquier restricción de espacio para deshacer, el sistema priorizará las operaciones DML sobre la retención de deshacer, lo que significa que es posible que no se alcance el umbral bajo. Si se debe garantizar el umbral de retención de deshacer, incluso a expensas de las operaciones DML, la cláusula RETENTION GUARANTEE se puede establecer en el espacio de tabla de deshacer durante o después de la creación.

-- 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.

Como sugiere el nombre, el valor NOT APPLY se asigna a los espacios de tabla sin deshacer para los que no se aplica esta funcionalidad.

Asesor de deshacer

El Asesor de deshacer en Enterprise (Home > Advisor Central > Undo Management > Undo Advisor) proporciona recomendaciones para deshacer la configuración. También se puede acceder al Asesor de deshacer desde PL/SQL mediante el paquete DBMS_ADVISOR, pero ninguno de los procedimientos de generación de informes admite este asesor, por lo que debe acceder a las recomendaciones de las vistas DBA_ADVISOR_% manualmente.

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;
/

Asesor de segmento

El asesor de segmentos realiza un análisis de la fragmentación de espacios de tabla, segmentos u objetos específicos y hace recomendaciones sobre cómo se puede recuperar el espacio. Se puede acceder al asesor desde Enterprise Manager (Home > Advisor Central > Segment Advisor) o desde PL/SQL mediante el paquete DBMS_ADVISOR.

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;

Reducción de segmento online

Esta sección se ha movido y expandido a un artículo separado.

ALTER TABLE … SHRINK SPACE Command: Reducción de segmento en línea para tablas, LOB e IOT
Según las recomendaciones del asesor de segmento, puede recuperar espacio de objetos específicos utilizando una de las variaciones del comando ALTER TABLE … SHRINK SPACE.

-- 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;

La contracción se logra moviendo filas entre bloques, de ahí el requisito de que se habilite el movimiento de fila para que se produzca la contracción. Esto puede causar problemas con los disparadores basados en ROWID. El proceso de reducción solo está disponible para objetos en espacios de tabla con la gestión automática del espacio de segmento habilitada.

La opción COMPACT permite dividir la operación de contracción en dos etapas. Primero, las filas se mueven usando la opción COMPACT, pero el HWM no se ajusta, por lo que no se invalidan las declaraciones SQL analizadas. El HWM se puede ajustar en una fecha posterior volviendo a emitir el estado de cuenta sin la opción COMPACT. En este punto, será necesario volver a analizar cualquier instrucción SQL dependiente.

A continuación se muestran otros comandos de reducción de interés.

-- Shrink a LOB segment.
ALTER TABLE table_name MODIFY LOB(lob_column) (SHRINK SPACE CASCADE);

-- Shrink an IOT overflow segment.
ALTER TABLE iot_name OVERFLOW SHRINK SPACE;

Estimación de recursos de nuevos segmentos

Las adiciones al paquete DBMS_SPACE permiten estimar los tamaños de los segmentos antes de que se creen realmente, lo que permite al DBA organizar el espacio en disco por adelantado.

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.

La función canalizada OBJECT_GROWTH_TREND utiliza información de AWR para mostrar información sobre las tendencias de crecimiento de objetos específicos.

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.

La columna QUALITY indica la calidad de la salida de la siguiente manera.

  • GOOD – los datos para el punto de tiempo se relacionan con los datos dentro del repositorio de AWR con una marca de tiempo dentro del 10% del intervalo.
  • INTERPOLATED – Los datos para este punto de tiempo no cumplieron con los criterios BUENOS pero se basaron en datos recopilados antes y después del punto de tiempo.
  • PROJECTED – el punto de tiempo está en el futuro, por lo que los datos se estiman en función de las estadísticas de crecimiento anteriores.

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *