IV Ciclo de Palestras de Banco de Dados UNINOVE
Oracle para Desenvolvedores:
Recursos e Técnicas( visões gerais )
André Luís P. Santos
Uninove – campus Vergueiro
São Paulo, 25 de outubro de 2016
Sobre o palestrante
André Luís P. Santoshttps://www.linkedin.com/in/andrelpsantos
• Experiência profissional desde 1990 na área de Informática, atuando com desenvolvimento de sistemas, em empresas de vários setores.
• Exerceu funções de "AD" (administração de dados) e "DBA de Desenvolvimento/Homologação", com foco em modelagem de dados e implementações em Oracle.
• Realizou trabalhos também com BI (business intelligence, data warehousee data marts) e implantação de ERP (SAP R/3).
• Atividade de mais de 12 anos como professor em ensino universitário e técnico.
• Áreas de interesse:‒ Bancos de dados: modelagem de dados; projeto físico; VLDB; BI e DW;
otimização de desempenho (performance tuning) com Oracle Database.‒ Metodologias ágeis (XP - Extreme Programming, Scrum, FDD).‒ Ensino e treinamento
Banco de Dados é trabalho apenas do DBA ? ( Não!!! )
Fonte: http://vidadeprogramador.com.br/2012/01/27/duelo-de-titas/
Banco de Dados é trabalho apenas do DBA ? ( Não!!! )
Fonte: http://vidadeprogramador.com.br/2016/03/24/praga/
Modelo de Dados é trabalho apenas do AD ? ( Não!!! )
Conhecimentos importantes na formação
• Em cursos específicos , pode-se obter conhecimentos complementares em:� Tecnologias e produtos� Padrões, metodologias� Idiomas
• Em cursos superiores (faculdade, formação universitária) há o aprendizado de fundamentos que são muito importantes para desempenho profissional. Por exemplo:� Modelagem de Dados� Teoria Relacional� Normalização� Estrutura de Dados e Algoritmos� Fundamentos de Computação� Estatística (e outras disciplinas da Matemática)
• Aprendizado autodidata : estudar sempre! ;)� Livros� Internet (documentação online, artigos, sites, blogs, vídeos)� Participação em grupos técnicos, fóruns de discussão
DB-Engines Ranking
Fonte: http://db-engines.com/en/ranking
The DB-Engines Ranking ranks database management systems according to their popularity.
The ranking is updated monthly.
DB-Engines Ranking – Trend Popularity
Fonte: http://db-engines.com/en/ranking_trend
Desenvolvedor “Full-Stack”
O perfil de desenvolvedor “full stack” corresponde àquele que pode lidar com várias camadas de software de um sistema (back-end — incluindo database — até front-end).
Fonte: https://www.linkedin.com/pulse/%E0%B8%84%E0%B8%93%E0%B9%80%E0%B8
%9B%E0%B8%99-full-stack-developer-%E0%B9%83%E0%B8%8A%E0%B8%AB%E0%B8%A3%E0%B8%AD%E0%B9%
84%E0%B8%A1-pongsakorn-poosankam
Fonte: http://www.thatsoftwaredude.com/content/
6219/working-as-a-full-stack-developer
SGBD “objeto-relacional”
• Confiabilidade
• Segurança
• Desempenho (CBO, processos em background, índices baseados em função, cluster tables, index-organized tables, etc.)
• Tolerância a falhas
• Configurável, gerenciável, flexível
• Compatibilidade a padrões
• Multi-plataforma
• Escalabilidade (caches, MTS, particionamento, paralelismo)
• Soluções para alta disponibilidade (RAC / grid)
• Soluções para contingência (Data Guard / Standby Database)
• Recursos para desenvolvimento: compatibilidade SQL ANSI/ISO, PL/SQL (trigers, stored procedures, functions, packages), funções analíticas, visões materializadas, tabelas temporárias, Java stored procedures, external tables, etc.
Oracle Database
• Edições:• Enterprise Edition
• Standard Edition (“Standard Edition 2” – a partir da versão 12c)
• Standard Edition One (não mais disponível a partir da versão 12c)
• Personal Edition
• Express Edition (“Oracle XE”, totalmente gratuita, com limitações técnicas — até 11 GB de armazenamento, 1 GB RAM, 1 CPU)
• Plataformas (dependendo da versão):• Unix (Solaris, AIX, HP-UX)
• Linux (considerar distribuições homologadas)
• Windows (32 bits e 64 bits)
• Outras plataformas: mainframes IBM (zLinux), OpenVMS.
• Documentação sobre recursos, edições e licenciamento:• Oracle: “Database Licensing Information”
Oracle Database – Edições e Plataformas
Licenças:
• OTN (Oracle Technology Network) – correspondente à instalação da Enterprise Edition — sem custo, mas sem uso comercial em servidores —apenas para uso individual de desenvolvedor (estudo, desenvolvimento de protótipos de aplicação, apresentações).
• Personal Edition – também corresponde à Enterprise Edition , para uso individual, com custo — permite assinatura de suporte (MOS – “My Oracle Support”, o antigo “Metalink”).
• Express Edition (“Oracle XE”, totalmente gratuita, com limitações técnicas — até 11 GB de armazenamento, 1 GB RAM, 1 CPU) – sem suporte formal.
• Enterprise / Standard – licenciamento voltado a empresas: por “usuário nomeado” ou por “processador” (usuários ilimitados). Além disso, há diversas “Options ” (opções”— exemplos: “Partitioning”, “Diagnostic Pack”*, “TuningPack”*, “In-Memory”, “OLAP”, “Advanced Security”, “Advanced Compression”) que podem ser adquiridas à parte, para a edição Enterprise.
* OBS. importante — configuração do parâmetro de inicialização , conforme licenciamento: “CONTROL_MANAGEMENT_PACK_ACCESS ” (“DIAGNOSTIC+TUNING”, “DIAGNOSTIC”, “NONE”).
Oracle – disponibilidade para Desenvolvedores
Oracle Database:
Conceitos importantes para
Desenvolvedores
(foco em “desempenho”)
Aplicações: Conceitos importantes para desempenho
Performance TuningEntre muitos aspectos, para otimização do desempenho de aplicações com Oracle, consideramos:
• Reaproveitamento de instruções SQL, em cache.
• Projeto lógico (modelo de dados, normalização, constraints).
• Projeto físico (recursos do SGBD).
• Coleta de estatísticas para o CBO (Cost-Based Optimizer).
• Instrumentação, análise e diagnóstico de problemas.
• Tuning de aplicações (SQL e PL/SQL – stored procedures).
• Tuning de instância/database.
Reaproveitamento de instruções SQL, em cache
• Um dos principais fundamentos para melhor escalabilidade de um servidor de banco de dados relacional é o uso de “SQL parametrizado ” — recurso também conhecido como “prepared statements”.
• No SGBD Oracle, isso é feito com a utilização das denominadas “bind variables” (“variáveis de ligação ”).
• Há um melhor aproveitamento do cache de instruções (“Shared Pool ” – “SQL Area ”), reutilizando planos de execução — realização de “soft parse”, ao invés de “hard parse”.
• Outro benefício na qualidade do software, com o uso de “bind variábles” é na segurança — evitando ataques de “SQL Injection ”.
Parse Execute Fetch
Reaproveitamento de instruções SQL, em cache
SELECT cod, nome FROM cliente WHERE cod = 514; Hard parse
SELECT cod, nome FROM cliente WHERE cod = 2091; Hard parse
SELECT cod, nome FROM cliente WHERE cod = 35; Hard parse
Sequência de instruções sem “bind variables” (uso de valores literais):
SELECT cod, nome FROM cliente WHERE cod = :p_cod; Hard parse
SELECT cod, nome FROM cliente WHERE cod = :p_cod; Soft parse
SELECT cod, nome FROM cliente WHERE cod = :p_cod; Soft parse
Sequência de instruções com “bind variables” (SQL parametrizado):
Projeto lógico (modelo de dados, normalização, constraints)
• O projeto lógico (derivado de um modelo conceitual de dados) deve ser adequado a:
‒ Paradigma tecnológico adotado (base relacional );‒ Finalidade da base de dados: transacional (OLTP) ou analítica
(OLAP);
• Considerar normalização (consistência) e “denormalizaçao” controlada (performance e históricos) — lembrando que algumas questões de performance podem ser resolvidas no projeto físico (SGBD).
• Constraints : além de serem fundamentais para garantir integridade de dados , também são consideradas pelo “otimizador ” (optimizer) na geração de planos de execução de SQL.
Conceitual Lógico Físico
Projeto físico (recursos do SGBD)
Pode-se considerar vários recursos disponíveis (conforme a versão, edição e licenciamento):
• Índices
• Visões Materializadas (“materialized views” – mviews), incluindo “query rewrite”
• Tabelas Temporárias (GTT – Global Temporary Tables)
• Particionamento
• Replicação
• Compressão
• In-Memory Database Cache
• Oracle OLAP (SQL access to OLAP Cubes)
• Stored Procedures (PL/SQL)
etc...
Coleta de estatísticas para o CBO(Cost-Based Optimizer)
• A coleta e atualização de estatísticas são fundamentais para o funcionamento do CBO (Cost-Based Optimizer).
• Falta de estatísticas ou estatísticas desatualizadas podem fazer com que o CBO gere planos de execução ruins para as instruções SQL.
• Considerar a geração e configuração de histogramas (tipo especial de estatística), para colunas com distribuição de dados não-homogênea .
• OBS.: Até a versão 10g do Oracle, algumas vezes ocorria um problema conhecido como “bind variable peeking” — que começou a ser solucionado na versão 11g, com a implementação de “Adaptive Cursor Sharing” (ampliado na versão 12c).
Instrumentação, análise e diagnóstico de problemas
• Instrumentação: geração de “traces”, identificação (DBMS_APPLICATION_INFO), debug, etc.
• Análise: verificação de eventos de espera (“wait events”), “queries ofensoras” e outros pontos de atenção.‒ Obs.: Algumas views dinâmicas de performance, associadas ao AWR
(Automatic Workload Repository) exigem licença do “Diagnostic Pack”.
• Diagnóstico: entender as causas dos problemas e possíveis soluções.
‒ Contenção e bloqueios
‒ Excesso de leituras físicas‒ Excesso de “hard parse” (alto uso de CPU)
‒ “Fragmentação” (?) — “chained rows”, linhas encadeadas em mais de um bloco de dados (ANALYZE).
‒ Planos de execução ruins (DBMS_STATS, EXPLAIN PLAN, DBMS_XPLAN.DISPLAY / DISPLAY_CURSOR).
‒ Conversões implícitas de “data types”.
Etc...
Tuning de aplicações(SQL e PL/SQL – stored procedures)
• Conhecer e aprimora-se em SQL é essencial para desenvolvedores que preocupam-se com bom desempenho de sistemas.
‒ Cuidado com a “facilidade” dos frameworks de persistência (ORM – Object-Relational Mapping). Por exemplo: “Hibernate”.
• O conhecimento de PL/SQL amplia as possibilidades.
Fonte: http://www.fabioprado.net/2014/04/o-que-e-tuning-como-tunar.htmlReferência: “Tuning When You Can’t Touch The Code”, Michael R. Ault
Tuning de instância/database
• Ajuste de performance de instância e database do SGBD é um trabalho de reponsabilidade do DBA – Database Administrator.‒ Configuração‒ Dimensionamento‒ Monitoração‒ Análise do ambiente
• Porém o conhecimento sobre “desenvolvimento de sistemas” (experiência) é importante para análise de problemas e formulação de possíveis soluções de forma mais assertiva.
• Portanto o trabalho em conjunto (DBA’s e Desenvolvedores) colabora para o sucesso do sistema.
Visão geral sobre
Arquitetura do Oracle:
• Instância e Database
• Estruturas de memória
• Estruturas físicas
Composição básica de um sistema Oracle Database Server:Arquitetura básica do SGBD Oracle
Hierarquias das estruturas de armazenamento de dados:
Organização de armazenamento no Oracle
Parâmetros:
• PCTFREE: define o percentual de espaço livre que deve ser reservado no bloco (não recebendo mais inserções), para crescimento das linhas (atualizações).
• PCTUSED: após atingir “pctused”, indica o percentual mínimo (na ocorrência de exclusões) a partir do qual o bloco poderá receber novas inclusões.
Bloco de dados (data block) Oracle
Visão geral sobre
Arquiteturas de Servidores de
Banco de Dados Oracle
Sistemas para processamento paralelo.
• SMP – Symmetric Multiprocessing (multiprocessamentosimétrico)
• Vários servidores, tanto baseados em processadores RISC como em CISC, utilizam essa arquitetura.
• Numa instalação Oracle, é usado o termo “single instance” (instância única).
• Cluster (agrupamento)• Exemplo: Oracle RAC (Real Application Cluster).
• MPP – Massively Parallel Processing (processamento maciçamente paralelo)
• Comercialmente apresentados em “database appliances ” (produtos que integram hardware e software) — Oracle Engineered Systems.
• Exemplo: Oracle Exadata (concorrentes: Teradata, Netezza).
Arquiteturas básicas de servidores
SMP – Symmetric Multiprocessing (multiprocessamento simétrico)
• Servidor com mais de um microprocessador (CPU), compartilhando a mesma memória (RAM) e discos.]
CPU CPU CPU...
RAM
Discos
Arquiteturas básicas de servidores
Cluster (agrupamento)• Genericamente, é um grupo de computadores (servidores,
eventualmente cada um “SMP”) que trabalham de forma colaborativa, formando um sistema único.
• No Oracle RAC, os computadores de um “cluster” compartilham um armazenamento centralizado (discos, ou servidor de “storage”).
• O conceito de “grid computing” engloba também a idéia de “cluster”.
Arquiteturas básicas de servidores
MPP – Massively Parallel Processing (processamento maciçamente/“massivamente” paralelo)
• Conceito de “shared nothing ” (nada compartilhado). Conjunto de computadores/módulos (geralmente com um “coordenador”), cada um com seu conjunto de discos.
• Conceito de particionamento “database sharding ”.
Arquiteturas básicas de servidores
interconexão / barramento
Servidor 1Instância 1 (ORACLE_SID = “dbsup1”)
SGAPGA
Processos em background
Server Process
Servidor 2Instância 2 (ORACLE_SID = “dbsup2”)
SGAPGA
Processos em background
Server Process
Undo Tbs. 2 Redo Log
Oracle Home
Undo Tbs. 2 Redo Log
Oracle Home
Storage
Tablespaces(dados, índices, etc.)
TEMP(Temporary tablespaces)
Oracle RAC – Real Application Cluster
Oracle Dataguard
• Oracle RAC – Real Application Cluster
• Solução para “alta disponibilidade ”.
• Benefício adicional: balanceamento de carga .
• Oracle Data Guard (Standby Database)
• Solução para “contingência ”.• É recomendado que a base replicada fique em local
distante para maior segurança contra desastres.
• Benefício adicional: base alternativa (replicada) para consultas longas (tipicamente para relatórios).
Oracle RAC – Real Application Cluster
Oracle 12c – Arquitetura “Multitenant”
Artigo: Artigo: "Many in One", de Arup Nanda - Oracle Magazine (September/October, 2013)Fonte: http://www.oracle.com/technetwork/issue-archive/2013/13-sep/o53cloud-1971984.html
Até a versão 11g: um database por instância.
Oracle 12c:vários databases por instância.
• CDB – Container Database• PDB – Pluggable Databases
Oracle Database:
Recursos importantes para
projeto e implementação
(foco em “desempenho”)
Alguns recursos para otimização de desempenho:
• Processamento com paralelismo• Particionamento de tabelas e índices• Índices:
* B-Tree* Baseados em função* Chave reversa* Bitmap
• Compressão de dados• Materilized Views• Tabelas temporárias• Carga de dados em modo otimizado (direct-path load)• External tables• Arranjo físico alternativos:
* Index-organized tables (IOT)* Cluster tables
Recursos para o projeto físico do banco de dados
ParalelismoProcessamento paralelo• Aproveita a capacidade de processamento de servidores com múltiplas
CPU’s (e múltiplos núcleos – “cores”).
• Pode ajudar muito no desempenho de grandes leituras físicas de tabelas (full table scans) e na criação de índices de tabelas grandes.Atenção – Após criar um índice com paralelismo, deve-se reajustá-lo para que não fique configurado com paralelismo como padrão:
CREATE INDEX ... PARALELL n;ALTER INDEX ... NOPARALLEL;
• Porém também pode prejudicar o processamento se utilizado em excesso, saturando a capacidade da máquina servidora ou tornando a execução da query SQL mais complexa (testar: custo / benefício).
• Para ajustar o grau de paralelismo (DOP – degree of parallelism), considerar parâmetros (cpu_count, parallel_threads_per_cpu), concorrência, margem de segurança, realizar testes comparativos.
• Paralelismo em consultas: conforme configuração da tabela (create / alter table) ou através de “hint” (PARALLEL).
• Paralelismo em DML: necessário habilitar (ENABLE PARALLEL DML), através de configuração ou “hint”, para utilização.
Particionamento
Particionamento de tabelas e índices
• Fisicamente uma tabela (e respectivos índices), podem ser segmentados em “partições”, conforme um critério (chave de particionamento).
• O objetivo é evitar a necessidade da leitura da tabela inteira, considerando apenas as partições necessárias.
• Em alguns casos (SQL-DML), pode colaborar com o processamento paralelo.
Índices
Índices B -Tree
• São os índices “convencionais” (index_type = normal).
• Mesmo em tabelas grandes, proporcionam bom desempenho em consultas que buscam relativamente poucos registros.
• Podem ser “unique” (mantém unicidade, valores únicos na chave) ou “non-unique”.
• Na definição de uma contraint “primary key” (chave primária) ou “unique” (chave de unicidade), normalmente é criado automaticamente um índice “unique” associado, com o mesmo nome da constraint.
• Para constraints de “foreign key” (chaves estrangeiras) o SGBD não cria índices automaticamente — porém, geralmente, é recomendável que existam índices correspondentes.
Índices
Índices baseados em Função
• São índices cuja chave contém uma expressão ou função (determinística).
• Permitem que buscas que utilizem funções ou expressões beneficiem-se de um índice.
• Exemplo 1 — o índice não será utilizado:
• Exemplo 2 — o índice poderá ser utilizado:
Dica : Em alguns casos, pode ser útil indexar sub-conjuntos de uma tabela, através de uma função que retorne “nulo” (NULL) para determinadas ocorrências de valores.
CREATE INDEX teste1 ON teste (nome);
SELECT ... FROM teste WHERE UPPER(nome) = 'FULANO';
CREATE INDEX teste2 ON teste (UPPER(nome));
SELECT ... FROM teste WHERE UPPER(nome) = 'FULANO';
Índices
Índices de Chave Reversa
• Em casos específicos, na criação de um índice B-tree, pode-se especificar a cláusula REVERSE, para inverter o armazenamento (sequência de bytes) das chaves e sua respectiva indexação.
Exemplo: o código “1234” seria armazenado como “4321”.
• As consultas SQL continuam sendo feitas de forma natural, transparente.
• É útil nos casos em que pode haver uma tendência de desbalanceamento na distribuição de valores no índice.Exemplo:
Valores reais:
A00001
A00002
A00003
Armazenados na chave:
10000A
20000A
30000A
Índices
Índices Bitmap
• Tipo especial de índice (estrutura diferente do B-Tree) que utiliza “mapa de bits”.
• São indicados para tabelas com grande volume de registros, mas utilizados em atributos (colunas) com “baixa cardinalidade” (quantidade de valores distintos relativamente pequena).
• OBS.: Não são aconselháveis para colunas que sofrem muitas atualizações (característica de sistemas OLTP), pois sua manutenção exige bloqueio (lock) de conjuntos de registros da tabela.
Índices – aspectos gerais
Diretrizes a serem consideradas na definição de índices:
• “Tablespaces” para índices : não afetam o desempenho.• Costuma-se criar tablespaces específicas para os índices
(para que fiquem separados das tabelas). • O principal motivo é uma melhor organização e facilitar
administração backups das tabelas.
• Cláusula “Online” : a cláusula ONLINE permite que a tabela continue a ser atualizada (DML: insert, update, delete) durante a criação ou reconstrução (rebuild) de um índice.
• Carga de dados : na carga de grandes volumes de dados, uma técnica comum é desabilitar os índices e recriá-los depois.
Índices – aspectos gerais
(continuação...)
• Valores nulos : nulos (NULL) não são indexados. Portanto:• Buscas por nulo (WHERE campo IS NULL) não utilizam
índice.• Constraints/índices “unique” permitem mais de uma
ocorrência de nulo (em conformidade com padrão SQL ANSI/ISO).
• Operador “Like” : consultas com operador LIKE que iniciem com caracteres “curinga”, impossibilitam a busca por índice.• Exemplo: Supondo que haja um índice por “cidade”.
- Utilizaria o índice: ... WHERE cidade LIKE 'RIO%'- Não utilizaria o índice: ... WHERE cidade LIKE '%RIO%'
Compressão de tabelas e índices
Compressão de dados
• Os dados de uma tabela podem ser comprimidos (através de métodos simples, como tabela de valores e ponteiros).
• Desse modo, além da economia de espaço em disco, pode-se ter melhor desempenho em grandes leituras físicas (e sem perda de desempenho para escrita).
• Nota : No Oracle, para compressão, é recomendável a carga de dados previamente ordenados, costumar ter uma taxa de rendimento melhor, pois a compressão é realizada por bloco de dados.
• Existe a opção “Advanced Compression”, que amplia os recursos de compactação.
Visões materializadas
Materilized Views
• Visões “materializadas” basicamente funcionam como “views” comuns, porém os dados são armazenados fisicamente (ou seja, não é apenas um objeto lógico).
• Os principais objetivos são: acelerar consultas complexas e replicação de dados.
• Em versões mais antigas, era utilizado o termo “snapshot” (essa sintaxe ainda é mantida para compatibilidade).
Tabelas temporárias
Global Temporary Table (GTT)
• São tabelas definidas, cujos dados são temporários (assim como o conteúdo dos eventuais índices):
• ON COMMIT DELETE ROWS: dados persistem até a finalização da “transação ” (commit / rollback).
• ON COMMIT PRESERVE ROWS: dados persistem até o final da “sessão ” (conexão).
• Os dados são visíveis apenas em cada sessão, portanto não estão sujeitos a bloqueio (lock) de registros em comandos DML (insert, update, delete).
• “TRUNCATE TABLE” também é específico por sessão.
• Geram pouco volume de “redo log”.
• Normalmente são alocados em tablespace temporária.
Carga de dados otimizada
Carga de dados em modo otimizado
• “Direct-path load ” é o modo otimizado para “carga de dados” com grande volume de registros.
• Não utiliza espaço para “undo” (rollback).
• Sintaxe básica para comando “insert” através de “hint”:INSERT /*+APPEND */ INTO tabela SELECT ...
• Nota: No plano de execução constará como “LOAD AS SELECT ”.
• Utilitário SQL-Loader:sqlldr direct=true ...
• Restrições:• Triggers devem ser desabilitados.• Relacionamentos (constraints referenciais) devem ser
desabilitados.• Para a mesma tabela, só pode haver um processo por vez.
Tabelas “externas”
External Tables
• São tabelas definidas, cujos dados residem numa fonte externa (normalmente “arquivos texto”).
• Geralmente utilizam o mecanismo (driver) de acesso do SQL-Loader — possibilitando alta performance.
• São apenas para leitura (read only).
• A principal utilização é para facilitar carga de dados, tornando o processo transparente para a aplicação (acessando como se fosse uma tabela comum).
• A fonte de dado (arquivo texto) deve estar acessível para o servidor Oracle (diretório).
Tabelas “organizadas em índice”
Index-Organized Table (IOT)
• Tabelas armazenadas fisicamente como estrutura de índice B-tree, conforme a chave primária (PK).
• Proporcionam melhor performance em acessos pela chave primária (menos leituras físicas: não é necessário acessar o índice para depois acessar a tabela).
• Permitem índices secundários.
Tabelas em “cluster”
Cluster Tables
• São tabelas cujos registros são armazenados fisicamente em “agrupamentos” (clusters), conforme a chave definida.
• Podem ser entendidas como um “join” físico de tabelas.
• A utilidade é em tabelas que quase sempre são acessadas em conjunto.
• Do ponto de vista “lógico” são tabelas separadas.
Obrigado!
Perguntas?