Algumas vezes nos deparamos com o seguinte tipo de problema em nosso trabalho:

“Vou fazer uma alteração no servidor de banco de dados. Qual será o impacto? Vai melhorar ou vai piorar? E quanto?”

Se você também se deparou com esta situação, o Oracle 11g oferece um ótimo recurso que pode te reportar a diferença do “antes e depois” da alteração. Seja ela uma alteração simples, como a criação de um índice em uma tabela ou uma nova coleta de estatísticas; até grandes alterações como upgrades do SGBD, do sistema operacional, ou até mesmo do hardware do servidor.

Conheçam o SQL Performance Analyzer. Com ele você pode ter métricas exatas da diferença de performance depois que a alteração desejada seja efetuada. Vou mostrar isto na prática, usando como base um exemplo que peguei no site Oracle-Base.

Primeiro, conecte-se à sua base de dados, com um usuário que tenha grants CONNECT e CREATE TABLE.

P:>sqlplus milton/milton

SQL*Plus: Release 11.2.0.1.0 Production on Qui Ago 4 10:27:03 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Conectado a:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing
options

SQL> CREATE TABLE objetos AS
  2  SELECT * FROM all_objects;

Tabela criada.

Criei uma tabela acima, com uma quantidade razoável de registros, para usá-la no nossos testes. Com a tabela recém-criada, vamos fazer uma coleta de estatísticas:

 SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'OBJETOS',
cascade => true);

Procedimento PL/SQL concluÝdo com sucesso.

Agora vamos rodar alguns select’s – que serão os select’s comparados no “antes e depois”. Em um sistema do “mundo real”, troque pelos select’s mais críticos do seu sistema, ou seja, aqueles que você pretende fazer a comparação.

SELECT COUNT(*) FROM objetos WHERE object_id <= 100;
SELECT object_name FROM objetos  WHERE object_id = 100;
SELECT COUNT(*) FROM objetos WHERE object_id <= 1000;
SELECT object_name FROM objetos WHERE object_id = 1000;
SELECT COUNT(*) FROM objetos WHERE object_id BETWEEN 100 AND 1000;

Ok, já criamos uma tabela com uma boa quantidade de dados, coletamos estatísticas da tabela e rodamos alguns select’s sobre essa tabela. Reparem que todos os select’s usaram a coluna object_id na cláusula WHERE. Esta coluna não tem índice, e é exatamente essa a alteração que faremos. Vamos criar um índice nesta coluna e depois comparar a performance destes select’s sem índice e com índice.

Agora vamos conectar como SYS em um outro terminal, para criar um “SQL Tuning Set”:

P:>sqlplus / as sysdba

SQL> EXEC DBMS_SQLTUNE.create_sqlset(sqlset_name => 'teste_milton');

Em seguida, a função SELECT_CURSOR_CACHE é usado para recuperar um cursor que contém todas as instruções SQL que foram analisados ​​pelo esquema MILTON e que contém a palavra “objetos”. O cursor resultante é carregado para o SQLSET utilizando o procedimento LOAD_SQLSET.

DECLARE
  l_cursor  DBMS_SQLTUNE.sqlset_cursor;
BEGIN
  OPEN l_cursor FOR
     SELECT VALUE(a)
     FROM   TABLE(
              DBMS_SQLTUNE.select_cursor_cache(
                basic_filter   => 'sql_text LIKE ''%objetos%''
                     and parsing_schema_name = ''MILTON''',
                attribute_list => 'ALL')
            ) a;

  DBMS_SQLTUNE.load_sqlset(sqlset_name     => 'teste_milton',
                           populate_cursor => l_cursor);
END;
/
SQL> SELECT sql_text
  2  FROM   dba_sqlset_statements
  3  WHERE  sqlset_name = 'teste_milton';

SQL_TEXT
-------------------------------------------------------------------
SELECT object_name FROM objetos WHERE object_id = 100
SELECT COUNT(*) FROM objetos WHERE object_id BETWEEN 100 and 1000
SELECT COUNT(*) FROM objetos WHERE object_id <= 1000
SELECT COUNT(*) FROM objetos WHERE object_id <= 100
CREATE TABLE objetos AS
SELECT * FROM all_objects

SELECT object_name FROM objetos WHERE object_id = 1000

6 linhas selecionadas.

Agora sim vamos efetivamente usar o pacote do Performance Analyzer, o DBMS_SQLPA. Primeiro, criar uma “analysis task”:

CONN / AS SYSDBA

VARIABLE v_task VARCHAR2(64);
EXEC :v_task :=  DBMS_SQLPA.create_analysis_task(sqlset_name =>
'teste_milton');

PL/SQL procedure successfully completed.

SQL> PRINT :v_task

V_TASK
---------------------------------------------------------------
TAREFA_643

Agora vamos capturar e armazenar a análise do “antes”, ou seja, a performance atual, antes da criação do índice:

BEGIN
  DBMS_SQLPA.execute_analysis_task(
    task_name       => :v_task,
    execution_type  => 'test execute',
    execution_name  => 'before_change');
END;
/

Agoro volto ao schema “milton” para criar o índice e coleto novamente as estatísticas:

 CONN milton/milton

CREATE INDEX objetos_index_01 ON objetos(object_id);

EXEC DBMS_STATS.gather_table_stats(USER, 'OBJETOS',
                                     cascade => TRUE);

Novamente como usuário SYS, capturo a análise de performance da nova situação, ou seja, após a crianção do índice:

BEGIN
  DBMS_SQLPA.execute_analysis_task(
    task_name       => :v_task,
    execution_type  => 'test execute',
    execution_name  => 'after_change');
END;
/

Executar a comparação de performance:

 BEGIN
  DBMS_SQLPA.execute_analysis_task(
    task_name        => :v_task,
    execution_type   => 'compare performance',
    execution_params => dbms_advisor.arglist(
                          'execution_name1',
                          'before_change',
                          'execution_name2',
                          'after_change')
    );
END;
/

E finalmente, vamos gerar uma página HTML contendo o relatório. Algo lindo pra mostrar pro seu gerente, é uma evidência quantitativa de melhoria de performance!

SQL> SET PAGESIZE 0
SQL> SET LINESIZE 1000
SQL> SET LONG 1000000
SQL> SET LONGCHUNKSIZE 1000000
SQL> SET TRIMSPOOL ON
SQL> SET TRIM ON

SQL> SPOOL D:Worktestessql_perf_analyzer.htm

SQL> SELECT DBMS_SQLPA.report_analysis_task(:v_task, 'HTML', 'ALL')
     FROM   dual;

SQL> SPOOL OFF

Feito! Agora é só abrir o arquivo “D:Worktestessql_perf_analyzer.htm” no seu browser e analisar as informações.

Milton Bastos é DBA Oracle e Desenvolvedor PL/SQL, dividido entre Apucarana/PR e Curitiba/PR. Certificações: OCA (Oracle 11g DBA Certified Associate), Oracle Database 11g Data Warehousing Certified Implementation Specialist, Oracle Database 11g Sales Specialist Assessment, Oracle Database Appliance PreSales Specialist Assessment, Oracle Database Appliance Sales Specialist Assessment

&nbsp;