ESTUDO E APLICAÇÃO DE TUNING NO SISTEMA GSB

1

CENTRO UNIVERSITÁRIO DE ARARAQUARA - UNIARA DEPARTAMENTO DE CIÊNCIAS DA ADMINISTRAÇÃO E TECNOLOGIA CURSO: MBA EM GESTÃO DE BANCO DE DADOS(ORACLE)

BRENO CRISTOVÃO ROCHA

ESTUDO E APLICAÇÃO DE TUNING NO SISTEMA GSB

ARARAQUARA 2013

2

BRENO CRISTOVÃO ROCHA

ESTUDO E APLICAÇÃO DE TUNING NO SISTEMA GSB

Trabalho

de

Conclusão

de

Curso

apresentado ao Departamento de Ciências da Administração e Tecnologia, do Centro Universitário de Araraquara-Uniara, como exigência para obtenção do título de Especialista em Gestão de banco de dados Oracle.

ARARAQUARA 2013

3

DECLARAÇÃO

Eu, Breno Cristovão Rocha, RG MG-13.616-958, aluno regularmente matriculado no Curso de MBA em Gestão de Banco de Dados Oracle do Centro Universitário de AraraquaraUNIARA, declaro ser autor do texto apresentado como trabalho de Conclusão de Curso- TCC com o título “Estudo e Aplicação de Tuning no sistema GSB”. Afirmo também ter seguido as normas da ABNT referentes às citações textuais que utilizei e das quais eu não sou autor, creditando, assim, a autoria a seus verdadeiros autores(Lei n.9.610, 19/02/1998). Através desta declaração dou ciência de minha responsabilidade sobre o texto apresentado e assumo qualquer responsabilidade por eventuais problemas legais, no tocante aos direitos autorais e originalidade do texto.

Araraquara, __ de ______________de 2013.

________________________________

4

FOLHA DE APROVAÇÃO

O presente trabalho de Conclusão de Curso foi examinado nesta data pela Banca Examinadora composta pelos seguintes membros:

Orientador (a): Prof. Dr. (Marcus Rogerio Oliveira) 1º Examinador (a): Prof. Dr. (

)

2º Examinador (a): Prof. Dr. (

)

Nota: _________

Data: ___/___/___

5

Dedico este trabalho ao meu pai Carlos Humberto dos Reis Rocha (in memorian)

6

AGRADECIMENTOS

Dedico especiais agradecimentos aos meus pais, Carlos Humberto dos Reis Rocha e Cremilda Maria Cristovão Rocha, por estarem sempre ao meu lado.

Agradeço a minha namorada e companheira inseparável, Fernanda Ribeiro Souto, pelo apoio em minhas decisões e grandes incentivos.

A todos os professores da Uniara, que durante este período incentivaram na busca do conhecimento.

A todas as pessoas que contribuíram diretamente ou indiretamente para a realização deste trabalho e para a conclusão do curso.

7

RESUMO

Este trabalho tem como objetivo definir o conceito de banco de dados Oracle, Tuning e também apresentar técnicas de Tuning como uma proposta para auxiliar na descoberta de soluções de problemas com desempenho de consultas e otimizar a taxa de transferência de dados no banco de dados Oracle. É apresentado as principais formas de se realizar o tuning, tais como aumento de memória, índices e otimização de query. Por fim, é possível se notar que a aplicação do Tuning em um banco de dados é de grande importância para melhor desenvoltura e performance do banco.

Palavra chave: Oracle, tuning, banco de dados

8

ABSTRACT This work aims to define the concept of Oracle database, and also present Tuning Tuning techniques as a proposal to assist in finding solutions to problems with query performance and optimize the throughput of data in Oracle database. It presented the main ways to perform tuning, such as increased memory, indexes and query optimization. Finally, it can be noted that the application of Tuning in a database is very important for improved performance and ease of the seat.

Palavra chave: Oracle, tuning, database

9

LISTA DE FIGURAS

Figura 1 - Arquitetura da Instância Oracle. ........................................................................................... 17 Figura 2- Exemplo de um Banco de Dados Modelo E-R. ....................................................................... 18 Figura 3- Exemplo de um Banco de Dados Modelo Relacional. ............................................................ 19 Figura 4 – Status Banco de dados ......................................................................................................... 25 Figura 5 – Detalhe dos Serviços ............................................................................................................ 26 Figura 6 – Objetos ................................................................................................................................. 27 Figura 7 – Eventos ................................................................................................................................ 27 Figura 8 – Final ..................................................................................................................................... 34

10

LISTA DE SIGLAS

DBA

Database administrator

SGBD

Sistema de Gerenciamento de Banco de Dados

SGA

System Global Area

PMON

Process MONitor

MON

System MONitor

DBWR

DataBase WRiter

LGWR

LOG WRiter

CKPT

Oracle Checkpoint Process

XML

Extensible Markup Language

SQL

Structured Query Language

ANSI

