O AUTOTRACE é um recurso do SQL*Plus que permite gerar, automaticamente, um relatório baseado no plano de execução usado pelo otimizador assim como também as estatísticas referentes à execução daquele SQL. O Relatório é gerado após a execução com sucesso de comandos DML (SELECT, DELETE, UPDATE e INSERT). Ele é usado para monitorar e otimizar a performance dessas declarações.
O AUTOTRACE pode ser utilizado de cinco maneiras:
1. SET AUTOTRACE OFF: Nenhum relatório de AUTOTRACE é gerado. Esta é a opção padrão (Default).
2. SET AUTOTRACE ON EXPLAIN: O relatório de AUTOTRACE mostra apenas o Plano de execução.
3. SET AUTOTRACE ON STATISTICS: O relatório de AUTOTRACE mostra apenas as estatísticas referentes a execução do SQL.
4. SET AUTOTRACE ON: O Relatório de AUTOTRACE inclui tanto o Plano de execução como as estatísticas referentes à execução do SQL. Também requer executar o script plustrce.sql e receber alguns privilégios de DBA.
5. SET AUTOTRACE TRACEONLY: Funciona como o SET AUTOTRACE ON, mas suprime a impressão do resultado da declaração se ela possuir. Também requer executar o script plustrce.sql e receber alguns privilégios de DBA.
➢ Executar o script plustrce.sql, que fica em ORACLE_HOME\sqlplus\admin com o usuário SYS (como SYSDBA);
➢ Ainda com o usuário SYS, atribuir o role PLUSTRACE para o usuário que fará uso do recurso, caso não seja o próprio DBA.
Exemplo:
1. Ative o SET AUTOTRACE TRACEONLY para exibir o plano de execução e as estatísticas, sem as linhas de retorno:
SQL> SET AUTOTRACE TRACEONLY; 2. Agora execute a seguinte consulta:
SELECT n.nfv_in_numero, i.pro_in_codigo FROM nota_fiscal_venda n, item_nota_fiscal_venda i
WHERE n.nfv_in_numero = i.nfv_in_numero; 3. O resultado deve ser parecido com:
Execution Plan
---
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=60 Bytes=780) 1 0 NESTED LOOPS (Cost=4 Card=60 Bytes=780)
2 1 TABLE ACCESS (FULL) OF 'ITEM_NOTA_FISCAL_VENDA' (Cost=4 Card=60 Bytes=480)
3 1 INDEX (UNIQUE SCAN) OF 'PK_NOTA_FISCAL_VENDA' (UNIQUE)
Statistics --- 0 recursive calls 0 db block gets 24 consistent gets 0 physical reads 0 redo size
1467 bytes sent via SQL*Net to client
532 bytes received via SQL*Net from client 5 SQL*Net roundtrips to/from client 0 sorts (memory)
0 sorts (disk) 60 rows processed
Lendo a saída de um AUTOTRACE
No exemplo acima, onde usamos o AUTOTRACE TRACEONLY, temos o plano de execução e as estatísticas, mas como interpretamos isso?
O plano de execução:
➢ Opitmizer indica o otimizador Oracle usado: CHOOSE ou RULE (Custo ou Regra);
custo total da declaração;
➢ Card indica a cardinalidade em cada ponto do plano. Entenda como cardinalidade o número de relacionamentos para recuperar os dados;
➢ Bytes indica o número de bytes envolvidos em cada ponto do plano;
➢ Nested, como o nome já diz, é algo aninhado e no nosso exemplo temos NESTED LOOP, ou seja, um LOOP aninhado que foi gerado devido ao relacionamento de master/detail entre nota fiscal e item nota fiscal;
➢ TABLE ACCESS (FULL) indica o objeto em que foi realizado um acesso completo, ou seja, no nosso caso todas as linhas da tabela de item nota fiscal foram verificadas. Isso não é bom em tabelas com grande e médio volumes de dados. O ideal é um acesso parcial através de um indíce, como aconteceu com a tabela de notas;
➢ INDEX SCAN indica o índice utilizado para acessar um conjunto de dados, no nosso caso, na tabela de nota fiscal foi usada a chave primária.
As estatísticas:
➢ recursive calls: Número de chamadas recursivas dentro da instrução;
➢ db block gets: é o número de vezes que um bloco foi requisitado para o buffer cache; ➢ consistent gets; é o número de vezes que uma leitura consistente foi requisitada para um bloco do buffer cache.
➢ physical reads: é o número total de blocos de dados lidos do disco para o buffer cache. ➢ redo size: É o tamanho (em bytes) do log redo gerado durante essa operação;
➢ bytes sent via SQL*Net to client: Total de bytes enviados ao cliente pelos processos de segundo plano;
➢ bytes received via SQL*Net from client: Total de bytes enviados ao servidor pelo cliente;
➢ SQL*Net roundtrips to/from client: Número total de mensagens enviadas e recebidas pelo cliente;
➢ sorts (memory): Número de ordenações em memória;
➢ sorts (disk): Número de ordenações em disco. Isso é muito ruim para o desempenho pois executa I/O;
➢ rows processed: Número de linhas processadas na operação.
INDEX SCAN versus FULL TABLE SCAN
Se você estiver selecionando mais de 15 % das linhas de uma tabela, um FULL TABLE SCAN é geralmente mais rápido do que o acesso pelo índice. Quando o acesso por índice causar lentidão ao invés de apresentar um ganho de performance, você pode utilizar algumas técnicas para eliminar o uso do índice:
SELECT *
FROM produto p
WHERE p.pro_vl_ultimavenda+0 = 10000;
índice seria pela coluna pro_vl_ultimavenda e não pela coluna mais zero.
Um índice também não é usado se o Oracle tiver que realizar uma conversão implícita de dados. No nosso exemplo, pro_vl_ultimavenda é do tipo NUMBER, ou seja, se filtrarmos o valor como string, o índice não será usado:
SELECT *
FROM produto p
WHERE p.pro_vl_ultimavenda = '10000';
Essa manobra também poderia ser realizada aplicando uma função na coluna filtrada, sem que isso afetasse seu conteúdo, é claro.
CUIDADO: Lembre-se sempre que essa teoria só vale quando estamos selecionando mais do que 15% dos dados e, mesmo assim, deve ser analisada com atenção, pois as exceções também existem. Tenha responsabilidade no uso dessas técnicas.