AWR – Medindo IOPS e Throughput de I/O do Oracle Database
agosto 27, 2014

Uma conseqüência natural do trabalho de um DBA é passar horas e horas em cima de um relatório AWR identificando problemas e causas de performance do banco de dados.

Um dos enganos mais comum da maioria é conseguir identificar o IOPS e throughput de I/O dentro do próprio Oracle AWR. Esse ponto é essencial para você conseguir definir a sua infra-estrutura de I/O para combinar com a sua aplicação e além de conseguir levantar dados para debater de forma inteligente com sua equipe de storage.

Antes, vamos realmente entender o que é IOPS e throughput de dados, que são termos completamente diferentes:

IOPS – É a unidade padrão que mensura uma operação de Input/Output (I/O) por segundo. Usado para descrever I/O rate de aplicações principalmente utilizado para acessos randômicos I/O (leituras de indexes) e aplicações do tipo OLTP.

Throughput (Mbytes/s) – Usado para mensurar grandes taxas de transferências de blocos. Usado principalmente para aplicações seqüenciais de dados como DSS e OLAP. (full table scan)

O relatório de AWR pode ser seu aliado para identificar essas métricas de I/O na sessão Instance Activity Stats:

IOPS = “physical read total I/O requests” + “physical write total I/O requests”
MBytes/s = “physical read total bytes” + “physical write total bytes”

Ou você pode utilizar a seguinte consulta para mostrar esses valores:

select min(begin_time), max(end_time),
sum(case metric_name when 'Physical Read Total Bytes Per Sec' then average end) Physical_Read_Total_Bps,
sum(case metric_name when 'Physical Write Total Bytes Per Sec' then average end) Physical_Write_Total_Bps,
sum(case metric_name when 'Physical Read Total IO Requests Per Sec' then average end) Physical_Read_IOPS,
sum(case metric_name when 'Physical Write Total IO Requests Per Sec' then average end) Physical_write_IOPS,
snap_id
from dba_hist_sysmetric_summary group by snap_id
order by snap_id;

Exemplo relatório AWR:

physical read total IO requests   =  1,257.23
physical write total IO requests  =  1,746.11
physical read total bytes         =  76,314,718.10
physical write total bytes        =  85,321,521.11

Com os dados acima do AWR reportado, temos: ~ 3003 IOPS e ~ 155 Mbytes/s

Esse método é interessante ser utilizado após mudança de equipamentos/aplicação ou até mesmo identificar gargalos do ambiente e resing necessários de Hardware. Com isso em mãos fica mais fácil dialogar sobre esses temas.

Com o Oracle 11g é possível obter dados de IOPS e throughput através do Oracle Calibrate (DBMS_RESOURCE_MANAGER.CALIBRATE_IO) que é utilizado para identificar até onde o meu Hardware pode chegar, assim o Oracle acaba utilizando esse método para várias operações internas, como por exemplo o AUTO DOP. Aqui o contexto é um pouco diferente, através das métricas de IOPS e throughput identificados pelo AWR, eu consigo além de definir por tempo, posso alcançar a métrica onde minha aplicação ou hardware está mais consumindo de I/O.

Considerações finais:

- Para ambientes RAC, você deve agregar os valores de IOPS e MBytes/s para todas as instâncias do RAC. Assim o IOPS e MBbytes no ambiente cluster será a soma de todos os valores de cada node.

- Utilize o AWR em cima dos horários de maior utilização do ambiente.

- Nunca utilize relatórios AWR mais do que 1 hora. É comum encontramos relatórios AWR das 09 as 18 horas por exemplo, isso pode mascarar o real problema do seu banco de dados. Utilize tempos curtos de 1 hora baseado nos picos de maior cargas no banco de dados.

- Para alcançar IOPS e Throughput desejado, não apenas discos pode ser a razão e sim uma série de fatores agrupados, como o próprio banco de dados, o block device manager, multipathing driver, SCSI driver, HBA, System Bus, Switch/iSCSI, Storage Array, Disks, RAID e etc .. por isso é importante avaliar cada quesito em particular quando se fala em performance de I/O.

Post sobre Asynchronous I/O
novembro 23, 2012