American National Standards Institute

ISO

International Organization for Standardization

DDL

Data definition Language

DML

Data Manipulation Language

HD

Disco Rigído

RAM

Random Access Memory

11

SUMÁRIO 1

INTRODUÇÃO ............................................................................................................... 12 1.2 OBJETIVOS ....................................................................................................................... 13 1.2.1 Objetivos Gerais ....................................................................................................... 13 1.2.2 Objetivos Específicos ............................................................................................... 13 1.3 JUSTIFICATIVA ................................................................................................................. 13 1.4 METODOLOGIA................................................................................................................. 14

2

REFERENCIAL TEÓRICO .......................................................................................... 15 2.1 BANCO DE DADOS ............................................................................................................ 15 2.2 ESTRUTURA DO ORACLE .................................................................................................. 16 2.3 INSTÂNCIA ORACLE ......................................................................................................... 16 2.4 MODELO DE DADOS ......................................................................................................... 18 2.5 SQL ( STRUCTURED QUERY LANGUAGE ) ........................................................................ 20 2.6 TUNING ............................................................................................................................ 21 2.7 SOFTWARE PARA AGRONEGÓCIO ..................................................................................... 21 2.8 A SOLUÇÃO “GSB SOFTWARE”........................................................................................ 22

3

ESTUDO DE CASO: TUNING DE BANCO DE DADOS NO SOFTWARE GSB .. 24 3.1 AMBIENTE ........................................................................................................................ 24 3.2 SIMULAÇÃO NO SOFTWARE GSB ..................................................................................... 24 3.3 ESTATÍSTICAS INICIAIS ..................................................................................................... 25 3.4 AJUSTES INICIAIS ............................................................................................................. 27 3.5 AUMENTO DE MEMÓRIA .................................................................................................. 28 3.6 ÍNDICES ............................................................................................................................ 29 3.7 OTIMIZANDO QUERY........................................................................................................ 30

4

CONCLUSÃO ................................................................................................................. 36

12

1 INTRODUÇÃO

É interessante notar que, dia após dia, o mundo da tecnologia da informação está mais competitivo no que se diz respeito a novas tecnologias e novos recursos. No meio de tanta Tecnologia da Informação, se faz necessário que as empresas e instituições façam mais investimentos em tecnologia para que as informações possam ser geradas com rapidez e possam auxiliar com total segurança a tomada de decisões. Para muitos, falar em Tecnologia de Informação é sinônimo para se dizer da manipulação de hardwares, softwares, redes, internet, entre outros. Entretanto, esta definição vai além desse pensamento. Dessa forma, as organizações que desejam sucesso e destaque no mercado de alta e grande concorrência não devem, de maneira alguma, se abster da Tecnologia da Informação para melhores resultados diante deste mundo globalizado em que as empresas se encontram. E para sua manutenção no mercado competitivo em que se encontram, as empresas, além da tarefa de se implantar um bom software, que atenda a todas as necessidades, devem se preocupar com que forma todas as informações estão sendo manipuladas e armazenadas no banco de dados de sua aplicação principal. É fundamental que todos os dados armazenados e consultados em um banco de dados estejam disponíveis de forma confiável e rápida. Diante do cenário empresarial citado, quando se fala em agilidade em banco de dados logo se pode associar a agilidade do mesmo ao Tuning. Referida expressão nada mais é do que se aplicar idéias para otimizar o desempenho na recuperação ou atualização de dados. A Oracle tem produzido metodologias baseadas em desempenho para simplificar algumas atividades dos profissionais da Tecnologia da Informação e melhorar consideravelmente o desempenho dos sistemas. Existem alguns fatores dentro de um cenário organizacional que são consideráveis para se detectar problemas de desempenho em um banco de dados. Muitos profissionais cometem erros ao tentar solucionar problemas de desempenho em um banco de dados adicionando aos seus servidores processadores de alta potência, memórias e discos de armazenamento de dados. Em algumas situações esta ação pode realmente proporcionar uma melhoria de desempenho imediato. No entanto, qualquer desempenho obtido por adição de

13

aumento de hardware deve ser considerado um alívio em curto prazo, pois se as taxas de demanda e carga sobre a aplicação continuarem a crescer consideravelmente, o profissional logo irá se deparar com o mesmo problema novamente. Portanto, por vezes não é boa ideia adicionar recursos de hardware ao servidor de banco de dados. Diante destas situações, este trabalho tem como objetivo apresentar algumas dicas e técnicas para se realizar o Tuning da melhor forma possível em um banco de dados Oracle. 1.2 Objetivos Esta seção apresenta os objetivos deste trabalho.

1.2.1 Objetivos Gerais

Definir o conceito de banco de dados Oracle, Tuning e também apresentar técnicas de Tuning como uma proposta para auxiliar na descoberta de soluções de problemas com desempenho de consultas e otimizar a taxa de transferência de dados no banco de dados Oracle.

1.2.2 Objetivos Específicos

