DBAs World
47 pág.

DBAs World

Disciplina:Bancos de Dados para Redes81 materiais667 seguidores
Pré-visualização4 páginas
de acesso mais rápido
– Pouco usado pois o Rowid pode mudar ao

exportar/importar os dados da tabela

31

Métodos de acesso (cont)
� CLUSTER SCAN

– Registros de tabelas relacionadas mantidos no mesmo
bloco (NF e ítem NF, por exemplo)

– Índice com a chave do cluster + rowid
� HASH SCAN

– Aplicação de uma função ‘hash’ à chave do cluster
– Índice não necessário
– Obs: Métodos usados somente quando o comando fornece

a chave do cluster
� SAMPLE TABLE SCAN

– Usado quando comando utiliza a cláusula ‘SAMPLE’:
SELECT * FROM FUNCIONARIOS SAMPLE(10)
(Recupera somente 10% dos registros da tabela)

Métodos de acesso (cont)
� INDEX SCAN

– Pesquisa no índice da tabela
– Se todas as colunas da cláusula SELECT pertencem ao

índice, a tabela não é acessada.
– ‘Fator de Cluster’ indica o quanto os valores de uma coluna

indexada está distribuída nos blocos da tabela. Fator baixo
indica acesso a poucos blocos para determinado valor.

– Quando o valor da coluna for nulo, não há entrada de
informação no índice (b-tree)

� Métodos envolvendo índices:
– Index Unique Scans, Index Range Scans, Index Range

Scans Descending, Index Skip Scans, Full Scans, Fast Full
Index Scans, Index Joins

32

Joins

� Junção de dados de duas ou mais tabelas em uma
única consulta

� Tipos
– Junção simples (simple-join)
– Junções externas (outer-join)
– Produtos cartesianos

� Decisões para o plano de execução de join
– Métodos de acesso
– Método de Join: Nested loop, Hash join, Sort merge join,

Cartesian join, Outer join
– Ordem de Join: ordem de junção das tabelas, principal e

secundária(s)

Índices
� Índices concatenados

– Índices compostos por mais de uma coluna da tabela,
aumentam a seletividade do índice e podem eliminar o
acesso à tabela

– Orientações:
• Colunas usadas com freqüência na cláusula WHERE
• Colunas referenciadas em muitas consultas
• Ordem das colunas: a ordem mais utilizada, colunas mais

seletivas posicionadas na frente

� Índice baseado em função
– Para que o índice baseado em função seja usado, a

cláusula WHERE ou ORDER BY deve ser idêntica à
expressão do índice:

SELECT ... FROM funcionario WHERE UPPER(nome_func)

33

Índices (cont)
� Índices e chaves estrangeiras (FK)

– Oracle recomenda a criação de índices em todas as FKs:
• Agilização dos joins de tabelas
• Para não haver ‘lock’ em toda a tabela filha, quando se realiza,

na tabela pai, ‘delete’ em um registro ou ‘update’ na coluna
referenciada pela FK

– Nota: deve-se verificar sempre se o custo de manutenção
do índice não é superior ao seu benefício

� Monitoramento de uso de índices:
ALTER INDEX <indice> MONITORING USAGE;

(...)
SELECT * FROM V$OBJETC_USAGE; (coluna USED)

Índices (cont)
� Índice Bitmap

– Tabelas com grande quantidade de registros
– Coluna com baixa quantidade de valores distintos (pouco

seletiva)
– Para um determinado valor são recuperados muitos

registros
– Uso freqüente das colunas em funções de agrupamento

(sum, count)
– O índice bitmap economiza espaço, se comparado com b-

tree
– Exemplo:

– Obs: Índices bitmap só podem ser usados em condição com
“=“

Sexo = “M”
Sexo = “F”

1
0

1
01

0
1
0

1
0

1
0

34

Índices: critérios para criação
� Uso freqüente na cláusula WHERE
� Uso freqüente em Joins
� Possuam alta seletividade (índices b-tree)
� Não use índice b-tree em colunas com pouco

valores distintos (índices bitmap são mais
adeqüados)

� Evitar índice em colunas alteradas
freqüentemente (degrada operações DML)

Coleta de Estatísticas

� Estatísticas são dados sobre os objetos de BD:
– Número de registros em uma tabela
– Número de blocos de uma tabela ou índice
– Tamanho médio do registro de uma tabela
– Número de valores distintos de uma coluna
– Número de valores nulos de uma coluna
– Tamanho médio de uma coluna
– Distribuição dos valores de uma coluna (histograma)
– Número de blocos folhas em índices
– Quantidade de níveis em um índice
– Fator de bloco de um índice

35

Coleta de Estatísticas (cont)
� Package DBMS_STATS

GATHER_TABLE_STATS()
GATHER_INDEX_STATS ()
GATHER_SCHEMA_STATS ()
GATHER_DATABASE_STATS()

