Execution Plan: Como verificar a ordem que as operações são realizadas

novembro 29, 2015 V. J.A. Execution Plan

Quando iniciamos um trabalho de Tuning de uma instrução SQL, precisamos ler o plano de execução Oracle dessa instrução e entender o que cada uma das operações esta realizando e a sequência que essas operações estão sendo executadas.

O manuais da Oracle abordam essa questão de forma muito simplista e ao utilizarmos essas regras muitas vezes não conseguimos determinar a ordem correta de execução das operações.

Esse artigo tem como objetivo apresentar uma regra mais consistente que permita a verificação da sequência de execução das operações de forma simples e rápida.

Definição da regra

Começamos a leitura do plano a partir da operação id=0, essa é a operação pai (SELECT STATEMENT), a operação indentada um nível abaixo dela será sua filha que por sua vez será operação pai da operação indentada um nível abaixo dela também e assim sucessivamente.
Considerando este critério de identificação das operações, a seguir temos basicamente 4 regras:

Seguindo a partir da operação id=0, a primeira operação a ser executada no plano será aquela mais indentada que não possuir uma filha.
Na sequência será executada a operação irmã dessa operação se ela existir e não tiver uma operação filha.
Caso a operação irmã tenha filha vamos continuar verificando as operações que descendem dela até encontrar uma operação que não tenha filha, esta será a próxima operação a ser executada.
Após a execução de uma operação se ela não tiver irmãs a próxima operação a ser executada será a operação pai.

Leitura do plano de execução

Vamos verificar a seguir como utilizar as regras acima num plano de execução gerado pelo pacote DBMS_XPLAN, se você tiver dúvidas de como utilizar este pacote consulte o artigo “DBMS_XPLAN: você sabe usar esse extraordinário pacote?“

1
2
3
4
5
6
7
8
9
10
11
12

Plan hash value: 4239905139

--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 35 (100)| |
| 1 | SORT GROUP BY | | 11 | 77 | 35 (3)| 00:00:01 |
| 2 | MERGE JOIN CARTESIAN | | 2889 | 20223 | 34 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | EMPLOYEES | 107 | 749 | 3 (0)| 00:00:01 |
| 4 | BUFFER SORT | | 27 | | 32 (4)| 00:00:01 |
| 5 | INDEX FAST FULL SCAN| DEPT_ID_PK | 27 | | 0 (0)| |
--------------------------------------------------------------------------------------

1) Seguindo a regra (1) vamos identificar a partir do “Id 0” a operação descendente que não possui filha, nesse caso será a operação “TABLE ACCESS FULL” (Id 3), podemos observar que a operação abaixo dela esta no mesmo nível de indentação e portanto não é uma operação filha, é uma operação irmã.

2) A próxima operação a ser executada, segundo a regra (2), será a operação irmã se ela não tiver operação filha, no nosso plano a operação irmã (Id 4) tem uma filha (Id 5), então temos que aplicar a regra (3) que diz que temos que localizar a operação descendente que não tem operação filha, nesse caso será a operação “INDEX FAST FULL SCAN” (Id 5) que será executada.

3) A operação (Id 5) não possui operação filha e nem operação irmã, portanto vamos aplicar a regra (4) que diz que se não houver operação irmã a próxima operação a ser executada será a operação pai, ou seja será a operação “BUFFER SORT” (Id 4).

4) Como a operação (Id 4) tem somente uma irmã (Id 3) que já foi executada, vamos aplicar novamente a regra (4) e a próxima operação a ser executada será a operação pai dessa duas irmãs “MERGE JOIN CARTESIAN” (Id 2).

5) Em seguida verificamos que a operação (Id 2) não possui irmã, ou seja, não existe operação no mesmo nível de indentação, e nesse caso vamos aplicar novamente a regra (4) e a próxima operação será “SORT GROUP BY” (Id 1).

6) Concluindo as operações serão executadas na seguinte ordem de Id: 3, 5, 4, 2, 1, 0.

