Oracledbacr, por simple pasión ...-Copyleft Miembro Comunidad Tecnológica de Oracle Latinoamérica

Afinamiento de Tablas con Excesivos Borrados
Utilizando Oracle 10G
Ronald Fco. Vargas Quesada

Tip: Manejo de la “Marca de Agua” en tablas con muchos borrados en 10G.

REFERENCIAS:

Metalink Notes:
242736.1 10G New Features, On Segment Advisor 17/12/2004
242090.1 10G New Features, Segment Shrink
115586.1 How to deallocate unused space from table, index or cluster.
Bug 3516451.8 13/01/2005
Oracle Database 10G: New Features, Robert G. Freeman, OSBORNE
Capítulo 2, páginas 37 y 38, Shrinking and Compacting Segments Online.

En la mayoría de los casos, encontramos tablas que son utilizadas dentro de procesos de cierre de mes ó de pases a históricos, en las cuáles se efectúan borrados parciales de los registros de la tabla.

Cuando en un proceso se borran registros de forma parcial, la “Marca de Agua” no es reseteada, por tanto, los nuevos registros son insertados en bloques que se encuentran posteriores a esta y no en los bloques de los segmentos que fueron liberados con la acción del borrado.

Al pasar el tiempo, los procesos que involucran tablas con estas características especiales de insercciones y borrados, empiezan a experimentar problemas de rendimiento.

La mayoría de las veces, procedemos a recrear la tabla con nuevos parámetros de almacenamiento, para resetear la “Marca de Agua”, ó bien optamos por exportar e importar el objeto afectado.

Afortunamente, este proceso ahora, puede ser realizado de una manera más ágil, utilizando la nueva opción de “SHRINK” en el comando “ALTER TABLE”, que permite compactar los segmentos de un objeto y ajustar la “Marca de Agua” al mismo tiempo, eso si, aplicando ciertas restricciones.

CONECTÁNDONOS A LA BASE DE DATOS

Para probar esto, utilizaremos una base de datos 10G R1, versión 10.1.0.2.0 en un LINUX Red
Hat 3.0 AS.

Logee en la instancia con el usuario “hr” y el password “hr”. Recuerde que en 10G, la mayoría de
usuarios creados durante el proceso de instalación de la base de datos, excepto “SYSTEM y SYS”,quedan bloqueados de facto. Así que antes de hacer la prueba, desbloquee el usuario.

SQL> connect hr/hr
Connected.
SQL> desc employees
Name Null? Type
--------------------------------------- -------- ----------------------------
EMPLOYEE_ID NOT NULL NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
EMAIL NOT NULL VARCHAR2(25)
PHONE_NUMBER VARCHAR2(20)
HIRE_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2(10)
SALARY NUMBER(8,2)
COMMISSION_PCT NUMBER(2,2)
MANAGER_ID NUMBER(6)
DEPARTMENT_ID NUMBER(4)

CREACIÓN DE OBJETOS DE PRUEBA

Basándonos en la tabla “employee”, vamos a crear la tabla “test”, para simular el efecto de borrado de los registros.

SQL> create table test as select * from employees;
Table created.
SQL> select count(*) from test;
COUNT(*)
----------
107

Ahora insertemos algunos registros adicionales.

SQL> ed
1 declare
2 begin
3 for i in 1..10000 loop
4 insert into test select * from employees;
5 end loop;
6* end;

SQL> /
PL/SQL procedure successfully completed.

SQL> select count(*) from test;
COUNT(*)
----------
1070107

Determinando el espacio ocupado por el objeto
Ahora determinemos cuál es el espacio ocupado por la tabla “TEST” en la base de datos, utilizando el siguiente script:

set serveroutput on
declare
TOTAL_BLOCKS number;
TOTAL_BYTES number;
UNUSED_BLOCKS number;
UNUSED_BYTES number;
LAST_USED_EXTENT_FILE_ID number;
LAST_USED_EXTENT_BLOCK_ID number;
LAST_USED_BLOCK number;
begin
dbms_space.unused_space('HR','TEST','TABLE',
TOTAL_BLOCKS, TOTAL_BYTES, UNUSED_BLOCKS, UNUSED_BYTES,
LAST_USED_EXTENT_FILE_ID, LAST_USED_EXTENT_BLOCK_ID,
LAST_USED_BLOCK);
dbms_output.put_line('OBJECT_NAME = TEST');
dbms_output.put_line('-----------------------------------');
dbms_output.put_line('TOTAL_BLOCKS = 'TOTAL_BLOCKS);
dbms_output.put_line('TOTAL_BYTES = 'TOTAL_BYTES);
dbms_output.put_line('UNUSED_BLOCKS = 'UNUSED_BLOCKS);
dbms_output.put_line('UNUSED BYTES = 'UNUSED_BYTES);
dbms_output.put_line('LAST_USED_EXTENT_FILE_ID = 'LAST_USED_EXTENT_FILE_ID);
dbms_output.put_line('LAST_USED_EXTENT_BLOCK_ID = 'LAST_USED_EXTENT_BLOCK_ID);
dbms_output.put_line('LAST_USED_BLOCK = 'LAST_USED_BLOCK);
end;
/
Al ejecutar el script en la base de datos, obtenemos la siguiente información:

SQL> set serveroutput on
SQL> set linesize 120
SQL> /

OBJECT_NAME = TEST
-----------------------------------
TOTAL_BLOCKS = 11264
TOTAL_BYTES = 92274688
UNUSED_BLOCKS = 256
UNUSED BYTES = 2097152
LAST_USED_EXTENT_FILE_ID = 4
LAST_USED_EXTENT_BLOCK_ID = 2184
LAST_USED_BLOCK = 768

PL/SQL procedure successfully completed.

BORRANDO REGISTROS EN LA TABLA DE PRUEBA

Si realizamos un borrado de aquellos registros cuyo "rownum <> "

SQL> delete from test
2 where rownum <> commit;
Commit complete.

SQL> select count(*) from test;
COUNT(*)
----------
220108
SQL> /
OBJECT_NAME = TEST
-----------------------------------
TOTAL_BLOCKS = 11264
TOTAL_BYTES = 92274688
UNUSED_BLOCKS = 256
UNUSED BYTES = 2097152
LAST_USED_EXTENT_FILE_ID = 4
LAST_USED_EXTENT_BLOCK_ID = 2184
LAST_USED_BLOCK = 768

PL/SQL procedure successfully completed.

Como se puede observar la cantidad de bloques ocupados por la tabla sigue siendo igual, a la cantidad de bloques que teníamos previo al borrado. Insertemos nuevos registros en la tabla.

SQL> declare
2 begin
3 for i in 1..10 loop
4 insert into test select * from employees;
5 end loop;
6 end;
7 /

PL/SQL procedure successfully completed.

SQL> select count(*) from test;
COUNT(*)
----------
221178

Al verificar los bloques utilizados por la tabla “TEST”, posterior a la nueva insercción, encontramos que la cantidad de bloques ocupados sigue siendo la misma. Volvamos a repetir el proceso de inserción, pero esta vez, vamos a insertar más cantidad de registros.

OBJECT_NAME = TEST
-----------------------------------
TOTAL_BLOCKS = 11264
TOTAL_BYTES = 92274688
UNUSED_BLOCKS = 256
UNUSED BYTES = 2097152
LAST_USED_EXTENT_FILE_ID = 4
LAST_USED_EXTENT_BLOCK_ID = 2184
LAST_USED_BLOCK = 768
PL/SQL procedure successfully completed.

Esta vez, vamos a repetir el ciclo de inserción en 1000 veces, para insertar 100000 registros
apróximadamente.

SQL> declare
2 begin
3 for i in 1..1000 loop
4 insert into test select * from employees;
5 end loop;
6 end;
7 /

PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
OBJECT_NAME = TEST
-----------------------------------
TOTAL_BLOCKS = 11264
TOTAL_BYTES = 92274688
UNUSED_BLOCKS = 256
UNUSED BYTES = 2097152
LAST_USED_EXTENT_FILE_ID = 4
LAST_USED_EXTENT_BLOCK_ID = 2184
LAST_USED_BLOCK = 768
PL/SQL procedure successfully completed.
SQL> select count(*) from test;
COUNT(*)
----------
328178

Como se observa el objeto no creció, pero ahora tenemos 328178 en 92MB ocupados por la tabla y según nuestros cálculos, los 328178 registros, deberían ocupar apróximadamente una tercera parte de este espacio.

APLICANDO LA NUEVA CARACTERÍSTICA DE “SHRINK”.

Primero que todo, debemos habilitar la posibilidad de mover registros entre bloques del objeto,al igual que lo hacemos, cuando queremos reparar registros encadenados en una tabla, con la siguiente instrucción:

SQL> alter table test enable row movement;
Table altered.

Ahora procedamos a ejecutar el “SHRINK” de la tabla.

SQL> alter table test shrink space cascade;
Table altered.

Al ejecutar nuevamente el script para obtener los bloques utilizados por el tabla, obtenemos:

OBJECT_NAME = TEST
-----------------------------------
TOTAL_BLOCKS = 3160
TOTAL_BYTES = 25886720
UNUSED_BLOCKS = 0
UNUSED BYTES = 0
LAST_USED_EXTENT_FILE_ID = 4
LAST_USED_EXTENT_BLOCK_ID = 6280
LAST_USED_BLOCK = 88

PL/SQL procedure successfully completed.

Ahora podemos observar cómo el tamaño de bloques que componen el objeto a disminuído de
11264 a 3160, y por supuesto su tamaño también a cambiado de 92MB a 26MB.

Optimización Basada en Costo ( CBO )
Ahora veamos esto a nivel de rendimiento. Borremos la tabla y volvamos a crearla con un índice sobre los campos “employee_id, first_name y last_name”.

SQL> create index PK_test_IDX01 on test(employee_id, first_name, last_name);
Index created.
SQL> declare
2 begin
3 for i in 1..10000 loop
4 insert into test select * from employees;
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.

Procedemos a fijar los nuevos registros en la tabla de pruebas.

SQL> SQL> commit;
Commit complete.

Verificamos la cantidad de registros existentes en la tabla “TEST”.

SQL> select count(*) from test;
COUNT(*)
----------
1070107

Obtengamos el plan de ejecución de la siguiente instrucción sobre la tabla “TEST” utilizando el índice que hemos creado previamente.

SQL> explain plan for
2 select employee_id, first_name, last_name, job_id, salary from test
3 where employee_id=100;

Ahora desplegamos el contenido del “Explain Plan”.

SQL> select * from table ( dbms_xplan.display);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
Plan hash value: 2321975210
---------------------------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
---------------------------------------------------------------------------------------------
0 SELECT STATEMENT 10347 596K 71 (0) 00:00:01
1 TABLE ACCESS BY INDEX ROWID TEST 10347 596K 71 (0) 00:00:01
* 2 INDEX RANGE SCAN PK_TEST_IDX01 10347 70 (0) 00:00:01
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
---------------------------------------------------
2 - access("EMPLOYEE_ID"=100)
Note
-----
- dynamic sampling used for this statement
18 rows selected.

SQL> delete from test
2 where rownum <>

SQL> commit;

Commit complete.
SQL> explain plan for
2 select employee_id, first_name, last_name, job_id, salary from test
3 where employee_id=100;
Explained.

SQL> select * from table ( dbms_xplan.display);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
Plan hash value: 2321975210
-------------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
-------------------------------------------------------------------------------
0 SELECT STATEMENT 2056 118K 62 (0) 00:00:01
1 TABLE ACCESS BY INDEX ROWID TEST 2056 118K 62 (0) 00:00:01
* 2 INDEX RANGE SCAN PK_TEST_IDX01 2056 61 (0) 00:00:01
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
---------------------------------------------------
2 - access("EMPLOYEE_ID"=100)
Note
-----
- dynamic sampling used for this statement
18 rows selected.
SQL> alter table test enable row movement;
Table altered.
SQL> alter table test shrink space cascade;
Table altered.
SQL> explain plan for
2 select employee_id, first_name, last_name, job_id, salary from test
3 where employee_id=100;
Explained.
SQL> select * from table ( dbms_xplan.display );

PLAN_TABLE_OUTPUT
------------------------------------------
Plan hash value: 2321975210
---------------------------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
---------------------------------------------------------------------------------------------
0 SELECT STATEMENT 2056 118K 15 (0) 00:00:01
1 TABLE ACCESS BY INDEX ROWID TEST 2056 118K 15 (0) 00:00:01
* 2 INDEX RANGE SCAN PK_TEST_IDX01 2056 14 (0) 00:00:01
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
---------------------------------------------------
2 - access("EMPLOYEE_ID"=100)
Note
-----
- dynamic sampling used for this statement
18 rows selected.
OBJECT_NAME = PK_TEST_IDX01
-----------------------------------
TOTAL_BLOCKS = 976
TOTAL_BYTES = 7995392
UNUSED_BLOCKS = 6
UNUSED BYTES = 49152
LAST_USED_EXTENT_FILE_ID = 4
LAST_USED_EXTENT_BLOCK_ID = 2696
LAST_USED_BLOCK = 74
PL/SQL procedure successfully completed.

CONSIDERACIONES
Dentro de las consideraciones que se debe tener, es que este procedimiento no puede ser utilizado en los siguientes casos:

• 
  • Tablas en Clusters
  • Cualquier tabla con un campo LONG
  • Tablas con Vistas Materializadas con la opción on-commit.
  • Cualquier tabla con una Vista Materializada basada en ROWID.
  • Un índice tipo LOB
  • IOT
  • Segmentos LOB compartidos
  • Segmentos temporales y de UNDO.