� Comando ‘ANALYZE’ não é mais
recomendado pela Oracle

� Coleta dinâmica de estatísticas pode ser
controlada configurando o parâmetro
OPTIMIZER_DYNAMIC_SAMPLING (valores 0 a 10)

Hints de Otimização

SELECT /*+ <hint> */ ...
FROM ...
� Também podem ser utilizados em comandos

INSERT, UPDATE, DELETE
� Se aplica ao bloco SQL onde ele existe e

sobrescreve parâmetros de instância e de sessão
� Nota: use ‘hints’ como último remédio, pois eles

aumentam o esforço de manutenção
� Nota 2: verifique se a situação que justificou o hint

ainda existe, ou se na nova situação o hint não está
prejudicando a performance

36

Hints (cont)
� Hints para modo de otimização: ALL_ROWS,

FIRST_ROWS(N)
� Hints de método de acesso: full table scan,

index scan, fast full index scan, etc
� Hints de métodos de join: nested loop, hash

join, sort merge join
� Hints de ordem de joins e operações: ordered

e leading

Hints (cont)
select /*+ FULL(func) */ cod_func --full table scan
from funcionarios func
where cod_funcionario > 20;

select /*+ USE_MERGE(d f) */ f.cod_func, f.cod_dep
from funcionarios f, departamento d
where f.cod_dep = d.cod_dep;
-- método sort merge nas tabelas d e f

select /*+ LEADING(d) USE_NL(f) */ f.cod_func, f.cod_dep
from funcionarios f, departamento d
where f.cod_dep = d.cod_dep;
-- nested loop com a tabela f sendo a “inner”, driving table é

a tabela d

37

Estratégias de Otimização

� Redução do tempo de resposta ou redução do uso
de recursos de determinado comando:
– Redução da carga: com um plano de execução mais

eficiente (criação de índices ou forçando um determinado
caminho de acesso)

– Balanceamento de carga: execução de processos ‘batch’ e
relatórios pesados em horários de menor uso do sistema

– Paralelização da execução: comandos que acessam
grande quantidade de dados podem ser paralelizados (não
recomendável para sistemas com alto nível de concorrência,
OLTP)

Agenda

� Arquitetura BD Oracle
� Segurança
� Backup
� Gerência de Objetos do BD
� Performance

�Otimização de aplicações (SQL Tuning)
– Otimização da instância (BD)

� Design

38

Configuração da Instância
(manual)

Configuração mínima

� Initialization Parameters
DB_NAME = finance
DB_DOMAIN = hq.company.com
CONTROL_FILES = ('/u01/database/control1.dbf',

'/u02/database/control2.dbf')
DB_BLOCK_SIZE = 8192
DB_BLOCK_BUFFERS = 12000 # this is approximately 100 Mb
DB_FILES = 200 # Maximum 200 files in the database
SHARED_POOL_SIZE = 100000000 # 100 Mb
PROCESSES = 80 # Would be good for approximately 70

# directly connected users
# log_archive_XXX
# Set various archiving parameters

39

Configuração da PGA (exemplo)
� Otimização da PGA:

WORKAREA_SIZE_POLICY = AUTO
PGA_AGGREGATE_TARGET = 100000K
sendo:
PGA_AGGREGATE_TARGET = (TOTAL_MEM * 80%) * 20% for an
OLTP system
PGA_AGGREGATE_TARGET = (TOTAL_MEM * 80%) * 50% for a DSS
system

� Configuração manual da PGA:
WORKAREA_SIZE_POLICY = MANUAL
SORT_AREA_SIZE = ???
HASH_AREA_SIZE = ???
BITMAP_MERGE_AREA_SIZE = ???
CREATE_BITMAP_AREA_SIZE = ???

Configuração na Instalação

40

Alertas

� Notificam quando determinadas
métricas (limites) são alcançadas ou
ocorrem certos eventos.

� Exemplo 1: Tablespace 85% full:
'warning', 97% full: crítico (limites)

� Exemplo 2: ‘Snapshot too old’,
‘Recovery area low on free space’
(eventos)

Diagnósticos

� Automatic Database Diagnostics
Monitor (ADDM)
– Coleta periódica (uma vez por hora) de

informações de status do DB e carga de
trabalho na AWR (Automatic Workload
Repository)

– Análises estatísticas: tempo de cpu e
memória, sobrecarga de conexões,
ocorrências de 'lock' em transações

41

Métricas

Alertas (exemplo)

42

Alertas por e-mail

Host Name=prod.empresa.com
Metric=Response Time per Call
Timestamp=08-NOV-2003 10:10:01 (GMT -

7:00)
Severity=Critical
Message=Tempo de resposta por chamada

excedeu limite estabelecido. Verifique
análise ADDM.

Rule Name=
Rule Owner=SYSMAN