{"id":711,"date":"2016-05-09T22:08:58","date_gmt":"2016-05-10T01:08:58","guid":{"rendered":"http:\/\/dbtimewizard.com.br\/blog\/?p=711"},"modified":"2019-11-11T18:57:30","modified_gmt":"2019-11-11T21:57:30","slug":"o-otimizador-do-oracle-precisa-da-nossa-ajuda","status":"publish","type":"post","link":"http:\/\/dbtimewizard.com.br\/blog\/o-otimizador-do-oracle-precisa-da-nossa-ajuda\/","title":{"rendered":"O Otimizador do Oracle precisa da nossa ajuda?"},"content":{"rendered":"

As estat\u00edsticas sobre os dados de uma tabela s\u00e3o fundamentais para o Otimizador escolher qual a maneira mais eficiente de acessar esses dados, mas existem informa\u00e7\u00f5es sobre os dados que n\u00e3o s\u00e3o coletadas de forma autom\u00e1tica e portanto n\u00e3o est\u00e3o dispon\u00edveis para o Otimizador sem que haja uma interven\u00e7\u00e3o manual, o que causa em algumas situa\u00e7\u00f5es a escolha de planos de execu\u00e7\u00e3o ineficientes. Respondendo a quest\u00e3o proposta no titulo desse artigo: sim, em algumas situa\u00e7\u00f5es especificas precisamos ajudar o Otimizador, coletando estat\u00edsticas adicionais.<\/span><\/p>\n

Neste artigo vamos abordar as “Extended statistics”, vamos simular uma consulta onde o Otimizador n\u00e3o consegue estimar a cardinalidade da opera\u00e7\u00e3o de forma correta e como consequ\u00eancia cria um plano de execu\u00e7\u00e3o ineficiente.<\/span><\/p>\n

Conceito “Extended statistics”<\/span><\/h2>\n

As “Extended statistics” foram implementadas no Banco Oracle para tratar dois tipos de situa\u00e7\u00f5es onde as estat\u00edsticas existentes n\u00e3o fornecem informa\u00e7\u00f5es suficientes para o Otimizador fazer uma boa estimativa da cardinalidade:<\/span><\/p>\n

1. Quando existem m\u00faltiplos predicados em diferentes colunas na clausula WHERE de uma instru\u00e7\u00e3o SQL para uma tabela e existe algum tipo de rela\u00e7\u00e3o entre essas colunas, por exemplo quando temos uma tabela de ve\u00edculos automotores e nela temos duas colunas: Nome do veiculo e Fabricante. Nesse caso existe uma rela\u00e7\u00e3o entre essas duas colunas pois os registros existentes na tabela de um determinado veiculo s\u00f3 podem ser de um determinado fabricante. (Ex: Uno\/Fiat, HB20\/Hyndai, Fiesta\/Ford,etc..)<\/span><\/p>\n

2. Quando um predicado utiliza uma express\u00e3o, por exemplo uma fun\u00e7\u00e3o do Oracle ou uma fun\u00e7\u00e3o criada pelo usu\u00e1rio.<\/span><\/p>\n

Simula\u00e7\u00e3o “Extended statistics”<\/span><\/h2>\n

A seguir vamos simular a consulta de uma tabela onde duas colunas possuem um relacionamento e em fun\u00e7\u00e3o disso as estat\u00edsticas tradicionais n\u00e3o s\u00e3o capazes de fornecer as informa\u00e7\u00f5es necess\u00e1rias para o Otimizador fazer uma boa estimativa da cardinalidade da opera\u00e7\u00e3o, na sequ\u00eancia vamos criar as “Extended statistics” e verificar que com esse tipo de informa\u00e7\u00f5es o Otimizador vai melhorar significativamente a estimativa da cardinalidade e como consequ\u00eancia melhorar o plano de execu\u00e7\u00e3o.<\/span><\/p>\n

Cria\u00e7\u00e3o da tabela<\/span><\/h3>\n

Primeiro vamos criar a tabela que utilizaremos na consulta da nossa simula\u00e7\u00e3o e popular com dados que tenham alguma rela\u00e7\u00e3o entre duas colunas:<\/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
98
99
100
<\/div><\/td>
SQL ><\/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> 64bit Production
\n
\nSQL ><\/span>
\nSQL ><\/span> create table dbtw01 (<\/span>id number,<\/span> code1k number,<\/span> code100 number,<\/span> text varchar(<\/span>40<\/span>)<\/span> )<\/span>;<\/span>
\n
\nTable created.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> DECLARE<\/span>
\n  2<\/span>
\n  3<\/span>     v_id     NUMBER :=<\/span> 1<\/span>;<\/span>
\n  4<\/span>     var_x    NUMBER :=<\/span> 1<\/span>;<\/span>
\n  5<\/span>     l_num    number;<\/span>
\n  6<\/span>
\n  7<\/span>  BEGIN
\n  8<\/span>
\n  9<\/span>     FOR<\/span> var1 IN 1<\/span>..<\/span>100<\/span>
\n 10<\/span>     LOOP
\n 11<\/span>        var_x :=<\/span> 1<\/span>;<\/span>
\n 12<\/span>        FOR<\/span> var2 IN 1<\/span>..<\/span>2<\/span>
\n 13<\/span>        LOOP
\n 14<\/span>           l_num :=<\/span> round<\/span><\/a>(<\/span>dbms_random.<\/span>value(<\/span>100<\/span>,<\/span>0<\/span>)<\/span>)<\/span>;<\/span>
\n 15<\/span>           insert into dbtw01 values(<\/span>v_id,<\/span> var_x,<\/span> l_num,<\/span> 'DBTimeWizard - Performance and Tuning'<\/span>)<\/span>;<\/span>
\n 16<\/span>           v_id :=<\/span> v_id +<\/span> 1<\/span>;<\/span>
\n 17<\/span>        END<\/span><\/a> LOOP;<\/span>
\n 18<\/span>        var_x :=<\/span> 4<\/span>;<\/span>
\n 19<\/span>        FOR<\/span> var2 IN 1<\/span>..<\/span>8<\/span>
\n 20<\/span>        LOOP
\n 21<\/span>           l_num :=<\/span> round<\/span><\/a>(<\/span>dbms_random.<\/span>value(<\/span>100<\/span>,<\/span>0<\/span>)<\/span>)<\/span>;<\/span>
\n 22<\/span>           insert into dbtw01 values(<\/span>v_id,<\/span> var_x,<\/span> l_num,<\/span> 'DBTimeWizard - Performance and Tuning'<\/span>)<\/span>;<\/span>
\n 23<\/span>           v_id :=<\/span> v_id +<\/span> 1<\/span>;<\/span>
\n 24<\/span>        END<\/span><\/a> LOOP;<\/span>
\n 25<\/span>        var_x :=<\/span> 7<\/span>;<\/span>
\n 26<\/span>        FOR<\/span> var2 IN 1<\/span>..<\/span>50<\/span>
\n 27<\/span>        LOOP
\n 28<\/span>           l_num :=<\/span> round<\/span><\/a>(<\/span>dbms_random.<\/span>value(<\/span>100<\/span>,<\/span>0<\/span>)<\/span>)<\/span>;<\/span>
\n 29<\/span>           insert into dbtw01 values(<\/span>v_id,<\/span> var_x,<\/span> l_num,<\/span> 'DBTimeWizard - Performance and Tuning'<\/span>)<\/span>;<\/span>
\n 30<\/span>           v_id :=<\/span> v_id +<\/span> 1<\/span>;<\/span>
\n 31<\/span>        END<\/span><\/a> LOOP;<\/span>
\n 32<\/span>        var_x :=<\/span> 11<\/span>;<\/span>
\n 33<\/span>        FOR<\/span> var2 IN 1<\/span>..<\/span>150<\/span>
\n 34<\/span>        LOOP
\n 35<\/span>           l_num :=<\/span> round<\/span><\/a>(<\/span>dbms_random.<\/span>value(<\/span>100<\/span>,<\/span>0<\/span>)<\/span>)<\/span>;<\/span>
\n 36<\/span>           insert into dbtw01 values(<\/span>v_id,<\/span> var_x,<\/span> l_num,<\/span> 'DBTimeWizard - Performance and Tuning'<\/span>)<\/span>;<\/span>
\n 37<\/span>           v_id :=<\/span> v_id +<\/span> 1<\/span>;<\/span>
\n 38<\/span>        END<\/span><\/a> LOOP;<\/span>
\n 39<\/span>        var_x :=<\/span> 13<\/span>;<\/span>
\n 40<\/span>        FOR<\/span> var2 IN 1<\/span>..<\/span>300<\/span>
\n 41<\/span>        LOOP
\n 42<\/span>           l_num :=<\/span> round<\/span><\/a>(<\/span>dbms_random.<\/span>value(<\/span>100<\/span>,<\/span>0<\/span>)<\/span>)<\/span>+<\/span>var_x;<\/span>
\n 43<\/span>           insert into dbtw01 values(<\/span>v_id,<\/span> var_x,<\/span> l_num,<\/span> 'DBTimeWizard - Performance and Tuning'<\/span>)<\/span>;<\/span>
\n 44<\/span>           v_id :=<\/span> v_id +<\/span> 1<\/span>;<\/span>
\n 45<\/span>        END<\/span><\/a> LOOP;<\/span>
\n 46<\/span>        var_x :=<\/span> 17<\/span>;<\/span>
\n 47<\/span>        FOR<\/span> var2 IN 1<\/span>..<\/span>490<\/span>
\n 48<\/span>        LOOP
\n 49<\/span>           l_num :=<\/span> round<\/span><\/a>(<\/span>dbms_random.<\/span>value(<\/span>100<\/span>,<\/span>0<\/span>)<\/span>)<\/span>;<\/span>
\n 50<\/span>           insert into dbtw01 values(<\/span>v_id,<\/span> var_x,<\/span> l_num,<\/span> 'DBTimeWizard - Performance and Tuning'<\/span>)<\/span>;<\/span>
\n 51<\/span>           v_id :=<\/span> v_id +<\/span> 1<\/span>;<\/span>
\n 52<\/span>        END<\/span><\/a> LOOP;<\/span>
\n 53<\/span>        var_x :=<\/span> 19<\/span>;<\/span>
\n 54<\/span>        FOR<\/span> var2 IN 1<\/span>..<\/span>200<\/span>
\n 55<\/span>        LOOP
\n 56<\/span>           insert into dbtw01 values(<\/span>v_id,<\/span> var_x,<\/span> 35<\/span>,<\/span> 'DBTimeWizard - Performance and Tuning'<\/span>)<\/span>;<\/span>
\n 57<\/span>           v_id :=<\/span> v_id +<\/span> 1<\/span>;<\/span>
\n 58<\/span>        END<\/span><\/a> LOOP;<\/span>
\n 59<\/span>        COMMIT;<\/span>
\n 60<\/span>     END<\/span><\/a> LOOP;<\/span>
\n 61<\/span>
\n 62<\/span>  END<\/span><\/a>;<\/span>
\n 63<\/span>  \/<\/span>
\n
\nPL\/<\/span>SQL procedure successfully completed.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> create index dbtw01_idx on dbtw01(<\/span>code100,<\/span> code1k)<\/span> compress 2<\/span>;<\/span>
\n
\nIndex created.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> exec<\/span><\/a> dbms_stats.<\/span>gather_table_stats(<\/span>ownname=><\/span> USER,<\/span> tabname=><\/span> 'DBTW01'<\/span>,<\/span> cascade=><\/span> true<\/span>,<\/span> method_opt=><\/span> 'FOR ALL COLUMNS SIZE SKEWONLY'<\/span>)<\/span>;<\/span>
\n
\nPL\/<\/span>SQL procedure successfully completed.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> select column_name,<\/span> num_distinct,<\/span> histogram from dba_tab_col_statistics where table_name=<\/span>'DBTW01'<\/span>;<\/span>
\n
\nCOLUMN_NAME                    NUM_DISTINCT HISTOGRAM
\n------------------------------<\/span> ------------<\/span> ---------------<\/span>
\nTEXT                                      1<\/span> FREQUENCY
\nCODE100                                 114<\/span> FREQUENCY
\nCODE1K                                    7<\/span> FREQUENCY
\nID                                   120000<\/span> NONE
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Execu\u00e7\u00e3o da consulta<\/span><\/h3>\n

Agora vamos executar uma consulta que vai acessar a tabela criada na etapa anterior:<\/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
<\/div><\/td>
SQL ><\/span> ALTER SESSION SET statistics_level=<\/span>ALL;<\/span>
\n
\nSession altered.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> select \/* dbtw101 *\/<\/span> id,<\/span> code1k,<\/span> code100,<\/span> text
\n  2<\/span>    from dbtw01
\n  3<\/span>   where code1k=<\/span>17<\/span>
\n  4<\/span>     and code100=<\/span>35<\/span>;<\/span>
\n
\n        ID     CODE1K    CODE100 TEXT
\n----------<\/span> ----------<\/span> ----------<\/span> ----------------------------------------<\/span>
\n       598<\/span>         17<\/span>         35<\/span> DBTimeWizard -<\/span> Performance and Tuning
\n       527<\/span>         17<\/span>         35<\/span> DBTimeWizard -<\/span> Performance and Tuning
\n       539<\/span>         17<\/span>         35<\/span> DBTimeWizard -<\/span> Performance and Tuning
\n..............<\/span>
\n    119364<\/span>         17<\/span>         35<\/span> DBTimeWizard -<\/span> Performance and Tuning
\n
\n485<\/span> rows selected.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> column sql_id new_value m_sql_id
\nSQL ><\/span> column child_number new_value m_child_no
\nSQL ><\/span>
\nSQL ><\/span> SELECT sql_id,<\/span> child_number
\n  2<\/span>    FROM v$sql<\/span>
\n  3<\/span>   WHERE sql_text LIKE '%dbtw101%'<\/span>
\n  4<\/span>     AND sql_text NOT LIKE '%v$sql%'<\/span>;<\/span>
\n
\nSQL_ID        CHILD_NUMBER
\n-------------<\/span> ------------<\/span>
\n9sscfjtmkf8q2            0<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/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>
\n
\nPLAN_TABLE_OUTPUT
\n----------------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\nSQL_ID  9sscfjtmkf8q2,<\/span> child number 0<\/span>
\n-------------------------------------<\/span>
\nselect \/* dbtw101 *\/<\/span> id,<\/span> code1k,<\/span> code100,<\/span> text   from dbtw01  where
\ncode1k=:<\/span>"SYS_B_0"<\/span>    and code100=:<\/span>"SYS_B_1"<\/span>
\n
\nPlan hash<\/span><\/a> value:<\/span> 658621498<\/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>   273<\/span> (<\/span>100<\/span>)<\/span>|<\/span>          |<\/span>    485<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>     980<\/span> |<\/span>
\n|*<\/span>  1<\/span> |<\/span>  TABLE ACCESS FULL|<\/span> DBTW01 |<\/span>      1<\/span> |<\/span>   8486<\/span> |<\/span>   406K|<\/span>   273<\/span>   (<\/span>1<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>04<\/span> |<\/span>    485<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>     980<\/span> |<\/span>
\n----------------------------------------------------------------------------------------------------------------------<\/span>
\n
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
\n---------------------------------------------------<\/span>
\n
\n   1<\/span> -<\/span> filter(<\/span>(<\/span>"CODE100"<\/span>=:<\/span>SYS_B_1 AND "CODE1K"<\/span>=:<\/span>SYS_B_0)<\/span>)<\/span>
\n
\n
\n19<\/span> rows selected.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

A consulta retornou 485 linhas e para tal realizou uma opera\u00e7\u00e3o “TABLE ACCESS FULL”, o Otimizador escolheu esta opera\u00e7\u00e3o pois ele estimou que teria que acessar 8.486 linhas e para essa quantidade de linhas o acesso utilizando um \u00edndice seria mais oneroso.<\/span><\/p>\n

 <\/p>\n

Utilizando “Extended statistics”<\/span><\/h3>\n

Agora vamos dar uma pequena ajuda ao Otimizador, para tal vamos criar as “Extended statistics” das duas colunas que est\u00e3o sendo utilizadas na clausula WHERE e executar novamente a mesma consulta:<\/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
<\/div><\/td>
SQL ><\/span> SELECT DBMS_STATS.<\/span>CREATE_EXTENDED_STATS(<\/span>null<\/span>,<\/span>'dbtw01'<\/span>,<\/span> '(code1k, code100)'<\/span>)<\/span> FROM DUAL;<\/span>
\n
\nDBMS_STATS.<\/span>CREATE_EXTENDED_STATS(<\/span>NULL<\/span>,<\/span>'DBTW01'<\/span>,<\/span>'(CODE1K,CODE100)'<\/span>)<\/span>
\n---------------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\nSYS_STUGYIUDX9_XG#MKTWSLK2DJ54
\n<\/span>
\nSQL ><\/span>
\nSQL ><\/span> exec<\/span><\/a> dbms_stats.<\/span>gather_table_stats(<\/span>ownname=><\/span> USER,<\/span> tabname=><\/span> 'DBTW01'<\/span>,<\/span> cascade=><\/span> true<\/span>,<\/span> method_opt=><\/span> 'FOR ALL COLUMNS SIZE SKEWONLY'<\/span>)<\/span>;<\/span>
\n
\nPL\/<\/span>SQL procedure successfully completed.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> select column_name,<\/span> num_distinct,<\/span> histogram from dba_tab_col_statistics where table_name=<\/span>'DBTW01'<\/span>;<\/span>
\n
\nCOLUMN_NAME                    NUM_DISTINCT HISTOGRAM
\n------------------------------<\/span> ------------<\/span> ---------------<\/span>
\nSYS_STUGYIUDX9_XG#MKTWSLK2DJ54          595 HEIGHT BALANCED
\n<\/span>TEXT                                      1<\/span> FREQUENCY
\nCODE100                                 114<\/span> FREQUENCY
\nCODE1K                                    7<\/span> FREQUENCY
\nID                                   120000<\/span> NONE
\n
\nSQL ><\/span>
\nSQL ><\/span> select \/* dbtw102 *\/<\/span> id,<\/span> code1k,<\/span> code100,<\/span> text
\n  2<\/span>    from dbtw01
\n  3<\/span>   where code1k=<\/span>17<\/span>
\n  4<\/span>     and code100=<\/span>35<\/span>;<\/span>
\n
\n        ID     CODE1K    CODE100 TEXT
\n----------<\/span> ----------<\/span> ----------<\/span> ----------------------------------------<\/span>
\n       598<\/span>         17<\/span>         35<\/span> DBTimeWizard -<\/span> Performance and Tuning
\n       527<\/span>         17<\/span>         35<\/span> DBTimeWizard -<\/span> Performance and Tuning
\n       539<\/span>         17<\/span>         35<\/span> DBTimeWizard -<\/span> Performance and Tuning
\n..........<\/span>
\n    119364<\/span>         17<\/span>         35<\/span> DBTimeWizard -<\/span> Performance and Tuning
\n
\n485<\/span> rows selected.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> column sql_id new_value m_sql_id
\nSQL ><\/span> column child_number new_value m_child_no
\nSQL ><\/span>
\nSQL ><\/span> SELECT sql_id,<\/span> child_number
\n  2<\/span>    FROM v$sql<\/span>
\n  3<\/span>   WHERE sql_text LIKE '%dbtw102%'<\/span>
\n  4<\/span>     AND sql_text NOT LIKE '%v$sql%'<\/span>;<\/span>
\n
\nSQL_ID        CHILD_NUMBER
\n-------------<\/span> ------------<\/span>
\n59v6y95ffxr2y            0<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/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>
\n
\nPLAN_TABLE_OUTPUT
\n------------------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\nSQL_ID  59v6y95ffxr2y,<\/span> child number 0<\/span>
\n-------------------------------------<\/span>
\nselect \/* dbtw102 *\/<\/span> id,<\/span> code1k,<\/span> code100,<\/span> text   from dbtw01  where
\ncode1k=:<\/span>"SYS_B_0"<\/span>    and code100=:<\/span>"SYS_B_1"<\/span>
\n
\nPlan hash<\/span><\/a> value:<\/span> 954539352<\/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>    78<\/span> (<\/span>100<\/span>)<\/span>|<\/span>          |<\/span>    485<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>     344<\/span> |<\/span>
\n|<\/span>   1<\/span> |<\/span>  TABLE ACCESS BY INDEX ROWID|<\/span> DBTW01     |<\/span>      1<\/span> |<\/span>    148<\/span> |<\/span>  7400<\/span> |<\/span>    78<\/span>   (<\/span>0<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/span> |<\/span>    485<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>     344<\/span> |<\/span>
\n|*<\/span>  2<\/span> |<\/span>   INDEX RANGE<\/span><\/a> SCAN          |<\/span> DBTW01_IDX |<\/span>      1<\/span> |<\/span>    148<\/span> |<\/span>       |<\/span>     1<\/span>   (<\/span>0<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/span> |<\/span>    485<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>      35<\/span> |<\/span>
\n------------------------------------------------------------------------------------------------------------------------------------<\/span>
\n
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
\n---------------------------------------------------<\/span>
\n
\n   2<\/span> -<\/span> access(<\/span>"CODE100"<\/span>=:<\/span>SYS_B_1 AND "CODE1K"<\/span>=:<\/span>SYS_B_0)<\/span>
\n
\n
\n20<\/span> rows selected.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

A consulta retornou 485 linhas novamente e o Otimizador estimou que seriam 148 linhas, essa estimativa esta muito pr\u00f3xima do valor real, para essa quantidade de linhas a utiliza\u00e7\u00e3o de um \u00edndice \u00e9 mais eficiente, por esta raz\u00e3o ele utilizou o \u00edndice DBTW01_IDX.<\/span><\/p>\n

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

Quando comparamos a quantidade de buffers acessados pelas duas consultas, podemos constatar que a segunda consulta foi mais eficiente, pois enquanto na primeira consulta foram acessados\u00a0980 buffers, na segunda a quantidade caiu para\u00a0344 buffers.<\/span><\/p>\n

A falha na estimativa da cardinalidade da opera\u00e7\u00e3o pelo Otimizador \u00e9 uma das principais raz\u00f5es que levam o Otimizador a criar planos de execu\u00e7\u00e3o ineficientes.<\/span><\/p>\n

As “Extended statistics” podem e devem ser utilizadas sempre que o Otimizador n\u00e3o consegue fazer uma boa estimativa da cardinalidade da opera\u00e7\u00e3o, pois os predicados presentes na clausula WHERE de uma instru\u00e7\u00e3o SQL possuem algum relacionamento ou quando a instru\u00e7\u00e3o SQL utiliza fun\u00e7\u00e3o no predicado.<\/span><\/p>\n

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

https:\/\/blogs.oracle.com\/optimizer\/entry\/extended_statistics<\/a><\/span><\/strong><\/p>\n

https:\/\/docs.oracle.com\/cd\/E11882_01\/server.112\/e41573\/stats.htm#PFGRF94725<\/a><\/span><\/strong><\/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":"

As estat\u00edsticas sobre os dados de uma tabela s\u00e3o fundamentais para o Otimizador escolher qual a maneira mais eficiente de acessar esses dados, mas existem informa\u00e7\u00f5es sobre os dados que n\u00e3o s\u00e3o coletadas de forma autom\u00e1tica e portanto n\u00e3o est\u00e3o dispon\u00edveis para o Otimizador sem que haja uma interven\u00e7\u00e3o manual, o que causa em algumas situa\u00e7\u00f5es a escolha de planos<\/p>\n","protected":false},"author":2,"featured_media":712,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[71],"tags":[129,128,6,42,127,10],"class_list":["post-711","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-estatisticas","tag-cardinalidade","tag-cardinality","tag-cbo","tag-cost-base-optimizer","tag-extended-statistics","tag-otimizador"],"aioseo_notices":[],"_links":{"self":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/711","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=711"}],"version-history":[{"count":23,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/711\/revisions"}],"predecessor-version":[{"id":2097,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/711\/revisions\/2097"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/media\/712"}],"wp:attachment":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/media?parent=711"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/categories?post=711"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/tags?post=711"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}