O objetivo específico deste trabalho é apresentar soluções de Tuning em instruções SQL para auxiliar desenvolvedores e DBA’s que participam do desenvolvimento e implantação de software a aperfeiçoar o desempenho dos sistemas, principalmente os sistemas que apresentam algum tipo de instabilidade e necessitam de uma boa performance para viabilizar e garantir o uso da aplicação.

1.3 Justificativa Como o crescimento do uso de sistemas de informações é cada vez maior e mais complexo nas organizações, a necessidade e demanda por sistemas mais ágeis e com alta

14

performance é de fundamental importância. Dessa forma, para que um sistema de informação seja rápido e eficaz se faz necessário que os profissionais da área de Tecnologia da Informação tenham conhecimentos básicos e avançados sobre Tuning em diversas áreas de atuação, justificando a proposta de realizar Tuning em banco de dados Oracle, a fim de um melhor desempenho para todos os usuários envolvidos de uma aplicação.

1.4 Metodologia A metodologia deste trabalho está baseada em um método de pesquisa sobre o assunto Tuning no banco de dados Oracle. Os tipos de materiais usados para pesquisa são livros, periódicos, revistas e documentação oficial da Oracle Corporation.

15

2 REFERENCIAL TEÓRICO

Nesta seção são apresentados os conceitos que serviram de base para o desenvolvimento deste trabalho.

2.1 Banco de Dados Um sistema de banco de dados é definido como uma coleção de dados que são inter-relacionados e um conjunto de programas utilizados pelos usuários, permitindo que estes acessem e modifiquem esses dados. Tem por finalidade oferecer aos usuários uma visão abstrata, ocultando detalhes sem muita importância, como, por exemplo, a forma que é armazenada e mantida. Essa abstração de dados pode ser classificada em três níveis distintos: ● Nível físico: É o tipo de nível mais baixo da abstração de dados. Descreve como os dados são verdadeiramente armazenados, detalhando, em um baixo nível, estruturas de dados complexos. ● Nível lógico: É o tipo de nível mediano da abstração de dados. Descreve os dados que estão armazenados no banco de dados, bem como a relação existente entre eles. ● Nível de view: É o tipo de nível mais alto da abstração de dados. Descreve parte do conteúdo de banco de dados. Simplifica a interação com o sistema, que pode oferecer várias visões para o mesmo banco de dados.

Com o tempo os bancos de dados mudam à medida que informações são incluídas ou excluídas. O projeto geral do banco de dados é denominado “esquema de banco de dados”, onde é, raramente, modificado. O conjunto de informações que nele é armazenado é denominado de “instância do banco de dados”. Os valores das variáveis utilizadas em um programa, por certo tempo, corresponderão a uma instância do banco de dados. Vários são os esquemas que os sistemas de banco de dados podem possuir. O esquema físico corresponde ao nível físico do banco de dados, enquanto o esquema lógico corresponde respectivamente ao nível lógico do banco de dados. Quando um banco de dados

16

possui diversos esquemas no nível de view, estes recebem o nome de sub-esquemas, descrevendo as diferentes visões que o banco de dados possui. Os bancos de dados são amplamente utilizados em diversos segmentos: bancos, universidades, instituições financeiras, telecomunicação, linhas aéreas, indústria, recursos humanos, entre outros. Surgiram com o intuito de abater algumas dificuldades vigentes nas empresas como: redundância e inconsistência de dados, dificuldade de acesso a dados, isolamento de dados, problemas de integridade, atomicidade e segurança, anomalias de acesso concorrente, entre outros (SILBERSCHATZ, 2006).

2.2 Estrutura do Oracle O SGBD (Sistema de Gerenciamento de Banco de Dados) Oracle é um banco de dados composto por diversos mecanismos de execução. A estrutura e arquitetura do Oracle são compostas pela união da instância com os arquivos de dados e, dependendo da arquitetura do hardware, podem apresentar características que permitem múltiplos computadores compartilhar os mesmos dados, softwares e periféricos, tirando, assim, um maior proveito de toda a estrutura do banco de dados Oracle. Neste trabalho será apresentado um estudo sobre uma melhor performance do banco de dados Oracle 10g.

2.3 Instância Oracle

A instância do banco de dados Oracle é caracterizada por um conjunto de estruturas de memória e processos que acessam um arquivo de banco de dados. A cada inicialização de uma instância no banco de dados Oracle, uma área do SGA ( System Global Area ) é alocada em união com os processos de segundo plano como Datafiles ( Arquivos de Dados), Redo (Arquivos de Registro), arquivos de controle e arquivos de parâmetros são inicializados e o banco de dados por sua vez é montado. Durante o início de uma instância no banco de dados, faz-se uma verificação de integridade dos arquivos do sistema para em seguida a SGA ser disponibilizada.

17

A figura 1 demonstra de forma mais clara o processo de montagem da instância do Oracle.