Essa é para você que quer aprender mais sobre o Asynchronous IO, o que ele é e como ele trabalha.

Esse é um post da IBM escrito por M. Tim Jones, mais que pode ser encontrada no metalink pelo documento Asynchronous I/O Support on OCFS/OCFS2 and Related Settings: filesystemio_options, disk_asynch_io [ID 432854.1].

Acabei de ler o documento e fiquei impressionado com os detalhes explicado pelo autor.

Vale a pena conferir:

http://www.ibm.com/developerworks/linux/library/l-async/index.html

Checando o Asynchronous I/O no Linux
junho 19, 2012

Asynchronous I/O é uma maneira diferente de processar os dados de input/output. Utilizando Async I/O permite que outros processos continue antes da transmissão ter sido concluída, fornecendo assim grande ganho de performance para operações de IO.

Para verificar se está característica está habilitado checamos através do slabinfo. O slabinfo mantém todas as estatísticas sobre os objetos em memória, assim podemos verificar as operações de AIO já que ele trata os objetos na memória virtual.

cat /proc/slabinfo | grep kio

Caso o seu ASYNC IO esteja habilitado o retorno do comando acima deve parecer assim:

kioctx                 188       244        128 9       9 1 : 252 126
kiocb                  43010 43010      96  1652 1652 1 : 252 126

Agora, se o seu AIO não está habilitado o kiocb deve estar seguido de dois 0 (zeros).

kioctx                28     60    192   20    1 : tunables  120   60    8 : slabdata      3      3      0
kiocb                  0 0    128   30    1 : tunables  120   60    8 : slabdata      0      0      0

Com o a utilizando do ASMLib, esse comportamento não pode ser seguido já que o ASMLib não utiliza as funções POSIX aio_*(), então nunca vamos ver o kiocb em ação.

Quando o ASMLib é utilizado podemos simplesmente checar através do parâmetro disk_asynch_io na instância ASM, você pode inclusive habilitar e desabilitar o AIO apenas definindo o parâmetro disk_asynch_io para TRUE ou FALSE.

Erro ao remover diskgroup ASM – ORA-15027
maio 29, 2012

O erro acontece quando você tenta desmontar ou remover um diskgroup em que existe um (ou mais) arquivo sendo utilizado atualmente pelo ASM.

SQL> alter diskgroup dgroup1 dismount;
alter diskgroup dgroup1 dismount
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15027: active use of diskgroup "DGROUP1" precludes its dismount

SQL> drop diskgroup DGROUP1 including contents;
drop diskgroup DGROUP1 including contents
*
ERROR at line 1:
ORA-15039: diskgroup not dropped
ORA-15027: active use of diskgroup "DGROUP1" precludes its dismount

O ASM por si só, é muito inteligente e sabe que se o diskgroup for desmontado ou removido, poderá ocorrer qualquer perca de dados. Você pode por exemplo incluir a opção force no comando para desmontar o diskgroup de maneira “forçada” sem nenhuma checagem de perca de dados, que isso pode trazer grande dores de cabeça … já que pode haver perca de dados …

SQL> alter diskgroup dgroup1 dismount;
alter diskgroup dgroup1 dismount
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15027: active use of diskgroup "DGROUP1" precludes its dismount

SQL> alter diskgroup dgroup1 dismount force;

Diskgroup altered.

Como então desmontar/remover o diskgroup sem qualquer perca de dados? No meu caso o problema estava com o spfile do ASM, ele estava justamente no diskgroup que gostaria de remover (+DGROUP1). Como eu descobri isso? Simples … através da view v$asm_file:

SQL> select f.group_number, d.name, f.file_number, f.bytes, f.type
2       from v$asm_file f, v$asm_diskgroup d
3       where f.group_number = d.group_number;

GROUP_NUMBER NAME	      FILE_NUMBER      BYTES        TYPE
------------ ------------------------------   -----------                  ----------         ----------------------------------------------------------------
	   1 DGROUP1		       253                       1536             ASMPARAMETERFILE

Ok, mais qual solução? Mover o spfile do ASM para um outro diskgroup disponível:

SQL> select name, state from v$asm_diskgroup;

