Banco de dados um estudo no oracle 10g

Banco de dados um estudo no oracle 10g

(Parte 1 de 8)

DE BANCO DE DADOS: UM ESTUDO NO ORACLE 10G TRABALHO DE GRADUAÇÃO

Universidade Federal de Pernambuco Graduação em Ciência da Computação Centro de Informática

Aluno: Marcellus Antonius de Castro Tavares (mact@cin.ufpe.br) Orientador: Fernando da Fonseca de Souza (fdfd@cin.ufpe.br)

Recife, 5 de outubro de 2006.

Agradecimentos

À minha família pelo apoio durante toda a minha vida e à minha namorada, por todo o carinho e pela paciência que ela teve comigo durante todo esse semestre.

Resumo

O desempenho do Sistema de Gerenciamento de Banco de Dados

(SGBD) está diretamente relacionado com a eficiência das aplicações. No contexto atual, sistemas computacionais têm se tornado cada vez mais importantes nos negócios e a eficiência deles pode ser decisiva para o sucesso do empreendimento, principalmente no que se refere aos negócios on-line.

O trabalho apresentado nessa monografia aborda tópicos sobre administração de sistema de gerenciamento de banco de dados. Serão enfatizados principalmente, técnicas de administração que têm influência direta sobre a performance e a estabilidade do SGBD Oracle Database 10g [ORCL10G].

Abstract

The performance of the Database Management System (DBMS) is directly related to the efficiency of the applications. In the current context, computational systems have become very important in businesses and their efficiency can be decisive for the success of the company, especially when the business is totally on-line.

This monograph focuses on topics about the administration of Databases

Management System. The project will emphasize on administration techniques that have direct influence on the performance and the stability of the DBMS Oracle Database 10g [ORCL10G].