Figura 1 - Arquitetura da Instância Oracle. Fonte: (CARMO, 2013).

Um banco de dados pode ter mais de uma instância, e para executar uma ou mais instância de um banco de dados Oracle, é necessário realizar a configuração de um arquivo de controle conhecido como LISTENER (Ouvidor). É através do Listener que se são controladas as conexões das instâncias. Os processos de segundo plano citados anteriormente, também conhecidos como Background (processos de segundo plano ), executam diversas funções dentro do banco de dados Oracle, mas podem variar as suas execuções de acordo com a configuração do banco de dados. Estes processos são gerenciados pelo próprio banco de dados exigindo uma atenção e configuração por porte do profissional que esteja administrando o banco de dados Oracle. Este profissional é mais conhecido no mundo da Tecnologia da Informação como DBA. Com o objetivo de listar os principais processos de segundo plano, abaixo segue a lista:

18

 PMON: Processo responsável em recuperar falhas de usuário (caso necessário), onde só é executado caso uma falha no processo de um usuário venha a acontecer.  SMON: É executado quando o banco de dados é iniciado, executando a recuperação de uma instância (se necessário) que falhou e está sendo reinicializada.  DBWR: É responsável pelo gerenciamento de blocos modificados do cache de buffer do banco de dados a serem armazenados nos Datafiles.  LGWR: Este processo é responsável por gerenciar o conteúdo do redo log buffer a ser armazenado no arquivo de redo on-line, onde o armazenamento é efetuado de forma sequencial respeitando a estrutura dos arquivos de redo.  CKPT: Ocorre quando é executada a validação de uma operação no banco de dados através do comando COMMIT.  ARCH: Responsável por copiar os arquivos de registro de redo on-line para armazenálos em arquivos quando eles estão cheios ou quando ocorre uma troca de registro. 2.4 Modelo de Dados O modelo de dados é um conjunto de ferramentas que descreve os dados do banco de dados, bem como a relação, semântica e restrição de consistência desses dados. Vários são os modelos de dados, podendo ser classificados em quatro categorias distintas: ● Modelo entidade/relacionamento (E-R): Esse modelo comporta um conjunto de objetos básicos (entidades), e suas relações, baseados sempre na percepção de um mundo real. A entidade poderá ser qualquer coisa ou objeto do mundo real, distinguível dos outros objetos. As relações são simplesmente a associação entre essas várias entidades. A Figura 2 mostra um exemplo de banco de dados E-R.

Figura 2- Exemplo de um Banco de Dados Modelo E-R. Fonte: (SILBERSCHATZ, 2006, p. 11).

19

● Modelo relacional: Esse modelo utiliza de conjuntos de tabelas para representar seus dados, bem como a relação entre eles. As tabelas possuem diversas colunas onde cada coluna recebe um único nome. Tal modelo é baseado em registros (representado pelas colunas), devido aos registros serem estruturados em formato fixo de vários tipos, cada um com seus campos e atributos. É atualmente o modelo mais utilizado entre os bancos de dados. A Figura 3 mostra um exemplo de banco de dados relacional.

Figura 3- Exemplo de um Banco de Dados Modelo Relacional. Fonte: (SILBERSCHATZ, 2006, p. 8).

● Modelo de dados baseado em objetos: Esse modelo traz combinações dos recursos utilizados no modelo orientado a objetos e relacional. Herança, identidade do objeto e

20

encapsulamento são recursos utilizados nessa modelagem de dados. Pode, assim, ser considerado como uma extensão do modelo E-R. ● Modelo de dados semi-estruturado: Esse modelo de dados permite que item de dados individuais do mesmo tipo possam comportar conjuntos com diferentes atributos. Isso o torna o oposto do modelo de dados baseado em objetos, onde os itens precisam ter o mesmo conjunto de atributos. A XML (Extensible Markup Language) é amplamente utilizada neste modelo, a fim de representar dados semi-estruturados (SILBERSCHATZ,2006)

2.5 SQL ( Structured Query Language )

Em 1970, a IBM desenvolveu, como parte do projeto R, a versão original da SQL, chamada originalmente de Sequel. Ganhando evolução, essa linguagem passou a se chamar oficialmente SQL (Structured Query Language). Tornou-se, desde então, linguagem padrão dos bancos de dados. O ANSI (American National Standards Institute) e a ISO (International Organization for Standardization) publicaram, em 1986, um padrão SQL, chamado SQL-86. Mais tarde, em 1989, o ANSI lançou uma versão estendida da linguagem: a SQL-89. Logo, a versão que se tornou padrão foi a SQL-92, depois seguida da SQL-1999. SQL-2003 incluindo suporte básico ao padrão XML. E por fim a SQL-2006 possuindo a interação entre SQL e XML . A linguagem SQL é composta por várias partes, tais como: Linguagem de Definição de Dados (DDL, fornecendo comandos para definir, excluir e modificar esquemas), Linguagem de Manipulação de Dados Interativa (DML, além de pertencer os comandos da DDL, inclui também linguagem baseada na álgebra relacional e cálculo relacional de tupla), Integridade (restrições de acessos aos dados armazenados no banco de dados, onde a violação das mesmas são proibidas), Definição de view, Controle de Transações, SQL Embutida e SQL Dinâmica (definindo como as instruções serão incorporadas pelas linguagens de programação), Autorização (especificando os direitos de acessos referentes as relações e views) (SILBERSCHATZ, 2006).

