{"id":2133,"date":"2020-02-26T10:05:15","date_gmt":"2020-02-26T13:05:15","guid":{"rendered":"http:\/\/dbtimewizard.com.br\/blog\/?p=2133"},"modified":"2020-02-26T10:05:15","modified_gmt":"2020-02-26T13:05:15","slug":"como-o-hybrid-histogram-contribui-para-deixar-o-otimizador-mais-inteligente","status":"publish","type":"post","link":"http:\/\/dbtimewizard.com.br\/blog\/como-o-hybrid-histogram-contribui-para-deixar-o-otimizador-mais-inteligente\/","title":{"rendered":"Como o Hybrid Histogram contribui para deixar o Otimizador mais inteligente?"},"content":{"rendered":"

O Otimizador do Oracle cria planos de execu\u00e7\u00e3o baseado nas estat\u00edsticas dos objetos no banco de dados, a qualidade destas informa\u00e7\u00f5es \u00e9 decisiva na precis\u00e3o das estimativas de acesso das opera\u00e7\u00f5es no plano de execu\u00e7\u00e3o e consequentemente determinam o desempenho das instru\u00e7\u00f5es SQL. Os Histogramas<\/strong> <\/a>s\u00e3o parte destas estat\u00edsticas e at\u00e9 a vers\u00e3o 11g existiam somente 2 tipos (FREQUENCY e HEIGHT BALANCED), Estes dois tipos de Histogramas n\u00e3o conseguiam prover o Otimizador com estat\u00edsticas de qualidade para algumas situa\u00e7\u00f5es de conjuntos de dados, por esta raz\u00e3o a partir da vers\u00e3o 12c o Banco de dados Oracle passou a utilizar mais dois tipos de Histogramas (TOP FREQUENCY e HYBRID).<\/span><\/p>\n

Neste artigo vamos mostrar como o novo Histograma HYBRID \u00e9 mais preciso, para isto vamos comparar a execu\u00e7\u00e3o de uma consulta com o mesmo conjunto de dados em duas bases, na base 11g a consulta vai utilizar o Histograma HEIGHT BALANCED e na base 19c ela vai utilizar o Histograma HYBRID que ganhou este nome pois ele combina as caracter\u00edsticas dos Histogramas FREQUENCY e HEIGHT BALANCED.<\/span><\/p>\n

DEMONSTRA\u00c7\u00c3O\u00a0PR\u00c1TICA<\/span><\/h2>\n

A\u00a0primeira simula\u00e7\u00e3o ser\u00e1 realizada numa\u00a0base 11gR2 onde vamos verificar que o Otimizador utiliza o Histograma HEIGHT BALANCED\u00a0e faz uma estimativa de cardinalidade imprecisa e como consequ\u00eancia escolhe um m\u00e9todo de acesso inadequado:<\/span><\/p>\n

1.1) Criar uma tabela com um \u00edndice <\/span><\/strong>
\n1.2) Executar a consulta na tabela criada<\/span><\/strong>
\n1.3) Executar a consulta novamente incluindo um HINT para corrigir a estimativa de cardinalidade<\/span><\/strong><\/p>\n

A\u00a0segunda simula\u00e7\u00e3o ser\u00e1 realizada numa base 19c onde vamos verificar que o Otimizador utiliza o Histograma HYBRID o que melhora a estimativa de cardinalidade\u00a0de acesso a tabela\u00a0e como consequ\u00eancia\u00a0ser\u00e1 escolhido um plano de execu\u00e7\u00e3o com um m\u00e9todo de acesso mais eficiente.<\/span><\/p>\n

2.1) Criar uma tabela com um \u00edndice <\/span><\/strong>
\n2.2) Executar uma consulta na tabela criada<\/span><\/strong><\/p>\n

