{"id":1812,"date":"2018-10-21T21:51:27","date_gmt":"2018-10-21T23:51:27","guid":{"rendered":"http:\/\/dbtimewizard.com.br\/blog\/?p=1812"},"modified":"2019-11-11T18:36:26","modified_gmt":"2019-11-11T21:36:26","slug":"como-uma-consulta-pode-ter-alto-desempenho-sem-dados-estatisticos","status":"publish","type":"post","link":"http:\/\/dbtimewizard.com.br\/blog\/como-uma-consulta-pode-ter-alto-desempenho-sem-dados-estatisticos\/","title":{"rendered":"Como uma consulta pode ter alto desempenho sem dados estat\u00edsticos?"},"content":{"rendered":"

O Otimizador do Oracle cria planos de execu\u00e7\u00e3o com alta performance baseado nas estat\u00edsticas dos objetos no banco de dados, com estas informa\u00e7\u00f5es ele consegue fazer estimativas de acesso para cada uma das opera\u00e7\u00f5es no plano de execu\u00e7\u00e3o e a qualidade destas estimativas \u00e9 que determina o desempenho das instru\u00e7\u00f5es SQL, mas o que acontece quando algum objeto envolvido na instru\u00e7\u00e3o SQL n\u00e3o possui estat\u00edsticas? Desde a vers\u00e3o 9i at\u00e9 a vers\u00e3o 11gR2, quando um dos objetos envolvidos na instru\u00e7\u00e3o SQL n\u00e3o possui estat\u00edsticas o Otimizador utiliza o recurso DYNAMIC SAMPLING<\/strong><\/a> para coletar estat\u00edsticas a partir de uma pequena amostra de registros dos objetos durante a montagem do plano de execu\u00e7\u00e3o.<\/span><\/p>\n

Este recurso \u00e9 muito \u00fatil, porem ele n\u00e3o resolve problemas relativos a defici\u00eancias na coleta de estat\u00edsticas como estat\u00edsticas desatualizadas e falta de histogramas. Na vers\u00e3o 12c a Oracle implementou uma melhoria no recurso DYNAMIC SAMPLING e agora o Otimizador tamb\u00e9m consegue contornar problemas de estat\u00edsticas desatualizadas e falta de histogramas, neste artigo vamos fazer 2 simula\u00e7\u00f5es para demonstrar a evolu\u00e7\u00e3o do recurso DYNAMIC SAMPLING, a primeira numa base 11gR2 e a segunda numa base 12cR2.<\/span><\/p>\n

Na primeira simula\u00e7\u00e3o vamos verificar a inefici\u00eancia do recurso DYNAMIC SAMPLING na vers\u00e3o 11gR2 quando as estat\u00edsticas est\u00e3o desatualizadas ou sem histogramas:<\/span><\/p>\n

1.1) Criar uma tabela com um \u00edndice <\/span><\/strong>
\n1.2) Executar uma consulta na tabela criada sem as estat\u00edsticas<\/span><\/strong>
\n1.3) Coletar as estat\u00edsticas e executar a consulta novamente<\/span><\/strong>
\n1.4) Inserir registros na tabela para as estat<\/span>\u00edsticas ficarem desatualizadas<\/span><\/strong>
\n1.5) Executar a consulta novamente<\/span><\/strong><\/p>\n

Na segunda simula\u00e7\u00e3o vamos verificar a melhoria no recurso DYNAMIC SAMPLING na vers\u00e3o 12cR2 quando as estat\u00edsticas est\u00e3o desatualizadas ou sem histogramas :<\/span><\/p>\n

2.1) Criar uma tabela com um \u00edndice <\/span><\/strong>
\n2.2) Executar uma consulta na tabela criada <\/span><\/strong>
\n2.3) Coletar as estat\u00edsticas e executar a consulta novamente<\/span><\/strong>
\n2.4) Inserir registros na tabela para as estat\u00edsticas ficarem desatualizadas<\/span><\/strong>
\n2.5) Executar a consulta novamente<\/span><\/strong><\/p>\n

 <\/p>\n

1.1) Criar uma tabela com um \u00edndice<\/span><\/h2>\n

Vamos criar os objetos necess\u00e1rios para realizar a nossa simula\u00e7\u00e3o e inicialmente n\u00e3o vamos coletar as estat\u00edsticas.<\/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
<\/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 dbtw056 as<\/span>
\n  2<\/span>     select 20<\/span>       as<\/span> codigo,<\/span>
\n  3<\/span>            'DBTimeWizard - Oracle Performance and Tuning'<\/span> as<\/span> produto
\n  4<\/span>       from dual connect by level<=<\/span>9981<\/span>;<\/span>
\n
\nTabela criada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> INSERT INTO dbtw056 select rownum,<\/span> 'DBTimeWizard - Oracle Performance and Tuning'<\/span>
\n  2<\/span>                        from dual connect by level<=<\/span>19<\/span>;<\/span>
\n
\n19<\/span> linhas criadas.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> COMMIT;<\/span>
\n
\nCommit conclu\u00eddo<\/span>.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> create index dbtw056_codigo_idx on dbtw056(<\/span>codigo)<\/span>;<\/span>
\n
\n\u00cdndice criado.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> SELECT table_name,<\/span> stale_stats,<\/span> last_analyzed
\n  2<\/span>    FROM dba_tab_statistics
\n  3<\/span>   WHERE table_name=<\/span>'DBTW056'<\/span>;<\/span>
\n
\nTABLE_NAME                     STA LAST_ANA
\n------------------------------<\/span> ---<\/span> --------<\/span>
\nDBTW056
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

Observamos acima no comando SELECT na vis\u00e3o DBA_TAB_STATISTICS que as estat\u00edsticas n\u00e3o est\u00e3o dispon\u00edveis.<\/span><\/p>\n

 <\/p>\n