GROUP_NUMBER NAME			    STATE
------------ ------------------------------ -----------
	   1 DGROUP1			    MOUNTED
	   2 DGROUP2			    MOUNTED

SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Automatic Storage Management option

[oracle@oracle11g ~]$ asmcmd
ASMCMD> spget
+DGROUP1/asm/asmparameterfile/registry.253.784483237

Veja acima que tenho dois diskgroup, quero passar então o meu parameter file para o DISKGROUP2:

SQL> create pfile='/u01/app/oracle/init+ASM.ora' from spfile;

File created.

SQL> shut immediate;
ASM diskgroups dismounted
ASM instance shutdown

SQL> startup pfile='/u01/app/oracle/init+ASM.ora';
ASM instance started

Total System Global Area  284565504 bytes
Fixed Size		    1344456 bytes
Variable Size		  258055224 bytes
ASM Cache		   25165824 bytes
ASM diskgroups mounted

SQL> show parameter spfile

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
spfile				     string

Veja que agora como subi o ASM através do pfile, nenhum spfile está configurado para ele. Agora iremos criar um spfile para a o diskgroup DGROUP2 através do pfile que acabamos de criar.

SQL> create spfile='+DGROUP2/init.ora' from pfile='/u01/app/oracle/init+ASM.ora';
File created.

SQL> shut immediate;
ASM diskgroups dismounted
ASM instance shutdown

SQL> startup
ASM instance started
Total System Global Area 284565504 bytes
Fixed Size 1344456 bytes
Variable Size 258055224 bytes
ASM Cache 25165824 bytes
ASM diskgroups mounted

SQL> show parameter spfile

NAME                                   TYPE          VALUE
------------------------------------ ----------- ------------------------------
spfile                                 string        +DGROUP2/init.ora
SQL>
SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Automatic Storage Management option

[oracle@oracle11g oracle]$ asmcmd
ASMCMD> spget
+DGROUP2/init.ora
ASMCMD> exit

Agora que temos o spfile do ASM apontando para o DGROUP2 e não mais para o DGROUP1, podemos sem problema nenhum desmontar/remover o diskgroup desejado.

SQL> alter diskgroup dgroup1 dismount;
Diskgroup altered.

SQL> alter diskgroup dgroup1 mount;
Diskgroup altered.

SQL>  drop diskgroup dgroup1 including contents;
Diskgroup dropped.

SQL> select group_number, name, state from v$asm_diskgroup;
GROUP_NUMBER     NAME			         STATE
------------    ------------------------------ -----------
	   2    DGROUP2		                 MOUNTED
Migrando para ASM utilizando apenas o RMAN
janeiro 16, 2012

O ASM é sem dúvida um dos maiores avanços de gerenciamento de disco e muitas coisas relacionadas a I/O (performance, gerenciamento, etc..). Em um outro post estarei mostrando as vantagens de um ambiente utilizando ASM e como ele é benéfico para o ambiente.

Apesar de todas essas vantagens, ainda hoje existem muitos ambientes em que não é utilizado o ASM. Pensando nisso hoje estaremos vendo como utilizar o RMAN (apenas o RMAN) para realizar uma migração de um filesystem comum como ext3 para ASM.

No nosso caso, estaremos movendo todos os datafiles e tempfiles do banco dbtst para o diskgroup +DGROUP1.

[oracle@oracle11gR3 ~]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Wed Jan 4 13:56:46 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to target database: DBTST (DBID=2987787466)

-- Habilitando o Oracle Managed File
RMAN> sql "alter system set db_create_file_dest=''+DGROUP1'' scope=spfile";
using target database control file instead of recovery catalog
sql statement: alter system set db_create_file_dest=''+DGROUP1'' scope=spfile

-- Mudando a localização do controlfile
RMAN> sql "alter system set control_files=''+DGROUP1'' scope=spfile";
using target database control file instead of recovery catalog
sql statement: alter system set control_files=''+DGROUP1'' scope=spfile

RMAN> shutdown immediate;
database closed
database dismounted
Oracle instance shut down

-- Subimos o banco em modo NOMOUNT
RMAN> startup nomount
connected to target database (not started)
Oracle instance started

Total System Global Area     839282688 bytes

