{"id":115,"date":"2015-11-29T21:15:44","date_gmt":"2015-11-29T23:15:44","guid":{"rendered":"http:\/\/dbtimewizard.com.br\/blog\/?p=115"},"modified":"2019-10-10T23:08:37","modified_gmt":"2019-10-11T02:08:37","slug":"execution-plan-como-verificar-a-ordem-que-as-operacoes-sao-realizadas","status":"publish","type":"post","link":"http:\/\/dbtimewizard.com.br\/blog\/execution-plan-como-verificar-a-ordem-que-as-operacoes-sao-realizadas\/","title":{"rendered":"Execution Plan: Como verificar a ordem que as opera\u00e7\u00f5es s\u00e3o realizadas"},"content":{"rendered":"

Quando iniciamos um trabalho de Tuning de uma instru\u00e7\u00e3o SQL, precisamos ler o plano de execu\u00e7\u00e3o Oracle dessa instru\u00e7\u00e3o e entender o que cada uma das opera\u00e7\u00f5es esta realizando e a sequ\u00eancia que essas opera\u00e7\u00f5es est\u00e3o sendo executadas.<\/span><\/p>\n

O manuais da Oracle abordam essa quest\u00e3o de forma muito simplista e ao utilizarmos essas regras muitas vezes\u00a0n\u00e3o conseguimos determinar a ordem correta de execu\u00e7\u00e3o das opera\u00e7\u00f5es.<\/span><\/p>\n

Esse artigo tem como objetivo apresentar uma regra mais consistente que permita a verifica\u00e7\u00e3o da sequ\u00eancia de execu\u00e7\u00e3o das opera\u00e7\u00f5es de forma simples e r\u00e1pida.<\/span><\/p>\n

Defini\u00e7\u00e3o da regra<\/span><\/strong><\/p>\n

Come\u00e7amos a leitura do plano a partir da opera\u00e7\u00e3o id=0, essa \u00e9 a\u00a0opera\u00e7\u00e3o pai (SELECT STATEMENT), a opera\u00e7\u00e3o indentada um n\u00edvel abaixo dela ser\u00e1 sua filha que por sua vez ser\u00e1 opera\u00e7\u00e3o pai da opera\u00e7\u00e3o indentada um n\u00edvel abaixo dela tamb\u00e9m e assim sucessivamente. <\/span>
\nConsiderando este crit\u00e9rio de identifica\u00e7\u00e3o das opera\u00e7\u00f5es, a seguir temos basicamente 4 regras:<\/span><\/p>\n

    \n
  1. Seguindo a partir da opera\u00e7\u00e3o id=0, a primeira opera\u00e7\u00e3o a ser executada no\u00a0plano ser\u00e1 aquela mais indentada que n\u00e3o possuir uma filha.<\/span><\/li>\n
  2. Na sequ\u00eancia ser\u00e1 executada a opera\u00e7\u00e3o irm\u00e3 dessa opera\u00e7\u00e3o se ela existir e n\u00e3o tiver uma opera\u00e7\u00e3o filha.<\/span><\/li>\n
  3. Caso a opera\u00e7\u00e3o irm\u00e3 tenha filha vamos continuar verificando as opera\u00e7\u00f5es que descendem dela at\u00e9 encontrar uma opera\u00e7\u00e3o que n\u00e3o tenha filha, esta ser\u00e1 a pr\u00f3xima opera\u00e7\u00e3o a ser executada.<\/span><\/li>\n
  4. Ap\u00f3s a execu\u00e7\u00e3o de uma opera\u00e7\u00e3o se ela n\u00e3o tiver irm\u00e3s a pr\u00f3xima opera\u00e7\u00e3o a ser executada ser\u00e1 a opera\u00e7\u00e3o pai.<\/span><\/li>\n<\/ol>\n

    Leitura do plano de execu\u00e7\u00e3o<\/span><\/strong><\/p>\n

    Vamos verificar a seguir como utilizar as regras acima num plano de execu\u00e7\u00e3o gerado pelo pacote DBMS_XPLAN, se voc\u00ea tiver d\u00favidas de como utilizar este pacote consulte o artigo “DBMS_XPLAN: voc\u00ea sabe usar esse extraordin\u00e1rio pacote?<\/a>“<\/span><\/p>\n

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    <\/div><\/td>
    Plan hash<\/span><\/a> value:<\/span> 4239905139<\/span>
    \n
    \n--------------------------------------------------------------------------------------<\/span>
    \n|<\/span> Id  |<\/span> Operation               |<\/span> Name       |<\/span> Rows  |<\/span> Bytes |<\/span> Cost (<\/span>%<\/span>CPU)<\/span>|<\/span>     Time<\/span><\/a>     |<\/span>
    \n--------------------------------------------------------------------------------------<\/span>
    \n|<\/span>   0<\/span> |<\/span> SELECT STATEMENT        |<\/span>            |<\/span>       |<\/span>       |<\/span>    35<\/span> (<\/span>100<\/span>)<\/span>|<\/span>          |<\/span>
    \n|<\/span>   1<\/span> |<\/span>      SORT<\/span><\/a> GROUP BY          |<\/span>            |<\/span>    11<\/span> |<\/span>    77<\/span> |<\/span>    35<\/span>   (<\/span>3<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/span> |<\/span>
    \n|<\/span>   2<\/span> |<\/span>   MERGE     JOIN<\/span><\/a> CARTESIAN  |<\/span>            |<\/span>  2889<\/span> |<\/span> 20223<\/span> |<\/span>    34<\/span>   (<\/span>0<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/span> |<\/span>
    \n|<\/span>   3<\/span> |<\/span>    TABLE ACCESS FULL    |<\/span> EMPLOYEES  |<\/span>   107<\/span> |<\/span>   749<\/span> |<\/span>     3<\/span>   (<\/span>0<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/span> |<\/span>
    \n|<\/span>   4<\/span> |<\/span>    BUFFER     SORT<\/span><\/a>          |<\/span>            |<\/span>    27<\/span> |<\/span>       |<\/span>    32<\/span>   (<\/span>4<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/span> |<\/span>
    \n|<\/span>   5<\/span> |<\/span>     INDEX FAST FULL SCAN|<\/span> DEPT_ID_PK |<\/span>    27<\/span> |<\/span>       |<\/span>     0<\/span>   (<\/span>0<\/span>)<\/span>|<\/span>          |<\/span>
    \n--------------------------------------------------------------------------------------<\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

    1) Seguindo a regra (1)<\/span><\/strong> vamos identificar a partir do “Id 0” a opera\u00e7\u00e3o descendente que n\u00e3o possui filha, nesse caso ser\u00e1 a opera\u00e7\u00e3o “TABLE ACCESS FULL” (Id 3)<\/span><\/strong>,\u00a0podemos observar que a opera\u00e7\u00e3o abaixo dela esta no mesmo n\u00edvel de indenta\u00e7\u00e3o e portanto n\u00e3o \u00e9 uma opera\u00e7\u00e3o filha, \u00e9 uma opera\u00e7\u00e3o irm\u00e3.<\/span><\/p>\n

    2) A pr\u00f3xima opera\u00e7\u00e3o a ser executada, segundo a regra (2)<\/span>, ser\u00e1 a opera\u00e7\u00e3o irm\u00e3 se ela n\u00e3o tiver opera\u00e7\u00e3o filha, no nosso plano a opera\u00e7\u00e3o irm\u00e3 (Id 4) tem uma filha (Id 5), ent\u00e3o temos que aplicar a regra (3)<\/span><\/strong> que diz que temos que localizar a opera\u00e7\u00e3o descendente que n\u00e3o tem opera\u00e7\u00e3o filha, nesse caso ser\u00e1 a opera\u00e7\u00e3o “INDEX FAST FULL SCAN” (Id 5)<\/span><\/strong> que ser\u00e1 executada.<\/span><\/p>\n

    3) A opera\u00e7\u00e3o (Id 5) n\u00e3o possui opera\u00e7\u00e3o filha e nem opera\u00e7\u00e3o irm\u00e3, portanto vamos aplicar a regra (4)<\/span><\/strong> que diz que se n\u00e3o houver opera\u00e7\u00e3o irm\u00e3 a pr\u00f3xima opera\u00e7\u00e3o a ser executada ser\u00e1 a opera\u00e7\u00e3o pai, ou seja ser\u00e1 a opera\u00e7\u00e3o “BUFFER SORT” (Id 4)<\/span><\/strong>.<\/span><\/p>\n

    4) Como a opera\u00e7\u00e3o (Id 4) tem somente uma irm\u00e3 (Id 3) que j\u00e1 foi executada, vamos aplicar novamente a regra (4)<\/span><\/strong> e a pr\u00f3xima opera\u00e7\u00e3o a ser executada ser\u00e1 a opera\u00e7\u00e3o pai dessa duas irm\u00e3s “MERGE JOIN CARTESIAN” (Id 2)<\/span><\/strong>.<\/span><\/p>\n

    5) Em seguida verificamos que a opera\u00e7\u00e3o (Id 2) n\u00e3o possui irm\u00e3, ou seja, n\u00e3o existe opera\u00e7\u00e3o no mesmo n\u00edvel de indenta\u00e7\u00e3o, e nesse caso vamos aplicar novamente a regra (4)<\/span><\/strong> e a pr\u00f3xima opera\u00e7\u00e3o ser\u00e1 “SORT GROUP BY” (Id 1)<\/span><\/strong>.<\/span><\/p>\n

    6) Concluindo as opera\u00e7\u00f5es ser\u00e3o executadas na seguinte ordem de Id: 3, 5, 4, 2, 1, 0.<\/span><\/p>\n

     <\/p>\n

    Para facilitar a visualiza\u00e7\u00e3o, veja abaixo o mesmo plano onde foi adicionada uma coluna com Id pai da opera\u00e7\u00e3o (Pid) e uma coluna com um numero que indica a ordem de execu\u00e7\u00e3o das opera\u00e7\u00f5es (Ord):<\/span><\/p>\n

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    <\/div><\/td>
    Plan hash<\/span><\/a> value:<\/span> 4239905139<\/span>
    \n
    \n--------------------------------------------------------------------------------------------------<\/span>
    \n|<\/span> Id  |<\/span> Pid |<\/span>     Ord<\/span><\/a> |<\/span> Operation               |<\/span> Name       |<\/span> Rows  |<\/span> Bytes |<\/span> Cost (<\/span>%<\/span>CPU)<\/span>|<\/span> Time<\/span><\/a>     |<\/span>
    \n--------------------------------------------------------------------------------------------------<\/span>
    \n|<\/span>   0<\/span> |<\/span>     |<\/span>   6<\/span> |<\/span> SELECT STATEMENT        |<\/span>            |<\/span>       |<\/span>       |<\/span>    35<\/span> (<\/span>100<\/span>)<\/span>|<\/span>          |<\/span>
    \n|<\/span>   1<\/span> |<\/span>   0<\/span> |<\/span>   5<\/span> |<\/span>      SORT<\/span><\/a> GROUP BY          |<\/span>            |<\/span>    11<\/span> |<\/span>    77<\/span> |<\/span>    35<\/span>   (<\/span>3<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/span> |<\/span>
    \n|<\/span>   2<\/span> |<\/span>   1<\/span> |<\/span>   4<\/span> |<\/span>   MERGE     JOIN<\/span><\/a> CARTESIAN  |<\/span>            |<\/span>  2889<\/span> |<\/span> 20223<\/span> |<\/span>    34<\/span>   (<\/span>0<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/span> |<\/span>
    \n|<\/span>   3<\/span> |<\/span>   2<\/span> |<\/span>   1<\/span> |<\/span>    TABLE ACCESS FULL    |<\/span> EMPLOYEES  |<\/span>   107<\/span> |<\/span>   749<\/span> |<\/span>     3<\/span>   (<\/span>0<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/span> |<\/span>
    \n|<\/span>   4<\/span> |<\/span>   2<\/span> |<\/span>   3<\/span> |<\/span>    BUFFER     SORT<\/span><\/a>          |<\/span>            |<\/span>    27<\/span> |<\/span>       |<\/span>    32<\/span>   (<\/span>4<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/span> |<\/span>
    \n|<\/span>   5<\/span> |<\/span>   4<\/span> |<\/span>   2<\/span> |<\/span>     INDEX FAST FULL SCAN|<\/span> DEPT_ID_PK |<\/span>    27<\/span> |<\/span>       |<\/span>     0<\/span>   (<\/span>0<\/span>)<\/span>|<\/span>          |<\/span>
    \n--------------------------------------------------------------------------------------------------<\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

     <\/p>\n

    Leitura de um plano de execu\u00e7\u00e3o mais complexo<\/strong><\/span><\/h2>\n

    A leitura do plano de execu\u00e7\u00e3o acima serviu apenas para treinar o entendimento de como devem ser aplicadas as regras, vamos fazer outro exerc\u00edcio com um plano de execu\u00e7\u00e3o um pouco mais complexo para consolidar esse entendimento.<\/span><\/p>\n

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    <\/div><\/td>
    Plan hash<\/span><\/a> value:<\/span> 2215075747<\/span>
    \n
    \n-----------------------------------------------------------------------------------------<\/span>
    \n|<\/span> Id  |<\/span> Operation                |<\/span> Name         |<\/span> Rows  |<\/span> Bytes |<\/span> Cost (<\/span>%<\/span>CPU)<\/span>|<\/span>     Time<\/span><\/a>     |<\/span>
    \n-----------------------------------------------------------------------------------------<\/span>
    \n|<\/span>   0<\/span> |<\/span> SELECT STATEMENT         |<\/span>              |<\/span>       |<\/span>       |<\/span>   410<\/span> (<\/span>100<\/span>)<\/span>|<\/span>          |<\/span>
    \n|*<\/span>  1<\/span> |<\/span>  FILTER                  |<\/span>              |<\/span>       |<\/span>       |<\/span>            |<\/span>          |<\/span>
    \n|<\/span>   2<\/span> |<\/span>       SORT<\/span><\/a> GROUP BY          |<\/span>              |<\/span>    20<\/span> |<\/span>   820<\/span> |<\/span>   410<\/span>   (<\/span>1<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>05<\/span> |<\/span>
    \n|*<\/span>  3<\/span> |<\/span>        HASH<\/span><\/a> JOIN<\/span><\/a>             |<\/span>              |<\/span> 55500<\/span> |<\/span>  2222K|<\/span>   408<\/span>   (<\/span>1<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>05<\/span> |<\/span>
    \n|<\/span>   4<\/span> |<\/span>     TABLE ACCESS FULL    |<\/span> COUNTRIES    |<\/span>    23<\/span> |<\/span>   345<\/span> |<\/span>     3<\/span>   (<\/span>0<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/span> |<\/span>
    \n|<\/span>   5<\/span> |<\/span>     TABLE ACCESS FULL    |<\/span> CUSTOMERS    |<\/span> 55500<\/span> |<\/span>  1409K|<\/span>   405<\/span>   (<\/span>1<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>05<\/span> |<\/span>
    \n|<\/span>   6<\/span> |<\/span>       SORT<\/span><\/a> AGGREGATE<\/span><\/a>         |<\/span>              |<\/span>     1<\/span> |<\/span>    10<\/span> |<\/span>            |<\/span>          |<\/span>
    \n|*<\/span>  7<\/span> |<\/span>        HASH<\/span><\/a> JOIN<\/span><\/a>             |<\/span>              |<\/span> 55500<\/span> |<\/span>   541K|<\/span>   406<\/span>   (<\/span>1<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>05<\/span> |<\/span>
    \n|<\/span>   8<\/span> |<\/span>     INDEX FULL SCAN      |<\/span> COUNTRIES_PK |<\/span>    23<\/span> |<\/span>   115<\/span> |<\/span>     1<\/span>   (<\/span>0<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/span> |<\/span>
    \n|<\/span>   9<\/span> |<\/span>     TABLE ACCESS FULL    |<\/span> CUSTOMERS    |<\/span> 55500<\/span> |<\/span>   270K|<\/span>   405<\/span>   (<\/span>1<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>05<\/span> |<\/span>
    \n|<\/span>  10<\/span> |<\/span>       SORT<\/span><\/a> GROUP BY          |<\/span>              |<\/span>     1<\/span> |<\/span>    13<\/span> |<\/span>            |<\/span>          |<\/span>
    \n|<\/span>  11<\/span> |<\/span>    VIEW                  |<\/span>              |<\/span>    12<\/span> |<\/span>   156<\/span> |<\/span>   407<\/span>   (<\/span>1<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>05<\/span> |<\/span>
    \n|<\/span>  12<\/span> |<\/span>         SORT<\/span><\/a> GROUP BY        |<\/span>              |<\/span>    12<\/span> |<\/span>   528<\/span> |<\/span>   407<\/span>   (<\/span>1<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>05<\/span> |<\/span>
    \n|<\/span>  13<\/span> |<\/span>      NESTED LOOPS        |<\/span>              |<\/span>   162<\/span> |<\/span>  7128<\/span> |<\/span>   407<\/span>   (<\/span>1<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>05<\/span> |<\/span>
    \n|<\/span>  14<\/span> |<\/span>       VIEW               |<\/span> VW_GBF_8     |<\/span>   162<\/span> |<\/span>  6318<\/span> |<\/span>   407<\/span>   (<\/span>1<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>05<\/span> |<\/span>
    \n|<\/span>  15<\/span> |<\/span>            SORT<\/span><\/a> GROUP BY     |<\/span>              |<\/span>   162<\/span> |<\/span>  4212<\/span> |<\/span>   407<\/span>   (<\/span>1<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>05<\/span> |<\/span>
    \n|<\/span>  16<\/span> |<\/span>         TABLE ACCESS FULL|<\/span> CUSTOMERS    |<\/span> 55500<\/span> |<\/span>  1409K|<\/span>   405<\/span>   (<\/span>1<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>05<\/span> |<\/span>
    \n|*<\/span> 17<\/span> |<\/span>       INDEX UNIQUE SCAN  |<\/span> COUNTRIES_PK |<\/span>     1<\/span> |<\/span>     5<\/span> |<\/span>     0<\/span>   (<\/span>0<\/span>)<\/span>|<\/span>          |<\/span>
    \n-----------------------------------------------------------------------------------------<\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

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

    A execu\u00e7\u00e3o do plano ser\u00e1 na seguinte ordem Id: 4,5,3,2,8,9,7,6,16,15,14,17,13,12,11,10,1,0<\/span><\/p>\n

    Para facilitar a visualiza\u00e7\u00e3o, veja abaixo o mesmo plano onde foi adicionada uma coluna com Id pai da opera\u00e7\u00e3o (Pid) e uma coluna com um numero que indica a ordem de execu\u00e7\u00e3o das opera\u00e7\u00f5es (Ord):<\/span><\/p>\n

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

    Conclus\u00e3o<\/span><\/strong><\/h2>\n

    Nesse momento voc\u00ea pode estar pensando que \u00e9 complicado aplicar essas regras e determinar a ordem em que as opera\u00e7\u00f5es s\u00e3o executadas em um plano de execu\u00e7\u00e3o, mas com a pr\u00e1tica essa leitura fica relativamente f\u00e1cil. A visualiza\u00e7\u00e3o da sequ\u00eancia de execu\u00e7\u00e3o do plano \u00e9 muito importante pois ela nos ajudar\u00e1 a entender por que uma determinada instru\u00e7\u00e3o SQL n\u00e3o esta executando com um tempo de resposta satisfat\u00f3rio.\u00a0<\/span><\/p>\n

    Refer\u00eancias<\/span><\/strong><\/h2>\n


    \n    http:\/\/allthingsoracle.com\/execution-plans-part-3-the-rule\/<\/a><\/span><\/p>\n

     
    \n 
    \n    \"Promo-D75L\"<\/a><\/p>\n

    \"video-02\"<\/a>
    \n 
    \n 
    \n 
    \n 
    \n 
    \n 
    \n 
    \n 
    \n 
    \n 
    \n 
    \n 
    \n 
    \n <\/p>\n","protected":false},"excerpt":{"rendered":"

    Quando iniciamos um trabalho de Tuning de uma instru\u00e7\u00e3o SQL, precisamos ler o plano de execu\u00e7\u00e3o Oracle dessa instru\u00e7\u00e3o e entender o que cada uma das opera\u00e7\u00f5es esta realizando e a sequ\u00eancia que essas opera\u00e7\u00f5es est\u00e3o sendo executadas. O manuais da Oracle abordam essa quest\u00e3o de forma muito simplista e ao utilizarmos essas regras muitas vezes\u00a0n\u00e3o conseguimos determinar a ordem<\/p>\n","protected":false},"author":2,"featured_media":117,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[35],"tags":[7,36,37,11,38],"class_list":["post-115","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-execution-plan","tag-dbms_xplan","tag-execution-plan","tag-ordem-de-execucao","tag-plano-de-execucao","tag-sql-tuning"],"aioseo_notices":[],"_links":{"self":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/115","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/comments?post=115"}],"version-history":[{"count":30,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/115\/revisions"}],"predecessor-version":[{"id":2062,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/115\/revisions\/2062"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/media\/117"}],"wp:attachment":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/media?parent=115"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/categories?post=115"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/tags?post=115"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}