21

2.6 Tuning O Tuning tem como objetivo ajustar o SGBD para um melhor desempenho do banco de dados. Para se realizar o Tuning, é preciso ter conhecimento sobre banco de dados, desenvolvimento de aplicação, sistema operacional e hardware. Um requisito que chama atenção é o hardware, pois é através dele que é possível abstrair informações de utilização de processador, memória utilizada e paginação. Ao realizar um Tuning é extremamente importante identificar o problema que está causando o gargalo e intervir sobre a causa em busca em um processo eficaz. Durante a execução do Tuning, não é possível seguir um manual de instruções e começar a aplicar a técnica do Tuning. O que pode ser feito é um levantamento da atual situação que se encontra hardware, arquitetura do sistema, rede e banco de dados e traçar um plano de execução. O ideal ao se desenvolver uma aplicação, é que, em conjunto com o processo de implementação da aplicação, também se crie uma metodologia para se ajustar todo o cenário a uma aplicação rápida e consistente. Contudo, mesmo projetando boas técnicas de programação e performance do banco de dados, todo este planejamento pode ficar ultrapassado, se fazendo necessário que o DBA sempre esteja fazendo manutenções em seu banco de dados.

2.7 Software para Agronegócio As grandes empresas que pertencem ao segmento do agronegócio, em geral, possuem um software para controlar e gerenciar todas as suas informações. Nas empresas agrícolas pequenas ou familiares frequentemente as soluções são manuais ou não existem formalmente e o tratamento das informações é feito sem qualquer tipo de registro. Com o crescimento do agronegócio surge a necessidade de práticas mais complexas de manuseio e produção, levando à necessidade cada vez maior de informações. Os sistemas manuais e os que são montados sobre planilhas se tornam insuficientes para o manuseio e análise de forma rápida no momento de uma tomada de decisão. Surge, então, a necessidade de investir em sistemas informatizados, para, com base nas informações, encontrar as melhores informações e resultados. Esses softwares visam

22

atender os níveis de decisão de uma empresa no agronegócio e muitas vezes utilizam a sua base de dados gerada pelo software, que através de recursos computacionais permite que os seus gestores façam uma análise mais segura. Normalmente, o software pode definir os rumos de uma empresa agrícola, pois é através dele que se pode ter noção do custo total de um talhão de determinada cultura.

2.8 A solução “GSB Software” O sistema de informações para empresas agrícolas deve ser adaptado às muitas variáveis do agronegócio. Surge como solução para o Agronegócio o Sistema GSB Software, especializado neste segmento; trabalhando com analistas, implantadores e programadores com conhecimento prático no setor, acompanhamento, suporte constante e apoio na analise dos resultados finais. Um software planejado, elaborado e desenvolvido de acordo com as necessidades e realidade do empresário rural. E, ainda de acordo com os conceitos de administração. Permite integração com outras soluções administrativas desenvolvidas por demais empresas. Dispõe de sistema de segurança dos dados, através de controle de acesso por usuário protegido por senhas, onde o gestor da solução pode controlar e restringir informações de acordo com as suas regras de acesso à informação. O GSB Software oferece soluções nos seguintes processos:  Gestão de Compras  Gestão de Produção / Armazenagem / Beneficiamento  Gestão de Vendas  Gestão do Financeiro  Gestão Fiscal  Gestão Contábil  Gestão de custos no agronegócio em geral  Gestão de Fábricas de rações e Adubos  Gestão de Algodoeira

23

 Gestão de Fruticultura  Gestão de Confinamentos

24

3 ESTUDO DE CASO: TUNING DE BANCO DE DADOS NO SOFTWARE GSB Este capítulo apresenta a aplicação das metodologias e técnicas de tuning de banco de dados no software GSB.

3.1 Ambiente O sistema GSB se encontra em uso há 10 (dez) anos e a versão testada foi a 4.7. Atualmente existem aproximadamente 50 (cinquenta) empresas agrícolas utilizando o sistema, sendo, assim, formada uma base de dados grande e diversificada, que possibilita identificar consultas SQL que consomem mais recursos do banco de dados Oracle e também os gargalos. O hardware e sistema operacional utilizado foi: Processador = Intel Core i3-3220 3.30 GHz Memória = 4 GB HD = 500 GB Sistema Operacional = Windows Server 2008

3.2 Simulação no Software GSB

