Mejorando el rendimiento en un Real Application Cluster (RAC) usando Tablespaces Temporales Locales
Revisado por Francisco Riccio
Introducción
Un tablespace temporal local (Local Temporary Tablespace) almacena archivos temporales separados, no compartidos para cada instancia de la base de datos dentro de un ambiente clusterizado (RAC). Un tablespace temporal local se usa para depositar resultados temporales de consultas SQL que involucren ordenamientos, agregaciones hash y uniones. Estos resultados son accesibles solo por la instancia que invocó la consulta. En contraste, los tablespace temporales compartidos (default) residen en un disco compartido y están disponibles para todas las instancias dentro del RAC.
El concepto de Tablespace Temporal Local es nuevo en Oracle Database 12cR2 (12.2.0.1.0). En releases previos, los tablespaces temporales compartidos eran simplemente llamados tablespaces temporales. Así que, iniciando con este reléase, el termino tablespace temporal puede referirse a un tablespace temporal compartido a menos que se especifique el tipo.
Es posible crear tablespaces temporales para instancias en modo read/only o read/write. Cuando muchas instancias read/only acceden una base de datos, el uso de tablespaces temporales locales puede mejorar el desempeño de consultas que requieren ordenamientos, agregaciones hash y joins.
Beneficios de usar Tablespaces Temporales Locales
- Evita sobrecarga en encolamiento de control file (CF enqueue) en un ambiente clusterizado.
- Mejora el rendimiento de I/O (E/S) mediante la utilización de almacenamiento de disco local.
- Evita los costos asociados a manejo de recursos temporales (red, CPU) entre instancias dentro del clúster.
- Mejora el rendimiento durante el encendido de bases de datos eliminando la gestión de metadatos en espacio de disco.
Comparación de Tablespace Temporal Local Vs Tablespace Temporal
- Cuando se crea un Tablespace Temporal Local, este creara un solo BigFile por tablespace.
- Cuando un usuario esté conectado a un nodo solo de lectura se utilizará el Tablespace temporal local.
- Cuando un usuario esté conectado a un nodo de lectura/escritura se utilizará el Tablespace temporal compartido.
Valores de la propiedad SHARED en un Tablespace Temporal
Con la adición de este nuevo tipo de Tablespace Temporal, se agregó una nueva característica visible desde la vista DBA_TEMP_FILES. La propiedad se denomina SHARED y puede tener los siguientes valores:
- SHARED: para tablespaces temporales compartidos
- LOCAL_ON_ALL: para tablespaces temporales locales sobre todas las instancias
- LOCAL_ON_RIM: para tablespaces temporales locales sobre las instancias que sean read only
Funcionalidad de un Tablespace Temporal Local
A continuación, vamos a manipular un ambiente configurado con Tablespace Temporal Local.
Verificando las instancias del Oracle RAC
[oracle@rac1-12cR2 ~]$ ps -ef | grep pmon
oracle 12414 1 0 11:04 ? 00:00:00 asm_pmon_+ASM1
oracle 13930 1 0 11:05 ? 00:00:00 ora_pmon_orcl1
oracle 15620 15425 0 11:43 pts/1 00:00:00 grep --color=auto pmon
[oracle@rac1-12cR2 ~]$ srvctl status database -d orcl
Instance orcl1 is running on node rac1-12cR2
Instance orcl2 is running on node rac2-12cR2
[oracle@rac1-12cR2 ~]$Iniciamos sesión dentro de la instancia 1 y verificamos los tablespaces permanentes y temporales asignados
[oracle@rac1-12cR2 ~]$ . oraenv
ORACLE_SID = [orcl] ? orcl
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@rac1-12cR2 ~]$ sqlplus /nolog
SQL*Plus: Release 12.2.0.1.0 - Production on Thu Aug 15 11:43:32 2019
Version 12.2.0.1.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.
SQL> connect sys/oracle@orcl as sysdba
Connected.
SQL> SELECT PROPERTY_NAME, PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME like
'DEFAULT_%_TABLESPACE';
PROPERTY_NAME PROPERTY_VALUE
-------------------------------------------------- -----------------------
DEFAULT_PERMANENT_TABLESPACE USERS
DEFAULT_TEMP_TABLESPACE TEMPVerificamos la propiedad SHARED del tablespace temporal default
QL> select file_name, tablespace_name,shared,inst_id from dba_temp_files;
FILE_NAME TABLESPACE SHARED INST_ID
---------------------------- -------- ------------- ----------
+DATADG/ORCL/TEMPFILE/temp.266 TEMP SHARED
.1000655421
SQL> select instance_name, instance_number from v$instance;
INSTANCE_NAME INSTANCE_NUMBER
----------------------- ----------------------------
orcl1 1
SQL> select instance_name,instance_number from gv$instance;
INSTANCE_NAME INSTANCE_NUMBER
------------------------- --------------------------
orcl1 1
orcl2 2
SQL>Iniciamos sesión dentro de la instancia 2 y verificamos los tablespaces permanentes y temporales asignados
[oracle@rac2-12cR2 ~]$ . oraenv
ORACLE_SID = [orcl2] ? orcl2
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@rac2-12cR2 ~]$ sqlplus /nolog
SQL*Plus: Release 12.2.0.1.0 - Production on Fri Aug 16 11:11:36 2019
Version 12.2.0.1.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.
SQL> connect sys/oracle@orcl as sysdba
Connected.
SQL> select instance_name,instance_number from gv$instance;
INSTANCE_NAME INSTANCE_NUMBER
------------------------ ---------------------------
orcl2 2
orcl1 1
SQL> select instance_name,instance_number from v$instance;
INSTANCE_NAME INSTANCE_NUMBER
------------------------ ---------------------------
orcl2 2
SQL> select file_name,tablespace_name,shared,inst_id from dba_temp_files;
FILE_NAME TABLESPACE_NAME SHARED INST_ID
---------------------------- ------------- ------ ------
+DATADG/ORCL/TEMPFILE/temp.266 TEMP SHARED
.1000655421
/u01/app/oracle/local_all_temp01.dbf_1 LOCAL_ALL_TEMP LOCAL_ON_ALL 1
/u01/app/oracle/local_all_temp01.dbf_2 LOCAL_ALL_TEMP LOCAL_ON_ALL 2
SQL>Creando un nuevo Tablespace Temporal local desde la instancia 1 (orcl1)
SQL> create local temporary tablespace for all local_all_temp tempfile '/u01/app/oracle/
local_all_temp01.dbf' size 10M extent management local uniform size 1M;
Tablespace created.Nota: como eliminar un Tablespace Temporal Local
SQL> drop tablespace LOCAL_ALL_TEMP including contents and datafiles;Verificando los Tablespace Temporal Local
SQL> select inst_id,name,bigfile from gv$tablespace where name like '%LOCAL%'; INST_ID NAME BIGFILE ---------- ------------------------------ ---------- 2 LOCAL_ALL_TEMP YES 1 LOCAL_ALL_TEMP YES
SQL> select inst_id,name,bigfile from gv$tablespace where name like '%LOCAL%';
INST_ID NAME BIGFILE
---------- ------------------------------ ----------
2 LOCAL_ALL_TEMP YES
1 LOCAL_ALL_TEMP YESEstableciendo un Tablepace Temporal normal como el tablespace default en la base de datos
SQL> alter database default temporary tablespace temp;
alter database default temporary tablespace temp
*
ERROR at line 1:
ORA-12907: tablespace TEMP is already the default temporary tablespaceEstableciendo un Tablespace Temporal Local como el tablespace default en la base de datos
SQL> alter database default local temporary tablespace LOCAL_ALL_TEMP;
Database altered.
SQL> select file_name,tablespace_name,shared,inst_id from dba_temp_files;
FILE_NAME NAME SHARED INST_ID
---------------------------------------------------------------------------------------------
+DATADG/ORCL/TEMPFILE/temp.266.1000655421 TEMP SHARED
/u01/app/oracle/local_all_temp01.dbf_1 LOCAL_ALL_TEMP LOCAL_ON_ALL 1
/u01/app/oracle/local_all_temp01.dbf_2 LOCAL_ALL_TEMP LOCAL_ON_ALL 2
SQL>Verificamos los valores de los tablespaces default en la base de datos
SQL> SELECT PROPERTY_NAME, PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME
like 'DEFAULT_%_TABLESPACE';
PROPERTY_NAME PROPERTY_VALUE
----------------------------------- -----------------------------------
DEFAULT_LOCAL_TEMP_TABLESPACE LOCAL_ALL_TEMP
DEFAULT_PERMANENT_TABLESPACE USERS
DEFAULT_TEMP_TABLESPACE TEMPCreando un usuario (scott) y estableciendo el Tablespace Temporal Local como su default
SQL> alter session set "_ORACLE_SCRIPT"=true;
Session altered.
SQL> create user scott identified by oracle default tablespace users default temporary
tablespace temp;
User created.
SQL> grant connect,resource to scott;
Grant succeeded.
SQL> alter user scott local temporary tablespace LOCAL_ALL_TEMP;
User altered.
SQL> select DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE,LOCAL_TEMP_TABLESPACE
from dba_users
where username='SCOTT';
DEFAULT_TABLESPACE TEMPORARY_TABLESPACE LOCAL_TEMP_TABLESPACE
------------------ ----------------------- ---------------------
USERS TEMP LOCAL_ALL_TEMP
SQL> exitBug reportado durante la actualización de una base de datos hacia Oracle 12cR2 (12.2.0.1.0)
Como el concepto de Tablespace Temporal Local fue una característica añadida en 12cR2, a partir de esta versión todos los usuarios cuentan con una nueva propiedad llamada LOCAL_TEMP_TABLESPACE, mientras se realiza una actualización de versión desde 11gR2 o 12cR1 hacia Oracle 12cR2 (12.2.0.1.0) la propiedad en cuestión de algunos usuarios se establece como “SYSTEM” y para otros usuarios como NULL. Esto puede generar muchos problemas si no lo solucionamos a tiempo. Este bug ha sido corregido en Oracle 18c (18.1).
Para verificar si nuestra base de datos fue afectada por este bug, podemos ejecutar la siguiente consulta:
SQL> select username from dba_users where local_temp_tablespace='SYSTEM';
USERNAME
---------------------------------------
RMAT_TEMP
GSMADMIN_INTERNAL
DBSNMP
GLOBAL_IDENT_SCHEMA_USER
RMAT_SOAP
RMANUSER
RMATV2_DEC_2015
RMAT_REPORTS_USER
RMATV2_DEC_2016
RMATAPP
RMATV2_DEC_2017
RMATV2_2017
RMATV2_2016
RMATV2
RMAT_INFO
SYSTEM
SYSPara corregir esto debemos modificar manualmente cada usuario y colocar el valor de su propiedad hacia el tablespace temporal default.
SQL> alter user <username> LOCAL TEMPORARY TABLESPACE <default temporary="" tablespace="">
or
</default></username>Utilizar el siguiente script para extraer todas las sentencias necesarias
<div class="ocode">
<div class="ocode-bttn" data-success="Copied to Clipboard" data-error="Error: Could not Copy">
<div><a href="#copy">Copy</a></div>
<div class="ocode-success" aria-hidden="true">Copied to Clipboard</div><div class="ocode-error" aria-hidden="true">Error: Could not Copy</div></div>
<pre aria-label="Code block 14"><code>
SQL> select 'alter user '||username||' LOCAL TEMPORARY TABLESPACE '||TEMPORARY_TABLESPACE||';'
from dba_users where username not in ('XS$NULL') and local_temp_tablespace = 'SYSTEM';
</code></pre> </div>Para más información puede consultar la siguiente nota:
12.2 Database Upgrade Has Marked SYSTEM TABLESPACE as LOCAL_TEMP_TABLESPACE for Few Database Users (Doc ID 2385430.1)
YV Ravi Kumar es un Oracle ACE Director y Oracle Certified Master (OCM) con 20 años de experiencia en Banca, Servicios financieros y seguros (BFSI) y ha ocupado varios puestos como Vise Presidente (IT), Senior Database Architect y Production DBA. Tambien es OCP en Oracle 8i, 9i, 10g, 11g y 12c y posee certificaciones en Golden Gate, RAC, Performance Tuning y Oracle Exadata. Ha publicado mas de 100 articulos para OTN-Español, OTN-Portugues, OTN-Ingles, TOAD World, UKOUG, OTech Magazine y Redgate. Ha sido speaker en @OOW, @NYOUG, @OTN, @AIOUG, Samgam y @IOUG. Diseñó, arquitectó e implementó la Base de Datos del Sistema Bancario Central (CBS) para los Bancos Centrales de dos países: India y Mahe, Seychelles. Es cofundador de OraWorld (www.oraworld.com). Aprenda mas sobre su perfil en LaseSoft.
Mariami Kupatadze es Oracle Certified Master desde 2016. Ella es la primera OCM en su pais, Georgia. Mari es miembro del programa Oracle ACE. Mari es una arquitecta de soluciones en FlashGrid Inc. Tiene mas de diez años de experiencia en administración de base de datos y ha trabajado en un total de 12 compañías durante su carrera. Esta altamente especializada en Oracle Exadata, Oracle 10g, 11g, 12c, 18c y 19c, RAC, Data Guard, Golden Gate, Optimización y tuneo, EM Cloud Control, estrategias de Backup y recuperación de desastres, migraciones de base de datos, Oracle Cloud IaaS, Oracle Database Security, Oracle Audit Vault and Database Firewall y administración de Linux. Ha publicado varios articulos en la revista virtual OraWorld. Mari ha sido speaker para APAC Oracle User Groups Community (APACOUC) y Luxembourg Oracle Users Group (LOXOUG). Es una blogger desde 2010, ha publicado 279 articulos acerca de su experiencia con Oracle y Linux, su blog, dba010.com ha sido reconocido entre el top 60 de Oracle Blogs.
Julio Ayapán es Ingeniero en Ciencias y Sistemas, Administrador de base de datos Oracle con más de 4 años de experiencia en proyectos de infraestructura, Bases de Datos Oracle 10g, 11g y 12c sobre Linux y Solaris. Posee la certificación “Oracle Certified Professional 11g y 12c” y Oracle ACE Associate. Ha sido Conferencista en OTN Tour Latinoamericano 2016 y 2017 en Guatemala. Es parte de la junta directiva del Grupo de Usuarios Oracle de Guatemala (GOUG). Actualmente es Consultor de Bases de datos Oracle en eProseed Central America. Publica artículos frecuentemente en su blog http://oraclehomegt.blogspot.com. Twitter @jayapangt.
Este artículo ha sido revisado por el equipo de productos Oracle y se encuentra en cumplimiento de las normas y prácticas para el uso de los productos Oracle.