DBAs World
47 pág.

DBAs World

Disciplina:Bancos de Dados para Redes83 materiais668 seguidores
Pré-visualização4 páginas
– Usuários não criam ou
modificar quaisquer
objetos de BD (meta-
dados)

– Usuários têm privilégios
para atualizar objetos
nos schemas (A)

Agenda

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

14

Backup

� Backup físico: data files, control files, spfile,
archived redo logs

� Backup lógico: tabelas, schemas,
tablespaces

� Backup on-line x off-line (inconsistente x
consistente): BD aberto, redo logs não
aplicados aos data files x BD fechado, data
files sem transações pendentes (redo)

� Backup “quente” (online), somente se BD
operando em ARCHIVELOG mode

Backup (cont)
� Backup ‘full’ x backup incremental
� Restauração de BD a partir de um

backup consistente: imediato
� Restauração de BD a partir de um

backup inconsistente: transações
pendentes, restaura os data files e
aplica as transações constantes do
Redo Log –> BD consistente!

15

Backup (dicas)

� BD operando em ARCHIVELOG MODE
� Utilizar o RMAN (automatizar o backup)
� Fazer backup também do control file e

spfile (server parameter file) –
pequenos-

� Manter Backup Externo

Agenda

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

16

Objetos de Schema
� Clusters
� Database links
� Database triggers
� Dimensions
� External procedure libraries
� Indexes and index types
� Java classes, Java resources, and Java

sources
� Materialized views and materialized view logs

Objetos de Schema (cont)
� Object tables, object types, and object views
� Operators
� Sequences
� Stored functions, procedures, and packages
� Synonyms
� Tables and index-organized tables
� Views

17

Objetos, tablespaces e datafiles

Outros objetos
Objetos do BD não pertencentes a um schema,

mas igualmente acessíveis através de
comandos SQL

� Contexts
� Directories
� Profiles
� Roles
� Tablespaces
� Users
� Rollback segments

18

Dicionário de Dados

� Conjunto de tabelas e views com
informações do banco de dados

� Usuários, tabelas, constraints, espaço
disponível em cada schema (...)

� Atualizados pelo Oracle conforme
modificações realizadas no BD (read-only
para usuários)

� Utilizado pelo Oracle em todas as suas
operações

Partições

� Tabelas grandes (milhões de registros)
� Partições têm mesma estrutura lógica, mas

conteúdo (linhas) distintas
� Exemplo: particionamento por ano
� Vantagens:

– operações de manutenção (carga, criação de
índices, rebuild, backup/recovery) podem ser
realizadas no nível da partição, com melhor
performance

– Operações de consultas referenciam a tabela,
sem conhecer o particionamento subjacente
(transparente para a aplicação)

19

Métodos de Particionamento

Particionamento Composto

20

Clusters

� Grupo de tabelas armazenadas
fisicamente no mesmo bloco

� Aplicável a tabelas com colunas
comuns, onde os registros relacionados
são freqüentemente manipulados juntos

� Exemplo: Nota Fiscal e itens da nota

Clusters

21

Ferramentas

� Ferramenta: Enterprise Manager
– Monitoramento e administração dos servidores de

bancos de dados e instâncias Oracle
– Interface Web

� Ferramenta: TOAD
– Gerência dos objetos de banco de dados
– Execução de SQL
– Interface Gráfica

Enterprise Manager

22

Enterprise Manager

TOAD

23

TOAD

Agenda

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

24

Performance

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

Linguagem SQL

� Linguagem não procedural: o que se
deseja

SELECT employee_id, last_name
FROM employees
WHERE last_name = ‘SILVA’;

� Não indica como o comando é
executado

25

Plano de Execução

� Comando EXPLAIN PLAN...
� Opção AUTOTRACE no SQL*Plus
� Plano de Execução é gravado na tabela

PLAN_TABLE
� O Otimizador Oracle é baseado em

custo (CBO), a partir da versão 10g

Plano de Execução (exemplo)

26

Processamento de comandos

Analisador
(parser)

Otimizador

Gerador de
registros

Dicionário
de Dados

Usuário

Consulta
SQL

Consulta
analisada

pelo Parser

Estatísticas

Plano de
execução

Executor
SQL

Resultado

Funcionamento do Otimizador
Oracle
� Execução de expressões e condições
� Transformação de comandos
� Avaliação do objetivo da otimização

(por exemplo, obter primeiros registros
mais rápido)

� Seleção do caminho de acesso
� Seleção da ordem de ‘joins’

27

Expressões (exemplo)
� Como o CBO avalia o operador IN