Base\u00a011gR2<\/span><\/h2>\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 coletar as estat\u00edsticas de forma que seja criado o Histograma para a coluna CUST_CITY que \u00e9 utilizada como filtro na cl\u00e1usula WHERE.<\/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
<\/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 dbtw063
\n  2<\/span>     AS<\/span> SELECT *<\/span>
\n  3<\/span>          FROM sh.<\/span>customers
\n  4<\/span>      ORDER BY country_id;<\/span>
\n
\nTabela criada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> CREATE index dbtw063_idx
\n  2<\/span>      ON dbtw063(<\/span>cust_city)<\/span>;<\/span>
\n
\n\u00cdndice criado.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> EXEC<\/span><\/a> DBMS_STATS.<\/span>GATHER_TABLE_STATS(<\/span>user,<\/span>'DBTW063'<\/span>,<\/span>method_opt=><\/span>'for columns cust_city size 254'<\/span>)<\/span>;<\/span>
\n
\nProcedimento PL\/<\/span>SQL conclu\u00eddo<\/span> com sucesso.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> COL column_name FOR<\/span> A16;<\/span>
\nSQL ><\/span>
\nSQL ><\/span> SELECT column_name,<\/span>
\n  2<\/span>         num_distinct,<\/span>
\n  3<\/span>         density,<\/span>
\n  4<\/span>         num_nulls,<\/span>
\n  5<\/span>         avg_col_len ,<\/span>
\n  6<\/span>         histogram,<\/span>
\n  7<\/span>         num_buckets
\n  8<\/span>    FROM user_tab_col_statistics
\n  9<\/span>   WHERE table_name =<\/span> 'DBTW063'<\/span>
\n 10<\/span>     AND column_name =<\/span> 'CUST_CITY'<\/span>;<\/span>
\n
\nCOLUMN_NAME      NUM_DISTINCT    DENSITY  NUM_NULLS AVG_COL_LEN HISTOGRAM       NUM_BUCKETS
\n----------------<\/span> ------------<\/span> ----------<\/span> ----------<\/span> -----------<\/span> ---------------<\/span> -----------<\/span>
\nCUST_CITY                 620<\/span> ,<\/span>002212389<\/span>          0<\/span>          10<\/span> HEIGHT BALANCED         254<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Observamos acima que a consulta na vis\u00e3o USER_TAB_COL_STATISTICS confirma a cria\u00e7\u00e3o do histograma HEIGHT BALANCED na coluna CUST_CITY.<\/span><\/p>\n

1.2) Executar uma consulta na tabela criada<\/span><\/h2>\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
<\/div><\/td>
SQL ><\/span> ALTER SESSION SET statistics_level =<\/span> all;<\/span>
\n
\nSess\u00e3o alterada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> SELECT cust_city,<\/span> country_id,<\/span> count<\/span><\/a>(<\/span>1<\/span>)<\/span>
\n  2<\/span>    FROM dbtw063
\n  3<\/span>   WHERE cust_city IN (<\/span>'Tokyo'<\/span>,<\/span>'Arnhem'<\/span>)<\/span>
\n  4<\/span>   GROUP by cust_city,<\/span> country_id;<\/span>
\n
\nCUST_CITY                      COUNTRY_ID   COUNT<\/span><\/a>(<\/span>1<\/span>)<\/span>
\n------------------------------<\/span> ----------<\/span> ----------<\/span>
\nTokyo                               52782<\/span>         16<\/span>
\nArnhem                              52770<\/span>         17<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> SELECT *<\/span> FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor(<\/span>format=><\/span>'allstats last'<\/span>)<\/span>)<\/span>;<\/span>
\n
\nPLAN_TABLE_OUTPUT
\n------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\nSQL_ID  gsp9gna1k97nn,<\/span> child number 0<\/span>
\n-------------------------------------<\/span>
\nSELECT cust_city,<\/span> country_id,<\/span> count<\/span><\/a>(<\/span>:<\/span>"SYS_B_0"<\/span>)<\/span>   FROM dbtw063  WHERE
\ncust_city IN (<\/span>:<\/span>"SYS_B_1"<\/span>,:<\/span>"SYS_B_2"<\/span>)<\/span>  GROUP by cust_city,<\/span> country_id
\n
\nPlan hash<\/span><\/a> value:<\/span> 1469487015<\/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>  OMem |<\/span>  1Mem |<\/span> Used-<\/span>Mem |<\/span>
\n-------------------------------------------------------------------------------------------------------------------<\/span>
\n|<\/span>   0<\/span> |<\/span> SELECT STATEMENT   |<\/span>         |<\/span>      1<\/span> |<\/span>        |<\/span>      2<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>    1457<\/span> |<\/span>       |<\/span>       |<\/span>          |<\/span>
\n|<\/span>   1<\/span> |<\/span>  HASH<\/span><\/a> GROUP BY     |<\/span>         |<\/span>      1<\/span> |<\/span>      2<\/span> |<\/span>      2<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>    1457<\/span> |<\/span>  1301K|<\/span>  1301K|<\/span>  599K (<\/span>0<\/span>)<\/span>|<\/span>
\n|*<\/span>  2<\/span> |<\/span>   TABLE ACCESS FULL|<\/span> DBTW063 |<\/span>      1<\/span> |<\/span>    133<\/span> |<\/span>     33<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>    1457<\/span> |<\/span>       |<\/span>       |<\/span>          |<\/span>
\n-------------------------------------------------------------------------------------------------------------------<\/span>
\n
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
\n---------------------------------------------------<\/span>
\n
\n   2<\/span> -<\/span> filter(<\/span>(<\/span>"CUST_CITY"<\/span>=:<\/span>SYS_B_1 OR "CUST_CITY"<\/span>=:<\/span>SYS_B_2)<\/span>)<\/span>
\n
\n
\n20<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Observe no plano de execu\u00e7\u00e3o que o Otimizador estimou que seriam selecionadas 133 linhas na tabela (Coluna E-Rows), quando na verdade foram acessadas somente 33 linhas (Coluna A-Rows). Devido a esta estimativa imprecisa o Otimizador decide utilizar a opera\u00e7\u00e3o TABLE ACCESS FULL para acessar as linhas da tabela, isto tornou o plano de execu\u00e7\u00e3o ineficiente pois para acessar 33 linhas da tabela ele poderia utilizar o \u00edndice que consumiria menos recursos do banco.<\/span><\/p>\n

