Aumente a Performance usando Local Temporary Tablespaces em Real Application Cluster (RAC)

Por Y V Ravi Kumar Oracle ACE director, Mariami Kupatadze Oracle Associate, Rodrigo Mufalani Oracle ACE
Publicado em Novembro 2019

Revisado por Juan Pablo Guizado




Introdução

Uma tablespace temporária compartilhada armazena os arquivos em disco compartilhado em um ambiente em cluster, então o espaço temporário é acessível por todas as instances do cluster em contraste a uma tablespace armazenada separadamente, com tempfiles não compartilhados.

O conceito de local temporary tablespace é novo no Oracle database 12cR2 (12.2.0.1.0). Em versões anteriores, o termo temporary tablespace era usado para referenciar shared temporary tablespaces.

Criar local temporary tablespace para ambos read-only e read-write database instances aumenta a performance de queries que envolvem operações de sort, hash aggregations e joins.



Os benefícios de usar Local Temporary Tablespaces

  • Criar Local Temporary Tablespace evita CF enqueue overhead em um ambiente clusterizado.
  • Melhora a performance do I/O usando leitura loca e não um disco compartilhado.
  • Evitando o custoso gerenciamento de espaço temporário cross-instance em instancias em cluster.
  • Melhora a performance do instance startup eliminando gerenciamento de metadados no disco.

 

Utilização de Local Temporary Tablespace Vs Temporary Tablespace

  • Criar  uma Local Temporary Tablespace, isso irá criar BIGfile por tablespace.
  • Quando o usuário está conectado ao nó leitor a Local Temporary Tablespace será utilizada.
  • Quando o usuário está conectado a uma instance Read / Write Instance uma shared Temporary Tablespace será utilizada.

 

Properties de Local Temporary Tablespaces

  • SHARED: para uma temporary tablespace compartilhada
  • LOCAL_ON_ALL: para temporary tablespace local em todas as instances
  • LOCAL_ON_RIM: para temporary tablespace local em read-only instances


Funcionalidade de Local Temporary Tablespace




Cheque as instances de um 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 ~]$



Login na instance RAC Instance-1 e cheque as Permanent e Temporary Tablespaces

[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                          	TEMP



Cheque a propriedade Default Temporary Tablespace

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



Login na instance RAC Instance-2 e cheque as Permanent e Temporary Tablespaces

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



Crie uma Local Temporary Tablespace da Instance-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 a Local Temporary Tablespace

SQL> drop tablespace LOCAL_ALL_TEMP including contents and  datafiles;


Por favor, note como ficou  a propriedade Local Temporary Tablespace em ambos as instances RAC.

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>



Cheque a propriedade Local Temporary Tablespace

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



Configure a Default Temporary Tablespace para o Database

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 tablespace



Configure a Local Temporary Tablespace para o Database como default

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>



Cheque a default temporary tablespaces para o database

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                    TEMP



Crie o usuário (scott) e coloque a Local Temporary Tablespace como padrão

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



Workaround quando está atualizado o database para a versão Oracle 12cR2 (12.2.0.1.0)

Quando está atualizando a versão do Oracle 11gR2 e/ou Oracle 12cR1 para o Oracle 12cR2 (12.2.0.1.0) Local_temp_tablespace de alguns usuários do banco de dados mudam para  "SYSTEM" tablespace e de alguns usuários para NULL. Esse problema foi corrigido na versão Oracle 18c (18.1) release.


Cheque em um database atualizado do Oracle 11gR2 (11.2.0.4.0) para o Oracle 12cR2 (12.2.0.1.0)

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
SYS


Manualmente mude a Local Temporary Tablespace dos usuários para a default Temporary Tablespace do database.

SQL> alter user <Username> LOCAL TEMPORARY TABLESPACE  <Default Temporary Tablespace>


(OU)

Use o seguinte script para mudar todos os usuários de uma só vez

SQL> select 'alter user '||username||' LOCAL TEMPORARY TABLESPACE '||TEMPORARY_TABLESPACE||';' 
from dba_users where username not in ('XS$NULL') and local_temp_tablespace = 'SYSTEM';



Por favor, para mais detalhes verifique:

12.2 Database Upgrade Has Marked SYSTEM TABLESPACE as LOCAL_TEMP_TABLESPACE for Few Database Users (Doc ID 2385430.1)

Instale o patch 23715518 na home alvo do Oracle 12cR2 (12.2.0.1.0) e depois restaure um backup e refaça o Upgrade. 


Conclusão: Criar local temporary tablespaces para ambos read-only e read/write database instances irá ser utilizada no nó de leitura e em instances read/write.




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.

Rodrigo Mufalani é um Oracle ACE member e Oracle Certified Master (OCM) com 15 anos de experiência, começou com o Oracle 8i, mas teve a oportunidade de dar suporte a Oracle 7.3.4 em diante. É especialista em banco de dados Oracle com foco principal em Engineered Systems, Performance & Tuning e RAC. Ele é fundador e presidente e também palestrante do Luxembourg Oracle User Group. É palestrante em eventos de Oracle como: OTN LAD TOUR e OTN EMEA TOUR e outros. Atualmente trabalha como Principal DBA na eProseed Europe. Foi o terceiro Oracle ACE a ser nomeado no Brasil. Twitter @mufalani / blog Mufalani.worpress.com

Este artigo foi revisto pela equipe de produtos Oracle e está em conformidade com as normas e práticas para o uso de produtos Oracle.