1. Documentación en Tahiti -> Masters Book List -> Administrator’s Guide -> 14 Managing Tablespaces
2. Resumen de las distintas configuraciones a la hora de crear tablespace permanentes
LOCALLY MANAGED TABLESPACE | DICTIONARY MANAGED TABLESPACE -------------------------------------- -------------------------------------- | AUTOMATIC SEGMENT | MANUAL SEGMENT | Los EXTENTS se gestionan a través | | SPACE MANAGEMENT | SPACE MANAGEMENT | del Diccionario de Datos | | (ASSM) | (MSSM) | (En desuso) | ------------------- ------------------ -------------------------------------- | Utiliza Bitmaps | Utiliza Freelists| Utiliza Dict. => SYS.EUT$ y SYS.FET$ | ------------------- ------------------ --------------------------------------
3. Empezamos creando diferentes tipos de TABLESPACE permanentes
-- Creamos un TBS sin ningún opción para ver como lo crea por defecto
-- Esta sentencia es equivalente a esta otra
-- CREATE TABLESPACE TEST01 DATAFILE '/u01/app/oracle/oradata/OCM/test01.dbf' SIZE 100M
-- SEGMENT SPACE MANAGEMENT AUTO EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
CREATE TABLESPACE TEST01 DATAFILE '/u01/app/oracle/oradata/OCM/test01.dbf' SIZE 100M;
-- Comprobamos cómo se ha creado este TABLESPACE (TEST01) y revisamos el resto de TBS de la BD
-- Por defecto, si no específicas las propiedades => LOCALLY MANAGED + ASSM + AUTOALLOCATE (SYSTEM)
SELECT
TABLESPACE_NAME,
BLOCK_SIZE,
EXTENT_MANAGEMENT,
SEGMENT_SPACE_MANAGEMENT,
ALLOCATION_TYPE,
BIGFILE
FROM
DBA_TABLESPACES;
-- Ahora crearemos un TBS LOCALLY MANAGED + ASSM + ALLOCATE UNIFORM (Extents de 1M)
CREATE TABLESPACE TEST02 DATAFILE '/u01/app/oracle/oradata/OCM/test02.dbf' SIZE 100M
SEGMENT SPACE MANAGEMENT AUTO EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
-- Creamos un tercer TBS LOCALLY MANAGED + MSSM + AUTOALLOCATE
CREATE TABLESPACE TEST03 DATAFILE '/u01/app/oracle/oradata/OCM/test03.dbf' SIZE 100M
SEGMENT SPACE MANAGEMENT MANUAL EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
-- Por defecto los TBS se crean con sus DATAFILES en modo NOAUTOEXTENSIBLE, es decir, su tamaño es fijo
-- Creamos un cuarto TBS con un DATAFILE AUTOEXTENSIBLE con un tamaño máximo de 2G
CREATE TABLESPACE TEST04 DATAFILE '/u01/app/oracle/oradata/OCM/test04.dbf' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE 2G;
-- Con MSSM, Oracle utiliza FREELISTS para saber que bloques están libres
-- Con ASSM, Oracle se encarga de la gestión libre en los bloques usando Bitmaps
-- Hay un parámetro crucial, PCTFREE, que especifica el espacio libre para futuros UPDATES, tanto en ASSM como en MSSM
-- Vamos a hacer una prueba llenando dos tablas con diferentes valores para PCTFREE y ver lo que ocupan
CREATE TABLE TEST_PCTFREE_0 TABLESPACE TEST01 PCTFREE 0 AS SELECT * FROM DBA_OBJECTS;
CREATE TABLE TEST_PCTFREE_50 TABLESPACE TEST01 PCTFREE 50 AS SELECT * FROM DBA_OBJECTS;
-- Pasamos estadísticas a las tablas
ANALYZE TABLE TEST_PCTFREE_0 COMPUTE STATISTICS;
ANALYZE TABLE TEST_PCTFREE_50 COMPUTE STATISTICS;
-- Comprobamos como diferentes valores para PCTFREE pueden provocar diferentes ocupaciones para una tabla
SELECT
TABLE_NAME,
NUM_ROWS,
BLOCKS,
AVG_SPACE,
PCT_FREE
FROM
DBA_TABLES
WHERE
TABLE_NAME IN ('TEST_PCTFREE_0','TEST_PCTFREE_50');
4. Hasta ahora estamos especificando la ubicación de los DATAFILES en cada TBS que creamos. Pero podemos usar Oracle Managed Files (OMF) si queremos evitar esta tarea.
-- Necesitamos especificar la ruta por defecto para OMF ALTER SYSTEM SET DB_CREATE_FILE_DEST='/u01/app/oracle/oradata/OCM/' SCOPE=BOTH; -- Creamos un quinto TBS sin especificar la ubicación de sus datafiles -- Esto crea un sólo datafile AUTOEXTENSIBLE de 100M y tamaño máximo 32GB CREATE TABLESPACE TEST05; -- Comprobamos las características de DATAFILE creado SELECT FILE_NAME, BYTES/1024/1024 "MB", AUTOEXTENSIBLE, MAXBYTES/1024/1024 "MB" FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='TEST05';
5. En el Objetivo «Determine and set sizing parameters for database structures» hemos visto como crear TBS de diferente tamaño de bloque.
-- Hagamos otra prueba rápida creando un TBS con el tamaño de bloque de 16K ALTER SYSTEM SET DB_16K_CACHE_SIZE=50M SCOPE=BOTH; CREATE TABLESPACE TEST06 BLOCKSIZE 16K;
6. El último ejercicio que vamos a hacer con TBS permanentes es crear un TABLESPACE BIGFILE. La ventaja de usar TBS BIGFILE es que podemos tener DATAFILES enormes (si usamos tamaño de bloque de 8k pueden tener hasta 32TB). A esto le ves la utilidad cuando tienes una BD con 40.000 datafiles y tardas 30 min. en levantarla porque se tiene que recorrer todos.
-- Creamos un TBS indicando la clausula BIGFILE CREATE BIGFILE TABLESPACE TEST07 DATAFILE '/u01/app/oracle/oradata/OCM/test07.dbf' SIZE 100M; -- Volvemos a ver las características de todos los TBS creados SELECT TABLESPACE_NAME, BLOCK_SIZE, EXTENT_MANAGEMENT, SEGMENT_SPACE_MANAGEMENT, ALLOCATION_TYPE, BIGFILE FROM DBA_TABLESPACES;
7. Limpiamos el entorno de las pruebas con TBS permanentes.
-- Borramos los TBS y su contenido DROP TABLESPACE TEST01 INCLUDING CONTENTS AND DATAFILES; DROP TABLESPACE TEST02 INCLUDING CONTENTS AND DATAFILES; DROP TABLESPACE TEST03 INCLUDING CONTENTS AND DATAFILES; DROP TABLESPACE TEST04 INCLUDING CONTENTS AND DATAFILES; DROP TABLESPACE TEST05 INCLUDING CONTENTS AND DATAFILES; DROP TABLESPACE TEST06 INCLUDING CONTENTS AND DATAFILES; DROP TABLESPACE TEST07 INCLUDING CONTENTS AND DATAFILES; -- Revertimos los parámetros que hemos modificado ALTER SYSTEM RESET DB_16K_CACHE_SIZE SCOPE=SPFILE; ALTER SYSTEM SET DB_16K_CACHE_SIZE=0 SCOPE=MEMORY; ALTER SYSTEM RESET DB_CREATE_FILE_DEST SCOPE=SPFILE; ALTER SYSTEM SET DB_CREATE_FILE_DEST='' SCOPE=MEMORY;
8. Hagamos pruebas con TBS Temporales (TEMPORARY TABLESPACE).
-- Lo primero que hacemos es ver cuál es el TBS temporal por defecto SELECT PROPERTY_NAME, PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DEFAULT_TEMP_TABLESPACE';
9. Vamos a crear un grupo de TBS temporales añadiendo el TBS TEMP y uno nuevo que crearemos ahora.
-- Añadimos el TBS temporal actual (TEMP) a un grupo nuevo (TEMPGROUP) ALTER TABLESPACE TEMP TABLESPACE GROUP TEMPGROUP; -- Confirmamos la operación SELECT * FROM DBA_TABLESPACE_GROUPS; -- Creamos un segundo TBS temporal, y lo añadimos directamente al grup en la misma setencia CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE '/u01/app/oracle/oradata/OCM/temp201.dbf' SIZE 100M TABLESPACE GROUP TEMPGROUP; -- Validamos la operación SELECT * FROM DBA_TABLESPACE_GROUPS; -- También podemos especificar el grupo de TBS temporales como el TBS temporal por defecto ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMPGROUP; -- Revisamos el cambio SELECT PROPERTY_NAME, PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DEFAULT_TEMP_TABLESPACE'; -- Revertimos todos los cambios ALTER TABLESPACE TEMP TABLESPACE GROUP ''; ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP; DROP TABLESPACE TEMP2 INCLUDING CONTENTS AND DATAFILES;
10. En la versión 11g de Oracle podemos reducir el tablespace temporal. Incluso podemos fijar un mínimo de espacio reservado.
-- Ampliamos el TBS temporal antes de recortarlo para hacer las pruebas ALTER DATABASE TEMPFILE '/u01/app/oracle/oradata/OCM/temp01.dbf' RESIZE 200M; -- Podemos comprobar el tamaño con la siguiente consulta SELECT BYTES/1024/1024 FROM DBA_TEMP_FILES; -- Cortamos el tablespace temporal hasta un límite de 10M y consultamos el tamaño ALTER TABLESPACE TEMP SHRINK SPACE KEEP 10M; SELECT BYTES/1024/1024 FROM DBA_TEMP_FILES; -- Cortamos al máximo el TBS temporal y revisamos el espacio que ocupa ahora ALTER TABLESPACE TEMP SHRINK SPACE; SELECT BYTES/1024/1024 FROM DBA_TEMP_FILES;
11. En ocasiones resulta útil ver el espacio usado en el TBS temporal por los diferentes usuarios de la BD.
-- Podemos ver un resumen rápido del espacio total, asignado y espacio libre a través de la vista DBA_TEMP_FREE_SPACE SELECT * from DBA_TEMP_FREE_SPACE; -- Si queremos obtener el espacio usado del TBS temporal por sesión, podemos utilizar V$SORT_SEGMENT y V$TEMPSEG_USAGE -- Desde otra sesión lanzamos una consulta que necesite gran espacio para realizar sorts SELECT A.OBJECT_ID FROM DBA_OBJECTS A, DBA_OBJECTS B ORDER BY OBJECT_ID; -- Mientras realiza la ordenación podemos ver la ocupación de la sesión en el TBS temporal (recordad que este TBS es de 8k=8192) SELECT USER, SESSION_NUM "SERIAL#", SQL_ID, BLOCKS*8192/1024/1024 "MB" FROM V$TEMPSEG_USAGE; -- Podemos cancelar la consulta y recortar el TBS temporal ALTER TABLESPACE TEMP SHRINK SPACE;
12. Ahora vamos a hacer ejercicios con el TBS de UNDO
Documentación en Tahiti -> Masters Book List -> Administrator’s Guide -> 16 Managing Undo
13. Tenemos tres parámetros fundamentales que controlan la gestión y retención del TBS de UNDO
-- Mostramos los valores de estos tres parámetros
-- UNDO_MANAGEMENT = AUTO => Es el valor por defecto. Oracle se encarga de la gestión de los segmentos de Undo
En modo MANUAL se utilizarían los segmentos de ROLLBACK -> No recomendable
-- UNDO_RETENTION = 900 => Tiempo que Oracle INTENTA retener la información de UNDO (segundos)
-- UNDO_TABLESPACE = UNDOTBS1 => Nombre del TBS de UNDO. Si no se especifica Oracle utiliza el primero que encuentra
show parameter undo
14. El tamaño del TBS de UNDO y la retención recomendada puede variar en cada BD. Una forma de estimar el tamaño del TBS de UNDO es fijar la retención a un valor de 5-6 horas y comprobar el tamaño del TBS.
-- Ampliamos la retención (5h * 60min * 60seg = 18000) -- Cuando los DATAFILES del TBS de UNDO no están en modo AUTOEXTEND, el parámetro UNDO_RETENTION es ignorado ALTER SYSTEM SET UNDO_RETENTION=18000 SCOPE=BOTH; -- Después de 5 horas, observamos el tamaño del TBS de UNDO y eliminamos el AUTOEXTEND de los datafiles ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/OCM/undotbs01.dbf' AUTOEXTEND OFF; -- Otra forma rápida de obtener una recomendación es utilizar la vista V$UNDOSTAT -- Primero obtenemos el pico de UNDO utilizado por segundo SELECT MAX(UNDOBLKS/((END_TIME-BEGIN_TIME)*24*60*60)) "MAXUNDOPERSEC" FROM V$UNDOSTAT; -- Después obtenemos la Query con mayor tiempo de ejecución SELECT MAX(MAXQUERYLEN) FROM V$UNDOSTAT; -- El valor recomendado es MAXUNDOPERSEC * MAXQUERYLEN * DB_BLOCK_SIZE -- Lo podemos hacer todo en una sola consulta SELECT (SELECT MAX(UNDOBLKS/((END_TIME-BEGIN_TIME)*24*60*60)) "MAXUNDOPERSEC" FROM V$UNDOSTAT) * (SELECT MAX(MAXQUERYLEN) FROM V$UNDOSTAT) * (SELECT DISPLAY_VALUE FROM V$PARAMETER WHERE NAME='db_block_size') / 1024 / 1024 "UNDO ADVICE (MB)" FROM DUAL; -- También se puede obtener este información con el paquete DBMS_UNDO_ADV
15. También podemos utilizar el ADVISOR de UNDO del Enterprise Manager o a través del interfaz. Cuando instalemos Grid Control haremos pruebas a través de web. Ahora veremos la forma de hacerlo utilizando DBMS_ADVISOR.
-- Primero tenemos que obtener el intervalo de Snapshots de donde queremos hacer el estudio
-- Para ello sacamos el listado de SNAPSHOTS de AWR y escogemos aquellos que nos interesen por la ventana de tiempo
-- Yo voy a escoger los dos últimos SNAP_ID correspondientes a la última hora (228 y 229)
SELECT SNAP_ID, END_INTERVAL_TIME FROM DBA_HIST_SNAPSHOT ORDER BY 2;
-- Lanzamos el ADVISOR. Fijaros en los SNAPSHOTS que hemos escogido, 228 y 229
DECLARE
tid NUMBER;
tname VARCHAR2(30);
oid NUMBER;
BEGIN
DBMS_ADVISOR.CREATE_TASK('Undo Advisor', tid, tname, 'Undo Advisor Task');
DBMS_ADVISOR.CREATE_OBJECT(tname, 'UNDO_TBS', null, null, null, 'null', oid);
DBMS_ADVISOR.SET_TASK_PARAMETER(tname, 'TARGET_OBJECTS', oid);
DBMS_ADVISOR.SET_TASK_PARAMETER(tname, 'START_SNAPSHOT', 228);
DBMS_ADVISOR.SET_TASK_PARAMETER(tname, 'END_SNAPSHOT', 229);
DBMS_ADVISOR.SET_TASK_PARAMETER(tname, 'INSTANCE', 1);
DBMS_ADVISOR.execute_task(tname);
END;
/
-- Podemos hacer un seguimiento de la ejecución de la tarea en DBA_ADVISOR_TASKS
SELECT TASK_NAME, STATUS FROM DBA_ADVISOR_TASKS WHERE DESCRIPTION='Undo Advisor Task';
-- Una vez ha finalizado podemos sacar las averiguaciones (FINDINGS)
-- El nombre de la tarea (TASK_NAME) lo sacamos de la anterior consulta
-- Es mucho más cómodo usar el Enterprise Manager (EM), pero ya lo veremos más adelante
SELECT * FROM DBA_ADVISOR_FINDINGS WHERE TASK_NAME='TASK_329';
16. En cualquier momento puedes consultar la utilización de UNDO a través de la vista DBA_UNDO_STATS.
-- Un EXTENT de UNDO puede estar ACTIVO, EXPIRED o UNEXPIRED -- UNEXPIRED => EXTENTS reutilizables sólo si el TBS de UNDO está en modo NOGUARANTEE -- EXPIRED => EXTENTS de UNDO reutilizables en cualquier caso -- ACTIVE => UNDO en uso actualmente SELECT STATUS, SUM(BYTES)/1024/1024 MB FROM DBA_UNDO_EXTENTS GROUP BY STATUS;
17. Hemos visto en el anterior punto que el TBS de UNDO puede estar en modo GUARANTEE o NOGUARANTEE. Activaremos el modo GUARANTEE cuando queramos que ciertas sentencias muy pesadas finalicen a expensas de que otras fallen (ORA-30036). Cuando ejecutamos una sentencia muy pesada y falla porque el UNDO que necesita ha sido reutilizado falla por el error ORA-1555.
# No voy a entrar en los errores ORA-1555 y ORA-30036 porque hay mil páginas web que hablan de ello # Cuando os encontréis un error, podéis usar la utilidad "oerr" para sacar más información que el propio mensaje de error oerr ora 1555 # Os sugiere aumentar el parámetro UNDO_RETENTION o ampliar el UNDO oerr ora 30036 # Os sugiere ampliar el TBS de UNDO
-- Si fuera necesario podemos poner el TBS de UNDO en modo GUARANTE/NOGUARANTEE de forma muy sencilla ALTER TABLESPACE UNDOTBS1 RETENTION GUARANTEE; -- Desactivamos el modo GUARANTEE ALTER TABLESPACE UNDOTBS1 RETENTION NOGUARANTEE;
18. Ahora vamos a cambiar el TBS de UNDO. Para ello tenemos que crear un TBS nuevo y hacer el cambio.
-- Creamos un TBS de UNDO
CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE '/u01/app/oracle/oradata/OCM/undotbs201.dbf'
SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;
-- Cambios el parámetro de undo_tablespace = UNDOTBS2
ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS2 SCOPE=BOTH;
-- Borramos el TBS antiguo
DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES;
-- Revertiremos el cambio y añadimos un segundo DATAFILE al TBS de UNDO
CREATE UNDO TABLESPACE UNDOTBS1 DATAFILE '/u01/app/oracle/oradata/OCM/undotbs01.dbf'
SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;
ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS1 SCOPE=BOTH;
DROP TABLESPACE UNDOTBS2 INCLUDING CONTENTS AND DATAFILES;
ALTER TABLESPACE UNDOTBS1 ADD DATAFILE '/u01/app/oracle/oradata/OCM/undotbs02.dbf'
SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;