Performance Tuning. O que é, por onde começar e o que fazer? Fábio Prado.

(1)

Performance Tuning

(2)

www.fabioprado.net

julho de 14 Workshop Performance Tuning

Visão geral

• Nesta apresentação veremos:

—

O que é Tuning?

—

Objetivos do tuning

—

Problemas mais comuns

—

Onde e por que tunar?

—

Atividades de tuning (teoria, dicas e exemplos)

—

Considerações gerais sobre segurança e

performance.

(3)

(4)

www.fabioprado.net

O que é tuning?

• Tuning

se escreve com somente 2 Ns!

Tunning

com 3 Ns está errado!

—

Ver artigo

O que é Tuning?

• Em TI, refere-se basicamente ao conceito de

propor e aplicar mudanças para

otimizar o

desempenho

na recuperação ou atualização

de dados;

(5)

O que é tuning?

• É um termo que desperta um interesse

cada vez maior nos profissionais de TI,

devido ao aumento:

—

Do legado de sistemas corporativos (ERPs, GEDs

etc.) e sistemas web;

—

Da quantidade de usuários de BDs;

(6)

www.fabioprado.net

Principais objetivos do Tuning

• Minimizar

tempo de resposta

na recuperação

de dados;

(7)

Problemas mais comuns

• Os problemas mais comuns que impactam

negativamente na performance dos BDs são:

—

Gargalos de CPU;

—

Estruturas de memória subdimensionadas;

—

I/O ruim;

—

Instruções SQL ineficientes ou pesadas;

—

Regressão de performance após tunar SQL;

—

Contenção de recursos e alta concorrência;

(8)

(9)

Entendendo o Processo de Tuning

• Para otimizar é necessário:

1- Entender e identificar o problema;

2- Elaborar o

diagnóstico

;

3- Executar o diagnóstico, ou seja,

tunar

;

(10)

www.fabioprado.net

Entendendo o Processo de Tuning

Melhorou

Identifique o problema

Elabore o diagnóstico

Execute o diagnóstico

Teste o

desempenho

FIM

NÃO Melhorou

Diagnóstico: nome dado a um processo analítico que é utilizado para chegar a alguma conclusão e

à conclusão, por si própria

(11)

• Pense primeiro no

diagnóstico

e depois no

tuning

;

• Após alterar, teste:

—

Não acredite na sorte, lendas, mitos ou boatos;

—

Verifique se foi obtido o ganho de performance desejado;

—

Se necessário, volte atrás.

• Não existe uma fórmula mágica nem milagres

para tunar o desempenho de qualquer BD:

—

Se existisse, ela seria o padrão da instalação do SGBD!

(12)

www.fabioprado.net

Por onde começar?

Ganhos de performance por área de atuação

(13)

Por onde começar?

SQL ruim é a causa nº 1 de

problemas de performance

(14)

(15)

Atividades de tuning

• Os 3 tipos de atividades de tuning que

podem ser realizadas em um BD são:

1- Planejamento de performance;

2- Tuning de instância e BD;

(16)

www.fabioprado.net

1- Planejamento de performance

• Definição e configuração do ambiente em que

o BD será instalado, considerando-se os

seguintes itens:

—

Hardware;

—

Software;

—

Sistema Operacional;

(17)

1- Planejamento performance

Impacto do RAID em OLTP

70% leitura – 30% escrita

Carga de 400 GB de dados

Análise de tempo de resposta de RAID 10 X RAID 5

Fonte: Comprehending the Tradeoffs between Deploying Oracle® Database on RAID 5 and RAID

10 Storage Configurations, A Dell® Technical White Paper

(18)

www.fabioprado.net

1- Planejamento performance

HW Upgrade

16 GB RAM DDR-3 (

800 MHz

)

6 GB RAM DDR-2 (

400 MHz

)

2 cpu

quad-core

64 bits

Xeon de 2.13 GHz

2 cpu

single-core

64 bits

Xeon de 3.8 GHz

Intel E5506

Intel E7520

Máquina nova

Máquina antiga

Ganho médio de 233% no tempo

de execução de instruções SQL

(19)

1- Planejamento performance

HW Upgrade

(20)

www.fabioprado.net

2- Tuning de Instância e BD

• Ajuste de parâmetros e configurações do BD

para otimizar performance;

• Faz parte do trabalho de um DBA gerenciar a

segurança do Banco de Dados sem prejudicar

a sua performance, e vice-versa.

(21)

2- Tuning de Instância e BD

Diagnóstico

• Como identificar o problema e elaborar o

diagnóstico?

—

Consulte as

visões de performance dinâmicas

;

—

Analise os

Wait Events

;

—

Gere e analise

SQL Traces

:

Ver artigo

Analisando SQL traces em Bancos de Dados Oracle

.

—

Utilize as seguintes ferramentas:

Statspack

;

AWR

;

(22)

www.fabioprado.net

2- Tuning de Instância e BD

Diagnóstico - VPDs

• Consulte as visões de perf. dinâmicas:

—

Exemplos:

V$SQL

,

V$SQLSTAT

,

V$SYSSTAT

,

V$SESSTAT,

V$SYS_TIME_MODEL

,

V$SYSMETRIC

etc;

—

Ver artigo

Entendendo as visões de performance dinâmicas

.

(23)

2- Tuning de Instância e BD

Diagnóstico - WEs

• Consulte e analise os Wait Events:

Evolução dos Wait Events por versão do Oracle Database

104

140

220

400

800 1146

1567

0

200

400

600

800 1000

1200

1400

1600

1800

7.0.12

8 8i

9i

10GR1

11GR2

