Oracle para Desenvolvedores - recursos e técnicas - visões gerais (Uninove 2016)

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



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


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”.


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 ”.


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?