1 Introdução10
1.1 Objetivo1
1.2 Estrutura do Trabalho1
2 Arquitetura do Oracle 10g12
2.1 Estruturas de armazenamento lógico12
2.1.1 Tablespaces12
2.1.2 Blocos13
2.1.3 Extensões13
2.1.4 Segmentos14
2.2 Estruturas lógicas do banco de dados15
2.2.1 Tabelas15
2.2.2 Índices17
2.2.3 Seqüências18
2.2.4 Visões19
2.2.5 Usuários/Esquemas20
2.3 Estruturas de armazenamento físico21
2.3.1 Arquivos de parâmetro de inicialização2
2.3.2 Arquivo de controle2
2.3.3 Arquivos de redo log24
2.3.4 Arquivos de dados25
2.4 Estruturas de memória25
2.4.1 System Global Area25
2.5 Processos em segundo plano26
3 Gerenciamento do Oracle 10g29
3.1 Gerenciamento de Tablespaces29
3.1.1 Tablespaces Permanentes29
3.1.2 Tablespace Temporário30
3.1.3 Tablespace de undo32
3.2 Gerenciamento de Armazenamento32
3.2.1 Criação de tablespaces32
3.2.2 Alteração de tablespaces34
3.2.3 Movendo arquivos de dados36
3.2.4 Movendo arquivos de controle38
3.3 Monitoramento do uso de espaço39
3.3.1 Blocos, extensões e segmentos no Oracle40
3.3.2 Visões de Dicionário e de Desempenho Dinâmico43
3.3.3 Recursos Disponíveis no Oracle 10g46
3.3.4 Gerenciamento do tablespace SYSAUX49
3.3.5 Gerenciamento de arquivos de redo log50
3.3.6 Gerenciamento de espaço – Segment Advisor51
3.3.7 Uso de índices53
3.3.9 Automatização de tarefas com o Scheduler57
3.3.10 Gerenciamento de Transações com o Tablespace Undo58
4 Ajuste de banco de dados64
4.1 Ajuste de SQL64
4.1.1 Impacto do ordenamento sobre a carga de dados65
4.1.2 Clusters65
4.1.3 Planos de explicação6
4.2 Ajuste do uso de memória67
4.2.1 Ajustando o tamanho da SGA69
4.3 Ajuste do acesso a dados71
4.3.1 Identificando linhas encadeadas71
4.3.2 Tabelas organizadas por índice72
4.4 Ajuste do armazenamento físico73
5 Oracle Grid74
5.1 Oracle Application Server 10g75
5.2 Oracle Database 10g76
5.3 Oracle Enterprise Manager Grid Control78
5.3.1 Oracle Management Agent - OMA79
5.3.2 Oracle Management Service - OMS80
5.3.3 Oracle Management Repository - OMR81
5.4 Grid Control Console81
6 Conclusão86
Figura 2.1 – Estruturas de armazenamento físico. Fonte: [LONEY05]21
Figura 3.1 – Conteúdo do tablespace SYSAUX30
Figura 3.2 – Edição de tablespaces35
Figura 3.3 – Edição do arquivo de dados35
Figura 3.4 – Adicionando arquivo de dados36
Figura 3.5 – Blocos, extensões e segmentos. Fonte: [DAWES05]40
Figura 3.6 – Formato do bloco Oracle. Fonte: [ORCLCONCEPTS]41
Figura 3.7 – Descrição da visão DBA_TABLESPACES4
Figura 3.8 – Descrição da visão DBA_SEGMENTS4
Figura 3.9 – Descrição da visão DBA_FREE_SPACE45
Figura 3.10 – Criação de um tablespace de undo59
Figura 3.1 – Adicionando arquivo de dados ao tablespace de undo60
Figura 3.1- Definição da estratégia de alocação de espaço no tablespace de undo60
Figura 3.13 - Interface da ferramenta Undo Advisor no Enterprise Manager63
Figura 4.1 – Estimativa dos valores da shared pool70
Figura 5.1 – Componentes do Grid Control. Fonte: [OEMSG05]79
Figura 5.2 – Oracle Management Agent. Fonte: [OEMSG05]80
Figura 5.3 – Oracle Management Service. Fonte: [OEMSG05]81
Figura 5.3 - Grid Control Console: Home82
Figura 5.4 - Grid Control Console: Targets82
Figura 5.5 - Grid Control Console: Deployments83
Figura 5.6 - Grid Control Console: Alerts84
Figura 5.7 - Grid Control Console: Jobs84
Quadro 2.1 – Visões de dicionários de dados20
Quadro 2.2 – Visões de desempenho dinâmico20
Quadro 2.3 – Criação do SPFILE a partir do PFILE2
Quadro 2.4 – Backup do arquivo de controle23
Quadro 2.5 – Exibição do valor do parâmetro USER_DUMP_TEST23
Quadro 2.6 – Especificação dos arquivos de controle23
Quadro 3.1 – Criação de grupos temporários31
Quadro 3.2 – Definição de um grupo de tablespaces temporário como padrão31
Quadro 3.3 – Remoção de grupos temporários32
Quadro 3.4 – Comando para a criação de tablespaces3
Quadro 3.5 – Comando para a localização dos arquivos de dados do tablespace37
Quadro 3.6 – Comando para iniciar o banco de dados (modo MOUNT)37
DATABASE)37
Quadro 3.8 – Comando para abrir o banco de dados37
Quadro 3.9 – Comando de alteração da tabela para modo offline38
TABLESPACE)38
Quadro 3.1 – Parâmetros de inicialização relacionados ao OMF47
Quadro 3.12 – Comando para criação de grupos de disco de redundância normal48
ambiente ASM)49
Quadro 3.14 – Comando para a seleção dos ocupantes do tablespace SYSAUX49
do tablespace SYSAUX50
50
Quadro 3.17 – Comando para habilitar o movimento de linhas de uma tabela51
Quadro 3.18 – Configuração do Segment Advisor52
Quadro 3.19 – Consulta às recomendações do Segment Advisor53
Quadro 3.20 – Seleção da ação sugerida pelo Segment Advisor53
Quadro 3.21 – Comando para monitorar a utilização do índice54
Quadro 3.23 – Consulta à visão V$OBJECT_USAGE sobre a utilização do índice54
Quadro 3.24 – Comando para a reconstrução de índice5
Quadro 3.25 – Comando para listagem de transações suspensas56
Quadro 3.26 – Visões utilizadas no monitoramento do tablespace de undo61
Quadro 3.27 – Cálculo para estimar o tamanho do tablespace de undo62
Quadro 4.1 – Comando para geração do plano de consulta6
Quadro 4.2 – Seleção do plano de execução67
Quadro 4.3 – Consultas no shared pool que mais utilizam E/S68
Quadro 4.4 – Parâmetros de memória69
Quadro 4.5 – Estimando o tamanho do shared pool70