No decorrer deste projeto, a forma de realizar testes para simular o dia a dia das propriedades rurais que utilizam o software GSB serão todas realizadas por uma base de dados teste, usada para realizar vários tipos de testes e cargas dentro do ambiente de desenvolvimento do software. Dessa forma, ao realizar os testes nesta base de dados, serão realizados testes de inserção de dados e também consultas através de relatórios gerencias que o software GSB possui. A cada etapa do Tuning será inserida a mesma carga de dados e consultas e, em seguida, buscadas informações de desempenho do banco de dados, propiciando a análise da eficiência das metodologias e técnicas de Tuning.

25

3.3 Estatísticas Iniciais Para análise inicial foram realizados testes de estresse no banco de dados utilizando várias inserções de dados e consultas de relatórios. A primeira análise serviu como base de comparação paras as análises nas etapas do Tuning e também para uma análise de comparação dos resultados obtidos. Abaixo esta a lista de comandos SQL utilizados:

Insert e update : Nas tabela de cadastro/fornecedor, notas fiscais de entrada e saída, apontamento de mão de obra.

Selects simples: select buscando todos os campos das tabelas que sofreram inserções e atualizações.

Selects complexos: Com relatórios de custos, movimentação de estoque, apontamento de mão de obra e utilização de implementos agrícolas.

Após a realização de vários testes descritos acima de forma randômica, obteve-se alguns resultados apresentados na figura 4.

Figura 4 – Status Banco de dados Fonte: Software MyOra

26

As figuras 5,6 e 7abaixo demonstram a reação do Oracle à carga de dados que foi realizada no banco de dados. Pode-se observar medições como entrada e saída física, entrada e saída lógica, tempo de CPU e tempo decorrido. Através das figuras é constatado que grande parte do tempo de espera para processar as transações se deve ao uso de CPU e entrada e saída do sistema. Após a observação, é possível detectar alguns pontos de balança de carga de entrada e saída de datafile. No decorrer dos próximos tópicos serão abordadas algumas formas de trabalhar estes problemas.

Figura 5 – Detalhe dos Serviços Fonte: Software MyOra

27

Figura 6 – Objetos Fonte: Software MyOra

Figura 7 – Eventos Fonte: Software MyOra

3.4 Ajustes Iniciais O software GSB está sendo executado sobre o Windows Server 2007. Uma consideração de Tuning quando se usa este sistema operacional é sobre a quantidade disponível de memória para executar todos os processos do Oracle e todos os processos de frequentes do Windows. Como o sistema operacional utiliza uma boa quantidade de memória virtual do servidor, fazendo com que o Oracle e processos de usuários utilizem a memória

28

disponível, quando se faz uso de toda a memória disponível pelo servidor, o Windows começa a fazer uso de paginação e o desempenho do Oracle pode se comprometer. Para que isso não ocorra, é possível tomar algumas medidas para que isso não atrapalhe o desempenho do Oracle e não ocorra paginação pelo servidor. Uma das medidas é configurar o Windows para que ele dê preferência ao uso da memória para aplicações de rede. Outra configuração válida é priorizar o protocolo de rede mais requisitado. Os ajustes iniciais foram realizados, porém não houve mudanças significativas no desempenho do banco de dados em geral.

3.5 Aumento de Memória

Um dos principais motivos para um bom desempenho de SGBDs é o uso da memória RAM para fazer cache de dados e por ter a memória RAM um melhor desempenho. No entanto, devido a utilização de tantos recursos do sistema operacional, quantidade de dados armazenados e usuários fazendo requisições dos dados, existe uma divisão no uso dos recursos de memória RAM, a memória mais importante que se pode efetuar Tuning de sistema operacional. Reservando a quantidade ideal para a SGA/PGA se obtém uma boa relação entre processos do servidor e entradas e saídas de requisições. Se a SGA não for suficiente, os ciclos do servidor ficam ociosos esperando que as entradas e saídas estejam completas. Portanto, deve-se trabalhar em cima do Tuning para que isso não ocorra. A PGA é a memória alocada quando o usuário se conecta ao banco de dados e começa a enviar instruções SQL, ordenação e agrupações. O buffer pool é uma espécie de memória alocada para gerenciamento do banco de dados. Quanto maior a quantidade de memória RAM, o servidor pode ter tempo de resposta mais rápido e eficaz. Faltando memória RAM para os processos SGA e PGA o Oracle faz uso da memória virtual, ocasionando um desempenho baixo. As medidas tomadas para que isso não ocorresse foram:  Aumentar o shared pool ALTER SYSTEM SET SHARED_POOL_SIZE = 250 M SCOPE=SPFILE;  Aumentar o DB cache buffer pool ALTER SYSTEM SET DB_CACHE_SIZE = 250 M SCOPE=SPFILE;

29

 Aumentar o PGA ALTER SYSTEM SET PGA_AGGREGATE_TARGET = 200M SCOPE=SPFILE;

Os novos valores serão atualizados assim que a instância do Oracle for reiniciada.

3.6 Índices

