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

plan_order

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:

  1. 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.
  2. Na sequência será executada a operação irmã dessa operação se ela existir e não tiver uma operação filha.
  3. 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.
  4. 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

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
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. 

Referências


http://allthingsoracle.com/execution-plans-part-3-the-rule/

 

 

Mídia social

 

Deixe uma resposta

O seu endereço de email não será publicado. Campos obrigatórios são marcados com *