Planos de Execução em ORACLE

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.

Publicado na edição 28 (PDF) da Revista PROGRAMAR.