select fist_name, last_name
from funcionarios
where last_name IN (’SMITH’, ’KING’, ’JONES’)

transformada em

select fist_name, last_name
from funcionarios
where last_name = ’SMITH’ OR last_name = ’KING’ OR

last_name = ’JONES’

Transformação de queries
(exemplo)
� Como o CBO modifica subqueries aninhadas

SELECT *
FROM orders
WHERE customer_id IN
(SELECT customer_id FROM customers);

Se coluna customer_id é a Primary Key ou Unique Key da
tabela customers:

SELECT orders.*
FROM orders, customers
WHERE orders.customer_id = customers.customer_id;

� Importante: a query transformada retorna o mesmo resultado da
inicial

28

Ações para melhoria de SQL
� Atualizar periodicamente as estatísticas de

otimização (de tabelas e índices)
� Analisar e alterar o plano de execução
� Reestruturar o comando
� Reestruturar os índices envolvidos no comando
� Modificar ou desabilitar constraints e triggers (obs:

“aprecie com moderação”)
� Reestruturar os dados
� Armazenar o plano de execução
� Reduzir a quantidade de acesso aos dados

Objetivos da Otimização
� Como são estabelecidos os objetivos da

otimização?
– Parâmetro OPTIMIZER_MODE =

• ALL_ROWS: melhor throughput
• FIRST_ROWS_n: melhor tempo de resposta para

primeiros ‘n’ registros
• FIRST_ROWS (n=1) �não aconselhável na versão

Oracle 10g (sem estatísticas, não usa otimização por
custo)

– Pode ser definido no nível da instância ou de uma
única sessão

29

Objetivos da Otimização (cont)
– Uso de ‘HINT’ no comando

• Hints são comentários inseridos no comando e
interpretados pelo otimizador como uma indicação do
usuário para a geração do plano de execução

SELECT /*+ ALL_ROWS */ COD_FUNC
FROM FUNCIONARIOS FUNC
WHERE COD_FUNCIONARIO > 20

SELECT /*+ FIRST_ROWS(10) */ COD_FUNC
FROM FUNCIONARIOS FUNC
WHERE COD_FUNCIONARIO > 20

Parâmetros do Otimizador
OPTIMIZER_MODE

OPTIMIZER_FEATURES_ENABLE = 10.1.0

CURSOR_SHARING = EXACT

DB_FILE_MULTIBLOCK_READ_COUNT

– Quantidade de blocos lidos em cada operação de I/O para “full
table scan” ou “index fast full scan”

– Default: 8
– Quanto maior este valor, menor o custo dos métodos de acesso
– Valor acima de 16 costumam não representar ganhos de

desempenho

PGA_AGREGATE_TARGET = 20% SGA ou 10MB (o maior)

30

Plano de Execução (exemplo)
SELECT f.cod_func, f.nome, f.salario, c.cargo_desc, d.dep_desc
FROM funcionarios f, cargos c, departamentos d
WHERE f.cod_cargo = c.cod_cargo
AND f.dep_cod = d.dep_cod
AND f.cod_func < 1002;

ID | OPERATION | NAME | ROWS | BYTES | COST
---------------------------------------------------------------------------------

0 | SELECT STATEMENT | | 3 | 189 | 10
1 | NESTED LOOPS | | 3 | 189 | 10
2 | NESTED LOOPS | | 3 | 141 | 7
3 | TABLE ACESS FULL | FUNCIONARIO | 3 | 60 | 4
4 | TABLE ACESS BY INDEX ROWID | CARGO | 19 | 513 | 2
5 | INDEX UNIQUE SCAN | CARGO_ID_PK | 3 | 189 | 10
6 | TABLE ACCESS BY INDEX ROWID | DEPARTAMENTO | 27 | 432 | 2
7 | INDEX UNIQUE SCAN | DEPT_ID_PK | 1 | |

Predicate information (identified by operation id):
3 – filter (“F”.”COD_FUNC”<1002)
5 – access (“F”.”COD_CARGO”=“C”.”COD_CARGO”)
7 – access (“F”.”DEP_COD”=“D”.”DEP_COD”)

1

2
3

4
5

6

7

Métodos de Acesso

� FULL TABLE SCAN
– Leitura de todos os blocos em seqüência
– Nas situações em que se recupera um percentual

grande da tabela (> 10%), ou a tabela tenha
poucos blocos, esse método é menos custos

� ROWID SCAN
– Rowid contém o endereço do registro (arquivo

físico + bloco + localização no bloco)
– É o método