Lista de quadros Quadro 3.7 – Comando para atualizar as referências aos arquivos de dados (ALTER Quadro 3.10 - Comando para atualizar as referências aos arquivos de dados (ALTER Quadro 3.13 – Comando CREATE TABLESPACE (referência ao arquivo de dados / Quadro 3.15 – Consulta para seleção do procedure para movimentação dos componentes Quadro 3.16 – Comando para movimentação dos componentes do tablespace SYSAUX Quadro 4.6 – Linhas encadeadas em uma tabela..............................................................71

Quadro 4.7 – Reconstrução de uma IOT73
Quadro 5.1- Modificação to tamanho do shared pool em um ambiente RAC7

1 Introdução

Sistemas computacionais têm se tornado extremamente críticos para o mundo corporativo. Ocorre um aumento crescente da dependência entre negócios e os sistemas de informação. Muitas novas gerações de empreendimentos como lojas virtuais, baseiam-se e dependem inteiramente da disponibilidade dessa infra-estrutura de Tecnologia da Informação (TI).

Para sobreviver no mercado e se manterem competitivas, as organizações devem procurar cada vez mais um aumento de eficiência e produtividade. Sistemas de TI, como parte estratégica da empresa, têm um papel fundamental nessa conquista.

Dentre os sistemas computacionais utilizados pelas empresas, destacamse os sistemas de gerenciamento de banco de dados (SGBD). Seu desempenho está em grande parte relacionado com o desempenho das aplicações.

Por estar presente na maioria das soluções corporativas, como mostra a tabela 1, o SGBD da Oracle [ORCL], mais especificamente a sua última versão, o Oracle Database 10g, foi escolhido como objeto de estudo deste trabalho.

Tabela 1 - Venda mundial de Sistemas de Gerenciamento de Banco de Dados Relacionais (Valores em milhões de dólares)

Companhia 2005 Divisão do mercado (%)

Fonte: Gartner Dataquest (Maio 2006) [GARTNER]

1.1 Objetivo

Esse trabalho tem como objetivo auxiliar os Administradores de Banco de

Dados (ABD) ou DBA (da sigla em Inglês – Database Administrator) na tarefa de administração do SGBD Oracle 10g.

Inicialmente será feito um estudo da arquitetura Oracle e posteriormente serão abordados diversos assuntos relacionados à administração do Oracle 10g como boas práticas de administração que visam aumentar a estabilidade e confiabilidade do sistema, técnicas utilizadas para se maximizar o desempenho do sistema, entre outros. Ao longo do trabalho, a ferramenta Oracle Enterprise Manager Database Control [OEMDBC] será utilizada para a execução de boa parte das tarefas administrativas.

1.2 Estrutura do Trabalho

Além deste capítulo introdutório, o trabalho encontra-se assim dividido: Capítulo 2 - Aborda conceitos sobre a arquitetura do SGBD Oracle como estruturas de armazenamento e de memória.

Capítulo 3 - Trata da administração do Sistema de Gerenciamento Oracle 10g, apresenta as técnicas relacionadas à administração que têm influência na performance do sistema.

desempenho geral do sistema

Capítulo 4 – Discute o ajuste do banco de dados, neste capítulo são identificados os elementos que podem ser ajustados de modo a melhorar o

Capítulo 5 – Faz um estudo da arquitetura e das ferramentas do sistema

Oracle 10g que dão suporte a arquitetura de computação em grade [TAURION04] e de que forma toda essa estrutura pode contribuir na melhoria da performance e estabilidade do sistema. Capítulo 6 – Conclusão e sugestões de trabalhos futuros.

2 Arquitetura do Oracle 10g

O servidor de banco de dados Oracle é composto de uma ou mais base de dados e uma instância. Embora esses termos sejam erroneamente utilizados de forma equivalente, seus conceitos são bastante diferentes.

Um banco de dados é uma coleção de dados em disco. No Oracle, esses dados podem estar em um ou mais arquivos de dados no servidor de banco de dados. Além dos arquivos de dados, o banco de dados é composto de outros dois tipos de arquivos: o arquivo de controle e o arquivo de redo log. O arquivo de controle é essencial para o funcionamento da base de dados, ele é continuamente escrito e contém metadados sobre a base e seu status atual. Os arquivos de redo log são indispensáveis para a recuperação da base em caso de falha, pois neles são guardados os históricos das operações na base de dados.

Várias estruturas físicas e lógicas também fazem parte do banco de dados, sendo a tabela a mais comum entre essas estruturas lógicas.

Uma instância é um grande bloco de memória alocado, a SGA (System

Global Area), juntamente com processos em background que interagem entre a SGA e o banco de dados.

2.1 Estruturas de armazenamento lógico 2.1.1 Tablespaces

Os arquivos de dados do banco de dados são agrupados em uma ou mais estruturas lógicas chamadas tablespace (espaço de tabela). O arquivo de dados, porém, deve pertencer a uma e somente uma tablespace.

O espaço de tabela pode ser classificado como temporário no sentido de que os segmentos salvos neste espaço são temporários. Tablespaces temporários são utilizados para trabalho de classificação e criação de índices, por exemplo.

Tablespaces podem ser gerenciados de duas formas: por dicionário ou localmente.

Quando uma tablespace é gerenciada pelo dicionário as extensões (extents) são alocadas e desalocadas de acordo com inserções e deleções em tabelas do dicionário, o que provoca um overhead maior porque mesmo que os usuários e aplicações possuam suas tabelas no tablespace USERS, por exemplo, o tablespace administrativo SYSTEM ainda teria que ser acessado para o gerenciamento desses dados nas tabelas. Isso cria um número maior nas requisições de E/S e pode ser um problema grande para comandos que fazem muitas operações de atualização no banco.

Em uma tablespace gerenciada localmente a alocação e desalocação dos extents são realizadas através de mapas de bits no próprio tablespace.

No Oracle 10g, se o tablespace administrativo SYSTEM for administrado localmente, todos os outros também devem ser.

Os blocos são as menores unidades de armazenamento de um banco de dados no Oracle 10g. O tamanho é medido em bytes e especificado no parâmetro de inicialização DB_BLOCK_SIZE. O tamanho do bloco deve ser um múltiplo do tamanho de bloco do sistema operacional para que as operações de E/S ocorram de modo eficiente.

2.1.3 Extensões

A extensão está um nível acima na hierarquia dos agrupamentos lógicos no banco de dados no Oracle10g. A extensão consiste de um ou mais blocos alocados a um tipo de objeto específico (tabela ou índice).

Os segmentos consistem em um conjunto de extensões. O segmento contém todos os dados de um agrupamento lógico dentro de um tablespace. Por exemplo, para cada tabela o Oracle aloca uma ou mais extensões para formar um segmento de dados da tabela, para cada índice ocorre essa mesma alocação das extensões para formar o segmento de índice.

(Parte 1 de 8)

Comentários