1.3) Executar a consulta novamente incluindo um HINT<\/span><\/h2>\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
<\/div><\/td>
SQL ><\/span> SELECT \/*+ OPT_ESTIMATE(INDEX_SCAN DBTW063 DBTW063_IDX ROWS=33) *\/<\/span> cust_city,<\/span> country_id,<\/span> count<\/span><\/a>(<\/span>1<\/span>)<\/span>
\n  2<\/span>    FROM dbtw063
\n  3<\/span>   WHERE cust_city IN (<\/span>'Tokyo'<\/span>,<\/span>'Arnhem'<\/span>)<\/span>
\n  4<\/span>   GROUP by cust_city,<\/span> country_id;<\/span>
\n
\nCUST_CITY                      COUNTRY_ID   COUNT<\/span><\/a>(<\/span>1<\/span>)<\/span>
\n------------------------------<\/span> ----------<\/span> ----------<\/span>
\nTokyo                               52782<\/span>         16<\/span>
\nArnhem                              52770<\/span>         17<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> SELECT *<\/span> FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor(<\/span>format=><\/span>'allstats last'<\/span>)<\/span>)<\/span>;<\/span>
\n
\nPLAN_TABLE_OUTPUT
\n------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\nSQL_ID  6rvn8phxqz613,<\/span> child number 0<\/span>
\n-------------------------------------<\/span>
\nSELECT \/*+ OPT_ESTIMATE(INDEX_SCAN DBTW063 DBTW063_IDX ROWS=33) *\/<\/span>
\ncust_city,<\/span> country_id,<\/span> count<\/span><\/a>(<\/span>:<\/span>"SYS_B_0"<\/span>)<\/span>   FROM dbtw063  WHERE
\ncust_city IN (<\/span>:<\/span>"SYS_B_1"<\/span>,:<\/span>"SYS_B_2"<\/span>)<\/span>  GROUP by cust_city,<\/span> country_id
\n
\nPlan hash<\/span><\/a> value:<\/span> 2102246616<\/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>  OMem |<\/span>  1Mem |<\/span> Used-<\/span>Mem |<\/span>
\n----------------------------------------------------------------------------------------------------------------------------------<\/span>
\n|<\/span>   0<\/span> |<\/span> SELECT STATEMENT              |<\/span>             |<\/span>      1<\/span> |<\/span>        |<\/span>      2<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>      31<\/span> |<\/span>       |<\/span>       |<\/span>          |<\/span>
\n|<\/span>   1<\/span> |<\/span>  HASH<\/span><\/a> GROUP BY                |<\/span>             |<\/span>      1<\/span> |<\/span>      2<\/span> |<\/span>      2<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>      31<\/span> |<\/span>  1301K|<\/span>  1301K|<\/span>  604K (<\/span>0<\/span>)<\/span>|<\/span>
\n|<\/span>   2<\/span> |<\/span>   INLIST ITERATOR             |<\/span>             |<\/span>      1<\/span> |<\/span>        |<\/span>     33<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>      31<\/span> |<\/span>       |<\/span>       |<\/span>          |<\/span>
\n|<\/span>   3<\/span> |<\/span>    TABLE ACCESS BY INDEX ROWID|<\/span> DBTW063     |<\/span>      2<\/span> |<\/span>    133<\/span> |<\/span>     33<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>      31<\/span> |<\/span>       |<\/span>       |<\/span>          |<\/span>
\n|*<\/span>  4<\/span> |<\/span>     INDEX RANGE<\/span><\/a> SCAN          |<\/span> DBTW063_IDX |<\/span>      2<\/span> |<\/span>     33<\/span> |<\/span>     33<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>       5<\/span> |<\/span>       |<\/span>       |<\/span>          |<\/span>
\n----------------------------------------------------------------------------------------------------------------------------------<\/span>
\n
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
\n---------------------------------------------------<\/span>
\n
\n   4<\/span> -<\/span> access(<\/span>(<\/span>"CUST_CITY"<\/span>=:<\/span>SYS_B_1 OR "CUST_CITY"<\/span>=:<\/span>SYS_B_2)<\/span>)<\/span>
\n
\n
\n23<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Incluindo o HINT OPT_ESTIMATE na consulta, nos informamos ao otimizador que a cardinalidade da opera\u00e7\u00e3o de acesso a tabela usando o \u00edndice selecionaria 33 linhas, com isso o Otimizador decide acessar a tabela utilizando o \u00edndice e o desempenho da consulta melhora 98% pois na primeira consulta o banco acessou 1.457 blocos para selecionar as 33 linhas e com o HINT ele acessou somente 31 blocos.<\/span>
\nObserve que a estimativa de cardinalidade da opera\u00e7\u00e3o de acesso a tabela continua como 133 linhas, pois n\u00e3o inclu\u00edmos um HINT para esta opera\u00e7\u00e3o e o Otimizador continua fazendo a estimativa baseado no Histograma HEIGHT BALANCED.<\/span><\/p>\n

Base\u00a019c<\/span><\/h2>\n

Agora vamos acessar uma base Oracle 19c e repetir o mesmo processo, mas agora o Otimizador vai utilizar o Histograma Hybrid pois a partir da vers\u00e3o 12c o banco j\u00e1 passou a trabalhar com ele. Nesta simula\u00e7\u00e3o a coleta de estat\u00edsticas vai gerar um Histograma HYBRID para a coluna presente no filtro da cl\u00e1usula WHERE da consulta que estamos utilizando.<\/span><\/p>\n

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

Vamos criar os objetos necess\u00e1rios para realizar a simula\u00e7\u00e3o na base 19c e vamos executar o comando de coleta de estat\u00edsticas de forma que seja criado o Histograma para a coluna CUST_CITY.<\/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
<\/div><\/td>
SQL ><\/span> SELECT BANNER_FULL FROM V$VERSION<\/span> where rownum <<\/span> 2<\/span>;<\/span>
\n
\nBANNER_FULL
\n-------------------------------------------------------------------------<\/span>
\nOracle Database 19c Enterprise Edition Release 19.<\/span>0.<\/span>0.<\/span>0.<\/span>0 -<\/span> Production
\nVersion 19.<\/span>3.<\/span>0.<\/span>0.<\/span>0
\n
\n
\n1<\/span> linha selecionada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> CREATE TABLE dbtw063
\n  2<\/span>     AS<\/span> SELECT *<\/span>
\n  3<\/span>          FROM sh.<\/span>customers
\n  4<\/span>      ORDER BY country_id;<\/span>
\n
\nTabela criada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> CREATE index dbtw063_idx
\n  2<\/span>      ON dbtw063(<\/span>cust_city)<\/span>;<\/span>
\n
\n\u00cdndice criado.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> EXEC<\/span><\/a> DBMS_STATS.<\/span>GATHER_TABLE_STATS(<\/span>user,<\/span>'DBTW063'<\/span>,<\/span>method_opt=><\/span>'for columns cust_city size 600'<\/span>)<\/span>;<\/span>
\n
\nProcedimento PL\/<\/span>SQL conclu\u00eddo<\/span> com sucesso.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> COL column_name FOR<\/span> A16;<\/span>
\nSQL ><\/span>
\nSQL ><\/span> SELECT column_name,<\/span>
\n  2<\/span>         num_distinct,<\/span>
\n  3<\/span>         density,<\/span>
\n  4<\/span>         num_nulls,<\/span>
\n  5<\/span>         avg_col_len ,<\/span>
\n  6<\/span>         histogram,<\/span>
\n  7<\/span>         num_buckets
\n  8<\/span>    FROM user_tab_col_statistics
\n  9<\/span>   WHERE table_name =<\/span> 'DBTW063'<\/span>
\n 10<\/span>     AND column_name =<\/span> 'CUST_CITY'<\/span>;<\/span>
\n
\nCOLUMN_NAME      NUM_DISTINCT      DENSITY  NUM_NULLS AVG_COL_LEN HISTOGRAM       NUM_BUCKETS
\n----------------<\/span> ------------<\/span> ------------<\/span> ----------<\/span> -----------<\/span> ---------------<\/span> -----------<\/span>
\nCUST_CITY                 620<\/span>   .000927000<\/span>          0<\/span>          10<\/span> HYBRID                  600<\/span>
\n
\n1<\/span> linha selecionada.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Outra mudan\u00e7a ocorrida com os Histogramas a partir da vers\u00e3o 12c \u00e9 que o numero de Buckets que era limitado em 254 foi expandido para 2048, observe no comando de coleta de estat\u00edsticas acima que alterei o par\u00e2metro size para 600 para for\u00e7ar a cria\u00e7\u00e3o do Histograma HYBRID.<\/span>
\nTamb\u00e9m podemos constatar acima que a consulta na vis\u00e3o USER_TAB_COL_STATISTICS confirma a cria\u00e7\u00e3o do histograma HYBRID na coluna CUST_CITY.<\/span><\/p>\n

 <\/p>\n

2.2) Executar uma consulta na tabela criada<\/span><\/h2>\n

 
\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
<\/div><\/td>
SQL ><\/span> ALTER SESSION SET statistics_level =<\/span> all;<\/span>
\n
\nSess\u00e3o alterada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> SELECT cust_city,<\/span> country_id,<\/span> count<\/span><\/a>(<\/span>1<\/span>)<\/span>
\n  2<\/span>    FROM dbtw063
\n  3<\/span>   WHERE cust_city IN (<\/span>'Tokyo'<\/span>,<\/span>'Arnhem'<\/span>)<\/span>
\n  4<\/span>   GROUP by cust_city,<\/span> country_id;<\/span>
\n
\nCUST_CITY                      COUNTRY_ID   COUNT<\/span><\/a>(<\/span>1<\/span>)<\/span>
\n------------------------------<\/span> ----------<\/span> ----------<\/span>
\nTokyo                               52782<\/span>         16<\/span>
\nArnhem                              52770<\/span>         17<\/span>
\n
\n2<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> SELECT *<\/span> FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor(<\/span>format=><\/span>'allstats last'<\/span>)<\/span>)<\/span>;<\/span>
\n
\nPLAN_TABLE_OUTPUT
\n------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\nSQL_ID  7y7scsjjdz757,<\/span> child number 0<\/span>
\n-------------------------------------<\/span>
\nSELECT cust_city,<\/span> country_id,<\/span> count<\/span><\/a>(<\/span>1<\/span>)<\/span>   FROM dbtw063  WHERE cust_city
\nIN (<\/span>'Tokyo'<\/span>,<\/span>'Arnhem'<\/span>)<\/span>  GROUP by cust_city,<\/span> country_id
\n
\nPlan hash<\/span><\/a> value:<\/span> 646836277<\/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>  OMem |<\/span>  1Mem |<\/span> Used-<\/span>Mem |<\/span>
\n------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\n|<\/span>   0<\/span> |<\/span> SELECT STATEMENT                      |<\/span>             |<\/span>      1<\/span> |<\/span>        |<\/span>      2<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>      31<\/span> |<\/span>       |<\/span>       |<\/span>          |<\/span>
\n|<\/span>   1<\/span> |<\/span>  HASH<\/span><\/a> GROUP BY                        |<\/span>             |<\/span>      1<\/span> |<\/span>     26<\/span> |<\/span>      2<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>      31<\/span> |<\/span>  1282K|<\/span>  1282K|<\/span>  663K (<\/span>0<\/span>)<\/span>|<\/span>
\n|<\/span>   2<\/span> |<\/span>   INLIST ITERATOR                     |<\/span>             |<\/span>      1<\/span> |<\/span>        |<\/span>     33<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>      31<\/span> |<\/span>       |<\/span>       |<\/span>          |<\/span>
\n|<\/span>   3<\/span> |<\/span>    TABLE ACCESS BY INDEX ROWID BATCHED|<\/span> DBTW063     |<\/span>      2<\/span> |<\/span>     58<\/span> |<\/span>     33<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>      31<\/span> |<\/span>       |<\/span>       |<\/span>          |<\/span>
\n|*<\/span>  4<\/span> |<\/span>     INDEX RANGE<\/span><\/a> SCAN                  |<\/span> DBTW063_IDX |<\/span>      2<\/span> |<\/span>     58<\/span> |<\/span>     33<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>       5<\/span> |<\/span>       |<\/span>       |<\/span>          |<\/span>
\n------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\n
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
\n---------------------------------------------------<\/span>
\n
\n   4<\/span> -<\/span> access(<\/span>(<\/span>"CUST_CITY"<\/span>=<\/span>'Arnhem'<\/span> OR "CUST_CITY"<\/span>=<\/span>'Tokyo'<\/span>)<\/span>)<\/span>
\n
\n
\n22<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Observe no plano de execu\u00e7\u00e3o que com a utiliza\u00e7\u00e3o do Histograma HYBRID a estimativa de cardinalidade foi de 58 linhas, bem pr\u00f3ximo das 33 linhas que de fato ser\u00e3o acessadas, esta melhora na estimativa fez com que o Otimizador escolhesse acessar as linhas na tabela utilizando a opera\u00e7\u00e3o INDEX RANGE SCAN que como vimos anteriormente \u00e9 mais eficiente do que utilizar uma opera\u00e7\u00e3o TABLE ACCESS FULL.<\/span><\/p>\n

 <\/p>\n

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

 <\/p>\n

A implementa\u00e7\u00e3o de novos Histogramas a partir da vers\u00e3o 12c aumentou a precis\u00e3o do Otimizador no calculo das estimativas de cardinalidade para conjunto de dados com repeti\u00e7\u00e3o n\u00e3o uniformes (SKEWED), com isso o Otimizador passou a escolher planos de execu\u00e7\u00e3o mais eficientes e consequentemente o desempenho das consultas melhoraram. O outro tipo de Histograma criado a partir da vers\u00e3o 12c \u00e9 o TOP FREQUENCY que \u00e9 uma esp\u00e9cie de UPGRADE do Histograma FREQUENCY, mas isto j\u00e1 \u00e9 assunto para um outro artigo.<\/span><\/p>\n

 <\/p>\n

REFER\u00caNCIAS<\/span><\/h2>\n

 <\/p>\n

https:\/\/docs.oracle.com\/database\/121\/<\/a><\/span><\/strong><\/p>\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 baseado nas estat\u00edsticas dos objetos no banco de dados, a qualidade destas informa\u00e7\u00f5es \u00e9 decisiva na precis\u00e3o das estimativas de acesso das opera\u00e7\u00f5es no plano de execu\u00e7\u00e3o e consequentemente determinam o desempenho das instru\u00e7\u00f5es SQL. Os Histogramas s\u00e3o parte destas estat\u00edsticas e at\u00e9 a vers\u00e3o 11g existiam somente 2 tipos (FREQUENCY e<\/p>\n","protected":false},"author":2,"featured_media":2135,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[71],"tags":[39,226,162,161,225,227,82,10,228,119],"class_list":["post-2133","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-estatisticas","tag-estatisticas","tag-height-balanced-histograms","tag-histogramas","tag-histograms","tag-hybrid-histograms","tag-new-feature-12c","tag-optimizer","tag-otimizador","tag-skew","tag-statistics"],"aioseo_notices":[],"aioseo_head":"\n\t\t\n\t\n\t\n\t\n\t\n\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t