Para facilitar a visualização, veja abaixo o mesmo plano onde foi adicionada uma coluna com Id pai da operação (Pid) e uma coluna com um numero que indica a ordem de execução das operações (Ord):

1
2
3
4
5
6
7
8
9
10
11
12

Plan hash value: 4239905139

--------------------------------------------------------------------------------------------------
| Id | Pid | Ord | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | | 6 | SELECT STATEMENT | | | | 35 (100)| |
| 1 | 0 | 5 | SORT GROUP BY | | 11 | 77 | 35 (3)| 00:00:01 |
| 2 | 1 | 4 | MERGE JOIN CARTESIAN | | 2889 | 20223 | 34 (0)| 00:00:01 |
| 3 | 2 | 1 | TABLE ACCESS FULL | EMPLOYEES | 107 | 749 | 3 (0)| 00:00:01 |
| 4 | 2 | 3 | BUFFER SORT | | 27 | | 32 (4)| 00:00:01 |
| 5 | 4 | 2 | INDEX FAST FULL SCAN| DEPT_ID_PK | 27 | | 0 (0)| |
--------------------------------------------------------------------------------------------------

Leitura de um plano de execução mais complexo

A leitura do plano de execução acima serviu apenas para treinar o entendimento de como devem ser aplicadas as regras, vamos fazer outro exercício com um plano de execução um pouco mais complexo para consolidar esse entendimento.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

Plan hash value: 2215075747

-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 410 (100)| |
|* 1 | FILTER | | | | | |
| 2 | SORT GROUP BY | | 20 | 820 | 410 (1)| 00:00:05 |
|* 3 | HASH JOIN | | 55500 | 2222K| 408 (1)| 00:00:05 |
| 4 | TABLE ACCESS FULL | COUNTRIES | 23 | 345 | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | CUSTOMERS | 55500 | 1409K| 405 (1)| 00:00:05 |
| 6 | SORT AGGREGATE | | 1 | 10 | | |
|* 7 | HASH JOIN | | 55500 | 541K| 406 (1)| 00:00:05 |
| 8 | INDEX FULL SCAN | COUNTRIES_PK | 23 | 115 | 1 (0)| 00:00:01 |
| 9 | TABLE ACCESS FULL | CUSTOMERS | 55500 | 270K| 405 (1)| 00:00:05 |
| 10 | SORT GROUP BY | | 1 | 13 | | |
| 11 | VIEW | | 12 | 156 | 407 (1)| 00:00:05 |
| 12 | SORT GROUP BY | | 12 | 528 | 407 (1)| 00:00:05 |
| 13 | NESTED LOOPS | | 162 | 7128 | 407 (1)| 00:00:05 |
| 14 | VIEW | VW_GBF_8 | 162 | 6318 | 407 (1)| 00:00:05 |
| 15 | SORT GROUP BY | | 162 | 4212 | 407 (1)| 00:00:05 |
| 16 | TABLE ACCESS FULL| CUSTOMERS | 55500 | 1409K| 405 (1)| 00:00:05 |
|* 17 | INDEX UNIQUE SCAN | COUNTRIES_PK | 1 | 5 | 0 (0)| |
-----------------------------------------------------------------------------------------

