{"id":170,"date":"2015-12-13T22:53:59","date_gmt":"2015-12-14T00:53:59","guid":{"rendered":"http:\/\/dbtimewizard.com.br\/blog\/?p=170"},"modified":"2019-10-10T23:09:49","modified_gmt":"2019-10-11T02:09:49","slug":"execution-plan-como-interpretar-os-valores-estatisticos-coletados-durante-a-execucao","status":"publish","type":"post","link":"http:\/\/dbtimewizard.com.br\/blog\/execution-plan-como-interpretar-os-valores-estatisticos-coletados-durante-a-execucao\/","title":{"rendered":"Execution Plan: Como interpretar os valores estat\u00edsticos coletados durante a execu\u00e7\u00e3o"},"content":{"rendered":"

No artigo anterior<\/a><\/strong> foram abordadas as estat\u00edsticas estimadas pelo Otimizador \u00a0para o plano de execu\u00e7\u00e3o, agora vamos tratar das estat\u00edsticas geradas ap\u00f3s a execu\u00e7\u00e3o do plano, \u00e9 nesse momento que temos condi\u00e7\u00f5es de avaliar se o Otimizador acertou em suas estimativas. A efic\u00e1cia\u00a0do plano de execu\u00e7\u00e3o esta diretamente relacionada com a precis\u00e3o da estimativa dessas estat\u00edsticas, ou seja, quanto melhor for a estimativa melhor ser\u00e1 o plano de execu\u00e7\u00e3o.<\/span><\/p>\n

As estat\u00edsticas geradas durante a execu\u00e7\u00e3o da instru\u00e7\u00e3o SQL s\u00e3o tamb\u00e9m chamadas pelo termo t\u00e9cnico de “Rowsource execution statistics<\/strong>” e suas informa\u00e7\u00f5es est\u00e3o dispon\u00edveis na vis\u00e3o V$SQL_PLAN_STATISTICS_ALL<\/a><\/strong>, nela podemos obter informa\u00e7\u00f5es do trabalho realizado por cada uma das opera\u00e7\u00f5es do plano de execu\u00e7\u00e3o, incluindo o n\u00famero de vezes que cada opera\u00e7\u00e3o foi chamada, numero de “buffers” acessados, se o acesso foi em “consistent mode” ou “current mode”, n\u00famero de leituras f\u00edsicas e l\u00f3gicas, total de tempo gasto por cada opera\u00e7\u00e3o e varias outras informa\u00e7\u00f5es sobre utiliza\u00e7\u00e3o “workarea”.<\/span><\/p>\n

A base utilizada para gerar os planos de execu\u00e7\u00e3o nesse artigo foi Oracle Database 11.2.0.4 e para gerar esse planos\u00a0utilizamos o pacote DBMS_XPLAN, se voc\u00ea tiver d\u00favidas quanto a utiliza\u00e7\u00e3o deste pacote consulte o artigo “DBMS_XPLAN: voc\u00ea sabe usar esse extraordin\u00e1rio pacote?<\/a><\/strong>“<\/span><\/p>\n

WORKAREA<\/span><\/h2>\n

As instru\u00e7\u00f5es SQL utilizadas nesse artigo foram escritas de forma a gerar planos de execu\u00e7\u00e3o que possibilitem a explica\u00e7\u00e3o das estat\u00edsticas geradas em tempo de execu\u00e7\u00e3o e n\u00e3o s\u00e3o bons exemplos de consultas em termos de desempenho. A seguir vamos executar uma instru\u00e7\u00e3o SQL simples sem ativar “Rowsource execution statistics” e verificar o que o pacote DBMS_XPLAN.DISPLAY_CURSOR() nos fornece de informa\u00e7\u00e3o:<\/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
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
<\/div><\/td>
dbauser@<\/span>LAB11 ><\/span>ALTER SESSION SET statistics_level=<\/span>TYPICAL;<\/span>
\n
\nSession altered.<\/span>
\n
\ndbauser@<\/span>LAB11 ><\/span>
\ndbauser@<\/span>LAB11 ><\/span>
\ndbauser@<\/span>LAB11 ><\/span>select \/*+ UNNEST *\/<\/span> object_type,<\/span>
\n  2<\/span>         count<\/span><\/a>(<\/span>1<\/span>)<\/span> as<\/span> sub_total
\n  3<\/span>    from (<\/span>select owner,<\/span> object_type,<\/span> count<\/span><\/a>(<\/span>1<\/span>)<\/span> as<\/span> qtde from my_objects group by owner,<\/span> object_type)<\/span>
\n  4<\/span>   group by object_type
\n  5<\/span>   order by 2<\/span> desc;<\/span>
\n
\nOBJECT_TYPE          SUB_TOTAL
\n-------------------<\/span> ----------<\/span>
\nTABLE                      125<\/span>
\nINDEX                      119<\/span>
\nSEQUENCE                   103<\/span>
\nSYNONYM                     93<\/span>
\nPACKAGE                     91<\/span>
\n...<\/span>
\n...<\/span>
\nEDITION                      1<\/span>
\nCLUSTER                      1<\/span>
\nTABLE SUBPARTITION           1<\/span>
\n
\n46<\/span> rows selected.<\/span>
\n
\ndbauser@<\/span>LAB11 ><\/span>
\ndbauser@<\/span>LAB11 ><\/span>
\ndbauser@<\/span>LAB11 ><\/span>column sql_id new_value m_sql_id
\ndbauser@<\/span>LAB11 ><\/span>column child_number new_value m_child_no
\ndbauser@<\/span>LAB11 ><\/span>
\ndbauser@<\/span>LAB11 ><\/span>
\ndbauser@<\/span>LAB11 ><\/span>SELECT sql_id,<\/span> child_number
\n  2<\/span>    FROM v$sql<\/span>
\n  3<\/span>   WHERE sql_text LIKE '%UNNEST%'<\/span>
\n  4<\/span>     and sql_text LIKE '%my_objects%'<\/span>
\n  5<\/span>     AND sql_text NOT LIKE '%v$sql%'<\/span>;<\/span>
\n
\nSQL_ID        CHILD_NUMBER
\n-------------<\/span> ------------<\/span>
\nbpv0k45r54w9y            0<\/span>
\nbpv0k45r54w9y            1<\/span>
\n
\ndbauser@<\/span>LAB11 ><\/span>
\ndbauser@<\/span>LAB11 ><\/span>SELECT *<\/span>
\n  2<\/span>    FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'&m_sql_id'<\/span>,&<\/span>m_child_no,<\/span>'allstats last'<\/span>)<\/span>)<\/span>;<\/span>
\nold   2<\/span>:<\/span>   FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'&m_sql_id'<\/span>,&<\/span>m_child_no,<\/span>'allstats last'<\/span>)<\/span>)<\/span>
\nnew<\/span>   2<\/span>:<\/span>   FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'bpv0k45r54w9y'<\/span>,<\/span>         1<\/span>,<\/span>'allstats last'<\/span>)<\/span>)<\/span>
\n
\nPLAN_TABLE_OUTPUT
\n----------------------------------------------------------------------------------------------------------------<\/span>
\nSQL_ID  bpv0k45r54w9y,<\/span> child number 1<\/span>
\n-------------------------------------<\/span>
\nselect \/*+ UNNEST *\/<\/span> object_type,<\/span>        count<\/span><\/a>(<\/span>:<\/span>"SYS_B_0"<\/span>)<\/span> as<\/span> sub_total
\n  from (<\/span>select owner,<\/span> object_type,<\/span> count<\/span><\/a>(<\/span>:<\/span>"SYS_B_1"<\/span>)<\/span> as<\/span> qtde from
\nmy_objects group by owner,<\/span> object_type)<\/span>  group by object_type  order by
\n:<\/span>"SYS_B_2"<\/span> desc
\n
\nPlan hash<\/span><\/a> value:<\/span> 2143035820<\/span>
\n
\n--------------------------------------------------------------------------------<\/span>
\n|<\/span> Id  |<\/span> Operation             |<\/span> Name       |<\/span> E-<\/span>Rows |<\/span>  OMem |<\/span>  1Mem |<\/span> Used-<\/span>Mem |<\/span>
\n--------------------------------------------------------------------------------<\/span>
\n|<\/span>   0<\/span> |<\/span> SELECT STATEMENT      |<\/span>            |<\/span>        |<\/span>       |<\/span>       |<\/span>          |<\/span>
\n|<\/span>   1<\/span> |<\/span>  SORT<\/span><\/a> ORDER BY        |<\/span>            |<\/span>     46<\/span> |<\/span>  2048<\/span> |<\/span>  2048<\/span> |<\/span> 2048<\/span>  (<\/span>0<\/span>)<\/span>|<\/span>
\n|<\/span>   2<\/span> |<\/span>   HASH<\/span><\/a> GROUP BY       |<\/span>            |<\/span>     46<\/span> |<\/span>  1200K|<\/span>  1200K|<\/span> 1335K (<\/span>0<\/span>)<\/span>|<\/span>
\n|<\/span>   3<\/span> |<\/span>    VIEW               |<\/span>            |<\/span>   5920<\/span> |<\/span>       |<\/span>       |<\/span>          |<\/span>
\n|<\/span>   4<\/span> |<\/span>     HASH<\/span><\/a> GROUP BY     |<\/span>            |<\/span>   5920<\/span> |<\/span>    10M|<\/span>  2855K|<\/span> 2610K (<\/span>0<\/span>)<\/span>|<\/span>
\n|<\/span>   5<\/span> |<\/span>      TABLE ACCESS FULL|<\/span> MY_OBJECTS |<\/span>    119K|<\/span>       |<\/span>       |<\/span>          |<\/span>
\n--------------------------------------------------------------------------------<\/span>
\n
\nNote
\n-----<\/span>
\n   -<\/span> Warning:<\/span> basic plan statistics not available.<\/span> These are only collected when:<\/span>
\n       *<\/span> hint 'gather_plan_statistics'<\/span> is used for<\/span> the statement or
\n       *<\/span> parameter 'statistics_level'<\/span> is set to 'ALL'<\/span>,<\/span> at session or system<\/span><\/a> level
\n
\n
\n26<\/span> rows selected.<\/span>
\n
\ndbauser@<\/span>LAB11 ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

No inicio do processo acima, o par\u00e2metro “statistics_level” foi definido como “TYPICAL”, nesta situa\u00e7\u00e3o a coleta de estat\u00edsticas “Rowsource execution” esta desativada e o plano de execu\u00e7\u00e3o vai omitir varias informa\u00e7\u00f5es estat\u00edsticas importantes, alem disso se voc\u00ea verificar na se\u00e7\u00e3o “Note” logo abaixo do plano de execu\u00e7\u00e3o existe a informa\u00e7\u00e3o de que para obter essas informa\u00e7\u00f5es estat\u00edsticas temos que utilizar o “hint gather_plan_statistics” ou definir o par\u00e2metro ‘statistics_level’ como ‘ALL’.<\/span><\/p>\n

O par\u00e2metro “LAST” utilizado na fun\u00e7\u00e3o “DISPLAY_CURSOR” do pacote DBMS_XPLAN determina que somente as estat\u00edsticas da ultima execu\u00e7\u00e3o ser\u00e3o exibidas. Nesta simula\u00e7\u00e3o o plano de execu\u00e7\u00e3o mostra tr\u00eas colunas estat\u00edsticas relativas a “WORKAREA” ou \u00e1rea de trabalho:<\/span><\/p>\n

OMem<\/strong>: Quantidade de espa\u00e7o\u00a0na memoria que o Oracle estima que ser\u00e1 necess\u00e1ria para executar a opera\u00e7\u00e3o.<\/span>
\n1Mem<\/strong>: Valor de memoria acima do qual o Oracle ainda consegue realizar a opera\u00e7\u00e3o com “ONE-PASS WORKAREA”.<\/span>
\nUsed-Mem<\/strong>: Memoria que foi utilizada na ultima execu\u00e7\u00e3o.<\/span><\/p>\n

Caso voc\u00ea deseje saber como foi a utiliza\u00e7\u00e3o da memoria de varias execu\u00e7\u00f5es do mesma instru\u00e7\u00e3o SQL, podemos executar a fun\u00e7\u00e3o DISPLAY_CURSOR sem a op\u00e7\u00e3o “LAST”.<\/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
25
26
27
28
29
30
31
32
33
34
35
36
37
<\/div><\/td>
dbauser@<\/span>LAB11 ><\/span>SELECT *<\/span>
\n  2<\/span>    FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'&m_sql_id'<\/span>,&<\/span>m_child_no,<\/span>'allstats'<\/span>)<\/span>)<\/span>;<\/span>
\nold   2<\/span>:<\/span>   FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'&m_sql_id'<\/span>,&<\/span>m_child_no,<\/span>'allstats'<\/span>)<\/span>)<\/span>
\nnew<\/span>   2<\/span>:<\/span>   FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'bpv0k45r54w9y'<\/span>,<\/span>         0<\/span>,<\/span>'allstats'<\/span>)<\/span>)<\/span>
\n
\nPLAN_TABLE_OUTPUT
\n----------------------------------------------------------------------------------------------<\/span>
\nSQL_ID  bpv0k45r54w9y,<\/span> child number 0<\/span>
\n-------------------------------------<\/span>
\nselect \/*+ UNNEST *\/<\/span> object_type,<\/span>        count<\/span><\/a>(<\/span>:<\/span>"SYS_B_0"<\/span>)<\/span> as<\/span> sub_total
\n  from (<\/span>select owner,<\/span> object_type,<\/span> count<\/span><\/a>(<\/span>:<\/span>"SYS_B_1"<\/span>)<\/span> as<\/span> qtde from
\nmy_objects group by owner,<\/span> object_type)<\/span>  group by object_type  order by
\n:<\/span>"SYS_B_2"<\/span> desc
\n
\nPlan hash<\/span><\/a> value:<\/span> 2143035820<\/span>
\n
\n--------------------------------------------------------------------------------<\/span>
\n|<\/span> Id  |<\/span> Operation             |<\/span> Name       |<\/span> E-<\/span>Rows |<\/span>  OMem |<\/span>  1Mem |<\/span>  O\/<\/span>1<\/span>\/<\/span>M   |<\/span>
\n--------------------------------------------------------------------------------<\/span>
\n|<\/span>   0<\/span> |<\/span> SELECT STATEMENT      |<\/span>            |<\/span>        |<\/span>       |<\/span>       |<\/span>          |<\/span>
\n|<\/span>   1<\/span> |<\/span>  SORT<\/span><\/a> ORDER BY        |<\/span>            |<\/span>     46<\/span> |<\/span>  2048<\/span> |<\/span>  2048<\/span> |<\/span>     3<\/span>\/<\/span>0<\/span>\/<\/span>0<\/span>|<\/span>
\n|<\/span>   2<\/span> |<\/span>   HASH<\/span><\/a> GROUP BY       |<\/span>            |<\/span>     46<\/span> |<\/span>  1200K|<\/span>  1200K|<\/span>     3<\/span>\/<\/span>0<\/span>\/<\/span>0<\/span>|<\/span>
\n|<\/span>   3<\/span> |<\/span>    VIEW               |<\/span>            |<\/span>   5920<\/span> |<\/span>       |<\/span>       |<\/span>          |<\/span>
\n|<\/span>   4<\/span> |<\/span>     HASH<\/span><\/a> GROUP BY     |<\/span>            |<\/span>   5920<\/span> |<\/span>    10M|<\/span>  2855K|<\/span>     3<\/span>\/<\/span>0<\/span>\/<\/span>0<\/span>|<\/span>
\n|<\/span>   5<\/span> |<\/span>      TABLE ACCESS FULL|<\/span> MY_OBJECTS |<\/span>    119K|<\/span>       |<\/span>       |<\/span>          |<\/span>
\n--------------------------------------------------------------------------------<\/span>
\n
\nNote
\n-----<\/span>
\n   -<\/span> Warning:<\/span> basic plan statistics not available.<\/span> These are only collected when:<\/span>
\n       *<\/span> hint 'gather_plan_statistics'<\/span> is used for<\/span> the statement or
\n       *<\/span> parameter 'statistics_level'<\/span> is set to 'ALL'<\/span>,<\/span> at session or system<\/span><\/a> level
\n
\n
\n26<\/span> rows selected.<\/span>
\n
\ndbauser@<\/span>LAB11 ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

Na simula\u00e7\u00e3o acima podemos observar que a coluna “Used-Mem” foi substitu\u00edda por “O\/1\/M”, essa coluna mostra as estat\u00edsticas de tr\u00eas situa\u00e7\u00f5es na sequ\u00eancia, a primeira representa o numero de vezes que o Oracle realizou a opera\u00e7\u00e3o na memoria, no caso acima foram 3 vezes, a segunda representa o numero de execu\u00e7\u00f5es com “ONE-PASS”, ou seja, n\u00e3o foi poss\u00edvel realizar toda a opera\u00e7\u00e3o na mem\u00f3ria que para a nossa simula\u00e7\u00e3o foi zero e a terceira representa o numero de execu\u00e7\u00f5es com “MULTI-PASS”, quando esta situa\u00e7\u00e3o ocorrer o tempo de reposta da instru\u00e7\u00e3o SQL fica muito ruim, tamb\u00e9m neste caso a nossa simula\u00e7\u00e3o indica que n\u00e3o houve ocorr\u00eancia.<\/span><\/p>\n

EXECUTION STATISTICS<\/span><\/h2>\n

A seguir vamos analisar um plano de execu\u00e7\u00e3o onde as “Rowsource execution statistics” v\u00e3o ser ativadas utilizando o par\u00e2metro “statistics_level” definido a n\u00edvel de sess\u00e3o como “ALL”. \u00c9 importante lembrar que n\u00e3o \u00e9 recomendado definir esse par\u00e2metro como “ALL” a n\u00edvel de sistema, pois isso vai causar um carga extra no banco de dados e degradar o tempo de resposta de todas as instru\u00e7\u00f5es SQL desse banco. Outra observa\u00e7\u00e3o importante \u00e9 que se for utilizado o “hint gather_plan_statistics” para ativar as “Rowsource execution statistics” a coluna “A-TIME” que representa o tempo gasto nas opera\u00e7\u00f5es apresentar\u00e1 valores n\u00e3o confi\u00e1veis.<\/span><\/p>\n

 <\/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
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
<\/div><\/td>
dbauser@<\/span>LAB11 ><\/span>ALTER SESSION SET statistics_level=<\/span>ALL;<\/span>
\n
\nSession altered.<\/span>
\n
\ndbauser@<\/span>LAB11 ><\/span>
\ndbauser@<\/span>LAB11 ><\/span>
\ndbauser@<\/span>LAB11 ><\/span>WITH OBJ AS<\/span> (<\/span>
\n  2<\/span>     select owner,<\/span> object_type,<\/span> object_name,<\/span> status from my_objects order by 1<\/span>,<\/span>2<\/span>,<\/span>3<\/span>
\n  3<\/span>     )<\/span>
\n  4<\/span>  select \/*+ USE_NL(o t) *\/<\/span> t.<\/span>owner,<\/span>
\n  5<\/span>         o.<\/span>object_type,<\/span>
\n  6<\/span>         count<\/span><\/a>(<\/span>1<\/span>)<\/span> nunber_of_obj
\n  7<\/span>    from OBJ o,<\/span>
\n  8<\/span>         MY_TABLES t
\n  9<\/span>   where t.<\/span>owner =<\/span> o.<\/span>owner
\n 10<\/span>     and t.<\/span>owner in (<\/span>'XDB'<\/span>,<\/span>'SYSMAN'<\/span>,<\/span>'SYS'<\/span>)<\/span>
\n 11<\/span>   group by t.<\/span>owner,<\/span> o.<\/span>object_type
\n 12<\/span>   order by 1<\/span>,<\/span>2<\/span>;<\/span>
\n
\nOWNER                          OBJECT_TYPE         NUNBER_OF_OBJ
\n------------------------------<\/span> -------------------<\/span> -------------<\/span>
\nSYS                            CLUSTER                     10220<\/span>
\nSYS                            CONSUMER GROUP              25550<\/span>
\nSYS                            CONTEXT                      7154<\/span>
\n...<\/span>
\n
\nSYSMAN                         EVALUATION CONTEXT           1456<\/span>
\nSYSMAN                         FUNCTION<\/span>                     8736<\/span>
\nSYSMAN                         INDEX                      748384<\/span>
\n...<\/span>
\n
\nXDB                            FUNCTION<\/span>                      192<\/span>
\nXDB                            INDEX                       14464<\/span>
\nXDB                            INDEXTYPE                      64<\/span>
\n...<\/span>
\n
\n73<\/span> rows selected.<\/span>
\n
\ndbauser@<\/span>LAB11 ><\/span>
\ndbauser@<\/span>LAB11 ><\/span>
\ndbauser@<\/span>LAB11 ><\/span>
\ndbauser@<\/span>LAB11 ><\/span>column sql_id new_value m_sql_id
\ndbauser@<\/span>LAB11 ><\/span>column child_number new_value m_child_no
\ndbauser@<\/span>LAB11 ><\/span>
\ndbauser@<\/span>LAB11 ><\/span>
\ndbauser@<\/span>LAB11 ><\/span>SELECT sql_id,<\/span> child_number
\n  2<\/span>    FROM v$sql<\/span>
\n  3<\/span>   WHERE sql_text LIKE '%USE_NL(o t)%'<\/span>
\n  4<\/span>     AND sql_text NOT LIKE '%v$sql%'<\/span>;<\/span>
\n
\nSQL_ID        CHILD_NUMBER
\n-------------<\/span> ------------<\/span>
\ncaj292vwr7n01            0<\/span>
\ndz461gr7wmqh7            0<\/span>
\n
\n2<\/span> rows selected.<\/span>
\n
\ndbauser@<\/span>LAB11 ><\/span>
\ndbauser@<\/span>LAB11 ><\/span>SELECT *<\/span>
\n  2<\/span>    FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'&m_sql_id'<\/span>,&<\/span>m_child_no,<\/span>'allstats last'<\/span>)<\/span>)<\/span>;<\/span>
\n
\nPLAN_TABLE_OUTPUT
\n----------------------------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\nSQL_ID  dz461gr7wmqh7,<\/span> child number 0<\/span>
\n-------------------------------------<\/span>
\nWITH OBJ AS<\/span> (<\/span>    select owner,<\/span> object_type,<\/span> object_name,<\/span> status from
\nmy_objects order by :<\/span>"SYS_B_0"<\/span>,:<\/span>"SYS_B_1"<\/span>,:<\/span>"SYS_B_2"<\/span>    )<\/span> select \/*+
\nUSE_NL(o t) *\/<\/span> t.<\/span>owner,<\/span>        o.<\/span>object_type,<\/span>        count<\/span><\/a>(<\/span>:<\/span>"SYS_B_3"<\/span>)<\/span>
\nnunber_of_obj   from OBJ o,<\/span>        MY_TABLES t  where t.<\/span>owner =<\/span> o.<\/span>owner
\n   and t.<\/span>owner in (<\/span>:<\/span>"SYS_B_4"<\/span>,:<\/span>"SYS_B_5"<\/span>,:<\/span>"SYS_B_6"<\/span>)<\/span>  group by t.<\/span>owner,<\/span>
\no.<\/span>object_type  order by :<\/span>"SYS_B_7"<\/span>,:<\/span>"SYS_B_8"<\/span>
\n
\nPlan hash<\/span><\/a> value:<\/span> 1844763738<\/span>
\n
\n--------------------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\n|<\/span> Id  |<\/span> Operation             |<\/span> Name          |<\/span> Starts |<\/span> E-<\/span>Rows |<\/span> A-<\/span>Rows |<\/span>   A-<\/span>Time<\/span><\/a>   |<\/span> Buffers |<\/span> Reads  |<\/span> Writes |<\/span>  OMem |<\/span>  1Mem |<\/span> Used-<\/span>Mem |<\/span> Used-<\/span>Tmp|<\/span>
\n--------------------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\n|<\/span>   0<\/span> |<\/span> SELECT STATEMENT      |<\/span>               |<\/span>      1<\/span> |<\/span>        |<\/span>     73<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>22.05<\/span> |<\/span>     246K|<\/span>    885<\/span> |<\/span>    873<\/span> |<\/span>       |<\/span>       |<\/span>          |<\/span>         |<\/span>
\n|<\/span>   1<\/span> |<\/span>  SORT<\/span><\/a> GROUP BY NOSORT |<\/span>               |<\/span>      1<\/span> |<\/span>     98<\/span> |<\/span>     73<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>22.05<\/span> |<\/span>     246K|<\/span>    885<\/span> |<\/span>    873<\/span> |<\/span>       |<\/span>       |<\/span>          |<\/span>         |<\/span>
\n|<\/span>   2<\/span> |<\/span>   NESTED LOOPS        |<\/span>               |<\/span>      1<\/span> |<\/span>  33945<\/span> |<\/span>     41M|<\/span>00<\/span>:<\/span>00<\/span>:<\/span>14.45<\/span> |<\/span>     246K|<\/span>    885<\/span> |<\/span>    873<\/span> |<\/span>       |<\/span>       |<\/span>          |<\/span>         |<\/span>
\n|<\/span>   3<\/span> |<\/span>    VIEW               |<\/span>               |<\/span>      1<\/span> |<\/span>  15474<\/span> |<\/span>  42603<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>02.24<\/span> |<\/span>    1802<\/span> |<\/span>    873<\/span> |<\/span>    873<\/span> |<\/span>       |<\/span>       |<\/span>          |<\/span>         |<\/span>
\n|<\/span>   4<\/span> |<\/span>     SORT<\/span><\/a> ORDER BY     |<\/span>               |<\/span>      1<\/span> |<\/span>  15474<\/span> |<\/span>  42603<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>02.23<\/span> |<\/span>    1802<\/span> |<\/span>    873<\/span> |<\/span>    873<\/span> |<\/span>  2772K|<\/span>   749K|<\/span>67584<\/span>  (<\/span>25<\/span>|<\/span>    3072<\/span> |<\/span>
\n|*<\/span>  5<\/span> |<\/span>      TABLE ACCESS FULL|<\/span> MY_OBJECTS    |<\/span>      1<\/span> |<\/span>  15474<\/span> |<\/span>  42603<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.03<\/span> |<\/span>    1701<\/span> |<\/span>      0<\/span> |<\/span>      0<\/span> |<\/span>       |<\/span>       |<\/span>          |<\/span>         |<\/span>
\n|*<\/span>  6<\/span> |<\/span>    INDEX RANGE<\/span><\/a> SCAN   |<\/span> MY_TABLES_IDX |<\/span>  42603<\/span> |<\/span>      2<\/span> |<\/span>     41M|<\/span>00<\/span>:<\/span>00<\/span>:<\/span>07.76<\/span> |<\/span>     245K|<\/span>     12<\/span> |<\/span>      0<\/span> |<\/span>       |<\/span>       |<\/span>          |<\/span>         |<\/span>
\n--------------------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\n
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
\n---------------------------------------------------<\/span>
\n
\n   5<\/span> -<\/span> filter(<\/span>(<\/span>INTERNAL_FUNCTION(<\/span>"OWNER"<\/span>)<\/span> AND INTERNAL_FUNCTION(<\/span>"OWNER"<\/span>)<\/span>)<\/span>)<\/span>
\n   6<\/span> -<\/span> access(<\/span>"T"<\/span>.<\/span>"OWNER"<\/span>=<\/span>"O"<\/span>.<\/span>"OWNER"<\/span>)<\/span>
\n       filter(<\/span>(<\/span>"T"<\/span>.<\/span>"OWNER"<\/span>=:<\/span>SYS_B_4 OR "T"<\/span>.<\/span>"OWNER"<\/span>=:<\/span>SYS_B_5 OR "T"<\/span>.<\/span>"OWNER"<\/span>=:<\/span>SYS_B_6)<\/span>)<\/span>
\n
\n
\n30<\/span> rows selected.<\/span>
\n
\ndbauser@<\/span>LAB11 ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Analisando o plano acima verificamos que foram inclu\u00eddas varias colunas estat\u00edsticas, a seguir vamos descrever o que representa cada uma delas:<\/span><\/p>\n

Starts:<\/strong> N\u00famero de vezes que a opera\u00e7\u00e3o dessa linha foi executada.<\/span>
\nE-rows:<\/strong> Estimativa de registros que ser\u00e3o entregues a opera\u00e7\u00e3o pai por uma execu\u00e7\u00e3o da opera\u00e7\u00e3o dessa linha.<\/span>
\nA-rows:<\/strong> N\u00famero acumulado de registros que ser\u00e3o entregues a opera\u00e7\u00e3o pai de todas as execu\u00e7\u00f5es da opera\u00e7\u00e3o dessa linha.<\/span>
\nA-time:<\/strong> Tempo gasto na execu\u00e7\u00e3o da opera\u00e7\u00e3o dessa linha incluindo os tempos da opera\u00e7\u00f5es descendentes.<\/span>
\nBuffers:\u00a0<\/strong>N\u00famero de “buffers” acessados na execu\u00e7\u00e3o da opera\u00e7\u00e3o dessa linha incluindo seus descendentes.<\/span>
\nReads:<\/strong> N\u00famero de blocos lidos do disco na execu\u00e7\u00e3o da opera\u00e7\u00e3o dessa linha inclu\u00eddo seus descendentes.<\/span>
\nWrites:<\/strong> N\u00famero de blocos gravados em disco na execu\u00e7\u00e3o da opera\u00e7\u00e3o dessa linha incluindo seus descendentes.<\/span>
\nUsed-Tmp:\u00a0<\/strong>Quantidade de espa\u00e7o utilizando na tablespace tempor\u00e1ria em KB para execu\u00e7\u00e3o da opera\u00e7\u00e3o dessa linha.<\/span><\/p>\n

LEITURA DO PLANO DE EXECU\u00c7\u00c3O<\/span><\/h2>\n
1
2
3
4
5
6
7
8
9
10
11
12
13
<\/div><\/td>
Plan hash<\/span><\/a> value:<\/span> 1844763738<\/span>
\n
\n--------------------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\n|<\/span> Id  |<\/span> Operation             |<\/span> Name          |<\/span> Starts |<\/span> E-<\/span>Rows |<\/span> A-<\/span>Rows |<\/span>   A-<\/span>Time<\/span><\/a>   |<\/span> Buffers |<\/span> Reads  |<\/span> Writes |<\/span>  OMem |<\/span>  1Mem |<\/span> Used-<\/span>Mem |<\/span> Used-<\/span>Tmp|<\/span>
\n--------------------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\n|<\/span>   0<\/span> |<\/span> SELECT STATEMENT      |<\/span>               |<\/span>      1<\/span> |<\/span>        |<\/span>     73<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>22.05<\/span> |<\/span>     246K|<\/span>    885<\/span> |<\/span>    873<\/span> |<\/span>       |<\/span>       |<\/span>          |<\/span>         |<\/span>
\n|<\/span>   1<\/span> |<\/span>  SORT<\/span><\/a> GROUP BY NOSORT |<\/span>               |<\/span>      1<\/span> |<\/span>     98<\/span> |<\/span>     73<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>22.05<\/span> |<\/span>     246K|<\/span>    885<\/span> |<\/span>    873<\/span> |<\/span>       |<\/span>       |<\/span>          |<\/span>         |<\/span>
\n|<\/span>   2<\/span> |<\/span>   NESTED LOOPS        |<\/span>               |<\/span>      1<\/span> |<\/span>  33945<\/span> |<\/span>     41M|<\/span>00<\/span>:<\/span>00<\/span>:<\/span>14.45<\/span> |<\/span>     246K|<\/span>    885<\/span> |<\/span>    873<\/span> |<\/span>       |<\/span>       |<\/span>          |<\/span>         |<\/span>
\n|<\/span>   3<\/span> |<\/span>    VIEW               |<\/span>               |<\/span>      1<\/span> |<\/span>  15474<\/span> |<\/span>  42603<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>02.24<\/span> |<\/span>    1802<\/span> |<\/span>    873<\/span> |<\/span>    873<\/span> |<\/span>       |<\/span>       |<\/span>          |<\/span>         |<\/span>
\n|<\/span>   4<\/span> |<\/span>     SORT<\/span><\/a> ORDER BY     |<\/span>               |<\/span>      1<\/span> |<\/span>  15474<\/span> |<\/span>  42603<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>02.23<\/span> |<\/span>    1802<\/span> |<\/span>    873<\/span> |<\/span>    873<\/span> |<\/span>  2772K|<\/span>   749K|<\/span>67584<\/span>  (<\/span>25<\/span>|<\/span>    3072<\/span> |<\/span>
\n|*<\/span>  5<\/span> |<\/span>      TABLE ACCESS FULL|<\/span> MY_OBJECTS    |<\/span>      1<\/span> |<\/span>  15474<\/span> |<\/span>  42603<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.03<\/span> |<\/span>    1701<\/span> |<\/span>      0<\/span> |<\/span>      0<\/span> |<\/span>       |<\/span>       |<\/span>          |<\/span>         |<\/span>
\n|*<\/span>  6<\/span> |<\/span>    INDEX RANGE<\/span><\/a> SCAN   |<\/span> MY_TABLES_IDX |<\/span>  42603<\/span> |<\/span>      2<\/span> |<\/span>     41M|<\/span>00<\/span>:<\/span>00<\/span>:<\/span>07.76<\/span> |<\/span>     245K|<\/span>     12<\/span> |<\/span>      0<\/span> |<\/span>       |<\/span>       |<\/span>          |<\/span>         |<\/span>
\n--------------------------------------------------------------------------------------------------------------------------------------------------------<\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Utilizando as regras apresentadas no artigo “Execution Plan: Como verificar a ordem que as opera\u00e7\u00f5es s\u00e3o realizadas?<\/a><\/strong>” chegamos a conclus\u00e3o que as opera\u00e7\u00f5es desse plano s\u00e3o executadas na seguinte ordem: 5,4,3,6,2,1,0<\/strong>.<\/span><\/p>\n

(5)<\/strong> A primeira opera\u00e7\u00e3o a ser executada foi “TABLE ACCESS FULL” na tabela “MY_OBJECTS”, o Otimizador estimou que seriam lidos 15.474 registros e durante a execu\u00e7\u00e3o foram lidos 42.603 registros, o tempo gasto nesta opera\u00e7\u00e3o foi de 3 cent\u00e9simos de segundo e foram lidos 1701 blocos do “Buffer cache”.<\/span><\/p>\n

(4)<\/strong> A segunda opera\u00e7\u00e3o a ser executada foi “SORT ORDER BY” dos registros (42.603) selecionados na opera\u00e7\u00e3o filha (5), essa opera\u00e7\u00e3o teve uma dura\u00e7\u00e3o de 2 segundos e 20 cent\u00e9simos de segundo, porem na linha da opera\u00e7\u00e3o temos 2 segundos e 23 cent\u00e9simos de segundo pois esse valor acumula os tempos da opera\u00e7\u00f5es filhas, nessa opera\u00e7\u00e3o alem dos 101 blocos de lidos do “Buffer cache” (1701 + 101 = 1802), temos 873 blocos de leitura de disco, 873 blocos de grava\u00e7\u00e3o em disco e 3.072 KB de utiliza\u00e7\u00e3o da tablespace de segmentos tempor\u00e1rios. Essas estat\u00edsticas adicionais aparecem pois a opera\u00e7\u00e3o “SORT ORDER BY” precisa de uma \u00e1rea de trabalho e nesse caso a \u00e1rea de memoria (SORT_AREA_SIZE) destinada para esta atividade foi insuficiente e o banco teve que utilizar a tablespace de segmentos tempor\u00e1rios para concluir a opera\u00e7\u00e3o.<\/span><\/p>\n

(3)<\/strong> A terceira opera\u00e7\u00e3o a ser executada foi “VIEW”, o tempo gasto para executar esta opera\u00e7\u00e3o foi 1 cent\u00e9simo de segundo (lembrando que o valor apresentado \u00e9 um acumulado dos tempos da opera\u00e7\u00f5es filhas 2.23 + 0.01 = 2.24), as demais colunas estat\u00edsticas repetem o valores da opera\u00e7\u00e3o filha pois s\u00e3o colunas de valores acumulados.<\/span><\/p>\n

(6)<\/strong> A quarta opera\u00e7\u00e3o a ser executada foi “INDEX RANGE SCAN” no \u00edndice “MY_TABLES_IDX”, neste caso o Otimizador estimou 2 registros (E-Rows) e foram lidos 41 milh\u00f5es (A-Rows), nessa opera\u00e7\u00e3o podemos observar que diferente das demais opera\u00e7\u00f5es, a coluna “Starts” apresenta o valor de 42603 que significa que esta opera\u00e7\u00e3o foi executada 42603 vezes. Isso explica por que somente esta opera\u00e7\u00e3o consumiu o tempo de 7 segundos e 76 cent\u00e9simos de segundos, fez a leitura de 245 mil blocos no “Buffer Cache” e 12 leituras f\u00edsicas. \u00c9 importante observar que esta opera\u00e7\u00e3o n\u00e3o tem opera\u00e7\u00f5es filhas, logo os valores estat\u00edsticas s\u00e3o exclusivos dela.<\/span><\/p>\n

(2)<\/strong> A quinta opera\u00e7\u00e3o a ser executada foi “NESTED LOOPS”, na verdade devido a natureza dessa opera\u00e7\u00e3o, a opera\u00e7\u00e3o filha (6) \u00e9 executada em conjunto com essa opera\u00e7\u00e3o at\u00e9 o fim do “LOOP”, para defini\u00e7\u00e3o de sequ\u00eancia dizemos que a opera\u00e7\u00e3o (6) foi executada primeiro pois ela \u00e9 filha e sempre vai gerar informa\u00e7\u00e3o para a opera\u00e7\u00e3o pai (2). No plano observamos que o Otimizador estimou 33945 registros quando na verdade foram lidos 41 milh\u00f5es, o tempo de dura\u00e7\u00e3o dessa opera\u00e7\u00e3o foi de 4 segundos e 45 cent\u00e9simos de segundo pois os 10 segundos adicionais que aparecem no plano s\u00e3o das opera\u00e7\u00f5es filhas 3 e 6 (2.24 + 7.76 = 10). O demais valores estat\u00edsticos s\u00e3o valores acumulados e s\u00e3o resultado da soma das opera\u00e7\u00f5es filhas.<\/span><\/p>\n

(1)<\/strong> A sexta opera\u00e7\u00e3o a ser executada foi “SORT GROUP BY NOSORT”, nessa opera\u00e7\u00e3o o Otimizador estimou que seriam tratados 98 registros e ap\u00f3s execu\u00e7\u00e3o foram 73 registros, com um tempo de dura\u00e7\u00e3o de 22 segundos e cinco cent\u00e9simos acumulando os tempos de todas as opera\u00e7\u00f5es descendentes. Demais estat\u00edsticas permaneceram inalteradas e como s\u00e3o valores acumulados significa que para essas colunas n\u00e3o houve atividade.<\/span><\/p>\n

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

As estat\u00edsticas geradas em tempo de execu\u00e7\u00e3o s\u00e3o uma fonte importante de informa\u00e7\u00e3o para analise do desempenho da instru\u00e7\u00e3o SQL, atrav\u00e9s dela conseguimos saber se o Otimizador conseguiu estimar corretamente a quantidade de registro para cada opera\u00e7\u00e3o, conseguimos identificar qual opera\u00e7\u00e3o esta com dura\u00e7\u00e3o de tempo maior, conseguimos ver se a ordem de jun\u00e7\u00e3o das tabelas \u00e9 a mais adequada e muito mais. Existem muitas t\u00e9cnicas de tuning que podem ser aplicadas para melhoria de desempenho de instru\u00e7\u00f5es SQL, mas em quase todas voc\u00ea precisa saber interpretar corretamente as estat\u00edsticas que s\u00e3o exibidas no plano de execu\u00e7\u00e3o para obter sucesso nesse trabalho.<\/span><\/p>\n

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

http:\/\/allthingsoracle.com\/execution-plans-part-11-actuals\/<\/a><\/p>\n

http:\/\/docs.oracle.com\/cd\/E11882_01\/server.112\/e40402\/dynviews_3057.htm#REFRN30252<\/a><\/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":"

No artigo anterior foram abordadas as estat\u00edsticas estimadas pelo Otimizador \u00a0para o plano de execu\u00e7\u00e3o, agora vamos tratar das estat\u00edsticas geradas ap\u00f3s a execu\u00e7\u00e3o do plano, \u00e9 nesse momento que temos condi\u00e7\u00f5es de avaliar se o Otimizador acertou em suas estimativas. A efic\u00e1cia\u00a0do plano de execu\u00e7\u00e3o esta diretamente relacionada com a precis\u00e3o da estimativa dessas estat\u00edsticas, ou seja, quanto melhor<\/p>\n","protected":false},"author":2,"featured_media":171,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[35],"tags":[6,42,46,36,10,29,11,47,45,48,43,44],"class_list":["post-170","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-execution-plan","tag-cbo","tag-cost-base-optimizer","tag-dbms_xplan-display_cursor","tag-execution-plan","tag-otimizador","tag-performance-tuning","tag-plano-de-execucao","tag-rowsource-execution-statistics","tag-sql-statement-analysis","tag-statistics_level","tag-vsql_plan_statistics_all","tag-workarea"],"aioseo_notices":[],"_links":{"self":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/170","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=170"}],"version-history":[{"count":22,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/170\/revisions"}],"predecessor-version":[{"id":2064,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/170\/revisions\/2064"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/media\/171"}],"wp:attachment":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/media?parent=170"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/categories?post=170"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/tags?post=170"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}