Introdução

Nas últimas duas edições, abordámos o tema da optimização de SQL com o recurso a técnicas de melhoria do código SQL. Recorremos, nomeadamente, à utilização de bind variables e à correcta implementação de índices. Nesta edição, vamos apresentar uma das ferramentas de diagnóstico que permitem prever e verificar a optimização: os planos de execução.

O que é

Antes de efectuar um SELECT na base de dados, o optimizador traça um plano de execução, de modo a avaliar que dados recolher, onde e como os deve obter. O plano de execução é influenciado por múltiplos factores e varia ao longo do tempo, isto é, varia consoante o estado presente da base de dados.

Os principais factores que definem o plano de execução são a dimensão das tabelas acedidas, a diversidade de dados das tabelas (selectividade) e a existência ou não de índices. Mas, como é que o optimizador obtém, rapidamente, esta informação? O Oracle produz estatísticas da base de dados. As estatísticas registam os principais factores que definem o plano de execução. Deste modo, o optimizador, em vez de percorrer todos os dados afectados por um SELECT, sabe à partida o que vai encontrar. No entanto, para que as estatísticas produzam bons resultados nas queries, é fundamental que estejam actualizadas. Por exemplo, se a estatística nos diz que uma tabela tem dez registos mas, entretanto, carregámos um milhão de novos registos, a estatística produzirá valores errados e induzirá o optimizador por um caminho que não é o óptimo.

Podemos fazer uma analogia com uma deslocação de automóvel desde casa até ao local de trabalho. Se soubermos, antecipadamente, o estado do trânsito sabemos qual a via que, mesmo não parecendo ser a mais óbvia, deverá ser a mais rápida. Se tivermos ouvido o estado do trânsito há uma hora atrás, a situação deverá já estar totalmente diferente, quando sairmos de casa, e a rota mais rápida poderá agora estar entupida. Se, por último, desconhecermos totalmente o estado do trânsito, seguiremos pelo caminho que consideramos mais directo mas que, eventualmente, poderá não ser o mais rápido naquele momento. O Oracle procede do mesmo modo, pelo que, manter as estatísticas actualizadas é uma mais-valia para que o optimizador possa tomar a melhor decisão e, no caso do plano de execução, apresentar o plano mais idêntico à realidade.

Criar a tabela PLAN_TABLE

Os planos de execução são registados numa tabela, usualmente definida como PLAN_TABLE, sob a forma de linhas com relações hierárquicas entre si. Por isso, é possível registar vários planos de execução na PLAN_TABLE, de modo a compararmos diferentes queries.

A PLAN_TABLE poderá não estar disponível no ambiente Oracle. O Oracle fornece um script que permite criar essa tabela facilmente. O script pode ser encontrado na directoria %ORACLE_HOME%/rdbms/admin com o nome UTLXPLAN.SQL. Consoante a versão de Oracle que estamos a utilizar, o script pode conter algumas variações a nível das colunas da tabela. Este script não é mais do que um CREATE TABLE, mas não será aqui exposto, devido à dimensão do mesmo e às variantes que existem para diferentes versões Oracle.

Tendo a garantia de que a tabela existe, devemos sempre limpar o seu conteúdo, antes de iniciarmos o “estudo” das queries, recorrendo à instrução TRUNCATE TABLE PLAN_TABLE.

Determinar o plano de execução

Estamos, agora, em condições de começar a obter planos de execução das nossas queries. Necessitamos, primeiramente, de registar o plano de execução na PLAN_TABLE, recorrendo à instrução EXPLAIN PLAN SET STATEMENT_ID = 'XPTO' FOR, seguida da query que queremos analisar. Por exemplo:

EXPLAIN PLAN SET STATEMENT_ID = 'XPTO' FOR
SELECT C.COD_ASSOC FROM ASSOCIADOS A INNER JOIN CARTOES C ON C.COD_ASSOC = A.CODIGO
WHERE A.CODIGO BETWEEN 21000 AND 21500;

O STATEMENT_ID atribuído é o identificar do plano do tipo varchar2. O plano está criado e podemos continuar a determinar os planos de execução de outras queries ou, por exemplo, verificar os planos antes e após o cálculo das estatísticas. Os planos ficam registados na tabela e só serão eliminados manualmente, pelo que se poderão guardar os registos para consulta futura.

Analisar o plano de execução