O índice é uma estrutura opcional criada para ajudar no acesso mais rápido aos dados. Os índices são capazes de diminuir significamente o acesso ao disco, que automaticamente obtém melhores resultados no desempenho do banco de dados. O Oracle possui vários tipos de índices e opções de armazenamento que proporcionam benefícios de desempenho e gerenciamento. Abaixo segue alguns tipos de índices.  Índices B-tree – a estrutura de índice default da Oracle  Índice concatenado  Índice agrupado  Índices Bitmap  Índice sobre tabelas - index-organized table (IOT)

Um índice deve ser criado sobre uma tabela onde a consulta em geral retorna de dois a quatro por cento dos dados da tabela. O índice do tipo B-tree pode melhorar o tempo de resposta quando se seleciona dados onde uma chave tem valor especifico e resultados estão entre uma faixa de valor como, por exemplo, intervalo de datas. Um índice concatenado é um tipo de índice criado sobre múltiplas colunas em uma tabela. As colunas neste tipo de índice aparecem em qualquer ordem e a partir desta ordem é definido se o índice concatenado pode aparecer em qualquer ordem definindo se o índice completo é usado ou não na consulta. A vantagem de uma chave concatenada é que

30

frequentemente ela é mais seletível que uma chave única, melhorando a performance do índice. O índice agrupado tem como mecanismo armazenar linhas armazenadas de uma ou mais tabela no mesmo segmento. Na prática, índices agrupados são bastante limitados e apenas devem ser usados quando as tabelas são sempre referenciadas juntas. Os índices Bitmap criam um bitmap para cada valor único da coluna em questão. Cada bitmap contém um único bitmap ( 0 ou 1 ) para cada linha da tabela. “1” indica que a linha tem o valor especificado pelo bitmap e “0” indica que não tem. Se usado apropriadamente é bastante compacto. A tabela de TTICKET_GASTO é uma das principais tabelas do sistema GBS, sendo ela que destina os produtos na movimentação de estoque. Foi criado o índice do tipo BITMAP nas colunas COD_PROP e COD_FIL devido a estes campos estarem sempre se repetindo.

CREATE BITMAP INDEX CUSTO.tick_gasto ON CUSTO.TTICKET_GASTO (COD_PROP, COD_FIL) NOLOGGING TABLESPACE TSCUSTO STORAGE ( BUFFER_POOL DEFAULT ) NOPARALLEL;

3.7 Otimizando Query

A estruturação de uma Query pode ser escrita de diversas maneiras diferentes com a mesma funcionalidade. Já a performance pode variar de acordo com o tempo de execução da query. A partir desta variação de tempo entre uma query e outra, sempre que possível se torna necessário escrever a query que gaste o menor tempo. Quando múltiplas tabelas são acessadas em uma query SQL, elas podem ser combinadas em qualquer ordem e retornar o mesmo resultado. Através de uma junção (join) o tempo de uma query pode ter variações significativas.

31

A metodologia de Tuning envolve em identificar quais as query que consomem mais recursos e otimizá-las. Uma das query selecionadas para otimizar o código SQL está descrita abaixo.: select b.sigla_local, c.cod_prod, c.safra, c.lote, nvl(c.estoque,0) estoque, c.valor, c.dt_valid, d.peso_unit, e.desc_nome, f.desc_varied, g.desc_pen||' '||J.desc_categ desc_pen, h.sigla_grupo, h.nome_grupo, i.ab from tprop a, tlocal_est b, tprod_est c, tprod d,tnm_prod e, tvaried f, tpen g, tgrupo h, tunid i,tprod_categ J where c.cod_prop = a.cod_prop and b.cod_local = c.cod_local and c.cod_prod = d.cod_prod and d.cod_nome_prod = e.cod_nome_prod and d.cod_varied = f.cod_varied (+) and d.cod_pen = g.cod_pen (+) and d.cod_categ = J.cod_categ(+) and d.cod_grupo = h.cod_grupo (+) and d.tipo

= 2 and

d.cod_unid = i.cod_unid and c.cod_prop like :xcod_prop and c.cod_fil like :xcod_fil and

32

c.cod_local like :xcod_local and nvl(d.cod_grupo,0) like :xcod_grupo and nvl(d.cod_grupo1,0) like :xcod_grupo1 and d.cod_nome_prod like :xcod_nome_prod and nvl(d.cod_varied,0) like :xcod_varied and nvl(d.cod_categ,0) like :xcod_categ and nvl(d.cod_pen,0) like :xcod_pen and d.peso_unit like :xpeso and c.safra like :xsafra and c.lote like :xlote and nvl(d.cod_tec,0) like :xcod_tec and nvl(d.registro,0) like :xregistro and c.status_lote like :xstatus_lote and nvl(b.cod_nr,0) like :xcod_nr and nvl(d.cod_nr,0) like :xcod_fab order

by

h.sigla_grupo,e.desc_nome,f.desc_varied,J.desc_categ||'