1.2) Executar uma consulta na tabela criada sem as estat\u00edsticas<\/span><\/h2>\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>
SQL ><\/span> ALTER SESSION SET optimizer_dynamic_sampling=<\/span>11<\/span>;<\/span>
\n
\nSess\u00e3o alterada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> ALTER SESSION SET statistics_level=<\/span>ALL;<\/span>
\n
\nSess\u00e3o alterada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> SELECT \/* dbtw-056.1 *\/<\/span> *<\/span>
\n  2<\/span>    FROM dbtw056
\n  3<\/span>   WHERE codigo=<\/span>11<\/span>;<\/span>
\n
\n    CODIGO PRODUTO
\n----------<\/span> --------------------------------------------<\/span>
\n        11<\/span> DBTimeWizard -<\/span> Oracle Performance and Tuning
\n
\nSQL ><\/span>
\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 '%dbtw-056.1%'<\/span>
\n  4<\/span>     AND sql_text NOT LIKE '%v$sql%'<\/span>;<\/span>
\n
\nSQL_ID        CHILD_NUMBER
\n-------------<\/span> ------------<\/span>
\n5ychjrxz1g28n            0<\/span>
\n
\nSQL ><\/span>
\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>'ALLSTATS 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>'ALLSTATS LAST'<\/span>)<\/span>)<\/span>
\nnovo   2<\/span>:<\/span>   FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'5ychjrxz1g28n'<\/span>,<\/span>         0<\/span>,<\/span>'ALLSTATS LAST'<\/span>)<\/span>)<\/span>
\n
\nPLAN_TABLE_OUTPUT
\n----------------------------------------------------------------------------------------------------------------------------<\/span>
\n
\nSQL_ID  5ychjrxz1g28n,<\/span> child number 0<\/span>
\n-------------------------------------<\/span>
\nSELECT \/* dbtw-056.1 *\/<\/span> *<\/span>   FROM dbtw056  WHERE codigo=:<\/span>"SYS_B_0"<\/span>
\n
\nPlan hash<\/span><\/a> value:<\/span> 1209234527<\/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>
\n------------------------------------------------------------------------------------------------------------<\/span>
\n|<\/span>   0<\/span> |<\/span> SELECT STATEMENT            |<\/span>                    |<\/span>      1<\/span> |<\/span>        |<\/span>      1<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>       4<\/span> |<\/span>
\n|<\/span>   1<\/span> |<\/span>  TABLE ACCESS BY INDEX ROWID|<\/span> DBTW056            |<\/span>      1<\/span> |<\/span>     67<\/span> |<\/span>      1<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>       4<\/span> |<\/span>
\n|*<\/span>  2<\/span> |<\/span>   INDEX RANGE<\/span><\/a> SCAN          |<\/span> DBTW056_CODIGO_IDX |<\/span>      1<\/span> |<\/span>     67<\/span> |<\/span>      1<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>       3<\/span> |<\/span>
\n------------------------------------------------------------------------------------------------------------<\/span>
\n
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
\n---------------------------------------------------<\/span>
\n
\n   2<\/span> -<\/span> access(<\/span>"CODIGO"<\/span>=:<\/span>SYS_B_0)<\/span>
\n
\nNote
\n-----<\/span>
\n   -<\/span> dynamic sampling used for<\/span> this statement (<\/span>level=<\/span>11<\/span>)<\/span>
\n
\n
\n23<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

Observe no plano de execu\u00e7\u00e3o que o Otimizador estimou que seriam selecionadas 67 linhas na tabela, esta estimativa aproximada foi realizada pelo recurso DYNAMIC SAMPLING pois n\u00e3o havia estat\u00edsticas no dicionario de dados e como a quantidade de linhas estimadas e pequena comparada com a quantidade de linhas na tabela (10.000 linhas) o Otimizador decidiu utilizar o \u00edndice no plano de execu\u00e7\u00e3o.<\/span><\/p>\n

1.3) Coletar as estat\u00edsticas e executar a consulta novamente<\/span><\/h2>\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
<\/div><\/td>
SQL ><\/span> exec<\/span><\/a> dbms_stats.<\/span>gather_table_stats(<\/span>ownname=><\/span> USER,<\/span> tabname=><\/span> 'DBTW056'<\/span>,<\/span> cascade=><\/span> true<\/span>,<\/span> method_opt=><\/span> 'FOR ALL COLUMNS SIZE 1'<\/span>,<\/span>force=><\/span> true<\/span>,<\/span>degree=><\/span> 16<\/span>)<\/span>;<\/span>
\n
\nProcedimento PL\/<\/span>SQL conclu\u00eddo<\/span> com sucesso.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> SELECT column_name AS<\/span> "NAME"<\/span>,<\/span>
\n  2<\/span>         num_distinct AS<\/span> "#DISTINCT"<\/span>,<\/span>
\n  3<\/span>         density AS<\/span> "DENSITY"<\/span>,<\/span>
\n  4<\/span>         num_nulls AS<\/span> "#NULL"<\/span>,<\/span>
\n  5<\/span>         avg_col_len ,<\/span>
\n  6<\/span>         histogram,<\/span>
\n  7<\/span>         num_buckets AS<\/span> "#BUCKETS"<\/span>
\n  8<\/span>    FROM dba_tab_col_statistics
\n  9<\/span>   WHERE table_name =<\/span> 'DBTW056'<\/span>;<\/span>
\n
\nNAME                            #DISTINCT    DENSITY      #NULL AVG_COL_LEN HISTOGRAM         #BUCKETS
\n<\/span>------------------------------<\/span> ----------<\/span> ----------<\/span> ----------<\/span> -----------<\/span> ---------------<\/span> ----------<\/span>
\nPRODUTO                                 1<\/span>          1<\/span>          0<\/span>          45<\/span> NONE                     1<\/span>
\nCODIGO                                 20<\/span>        ,<\/span>05<\/span>          0<\/span>           3<\/span> NONE                     1<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> ALTER SESSION SET optimizer_dynamic_sampling=<\/span>11<\/span>;<\/span>
\n
\nSess\u00e3o alterada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> ALTER SESSION SET statistics_level=<\/span>ALL;<\/span>
\n
\nSess\u00e3o alterada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> SELECT \/* dbtw-056.2 *\/<\/span> *<\/span>
\n  2<\/span>    FROM dbtw056
\n  3<\/span>   WHERE codigo=<\/span>11<\/span>;<\/span>
\n
\n    CODIGO PRODUTO
\n----------<\/span> --------------------------------------------<\/span>
\n        11<\/span> DBTimeWizard -<\/span> Oracle Performance and Tuning
\n
\nSQL ><\/span>
\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 '%dbtw-056.2%'<\/span>
\n  4<\/span>     AND sql_text NOT LIKE '%v$sql%'<\/span>;<\/span>
\n
\nSQL_ID        CHILD_NUMBER
\n-------------<\/span> ------------<\/span>
\ndjrqpaw9dqaxw            0<\/span>
\n
\nSQL ><\/span>
\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>'ALLSTATS LAST'<\/span>)<\/span>)<\/span>;<\/span>
\n
\nPLAN_TABLE_OUTPUT
\n-------------------------------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\nSQL_ID  djrqpaw9dqaxw,<\/span> child number 0<\/span>
\n-------------------------------------<\/span>
\nSELECT \/* dbtw-056.2 *\/<\/span> *<\/span>   FROM dbtw056  WHERE codigo=:<\/span>"SYS_B_0"<\/span>
\n
\nPlan hash<\/span><\/a> value:<\/span> 3528933912<\/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>
\n---------------------------------------------------------------------------------------<\/span>
\n|<\/span>   0<\/span> |<\/span> SELECT STATEMENT  |<\/span>         |<\/span>      1<\/span> |<\/span>        |<\/span>      1<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>      84<\/span> |<\/span>
\n|*<\/span>  1<\/span> |<\/span>  TABLE ACCESS FULL|<\/span> DBTW056 |<\/span>      1<\/span> |<\/span>    500<\/span> |<\/span>      1<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>      84<\/span> |<\/span>
\n---------------------------------------------------------------------------------------<\/span>
\n
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
\n---------------------------------------------------<\/span>
\n
\n   1<\/span> -<\/span> filter(<\/span>"CODIGO"<\/span>=:<\/span>SYS_B_0)<\/span>
\n
\nNote
\n-----<\/span>
\n   -<\/span> dynamic sampling used for<\/span> this statement (<\/span>level=<\/span>11<\/span>)<\/span>
\n
\n
\n22<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

Ap\u00f3s a execu\u00e7\u00e3o da coleta das estat\u00edsticas podemos observar no SELECT na vis\u00e3o DBA_TAB_COL_STATISTICS que n\u00e3o existem histogramas nas duas colunas da tabela, observando o plano de execu\u00e7\u00e3o verificamos que o Otimizador estimou que seriam selecionadas 500 linhas na tabela e por esta raz\u00e3o decidiu fazer uma opera\u00e7\u00e3o TABLE ACCESS FULL. O Otimizador n\u00e3o utilizou o recurso DYNAMIC SAMPLING pois havia estat\u00edsticas da tabela e do \u00edndice no dicionario de dados do banco, mas como n\u00e3o havia HISTOGRAMAS o calculo utilizado para estimar o numero de linhas selecionadas foi: NUMERO DE LINHAS NA TABELA \/ NUMERO DE LINHAS DISTINTAS (10.000 \/ 20 = 500).<\/span><\/p>\n

1.4) Inserir registros na tabela para as estat\u00edsticas ficarem desatualizadas<\/span><\/h2>\n

Vamos inserir 2.000 linhas na tabela (10%) para for\u00e7ar uma situa\u00e7\u00e3o de estat\u00edsticas desatualizadas, na sequ\u00eancia vamos executar a consulta novamente e verificar se o recurso DYNAMIC SAMPLING vai ser utilizado.<\/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
<\/div><\/td>
SQL ><\/span> INSERT INTO dbtw056 select 15<\/span>,<\/span> 'DBTimeWizard - Oracle Performance and Tuning'<\/span>
\n  2<\/span>                        from dual connect by level<=<\/span> 2000<\/span>;<\/span>
\n
\n2000<\/span> linhas criadas.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> COMMIT;<\/span>
\n
\nCommit conclu\u00eddo<\/span>.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> exec<\/span><\/a> dbms_stats.<\/span>FLUSH_DATABASE_MONITORING_INFO(<\/span>)<\/span>;<\/span>
\n
\nProcedimento PL\/<\/span>SQL conclu\u00eddo<\/span> com sucesso.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> SELECT table_name,<\/span> stale_stats,<\/span> last_analyzed
\n  2<\/span>    FROM dba_tab_statistics
\n  3<\/span>   WHERE table_name=<\/span>'DBTW056'<\/span>;<\/span>
\n
\nTABLE_NAME                     STA LAST_ANA
\n------------------------------<\/span> ---<\/span> --------<\/span>
\nDBTW056                        YES 15<\/span>\/<\/span>10<\/span>\/<\/span>18<\/span>
\n
\n1<\/span> linha selecionada.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

Observe no SELECT na vis\u00e3o DBA_TAB_STATISTICS que o campo STALE_STATS esta como YES, isto significa que as estat\u00edsticas est\u00e3o desatualizadas.<\/span><\/p>\n

1.5) Executar a consulta novamente<\/span><\/h2>\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
<\/div><\/td>
SQL ><\/span> ALTER SESSION SET optimizer_dynamic_sampling=<\/span>11<\/span>;<\/span>
\n
\nSess\u00e3o alterada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> ALTER SESSION SET statistics_level=<\/span>ALL;<\/span>
\n
\nSess\u00e3o alterada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> SELECT \/* dbtw-056.3 *\/<\/span> *<\/span>
\n  2<\/span>    FROM dbtw056
\n  3<\/span>   WHERE codigo=<\/span>11<\/span>;<\/span>
\n
\n    CODIGO PRODUTO
\n----------<\/span> --------------------------------------------<\/span>
\n        11<\/span> DBTimeWizard -<\/span> Oracle Performance and Tuning
\n
\nSQL ><\/span>
\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 '%dbtw-056.3%'<\/span>
\n  4<\/span>     AND sql_text NOT LIKE '%v$sql%'<\/span>;<\/span>
\n
\nSQL_ID        CHILD_NUMBER
\n-------------<\/span> ------------<\/span>
\ngvy2nbd3qys8k            0<\/span>
\n
\nSQL ><\/span>
\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>'ALLSTATS LAST'<\/span>)<\/span>)<\/span>;<\/span>
\n
\nPLAN_TABLE_OUTPUT
\n----------------------------------------------------------------------------------------------------------<\/span>
\nSQL_ID  gvy2nbd3qys8k,<\/span> child number 0<\/span>
\n-------------------------------------<\/span>
\nSELECT \/* dbtw-056.3 *\/<\/span> *<\/span>   FROM dbtw056  WHERE codigo=:<\/span>"SYS_B_0"<\/span>
\n
\nPlan hash<\/span><\/a> value:<\/span> 3528933912<\/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>
\n---------------------------------------------------------------------------------------<\/span>
\n|<\/span>   0<\/span> |<\/span> SELECT STATEMENT  |<\/span>         |<\/span>      1<\/span> |<\/span>        |<\/span>      1<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>      99<\/span> |<\/span>
\n|*<\/span>  1<\/span> |<\/span>  TABLE ACCESS FULL|<\/span> DBTW056 |<\/span>      1<\/span> |<\/span>    500<\/span> |<\/span>      1<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>      99<\/span> |<\/span>
\n---------------------------------------------------------------------------------------<\/span>
\n
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
\n---------------------------------------------------<\/span>
\n
\n   1<\/span> -<\/span> filter(<\/span>"CODIGO"<\/span>=:<\/span>SYS_B_0)<\/span>
\n
\nNote
\n-----<\/span>
\n   -<\/span> dynamic sampling used for<\/span> this statement (<\/span>level=<\/span>11<\/span>)<\/span>
\n
\n
\n22<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

Analisando o plano de execu\u00e7\u00e3o da consulta podemos constatar que o Otimizador decidiu acessar a tabela utilizando a opera\u00e7\u00e3o TABLE ACCESS FULL pois a quantidade de linhas estimadas foi 500 novamente, ou seja, quando as estat\u00edsticas est\u00e3o desatualizadas o Otimizador n\u00e3o utiliza o recurso DYNAMIC SAMPLING.<\/span><\/p>\n

 <\/p>\n

Agora vamos acessar uma base Oracle 12cR2 e repetir o mesmo processo para verificar como o Otimizador vai tratar uma tabela com estatisticas desatualizas e sem histogramas.<\/span><\/p>\n

 <\/p>\n

2.1) Criar uma tabela com um \u00edndice<\/span><\/h2>\n

 <\/p>\n

Vamos criar os objetos necess\u00e1rios para realizar a nossa simula\u00e7\u00e3o na base 12cR2 e inicialmente n\u00e3o vamos executar o comando de coleta de estat\u00edsticas.<\/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
83
84
<\/div><\/td>
SQL ><\/span> SELECT BANNER FROM V$VERSION<\/span> where rownum <<\/span> 2<\/span>;<\/span>
\n
\nBANNER
\n--------------------------------------------------------------------------------<\/span>
\nOracle Database 12c Enterprise Edition Release 12.<\/span>2.<\/span>0.<\/span>1.<\/span>0 -<\/span> 64bit Production
\n
\n1<\/span> linha selecionada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> create table dbtw056 as<\/span>
\n  2<\/span>     select 20<\/span>       as<\/span> codigo,<\/span>
\n  3<\/span>            'DBTimeWizard - Oracle Performance and Tuning'<\/span> as<\/span> produto
\n  4<\/span>       from dual connect by level<=<\/span>9981<\/span>;<\/span>
\n
\nTabela criada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> INSERT INTO dbtw056 select rownum,<\/span> 'DBTimeWizard - Oracle Performance and Tuning'<\/span>
\n  2<\/span>                        from dual connect by level<=<\/span>19<\/span>;<\/span>
\n
\n19<\/span> linhas criadas.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> COMMIT;<\/span>
\n
\nCommit conclu\u00eddo<\/span>.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> create index dbtw056_codigo_idx on dbtw056(<\/span>codigo)<\/span>;<\/span>
\n
\n\u00cdndice criado.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> COL table_name FOR<\/span> A40;<\/span>
\nSQL ><\/span> SELECT table_name,<\/span> stale_stats,<\/span> last_analyzed
\n  2<\/span>    FROM dba_tab_statistics
\n  3<\/span>   WHERE table_name=<\/span>'DBTW056'<\/span>;<\/span>
\n
\nTABLE_NAME                               STA LAST_ANA
\n----------------------------------------<\/span> ---<\/span> --------<\/span>
\nDBTW056                                  NO  17<\/span>\/<\/span>10<\/span>\/<\/span>18<\/span>
\n
\n1<\/span> linha selecionada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> SELECT column_name AS<\/span> "NAME"<\/span>,<\/span>
\n  2<\/span>         num_distinct AS<\/span> "#DISTINCT"<\/span>,<\/span>
\n  3<\/span>         density AS<\/span> "DENSITY"<\/span>,<\/span>
\n  4<\/span>         num_nulls AS<\/span> "#NULL"<\/span>,<\/span>
\n  5<\/span>         avg_col_len ,<\/span>
\n  6<\/span>         histogram,<\/span>
\n  7<\/span>         num_buckets AS<\/span> "#BUCKETS"<\/span>
\n  8<\/span>    FROM dba_tab_col_statistics
\n  9<\/span>   WHERE table_name =<\/span> 'DBTW056'<\/span>;<\/span>
\n
\nNAME                                  #DISTINCT      DENSITY      #NULL AVG_COL_LEN HISTOGRAM         #BUCKETS
\n<\/span>------------------------------<\/span> ----------------<\/span> ------------<\/span> ----------<\/span> -----------<\/span> ---------------<\/span> ----------<\/span>
\nCODIGO                                        1<\/span>  1.000000000<\/span>          0<\/span>           3<\/span> NONE                     1<\/span>
\nPRODUTO                                       1<\/span>  1.000000000<\/span>          0<\/span>          45<\/span> NONE                     1<\/span>
\n
\n2<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> COL index_name FOR<\/span> A40;<\/span>
\nSQL ><\/span> SELECT index_name ,<\/span>
\n  2<\/span>         blevel ,<\/span>
\n  3<\/span>         num_rows ,<\/span>
\n  4<\/span>         distinct_keys ,<\/span>
\n  5<\/span>         clustering_factor ,<\/span>
\n  6<\/span>         status,<\/span>
\n  7<\/span>         leaf_blocks ,<\/span>
\n  8<\/span>         last_analyzed
\n  9<\/span>    FROM dba_indexes
\n 10<\/span>   WHERE table_name =<\/span> 'DBTW056'<\/span>
\n 11<\/span>   ORDER by owner,<\/span> index_name;<\/span>
\n
\nINDEX_NAME                                   BLEVEL   NUM_ROWS DISTINCT_KEYS CLUSTERING_FACTOR STATUS     LEAF_BLOCKS LAST_ANA
\n----------------------------------------<\/span> ----------<\/span> ----------<\/span> -------------<\/span> -----------------<\/span> ----------<\/span> -----------<\/span> --------<\/span>
\nDBTW056_CODIGO_IDX                                1<\/span>      10000<\/span>            20<\/span>                75<\/span> VALID               20<\/span> 17<\/span>\/<\/span>10<\/span>\/<\/span>18<\/span>
\n
\n1<\/span> linha selecionada.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

Observamos acima no comando SELECT na vis\u00e3o DBA_TAB_STATISTICS que apesar de n\u00e3o executarmos o comando para coleta de estat\u00edsticas, elas foram coletadas automaticamente pelo Oracle, porem elas est\u00e3o imprecisas pois o campo CODIGO informa que existe somente 1 valor distinto quando na verdade s\u00e3o 20. As estatisticas do \u00edndice foram coletadas de forma precisa.<\/span><\/p>\n

2.2) Executar uma consulta na tabela criada<\/span><\/h2>\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
<\/div><\/td>
SQL ><\/span> ALTER SESSION SET optimizer_dynamic_sampling=<\/span>11<\/span>;<\/span>
\n
\nSess\u00e3o alterada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> ALTER SESSION SET statistics_level=<\/span>ALL;<\/span>
\n
\nSess\u00e3o alterada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> SELECT \/* dbtw-056.1 *\/<\/span> *<\/span>
\n  2<\/span>    FROM dbtw056
\n  3<\/span>   WHERE codigo=<\/span>11<\/span>;<\/span>
\n
\n    CODIGO PRODUTO
\n----------<\/span> --------------------------------------------<\/span>
\n        11<\/span> DBTimeWizard -<\/span> Oracle Performance and Tuning
\n
\n1<\/span> linha selecionada.<\/span>
\n
\nSQL ><\/span>
\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 '%dbtw-056.1%'<\/span>
\n  4<\/span>     AND sql_text NOT LIKE '%v$sql%'<\/span>;<\/span>
\n
\nSQL_ID        CHILD_NUMBER
\n-------------<\/span> ------------<\/span>
\ncfp3qwbjnr0kq            0<\/span>
\n
\n1<\/span> linha selecionada.<\/span>
\n
\nSQL ><\/span>
\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>'ALLSTATS LAST'<\/span>)<\/span>)<\/span>;<\/span>
\n
\nPLAN_TABLE_OUTPUT
\n------------------------------------------------------------------------------------------------------------------------<\/span>
\nSQL_ID  cfp3qwbjnr0kq,<\/span> child number 0<\/span>
\n-------------------------------------<\/span>
\nSELECT \/* dbtw-056.1 *\/<\/span> *<\/span>   FROM dbtw056  WHERE codigo=<\/span>11<\/span>
\n
\nPlan hash<\/span><\/a> value:<\/span> 1201093745<\/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>
\n--------------------------------------------------------------------------------------------------------------------<\/span>
\n|<\/span>   0<\/span> |<\/span> SELECT STATEMENT                    |<\/span>                    |<\/span>      1<\/span> |<\/span>        |<\/span>      1<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>       4<\/span> |<\/span>
\n|<\/span>   1<\/span> |<\/span>  TABLE ACCESS BY INDEX ROWID BATCHED|<\/span> DBTW056            |<\/span>      1<\/span> |<\/span>      1<\/span> |<\/span>      1<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>       4<\/span> |<\/span>
\n|*<\/span>  2<\/span> |<\/span>   INDEX RANGE<\/span><\/a> SCAN                  |<\/span> DBTW056_CODIGO_IDX |<\/span>      1<\/span> |<\/span>      1<\/span> |<\/span>      1<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>       3<\/span> |<\/span>
\n--------------------------------------------------------------------------------------------------------------------<\/span>
\n
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
\n---------------------------------------------------<\/span>
\n
\n   2<\/span> -<\/span> access(<\/span>"CODIGO"<\/span>=<\/span>11<\/span>)<\/span>
\n
\nNote
\n-----<\/span>
\n   -<\/span> dynamic statistics used:<\/span> dynamic sampling (<\/span>level=<\/span>AUTO)<\/span>
\n
\n
\n23<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

Observe no plano de execu\u00e7\u00e3o que apesar das estat\u00edsticas serem imprecisas o Otimizador estimou com precis\u00e3o que seria selecionada 1 linha na tabela, esta estimativa foi poss\u00edvel pois o recurso DYNAMIC SAMPLING foi utilizado e o Otimizador decidiu utilizar o \u00edndice no plano de execu\u00e7\u00e3o.<\/span><\/p>\n

2.3) Coletar as estat\u00edsticas e executar a consulta novamente<\/span><\/h2>\n

<\/h2>\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
<\/div><\/td>
SQL ><\/span> exec<\/span><\/a> dbms_stats.<\/span>gather_table_stats(<\/span>ownname=><\/span> USER,<\/span> tabname=><\/span> 'DBTW056'<\/span>,<\/span> cascade=><\/span> true<\/span>,<\/span> method_opt=><\/span> 'FOR ALL COLUMNS SIZE 1'<\/span>,<\/span>force=><\/span> true<\/span>,<\/span>degree=><\/span> 16<\/span>)<\/span>;<\/span>
\n
\nProcedimento PL\/<\/span>SQL conclu\u00eddo<\/span> com sucesso.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> SELECT column_name AS<\/span> "NAME"<\/span>,<\/span>
\n  2<\/span>         num_distinct AS<\/span> "#DISTINCT"<\/span>,<\/span>
\n  3<\/span>         density AS<\/span> "DENSITY"<\/span>,<\/span>
\n  4<\/span>         num_nulls AS<\/span> "#NULL"<\/span>,<\/span>
\n  5<\/span>         avg_col_len ,<\/span>
\n  6<\/span>         histogram,<\/span>
\n  7<\/span>         num_buckets AS<\/span> "#BUCKETS"<\/span>
\n  8<\/span>    FROM dba_tab_col_statistics
\n  9<\/span>   WHERE table_name =<\/span> 'DBTW056'<\/span>;<\/span>
\n
\nNAME                                  #DISTINCT      DENSITY      #NULL AVG_COL_LEN HISTOGRAM         #BUCKETS
\n<\/span>------------------------------<\/span> ----------------<\/span> ------------<\/span> ----------<\/span> -----------<\/span> ---------------<\/span> ----------<\/span>
\nCODIGO                                       20<\/span>   .050000000<\/span>          0<\/span>           3<\/span> NONE                     1<\/span>
\nPRODUTO                                       1<\/span>  1.000000000<\/span>          0<\/span>          45<\/span> NONE                     1<\/span>
\n
\n2<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> ALTER SESSION SET optimizer_dynamic_sampling=<\/span>11<\/span>;<\/span>
\n
\nSess\u00e3o alterada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> ALTER SESSION SET statistics_level=<\/span>ALL;<\/span>
\n
\nSess\u00e3o alterada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> SELECT \/* dbtw-056.2 *\/<\/span> *<\/span>
\n  2<\/span>    FROM dbtw056
\n  3<\/span>   WHERE codigo=<\/span>11<\/span>;<\/span>
\n
\n    CODIGO PRODUTO
\n----------<\/span> --------------------------------------------<\/span>
\n        11<\/span> DBTimeWizard -<\/span> Oracle Performance and Tuning
\n
\n1<\/span> linha selecionada.<\/span>
\n
\nSQL ><\/span>
\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 '%dbtw-056.2%'<\/span>
\n  4<\/span>     AND sql_text NOT LIKE '%v$sql%'<\/span>;<\/span>
\n
\nSQL_ID        CHILD_NUMBER
\n-------------<\/span> ------------<\/span>
\nd4krxbj892rwr            0<\/span>
\n
\n1<\/span> linha selecionada.<\/span>
\n
\nSQL ><\/span>
\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>'ALLSTATS LAST'<\/span>)<\/span>)<\/span>;<\/span>
\n
\nPLAN_TABLE_OUTPUT
\n---------------------------------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\nSQL_ID  d4krxbj892rwr,<\/span> child number 0<\/span>
\n-------------------------------------<\/span>
\nSELECT \/* dbtw-056.2 *\/<\/span> *<\/span>   FROM dbtw056  WHERE codigo=<\/span>11<\/span>
\n
\nPlan hash<\/span><\/a> value:<\/span> 1201093745<\/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>
\n--------------------------------------------------------------------------------------------------------------------<\/span>
\n|<\/span>   0<\/span> |<\/span> SELECT STATEMENT                    |<\/span>                    |<\/span>      1<\/span> |<\/span>        |<\/span>      1<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>       4<\/span> |<\/span>
\n|<\/span>   1<\/span> |<\/span>  TABLE ACCESS BY INDEX ROWID BATCHED|<\/span> DBTW056            |<\/span>      1<\/span> |<\/span>      1<\/span> |<\/span>      1<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>       4<\/span> |<\/span>
\n|*<\/span>  2<\/span> |<\/span>   INDEX RANGE<\/span><\/a> SCAN                  |<\/span> DBTW056_CODIGO_IDX |<\/span>      1<\/span> |<\/span>      1<\/span> |<\/span>      1<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>       3<\/span> |<\/span>
\n--------------------------------------------------------------------------------------------------------------------<\/span>
\n
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
\n---------------------------------------------------<\/span>
\n
\n   2<\/span> -<\/span> access(<\/span>"CODIGO"<\/span>=<\/span>11<\/span>)<\/span>
\n
\nNote
\n-----<\/span>
\n   -<\/span> dynamic statistics used:<\/span> dynamic sampling (<\/span>level=<\/span>AUTO)<\/span>
\n
\n
\n23<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

Ap\u00f3s a execu\u00e7\u00e3o da coleta das estat\u00edsticas podemos observar no SELECT na vis\u00e3o DBA_TAB_COL_STATISTICS que o numero de linhas distintas agora esta correto (20 linhas) porem n\u00e3o existem histogramas nas duas colunas da tabela, observando o plano de execu\u00e7\u00e3o verificamos que o Otimizador estimou o numero de linhas de forma precisa novamente e criou um plano de execu\u00e7\u00e3o com acesso a tabela utilizando o \u00edndice. Para estimar a cardinalidade da opera\u00e7\u00e3o de acesso a tabela corretamente sem a ajuda de HISTOGRAMAS o Otimizador utilizou o recurso DYNAMIC SAMPLING.<\/span><\/p>\n

2.4) Inserir registros na tabela para as estat\u00edsticas ficarem desatualizadas<\/span><\/h2>\n

<\/h2>\n

Vamos executar o comando de coleta de estat\u00edsticas novamente para criar um HISTOGRAMA na coluna C\u00d3DIGO da tabela e na sequencia vamos inserir 2.000 linhas na tabela (10%) para for\u00e7ar uma situa\u00e7\u00e3o de estat\u00edsticas desatualizadas e inserir 4 linhas com o valor 11 na coluna C\u00d3DIGO\u00a0que esta sendo utilizada no filtro da cl\u00e1usula WHERE, na sequ\u00eancia vamos executar a consulta novamente e verificar se o recurso DYNAMIC SAMPLING vai ser utilizado.<\/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
<\/div><\/td>
SQL ><\/span> exec<\/span><\/a> dbms_stats.<\/span>gather_table_stats(<\/span>ownname=><\/span> USER,<\/span> tabname=><\/span> 'DBTW056'<\/span>,<\/span> cascade=><\/span> true<\/span>,<\/span> method_opt=><\/span> 'FOR ALL COLUMNS SIZE AUTO'<\/span>,<\/span>force=><\/span> true<\/span>,<\/span>degree=><\/span> 16<\/span>)<\/span>;<\/span>
\n
\nProcedimento PL\/<\/span>SQL conclu\u00eddo<\/span> com sucesso.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> SELECT column_name AS<\/span> "NAME"<\/span>,<\/span>
\n  2<\/span>         num_distinct AS<\/span> "#DISTINCT"<\/span>,<\/span>
\n  3<\/span>         density AS<\/span> "DENSITY"<\/span>,<\/span>
\n  4<\/span>         num_nulls AS<\/span> "#NULL"<\/span>,<\/span>
\n  5<\/span>         avg_col_len ,<\/span>
\n  6<\/span>         histogram,<\/span>
\n  7<\/span>         num_buckets AS<\/span> "#BUCKETS"<\/span>
\n  8<\/span>    FROM dba_tab_col_statistics
\n  9<\/span>   WHERE table_name =<\/span> 'DBTW056'<\/span>;<\/span>
\n
\nNAME                 #DISTINCT    DENSITY      #NULL AVG_COL_LEN HISTOGRAM         #BUCKETS
\n<\/span>-------------------<\/span> ----------<\/span> ----------<\/span> ----------<\/span> -----------<\/span> ---------------<\/span> ----------<\/span>
\nCODIGO                      20<\/span>     ,<\/span>00005<\/span>          0<\/span>           3<\/span> FREQUENCY               20<\/span>
\nPRODUTO                      1<\/span>          1<\/span>          0<\/span>          45<\/span> NONE                     1<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> INSERT INTO dbtw056 select 15<\/span>,<\/span> 'DBTimeWizard - Oracle Performance and Tuning'<\/span>
\n  2<\/span>                        from dual connect by level<=<\/span> 2000<\/span>;<\/span>
\n
\n2000<\/span> linhas criadas.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> INSERT INTO dbtw056 select 11<\/span>,<\/span> 'DBTimeWizard - Oracle Performance and Tuning'<\/span>
\n  2<\/span>                        from dual connect by level<=<\/span> 4<\/span>;<\/span>
\n
\n4<\/span> linhas criadas.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> COMMIT;<\/span>
\n
\nCommit conclu\u00eddo<\/span>.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> exec<\/span><\/a> dbms_stats.<\/span>FLUSH_DATABASE_MONITORING_INFO(<\/span>)<\/span>;<\/span>
\n
\nProcedimento PL\/<\/span>SQL conclu\u00eddo<\/span> com sucesso.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> SELECT table_name,<\/span> stale_stats,<\/span> last_analyzed
\n  2<\/span>    FROM dba_tab_statistics
\n  3<\/span>   WHERE table_name=<\/span>'DBTW056'<\/span>;<\/span>
\n
\nTABLE_NAME                               STA LAST_ANA
\n----------------------------------------<\/span> ---<\/span> --------<\/span>
\nDBTW056                                  YES 18<\/span>\/<\/span>10<\/span>\/<\/span>18<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

Observe no select na vis\u00e3o DBA_TAB_COL_STATISTICS que o HISTOGRAMA foi criado na coluna C\u00d3DIGO e no SELECT na vis\u00e3o DBA_TAB_STATISTICS que o campo STALE_STATS esta como YES o que indica que as estat\u00edsticas est\u00e3o desatualizadas.<\/span><\/p>\n

 <\/p>\n

2.5) Executar a consulta novamente<\/span><\/h2>\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
<\/div><\/td>
SQL ><\/span> ALTER SESSION SET optimizer_dynamic_sampling=<\/span>11<\/span>;<\/span>
\n
\nSess\u00e3o alterada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> ALTER SESSION SET statistics_level=<\/span>ALL;<\/span>
\n
\nSess\u00e3o alterada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> SELECT \/* dbtw-056.3 *\/<\/span> *<\/span>
\n  2<\/span>    FROM dbtw056
\n  3<\/span>   WHERE codigo=<\/span>11<\/span>;<\/span>
\n
\n    CODIGO PRODUTO
\n----------<\/span> --------------------------------------------<\/span>
\n        11<\/span> DBTimeWizard -<\/span> Oracle Performance and Tuning
\n        11<\/span> DBTimeWizard -<\/span> Oracle Performance and Tuning
\n        11<\/span> DBTimeWizard -<\/span> Oracle Performance and Tuning
\n        11<\/span> DBTimeWizard -<\/span> Oracle Performance and Tuning
\n        11<\/span> DBTimeWizard -<\/span> Oracle Performance and Tuning
\n
\nSQL ><\/span>
\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 '%dbtw-056.3%'<\/span>
\n  4<\/span>     AND sql_text NOT LIKE '%v$sql%'<\/span>;<\/span>
\n
\nSQL_ID        CHILD_NUMBER
\n-------------<\/span> ------------<\/span>
\nfnytft0gnc6s2            0<\/span>
\n
\nSQL ><\/span>
\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>'ALLSTATS 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>'ALLSTATS LAST'<\/span>)<\/span>)<\/span>
\nnovo   2<\/span>:<\/span>   FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'fnytft0gnc6s2'<\/span>,<\/span>         0<\/span>,<\/span>'ALLSTATS LAST'<\/span>)<\/span>)<\/span>
\n
\nPLAN_TABLE_OUTPUT
\n---------------------------------------------------------------------------------------------------------------------------------------<\/span>
\nSQL_ID  fnytft0gnc6s2,<\/span> child number 0<\/span>
\n-------------------------------------<\/span>
\nSELECT \/* dbtw-056.3 *\/<\/span> *<\/span>   FROM dbtw056  WHERE codigo=<\/span>11<\/span>
\n
\nPlan hash<\/span><\/a> value:<\/span> 1201093745<\/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>
\n--------------------------------------------------------------------------------------------------------------------<\/span>
\n|<\/span>   0<\/span> |<\/span> SELECT STATEMENT                    |<\/span>                    |<\/span>      1<\/span> |<\/span>        |<\/span>      5<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>       5<\/span> |<\/span>
\n|<\/span>   1<\/span> |<\/span>  TABLE ACCESS BY INDEX ROWID BATCHED|<\/span> DBTW056            |<\/span>      1<\/span> |<\/span>      5<\/span> |<\/span>      5<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>       5<\/span> |<\/span>
\n|*<\/span>  2<\/span> |<\/span>   INDEX RANGE<\/span><\/a> SCAN                  |<\/span> DBTW056_CODIGO_IDX |<\/span>      1<\/span> |<\/span>      5<\/span> |<\/span>      5<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>       3<\/span> |<\/span>
\n--------------------------------------------------------------------------------------------------------------------<\/span>
\n
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
\n---------------------------------------------------<\/span>
\n
\n   2<\/span> -<\/span> access(<\/span>"CODIGO"<\/span>=<\/span>11<\/span>)<\/span>
\n
\nNote
\n-----<\/span>
\n   -<\/span> dynamic statistics used:<\/span> dynamic sampling (<\/span>level=<\/span>AUTO)<\/span>
\n
\n
\n23<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

Analisando o plano de execu\u00e7\u00e3o acima podemos constatar que o Otimizador estimou com precis\u00e3o que seriam selecionadas 5 linhas na tabela, esta estimativa n\u00e3o foi obtida no HISTOGRAMA da coluna C\u00d3DIGO pois foram inseridas 4 linhas ap\u00f3s a coleta do HISTOGRAMA, ou seja, o recurso DYNAMIC SAMPLING foi utilizado novamente pois as estat\u00edsticas estavam desatualizadas.<\/span><\/p>\n

CONCLUS\u00c3O<\/span><\/h2>\n

 <\/p>\n

O recurso DYNAMIC SAMPLING na vers\u00e3o 12cR2 ficou muito robusto, com ele o Otimizador passou a ser capaz de identificar v\u00e1rias situa\u00e7\u00f5es onde as estat\u00edsticas dos objetos do banco s\u00e3o insuficientes para garantir uma estimativa de cardinalidade satisfatoria. Esta \u00e9 apenas uma das muitas melhorias implementadas no Oracle 12cR2 visando melhorar a qualidade dos planos de execu\u00e7\u00e3o gerados pelo Otimizador.<\/span><\/p>\n

 <\/p>\n

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

https:\/\/docs.oracle.com\/en\/database\/oracle\/oracle-database\/12.2<\/a><\/strong>
\n 
\n 
\n 
\n\"Promo-D75L\"<\/a><\/p>\n

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

O Otimizador do Oracle cria planos de execu\u00e7\u00e3o com alta performance baseado nas estat\u00edsticas dos objetos no banco de dados, com estas informa\u00e7\u00f5es ele consegue fazer estimativas de acesso para cada uma das opera\u00e7\u00f5es no plano de execu\u00e7\u00e3o e a qualidade destas estimativas \u00e9 que determina o desempenho das instru\u00e7\u00f5es SQL, mas o que acontece quando algum objeto envolvido na<\/p>\n","protected":false},"author":2,"featured_media":1813,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[71],"tags":[163,103,39,162,161,82,10,205,119],"class_list":["post-1812","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-estatisticas","tag-dbms_stats","tag-dynamic-sampling","tag-estatisticas","tag-histogramas","tag-histograms","tag-optimizer","tag-otimizador","tag-stale-statistics","tag-statistics"],"aioseo_notices":[],"_links":{"self":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/1812","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=1812"}],"version-history":[{"count":31,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/1812\/revisions"}],"predecessor-version":[{"id":2072,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/1812\/revisions\/2072"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/media\/1813"}],"wp:attachment":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/media?parent=1812"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/categories?post=1812"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/tags?post=1812"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}