oracle

Hoje vou demonstrar o uso da package DBMS_SQLTUNE para gerar relatórios de monitoramento de SQL’s. Esse recurso foi adicionado na versão 11g, porém algumas funcionalidades entraram apenas na versão 11gR2.

Configuração do ambiente

Primeiramente, vamos conferir dois parâmetros obrigatórios para usar esse recurso:

SQL> conn / as sysdba
Conectado.

SQL> SHOW PARAMETER statistics_level
NAME                                 TYPE        VALUE
------------------------------------ ----------- -------------------
statistics_level                     string      TYPICAL

SQL> SHOW PARAMETER control_management_pack_access
NAME                                 TYPE        VALUE
------------------------------------ ----------- -------------------
control_management_pack_access       string      DIAGNOSTIC+TUNING

O parâmetro statistics_level tem que estar com o valor “TYPICAL” ou “ALL”. O parâmetro
control_management_pack_access tem que estar com o valor “DIAGNOSTIC+TUNING”. No meu ambiente, conforme vimos acima, está tudo OK.

/*+ MONITOR */

Vamos usar o hint MONITOR no SQL que desejamos monitorar, conforme o exemplo abaixo.

SQL> conn hr/hr
Conectado.
SQL> set lines 120
SQL> set pages 100
SQL> SELECT /*+ MONITOR */ e.first_name||' '||e.last_name nome,
                           j.job_title, d.department_name
  2  FROM employees e, jobs j, departments d
  3  WHERE e.department_id = d.department_id
  4  AND e.job_id = j.job_id;

REPORT_SQL_MONITOR

SQL> conn / as sysdba
Conectado.
SQL> SELECT sql_id, status, sql_text
  2  FROM   v$sql_monitor
  3  WHERE  username = 'HR';

SQL_ID        STATUS
------------- -------------------
SQL_TEXT
---------------------------------------------------------------------

908bjgq4y71u4 DONE (ALL ROWS)
SELECT /*+ MONITOR */ e.first_name||' '||e.last_name nome,
j.job_title, d.department_name
FROM employees e, jobs j, departments d
WHERE e.department_id = d.department_id
AND e.job_id = j.job_id

O select acima identificou o SQL que executei anteriormente no schema HR. Com isso eu consegui o seu ID (campo SQL_ID).

Com este ID eu consigo gerar o relatório usando o REPORT_SQL_MONITOR.

SQL> SET LONG 10000
SQL> SET LONGCHUNKSIZE 10000
SQL> SET LINESIZE 1000
SQL> SET PAGESIZE 0
SQL> SET TRIM ON
SQL> SET TRIMSPOOL ON
SQL> SET ECHO OFF
SQL> SET FEEDBACK OFF

SQL> SPOOL D:Worktestesreport_sql.htm
SQL> SELECT DBMS_SQLTUNE.report_sql_monitor(
  2    sql_id => '908bjgq4y71u4',
  3  type => 'HTML',
  4    report_level => 'ALL') AS report
  5  FROM dual;

SQL> SPOOL OFF

Pronto! Um arquivo em formato HTML foi criado no diretório especificado  –
D:Worktestesreport_sql.htm. O Arquivo mostra vários detalhes do SQL monitorado: plano de execução, plan hash, horários exatos de execução, duração, qual sessão executou, buffer gets, database time, entre outros.

REPORT_SQL_DETAIL

Essa função retorna detalhes do monitoramento, gerando um HTML da mesma maneira que o exemplo acima. Testem e aproveitem!

SQL> SPOOL D:Worktestesreport_sql_detail.htm
SQL> SELECT DBMS_SQLTUNE.report_sql_detail(
  2    sql_id => '908bjgq4y71u4',
  3    type => 'ACTIVE',
  4    report_level => 'ALL') AS report
  5        FROM dual;

SQL> SPOOL OFF

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