'||g.desc_pen,d.peso_unit,b.sigla_local

Observe que a query descrita acima está escrita no padrão Oracle, e a query otimizada está escrita no padrão ANSI abaixo:

select b.sigla_local, c.cod_prod, c.safra, c.lote, nvl(c.estoque,0) estoque, c.valor, c.dt_valid, d.peso_unit, e.desc_nome, f.desc_varied, g.desc_pen||' '||J.desc_categ desc_pen,

33

h.sigla_grupo, h.nome_grupo, i.ab from tprop a inner join tprod_est c on c.cod_prop = a.cod_prop inner join tlocal_est b on b.cod_local = c.cod_local inner join tprod d on c.cod_prod = d.cod_prod inner join tnm_prod e on d.cod_nome_prod = e.cod_nome_prod left outer join tvaried f on d.cod_varied = f.cod_varied left outer join tpen g on d.cod_pen = g.cod_pen left outer join tprod_categ j on d.cod_categ = j.cod_categ left outer join tgrupo h on d.cod_grupo = h.cod_grupo inner join tunid i on d.cod_unid = i.cod_unid where d.tipo = 2 and c.cod_prop like :xcod_prop and c.cod_fil like :xcod_fil and c.cod_local like :xcod_local and nvl(d.cod_grupo,0) like :xcod_grupo and nvl(d.cod_grupo1,0) like :xcod_grupo1 and d.cod_nome_prod like :xcod_nome_prod and nvl(d.cod_varied,0) like :xcod_varied and nvl(d.cod_categ,0) like :xcod_categ and nvl(d.cod_pen,0) like :xcod_pen and

34

d.peso_unit like :xpeso and c.safra like :xsafra and c.lote like :xlote and nvl(d.cod_tec,0) like :xcod_tec and nvl(d.registro,0) like :xregistro and c.status_lote like :xstatus_lote and nvl(b.cod_nr,0) like :xcod_nr and nvl(d.cod_nr,0) like :xcod_fab order

by

h.sigla_grupo,e.desc_nome,f.desc_varied,J.desc_categ||'

'||g.desc_pen,d.peso_unit,b.sigla_local

Com a utilização do padrão ANSI já se ganha performance na query consideravelmente (PRADO,2013). No sistema GSB foram adequadas diversas query para que o sistema ganhe performance em suas consultas de dados. Existem várias formas de se ganhar performance em uma query. A forma apresentada acima é apenas uma das formas existentes. Pode-se ganhar performance quando tem-se colunas somatórias e são realizados sub-selects ao invés de left joins, além de outras formas como citado acima. Após as alterações citadas anteriormente, foi realizada uma nova pesquisa de desempenho no banco de dados Oracle e o resultado obtido está listado abaixo:

Figura 8 – Final Fonte: Software MyOra

35

Após o aumento das variáveis se obteve uma melhoria nos processos de entrada e saída e também nos processos buffer wait e redo wait.

36

4

CONCLUSÃO

Para a realização e execução deste trabalho foi realizado um estudo teórico sobre Tuning de banco de dados, com o objetivo de melhorar o desempenho do banco de dados Oracle no sistema GSB. Diante o desenvolvimento deste trabalho ficou claro que o Tuning de banco de dados é fundamental para o desenvolvimento e manutenção de qualquer tipo de sistema. Para obtenção um resultado positivo quanto a performance de banco de dados, é desejável que já se estabeleça critérios de desempenho na fase inicial do desenvolvimento do sistema, fixando boas práticas de programação de modo que também se evite retrabalhos por causa de performance de banco de dados. Devido a algumas restrições de informações do sistema GSB, não foi possível disponibilizar código de query e demais informações, por se tratarem de informações confidenciais e de acesso somente a programadores e DBA’s da empresa GSB Software. Como este trabalho tem como proposta melhorar o desempenho do banco de dados através do Tuning, conclui-se que foi obtido um resultado positivo, pois vários relatórios e acessos a informações tiveram um desempenho e performance satisfatórios. Para uma possível continuação deste trabalho fica ainda em aberto a sugestão de se utilizar um método de balanço de carga nos dados e aumento de memória física.

37

REFERÊNCIAS SIBERSCHATZ, Abraham. Sistema de banco de dados. 5. ed. Rio de Janeiro: Elsevier, 2006. 781 p.p&b.

CARMO,William. Arquitetura do Banco de Dados. Disponível em: Acesso em: 07 maio,2013. PRADO,Fabio. SQL Padrão ANSI X Padrão Oracle. Qual é mais rápido?. Disponível em: < http://www.fabioprado.net/2012/05/sql-padrao-ansi-x-padrao-oracle.html/> Acesso em: 12 maio,2013.

DBA-ORACLE. Oracle SQL tuning - Tune individual SQL statements. Disponível em: Acesso em:14 jun. 2013. Tuning Guide. Performance Tuning Overview . Disponível em: Acesso em:09 jun. 2013.