Artigo SQL Magazine 20 - Monitoramento no Oracle 9i - Introdução

Monitoramento no Oracle 9i - Introdução

Rodrigo Salviatto

Uma das principais atividades do administrador de banco de dados é otimizar os processos e comandos SQL executados no banco de dados. Os comandos SQL utilizam recursos de CPU e memória entre outros, impactando na performance do servidor de banco de dados e no tempo de retorno dos dados para a aplicação.

O Oracle 9i possui ferramentas que nos auxiliam a identificar, interpretar e otimizar os comandos SQL executados. Veremos nas próximas páginas como utilizar, em um banco de dados Oracle9i, as ferramentas SQL Trace, TKPROF e o comando explain plan.

Estas ferramentas deverão ser utilizadas quando um database ou uma sessão específica estiver apresentando performance insatisfatória com o intuito de identificar a possível causa do problema. É interessante também utilizá-los para visualizar comandos SQL que estão sendo executados por aplicações desenvolvidas por terceiros.

O que é e para que serve o SQL Trace?

O SQL Trace ajuda a avaliar a eficiência de comandos SQL, pois provê informações de performance importantes. Considerando as seguintes etapas:

·         Parse: etapa onde o comando SQL é traduzido. São feitas checagens de direitos de acesso aos objetos referenciados no SQL e são verificados se a tabela, coluna e demais objetos existem no banco de dados;

·         Execute: etapa onde ocorre a execução do comando SQL pelo Oracle.

·         Fetch counts: Etapa onde as linhas são retornadas para a query na aplicação. Esta etapa é realizada apenas nos comandos select.

Ele gera as seguintes estatísticas para cada comando SQL:

·         CPU: tempo total do uso de CPU em segundos para todas as etapas de parse, execute e fetch;

·         DISK: número total de blocos físicos lidos no datafile em todas as etapas de parse, execute e fetch;

·         ROWS: número total de linhas processadas pelo comando SQL;

·         COUNT: tempo total em segundos onde o comando SQL passou pelas etapas de parse, execute e fetch.

É possível habilitarmos o SQL Trace para uma determinada sessão ou para uma determinada instância. Quando o SQL Trace está habilitado, as estatísticas de performance são coletadas para todos os comandos da SQL e são arquivados nos arquivos de trace (trace files). Veremos mais adiante como habilitar o SQL Trace (ler Nota 1).

Nota 1. SQL Trace

Quando o SQL Trace está habilitado para uma sessão, o Oracle gera um arquivo de trace para cada sessão contendo estatísticas do comando SQL executado nesta sessão. Em contrapartida, quando o SQL Trace está habilitado para uma instância, o Oracle irá gerar um arquivo de trace separado para cada processo executado na instância. Dessa forma, é necessário dimensionar espaço em disco do servidor de banco de dados, pois vários arquivos serão gerados após habilitar o SQL Trace no Oracle 9i.

O que é e para que serve o TKPROF?

O TKPROF é um programa que formata o conteúdo do arquivo de trace em um arquivo legível, que poderá ser melhor interpretado pelo DBA. Com esta ferramenta, é possível determinar o plano de execução dos comandos SQL executados no banco de dados Oracle e também criar um script com todas as estatísticas do database.

O TKPROF não reporta os comandos COMMIT e ROLLBACK em seu relatório. O DBA deverá analisar todas as informações geradas no arquivo de saída, mais especificamente os seguintes itens:

·         Comparar o número de parses e execuções: um sistema bem elaborado deverá ter um parse para “n” execuções, evitando que sejam feitos “n” parses para o mesmo comando;

·         Procurar por comandos SQL que não utilizam variáveis bind: alterar comandos para que utilizem variáveis bind;

·         Identificar full scan em tabelas, alto consumo de CPU e excessiva leitura de disco: localizar estes eventos e analisar os casos de acordo com cada database e/ou sistema.  O que pode ser um alto número de dados lidos do disco em um sistema OLTP não pode ser considerado alto em um sistema de data warehouse. Deve-se estudar caso a caso.

Utilizando o explain plan

Qual caminho de acesso ao banco de dados foi utilizado para executar uma determinada consulta? O explain plan poderá responder esta questão para você. Ele avalia o caminho executado por uma consulta e a coloca em uma tabela chamada PLAN_TABLE (todo esquema que utilizar o explain plan deverá ter esta tabela criada). Utilize o script utlxplan.sql localizado geralmente no diretório RDBMS do servidor de banco de dados para criar este esquema (Unix: /opt/oracle/app/oracle/admin/9.2.0.1.0/rdbms/utlxplan.sql – Windows: c:\oracle\ora92\rdbms\utlxplan.sql).

Vejamos um exemplo na Listagem 1 da criação da tabela PLAN_TABLE no esquema SCOTT em ambiente Windows.

SQL> conn scott@orcl

Enter Password: ******

Conectado.

SQL> set echo on

SQL> @d:\oracle\ora92\rdbms\admin\utlxplan.sql

SQL> create table PLAN_TABLE (

  2          statement_id    varchar2(30),

  3          timestamp       date,

  4          remarks         varchar2(80),

  5          operation       varchar2(30),

  6          options         varchar2(255),

  7          object_node     varchar2(128),

  8          object_owner    varchar2(30),

  9          object_name     varchar2(30),

 10          object_instance numeric,

 11          object_type     varchar2(30),

 12          optimizer       varchar2(255),

 13 ...