Fixed Size                     2233000 bytes
Variable Size                511708504 bytes
Database Buffers             318767104 bytes
Redo Buffers                   6574080 bytes

Nesse momento vamos restaurar o nosso control file atual (‘/u01/app/oracle/oradata/dbtst/control01.ctl’ ou ‘/u01/app/oracle/oradata/dbtst/control02.ctl’) para o diskgroup DGROUP1. O grande lance aqui é que como mudamos o parâmetro control_files para o +DGROUP1 e habilitamos o Oracle Managed Files e temos uma cópia consistência do control files pois baixamos o banco de maneira adequada, simplesmente restaurando o control file atual ele irá gerar no caminho do diskgroup DGROUP1.

Observe a saída do comando:

RMAN> restore controlfile from '/u01/app/oracle/oradata/dbtst/control01.ctl';

Starting restore at 04-JAN-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=137 device type=DISK

channel ORA_DISK_1: copied control file copy
output file name=+DGROUP1/dbtst/controlfile/current.256.771688895
Finished restore at 04-JAN-12

Como já temos nosso control file restaurado para o DGROUP1, podemos inicializar o banco em mount.

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

Agora vamos realizar um backup do tipo copy formatando para o diskgroup DGROUP1.

RMAN> BACKUP AS COPY DATABASE DATABASE FORMAT '+DGROUP1';

Starting backup at 04-JAN-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=137 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/dbtst/system01.dbf
output file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/data_D-DBTST_I-2987787466_TS-SYSTEM_FNO-1_01mvu2gt tag=TAG20120104T140308 RECID=2 STAMP=771689052
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:06
channel ORA_DISK_1: starting datafile copy

...

input datafile file number=00004 name=/u01/app/oracle/oradata/dbtst/users01.dbf
output file name=+DGROUP1/dbtst/datafile/users.262.771689571 tag=TAG20120104T140308 RECID=13 STAMP=771689575
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 04-JAN-12
channel ORA_DISK_1: finished piece 1 at 04-JAN-12
piece handle=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/0dmvu33a_1_1 tag=TAG20120104T140308 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 04-JAN-12
channel ORA_DISK_1: finished piece 1 at 04-JAN-12
piece handle=+DGROUP1/dbtst/backupset/2012_01_04/nnsnf0_tag20120104t140308_0.263.771689581 tag=TAG20120104T140308 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 04-JAN-12

Com a image copy do banco de dados feito, o vamos migrar nossos datafiles para esses arquivos de copias de backup. Isso tudo é feito através do comando SWITCH DATABASE TO COPY, que sempre quando utilizado deve-se realizar um recover do banco de dados.

RMAN> SWITCH DATABASE TO COPY; 

RMAN> RECOVER DATABASE;

Agora abrimos o banco e adicionamos um novo tempfile para o tablespace TEMP e removemos o tempfile ‘/u01/app/oracle/oradata/dbtst/temp01.dbf’ antigo. Como nosso OMF está apontando para o diskgroup DGROUP1 não precisamos especificar o caminho do tempfile na sua criação.

RMAN> SQL "ALTER DATABASE OPEN";
RMAN> SQL "ALTER TABLESPACE TEMP ADD TEMPFILE";
RMAN> SQL "ALTER DATABASE TEMPFILE ''/u01/app/oracle/oradata/dbtst/temp01.dbf'' DROP";

Após esses passos seus datafiles e tempfiles estarão todos no ASM.

Montando diskgroups ASM
junho 29, 2011

SQL> desc v$asm_diskgroup
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 GROUP_NUMBER                                       NUMBER
 NAME                                               VARCHAR2(30)
 SECTOR_SIZE                                        NUMBER
 BLOCK_SIZE                                         NUMBER
 ALLOCATION_UNIT_SIZE                               NUMBER
 STATE                                              VARCHAR2(11)
 TYPE                                               VARCHAR2(6)
 TOTAL_MB                                           NUMBER
 FREE_MB                                            NUMBER
 REQUIRED_MIRROR_FREE_MB                            NUMBER
 USABLE_FILE_MB                                     NUMBER
 OFFLINE_DISKS                                      NUMBER
 COMPATIBILITY                                      VARCHAR2(60)
 DATABASE_COMPATIBILITY                             VARCHAR2(60)

