2015.1- INF1340 - BD2
SGBD <Oracle>
Grupo:
• Amanda
• Christian
• Gustavo
2015.1- INF1340 - BD2
Breve Histórico
Larry Ellison e amigos fundam a SDL(Software Development Laboratories)
Primeira versão do oracle (Oracle V1) foi desenvolvida mas nunca lançada
oficialmente
Oracle V2 começou a ser comercializado e foi o primeiro banco de dados relacional.
A primeira versão não suportava transações mas implementava o SQL básico e
suportava “queries” e “join”
Após 4 anos, é lançado Oracle V3. Foi escrito em C e suporta COMMIT e
ROLLBACK, ou seja, funcionalidades para transações.
Oracle V7 aparece com novas funcionalidades : stored procedures, triggers e
Referential integrity
Oracle V8 já incorpora uma JVM(Java Virtual Machine) nativa conhecida como
“Aurora”. Essa versão veio com diversas melhorias como suporte a desenvolvimento
orientado a objetos.
O banco de dados Oracle é um dos mais utilizados. A partir da versão 12c passou a
ter suporte as principais plataformas(windows, linux e solaris).
De 1992 até hoje, a Oracle comprou diversas empresas. Uma compra importante foi
a empresa SUN. O SGDB da Oracle oferece um excelente suporte a
desenvolvedores java.
2015.1- INF1340 - BD2
Comparativo
Ranking dos principais gerenciadores de banco de dados baseado em sua popularidade.
Fonte : http://db-engines.com/en/ranking
2015.1- INF1340 - BD2
Comparativo
Como é calculado
1. Numero de menções dos sistemas em websites.
2. Interesse geral nos sistemas. Utiliza-se Google Trends para essa
pesquisa.
3. Frequência de discução técnica sobre o sistema. Utiliza-se Stack
Overflow e DBA Stack Exchange.
4. Numero de oferta de trabalho sobre o sistema mencionado.
5. Numero de profiles em redes sociais profissionais(serias) onde o
sistema é mencionado. Utiliza-se LinkedIn
6. Relevancia em redes sociais. Numero de Tweets no twitter sobre o
sistema.
2015.1- INF1340 - BD2
Arquitetura
Diagrama de Componentes
Instancia
SGA
Shared Pool
Processo
de usuario
Cache de
Dicionários
de Dados
Processo
do servidor
PMON
SMON
Buffer
de
Redo
Log
Cache de
Buffer de
Banco de
Dados
Cache de
Biblioteca
Java Pool
DBWn
LGWR
Large Pool
CKPT
Outros
PGA
Arquivos
de
parametros
Arquivos
de
senha
Arquivos
de
dados
Arquivos
de
Controle
Arquivos
de
redo log
Banco da Dados
Arquivos
de
Log
arquivados
2015.1- INF1340 - BD2
Arquitetura
Descrição de Componentes
• SGA
– Shared Pool
– Database Buffer Cache - mantém cópias dos blocos de dados lidos
– Redo Log Buffer - Mantém as informações sobre as mudanças ocorridas
• Background Process
– DBWn (database write)
– LGWR (Log Write)
– SMON (System Monitor)
– PMON (Process Monitor)
– CKPT (Checkpoint Monitor)
• Banco de dados
1.Redo Log: É o que torna possível refazermos uma transação
2. Controle: Arquivos binários que armazenam diversas informações
necessárias para manter o estado atual do banco de dados.
3. Dados: Armazena o dicionário de dados e objetos do usuário.
2015.1- INF1340 - BD2
Otimização
Introdução
•
O plano de execução é mostrado utilizando-se o comando “EXPLAIN PLAN”.
– O Comando “EXPLAIN PLAN” mostra o plano de execução escolhido pelo
otimizador da oracle para os comandos : “Select”, “Update”, “Insert” e
“Delete”.
– O Plano de execução é a sequência escolhida pelo otimizador que será
utilizado para executar a query.
•
A tabela de execução contém informações sobre:
– Otimização: O custo e a cardinalidade de cada operação
– Particionamento: O conjunto de partições acessadas (Tabelas podem ser
divididas em partes)
– Execução paralela: O método de distribuição para executar o “Join” (Nested
Loop/Hash Join/Sort Merge Join/Cartesian Join)
2015.1- INF1340 - BD2
Otimização
Introdução
•
Mudam o plano de execução :
– Schemas diferentes. Exemplo : Dois usuários podem apontar para diferentes
objetos no mesmo banco de dados e o plano de execução mudar.
– Custos diferentes.
São levados em consideração : Volume de dados e estatísticas, os tipos e
valores das variáveis e os parâmetros de inicialização.
•
Alem do “EXPLAIN PLAN”, é possível utilizar outros recursos. Um deles é
V$SQL_PlAN.
– V$SQL_PLAN : Mostra o plano de execução de um comando em SQL.
– Suas caracteristicas é semelhante a tabela de execução mas o comando
V$SQL_PLAN tem certas vantagens sobre o “EXPLAIN PLAN”, pois você
não precisa levar em consideração o ambiente de execução em que aquele
plano está sendo executado.
– Em resumo : mais geral.
2015.1- INF1340 - BD2
Otimização
Tabela de execução
A tabela de execução é gerada como output quando se utiliza o comando
“EXPLAIN PLAN”. É uma tabela temporária que fica disponível a todos os
usuários com informações sobre o plano de execução.
2015.1- INF1340 - BD2
Otimização
Executando o “Explain plan”
EXPLAIN PLAN FOR
SELECT last_name FROM employees;
EXPLAIN PLAN FOR
SET STATEMENT_ID = ’st1'
INTO my_plan_table
FOR
SELECT last_name FROM employees;
2015.1- INF1340 - BD2
Otimização
Exemplo comentado de um plano
a. O Exemplo a esquerda mostra
uma consulta em SQL utilizando o
“EXPLAIN PLAN” para demostrar
o funcionamento do comando
“SELECT”. O codigo em SQL faz
uma consulta na tabela
“employees” e procura telefones
que começam com 650.
a. A tabela de execução abaixo do
comando mostra a ordem de
execução.
Id_0) Executa o “SELECT”
É importante notar a identação.
Os comandos de dentro
executam em ordem para
executar os comandos de fora, ou
seja, o SELECT(id_0) representa
fazer um FULL TABLE SCAN na
tabela EMPLOYEES.
2015.1- INF1340 - BD2
Otimização
Como funciona o CBO(Cost-based optimizer) da
oracle?
•
O Otimizador gera um conjunto de possíveis planos de acordo com dicas e
informações disponíveis
•
Depois ele estima o custa de cada plano baseado em estatísticas que ele vai
calcular utilizando informações do dicionario, características da tabela, índices e
partições sendo acessadas pelo comando.
•
O custo calculado é proporcional aos recursos necessários para executar os
comandos de um certo plano. Esses “recursos” incluem IO,CPU e memoria.
•
O otimizador compara os custos e escolhe o que tem menor custo
•
Isso tudo acontece dentro de um otimizador chamado CBO (Cost-Based
Optimizer)
No proximo slide tem uma representação dos componentes e a ordem de
execução deles.
2015.1- INF1340 - BD2
Otimização
CBO (cost based optimizer)
2015.1- INF1340 - BD2
Otimização
Componentes CBO
●
QUERY TRANSFORMER (Transformador de query)
○ O principal objetivo dele é determinar se é vantajoso trocar a forma de
uma query para que se possível, gerar um plano de execução melhor.
●
ESTIMATOR (Estimador)
○ Gera trés tipos de medições
■ Seletividade
■ Cardinalidade
■ Custo
○ Essas estimativas não são independentes. Elas são relacionadas entre si.
Uma é derivada da outra.
○ O objetivo principal do estimador é estimar o custo de um plano. Caso
tenha “Estatísticas” disponíveis, ele vai levá-as em consideração para
computar as medições.
●
PLAN GENERATOR (Gerador de plano)
○ A função do gerador de plano é gerar diversos planos(um conjunto) e
escolher aquele que tem o menor custo.
2015.1- INF1340 - BD2
Otimização
Exemplo tabela & árvore
2015.1- INF1340 - BD2
●
Manual
●
RMAN (Oracle Recovery Manager)
Recuperação
○
Repositório de dados sobre backups
○
Backups incrementais
■ Backup consistido apenas dos blocos de dados alterados desde o
último backup.
○
Block media recovery
■ Reparação de pequenos blocos de dados corrompidos sem a
necessidade de utilizar um backup como um todo.
○
Binary Compression
■ Reduz o tamanho geral do backup.
2015.1- INF1340 - BD2
●
RMAN
○ Encrypted Backups
■ O backup é guardado de maneira encriptada
■ Modos: transparent, password-protected e dual-mode.
● Chave cifrada com, respectivamente, a chave mestra do DB, a
senha escolhida e ambos.
○
○
●
Recuperação
Duplicação automática
Conversão de dados entre plataformas
Oracle Flashback
○ Complemento ao backup. Permite ver estados passados de certo dado e
“rebobiná-lo” sem a necessidade de uma restauração através de backup.
2015.1- INF1340 - BD2
Recuperação
Comandos & exemplos
●
Comandos
○ %rman - Inicia o RMAN
○ BACKUP OPERAND
●
BACKUP DATABASE PLUS ARCHIVELOG;
BACKUP
INCREMENTAL LEVEL 1 CUMULATIVE
SKIP INACCESSIBLE
DATABASE;
RUN
{
ALLOCATE CHANNEL dev1 DEVICE TYPE DISK FORMAT '/disk1/%U';
ALLOCATE CHANNEL dev2 DEVICE TYPE DISK FORMAT '/disk2/%U';
BACKUP AS COPY
TABLESPACE SYSTEM, tools, users, undotbs;
}
2015.1- INF1340 - BD2
Recuperação
Comandos & exemplos
RUN #script daily
{
RECOVER COPY OF DATABASE
WITH TAG 'incr_update';
BACKUP
INCREMENTAL LEVEL 1
FOR RECOVER OF COPY WITH TAG
'incr_update'
DATABASE;
}
BACKUP
DEVICE TYPE sbt #tape duas semanas
BACKUPSET
COMPLETED BEFORE 'SYSDATE-14'
DELETE INPUT;
RUN
{
SET MAXCORRUPT FOR
DATAFILE 1,2,3,4,5 TO 1;
BACKUP CHECK LOGICAL
#logico e físico
DATABASE;
}
2015.1- INF1340 - BD2
Segurança & Autorização
Usuários, Grupos, Papéis
Quem tem acesso ao que???
A partir de onde?????
Quando???????
Como?????
Identificação do
usuário
Controle de
Acesso
2015.1- INF1340 - BD2
Segurança & Autorização
Usuários, Grupos, Papéis
●
USER: Identificação de um usuário que irá possuir privilégios (Grant) para
acessar e alterar dados.
●
PASSWORD: Criada junto do usuário é o elemento mais precioso do banco de
dados por parte do usuário.
●
SCHEMA: Criado pelo banco toda vez que um USER é criado. É uma coleção de
objetos pertencentes a um usuário.
●
OBJETOS DE USUÁRIO: Tables, Types, Views, Clusters, Indexes, Snapshots,
Sequences, Procedures, Functions e Packages.
●
OBJETOS ADMINISTRATIVOS: TableSpaces, Users, Profiles, Roles, Undo
Segments e Directories
●
ROLES: É um conjunto de privilégios que pode ser concedido a um usuário
2015.1- INF1340 - BD2
Segurança & Autorização
Comando CREATE ROLE
CREAT ROLE é usado para criar uma série de privilégios
CREATE ROLE dw_manager;
IDENTIFIED BY warehouse;
CREATE ROLE dw_manager;
IDENTIFIED GLOBALLY;
2015.1- INF1340 - BD2
Segurança & Autorização
Privilégios em comandos GRANT e REVOKE
GRANT REFERENCES (employee_id),
UPDATE (employee_id, salary, commission_pct)
ON hr.employees
TO oe;
REVOKE REFERENCES
ON hr.employees
FROM oe
CASCADE CONSTRAINTS;
2015.1- INF1340 - BD2
●
●
●
Procedimentos
Armazenados
Conjunto de comandos e parâmetros armazenados. Reutilizável e promove
metodologia padrão para exercer certa tarefa, sem a necessidade de duplicidade
de códigos.
Consiste em, geral, três partes: declarações, código em si (obrigatório) e tratador
de excessões.
Pode ser utilizado tanto SQL como PL/SQL ou, até mesmo, Java.
Header AS
[declaration statements
...]
BEGIN
…
[EXCEPTION
...]
END;
CREATE OR REPLACE nome_procedimento(arg1
tipo, ...) AS
BEGIN
....
END nome_procedimento;
2015.1- INF1340 - BD2
Gatilhos
● Conjunto de comandos executados a partir da ocorrência de certo
evento.
● Criação similar à procedimentos, mas com mais uma seção em sua
definição;
CREATE OR REPLACE TRIGGER nome_trigger
BEFORE<ou>AFTER event(DELETE OR INSERT OR UPDATE…)
ON <schema/table<ou>view>
FOR EACH ROW
WHEN (condições)
DECLARE
…
BEGIN
...
END;
2015.1- INF1340 - BD2
Exemplo de procedimento armazenado
ativado por gatilho
● Procedimento pode ser executado através de um gatilho
○ Comando CALL (obsoleto) ou o uso direto do nome do
procedimento.
CREATE OR REPLACE TRIGGER Example
AFTER INSERT ON emp
FOR EACH ROW
BEGIN
Insert_row_proc;
END;
CREATE OR REPLACE PROCEDURE Insert_row_proc AS
BEGIN
INSERT INTO emp@Remote VALUES ('x');
EXCEPTION
WHEN OTHERS THEN
INSERT INTO Emp_log VALUES ('x');
END;
2015.1- INF1340 - BD2
●
●
●
●
Referências
Histórico
○ Historia do banco de dados
http://en.wikipedia.org/wiki/Oracle_Database#History
Último acesso : 04/06/2015
Comparativo
○ http://db-engines.com/en/ranking
Ultimo acesso : 04/06/2015
Arquitetura
○ http://www.linhadecodigo.com.br/artigo/543/oracle-arquitetura-e-seuscomponentes.aspx
Último acesso : 04/06/2015
○ http://www.linhadecodigo.com.br/artigo/99/a-arquitetura-do-oracle.aspx
Último acesso : 04/06/2015
Otimização
○ Documentação oficial da oracle (USING EXPLAIN PLAN)
http://docs.oracle.com/cd/B19306_01/server.102/b14211/ex_plan.htm#g4223
1
Último acesso : 04/06/2015
○ Documentação oficial da oracle(Introduction to the optimizer)
http://docs.oracle.com/cd/B10500_01/server.920/a96533/optimops.htm
Último acesso : 04/06/2015
2015.1- INF1340 - BD2
Referências
●
Recuperação
http://docs.oracle.com/cd/E11882_01/backup.112/e10642/rcmintro.htm
Último acesso : 02/06/2015
●
Segurança
http://www.ebah.com.br/content/ABAAAA89AAA/usuario-x-squema-no-oracle Ultimo acesso : 04/06/2015
http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_9013.htm
Último acesso : 04/06/2015
●
Procedimentos armazenados e gatilhos
https://docs.oracle.com/cd/E12151_01/doc.150/e12155/triggers_proc_mysql.htm#
g1049668
Último acesso : 01/06/2015