01) Aplicando a regra (1): A operação “TABLE ACCESS FULL” (Id 4) será executada;
02) Aplicando a regra (2): A operação “TABLE ACCESS FULL” (Id 5) será executada;
03) Aplicando a regra (4): A operação “HASH JOIN” (Id 3) será executada;
04) Aplicando a regra (4): A operação “SORT GROUP BY” (Id 2) será executada;
05) Aplicando a regra (3): A operação “INDEX FULL SCAN” (Id 8) será executada;
06) Aplicando a regra (2): A operação “TABLE ACCESS FULL” (Id 9) será executada;
07) Aplicando a regra (4): A operação “HASH JOIN” (Id 7) será executada;
08) Aplicando a regra (4): A operação “SORT AGGREGATE” (Id 6) será executada;
09) Aplicando a regra (3): A operação “TABLE ACCESS FULL” (Id 16) será executada;
10) Aplicando a regra (4): A operação “SORT GROUP BY” (Id 15) será executada;
11) Aplicando a regra (4): A operação “VIEW” (Id 14) será executada;
12) Aplicando a regra (2): A operação “INDEX UNIQUE SCAN” (Id 17) será executada;
13) Aplicando a regra (4): A operação “NESTED LOOPS” (Id 13) será executada;
14) Aplicando a regra (4): A operação “SORT GROUP BY” (Id 12) será executada;
15) Aplicando a regra (4): A operação “VIEW” (Id 11) será executada;
16) Aplicando a regra (4): A operação “SORT GROUP BY” (Id 10) será executada;
17) Aplicando a regra (4): A operação “FILTER” (Id 1) será executada;
18) Aplicando a regra (4): A operação “SELECT STATEMENT” (Id 0) será executada;

A execução do plano será na seguinte ordem Id: 4,5,3,2,8,9,7,6,16,15,14,17,13,12,11,10,1,0

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

Plan hash value: 2215075747

-----------------------------------------------------------------------------------------------------
| Id | Pid | Ord | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | | 18 | SELECT STATEMENT | | | | 410 (100)| |
|* 1 | 0 | 17 | FILTER | | | | | |
| 2 | 1 | 4 | SORT GROUP BY | | 20 | 820 | 410 (1)| 00:00:05 |
|* 3 | 2 | 3 | HASH JOIN | | 55500 | 2222K| 408 (1)| 00:00:05 |
| 4 | 3 | 1 | TABLE ACCESS FULL | COUNTRIES | 23 | 345 | 3 (0)| 00:00:01 |
| 5 | 3 | 2 | TABLE ACCESS FULL | CUSTOMERS | 55500 | 1409K| 405 (1)| 00:00:05 |
| 6 | 1 | 8 | SORT AGGREGATE | | 1 | 10 | | |
|* 7 | 6 | 7 | HASH JOIN | | 55500 | 541K| 406 (1)| 00:00:05 |
| 8 | 7 | 5 | INDEX FULL SCAN | COUNTRIES_PK | 23 | 115 | 1 (0)| 00:00:01 |
| 9 | 7 | 6 | TABLE ACCESS FULL | CUSTOMERS | 55500 | 270K| 405 (1)| 00:00:05 |
| 10 | 1 | 16 | SORT GROUP BY | | 1 | 13 | | |
| 11 | 10 | 15 | VIEW | | 12 | 156 | 407 (1)| 00:00:05 |
| 12 | 11 | 14 | SORT GROUP BY | | 12 | 528 | 407 (1)| 00:00:05 |
| 13 | 12 | 13 | NESTED LOOPS | | 162 | 7128 | 407 (1)| 00:00:05 |
| 14 | 13 | 11 | VIEW | VW_GBF_8 | 162 | 6318 | 407 (1)| 00:00:05 |
| 15 | 14 | 10 | SORT GROUP BY | | 162 | 4212 | 407 (1)| 00:00:05 |
| 16 | 15 | 9 | TABLE ACCESS FULL| CUSTOMERS | 55500 | 1409K| 405 (1)| 00:00:05 |
|* 17 | 13 | 12 | INDEX UNIQUE SCAN | COUNTRIES_PK | 1 | 5 | 0 (0)| |
-----------------------------------------------------------------------------------------------------

Conclusão

Nesse momento você pode estar pensando que é complicado aplicar essas regras e determinar a ordem em que as operações são executadas em um plano de execução, mas com a prática essa leitura fica relativamente fácil. A visualização da sequência de execução do plano é muito importante pois ela nos ajudará a entender por que uma determinada instrução SQL não esta executando com um tempo de resposta satisfatório.