{"id":251,"date":"2016-01-17T23:25:03","date_gmt":"2016-01-18T01:25:03","guid":{"rendered":"http:\/\/dbtimewizard.com.br\/blog\/?p=251"},"modified":"2019-11-11T19:02:28","modified_gmt":"2019-11-11T22:02:28","slug":"qual-a-importancia-das-estatisticas-de-tabelas-para-o-otimizador","status":"publish","type":"post","link":"http:\/\/dbtimewizard.com.br\/blog\/qual-a-importancia-das-estatisticas-de-tabelas-para-o-otimizador\/","title":{"rendered":"Qual a import\u00e2ncia das estat\u00edsticas de tabelas para o Otimizador?"},"content":{"rendered":"

O Otimizador baseado em custo (CBO) utiliza estat\u00edsticas para determinar o custo dos diversos planos de execu\u00e7\u00e3o que ele produz para uma determinada instru\u00e7\u00e3o SQL e escolhe o plano com o menor custo para utilizar, portanto as informa\u00e7\u00f5es estat\u00edsticas s\u00e3o cruciais na escolha do plano de execu\u00e7\u00e3o e devem fornecer informa\u00e7\u00f5es precisas e atualizadas para que o Otimizador consiga realizar um bom trabalho.<\/span><\/p>\n

Existem tr\u00eas tipos de estat\u00edsticas de objetos: TABLE STATISTICS, COLUMN STATISTICS e INDEX STATISTICS. Nesse artigo vamos explorar um pouco as estat\u00edsticas de tabelas (TABLE STATISTICS), vamos fazer uma simula\u00e7\u00e3o e ver como o Otimizador utiliza essas informa\u00e7\u00f5es.<\/span><\/p>\n

 <\/p>\n

TABLE STATISTICS<\/span><\/p>\n

As informa\u00e7\u00f5es estat\u00edsticas de tabela podem ser consultadas na vis\u00e3o USER_TAB_STATISTICS, existem muitas informa\u00e7\u00f5es dispon\u00edveis nessa vis\u00e3o mas os campos mais importantes para o Otimizador s\u00e3o:<\/span><\/p>\n

NUM_ROWS<\/strong>\u00a0 \u00a0 \u00a0 \u00a0 – N\u00famero de linhas na tabela<\/span>
\nAVG_ROW_LEN<\/strong>\u00a0 – Tamanho m\u00e9dio de uma linha na tabela<\/span><\/p>\n

 <\/p>\n

Para ilustrar na pr\u00e1tica a utiliza\u00e7\u00e3o das estat\u00edsticas de tabelas, vamos fazer uma simula\u00e7\u00e3o simples com uma consulta utilizando o “SAMPLE SCHEMA HR<\/a><\/strong>” que faz parte da instala\u00e7\u00e3o do banco de dados Oracle, nessa simula\u00e7\u00e3o vamos executar a mesma consulta duas vezes, uma sem estat\u00edsticas e outra com estat\u00edsticas, o par\u00e2metro OPTIMIZER_DYNAMIC_SAMPLING ser\u00e1 desativado a n\u00edvel de sess\u00e3o pois o objetivo \u00e9 verificar como o Otimizador utiliza as estat\u00edsticas de tabela na montagem do plano de execu\u00e7\u00e3o.<\/span><\/p>\n

Caso tenha alguma d\u00favida na leitura do plano de execu\u00e7\u00e3o gerado pelo pacote DBMS_XPLAN consulte a s\u00e9rie de artigos que vai ajuda-lo a interpretar essas informa\u00e7\u00f5es:<\/span><\/p>\n

1. Como verificar a ordem que as opera\u00e7\u00f5es s\u00e3o realizadas<\/a><\/span><\/strong>
\n2. Como interpretar os valores estat\u00edsticos estimados<\/a><\/span><\/strong>
\n3. Como interpretar os valores estat\u00edsticos coletados durante a execu\u00e7\u00e3o<\/a><\/span><\/strong>
\n4. Como interpretar as se\u00e7\u00f5es n\u00e3o estat\u00edsticas do plano de execu\u00e7\u00e3o<\/a><\/span><\/strong><\/p>\n

Consulta sem as estat\u00edsticas de tabelas<\/span><\/h2>\n

Para execu\u00e7\u00e3o da consulta sem as estat\u00edsticas de tabelas vamos criar uma nova tabela copia da tabela EMPLOYEES e n\u00e3o vamos coletar as estat\u00edsticas, em seguida vamos verificar como o Otimizador estimou o numero de linhas dessa tabela.<\/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
<\/div><\/td>
HR@<\/span>LAB11 ><\/span> SELECT *<\/span> FROM V$VERSION<\/span> where rownum <<\/span> 2<\/span>;<\/span>
\n
\nBANNER
\n-------------------------------------------------------------------------------<\/span>
\nOracle Database 11g Enterprise Edition Release 11.<\/span>2.<\/span>0.<\/span>4.<\/span>0 -<\/span> Production
\n
\n1<\/span> linha selecionada.<\/span>
\n
\nDecorrido:<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>00.07<\/span>
\nHR@<\/span>LAB11 ><\/span>
\nHR@<\/span>LAB11 ><\/span> CREATE TABLE EMP
\n  2<\/span>  AS<\/span>
\n  3<\/span>  SELECT *<\/span> FROM EMPLOYEES;<\/span>
\n
\nTabela criada.<\/span>
\n
\nHR@<\/span>LAB11 ><\/span>
\nHR@<\/span>LAB11 ><\/span>
\nHR@<\/span>LAB11 ><\/span> SELECT num_rows,<\/span>  avg_row_len
\n  2<\/span>    FROM user_tab_statistics
\n  3<\/span>   WHERE table_name =<\/span> 'EMP'<\/span>;<\/span>
\n
\n  NUM_ROWS AVG_ROW_LEN
\n----------<\/span> -----------<\/span>
\n
\n
\nHR@<\/span>LAB11 ><\/span> ALTER SESSION SET statistics_level=<\/span>ALL;<\/span>
\n
\nSess\u00e3o alterada.<\/span>
\n
\nHR@<\/span>LAB11 ><\/span> Alter session set optimizer_dynamic_sampling=<\/span>0<\/span>;<\/span>
\n
\nSess\u00e3o alterada.<\/span>
\n
\nHR@<\/span>LAB11 ><\/span>
\nHR@<\/span>LAB11 ><\/span>
\nHR@<\/span>LAB11 ><\/span> select \/* tst102 *\/<\/span>
\n  2<\/span>         *<\/span>
\n  3<\/span>    from EMP;<\/span>
\n
\nEMPLOYEE_ID FIRST_NAME           LAST_NAME                 EMAIL                     PHONE_NUMBER         HIRE_DAT JOB_ID         SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
\n-----------<\/span> --------------------<\/span> -------------------------<\/span> -------------------------<\/span> --------------------<\/span> --------<\/span> ----------<\/span> ----------<\/span> --------------<\/span> ----------<\/span> -------------<\/span>
\n        198<\/span> Donald               OConnell                  DOCONNEL                  650.<\/span>507.<\/span>9833         21<\/span>\/<\/span>06<\/span>\/<\/span>07<\/span> SH_CLERK         2600<\/span>                       124<\/span>            50<\/span>
\n        199<\/span> Douglas              Grant                     DGRANT                    650.<\/span>507.<\/span>9844         13<\/span>\/<\/span>01<\/span>\/<\/span>08<\/span> SH_CLERK         2600<\/span>                       124<\/span>            50<\/span>
\n        200<\/span> Jennifer             Whalen                    JWHALEN                   515.<\/span>123.<\/span>4444         17<\/span>\/<\/span>09<\/span>\/<\/span>03<\/span> AD_ASST          4400<\/span>                       101<\/span>            10<\/span>
\n        201<\/span> Michael              Hartstein                 MHARTSTE                  515.<\/span>123.<\/span>5555         17<\/span>\/<\/span>02<\/span>\/<\/span>04<\/span> MK_MAN          13000<\/span>                       100<\/span>            20<\/span>
\n        202<\/span> Pat                  Fay                       PFAY                      603.<\/span>123.<\/span>6666         17<\/span>\/<\/span>08<\/span>\/<\/span>05<\/span> MK_REP           6000<\/span>                       201<\/span>            20<\/span>
\n..<\/span>
\n..<\/span>
\n..<\/span>
\n        197<\/span> Kevin                Feeney                    KFEENEY                   650.<\/span>507.<\/span>9822         23<\/span>\/<\/span>05<\/span>\/<\/span>06<\/span> SH_CLERK         3000<\/span>                       124<\/span>            50<\/span>
\n
\n107<\/span> linhas selecionadas.<\/span>
\n
\nHR@<\/span>LAB11 ><\/span>
\nHR@<\/span>LAB11 ><\/span>
\nHR@<\/span>LAB11 ><\/span> column sql_id new_value m_sql_id
\nHR@<\/span>LAB11 ><\/span> column child_number new_value m_child_no
\nHR@<\/span>LAB11 ><\/span>
\nHR@<\/span>LAB11 ><\/span> SELECT sql_id,<\/span> child_number
\n  2<\/span>    FROM v$sql<\/span>
\n  3<\/span>   WHERE sql_text LIKE '%tst102%'<\/span>
\n  4<\/span>     AND sql_text NOT LIKE '%v$sql%'<\/span>;<\/span>
\n
\nSQL_ID        CHILD_NUMBER
\n-------------<\/span> ------------<\/span>
\n923fg367zbm89            0<\/span>
\n
\nHR@<\/span>LAB11 ><\/span>
\nHR@<\/span>LAB11 ><\/span>
\nHR@<\/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>'typical iostats last'<\/span>)<\/span>)<\/span>;<\/span>
\nantigo   2<\/span>:<\/span>   FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'&m_sql_id'<\/span>,&<\/span>m_child_no,<\/span>'typical iostats last'<\/span>)<\/span>)<\/span>
\nnovo   2<\/span>:<\/span>   FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'923fg367zbm89'<\/span>,<\/span>         0<\/span>,<\/span>'typical iostats last'<\/span>)<\/span>)<\/span>
\n
\nPLAN_TABLE_OUTPUT
\n------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\n
\nSQL_ID  923fg367zbm89,<\/span> child number 0<\/span>
\n-------------------------------------<\/span>
\nselect \/* tst102 *\/<\/span>        *<\/span>   from EMP
\n
\nPlan hash<\/span><\/a> value:<\/span> 3956160932<\/span>
\n
\n-----------------------------------------------------------------------------------------------------------------------------<\/span>
\n|<\/span> Id  |<\/span> Operation         |<\/span> Name |<\/span> Starts |<\/span> E-<\/span>Rows |<\/span>E-<\/span>Bytes|<\/span> Cost (<\/span>%<\/span>CPU)<\/span>|<\/span> E-<\/span>Time<\/span><\/a>   |<\/span> A-<\/span>Rows |<\/span>   A-<\/span>Time<\/span><\/a>   |<\/span> Buffers |<\/span> Reads  |<\/span>
\n-----------------------------------------------------------------------------------------------------------------------------<\/span>
\n|<\/span>   0<\/span> |<\/span> SELECT STATEMENT  |<\/span>      |<\/span>      1<\/span> |<\/span>        |<\/span>       |<\/span>     3<\/span> (<\/span>100<\/span>)<\/span>|<\/span>          |<\/span>    107<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>      12<\/span> |<\/span>      2<\/span> |<\/span>
\n|<\/span>   1<\/span> |<\/span>  TABLE ACCESS FULL|<\/span> EMP  |<\/span>      1<\/span> |<\/span>    409<\/span> |<\/span> 54397<\/span> |<\/span>     3<\/span>   (<\/span>0<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/span> |<\/span>    107<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>      12<\/span> |<\/span>      2<\/span> |<\/span>
\n-----------------------------------------------------------------------------------------------------------------------------<\/span>
\n
\n
\n13<\/span> linhas selecionadas.<\/span>
\n
\nHR@<\/span>LAB11 ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

No plano de execu\u00e7\u00e3o acima observamos que a consulta retornou 107 linhas (A-Rows) porem o otimizador estimou 409 (E-Rows), esta estimativa \u00e9 baseada em valores padr\u00f5es que o Otimizador assume quando n\u00e3o existem estat\u00edsticas para a tabela.<\/span><\/p>\n

\"Default<\/a><\/p>\n

Para chegar ao valor 409 (E-Rows) o Otimizador utilizou a formula {num_of_blocks * (block_size – cache_layer) \/ avg_row_len}, o valor “block_size” \u00e9 8.192 no banco que estamos realizando essa simula\u00e7\u00e3o, o valor “cache_layer” = 24 e o valor “avg_row_len” quando n\u00e3o existem estat\u00edsticas para a tabela \u00e9 100 bytes conforme tabela acima. Para calcular o resultado da formula falta descobrir o valor de “num_of_blocks” que pode ser 100 ou valor real baseado no “extent map”, a seguir vamos utilizar o pacote “DBMS_SPACE.UNUSED_SPACE” para descobrir o numero de blocos utilizados pela tabela.<\/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
<\/div><\/td>
dbauser@<\/span>LAB11 ><\/span> show parameters db_block_size
\n
\nNAME                                 TYPE        VALUE
\n------------------------------------<\/span> -----------<\/span> ------------------------------<\/span>
\ndb_block_size                        integer     8192<\/span>
\ndbauser@<\/span>LAB11 ><\/span>
\ndbauser@<\/span>LAB11 ><\/span>
\ndbauser@<\/span>LAB11 ><\/span> create or replace
\n  2<\/span>  procedure show_space
\n  3<\/span>   (<\/span>p_segname in varchar2,<\/span>
\n  4<\/span>    p_owner in varchar2 default<\/span> user,<\/span>
\n  5<\/span>    p_type in varchar2 default<\/span> 'TABLE'<\/span>,<\/span>
\n  6<\/span>    p_partition in varchar2 default<\/span> NULL<\/span> )<\/span>
\n  7<\/span>    authid current_user
\n  8<\/span>  as<\/span>
\n  9<\/span>  l_free_blks number;<\/span>
\n 10<\/span>  l_total_blocks number;<\/span>
\n 11<\/span>  l_total_bytes number;<\/span>
\n 12<\/span>  l_unused_blocks number;<\/span>
\n 13<\/span>  l_unused_bytes number;<\/span>
\n 14<\/span>  l_LastUsedExtFileId number;<\/span>
\n 15<\/span>  l_LastUsedExtBlockId number;<\/span>
\n 16<\/span>  l_LAST_USED_BLOCK number;<\/span>
\n 17<\/span>
\n 18<\/span>  procedure p(<\/span> p_label in varchar2,<\/span> p_num in number )<\/span>
\n 19<\/span>  is
\n 20<\/span>  begin
\n 21<\/span>  dbms_output.<\/span>put_line(<\/span> rpad(<\/span>p_label,<\/span>40<\/span>,<\/span>'.'<\/span>)<\/span> ||<\/span>
\n 22<\/span>  p_num )<\/span>;<\/span>
\n 23<\/span>  end<\/span><\/a>;<\/span>
\n 24<\/span>
\n 25<\/span>  begin
\n 26<\/span>  dbms_space.<\/span>unused_space
\n 27<\/span>  (<\/span> segment_owner =><\/span> p_owner,<\/span>
\n 28<\/span>  segment_name =><\/span> p_segname,<\/span>
\n 29<\/span>  segment_type =><\/span> p_type,<\/span>
\n 30<\/span>  partition_name =><\/span> p_partition,<\/span>
\n 31<\/span>  total_blocks =><\/span> l_total_blocks,<\/span>
\n 32<\/span>  total_bytes =><\/span> l_total_bytes,<\/span>
\n 33<\/span>  unused_blocks =><\/span> l_unused_blocks,<\/span>
\n 34<\/span>  unused_bytes =><\/span> l_unused_bytes,<\/span>
\n 35<\/span>  LAST_USED_EXTENT_FILE_ID =><\/span> l_LastUsedExtFileId,<\/span>
\n 36<\/span>  LAST_USED_EXTENT_BLOCK_ID =><\/span> l_LastUsedExtBlockId,<\/span>
\n 37<\/span>  LAST_USED_BLOCK =><\/span> l_LAST_USED_BLOCK )<\/span>;<\/span>
\n 38<\/span>
\n 39<\/span>  p(<\/span> 'Total Blocks'<\/span>,<\/span> l_total_blocks )<\/span>;<\/span>
\n 40<\/span>  p(<\/span> 'Unused Blocks'<\/span>,<\/span> l_unused_blocks )<\/span>;<\/span>
\n 41<\/span>  p(<\/span> 'Blocks Used'<\/span>,<\/span> l_total_blocks-<\/span>l_unused_blocks )<\/span>;<\/span>
\n 42<\/span>
\n 43<\/span>  end<\/span><\/a>;<\/span>
\n 44<\/span>  \/<\/span>
\n
\nProcedimento criado.<\/span>
\n
\ndbauser@<\/span>LAB11 ><\/span> set serveroutput on
\ndbauser@<\/span>LAB11 ><\/span> exec<\/span><\/a> show_space(<\/span>'EMP'<\/span>,<\/span>'HR'<\/span>)<\/span>;<\/span>
\nTotal Blocks............................<\/span>8<\/span>
\nUnused Blocks...........................<\/span>3<\/span>
\nBlocks Used.............................<\/span>5<\/span>
\n
\nProcedimento PL\/<\/span>SQL conclu\u00eddo<\/span> com sucesso.<\/span>
\n
\ndbauser@<\/span>LAB11 ><\/span>
\ndbauser@<\/span>LAB11 ><\/span> select 5<\/span>*<\/span>(<\/span>8192<\/span>-<\/span>24<\/span>)<\/span>\/<\/span>100<\/span> from dual;<\/span>
\n
\n5<\/span>*<\/span>(<\/span>8192<\/span>-<\/span>24<\/span>)<\/span>\/<\/span>100<\/span>
\n---------------<\/span>
\n          408<\/span>,<\/span>4<\/span>
\n
\ndbauser@<\/span>LAB11 ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Conforme resultado da “procedure”\u00a0acima o n\u00famero de blocos usados pela tabela s\u00e3o 5 e aplicando esse valor na formula informada na documenta\u00e7\u00e3o da Oracle temos um resultado de 408,4 que \u00e9 o mesmo valor arrendondado que aparece no plano de execu\u00e7\u00e3o no campo E-Rows = 409.<\/span><\/p>\n

<\/h2>\n

Consulta com as estat\u00edsticas de tabelas<\/span><\/h2>\n

Agora vamos coletar as estat\u00edsticas da tabela utilizada na nossa simula\u00e7\u00e3o, executar a consulta novamente e observar quantas linha o Otimizador vai estimar para o plano de execu\u00e7\u00e3o.<\/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
<\/div><\/td>
HR@<\/span>LAB11 ><\/span> BEGIN
\n  2<\/span>     dbms_stats.<\/span>gather_table_stats(<\/span>
\n  3<\/span>     ownname =><\/span> user,<\/span>
\n  4<\/span>     tabname =><\/span> 'EMP'<\/span>,<\/span>
\n  5<\/span>     estimate_percent =><\/span> 100<\/span>,<\/span>
\n  6<\/span>     method_opt =><\/span> 'for all columns size auto'<\/span>,<\/span> cascade =><\/span> TRUE<\/span>)<\/span>;<\/span>
\n  7<\/span>  END<\/span><\/a>;<\/span>
\n  8<\/span>  \/<\/span>
\n
\nProcedimento PL\/<\/span>SQL conclu\u00eddo<\/span> com sucesso.<\/span>
\n
\nHR@<\/span>LAB11 ><\/span>
\nHR@<\/span>LAB11 ><\/span> SELECT num_rows,<\/span>  avg_row_len
\n  2<\/span>    FROM user_tab_statistics
\n  3<\/span>   WHERE table_name =<\/span> 'EMP'<\/span>;<\/span>
\n
\n  NUM_ROWS AVG_ROW_LEN
\n----------<\/span> -----------<\/span>
\n       107<\/span>          69<\/span>
\n
\nHR@<\/span>LAB11 ><\/span>
\nHR@<\/span>LAB11 ><\/span>
\nHR@<\/span>LAB11 ><\/span>
\nHR@<\/span>LAB11 ><\/span> ALTER SESSION SET statistics_level=<\/span>ALL;<\/span>
\n
\nSess\u00e3o alterada.<\/span>
\n
\nHR@<\/span>LAB11 ><\/span> Alter session set optimizer_dynamic_sampling=<\/span>0<\/span>;<\/span>
\n
\nSess\u00e3o alterada.<\/span>
\n
\nHR@<\/span>LAB11 ><\/span>
\nHR@<\/span>LAB11 ><\/span>
\nHR@<\/span>LAB11 ><\/span> select \/* tst104 *\/<\/span>
\n  2<\/span>         *<\/span>
\n  3<\/span>    from EMP;<\/span>
\n
\nEMPLOYEE_ID FIRST_NAME           LAST_NAME                 EMAIL                     PHONE_NUMBER         HIRE_DAT JOB_ID         SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
\n-----------<\/span> --------------------<\/span> -------------------------<\/span> -------------------------<\/span> --------------------<\/span> --------<\/span> ----------<\/span> ----------<\/span> --------------<\/span> ----------<\/span> -------------<\/span>
\n        198<\/span> Donald               OConnell                  DOCONNEL                  650.<\/span>507.<\/span>9833         21<\/span>\/<\/span>06<\/span>\/<\/span>07<\/span> SH_CLERK         2600<\/span>                       124<\/span>            50<\/span>
\n        199<\/span> Douglas              Grant                     DGRANT                    650.<\/span>507.<\/span>9844         13<\/span>\/<\/span>01<\/span>\/<\/span>08<\/span> SH_CLERK         2600<\/span>                       124<\/span>            50<\/span>
\n        200<\/span> Jennifer             Whalen                    JWHALEN                   515.<\/span>123.<\/span>4444         17<\/span>\/<\/span>09<\/span>\/<\/span>03<\/span> AD_ASST          4400<\/span>                       101<\/span>            10<\/span>
\n        201<\/span> Michael              Hartstein                 MHARTSTE                  515.<\/span>123.<\/span>5555         17<\/span>\/<\/span>02<\/span>\/<\/span>04<\/span> MK_MAN          13000<\/span>                       100<\/span>            20<\/span>
\n        202<\/span> Pat                  Fay                       PFAY                      603.<\/span>123.<\/span>6666         17<\/span>\/<\/span>08<\/span>\/<\/span>05<\/span> MK_REP           6000<\/span>                       201<\/span>            20<\/span>
\n...<\/span>
\n...<\/span>
\n...<\/span>
\n        197<\/span> Kevin                Feeney                    KFEENEY                   650.<\/span>507.<\/span>9822         23<\/span>\/<\/span>05<\/span>\/<\/span>06<\/span> SH_CLERK         3000<\/span>                       124<\/span>            50<\/span>
\n
\n107<\/span> linhas selecionadas.<\/span>
\n
\nHR@<\/span>LAB11 ><\/span>
\nHR@<\/span>LAB11 ><\/span>
\nHR@<\/span>LAB11 ><\/span> column sql_id new_value m_sql_id
\nHR@<\/span>LAB11 ><\/span> column child_number new_value m_child_no
\nHR@<\/span>LAB11 ><\/span>
\nHR@<\/span>LAB11 ><\/span> SELECT sql_id,<\/span> child_number
\n  2<\/span>    FROM v$sql<\/span>
\n  3<\/span>   WHERE sql_text LIKE '%tst104%'<\/span>
\n  4<\/span>     AND sql_text NOT LIKE '%v$sql%'<\/span>;<\/span>
\n
\nSQL_ID        CHILD_NUMBER
\n-------------<\/span> ------------<\/span>
\n85hb1va9m5s58            0<\/span>
\n
\nHR@<\/span>LAB11 ><\/span>
\nHR@<\/span>LAB11 ><\/span>
\nHR@<\/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>'typical iostats last'<\/span>)<\/span>)<\/span>;<\/span>
\nantigo   2<\/span>:<\/span>   FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'&m_sql_id'<\/span>,&<\/span>m_child_no,<\/span>'typical iostats last'<\/span>)<\/span>)<\/span>
\nnovo   2<\/span>:<\/span>   FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'85hb1va9m5s58'<\/span>,<\/span>         0<\/span>,<\/span>'typical iostats last'<\/span>)<\/span>)<\/span>
\n
\nPLAN_TABLE_OUTPUT
\n----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\n
\nSQL_ID  85hb1va9m5s58,<\/span> child number 0<\/span>
\n-------------------------------------<\/span>
\nselect \/* tst104 *\/<\/span>        *<\/span>   from EMP
\n
\nPlan hash<\/span><\/a> value:<\/span> 3956160932<\/span>
\n
\n--------------------------------------------------------------------------------------------------------------------<\/span>
\n|<\/span> Id  |<\/span> Operation         |<\/span> Name |<\/span> Starts |<\/span> E-<\/span>Rows |<\/span>E-<\/span>Bytes|<\/span> Cost (<\/span>%<\/span>CPU)<\/span>|<\/span> E-<\/span>Time<\/span><\/a>   |<\/span> A-<\/span>Rows |<\/span>   A-<\/span>Time<\/span><\/a>   |<\/span> Buffers |<\/span>
\n--------------------------------------------------------------------------------------------------------------------<\/span>
\n|<\/span>   0<\/span> |<\/span> SELECT STATEMENT  |<\/span>      |<\/span>      1<\/span> |<\/span>        |<\/span>       |<\/span>     3<\/span> (<\/span>100<\/span>)<\/span>|<\/span>          |<\/span>    107<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>      11<\/span> |<\/span>
\n|<\/span>   1<\/span> |<\/span>  TABLE ACCESS FULL|<\/span> EMP  |<\/span>      1<\/span> |<\/span>    107<\/span> |<\/span>  7383<\/span> |<\/span>     3<\/span>   (<\/span>0<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/span> |<\/span>    107<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>      11<\/span> |<\/span>
\n--------------------------------------------------------------------------------------------------------------------<\/span>
\n
\n
\n13<\/span> linhas selecionadas.<\/span>
\n
\nHR@<\/span>LAB11 ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Ap\u00f3s a coleta de estat\u00edsticas podemos observar que estimativa de linhas selecionadas \u00e9 igual ao n\u00famero de linhas que a consulta retorna (107 linhas), ou seja, quando as estat\u00edsticas existem o Otimizador as utiliza.<\/span><\/p>\n

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

Nesta simula\u00e7\u00e3o utilizamos uma consulta simples, a exist\u00eancia ou n\u00e3o de estat\u00edstica n\u00e3o fez diferen\u00e7a em termos de plano de execu\u00e7\u00e3o pois neste caso a consulta n\u00e3o tem filtro na clausula WHERE e a execu\u00e7\u00e3o da opera\u00e7\u00e3o “TABLE ACCESS FULL” na tabela “EMP” \u00e9 inevit\u00e1vel. Entretanto se essa consulta fosse uma jun\u00e7\u00e3o de duas tabelas, a exist\u00eancia das estat\u00edsticas de tabela\u00a0seria crucial na decis\u00e3o de qual tipo de JOIN seria utilizado nessa opera\u00e7\u00e3o.<\/span><\/p>\n

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

http:\/\/docs.oracle.com\/cd\/E25054_01\/server.1111\/e16638\/stats.htm<\/a><\/p>\n

http:\/\/kamranagayev.com\/2011\/10\/31\/dumping-an-oracle-data-block-reading-the-output-and-doing-some-math\/<\/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":"

O Otimizador baseado em custo (CBO) utiliza estat\u00edsticas para determinar o custo dos diversos planos de execu\u00e7\u00e3o que ele produz para uma determinada instru\u00e7\u00e3o SQL e escolhe o plano com o menor custo para utilizar, portanto as informa\u00e7\u00f5es estat\u00edsticas s\u00e3o cruciais na escolha do plano de execu\u00e7\u00e3o e devem fornecer informa\u00e7\u00f5es precisas e atualizadas para que o Otimizador consiga realizar<\/p>\n","protected":false},"author":2,"featured_media":252,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[71],"tags":[65,6,42,76,46,73,36,75,10,29,11,45,72,61,74],"class_list":["post-251","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-estatisticas","tag-ajuste-de-desempenho","tag-cbo","tag-cost-base-optimizer","tag-dbms_space-unused_space","tag-dbms_xplan-display_cursor","tag-estatisticas-de-tabelas","tag-execution-plan","tag-num_rows","tag-otimizador","tag-performance-tuning","tag-plano-de-execucao","tag-sql-statement-analysis","tag-table-statistics","tag-tempo-de-reposta","tag-user_tab_statistics"],"aioseo_notices":[],"_links":{"self":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/251","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=251"}],"version-history":[{"count":25,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/251\/revisions"}],"predecessor-version":[{"id":2106,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/251\/revisions\/2106"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/media\/252"}],"wp:attachment":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/media?parent=251"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/categories?post=251"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/tags?post=251"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}