Nas publicações anteriores foram abordados os tópicos: como determinar a sequência de execução do plano, como interpretar a estatísticas estimadas e as estatísticas geradas durante a execução do plano, agora vamos abordar algumas seções complementares que fornecem informações importantes para entender por que o plano de execução foi criado com…
Execution Plan: Como verificar a ordem que as operações são realizadas
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.
Ajudo DBAs e analistas de sistema a se destacarem em suas empresas
e obter um crescimento mais acelerado em suas carreiras, quer saber mais click no link abaixo:
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/
Parabéns, ótimo artigo, muito esclarecedor!!!
Olá Priscila,
Obrigado pelo reconhecimento.
Um forte abraço,
Valter Aquino
Segundo a regra 1, apos o id 0 por que não conhecemos pelo INDEX FAST FULL SCAN| DEPT_ID_PK no exemplo 1 ou TABLE ACCESS FULL| CUSTOMERS do exemplo dois? Não entendi muito bem.
Olá Danilo.
A regra 1 diz que a primeira operação a ser executada no plano de execução será aquela operação descendente da operação 0 mais indentada que não tiver filhas.
Vamos então pegar o exemplo 1 do plano de execução, a operação 0 = SELECT a operação filha do SELECT é a operação SORT GROUP BY que por sua vez tem uma operação filha que é um MERGE JOIN, este por sua vez tem uma operação filha que é a TABLE ACCESS FULL e como podemos observar a operação TABLE ACCESS FULL não tem filhos, ela tem uma operação IRMÃ pois a operação BUFFER SORT não esta indentada em relação a operação TABLE ACCESS FULL. Portanto a primeira operação a ser executada será a operação TABLE ACCESS FULL.
A Primeira operação a ser executada no plano de execução não é a operação mais indentada do plano, mas sim a operação descendente da operação 0 mais indentada na sequência.
Por favor tente aplicar esta regra no exemplo 2 e veja se conseguiu entender, se não fique a vontade para me questionar.
Obrigado por compartilhar a sua dúvida,
Um forte abraço.
Valter Aquino