12CR1

Q

u

a

n

ti

d

a

d

e

(24)

www.fabioprado.net

2- Tuning de Instância e BD

Diagnóstico - WEs

• Apenas

15 wait events

representam 90% de

todos que são coletados

(segundo pesquisa da Confio

Software)

. Exemplos:

db file sequential read

db file scattered read

direct patch read

/

direct path read temp

global cache cr request

buffer busy waits

/

read by other session

(25)

2- Tuning de Instância e BD

Diagnóstico - AWR

(26)

www.fabioprado.net

2- Tuning de Instância e BD

Diagnóstico - AWR

(27)

2- Tuning de Instância e BD

Diagnóstico - AWR

(28)

www.fabioprado.net

2- Tuning de Instância e BD

Otimização

Swingbench 15 sessões por 5 minutos, 11GR2, antes:

—

I/O síncrono: FILESYSTEMIO_OPTIONS =

NONE

—

1 GB de SGA + PGA: MEMORY_MAX_TARGET, MEMORY_TARGET =

1G

—

Commit síncrono: COMMIT_WAIT =

WAIT

(29)

2- Tuning de Instância e BD

Otimização

SwingBench 15 sessões por 5 minutos, 11GR2,

depois

:

—

I/O assíncrono: FILESYSTEMIO_OPTIONS =

SETALL

—

2 GB de SGA + PGA: MEMORY_MAX_TARGET, MEMORY_TARGET =

2G

—

Commit assíncrono: COMMIT_WAIT =

NOWAIT

(30)

www.fabioprado.net

3- SQL Tuning

• SQL Tuning é uma atividade complexa e que

requer muito esforço. Não tente economizar

tempo acreditando em lendas,

mitos

e

boatos!

• Existem muitas dicas e técnicas, mas não

existe uma

receita de bolo

que se aplica em

(31)

3- SQL Tuning

Mitos

(32)

www.fabioprado.net

3- SQL Tuning

Diagnóstico

• Gere e analise um plano de execução para

identificar o problema e elaborar o

diagnóstico:

—

Habilitando Autotrace (

SET AUTOTRACE

);

—

Executando o comando

EXPLAIN PLAN

;

—

Consultando a visão

V$SQL_PLAN

e outras visões

relacionadas;

—

Gerando arquivos de SQL Trace (ver artigo

Analisando SQL traces em Bancos de Dados

Oracle

).

(33)

3- SQL Tuning

Diagnóstico – Explain plan

(34)

www.fabioprado.net

3- SQL Tuning

Dicas e técnicas

• Algumas dicas para otimizar instruções SQL:

—

Crie índices para otimizar consultas frequentes e

verifique se eles estão sendo utilizados;

—

Utilize paralelismo em instruções SQL longas;

—

Utilize a cláusula CASE;

—

Utilize variáveis bind;

—

Utilize hints;

(35)

3- SQL Tuning

Otimizando com índices

Custo antes do índice =

77791

Custo depois do índice =

1659

(46.89 X melhor)

(36)

www.fabioprado.net

3- SQL Tuning

Otimizando com Paralelismo

SELECT

/+ PARALLEL /

...

Custo sem paralelismo =

7040

Custo com paralelismo =

969 (7.26 X melhor)

(37)

3- SQL Tuning

Otimizando com a instrução CASE

SELECT COUNT(1) FROM ... WHERE SALARY < 2000 UNION ALL SELECT COUNT(1) FROM ...

WHERE SALARY BETWEEN

2001AND 4000 UNION ALL

...

Custo sem CASE =

250000

Custo com CASE =

51913

(4.81 X melhor)

(38)

(39)

Segurança X Performance

Performance

Segurança

Quanto mais recursos de

segurança forem

implementados, menor será

a performance do BD.

(40)

www.fabioprado.net

Segurança X Performance

• TDE

degrada performance de instruções SQL;

• LOGGING

aumenta tempo de gravação;

• CONSTRAINTS

aumentam tempo de gravação;

• LOCKS

ocasionam espera e aumentam tempo

de gravação;

• COMMITS

frequentes aumentam quantidade de

gravações;

(41)

Referências

Criptografando tabelas com o TDE

http://www.fabioprado.net/2011/02/implementando-seguranca-com-o-tde.html

O que é Tuning?

http://www.fabioprado.net/2014/04/o-que-e-tuning-como-tunar.html

Oracle Database Performance Tuning Guide 11G

Release 2

http://docs.oracle.com/cd/E11882_01/server.112/e16638/toc.htm

Oracle Database SQL Tuning Guide 12c Release 1

http://docs.oracle.com/cd/E16655_01/server.121/e15858.pdf

Oracle Database 11g Performance Tuning Recipes

Editora: Apress, Autor: Sam R. Alapati, Darl Kuhn e Bill Padfield

Oracle Essentials - Oracle Database 11g

Editora: O´Reilly, Autor: Rick Greenwald, Robert Stackowiak e Jonathan Stern

Paralelismo automático no Oracle Database 11G - Parte 1

http://www.fabioprado.net/2013/02/paralelismo-automatico-no-oracle.html

Treinamento Database Performance Tuning

FABIOPRADO.NET

http://www.fabioprado.net/p/performance-tuning-oracle-database.html

Tuning When you can´t touch the code

(42)

www.fabioprado.net

(43)

1)

O que fazer para gerar o

diagnóstico de uma instrução

SQL?

(44)

www.fabioprado.net

Questões

2)

Cite 2 ferramentas que podem

ser utilizadas para gerar o

diagnóstico do BD.

(45)

Questões

3)

Quais os objetivos principais do