SQL> select GROUP_NUMBER, NAME, STATE from v$asm_diskgroup;

GROUP_NUMBER NAME                           STATE
------------ ------------------------------ -----------
           1 ORADATA                        MOUNTED
           0 ORADATA1                       DISMOUNTED

SQL> ALTER DISKGROUP ALL DISMOUNT;

Diskgroup altered.

SQL> select GROUP_NUMBER, NAME, STATE from v$asm_diskgroup;

GROUP_NUMBER NAME                           STATE
------------ ------------------------------ -----------
           0 ORADATA                        DISMOUNTED
           0 ORADATA1                       DISMOUNTED

SQL>  ALTER DISKGROUP ALL MOUNT;

Diskgroup altered.

SQL>  select GROUP_NUMBER, NAME, STATE from v$asm_diskgroup;

GROUP_NUMBER NAME                           STATE
------------ ------------------------------ -----------
           1 ORADATA                        MOUNTED
           0 ORADATA1                       MOUNTED
Movendo control file para ASM
maio 10, 2011

Maneira simples de mover o control file para o ASM, tudo é feito através do RMAN.

[oracle@oel510gasm oradata]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Mar 16 21:52:56 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> show parameter control_files

NAME                                    TYPE     VALUE
------------------------------------ ----------- ------------------------------
control_files                          string     /u01/app/oracle/oradata/control01.ctl,
/u01/app/oracle/oradata/control02.ctl,
/u01/app/oracle/oradata/control03.ctl

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@oel510gasm oradata]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Wed Mar 16 21:54:17 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

connected to target database (not started)

RMAN> startup nomount;

Oracle instance started

Total System Global Area     167772160 bytes

Fixed Size                     1218316 bytes
Variable Size                 67111156 bytes
Database Buffers              96468992 bytes
Redo Buffers                   2973696 bytes

RMAN> restore controlfile to '+DGROUP1/orcl/controlfile/control01.ctl' from '/u01/app/oracle/oradata/control01.ctl';

Starting restore at 16-MAR-11
using channel ORA_DISK_1

channel ORA_DISK_1: copied control file copy
Finished restore at 16-MAR-11

RMAN> restore controlfile to '+DGROUP1/orcl/controlfile/control02.ctl' from '/u01/app/oracle/oradata/control01.ctl';

Starting restore at 16-MAR-11
using channel ORA_DISK_1

channel ORA_DISK_1: copied control file copy
Finished restore at 16-MAR-11

RMAN> restore controlfile to '+DGROUP1/orcl/controlfile/control03.ctl' from '/u01/app/oracle/oradata/control01.ctl';

Starting restore at 16-MAR-11
using channel ORA_DISK_1

channel ORA_DISK_1: copied control file copy
Finished restore at 16-MAR-11

Após fazer o RESTORE do control file no ASM é necessário sinalizar ao spfile.

RMAN> sql 'alter system set control_files="
2> +DGROUP1/orcl/controlfile/control01.ctl",
3> "+DGROUP1/orcl/controlfile/control02.ctl",
4> "+DGROUP1/orcl/controlfile/control03.ctl"
5> scope=spfile';

sql statement: alter system set control_files="+DGROUP1/orcl/controlfile/control01.ctl", "+DGROUP1/orcl/controlfile/control02.ctl", "+DGROUP1/orcl/controlfile/control03.ctl" scope=spfile

RMAN> startup

connected to target database (not started)
Oracle instance started
database mounted
database opened

Total System Global Area     167772160 bytes

Fixed Size                     1218316 bytes
Variable Size                 67111156 bytes
Database Buffers              96468992 bytes
Redo Buffers                   2973696 bytes

[oracle@oel510gasm oradata]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Mar 16 22:02:21 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> show parameter control_files

NAME                                   TYPE         VALUE
------------------------------------ ----------- ------------------------------
control_files                          string      +DGROUP1/orcl/controlfile/cont
 rol01.ctl, +DGROUP1/orcl/contr
 olfile/control02.ctl, +DGROUP1
 /orcl/controlfile/control03.ctl