Naturalmente que, se a PLAN_TABLE é uma tabela, a visualização do plano de execução é feita com um SELECT à mesma tabela. Alguns editores SQL de Oracle já possuem essa funcionalidade à distância de um clique. O que fazem é executar um dos muitos SELECTs possíveis à tabela. O Oracle também já facilita a consulta dos planos de execução através da seguinte instrução, adaptada aqui ao nosso exemplo, com o respectivo statement_id:

SELECT * FROM TABLE(dbms_xplan.display('plan_table', 'xpto', 'all'));

O resultado desta query poderia ser o seguinte:

A forma correcta de ler o mapa do plano é começar pela instrução mais à direita (maior nível). Quando duas instruções estão ao mesmo nível, começa-se pela que tem um ID menor. Neste caso, vemos que a primeira instrução a ser executada será a n.º 2.

Vejamos, primeiro, as colunas da PLAN_TABLE apresentadas nesta query:

• Id: identificador da linha da instrução do presente plano. Atenção, não é o ID da linha na PLAN_TABLE, mas sim o ID da amostra de dados retirada dessa mesma tabela. Lembremo-nos que a PLAN_TABLE pode conter vários planos de execução.
• Operation: O tipo de instrução a ser executada.
• Name: Tabela ou índice a que se refere a Operation.
• Rows: Número de linhas afectadas ou acedidas.
• Bytes: Total de bytes que serão movimentados para ler os dados da instrução.
• Cost: O custo de CPU para a instrução. Este campo não tem qualquer unidade, pelo que o mesmo deverá ser utilizado como meio de comparação. Por exemplo, comparando o Cost CPU de uma query leve com uma query mais pesada. Este valor é parametrizado num ficheiro de configuração do Oracle e a sua compreensão mais profunda seria alvo de um tema de administração de bases de dados.

Note-se que os campos apresentados pela função do Oracle não correspondem aos campos da PLAN_TABLE em bruto. A função não faz mais do que criar uma VIEW com a selecção de dados da tabela do plano de execução. Analisemos, agora, a informação apresentada para este plano. Os IDs 2 e 3 estão sob a operação de junção NESTED LOOPS. Este é um dos tipos de junção de tabelas que consiste em:

• (2) Escolher a tabela sem índice na condição, neste caso a tabela CARTOES que não possui um índice no campo de junção COD_ASSOC. É feito um FULL TABLE SCAN filtrando as linhas com a condição:

  filter("C"."COD_ASSOC"<=21500 AND "C"."COD_ASSOC">=21000)

  Repare-se no * antes do 2, referente à nota em baixo (Predicate Information), que indica o filtro no SCAN com os números de código do SELECT:

  WHERE A.CODIGO BETWEEN 21000 AND 21500;

• (3) Para cada uma das 51 4 linhas da tabela anterior, já filtrada, é feito um acesso ao índice da tabela ASSOCIADOS, ASSOCIADOS_PK, obtendo apenas os dados com:

  filter("A"."CODIGO"<=21500 AND
  "A"."CODIGO">=21000)

  Tendo em conta que cada linhas das duas tabelas são cruzadas obedecendo ao match:

  access("C"."COD_ASSOC"="A"."CODIGO")

• (1) O Oracle pode agora juntar as tabelas, fazendo o INNER JOIN através do algoritmo de NESTED LOOPS. Obtêm-se 394 linhas, no final, e terão sido acedidos 3940 bytes.

Conclusão

O plano de execução é uma ferramenta bastante poderosa para fazer uma avaliação prévia do impacto da nossa query na base de dados. Seja para avaliar o impacto de uma query num ambiente de produção em alturas críticas ou simplesmente para optimizar e procurar queries com menor consumo de CPU ou IO, o plano de execução é um bom começo.

Conhecendo, detalhadamente, o tipo de operações que o Oracle executa, o plano de execução pode ser, na grande maioria dos casos, mais do que suficiente para prever o resultado da query na BD. No entanto, nem sempre o plano de execução é eficaz. Há casos específicos que necessitam de especial atenção e de uma avaliação pós-processamento através de outra ferramenta, o SQL Trace. O SQL Trace vai registar as operações realizadas pelo Oracle, permitindo-nos analisar detalhadamente o que ocorreu no SELECT. Enquanto o plano de execução permite fazer uma avaliação a priori, o SQL Trace faz uma avaliação a posteriori.

Convém, ainda, frisar que, para que o plano de execução seja o mais fiel possível, as estatística deverão estar actualizadas, o que nem sempre é possível ou viável se a dimensão das bases de dados for muito grande.

Referências

• Estrutura da tabela PLAN_TABLE e lista de operações produzidas pelo plano de execução
• Outras formas de representar o plano de execução