FACULDADE DE TECNOLOGIA DE SÃO JOSÉ DOS CAMPOS
CLÁUDIA MOREIRA RODRIGUES
FELIPE VALÉRIO DE SIQUEIRA
ESTUDO COMPARATIVO ENTRE TÉCNICAS DE PARTICIONAMENTO FOCANDO
A OTIMIZAÇÃO DE BUSCA POR DADOS EM TABELAS PARTICIONADAS
SÃO JOSÉ DOS CAMPOS
2010
i
CLÁUDIA MOREIRA RODRIGUES
FELIPE VALÉRIO DE SIQUEIRA
ESTUDO COMPARATIVO ENTRE TÉCNICAS DE PARTICIONAMENTO FOCANDO
A OTIMIZAÇÃO DE BUSCA POR DADOS EM TABELAS PARTICIONADAS
TRABALHO
DE
APRESENTADO
À
GRADUAÇÃO
FACULDADE
DE
TECNOLOGIA DE SÃO JOSÉ DOS CAMPOS,
COMO
PARTE
DOS
REQUISITOS
NECESSÁRIOS PARA A OBTENÇÃO DO
TÍTULO DE TECNÓLOGO EM BANCO DE
DADOS.
Orientador: Carlos Augusto Lombardi Garcia
SÃO JOSÉ DOS CAMPOS
2010
ii
CLÁUDIA MOREIRA RODRIGUES
FELIPE VALÉRIO DE SIQUEIRA
ESTUDO COMPARATIVO ENTRE TÉCNICAS DE PARTICIONAMENTO FOCANDO
A OTIMIZAÇÃO DE BUSCA POR DADOS EM TABELAS PARTICIONADAS
TRABALHO
DE
APRESENTADO
À
GRADUAÇÃO
FACULDADE
DE
TECNOLOGIA DE SÃO JOSÉ DOS CAMPOS,
COMO
PARTE
DOS
REQUISITOS
NECESSÁRIOS PARA A OBTENÇÃO DO
TÍTULO DE TECNÓLOGO EM BANCO DE
DADOS.
____________________________________________________________
ROGÉRIO MARINKE, ESP
____________________________________________________________
JULIANA FORIN PASQUINI MARTINEZ, ME
____________________________________________________________
CARLOS AUGUSTO LOMBARDI GARCIA, ME
____/____/____
DATA DE APROVAÇÃO
iii
AGRADECIMENTOS
Agradecemos ao professor e orientador Carlos Garcia por nos ajudar no desenvolvimento do
nosso trabalho de Graduação, além de ficarmos gratos pelo carinho e motivação do professor
Giuliano Bertoti durante sua disciplina de Trabalho de Conclusão de Curso.
iv
Dedicamos este trabalho às nossas famílias e
amigos que nos ajudaram nessa jornada,
tornando esse trabalho possível.
v
“A mente que se abre a uma nova idéia jamais
voltará ao seu tamanho original.”
Albert Einstein
vi
RESUMO
Este trabalho tem como objetivo realizar um estudo comparativo entre técnicas de
particionamento de tabelas utilizando o sistema gerenciador de banco de dados relacional
(SGBDR) Oracle, usando a versão mais recente, o Oracle 11g. No trabalho são abordados
conceitos sobre banco de dados e seus sistemas gerenciadores, particionamento de tabelas e os
métodos existentes no Oracle 11g, a Tabela de Planos, além de ser estudada a arquitetura do
SGBDR utilizado no desenvolvimento do projeto.
No desenvolvimento do projeto, são criados cenários particionados com métodos de
particionamentos distintos, e sobre os mesmos são realizados estudos de casos, sendo eles ao
final analisados. Ao final dessas análises, é apresentada uma comparação entre os dois métodos
de particionamento escolhidos no decorrer do trabalho, podendo dessa forma conhecer o
comportamento dos métodos de particionamentos em cenários, com e sem, um nível adicional de
otimização.
Palavras-Chave: Particionamento de tabelas, banco de dados, sistema gerenciador de banco
de dados relacional, otimização de consultas, tabela de planos.
vii
ABSTRACT
At the study, is proposed a comparactive study of techniques for partitioning tables using the
management system relational database (RDBMS) Oracle, using the last avaiable version, Oracle
11g. At work, are covered concepts about databases, Oracle 11g architecture, Plan table, table
partitioning and partitioning methods existing in Oracle 11g.
In project development, are created partitioned tables with different partitioning methods and are
made tests on these tables. At the end of tests analysis, is shown a comparison between the two
partitioning methods choosed during the study, to let it know which had the best behavior on the
tests.
Keywords: Table partitioning, database, Relational Database Management System, query
optimization, plan table.
viii
SUMÁRIO
1
2
INTRODUÇÃO .................................................................................................................... 13
1.1
Motivação ...................................................................................................................... 13
1.2
Objetivos ........................................................................................................................ 14
1.2.1
Objetivo geral ........................................................................................................ 14
1.2.2
Objetivo específico ................................................................................................ 14
1.3
Metodologia ................................................................................................................... 14
1.4
Organização do trabalho ................................................................................................ 15
BANCO DE DADOS E SEUS SISTEMAS GERENCIADORES ................................... 16
2.1
2.1.1
Visão Geral da Estrutura do Sistema ..................................................................... 18
2.1.2
História de alguns SGBDR .................................................................................... 23
2.2
3
Banco de dados .............................................................................................................. 16
Considerações Finais ..................................................................................................... 24
ARQUITETURA DO ORACLE 11G ................................................................................ 25
3.1
Estruturas de armazenamento lógico ............................................................................. 27
3.1.1
Blocos .................................................................................................................... 27
3.1.2
Extensões ............................................................................................................... 28
3.1.3
Segmentos .............................................................................................................. 29
3.1.4
Tablespaces ............................................................................................................ 29
3.2
Estruturas lógicas do banco de dados ............................................................................ 32
3.2.1
Tabelas ................................................................................................................... 33
3.2.2
Índices .................................................................................................................... 34
3.2.3
Seqüências ............................................................................................................. 35
3.2.4
Esquemas (Schemas).............................................................................................. 36
3.3
Estruturas de armazenamento físico .............................................................................. 37
3.3.1
3.4
Arquivos de dados ................................................................................................. 39
Estruturas de memória ................................................................................................... 40
3.4.1
System Global Área (SGA) .................................................................................... 41
3.5
Processos em segundo plano.......................................................................................... 43
3.6
Considerações Finais ..................................................................................................... 44
ix
4
5
6
PARTICIONAMENTO DE TABELAS ............................................................................ 45
4.1
Particionamento por faixa de valores ............................................................................. 47
4.2
Particionamento por intervalo de valores ...................................................................... 48
4.3
Particionamento por função Hash .................................................................................. 49
4.4
Particionamento por lista de valores .............................................................................. 51
4.5
Particionamento por referência ...................................................................................... 53
4.6
Particionamento por sistema .......................................................................................... 54
4.7
Particionamento por combinação de métodos ............................................................... 55
4.7.1
Particionamento por combinação de faixa de valores e função Hash .................... 56
4.7.2
Particionamento por combinação de faixa de valores e lista de valores ................ 57
4.7.3
Tabela de Planos .................................................................................................... 59
4.7.4
Considerações Finais ............................................................................................. 62
DESENVOLVIMENTO DOS ESTUDOS DE CASOS .................................................... 63
5.1.1
Especificação do Computador ............................................................................... 63
5.1.2
Tabela Customer .................................................................................................... 64
5.1.3
Consulta ................................................................................................................. 66
5.1.4
Cenário 1 ................................................................................................................ 68
5.1.5
Cenário 2 ................................................................................................................ 70
5.1.6
Estudo de Caso F1 – Sem índice adicional e particionado por faixa de valores ... 71
5.1.7
Estudo de Caso F2 – Com índice adicional e particionado por faixa de valores ... 73
5.1.8
Estudo de Caso H1 – Sem índice adicional e particionado por função Hash ........ 75
5.1.9
Estudo de Caso H2 – Com índice adicional e particionado por função Hash ....... 76
5.1.10
Conclusão dos Estudos de Casos ........................................................................... 78
5.1.11
Considerações Finais ............................................................................................. 84
CONSIDERAÇÕES FINAIS .............................................................................................. 85
6.1.1
Experiências ........................................................................................................... 86
6.1.2
Trabalhos futuros ................................................................................................... 86
APÊNDICE...................................................................................................................................87
REFERÊNCIAS BIBLIOGRÁFICAS.......................................................................................91
x
ÍNDICE DE FIGURAS
Figura 2.1 Visão geral da estrutura de um SGBD. Fonte: SILBERSCHATZ, 1999. ................... 22
Figura 3.1 Arquitetura de um Banco de Dados Oracle na plataforma Windows. Fonte: ODPG,
2009. .............................................................................................................................................. 26
Figura 3.2 Blocos de dados e Extensão. ....................................................................................... 28
Figura 3.3 Estrutura de um tablespace. Fonte: RAMALHO, 2004. ............................................. 31
Figura 3.4 Resumo da estrutura de armazenamento lógico do SGBDR Oracle. .......................... 32
Figura 3.5 Criação de uma tabela no Oracle. ................................................................................ 33
Figura 3.6 Criação de um índice no Oracle. ................................................................................. 35
Figura 3.7 Inserindo valores na tabela cliente utilizando uma seqüência..................................... 36
Figura 3.8 Criação de uma seqüência no Oracle. ......................................................................... 36
Figura 3.9 Estruturas de armazenamento físico. Fonte: LONEY, 2005. ...................................... 38
Figura 3.10 Estrutura interna de um data file no Oracle. Fonte: GREENWALD, 2004. ............. 39
Figura 3.11 Uma instância Oracle. Fonte: GREENWALD, 2004. ............................................... 40
Figura 3.12 Representa um típico SGA. Fonte: KYTE, 2005. ..................................................... 42
Figura 4.1 Criação da tabela CONTROLE_VENDAS particionada por faixa de valores. .......... 47
Figura 4.2 Utilizando a literal MAXVALUE. .............................................................................. 48
Figura 4.3 Criação da tabela Cliente particionada por intervalo de valores. ................................ 49
Figura 4.4 Criação da tabela Transação particionada por função Hash........................................ 50
Figura 4.5 Criação da tabela VENDAS_SP particionada por lista de valores. ............................ 51
Figura 4.6 Utilizando a partição DEFAULT. ............................................................................... 52
Figura 4.7 Exemplo de particionamento por lista de valores, por faixa de valores e por função
Hash. Fonte: OVLDB2. ................................................................................................................. 52
Figura 4.8 Criação da tabela PEDIDO particionada por referência. ............................................ 53
Figura 4.9 Criação da tabela VENDAS_SP particionada por sistema. ........................................ 54
Figura 4.10 Exemplo de inserção de dados na tabela VENDAS_SP particionada por sistema. .. 55
Figura 4.11 Criação da tabela ALUNO particionada pelo método de particionamento por
combinação de faixa de valores e função Hash. ............................................................................ 56
Figura 4.12 Criação da tabela VENDAS particionada pelo método de particionamento por
combinação de faixa de valores e lista de valores. ........................................................................ 58
xi
Figura 4.13 Exemplo de uso da consulta EXPLAIN PLAN......................................................... 60
Figura 4.14 Comando para selecionar dados da tabela de planos de execução. ........................... 60
Figura 4.15 Representação de partes da tabela PLAN_TABLE. .................................................. 61
Figura 5.1 Consulta utilizada nos Estudos de Casos. ................................................................... 66
Figura 5.2 Resultado da consulta utilizada nos Estudos de Casos................................................ 67
Figura 5.3 Quantidade de linhas inseridas em cada partição da tabela CUSTOMER particionada
por faixa de valores. ....................................................................................................................... 68
Figura 5.4 Trecho do código fonte da tabela CUSTOMER particionada por faixa de valores. ... 69
Figura 5.5 Quantidade de linhas inseridas em cada partição da tabela CUSTOMER particionada
por função Hash ............................................................................................................................. 70
Figura 5.6 Trecho do código da tabela CUSTOMER particionada por função Hash. ................. 70
Figura 5.7 Resultado do plano de execução da consulta em estudo realizada no Estudo de Caso
F1. .................................................................................................................................................. 71
Figura 5.8 Resultado do plano de execução da consulta em estudo realizada no Estudo de Caso
F2. .................................................................................................................................................. 74
Figura 5.9 Resultado do plano de execução da consulta em estudo realizada no Estudo de Caso
H1................................................................................................................................................... 75
Figura 5.10 Resultado do plano de execução da consulta em estudo realizada no Estudo de Caso
H2................................................................................................................................................... 77
Figura 5.11 Diferença entre os resultados do plano de execução da consulta realizada no Estudo
de Caso F1 e H1. ............................................................................................................................ 79
Figura 5.12 Diferença entre os resultados do plano de execução da consulta realizada no Estudo
de Caso F1 e H1. ............................................................................................................................ 80
Figura 5.13 Comparação entre os resultados obtidos no Estudo de Caso F1 e H1. ..................... 81
Figura 5.14 Comparação entre os resultados do plano de execução da consulta realizada no
Estudo de Caso F2 e H2. ................................................................................................................ 82
Figura 5.15 Comparação entre os resultados obtidos no Estudo de Caso F2 e H2. ..................... 83
xii
ÍNDICE DE TABELAS
Tabela 2.1 A receita mundial de vendedores de software de SGBDR em 2004, baseado em
vendas de novas licenças (Milhões de dólares). Fonte: Gartner Dataquest (maio, 2005). ............ 17
Tabela 3.1 Principais processos em segundo plano da Oracle...................................................... 43
Tabela 4.1 Colunas da tabela PLAN_TABLE .............................................................................. 62
Tabela 5.1 Especificações do servidor de teste ............................................................................. 64
Tabela 5.2 Descrição da Tabela CUSTOMER ............................................................................. 65
13
1
1.1
INTRODUÇÃO
Motivação
Na atualidade foi percebido um crescente aumento das massas de dados gerados por grandes
organizações. Dessa forma, surge também a necessidade de uma melhor administração desses
dados.
Neste caso, a utilização de um Sistema Gerenciador de Banco de Dados Relacionais (SGBDR),
torna-se vital para o gerenciamento desses dados, pois ele oferece um controle bem maior dos
dados, buscas tornam-se mais eficientes, a segurança dos dados pode ser reforçada e dados
podem ser compartilhados.
Porém, só a utilização do SGBDR não resolve todo o problema, ainda é vista a necessidade de
alta performance em buscas nessas massas de dados, tornando assim a questão do tempo de
resposta uma parte importante e que deve ser estudada, pois não há mais como administrar uma
empresa sem um banco de dados devidamente organizado e com tempos de resposta pequenos a
uma série de consultas/atualizações.
Para que seja possível acompanhar essas constantes mudanças nas massas de dados das
organizações, é preciso desenvolver consultas mais inteligentes, utilizar metodologias para poder
simplificá-las, agilizando assim o seu processamento, pois a quantidade de pessoas atualizando
ou buscando dados nos bancos de dados vem crescendo. Deixando assim claro, que a
dependência das organizações em relação ao sistema de banco de dados é algo real e que
necessita de atenção.
Esses bancos de dados que ampliam seus tamanhos a todo o momento com a inserção de novos
dados são divididos em tabelas. Segundo Alapati (2009), essas tabelas tendem a tomar
proporções muito grandes, armazenando gigabytes, ou até mesmo terabytes de dados, tornando
assim essas tabelas lentas para a busca de dados, sendo necessário o particionamento das
mesmas. Mas o uso indiscriminado do particionamento de tabelas, muitas vezes, não resolve o
problema. Como por exemplo, o uso do particionamento por faixa de valores em uma tabela
contendo muitas transações diárias. Neste caso, a escolha deste método de particionamento seria
14
equivocada, pois o método de particionamento por função Hash traria maior benefício em relação
a buscas por um valor específico na tabela.
Isso demonstra como o estudo das técnicas de particionamento de tabelas é necessário para que
seja possível encontrar a melhor solução para o problema proposto.
Nesse trabalho iremos realizar um estudo comparativo entre técnicas de particionamento de
tabelas, visando obter a comparação dos métodos de particionamentos utilizados durante o
estudo.
1.2
Objetivos
1.2.1
Objetivo geral
O objetivo deste trabalho é apresentar um estudo comparativo entre técnicas de particionamento
focando a otimização de busca por dados em tabelas particionadas.
1.2.2
Objetivo específico
a) Criar uma base de dados de grande volume para realização de testes.
b) Especificar um ambiente físico para testes.
c) Estruturar dois cenários utilizando métodos de particionamento distintos.
d) Executar dois estudos de casos em cada cenário criado.
e) Exibir uma análise referente aos resultados obtidos nos estudos de casos.
1.3
Metodologia
Serão realizados estudos de casos a partir de uma estrutura de busca em tabelas particionadas
utilizando um sistema gerenciador de banco de dados relacional (SGBDR), analisando o estudo
de forma que seja possível encontrar uma solução viável para os casos a serem estudados.
Neste trabalho será utilizado como ferramenta principal o SGBDR Oracle 11g.
15
1.4
Organização do trabalho
O presente trabalho está organizado em capítulos conforme segue:
Capítulo 1 – Capítulo introdutório que tem como objetivo situar o leitor em relação ao escopo do
trabalho, seus objetivos e os problemas / necessidades.
Capítulo 2 – Apresenta as bases teóricas sobre bancos de dados relacionais, sua estrutura e os
sistemas de busca. Aborda também alguns estudos co-relatos alinhados com o tema deste
trabalho.
Capítulo 3 – Aborda a arquitetura do SGBDR Oracle 11g, sendo apresentadas suas estruturas de
armazenamento lógico e físico, e além das estruturas lógicas do banco de dados, para se ter um
maior conhecimento no SGBDR utilizado nos estudos.
Capítulo 4 – Descreve o funcionamento do particionamento de tabelas e dos métodos de
particionamento existentes no Oracle 11g, além de introduzir o conceito sobre a Tabela de
Planos, que será utilizada no desenvolvimento do projeto.
Capítulo 5 – Apresenta os experimentos realizados durante este trabalho e seus resultados. Além
de apresentar a análise dos dados obtidos com os experimentos, a fim de estudar o
comportamento dos métodos de particionamentos utilizados nos experimentos.
Capítulo 6 – Conclusões e sugestões para trabalhos futuros.
16
2
2.1
BANCO DE DADOS E SEUS SISTEMAS GERENCIADORES
Banco de dados
De acordo com Silberschatz (2006), um banco de dados é um conjunto de dados de um indivíduo
ou de alguma organização. Esses dados que compõem um banco de dados podem ser de diversos
tipos, como dados de controle de produção, dados pessoais dos funcionários, entre outros tipos.
Mas esses dados não precisam necessariamente ser de um tipo específico, qualquer tipo de dados
que ocupem um grande volume de informações e não seja mais viável o controle dos mesmos em
papel, é um grande candidato a ser armazenado em um banco de dados.
Esse conjunto de dados é gerenciado por um sistema gerenciador de banco de dados (SGBD).
Segundo Date (2004), o SGBD é um sistema computadorizado que tem como finalidade
armazenar esses dados e permitir que seus usuários acessem e modifiquem essas informações
quando o desejar. Esse sistema ajuda os usuários a ter uma visão abstrata dos dados, fornecendo
apenas o necessário a eles.
Neste estudo será utilizado o Sistema Gerenciador de Banco de Dados Relacional (SGBDR),
fornecido pela Oracle Corporation, em sua versão mais recente, o Oracle 11g, pois segundo uma
pesquisa de Gartner, Inc, de 2004, os Bancos de Dados Relacionais cresceram 10 porcento em
2004, e em primeiro lugar com as maiores fatias de mercado, tanto em 2003 quanto em 2004,
aparecem a Oracle e a IBM. A seguir são exibidos os resultados dessa pesquisa na Tabela 1.1.
17
IBM Total
2,664.9
2004
Market
Share (%)
34.1
IBM
2,554.0
32.7
2,384.2
33.6
7.1
110.9
1.4
134.6
1.9
-17.6
Oracle
2,636.0
33.7
2,299.3
32.4
14.6
Microsoft
1,561.1
20.0
1,323.0
18.7
18.0
NCR Teradata
230.0
2.9
196.3
2.8
17.2
Sybase
178.0
2.3
177.2
2.5
0.5
Others
517.1
6.6
545.4
7.7
-5.2
Total
7,787.1
100.0
7,060.0
100.0
10.3
Companies
Revenue
2004
Informix Products
2003-2004
Growth (%)
2,518.8
2003
Market
Share (%)
35.5
2003
Revenue
5.8
Tabela 2.1 A receita mundial de vendedores de software de SGBDR em 2004,
baseado em vendas de novas licenças (Milhões de dólares).
Fonte: Gartner Dataquest (maio, 2005).
Conforme Ramalho (2004), para que o SGBDR possa interagir com os dados armazenados em
um banco de dados, é necessário utilizar a linguagem SQL (Structured Query Language). Essa
linguagem pode também ser dividida em linguagem de definição de dados (DDL), em linguagem
de manipulação de dados (DML) e linguagem de controle de dados (DCL), porém ficaremos com
apenas a DDL e DML.
Segundo Silberschatz (2006), a DDL é utilizada pelo SGBDR para formular um esquema de
banco de dados por um conjunto de determinações, além de enumerar adicionais dos dados.
Além da definição de um esquema de banco de dados, a DDL provê a possibilidade de
determinar algumas restrições de consistência, que ajudam a controlar as modificações feitas no
banco de dados, como por exemplo, as restrições de domínios. Esse tipo de restrição define um
tipo de dado para certo campo da tabela. Exemplo disso seria definir um campo com o tipo
“DATE”, ou seja, esse campo é restrito a armazenar dados que sejam datas, o mesmo ocorreria se
o campo fosse definido com o tipo “INTEGER” ou “NUMBER”, nos quais são restritos a
armazenar dados numéricos.
Diferentemente da DDL, segundo Silberschatz (2006), a DML fornece uma forma de os usuários
acessarem ou manipularem dados.
18
Alguns tipos de acesso são possíveis, como recuperação de informações, inserção, exclusão e
modificação de dados no banco de dados. Esses tipos são definidos pelos comandos “SELECT”,
“INSERT”, “DELETE” e “UPDATE”.
Segundo Silberschatz (2006), o SGBDR é responsável por restringir o acesso ou atualizações dos
dados armazenados nele. É responsável também por proporcionar segurança em relação ao
armazenamento de dados caso haja uma falha do sistema, ou uma falha externa, como uma falha
de energia, por exemplo. É por meio dele que é possível, inserir, alterar, excluir ou buscar dados
nos banco de dados.
Para uma maior compreensão do assunto, conhecer a divisão do SQL é importante, pois a seguir
será mostrada uma visão geral da estrutura do sistema de um SGBD, desde a sua parte externa à
interna, na visão do autor Silberschatz (1999). Sendo que, cada parte que compõe o sistema é
responsável por executar algum tipo de tarefa, como processar os comandos SQL, gerenciar o
armazenamento em disco, entre outras funções que serão vistas com mais detalhes a seguir.
2.1.1
Visão Geral da Estrutura do Sistema
Num contexto geral, conforme Silberschatz (1999), a estrutura de um SGBD é dividida em
processador de consultas e gerenciador de memória, além de ser responsável pelo armazenamento
em disco, isso na parte interna. Na parte externa conta ainda com os diversos tipos de usuários
que serão apresentados logo em seguida.
Conforme Silberschatz (1999) existem quatro tipos de usuários de sistemas de banco de dados,
são eles:
•
Programadores de aplicações: são os usuários profissionais que utilizam o sistema por
meio de chamadas DML, as quais são envolvidas por alguma linguagem qualquer, como
por exemplo, JAVA, C, C#, entre outras.
•
Usuários sofisticados: são os usuários que não criam programas e sim fazem suas
solicitações ao banco de dados por meio de linguagem de consultas.
19
•
Usuários especialistas: são os usuários que criam aplicações especializadas de banco de
dados. Não são aplicações tradicionais em processamento de dados, um exemplo disso
seria um sistema que armazena dados de tipos complexos como dados gráficos, áudio,
entre outros.
•
Usuários navegantes: são os usuários que utilizam o sistema através de um programa
aplicativo, como por exemplo, uma secretária utilizando um sistema de contas a pagar.
Quanto ao módulo de processamento de consultas, o mesmo está organizado da seguinte maneira:
•
Compilador DML: é ele que traduz o comando DML da linguagem de consulta para uma
linguagem de baixo nível, para que o componente de execução de consultas entenda o
comando. Além disso, ele tenta executar o comando do usuário de uma forma mais
eficiente. (ou seja, da melhor forma possível).
•
Pré-compilador para comandos DML, que é inserido no programa de aplicação de uma
linguagem qualquer como JAVA, C, entre outros. Ele é responsável em converter os
comandos DML em chamadas de procedimentos normais da linguagem hospedeira, além
de ter que trabalhar em conjunto com o compilador DML para que seja possível gerar o
código necessário.
•
Interpretador DDL: que interpreta os comandos DDL e armazena-os em um conjunto de
tabelas que possuem metadados.
•
Componentes para o tratamento de consultas: são eles que executam as instruções de
baixo nível criadas pelo compilador DML.
De maneira resumida o processador de consultas recebe as solicitações feitas ao banco de dados
por linguagem de consulta realizada por usuários sofisticados. Tendo as recebido, o processador
quebra esses comandos DML, de maneira que essas instruções sejam compreendidas pelo
gerenciador de memória.
20
Porém, como a linguagem DML é diferente da linguagem hospedeira do programa aplicativo é
preciso que o pré-compilador para comandos DML esses comandos em chamadas de
procedimentos normais da linguagem hospedeira, para que então, a instrução após esse processo,
seja enviada ao compilador da linguagem hospedeira, que com isso produzirá o código
apropriado.
Já o módulo de administração do armazenamento de dados tem como objetivo ser o mediador
entre os dados de baixo nível, guardados no banco de dados, os programas de aplicações e as
consultas passadas ao sistema. Sua estrutura conta com:
•
Gerenciamento de autorizações e integridade: que testa se as regras de integridade estão
sendo realmente realizadas e também verifica as permissões de acesso aos dados para o
usuário.
•
Gerenciamento de transações: administra o banco de dados de forma que o mesmo
continue correto (consistente) mesmo que ocorram falhas no sistema e também administra
as transações que ocorrem ao mesmo tempo não permitindo que as mesmas não interfiram
umas as outras quando executadas.
•
Administração de arquivos: organiza o espaço para o armazenamento em disco e as
estruturas de dados que representam estas informações armazenadas.
•
Administração de buffer: seleciona quais os dados que estão armazenados no disco irão
para a memória principal e logo em seguida para a memória cache.
Olhando para a parte física do SGBD, algumas estruturas de dados específicas compõem sua
estrutura, como:
•
Arquivo de dados (data files): é nele que o banco de dados é armazenado, como por
exemplo, todo o tipo de tabelas, índices, metadados do banco de dados, códigos
procedurais, entre outros.
21
•
Dicionário de dados (data dictionary): armazena os metadados da estrutura do banco de
dados, ou seja, descreve toda a estrutura do banco de dados, toda a informação da
estrutura em si.
•
Índices: segundo Alapati, índices criam ponteiros para os dados armazenados em colunas
específicas, possibilitando o acesso rápido a uma tupla, sem que haja a necessidade de
percorrer todas as tuplas da tabela, diminuindo dessa forma a quantidade de entrada e
saída de dados (I/O).
•
Estatísticas de dados: guarda informações estatísticas sobre o banco de dados. Essas
informações são usadas pelo processador de consultas para executar uma consulta feita
por um usuário de uma maneira eficaz.
A Figura 1.1 mostra resumidamente a estrutura de um sistema gerenciador de banco de dados,
descrita anteriormente.
22
Figura 2.1 Visão geral da estrutura de um SGBD.
Fonte: SILBERSCHATZ, 1999.
23
Após ter tido uma visão geral do funcionamento de um SGBD, nada mais interessante como
conhecer algum tipo de SGBDR. Dessa forma, será abordada um pouco da história de alguns
SGBDR que existem atualmente, levando em consideração que será utilizado no decorrer do
trabalho o Oracle 11g.
2.1.2
História de alguns SGBDR
Existem vários sistemas de gerenciadores de banco de dados relacionais, porém serão discutidos,
apenas alguns deles como o PostgreSQL, o Microsoft SQL Server, o IBM DB2 e o Oracle.
O PostgreSQL, é um sistema de gerenciamento de banco de dados objeto-relacional baseado no
postgres (desenvolvido na Universidade da Califórnia, em Berkeley Computer Science
Department). Foi o pioneiro em muitos dos conceitos que passaram a serem incorporados nos
demais SGBDR’s mais tarde. É um sistema de código aberto que pode ser usado, modificado e
distribuído gratuitamente por qualquer pessoa, seja para fim pessoal, comercial ou acadêmico.
O SQL Server é um SGBDR da Microsoft. Foi criado originalmente pela Sybase, na década de
80, para sistemas UNIX. Desde 2004 a Microsoft vem lançando novas versões do produto, que
pode ser usado em plataformas desktops, notebooks e até servidores. O produto atualmente
encontra-se na versão 2008.
O grupo de produtos IBM DB2 Universal Database nada mais é que servidores de bancos de
dados e um amplo pacote de produtos que é incorporado por produtos voltados para a inteligência
de negócios, integração de informações e gerenciamento de conteúdo. Dentre sua ampla área de
abrangência estão incluídos sistemas de auto nível, como mainframes, processadores
maciçamente paralelos (MPP) e servidores com multiprocessadores simétricos (SMP), além de
sistemas de média escala, como Workstations e dispositivos de mão.
E por final o Oracle, é um SGBDR criado pela Oracle Corporation, por Larry Ellison, Bob Miner
e Ed Oates. A empresa foi a primeira a chegar no mercado oferecendo um SGBD relacional. Esse
SGBDR possui uma linguagem estendida da linguagem SQL padrão, a PL/SQL (Procedural
Language/SQL), que é uma linguagem de programação procedural, que permite escrever
programas que possuem instruções SQL.
24
Após conhecer um pouco da história dos SGBDR’s, é importante conhecer mais detalhes do
funcionamento da arquitetura do SGBDR utilizado neste trabalho. A seguir, no próximo capítulo,
serão abordados mais detalhes sobre a arquitetura do Oracle 11g.
2.2
Considerações Finais
Esse capítulo teve como objetivo introduzir ao leitor conceitos sobre banco de dados, sobre o
sistema gerenciador de banco de dados e mostrar uma visão geral do funcionamento do mesmo,
além de falar sobre alguns SGBDR’s que existem.
25
3
ARQUITETURA DO ORACLE 11G
O conhecimento sobre a arquitetura interna do Oracle 11g, é de extrema importância para que se
possa ter um melhor entendimento de como funciona o ambiente em que o estudo será
desenvolvido.
Segundo Alapati (2009), o Oracle 11g utiliza estruturas de armazenamento lógico e físico que em
conjunto formam a estrutura do banco de dados Oracle.
Conforme o autor, o conjunto de estruturas de armazenamento lógico é formado por bloco de
dados (data blocks), extensões, segmentos e tablespaces; diferentemente do conjunto de
estruturas físicas que consiste em arquivos de dados (data files), arquivos de controle (control
files) e arquivos de redo log (redo log files).
Em resumo, quando um banco de dados é iniciado, uma área global de sistema (SGA) é alocada e
os processos do banco de dados são inicializados. O conjunto de estruturas de memória na SGA e
o conjunto de processos em segundo plano (background) de banco de dados formam o que
chamamos de uma instância do Oracle (ALAPATI, 2009).
A Figura 3.1 mostra, resumidamente, a arquitetura de um banco de dados Oracle na plataforma
Windows, exibindo como é feita a interação entre as estruturas de armazenamento lógico e físico
no sistema.
26
Figura 3.1 Arquitetura de um Banco de Dados Oracle na plataforma Windows.
Fonte: ODPG, 2009.
Tendo visto de forma geral a arquitetura de um banco de dados Oracle na plataforma Windows,
exibido na Figura 3.1. Serão abordados a seguir, primeiramente, o funcionamento da estrutura de
armazenamento lógico e suas divisões, e após isso, serão conhecidas algumas estruturas lógicas
do próprio SGBDR em estudo. Para que ao final sejam conhecidas as estruturas de
27
armazenamento físico, de memória e os processos de segundo plano, para um melhor
entendimento do assunto.
3.1
Estruturas de armazenamento lógico
Conforme Alapati (2009), em um banco de dados Oracle são utilizadas algumas estruturas de
armazenamento lógico para que possa ser possível administrar o armazenamento físico dos
dados. Essa composição lógica de um banco de dados Oracle é dividida em:
•
Blocos de dados
•
Extensões
•
Segmentos
•
Tablespaces
A seguir, será iniciada uma abordagem, primeiramente, sobre os blocos de dados, e em seguida,
serão vistas as outras divisões lógicas, citadas anteriormente.
3.1.1
Blocos
No banco de dados Oracle, segundo Alapati (2009), os blocos de dados estão na base de toda
estrutura de armazenamento de dados. Consistem basicamente em determinados espaços em
disco no sistema de armazenamento dos sistemas operacionais. Trata-se do menor entre os
componentes lógicos em um banco de dados. Essas unidades de blocos podem conter de 2 KB a
32 KB ou mais.
O autor ainda diz, que o tamanho dos blocos de dados devem ser estipulados pelo DBA
(Database Administrator), definindo um parâmetro de seu arquivo de inicialização chamado
DB_BLOCK_SIZE. Por exemplo, se o tamanho dos blocos de dados for de 8 KB, toda vez que
for necessário ler dados do banco, o SGBDR retornará em blocos de 8 KB, porém, se defini-los
como 32 KB, mesmo que sua informação ocupe apenas 10 KB, terá que ler todos os 64 KB para
que seja retornada.
28
Por isso, a definição do tamanho dos blocos de dados deve ser bem planejada levando em
consideração a quantidade e os tipos de dados que serão armazenados em um banco de dados
Oracle (ALAPATI, 2009).
A Figura 3.2 demonstra onde os blocos de dados ficam alojados no sistema.
Figura 3.2 Blocos de dados e Extensão.
A partir da Figura 3.2 é possível visualizar que um conjunto de bloco de dados forma uma
estrutura chamada extensão. A seguir, serão discutidos mais detalhes sobre extensões.
3.1.2
Extensões
Extensões são, basicamente, um ou mais blocos alocados a um objeto específico do banco de
dados, como uma tabela ou um índice (ALAPATI, 2009).
Conforme o autor, quando uma quantidade contínua de dados é combinada, denominamos esse
conjunto de dados de extensões. Com a criação de qualquer objeto de bando de dados, aloca-se
uma quantidade inicial de espaço, chamada de extensão inicial, e logo em seguida, o sistema
aloca o tamanho da extensão seguinte, e assim sucessivamente.
Uma vez alocados, os objetos permaneceram até que sejam retirados do banco.
29
3.1.3
Segmentos
Seguindo a mesma linha, os segmentos são um conjunto de extensões. Para cada objeto do banco
de dados, o Oracle aloca uma ou mais extensões, formando então um segmento (ALAPATI,
2009).
Segundo o autor, se no banco de dados foi criada uma tabela chamada Funcionário, então o
espaço que foi alocado para esta tabela é referido como segmento de Funcionário. Já índices
criados recebem o nome do segmento e o nome do índice em si.
Outro tipo de segmento existente são os segmentos temporários, que são usados pelo Oracle para
que possa executar algumas operações que necessitem guardar dados temporariamente.
3.1.4
Tablespaces
Os bancos de dados Oracle, segundo Alapati (2009), são divididos logicamente em tablespaces.
Os tablespaces são entidades lógicas onde estão contidos os arquivos físicos de dados e são as
estruturas lógicas primárias de um banco de dados.
O autor afirma que o tamanho de uma tablespace depende da quantidade de dados que é inserido
no banco de dados não havendo regras de limite máximo ou mínimo de dados, dependendo
unicamente da quantidade de hardware disponível.
Pode existir um número ilimitado de tablespaces em um banco de dados, porém as cinco
tablespaces a seguir, são geralmente as áreas que um banco deve ter, mesmo sendo possível criar
e utilizar um banco com apenas as duas primeiras:
•
System tablespace
•
Sysaux tablespace
•
Undo tablespace
•
Temporary tablespace
•
Default permanent tablespace
Alguns SGBDs não utilizam o conceito de tablespaces, porém esse conceito pode facilitar muito
em algumas operações como: atribuição de quotas para vários usuários no banco, realização de
backups parciais e recuperações, um armazém de dados pode ser facilmente dividido em várias
30
partes, indisponibilizar uma área do banco sem que ele como um todo tenha que ser bloqueado,
entre outras facilidades.
Conforme Alapati (2009), além do System e Sysaux existem alguns tipos de tablespaces que
provavelmente possam existir:
•
Tablespaces de arquivos grandes, com arquivos de dados que sozinhos podem conter de 8
a 128 terabytes.
•
Tablespaces de arquivos pequenos, que são as mais tradicionais tablespaces. As próprias
System e Sysaux são desse grupo de tablespaces.
•
Tablespaces temporárias, que possuem dados que apenas persistem durante o uso do
usuário.
•
Tablespaces permanentes, que incluem todas as tablespaces que não são temporárias.
•
Undo tablespaces, que são as tablespaces de restauração de dados no caso de um
rollback.
•
Tablespaces de apenas leitura, onde não é possível modificar os dados contidos. Muito
usadas em grandes arquivos de dados que não mudam.
A Figura 3.3 mostra como é composto internamente um tablespace, seguido de um comando
SQL, utilizado na criação de novos tablespaces.
31
Figura 3.3 Estrutura de um tablespace.
Fonte: RAMALHO, 2004.
De forma resumida, a Figura 3.4 demonstra a divisão existente na estrutura de armazenamento
lógico abordada nos tópicos anteriores.
32
Figura 3.4 Resumo da estrutura de armazenamento lógico do SGBDR Oracle.
Tendo conhecido as divisões da estrutura de armazenamento lógico do SGBDR Oracle, serão
discutidas a seguir as estruturas lógicas do banco de dados, como as tabelas, os índices, as
seqüências e os esquemas (schemas). Para que depois disso, sejam abordadas as estruturas de
armazenamento físico, de memória e os processos em segundo plano.
3.2
Estruturas lógicas do banco de dados
Segundo Alapati (2009), a estrutura lógica de um banco de dados Oracle, é constituída por
tabelas, índices, seqüências, visões (views) e esquemas (schemas).
Nos próximos tópicos, serão vistos mais detalhes dessas estruturas, iniciando o estudo
primeiramente com as tabelas, seguido pelas demais.
33
3.2.1
Tabelas
Tabelas são os objetos mais básicos de armazenamento em um banco de dados Oracle. São
entidades lógicas com as quais podemos ler e manipular dados, sendo elas na sua estrutura
formadas por linhas e colunas (ALAPATI, 2009).
Segundo o autor, os primeiros passos para se criar uma tabela são a definição de um nome para a
tabela e o grupo de colunas, onde cada coluna possui um nome e um tipo específico de dados.
Para alguns tipos de dados é preciso especificar a precisão ou largura do campo, o mesmo não é
necessário para outros tipos.
Abaixo, exibido na Figura 3.5, um exemplo de código para a criação de uma tabela no Oracle:
Figura 3.5 Criação de uma tabela no Oracle.
Existem quatro formas de se organizar uma tabela no Oracle, conforme o autor:
•
Organizadas em pilha: é a forma mais básica de organização de uma tabela, no qual os
dados são armazenados sem definição de ordem.
•
Organizadas por índice: é quando as tabelas são organizadas por um índice que funciona
como uma estrutura de árvore.
•
Organizadas em cluster: quando faz parte de um grupo que compartilha os mesmos blocos
de dados, pois geralmente são solicitados juntos.
34
•
Tabelas particionadas: onde é permitido dividir uma tabela em subtabelas, chamadas de
partições, obedecendo alguns critérios. É muito usado com tabelas que possuem um
número muito grande de dados. Mais detalhes sobre o seu funcionamento, e também os
métodos que são utilizados para particioná-la, serão vistos no próximo capítulo.
3.2.2
Índices
Os índices são estruturas usadas para melhorar a velocidade de busca em tabelas (ALAPATI,
2009). Do mesmo jeito que uma pessoa utiliza um índice de um livro para achar alguma página
sem ter a necessidade de passar por todas elas, o SGBD utiliza-o para buscar determinados
campos de uma tabela.
De uma forma geral, segundo Loney (2007) um índice Oracle armazena o valor da coluna (ou
colunas) que estão sendo indexadas, junto com o ROWID físico da linha que contém o valor
indexado – ROWID é um identificador exclusivo que o SGBDR utiliza internamente para
armazenar a localização física da linha (PRICE, 2009) –, exceto para tabelas organizadas por
índice (IOTs, index-organized tables), que utilizam a chave primária como um ROWID lógico.
Então, se o índice Oracle for comparado com um índice de um livro, o valor da coluna seria o
título e a coluna ROWID seria o número da página.
Referente a performance, segundo Alapati (2009), os índices reduzem a utilização do recurso de
E/S (entrada e saída) do computador. Recurso que pode deixar uma operação muito lenta por se
tratar de um dispositivo mecânico.
A utilização de um índice, porém, deve ser analisada, pois junto com uma melhora no ato de
buscar uma informação, ocorre uma diminuição de desempenho no ato de inserção ou exclusão
de dados, pois ao realizar esse tipo de operação, os índices devem ser imediatamente
recalculados, para que continuem exatos, conforme o autor. Além disso, ao se criar um índice de
uma tabela, com um determinado tamanho, o índice ocupará uma porcentagem desse tamanho,
necessitando de mais espaço em disco em relação a uma tabela sem índice.
Em termos gerais, segundo Alapati, se um banco possuir dados que não necessitam de muita
atualização, o recurso do índice é muito recomendado. Porém, se for um banco muito ativo em
termos de modificações, deve-se fazer uma análise para que o mesmo não seja prejudicado.
Dentre os vários tipos de índices, segundo o autor, podemos citar alguns mais importantes, como:
35
•
Índices exclusivos e não exclusivos: índices exclusivos são baseados em colunas únicas,
geralmente algo como o RG ou CPF, alguma informação que seja única. Já os índices não
exclusivos, não impõem a restrição de unicidade. Esses índices são criados se nenhuma
chave for utilizada em sua construção.
•
Índices primários e secundários: índices primários em uma tabela nunca devem ser nulos.
Já índices secundários são índices da mesma tabela, mas que podem não ser único.
•
Índices compostos: são índices criados a partir de duas ou mais colunas de uma tabela.
São úteis no caso de tabelas que são unificadas por mais de uma coluna.
É possível criar um índice utilizando o comando CREATE INDEX, conforme ilustrado na
Figura 3.6.
Figura 3.6 Criação de um índice no Oracle.
3.2.3
Seqüências
No Oracle, segundo Alapati (2009), uma seqüência (sequence) nada mais é do que um gerador de
números únicos criados em seqüência. É muito usada para definir um número sempre único a um
campo de uma tabela. Isso auxilia muito em uma coluna de ID (Identifier), pois sempre que for
inserido um registro a tabela, a seqüência insere um número único ao campo ID, conforme é
demonstrado na Figura 3.7, por exemplo:
36
Figura 3.7 Inserindo valores na tabela cliente utilizando uma seqüência.
Neste caso, se esse cliente for o primeiro a ser inserido na tabela, ele recebe o número “1” como
ID, já o próximo receberá o numero “2”, e assim sucessivamente. Mas para que isso aconteça
deve-se criar primeiramente a seqüência propriamente dita, como por exemplo, a
“SEQ_CLIENTE”, exibida na Figura 3.8, da seguinte forma:
Figura 3.8 Criação de uma seqüência no Oracle.
3.2.4
Esquemas (Schemas)
Schemas podem ser definidos como uma coleção lógica de estruturas de dados e, no Oracle, é
mais conhecido como sinônimo de usuários (ALAPATI, 2009).
Segundo o autor, além de conter tabelas, um schema contém objetos do banco de dados, como
procedures, functions, views, sequences, entre outros objetos. Essa separação lógica de objetos no
banco permite flexibilidade para gerir e garantir o funcionamento do banco de dados.
Basicamente, existem duas maneiras de se criar schemas em um banco de dados Oracle. A mais
comum é o login como proprietário do schema e a outra é criá-lo utilizando o comando CREATE
SCHEMA.
E aqui termina a abordagem sobre os detalhes das estruturas de armazenamento lógico e
estruturas lógicas do banco de dados. Dessa forma, serão vistas a seguir, as estruturas de
armazenamento físico e como são divididas; seguido por estruturas de memória, sendo visto mais
37
a respeito sobre a SGA e seus componentes internos, e por final o papel dos processos em
segundo plano.
3.3
Estruturas de armazenamento físico
Segundo Alapati (2005), um banco de dados Oracle é formado por algumas estruturas de
armazenamento físico no disco, como arquivos de dados (data files), arquivos de controle
(control files) e arquivos de redo log (redo log files).
Conforme o autor, alguns arquivos como os arquivos de redo log e os data files armazenam
dados do usuário, diferentemente dos arquivos de controle, que armazenam informações sobre o
próprio banco de dados.
Além do banco de dados Oracle utilizar esses arquivos, ele utiliza também outros arquivos do
sistema como os arquivos de inicialização, como o init.ora e o SPFILE, arquivos de
administração de rede (network administration files), como o tnsnames.ora e o listener.ora,
como também alert log files, trace files, e password files.
A Figura 3.9 ilustra, resumidamente, como é feita a integração entre a instância de um banco de
dados, a estrutura física da tabela e a estrutura lógica do banco de dados Oracle.
38
Figura 3.9 Estruturas de armazenamento físico.
Fonte: LONEY, 2005.
Após o panorama geral das estruturas de armazenamento físico, ilustrado na Figura 3.9. Será
visto, primeiramente, os detalhes sobre os arquivos de dados, seguido por estruturas de memória,
e por final os processos em segundo plano.
39
3.3.1
Arquivos de dados
Os arquivos de dados (data files) são as menores entidades na hierarquia de um banco de dados
Oracle. Neles são armazenados tabelas, índices, dicionário de dados, áreas temporárias e de
rollbacks (GREENWALD, 2004).
Em um banco de dados Oracle, conforme Alapati (2009), um data file pertence a apenas um
banco de dados, porém um banco pode possuir mais de um data file, que juntos constituem a
entidade lógica abordada anteriormente, o tablespace.
A maior parte do armazenamento físico em um banco é feito no data file, ou seja, ele ocupa a
maior parte do espaço total de um banco de dados.
Quando uma instância de um banco de dados necessita ler uma tabela, ela procura no data file
armazenado no disco, a não ser que essa tabela já esteja na memória cache do Oracle. Assim
também, quando uma instância de um banco precisar atualizar ou criar uma nova tabela, essas
atualizações serão escritas nos data files.
Segundo Greenwald (2004), um data file é compostos por blocos de dados, que por sua vez, são
compostos por blocos do sistema operacional no disco, conforme ilustrado na Figura 3.10.
Figura 3.10 Estrutura interna de um data file no Oracle.
Fonte: GREENWALD, 2004.
40
Por uma questão de desempenho, os dados modificados ou novos não são gravados
imediatamente nos data files. Para reduzir o acesso em disco e melhorar a performance, os dados
são armazenados em memória e gravados nos data files ao mesmo tempo, como determina o
processo de segundo plano DBWn (Data Block Writer) do Oracle.
3.4
Estruturas de memória
A Figura 3.11 ilustra o funcionamento de uma instância Oracle. Exibindo suas estruturas de
memória como o SGA, e seus processos em segundo plano (background). Tanto as estruturas de
memória, quanto os processos em segundo plano serão abordados a seguir, começando pela
estrutura de memória SGA.
Figura 3.11 Uma instância Oracle.
Fonte: GREENWALD, 2004.
41
3.4.1 System Global Área (SGA)
Segundo Alapati (2009), o System Global Area (SGA) é o componente de memória mais
importante em uma instância do Oracle. O autor ainda diz que seu objetivo é acelerar o
desempenho das consultas e também permitir que várias atividades concorrentes sejam
executadas no banco de dados.
Algo que deve ser levado em consideração é o tamanho do SGA, pois quando uma instância é
iniciada, ela aloca para si uma quantia de memória RAM do sistema operacional. Sendo que, essa
quantia é baseada no tamanho do componente SGA especificado previamente no arquivo de
inicialização.
Conforme Alapati (2009), o SGA é uma combinação de várias estruturas de memória, como:
•
Cachê do Buffer de Banco de Dados (Database Buffer Cache): é a memória de buffer que
o Oracle utiliza para armazenar dados lidos pelo processo de servidor (process server) dos
data files armazenados no disco, em resposta das requisições do usuário. Quando o
usuário modifica os dados, essas mudanças são feitas também no buffer do banco de
dados. Esse buffer possui tanto o bloco de dados lido do disco, como também o bloco
modificado que tem que ser reescrito no disco.
Quando um processo de usuário necessita de alguns dados, o Oracle primeiramente
procura por esses dados no cachê de buffer. Caso ele esteja no cachê de buffer, o processo
de servidor irá ler esses dados diretamente do SGA e logo em seguida mandará ao usuário
que o requisitou. Porém se eles não estiverem no cachê de buffer, o processo de servidor
irá ler os dados dos data files armazenados no disco e alocará esses dados no buffer do
banco de dados.
•
Pool Compartilhado (Shared Pool): essa memória é composta por dois subcaches
importantes, o cachê de biblioteca (Library Cache) e o dicionário de dados.
Todo o código SQL ou as unidades de programa PL/SQL, assim como procedures e
packages, são primeiramente analisadas e depois executadas. O Oracle armazena todas as
instruções no cachê de biblioteca e as compartilha com todos os usuários do banco de
dados. Assim quando uma instrução SQL for executada, primeiramente o Oracle
procurará no cachê de biblioteca, se não existe algum código SQL correspondente
42
analisado. Caso exista, o Oracle utilizará a versão do cachê de biblioteca, para reduzir o
tempo de processamento – esse processo é chamado de soft parse. Caso contrário, é
necessário analisar o código SQL – esse processo é conhecido como hard parse.
Diferentemente do cachê de biblioteca, o cachê de dicionário de dados possui definições
de objetos, nomes de usuários, regras (roles), privilégios, coleção de dados das tabelas
administrativas do sistema (SYSTEM e SYS), entre outras informações. Quando um
código SQL é executado, o Oracle precisa saber primeiramente qual é o nível de acesso
do usuário para poder executar a operação. Então é verificado o cachê do dicionário de
dados, caso essa informação seja encontrada, é finalizada a execução da operação. Se não,
será necessário buscar essa informação no dicionário de dados e logo em seguida passar
para o cachê do dicionário de dados.
•
Buffer de Redo Log (Redo Log Buffer): é um componente crucial do SGA, pois possui
informações necessárias para a reconstrução de mudanças feitas no banco de dados por
operações DML. Quando um processo de servidor muda algum dado no buffer de cachê
de dados, por meio dos comandos INSERT, DELETE, ou UPDATE, é gerado dado de
redo, o qual é gravado no buffer de arquivos de redo log. Depois essa informação é
gravada nos arquivos de redo log pelo processo Log Writter (LGWR).
A Figura 3.12 representa um resumo de um típico SGA.
Figura 3.12 Representa um típico SGA.
Fonte: KYTE, 2005.
43
3.5
Processos em segundo plano
A principal engrenagem das instâncias do Oracle são os processos em segundo plano. Pois são
eles que permitem que diversos usuários usem de forma eficiente e concorrentemente as
informações armazenadas nos arquivos do banco de dados. Esses processos são executados
automaticamente a partir do momento que uma instância é iniciada, e são desligados quando a
instância é parada (ALAPATI, 2009).
Na Tabela 3.1, é exibida uma lista com os principais processos em segundo plano da Oracle:
Processo em Segundo Plano
Database Writer (DBWn)
Função
Escreve modificações nas informações do
cachê de buffer para o disco (data files).
Log Writer (LGWR)
Escreve as informações do buffer de arquivos
de redo log para os arquivos de redo log.
Checkpoint (CKPT)
Atualiza os cabeçalhos de todos os data files
para registrar os detalhes do checkpoint.
Process Monitor (PMON)
Limpa um processo de usuário quando ocorre
falha do mesmo, garantindo que os recursos do
banco de dados sejam liberados.
System Monitor (SMON)
Executa tarefas do sistema de monitoramento
em relação a instâncias do Oracle.
Archiver (ARCn)
Arquiva arquivos de redo log.
Tabela 3.1 Principais processos em segundo plano da Oracle.
Agora com um conhecimento maior a respeito do funcionamento, e também de suas estruturas
lógicas do banco de dados do SGBDR em estudo, é possível compreender melhor o processo de
particionamento de tabelas, e entender o funcionamento dos diversos métodos existentes para
efetuar o particionamento de uma tabela no Oracle 11g. Ao próximo capítulo, serão abordados os
conceitos sobre particionamento de tabelas, seus métodos e exemplos dos mesmos, e também
será introduzido o conceito da tabela de planos, que é a tabela pela qual serão analisados os
estudos de casos.
44
3.6
Considerações Finais
Esse capítulo teve como objetivo introduzir ao leitor conceitos sobre a arquitetura lógica e física,
e estruturas lógicas do banco de dados Oracle 11g, para se obter um melhor entendimento sobre o
processo de particionamento de tabelas e os métodos de particionamento existentes na versão do
SGBDR em estudo, e também para compreender a tabela de planos, que será vista com mais
detalhes ao próximo capítulo, juntamente com o particionamento de tabelas e os métodos de
particionamento.
45
4
PARTICIONAMENTO DE TABELAS
Conforme Loney (2009), quando o número de registros aumenta em uma tabela de um banco de
dados, a performance, a administração, as cópias de segurança (backups) e as restaurações dos
dados (recovery) tornam-se mais lentas pelos dados estarem concentrados em apenas uma tabela,
podendo possuir muitas vezes gigabytes ou até mesmo terabytes de tamanho.
No entanto, é possível dividir registros de uma única tabela em múltiplas partes. Sendo esse
processo chamado de particionamento de tabelas, e as partes divididas da tabela chamadas de
partições (LONEY, 2009).
Além de diminuir a busca pelo particionamento da tabela, sendo necessário buscar apenas em
partições e não mais em toda a tabela, segundo Loney (2009), existem outras vantagens como, o
melhoramento da performance na administração, nas operações de backup e recovery e também
no processamento das consultas. Lembrando que, além de a técnica de particionamento permitir
particionar tabelas, ela também permite o particionamento de índices e índices organizados em
tabelas (index-organized table).
Após todas essas informações, ainda existe uma pergunta crucial: “Como saber quando
particionar uma tabela?”. Segundo o OVLDB, o guia da Oracle, ele sugere que tabelas com o
tamanho superior a 2 GB sejam consideradas candidatas a serem particionadas. Porém não deve
ser levado em conta apenas esse tamanho para que um banco de dados possa usufruir do
benefício do particionamento; deve ser aplicada essa técnica, quando é vista a necessidade de
melhoria na performance das buscas em tabelas ou por resultados mais rápidos, sendo o banco de
dados grande ou pequeno.
Algo importante a ser destacado é que qualquer tabela pode ser particionada em várias de
partições, exceto as tabelas que possuem colunas do tipo LONG ou LONG RAW. Contudo, é
possível utilizar os tipos CLOB ou BLOB, para resolver esse problema.
Ainda segundo o guia da Oracle, um aspecto importante em relação às consultas SQL e
declarações DML é que se ao trabalhar com tabelas particionadas, elas não necessitam de
modificações para acessar tabelas particionadas, ou seja, não é necessário reescrever sua
aplicação para utilizar o recurso do particionamento. No entanto, quando uma tabela é
particionada as declarações DDL podem manipular e acessar partições individuais da tabela ao
invés da tabela inteira. Tornando-se isso, um grande atrativo em relação ao gerenciamento da
46
performance em buscas, além de existirem várias técnicas de particionamento para o auxílio
desse gerenciamento.
Segundo Alapati (2009), o particionamento de tabelas no Oracle 11g pode ser feito de seis
maneiras diferentes como:
•
particionamento por faixa de valores (Range Partitioning),
•
particionamento por intervalo (Interval Partitioning),
•
particionamento por função hash (Hash Partitioning),
•
particionamento por lista de valores (List Partitioning),
•
particionamento por referência (Reference Partitioning),
•
particionamento por sistema (System Partitioning),
•
e em adição, o particionamento por combinação de métodos (Composite Partitioning),
que é justamente a combinação de algum dos métodos anteriores.
Contudo, segundo o autor, independente do método utilizado, algumas informações são
necessárias serem especificadas para que possa ser criada uma partição, como:
•
Método de partição: que é a escolha de algum dos métodos anteriores;
•
Coluna de partição: que é a coluna (ou colunas) que servirá de indicador para a partição.
Sendo que, a coluna ou conjunto de colunas de particionamento são denominados de
chaves de partição (partitioning keys). Lembrando que o Oracle, realiza automaticamente
as operações de INSERT, DELETE, UPDATE, utilizando a chave de partição para
direcionar as atualizações à partição apropriada (OVLDB).
•
Descrição da partição: essas descrições especificam quais serão os critérios para a
inclusão de chaves de particionamento em cada partição. Como por exemplo, no
particionamento por faixa de valores é utilizado a cláusula VALUES LESS THAN, para
limitar a chave de particionamento em cada partição.
A seguir, serão vistos mais detalhes sobre os métodos de particionamento de tabelas, citados
anteriormente, juntamente com um exemplo cada, para ficar mais claro como é feito o processo
de particionamento utilizando um dos métodos. E ao final do capítulo, será visto mais detalhes
sobre a tabela de planos, que será utilizada nos estudos de casos.
47
4.1
Particionamento por faixa de valores
Segundo Alapati (2009), o particionamento por faixa de valores foi o primeiro tipo de
particionamento a ser introduzido no Oracle. Esse tipo de particionamento é utilizado como o
próprio nome diz, para dados que podem ser divididos em faixas baseadas em alguns critérios.
Essas faixas podem ser baseadas em uma seqüência de números ou em outros critérios, mas essa
técnica geralmente é utilizada com faixas baseadas em tempo como, por exemplo, dados mensais
ou semestrais, segundo Alapati (2009). Porém para um melhor entendimento, na Figura 4.1 é
exibido um exemplo de particionamento de tabela, utilizando o método de particionamento por
faixa de valores.
Figura 4.1 Criação da tabela CONTROLE_VENDAS particionada por faixa de valores.
Analisando o código exibido na Figura 4.1, é possível perceber que primeiro é criado uma tabela
CONTROLE_VENDAS com os campos para inserir o número da filial da empresa, do vendedor,
do cliente que o vendedor atendeu, e a data da venda. Até então é uma tabela contendo uma
48
estrutura padrão do Oracle. Porém, a partir da linha 10, é possível visualizar que foi utilizado o
particionamento por faixa de valores, sendo ele dividido em quatro partições, e os dados
divididos em faixas de trimestres. E para cada partição é criado um tablespace diferente, no qual
armazenará a informação que atenda uma determinada chave de partição.
Como toda partição possui a cláusula VALUES LESS THAN, isso significa que toda vez que um
valor for igual ou maior que a chave de partição, ele será automaticamente armazenado na
próxima partição, caso ele satisfaça a condição da próxima chave de partição, e assim por diante.
Além disso, segundo o OVLDB, é possível utilizar a literal MAXVALUE, caso uma informação
seja diferente de qualquer chave de partição pré-determinada, inclusive o valor NULL. Na
Figura 4.2 é exibido um exemplo utilizando essa literal.
Figura 4.2 Utilizando a literal MAXVALUE.
Adicionando a linha de código anterior, exibida na Figura 4.2, ao exemplo da tabela
CONTROLE_VENDAS, é possível armazenar qualquer valor diferente das chaves de partição
pré-determinadas.
4.2
Particionamento por intervalo de valores
Conforme Alapati (2009), o particionamento por intervalo de valores é uma extensão do método
por faixa de valores. Pois, caso seja inserido um valor que exceda alguma partição, o próprio
Oracle irá criar automaticamente outra partição para esse novo registro, diferentemente do
particionamento por faixas, que iria lançar um erro nesse caso. Porém esse método possui
algumas restrições, como não suportar índices organizados em tabelas, mas para o estudo o
principal ponto a se saber é que, deve-se especificar apenas uma coluna de chave de partição,
na qual só pode ser do tipo NUMBER ou DATE.
49
A Figura 4.3 exibe um exemplo de particionamento de tabelas utilizando o método por intervalo
de valores.
Figura 4.3 Criação da tabela Cliente particionada por intervalo de valores.
No exemplo anterior exibido na Figura 4.3, é criada uma tabela com os campos para inserir o
número, o nome e o salário do cliente. Para que seja possível particionar por intervalos devemos
particionar por faixa primeiramente e após isso definir o intervalo entre as partições – lembrando
que esse intervalo só poderá ser do tipo NUMBER ou DATE. Depois disso, são criadas e
nomeadas as partições.
4.3
Particionamento por função Hash
Conforme Alapati (2009), o particionamento por função Hash é utilizado quando não há previsão
de divisão nos dados, ou seja, não há como definir uma distribuição equilibrada desses valores. A
única desvantagem desse método é que não há como saber em que partição está à informação,
pois algoritmo de Hash do SGBDR que é responsável por decidir em que partição o conteúdo
será armazenado, sendo sua distribuição de valores nas partições mais balanceada do que os
outros métodos de particionamento. Lembrando que, segundo o OVLDB, o algoritmo de Hash
50
fornecido pelo SGBDR não pode ser modificado. Uma das coisas mais importantes para utilizar
esse método é definir uma chave de partição para que o algoritmo de Hash se baseie nele, além de
definir quantas partições serão criadas. Lembrando que o número de partições deve ser sempre
uma potência de 2 (2,4,8,16,...) para que haja um melhor balanceamento. A seguir, na Figura 4.4,
é exibido um exemplo de particionamento de tabelas utilizando o método de particionamento por
função Hash.
Figura 4.4 Criação da tabela Transação particionada por função Hash.
No exemplo anterior, exibido na Figura 4.4, foi possível perceber que é criada uma tabela
chamada Transação com os campos para inserir o número da transação, o do cliente que efetuará
a transação e a data da transação. Se essa tabela receber todas as transações diárias de um banco,
por exemplo, não há como prever que dia existirão mais transações, dessa forma, a utilização dos
métodos de particionamento anteriores poderia trazer resultados insatisfatórios. Por conta disso, é
utilizado o método de partição por Hash, pois uma de suas características positivas é o
balanceamento da distribuição dos dados nas partições.
Ao final do código exibido na Figura 4.4, o particionamento por Hash recebe o número do
código da transação como chave de partição, sendo após isso, definidas quatro partições que
serão armazenadas nos respectivos tablespaces: ts1, ts2, ts3 e ts4.
51
4.4
Particionamento por lista de valores
Conforme Alapati (2009), o particionamento por lista de valores é preferencialmente utilizado
quando os dados estão distribuídos, como por exemplo, em cidades, territórios ou algum outro
atributo. Esse tipo de particionamento, diferentemente do particionamento por faixas, permite
agrupar e organizar conjuntos não ordenados e independentes dos dados de uma forma natural. A
Figura 4.5 mostra um exemplo de particionamento de tabela utilizando o método de
particionamento por lista de valores.
Figura 4.5 Criação da tabela VENDAS_SP particionada por lista de valores.
No exemplo anterior, exibido na Figura 4.5, é criado uma tabela chamada VENDAS_SP,
possuindo três campos para inserção de valores, um para número da venda, outro para o número
do pedido, e o último com a sigla da cidade.
A partir da linha 7, no exemplo da criação da tabela VENDAS_SP, é definido o método de
partição por lista, tendo ele como chave de partição, o atributo VEN_CIDADE, que se refere em
qual cidade foi realizada a venda. Já nas linhas de 8 a 12, são definidas quantas partições serão
criadas e quais as condições para que sejam gravados os registros nessas determinadas partições,
direcionando sempre para o seu tablespace respectivo.
Para evitar erros, segundo o OVLDB, caso seja inserido um valor que não contenha uma chave
de partição que tenha sido mapeada anteriormente, é possível utilizar a partição DEFAULT; pois
52
assim, qualquer valor não mapeado, será inserido automaticamente nessa partição. A seguir, na
Figura 4.6, é exibido um exemplo de utilização de partição DEFAULT.
Figura 4.6 Utilizando a partição DEFAULT.
Se adicionado à linha anterior, exibida na Figura 4.6, ao exemplo da tabela VENDAS_SP, todas
os
valores
de
chaves
de
partição
não
mapeados,
serão
inseridos
na
partição
VEN_DESCONHECIDA.
A seguir na Figura 4.7, é exibida uma ilustração de como os dados são particionados, pelos
métodos de particionamento por lista de valores, por faixa de valores e por função Hash.
Figura 4.7 Exemplo de particionamento por lista de valores,
por faixa de valores e por função Hash.
Fonte: OVLDB2.
53
4.5
Particionamento por referência
Segundo Alapati (2009), o particionamento por referência é utilizado quando há relação de pai e
filho entre as tabelas, ou seja, quando existe a relação de chave primária e estrangeira entre as
tabelas. Se existe esse relacionamento, a principal tarefa a ser realizada é indicar qual é a tabela
pai da relação para que a tabela filho herde a chave de partição da tabela pai.
A Figura 4.8 exibe um exemplo de particionamento de tabela utilizando o método de
particionamento por referência.
Figura 4.8 Criação da tabela PEDIDO particionada por referência.
Analisando o código visto na Figura 4.8, é possível perceber que na linha 7 é criada uma
constraint relacionada à chave primária da tabela PEDIDO. Logo a seguir, na linha 9 é definido o
54
particionamento por faixas, que será dividido em semestres, utilizando a data do pedido como
chave de partição para esse método de particionamento.
Agora na tabela ITEM_PED na linha 22, é criada uma constraint relacionada à chave estrangeira,
que faz referência à tabela PEDIDO, e logo após isso, na última linha, é definido o método de
partição por referência, recebendo como chave de partição a constraint previamente criada, a
IPED_ID_FK. Sendo assim, o método de particionamento por referência recebe essa constraint
como chave de partição.
4.6
Particionamento por sistema
Segundo Alapati (2009), o particionamento por sistema é o único método no qual a aplicação e
não o banco de dados controla onde os dados são armazenados. Pois a aplicação é responsável
por definir em qual partição uma determinada informação será armazenada, e se caso não seja
especificada a partição na inserção do dado, será lançado um erro. A Figura 4.9 exibe um
exemplo de particionamento de tabela utilizando o método de particionamento por sistema.
Figura 4.9 Criação da tabela VENDAS_SP particionada por sistema.
Foi utilizado a mesma estrutura da tabela VENDAS_SP, que pode ser visto na Figura 4.5, porém
agora essa tabela é particionada utilizando o método de particionamento por sistema.
55
O que difere o método de particionamento por sistema, de qualquer particionamento utilizado nos
exemplos anteriormente, é que a aplicação, e não o SGBDR que irá decidir onde serão
armazenados os dados.
A seguir na Figura 4.10, é exibido como seria feita a inserção de dados na tabela VENDAS_SP
utilizando o método de particionamento por sistema.
Figura 4.10 Exemplo de inserção de dados na tabela VENDAS_SP particionada por
sistema.
Diferentemente da operação INSERT, nas operações DELETE ou UPDATE, não é necessário
mencionar em qual partição uma determinada informação se encontra. Porém a Oracle
recomenda que isso seja feito, para evitar que o SGBDR percorra toda a tabela (ALAPATI,
2009).
4.7
Particionamento por combinação de métodos
Muitas vezes, segundo Alapati (2009), a utilização das técnicas anteriores de particionamento,
utilizadas isoladamente, não são suficientes para que os objetivos, quanto ao desempenho, sejam
atingidos. Isso indica que a criação de subpartições torna-se uma boa idéia, aproveitando os
pontos altos de cada técnica. As combinações podem ser feitas, por exemplo, utilizando o método
de particionamento por faixa de valores em conjunto com o método por função Hash. Neste caso,
primeiramente seriam criadas as partições utilizando o método por faixa de valores e
posteriormente seriam criadas as subpartições utilizando função Hash.
56
4.7.1
Particionamento por combinação de faixa de valores e função Hash
Conforme Alapati (2009), para tornar um particionamento por faixa de valores mais eficiente em
relação à distribuição dos dados, a criação de partições por função Hash, após as criadas por faixa
de valores, podem tornar o projeto otimizado.
Ainda segundo o autor, as vantagens tornam-se evidentes, pois ocorre a combinação do melhor
do particionamento por faixa de valores, como a facilidade de sua implementação, com o melhor
do particionamento por função Hash.
Na Figura 4.11, é exibido um exemplo de particionamento de tabela utilizando o método de
particionamento por combinação de faixa de valores e função Hash.
Figura 4.11 Criação da tabela ALUNO particionada pelo método de particionamento por
combinação de faixa de valores e função Hash.
No exemplo exibido na Figura 4.11, primeiramente a tabela ALUNO é particionada por faixa de
valores levando tendo como chave de partição o número de matrícula do aluno, onde serão
criadas quatro partições baseadas em faixa de valores. Após esse particionamento, as mesmas
57
quatro partições serão particionadas por função Hash, tendo como chave de partição o nome do
aluno, o que resultará, ao final, em 32 subpartições.
4.7.2
Particionamento por combinação de faixa de valores e lista de valores
Segundo Alapati (2009), no particionamento por combinação de faixa de valores e lista de
valores, primeiramente ocorre o particionamento baseado em faixa de valores. Após isso, é
utilizado o particionamento por lista de valores para dividir o primeiro grupo de partições.
A seguir na Figura 4.12, é exibido um exemplo de tabela particionada utilizando o método de
particionamento por combinação de faixa de valores e lista de valores.
58
Figura 4.12 Criação da tabela VENDAS particionada pelo método de particionamento por
combinação de faixa de valores e lista de valores.
59
Este procedimento cria 16 subpartições utilizando combinação de faixa de valores e lista de
valores contendo quatro subpartições em cada tablespace. Quando ocorrer uma inserção na tabela
vendas, primeiramente o Oracle verificará se o valor da chave de particionamento dessa linha esta
dentro do intervalo de uma partição específica, mapeando então para uma subpartição dentro
desta partição, mapeando o valor da chave da subpartição para a subpartição apropriada baseado
nos valores da partição por lista de valores.
Agora ao final da abordagem sobre os métodos de particionamento existentes no Oracle 11g, é
importante conhecer o que é a tabela de planos, quais são seus campos, qual a sua função, e para
qual propósito ela será utilizada no desenvolvimento do trabalho. Dessa forma, ao próximo tópico
serão abordados esses conceitos sobre a tabela de planos.
4.7.3
Tabela de Planos
Conforme Price (2009), o Oracle usa um subsistema chamado otimizador para gerar um caminho
mais eficiente para buscar os dados nas tabelas. Esse caminho gerado pelo otimizador é chamado
de plano de execução. A partir do Oracle 10g, é utilizado a otimização baseada em custo (CBO –
Cost-Based Optimization) que utiliza E/S de disco, utilização de CPU e utilização de memória
como unidades de trabalho. Nesse tipo de otimização ocorre a coleta de estatísticas sobre os
dados das tabelas e de índices automaticamente, para que seja possível gerar o melhor plano de
execução. E por meio disso, é possível avaliar uma consulta SQL em relação à outra, levando
assim a otimização de consultas.
Segundo o ODPTG, para executar uma consulta SQL, o plano de execução pode executar várias
etapas. Cada etapa pode retornar dados armazenados fisicamente no banco de dados ou preparar
as linhas de alguma maneira para o usuário que o requisitou. Um plano de execução exibe de
forma geral um caminho de acesso que foi utilizado para a recuperação dos dados que a consulta
necessita.
Para que seja possível examinar o plano de execução, segundo Price (2009), é necessário utilizar
o comando EXPLAIN PLAN. Esse comando preenche a tabela chamada PLAN_TABLE (ou
também, “tabela de plano”) com o plano de execução da consulta escolhida. A seguir é exibido
um exemplo na Figura 4.13 utilizando o comando EXPLAIN PLAN, juntamente com uma
60
consulta que irá selecionar o primeiro nome do cliente, no qual possua um número de
identificação igual a 600.
Figura 4.13 Exemplo de uso da consulta EXPLAIN PLAN.
No exemplo anterior, exibido na Figura 4.13, o comando EXPLAIN PLAN popula a tabela
PLAN_TABLE, com o plano de execução corrente, e com o auxílio do comando SET
STATEMENT_ID é nomeado esse plano de execução de “CUSTOMER”. Agora com o comando
ilustrado na Figura 4.14, são selecionadas algumas colunas da tabela PLAN_TABLE, onde o
nome do plano de execução seja “CUSTOMER”.
Figura 4.14 Comando para selecionar dados da tabela de planos de execução.
A Figura 4.15 demonstra o resultado obtido pela consulta do exemplo anterior (Figura 4.14).
Logo em seguida, na Tabela 1.4, serão descritos alguns campos da tabela de planos para o
estudo.
61
Figura 4.15 Representação de partes da tabela PLAN_TABLE.
A Tabela 1.4 exibida a seguir, mostra uma breve descrição sobre algumas colunas obtidas como
resultado na Figura 4.15, e também outras colunas que serão utilizadas mais adiante no trabalho,
como BYTES, COST, TIME, entre outras.
Colunas da tabela PLAN_TABLE
ID
Número atribuído a cada passo no plano de execução.
STATEMENT_ID
Nome do plano de execução.
OPERATION
Nome da operação interna realizada em uma determinada etapa.
Observemos que a primeira linha dessa coluna é gerada por uma
declaração. Essa linha pode conter uma das seguintes declarações:
DELETE, INSERT, SELECT ou UPDATE.
OPTIONS
Opção usada na operação, como por exemplo, FULL, que é a opção
de varredura integral.
OBJECT_NAME
Nome do objeto do banco de dados.
OBJECT_TYPE
Tipo do objeto da operação, como por exemplo, TABLE.
PARENT_ID
Número pai. Refere-se ao passo atual no plano de execução; como no
exemplo anterior, o ID igual a um, possui como pai o ID igual a zero.
POSITION
Ordem de processamento dos passos que têm o mesmo valor de
PARENT_ID.
CARDINALITY
Estimativa do número de linhas acessadas pela operação.
BYTES
Estimativa do número de bytes acessados pela operação.
COST
Cálculo das unidades de trabalho, ou seja, é feito o cálculo do
número de E/S de disco, da quantidade de CPU e da memória
utilizada para executar uma operação.
TIME
Estimativa do tempo transcorrido em segundos da operação.
62
PARTITION_START
É a partição inicial de uma faixa de partições acessadas. Ela pode ser
de valor n, ou seja, isso significa que a partição inicial (ou de
arranque) foi identificada pelo compilador SQL e seu número de
partição é igual a n.
PARTITION_STOP
É a partição final de uma faixa de partições acessadas. Ela pode ser
de valor n, ou seja, isso significa que a partição final foi identificada
pelo compilador SQL e seu número de partição é igual a n.
Tabela 4.1 Colunas da tabela PLAN_TABLE
Agora para finalizar, foi abordado ao decorrer do capítulo, o conceito de particionamento de
tabelas, os métodos de particionamento existentes no Oracle 11g, o conceito sobre a tabela de
planos e juntamente o conceito sobre o plano de execução. Com esses conhecimentos, é possível
seguir adiante com o trabalho, passando a seguir, para o desenvolvimento do projeto, que consiste
em criação de cenários utilizando métodos de particionamentos distintos, realização de estudos de
casos e análises dos resultados obtidos pelos mesmos.
4.7.4
Considerações Finais
Neste capítulo foi abordado o conceito sobre particionamento de tabelas, os métodos de
particionamento existentes no Oracle 11g e exemplos relacionados a eles, além de abordados os
conceitos sobre a tabela de planos e o plano de execução. A seguir, ao próximo capítulo, será
visto os detalhes sobre o desenvolvimento do projeto.
63
5
DESENVOLVIMENTO DOS ESTUDOS DE CASOS
Tendo adquirido conhecimento relacionado aos diversos métodos de particionamento de tabelas,
é possível perceber que o método de particionamento por faixa de valores e o método de
particionamento por função Hash são distintos, possuindo os dois, características positivas e
negativas.
Dessa forma, por esses dois métodos serem distintos, foram escolhidos para serem utilizados no
desenvolvimento do projeto, para que sejam estudados seus comportamentos em um determinado
ambiente.
No desenvolvimento do projeto, serão criados dois cenários utilizando a mesma estrutura de
tabela, a tabela CUSTOMER, e serão populados com a mesma quantidade de dados; para que
assim, ambos os cenários sejam submetidos a testes, e seja possível analisar os resultados obtidos
dos mesmos.
O primeiro cenário a ser criado, o Cenário 1, será particionado pelo método de particionamento
por faixa de valores. Já o segundo cenário, o Cenário 2, será particionado pelo método de
particionamento por função Hash.
Sendo criados os cenários, serão efetuados testes sobre os mesmos. Nos testes, serão utilizados
uma consulta específica sendo ela executada duas vezes em ambos os cenários, Cenário 1 e
Cenário 2. A primeira execução dessa consulta é feita em um dos cenários sem um índice
adicional, e a segunda execução, com um índice adicional. Desse modo, os resultados obtidos por
esses processos, serão analisados e comparados ao final do trabalho.
A seguir, ao próximo tópico, será exibida uma tabela com a especificação do servidor de teste, e
mais adiante, serão vistos mais detalhes sobre os cenários criados, a consulta e os resultados
obtidos por ela, e os estudos de casos.
5.1.1
Especificação do Computador
Na Tabela 1.5 são exibidas as especificações do servidor de teste, que foi utilizado no
desenvolvimento do projeto.
64
Especificações do servidor de teste
HD
220 GB
Memória RAM
3 GB
Processador
2x DualCore Xeon 5110, 1.60GHz
Sistema Operacional
Microsoft Windows Server 2003 Standard
Edition Service Pack 2
Tabela 5.1 Especificações do servidor de teste
A seguir, são dados os detalhes sobre a tabela CUSTOMER, utilizada nos cenários criados no
projeto.
5.1.2
Tabela Customer
A estrutura da tabela CUSTOMER é composta no geral por campos de diversos tipos como
NUMBER, VARCHAR, DATE, CHAR, de diferentes tamanhos. Essa tabela foi baseada na base
de dados oferecida pela editora Wiley, com apenas algumas modificações, que serão
mencionadas nos próximos parágrafos. A seguir, na Tabela 1.6, são exibidos os atributos da
tabela CUSTOMER.
Descrição da Tabela Customer
Campo
Tipo
Tamanho
Restrição
-
Primary Key
CUSTOMER_ID
NUMBER
FIRST_NAME
VARCHAR2
30
-
MIDDLE_INITIAL
CHAR
1
-
LAST_NAME
VARCHAR2
30
-
DATE_OF_BIRTH
DATE
-
-
MAIDEN_NAME_MOTHER
VARCHAR2
30
-
GENDER_LOOKUP
NUMBER
-
-
CITY_NAME
VARCHAR2
64
-
ADDRESS1
VARCHAR2
50
-
65
ADDRESS2
VARCHAR2
50
-
POSTAL_CODE
VARCHAR2
10
-
EMAIL_ADDRESS
VARCHAR2
64
-
TELEPHONE_NUMBER
VARCHAR2
12
-
PASSWORD_HASH
VARCHAR2
41
-
WEBSITE_ID
NUMBER
-
-
BALANCE
VARCHAR2
6
-
DATE_REGISTERED
DATE
-
-
DATE_UNREGISTERED
DATE
-
-
TIMESTAMP_CHANGED
DATE
-
-
REGION_ID
NUMBER
-
-
Tabela 5.2 Descrição da Tabela CUSTOMER
Visto os detalhes da tabela CUSTOMER, exibida na Tabela 1.6, pode-se visualizar que a tabela
possui no total 20 campos, sendo eles de vários tipos e tamanhos. Para preencher esses campos
em ambos os cenários, a tabela CUSTOMER será populada a partir da base de dados para a
execução de testes oferecida pela editora Wiley. Essa base de dados é utilizada no livro Pentaho
Solution: Business Intelligence and Data Warehousing with Pentaho and MySQL, publicado pela
própria editora de forma gratuita. Tanto a estrutura da tabela CUSTOMER, quanto à base de
dados foram tirados da base de dados oferecidos pela editora Wiley.
Para a realização dos testes, as tabelas CUSTOMER criadas em ambos os cenários, Cenário 1 e
Cenário 2, foram populadas com aproximadamente 20 milhões de registros cada, tendo em vista
que foi utilizado um artifício de programação, que pode ser visto na sessão APÊNDICE, com o
intuito de replicar os dados para obter um grande volume de dados.
Na próxima sessão, serão discutidos os detalhes da consulta que será utilizada nos estudos de
casos que serão vistos mais adiante, e também o resultado que essa consulta obtém quando
executada.
66
5.1.3
Consulta
A consulta exibida na Figura 5.1 obtém como resultado a quantidade de clientes em ordem
decrescente, que nasceram em um determinado ano, de uma lista pré-determinada de cidades.
Figura 5.1 Consulta utilizada nos Estudos de Casos.
Essa consulta, exibida na Figura 5.1, seleciona o nome da cidade, o ano de nascimento e o total
de clientes, de uma lista de cidades (Toledo, Bohemia, Tampa e Los Angeles), dos quais
nasceram entre 1940 e 1960, sendo eles agrupados pelo nome da cidade e também pelo ano de
nascimento, e ao final foram ordenados, primeiramente pelo total de clientes, seguido pela cidade
e por último pelo ano. O resultado obtido após a execução dessa consulta é o exibido a seguir, na
Figura 5.2.
67
Figura 5.2 Resultado da consulta utilizada nos Estudos de Casos
Conhecido os detalhes da tabela CUSTOMER e os da consulta que será utilizada nos estudos de
casos, será visto a seguir, os detalhes dos cenários que foram criados, Cenário 1 e Cenário 2, para
que após isso, sejam vistos os estudos de casos sobre os mesmos.
68
5.1.4
Cenário 1
No Cenário 1 foi criada a tabela CUSTOMER, que pode ser vista na Tabela 1.6, e a mesma foi
particionada pelo método de particionamento por faixas de valores. Foram criadas quatro
partições, sendo elas denominadas PARTICAO1, PARTICAO2, PARTICAO3 e PARTICAO4;
possuindo cada uma um tablespace correspondente. Cada tablespace foi criado inicialmente com
10 MB, sendo auto-extensível em 50 MB, possuindo um tamanho máximo de 5 GB. A Figura
5.3 mostra a quantidade de linhas inseridas em cada partição da tabela CUSTOMER referente ao
Cenário 1.
Figura 5.3 Quantidade de linhas inseridas em cada partição da tabela CUSTOMER
particionada por faixa de valores.
Logo a seguir, na Figura 5.4, é exibido um trecho do código fonte referente à tabela
CUSTOMER criada para esse cenário, demonstrando os detalhes referentes ao particionamento
por faixa de valores.
69
Figura 5.4 Trecho do código fonte da tabela CUSTOMER particionada por faixa de
valores.
Para a chave de partição foi escolhida a coluna DATE_OF_BIRTH, coluna pela qual armazena a
data de nascimento dos clientes, como se pode ver na Figura 5.4.
Cada partição criada irá receber uma faixa de valores, como por exemplo, na partição
PARTICAO1, conforme a Figura 5.4, o valor da chave de partição para essa partição é a data
01/01/1945, ou seja, todos os clientes que nasceram nas datas anteriores à 01/01/1945, serão
inseridos nessa partição. Já na partição PARTICAO2, o valor da chave de partição
correspondente é a data 01/01/1960, ou seja, todos os clientes nascidos entre 01/01/1945 e
01/01/1960, serão inseridos na partição PARTICAO2. Essa lógica ocorre com todas as outras
partições, recebendo sempre uma faixa de valores pré-definida.
Em resumo, o Cenário 1, utilizou a estrutura da tabela CUSTOMER, que pode ser vista na
Tabela 1.6, e foi aplicado o particionamento por faixa de valores na mesma, sendo a tabela
particionada em quatro partições. Conhecidos os detalhes do Cenário 1, serão vistos a seguir os
detalhes do Cenário 2.
70
5.1.5
Cenário 2
No Cenário 2 foi criada uma outra tabela CUSTOMER, com a mesma estrutura que pode ser
vista na Tabela 1.6, porém agora particionada pelo método de particionamento por função Hash.
Foram criadas quatro partições; possuindo cada partição um tablespace correspondente. Cada
tablespace foi criado inicialmente com 10 MB, sendo auto-extensível em 50 MB, possuindo um
tamanho máximo de 5 GB.
Para este Cenário, a chave de partição escolhida foi a mesma do Cenário 1, a coluna
DATE_OF_BIRTH. Cada partição irá receber um registro conforme o cálculo efetuado pela
função Hash do Oracle, ou seja, essa função fica responsável por escolher em qual partição será
inserido um novo registro.
A Figura 5.5 mostra a quantidade de registros inseridos na tabela CUSTOMER referentes ao
Cenário 2.
Figura 5.5 Quantidade de linhas inseridas em cada partição da tabela CUSTOMER
particionada por função Hash
Logo a seguir, na Figura 5.6 é exibido um trecho do código fonte da tabela CUSTOMER criada
para esse cenário, demonstrando os detalhes referentes ao particionamento por função Hash.
Figura 5.6 Trecho do código da tabela CUSTOMER particionada por função Hash.
71
Resumidamente, o Cenário 2 utilizou a mesma estrutura da tabela CUSTOMER, que pode ser
vista na Tabela 1.6, porém neste caso, a tabela foi particionada pelo método de particionamento
por função Hash, sendo criadas quatro partições.
Visto os detalhes dos Cenários, serão abordados a seguir os estudos de casos que foram
realizados nos cenários descritos anteriormente. Primeiramente os estudos de casos realizados no
Cenário 1, e em seguida, os realizados no Cenário 2.
5.1.6
Estudo de Caso F1 – Sem índice adicional e particionado por faixa de valores
Para este estudo de caso, foi utilizado o Cenário 1 descrito anteriormente, sem um nível adicional
de otimização, ou seja, não foi criado um índice adicional ao cenário. Sobre este cenário foi
executada a consulta apresentada anteriormente, que pode ser vista na Figura 5.1. Após ter sido
executada a consulta, foi gerado o plano de execução da mesma sobre o Cenário 1. E ao final do
estudo de caso, o resultado desse plano de execução é analisado. A seguir na Figura 5.7, pode-se
visualizar o resultado obtido do plano de execução da consulta em estudo.
Figura 5.7 Resultado do plano de execução da consulta em estudo realizada no Estudo de
Caso F1.
Visto o resultado do plano de execução da consulta exibido na Figura 5.7 será analisado esse
resultado mais detalhadamente, esclarecendo primeiramente o que cada coluna tem como valor
final, para que então sejam discutidos os passos executados no plano de execução.
A primeira coluna, a coluna ID, segundo ODPTG, exibe um número atribuído para cada passo
que foi executado em todo o plano de execução. Logo ao lado, a coluna OPERATION mostra o
72
nome da operação interna realizada no plano de execução. Como a consulta que foi executada
nos testes trata-se de um SELECT, então na primeira linha dessa coluna será exibido o valor
SELECT STATEMENT; conforme a documentação, isso significa que se trata de uma declaração
SELECT. Continuando, a coluna NAME exibe qual o objeto do banco de dados foi utilizado em
conjunto com alguma operação que tenha sido executada no plano. E a coluna ROWS exibe a
estimativa de linhas acessadas por cada operação.
A quinta coluna, a coluna BYTES, exibe a estimativa do número de bytes acessados por uma
devida operação. Ao lado, a coluna COST exibe a estimativa feita a partir do cálculo do número
de E/S de disco, da quantidade de CPU e da memória utilizada para executar da operação. E a
seguinte, a coluna Time exibe o tempo decorrido em segundos referente a alguma determinada
operação. E por final, a coluna PSTART exibe qual foi à primeira partição a ser acessada, e a
coluna PSTOP qual foi à última.
Compreendido o que cada coluna da tabela de planos representa, será feita a análise do ocorrido
no plano de execução exibido na Figura 5.7. Para entender o ocorrido, deve-se ler a tabela de
planos debaixo para cima, conforme o ODPTG. Desse modo, a seguir, é iniciada a leitura na
etapa 4 exibida na Figura 5.7.
A etapa 4 é a primeira etapa a ser executada e obtém o resultado TABLE ACCESS FULL, ou
seja, segundo ODPTG, para esse passo foi necessário executar uma varredura integral da tabela
CUSTOMER, nas partições de número 1, 2 e 3. E após ter concluído esse processo, seus
resultados são passados para a etapa 3.
A etapa 3 recebe como resultado PARTITION RANGE ITERATOR, que segundo ODPTG,
significa que foi feito um acesso a um subconjunto de partições, ou seja, foram acessadas apenas
as partições de número 1, 2 e 3, conforme pode-se visualizar nas colunas PSTART e PSTOP. Isso
ocorreu porque foi feito um filtro dos valores baseado nos predicados após a cláusula WHERE da
consulta. E concluído essa operação, seus resultados são passados para a etapa 2.
A etapa 2 tem como resultado uma operação HASH GROUP BY, que conforme ODPTG,
significa que as linhas retornadas pelo GROUP BY são alocadas em um cluster de hash baseado
no valor de hash atribuído a linha retornada pelo comando. Em um cluster de hash, todas as
linhas com o mesmo valor hash são armazenadas no mesmo bloco de dados. Quando uma
varredura hash é executada, o Oracle primeiro obtém o valor hash, aplicando a função hash a um
valor da chave do cluster especificado pela consulta. Para que depois disso, o Oracle faça uma
73
varredura nos blocos de dados que contém as linhas com o valor hash, retornando assim o
conjunto de linhas final desse passo, passando o para a etapa 1.
Na etapa 1, é executado a operação SORT ORDER BY, que segundo Powell (2004), significa
que foi aplicado uma operação de classificação nos dados recebidos do passo anterior, pois por
padrão a cláusula GROUP BY não classifica os valores e sim apenas os agrupa, então se caso
necessite de uma classificação deve-se utilizar a cláusula ORDER BY determinando quais
colunas os valores devem ser classificados e se desejar em qual ordem eles serão exibidos, se em
ordem crescente ou decrescente, sendo que a ordem crescente é a padrão do Oracle. E por final,
sendo o último passo, a etapa 1 retorna o conjunto de linhas resultante da consulta exibido
previamente na Figura 5.2 para o prompt do SQL Plus.
Contudo, a partir desse plano de execução, pode-se visualizar na Figura 5.7 que foram
consumidos do sistema aproximadamente 3,6 MB e um custo de aproximadamente de 1400 K
para a execução da consulta. Em relação ao tempo, o tempo estimado pelo plano de execução foi
de 4 minutos, 46 segundos e 59 centésimos.
Concluída a análise desse estudo de caso sem um nível adicional de otimização, será visto a
seguir, o próximo estudo de caso utilizando o mesmo cenário, o Cenário 1, porém com o nível
adicional de otimização, para que dessa forma possa-se conhecer o comportamento do
particionamento por faixa de valores neste ambiente.
5.1.7
Estudo de Caso F2 – Com índice adicional e particionado por faixa de valores
Conforme dito anteriormente, para este estudo de caso foi utilizado o Cenário 1, particionado por
faixa de valores, o mesmo do estudo de caso anterior, porém com um nível adicional de
otimização, ou seja, foi criado um índice adicional à tabela CUSTOMER, que pode ser vista na
Tabela 1.6. Após criado o índice adicional, foi executada a consulta descrita anteriormente, que
pode ser vista na Figura 5.1, e foi obtido um plano de execução da mesma. E ao final do estudo
de caso, os passos obtidos no plano de execução são analisados.
O índice adicional criado à tabela CUSTOMER, é um índice de árvore B (BTree), que segundo
Price (2009), é o índice padrão do Oracle. Esse índice adicional foi criado previamente a
execução da consulta, sendo adicionado sobre a coluna CITY_NAME, pela qual a consulta
executada recupera os valores referentes às cidades armazenadas na tabela CUSTOMER, e
74
nomeado de C1BTREE. A seguir na Figura 5.8, é exibido o resultado do plano de execução da
consulta em estudo.
Figura 5.8 Resultado do plano de execução da consulta em estudo realizada no Estudo de
Caso F2.
Observados os resultados referentes ao plano de execução da consulta exibido na Figura 5.8,
serão analisados seus passos a seguir, iniciando a leitura na última linha do plano de execução, a
etapa 5.
Na etapa 5, segundo Powell (2004), foi executada uma varredura de faixa de valores do índice
para encontrar uma faixa de ponteiros de ROWID, ou seja, um INDEX RANGE SCAN.
Recuperado esse conjunto de ROWIDs, esse resultado é passado para a etapa 4.
Na etapa 4, como a tabela CUSTOMER foi particionada e suas linhas são localizadas apenas por
índice global, segundo ODPTG, então pode ocorrer um TABLE ACCESS BY GLOBAL INDEX
ROWID. Nesse caso ocorreu um acesso utilizando o índice global da tabela, a chave primária,
recuperando as linhas referentes ao conjunto de ROWID, na etapa anterior. Esse resultado é
passado para a etapa 3.
Na etapa 3, conforme OPTG, a operação INLIST ITERATOR é executada quando o operador IN
faz parte da consulta. Nessa operação, segundo o ODPTG, ocorre uma iteração sobre a próxima
operação do plano para cada valor do predicado na IN-list. Os valores dentro dos parênteses do
operador IN são chamados de IN-List, segundo Schrag (1998). Finalizada essa etapa, os valores
são passados para a etapa 2.
Na etapa 2, como visto no estudo de caso anterior, foi executado um HASH GROUP BY, ou seja,
segundo o ODPTG, as linhas retornadas pela cláusula GROUP BY são alocadas em um cluster de
hash baseado no valor de hash atribuído a linha retornada pelo comando. Concluída essa etapa, o
conjunto de linhas resultantes são passados para a etapa 1.
75
Assim como a etapa 1 do estudo de caso anterior, a etapa 1 do plano de execução atual, executou
a operação SORT ORDER BY. Segundo Powell (2004), isso significa que foi aplicada uma
operação de classificação nos dados recebidos da etapa 2. E sendo a etapa 1, a última etapa
executada, então são retornados os dados resultantes da consulta exibido previamente na Figura
5.2 para o prompt do SQL Plus.
Para finalizar a análise desse estudo de caso, pode-se perceber a partir do plano de execução
exibido na Figura 5.8, que foram consumidos do sistema aproximadamente 3,6 MB e um custo
de aproximadamente de 77,2 K para a execução da consulta. Em relação ao tempo, o tempo
estimado pelo plano de execução foi de 15 segundos e 27 centésimos, sendo esse tempo bem
menor em relação ao estudo de caso anterior.
Conhecidos os resultados obtidos dos estudos de casos executados no Cenário 1, serão abordados
a seguir, os estudos de casos executados no Cenário 2. E ao final deles, será feita uma conclusão
referente a todos os estudos de casos que foram executados no decorrer do trabalho.
5.1.8
Estudo de Caso H1 – Sem índice adicional e particionado por função Hash
Para este estudo de caso, foi utilizado o Cenário 2, particionado por função Hash, descrito
anteriormente. Segundo a mesma linha de raciocínio dos estudos de casos anteriores,
primeiramente nesse estudo de caso, é executada a consulta, que pode ser vista na Figura 5.1,
sem um nível adicional de otimização ao cenário. Depois de executada essa consulta, é obtido o
plano de execução da mesma, sendo ele em seguida, analisado. A seguir na Figura 5.9, pode-se
visualizar o resultado do plano de execução da consulta para este estudo de caso.
Figura 5.9 Resultado do plano de execução da consulta em estudo realizada no Estudo de
Caso H1.
76
A partir da Figura 5.9, pode-se observar que foram realizadas quatro etapas para o término do
plano de execução da consulta em estudo. A seguir, serão analisadas as etapas executadas,
iniciando a análise pela etapa 4.
Na etapa 4 foi feito um TABLE ACCESS FULL, no qual segundo ODPTG, foi necessário
executar uma varredura integral da tabela CUSTOMER, nas partições 1, 2, 3 e 4, ou seja, foram
lidas todas as partições pertencentes a tabela CUSTOMER. E após ter sido concluída essa etapa,
seus resultados são passados para a etapa 3.
Na etapa 3 foi executada a operação PARTITION HASH ALL, que conforme ODPTG foi feito
um acesso à todas as partições. E finalizada essa operação, os resultados obtidos dessa etapa são
passados para a etapa 2.
Na etapa 2 foi executado um HASH GROUP BY, para que as linhas retornadas pelo GROUP BY
fossem alocadas em um cluster de hash baseado no valor de hash atribuído a linha retornada pelo
comando, conforme o ODPTG. E finalizado esse passo, os resultados obtidos são entregues à
etapa 1.
Na etapa 1 foi aplicada a operação SORT ORDER BY, pela qual executa uma operação de
classificação nos dados retornados pela etapa anterior (POWELL, 2004). E for final, sendo a
ultima etapa, retorna os valores resultantes para o prompt do SQL Plus.
Para concluir este estudo de caso, pode-se observar pela Figura 5.9, que para a execução do
plano de execução foram gastos aproximadamente 1,5 MB de recursos do sistema, e foi
necessário um custo de aproximadamente 152 K. Em relação ao tempo, o tempo estimado pelo
plano de execução foi de 30 segundos e 36 centésimos.
Conhecido os detalhes referentes do estudo de caso sem um nível adicional de otimização
realizado no Cenário 2, logo a seguir, serão descritos os detalhes do próximo estudo de caso
utilizando o mesmo cenário, porém com um nível adicional de otimização. E após esse estudo de
caso, serão analisados os resultados obtidos por todos os estudos de casos, sendo feitas
comparações e comentários a respeito dos mesmos.
5.1.9
Estudo de Caso H2 – Com índice adicional e particionado por função Hash
Neste estudo de caso, foi utilizado o Cenário 2, particionado por função Hash, descrito em
tópicos anteriores, no qual também foi utilizado no estudo de caso anterior. Porém neste estudo
77
de caso, é utilizado um nível adicional de otimização, ou seja, é criado um índice adicional à
tabela CUSTOMER. Tendo sido criado o índice adicional, é executada a consulta, que pode ser
vista na Figura 5.1, e após a execução da mesma, é obtido o resultado de seu plano de execução, e
em seguida o mesmo é analisado.
O índice adicional criado à tabela CUSTOMER para este cenário foi chamado de C2BTREE, e
conforme o índice criado na sessão 5.1.7, esse índice adicional também é baseado na coluna
CITY_NAME, pela qual a consulta recupera os nomes das cidades da tabela CUSTOMER. A
seguir na Figura 5.10, é exibido o resultado do plano de execução da consulta em estudo.
Figura 5.10 Resultado do plano de execução da consulta em estudo realizada no Estudo de
Caso H2.
Visto os resultados referentes ao plano de execução da consulta em estudo, exibidos na Figura
5.10, serão analisados os passos executados no mesmo, iniciando a análise pela última linha do
plano de execução, a etapa 5.
Na etapa 5, conforme Powell (2004), foi executada uma varredura de faixa de valores do índice
para encontrar uma faixa de ponteiros de ROWID; e recuperado esse conjunto de ROWIDs, o
resultado é passado para a etapa 4.
Na etapa 4, segundo ODPTG, ocorreu um acesso utilizando o índice global da tabela, a chave
primária, recuperando as linhas referentes ao conjunto de ROWID, da etapa anterior. Finalizando
a execução desse passo, o resultado obtido é passado para a etapa 3.
Na etapa 3, conforme ODPTG, ocorre uma iteração sobre a próxima operação do plano para cada
valor do predicado na IN-list. E finalizado essa etapa, os valores são passados para a etapa 2.
Na etapa 2, segundo o ODPTG, as linhas retornadas pela cláusula GROUP BY são alocadas em
um cluster de hash baseado no valor de hash atribuído a linha retornada pelo comando. E
havendo concluído essa etapa, os valores resultantes são passados para a etapa 1.
78
Na etapa 1, segundo Powell (2004), foi aplicada uma operação de classificação nos dados
recebidos da etapa 2, um SORT ORDER BY. E sendo a etapa 1, a última etapa a ser executada,
então são retornados os dados finais da consulta para o prompt do SQL Plus.
Para concluir a análise desse estudo de caso, pode-se visualizar a partir da Figura 5.10, que para
a realização do plano de execução foram consumidos do sistema aproximadamente 1,5 MB e um
custo de aproximadamente de 77,1 K para a execução da consulta. Em relação ao tempo, o tempo
estimado pelo plano de execução foi de 15 segundos e 26 centésimos.
Sendo este o último estudo de caso realizado, será feita a seguir, uma análise contendo todos os
estudos de caso efetuados ao longo do trabalho, sendo realizados conclusões, comparações e
comentários, referentes aos resultados obtidos nos estudos de casos executados durante o
trabalho.
5.1.10 Conclusão dos Estudos de Casos
Foram realizados quatro estudos de casos no decorrer do trabalho, sendo que dois deles
utilizaram o Cenário 1, e dois deles o Cenário 2. Os dois estudos de casos que foram executados
no Cenário 1, um deles possuía um nível adicional de otimização, ou seja, foi criado um índice
BTree adicional. O mesmo princípio foi utilizado, com os estudos de casos que foram executados
no Cenário 2.
Ao iniciar a criação dos cenários, já foi vista uma característica positiva sobre o armazenamento
em disco. O Cenário 2, tendo a tabela CUSTOMER particionada por função Hash, obteve maior
vantagem em relação ao Cenário 1, que possui a tabela CUSTOMER particionada por faixa de
valores. A seguir na Figura 5.11, são exibidos dois gráficos que comparam o armazenamento em
disco das partições entre os cenários, Cenário 1 e 2.
79
Figura 5.11 Diferença entre os resultados do plano de execução da consulta realizada no
Estudo de Caso F1 e H1.
Em todos os estudos de casos, foram utilizados os resultados do plano de execução sempre
obtidos após a execução da consulta, que pode ser vista na Figura 5.1. Esses resultados dos
estudos de casos serão analisados a seguir, sendo comparados os estudos de casos sem um nível
adicional de otimização, e após isso, será feita a comparação dos estudos de casos com um nível
adicional de otimização.
Primeiramente, analisando os resultados obtidos dos estudos de casos sem um nível adicional de
otimização, pode-se observar que houve diferenças entre o estudo de caso que foi utilizou a
tabela CUSTOMER particionada por faixa de valores, Estudo de Caso F1, e o que utilizou a
tabela CUSTOMER particionada por função Hash, Estudo de Caso H1. Dentre essas diferenças,
80
podemos destacar que no Estudo de Caso H1 houve modificações nas etapas executadas no
plano de execução, onde ao invés da ocorrência de um PARTITION RANGE ITERATOR,
observadas no Estudo de Caso F1, ocorreram respectivamente, um PARTIION HASH ALL. A
seguir na Figura 5.12, é exibida a diferença vista da terceira etapa do plano de execução
realizados nos Estudos de Casos F1 e H1.
Figura 5.12 Diferença entre os resultados do plano de execução da consulta realizada no
Estudo de Caso F1 e H1.
Além da diferença da operação na terceira etapa do plano de execução, os valores das colunas,
BYTES, COST e TIME diferiram-se entre si, sendo que o Estudo de Caso H1, que utilizou a
tabela CUSTOMER particionada por Hash, obteve valores consideravelmente menores em
relação ao Estudo de Caso F1. A seguir na Figura 5.13, são demonstradas essas diferenças de
81
resultados, exibindo diversos gráficos, mostrando a comparação entre a estimativa do consumo
do sistema, o custo e o tempo entre esses dois estudos de casos.
Figura 5.13 Comparação entre os resultados obtidos no Estudo de Caso F1 e H1.
Agora analisando os resultados obtidos dos estudos de casos que possuem um nível adicional de
otimização, foi observado que foram executadas as mesmas etapas em seus planos de execução
em ambos os estudos de casos, o Estudo de Caso F2 e H2. A seguir na Figura 5.14, pode-se
visualizar que foram executadas as mesmas etapas nos planos de execução de ambos os estudos
de casos, mesmo utilizando cenários que foram particionados por métodos de particionamentos
distintos.
82
Figura 5.14 Comparação entre os resultados do plano de execução da consulta realizada no
Estudo de Caso F2 e H2.
Em relação aos resultados das colunas, BYTES, COST e TIME, dos Estudos de casos F2 e H2,
foram observadas pequenas diferenças entre seus valores, sendo praticamente equivalentes. A
seguir na Figura 5.15, são demonstrados esses resultados, exibindo diversos gráficos, mostrando
a comparação entre a estimativa do consumo do sistema, o custo e o tempo entre esses dois
estudos de casos.
83
Figura 5.15 Comparação entre os resultados obtidos no Estudo de Caso F2 e H2.
Após as análises realizadas referentes aos Estudos de casos F1 e H1, que não possuem um nível
adicional de otimização, e aos Estudos de casos F2 e H2, que possuem um nível adicional de
otimização, ou seja, foi criado um índice adicional à tabela CUSTOMER, pode-se concluir que,
quando a consulta que foi utilizada nos testes, e pode ser revista na Figura 5.1, é executada nos
Estudos de casos sem um índice adicional, Estudos de casos F1 e H1, o Estudo de Caso H1,
que utiliza a tabela CUSTOMER particionada por função Hash, obtém melhores resultados do
que o Estudo de Caso F1, que utiliza a tabela CUSTOMER particionada por faixa de valores,
isso fica claro, pelo método de particionamento por função Hash, distribuir de maneira mais
uniforme o armazenamento em disco, proporcionando assim tempos de respostas equivalentes
dentro da partição acessada, tornando esse método um melhor candidato para o problema
proposto, quando não existe um nível adicional de otimização à tabela.
84
Porém, quando essa mesma consulta é executada nos estudos de casos que possuem um nível
adicional de otimização, os Estudos de casos F2 e H2, os resultados obtidos de ambos os estudos
de casos, tornam-se equivalentes. Isso ocorre pelo otimizador baseado em custo no Oracle 11g,
utilizar o índice criado previamente sobre a coluna CITY_NAME. Deixando dessa forma,
facultativa a opção de método de particionamento, ficando claro que a utilização de índices em
tabelas particionadas, é um assunto a ser levado em conta, e que se bem empregado, pode
resolver problemas referentes à performance nas buscas.
Terminado as análises feitas sobre todos os estudos de casos, a seguir serão feitas conclusões a
respeito do trabalho, e logo em seguida algumas contribuições, e idéias para trabalhos futuros.
5.1.11 Considerações Finais
Neste capítulo foram apresentados os cenários e os estudos de casos realizados durante este
trabalho, juntamente com seus resultados. Além de apresentar ao final, uma conclusão da análise
dos resultados obtidos com os experimentos executados, a fim de estudar o comportamento dos
métodos de particionamentos escolhidos para a efetuação dos testes. A seguir ao próximo
capítulo, serão feitas conclusões sobre o trabalho, serão citadas algumas experiências obtidas no
decorrer do trabalho e sugestões para trabalhos futuros.
85
6
CONSIDERAÇÕES FINAIS
No decorrer do trabalho, foram introduzidos diversos conceitos, como sobre SGBDR e sua
arquitetura, sobre particionamento de tabelas, sobre os vários métodos de particionamento
existentes no Oracle 11g e sobre a tabela de planos. E para o desenvolvimento do projeto, foram
escolhidos dois métodos de particionamento distintos, para que fossem submetidos a testes, dos
quais seriam demonstrados os resultados de desempenho obtidos pelos mesmos, sendo eles
testados, sem e com, um nível adicional de otimização.
No desenvolvimento do projeto, foram criados cenários com os métodos de particionamentos
escolhidos, o particionamento por faixa de valores e o particionamento por função Hash, e foram
realizados quatro estudos de casos no total, dos quais dois foram realizados em cada cenário,
sendo que de cada dois estudos de casos, um utilizou um nível adicional de otimização, ou seja,
foi inserido um índice adicional à tabela CUSTOMER criada nos cenários.
Ao serem analisados os resultados obtidos pelos estudos de casos F1 e H1, dos quais não
utilizaram um índice adicional aos cenários, foi constatado que o cenário particionado por função
Hash, o estudo de caso H1, obteve melhores resultados em relação ao cenário particionado por
faixa de valores, o estudo de caso F1, isso ocorreu por conta de que o cenário particionado por
função Hash, efetuou uma distribuição dos valores armazenados em disco mais uniforme,
proporcionando assim tempos de respostas equivalentes dentro da partição acessada, obtendo
dessa forma, resultados mais satisfatórios do que o do cenário particionado por faixa de valores.
Porém quando obtidos os resultados dos estudos de casos F2 e H2, dos quais utilizaram um
índice adicional aos cenários, foi averiguado que o otimizador de consultas baseado em custo
utilizou o índice adicional criado na coluna CITY_NAME da tabela CUSTOMER, de forma que,
os resultados obtidos de ambos os estudos de casos tornaram-se equivalentes, deixando assim,
facultativa a opção do método de particionamento. De modo que, fica claro que a utilização de
índices em tabelas particionadas é um assunto a ser levado em conta, e que se bem empregado,
pode resolver problemas referentes à performance nas buscas.
Dessa forma, pode-se concluir que este trabalho apresentou um estudo comparativo entre técnicas
de particionamento focando a otimização de busca por dados em tabelas particionadas. Além de
proporcionar um conhecimento mais aprofundado sobre a arquitetura do SGBDR Oracle 11g,
86
particionamento de tabelas e sobre os métodos de particionamento existentes do SGBDR
utilizado.
Porém, com o final deste trabalho, não são encerradas as pesquisas sobre o assunto, e sim abrem
a oportunidade de realização de trabalhos futuros. A seguir, serão citadas as experiências obtidas
durante o trabalho na seção 6.1.1, logo em seguida na seção 6.1.2, serão apontados trabalhos
futuros.
6.1.1
Experiências
A seguir são apresentadas experiências obtidas ao longo do desenvolvimento deste trabalho:
a) Ao executar scripts no SQL Plus, deve-se organizar as instruções antes da execução da
mesma, como por exemplo, ao inserir dados em alguma tabela, não pode haver várias
instruções de inserção na mesma linha, caso isso ocorra, isso acarretará em um erro.
b) Ao criar tabelas particionadas, devem-se primeiramente criar os tablespaces que
armazenarão os dados da tabela.
6.1.2
Trabalhos futuros
As contribuições alcançadas com este trabalho não encerram as pesquisas relacionadas à busca de
uma estrutura de alto desempenho para a busca de dados em tabelas particionadas, mas abrem
oportunidades para alguns trabalhos futuros, como:
a) Realizar um estudo por busca de dados em tabelas particionadas utilizando dicas ao
otimizador de consulta (hints).
b) Fazer um estudo comparativo entre tabelas particionadas e não particionadas.
87
APÊNDICES
APÊNDICE A: Criação de cenários para execução de testes.
Para a criação dos cenários para estudo de casos se faz necessário, primeiramente, a criação dos
tablespaces. Os tablespaces devem ser criados de acordo com o número de partições que serão
utilizadas, pois para cada partição deve existir um tablespace correspondente. Neste trabalho
foram criados no total oito tablespaces, quatro para a tabela particionada por faixa de valores e
quatro para a tabela particionada por função Hash. Um exemplo de criação de um tablespace é
exibido a seguir, na Figura 1.
Figura 1 Exemplo de criação de tablespace
88
Com os tablespaces já criados, a criação das tabelas particionadas deve ser efetuada. Neste
trabalho foram criadas duas tabelas contendo as mesmas estruturas, porém distintas em relação ao
método de particionamento utilizado. Para a criação de cada partição um tablespace é indicado
para armazenar os dados dessa partição. Um exemplo de criação de uma das tabelas
CUSTOMER criado no cenário que foi utilizado o método de particionamento por faixa de
valores pode ser visto na Figura 2.
Figura 2 Exemplo de criação da tabela CUSTOMER utilizada no cenário que foi
particionado por faixa de valores
89
Originalmente, após a instalação do Oracle 11g em um computador que possui um sistema
operacional em português, o formato da data fica sendo o formato brasileiro (DD/MM/YYYY),
porém os dados disponibilizados no site da Wiley possuem o formato americano,
"YYYY/MM/DD", e qualquer formato diferente desse acarretara em erro na inserção dos dados.
Desse modo, se faz necessária à modificação do formato de data do Oracle e esta mudança pode
ser feita através do comando exibido na Figura 3.
Figura 3 Exemplo de alteração no formato da data e hora no Oracle
A inserção dos dados é o próximo passo para a criação dos cenários. Esta operação deve ser
executada normalmente, utilizando as informações dos scripts disponibilizados pela editora.
Os dados disponibilizados possuem pouco mais que 140 mil linhas, então, para aumentar essa
massa de dados consideravelmente é preciso reinseri-la várias vezes na mesma tabela. Para
efetuar esse processo, é necessário desligar ou excluir a CONSTRAINT PRIMARY KEY da
tabela, pois os IDs serão repetidos. Uma forma para executar essa reinserção dos dados é a
utilização do comando exibido na Figura 4.
Figura 4 Exemplo de re-inserção dos dados na tabela CUSTOMER
O comando exibido na Figura 4 pode ser executado quantas vezes for necessário para se obter o
número de registros requerido (o comando pode ser executado dentro de um comando "FOR").
90
Após a re-inserção dos dados, os valores de ID dos registros estarão repetidos, então para que
sejam normalizados é necessária à reorganização dos mesmos através do comando exibido na
Figura 5.
Figura 5 Comando que executa a atualização dos índices após a re-inserção dos dados na
tabela CUSTOMER
Na Figura 5, a fim de esclarecimento, a seqüência previamente criada foi denominada "SEQ".
Com os IDs normalizados pode-se executar a criação, ou reativação, da CONSTRAINT
PRIMARY KEY.
Após a execução das etapas mencionadas anteriormente, será obtido um grande volume de dados
para a realização de testes.
91
REFERÊNCIAS BIBLIOGRÁFICAS
ALAPATI, S. R. Expert Oracle Database 10g Administration. Editora Apress, 2005.
ALAPATI, S. R. Expert Oracle Database 11g Administration. Editora Apress, 2009.
DATE, C.J. Introdução a Sistemas de Banco de Dados. Editora Campus, 8ª Edição, 2004.
Editora Wiley. Disponível em <http://www.wiley.com>. Acesso em 20 de jun. de 2010.
GARTNER. Pesquisa Worldwide Vendor Revenue From RDBMS Software in 2004, Based on
New
License
Sales
(Millions
of
Dollars),
maio
de
2005.
Disponível
em
<http://www.gartner.com/press_releases/asset_127553_11.html>. Acesso em 21 mar. 2010.
GREENWALD, R.; STACKOWIAK, R.; STERN, J. Oracle Essentials. Oracle Database 10g.
Editora O’Reilly, 3ª Edição, 2004.
KYTE, T. Expert Oracle Database Architecture: 9i and 10g Programming Techniques and
Solutions. Editora Apress, 2005.
LONEY, K.; BRYLA, B. Oracle Database 11g: Manual do DBA. Administre um banco de dados
corporativo Oracle escalável e seguro. Editora Bookman, 2009.
LONEY, K. Oracle 10g: O manual do DBA, 2005.
LONEY, K. Oracle Database 11g: The Complete Reference. Editora McGraw-Hill, 2009.
ODPG. Oracle® Database Platform Guide: 10g Release 1 (10.1) for Windows. Disponível em
<http://stanford.edu/dept/itss/docs/oracle/10g/win.101/b10113/architec.htm>. Acesso em: 26 nov.
2009.
92
ODPTG. Oracle® Database Performance Tuning Guide 11g Release 2 (11.2). Disponível em
<http://www.oracle.com/pls/db112/homepage>. Acesso em 22 de maio de 2010.
OPTG. Oracle 9i Database Performance Tuning Guide and Reference Release 2(9.2). Disponível
em <http://www.lc.leidenuniv.nl/awcourse/oracle/index.htm>. Acesso em 22 de maio de 2010.
OVLDB. Oracle® Database VLDB and Partitioning Guide 11g Release 1 (11.1). Disponível em
<http://www.filibeto.org/sun/lib/nonsun/oracle/11.1.0.6.0/B28359_01/server.111/b32024/partitio
n.htm>. Acesso em 23 fev. 2010.
OVLDB2. Oracle® Database VLDB and Partitioning Guide 11g Release 2 (11.2). Disponível
em <http://www.filibeto.org/sun/lib/nonsun/oracle/11.1.0.6.0/B28359_01/server.111/b32024/
partition.htm>. Acesso em 20 mar. 2010.
PRICE, J. Oracle Database 11g SQL. Domine SQL e PL/SQL no Banco de Dados Oracle,
Aborda as versões 11g, 10g, 9i e 8i. Editora Bookman, 2009.
POWELL, G. Oracle High Performance Tuning for 9i and 10g. Editora Digital Press, 2004.
RAMALHO, J. A. Oracle 10g. Editora Thompson Pioneira, 1ª Edição, 2004.
Referência
sobre
PostgreSQL.
Disponível
em
<http://www.postgresql.org/docs/8.4/interactive/intro-whatis.html>. Acesso em: 13 out. 2009.
Referência
sobre
PL/SQL.
Disponível
em
<http://www.oracle.com/technology/tech/pl_sql/index.html>. Acesso em: 13 out. 2009.
SCHRAG, R. Tuning SQL Statements that Use the IN Operator (1998). Disponível em
<http://www.dbspecialists.com/presentations.html#tunesql>. Acesso em 22 de maio de 2010.
SILBERSCHATZ, A. Sistemas de Banco de Dados. Editora Campus, 5ª Edição, 2006.
SILBERSCHATZ, A.; KORTH, H. F.; SUDARSHAN, S. Sistema de Banco de Dados. Editora
Pearson Makron Books, 3ª Edição, 1999.
93
SUMATHI, S. Fundamentals of Relational Database Management Systems. Editora Springer,
2007.