{"id":1775,"date":"2018-09-11T16:35:44","date_gmt":"2018-09-11T19:35:44","guid":{"rendered":"http:\/\/dbtimewizard.com.br\/blog\/?p=1775"},"modified":"2019-11-11T18:43:24","modified_gmt":"2019-11-11T21:43:24","slug":"quebrando-paradigmas-de-indices","status":"publish","type":"post","link":"http:\/\/dbtimewizard.com.br\/blog\/quebrando-paradigmas-de-indices\/","title":{"rendered":"Quebrando paradigmas de \u00edndices"},"content":{"rendered":"

Quando um \u00edndice tem baixa seletividade<\/strong><\/a> a probabilidade do Otimizador utiliza-lo \u00e9 muito pequena, mas existe uma altera\u00e7\u00e3o que podemos aplicar ao \u00edndice que confunde o Otimizador quando este realiza o calculo de cardinalidade<\/strong><\/a> e como consequ\u00eancia o Otimizador passa a utilizar o \u00edndice no plano de execu\u00e7\u00e3o. O mais incr\u00edvel de tudo isso \u00e9 que o plano de execu\u00e7\u00e3o gerado por um erro de estimativa do Otimizador \u00e9 melhor que o plano gerado inicialmente sem o erro de cardinalidade.<\/span><\/p>\n

 
\n <\/p>\n

Neste artigo vamos ver uma situa\u00e7\u00e3o que ocorre normalmente no gerenciamento de \u00edndices e que pode produzir efeitos colaterais positivos ou negativos, mas que precisam ser administrados para evitar que a sua aplica\u00e7\u00e3o passe a ter um desempenho indesejado ap\u00f3s a altera\u00e7\u00e3o de um \u00edndice.<\/span><\/p>\n

 
\n <\/p>\n

Para realizar esta simula\u00e7\u00e3o vamos:<\/span><\/strong><\/p>\n

1) Criar 1 tabela com um \u00edndice composto<\/span><\/strong>
\n 2) Executar a consulta e verificar que o plano de execu\u00e7\u00e3o n\u00e3o utiliza o \u00edndice<\/span><\/strong>
\n 3) Alterar o \u00edndice composto incluindo mais uma coluna<\/span><\/strong>
\n 4) Executar a consulta novamente e verificar que o plano de execu\u00e7\u00e3o passa a utilizar o \u00edndice<\/span><\/strong>
\n 5) Criar uma estat\u00edstica de grupo de colunas das colunas da cl\u00e1usula WHERE<\/span><\/strong>
\n 6) Executar a consulta novamente e verificar que o plano de execu\u00e7\u00e3o volta ao seu padr\u00e3o original<\/span><\/strong><\/p>\n

1) Criar a tabela da simula\u00e7\u00e3o<\/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 *<\/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> -----------------------<\/span>
\nSQL ><\/span> --<\/span> Defini\u00e7\u00e3o da tabela
\nSQL ><\/span> -----------------------<\/span>
\nSQL ><\/span>
\nSQL ><\/span> CREATE TABLE dbtw055 AS<\/span>
\n  2<\/span>  SELECT mod(<\/span>rownum,<\/span>5<\/span>)<\/span> col1,<\/span>
\n  3<\/span>         mod(<\/span>rownum,<\/span>5<\/span>)<\/span> col2,<\/span>
\n  4<\/span>         rownum  col3,<\/span>
\n  5<\/span>         'DBTimeWizard - Oracle Performance and Tuning'<\/span>   text
\n  6<\/span>    FROM dual
\n  7<\/span>  CONNECT BY level <=<\/span> 1000000<\/span>;<\/span>
\n
\nTabela criada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> ---------------------<\/span>
\nSQL ><\/span> --<\/span> Cria\u00e7\u00e3o do<\/span> \u00edndice
\nSQL ><\/span> ---------------------<\/span>
\nSQL ><\/span>
\nSQL ><\/span> CREATE INDEX dbtw055_comp2_idx ON dbtw055 (<\/span>col1,<\/span>col2)<\/span>;<\/span>
\n
\n\u00cdndice criado.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> -------------------------<\/span>
\nSQL ><\/span> --<\/span> Coleta de estat\u00edsticas
\nSQL ><\/span> -------------------------<\/span>
\nSQL ><\/span>
\nSQL ><\/span> exec<\/span><\/a> dbms_stats.<\/span>gather_table_stats(<\/span>ownname =><\/span> USER,<\/span>tabname =><\/span> 'DBTW055'<\/span>,<\/span>method_opt=><\/span>'for all columns size auto'<\/span>,<\/span>cascade=><\/span>true<\/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><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

2) Executar a consulta e verificar o plano de execu\u00e7\u00e3o<\/span><\/h2>\n

 <\/p>\n

Vamos realizar uma consulta na tabela incluindo no filtro da cl\u00e1usula WHERE as duas colunas presentes no \u00edndice criado.<\/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
<\/div><\/td>
SQL ><\/span> ALTER SESSION SET statistics_level =<\/span> ALL;<\/span>
\n
\nSess\u00e3o alterada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> SELECT \/* DBTW-055.1 *\/<\/span> DISTINCT text
\n  2<\/span>    FROM (<\/span>SELECT text
\n  3<\/span>            FROM dbtw055
\n  4<\/span>           WHERE col1 =<\/span> 0<\/span>
\n  5<\/span>             AND col2 =<\/span> 0<\/span>)<\/span>;<\/span>
\n
\nTEXT
\n--------------------------------------------<\/span>
\nDBTimeWizard -<\/span> Oracle Performance and Tuning
\n
\nSQL ><\/span>
\nSQL ><\/span> --<\/span> Recupera o SQL_ID e o CHILD_NUMBER  da consulta executada
\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-055.1%'<\/span>
\n  4<\/span>     AND sql_text NOT LIKE '%v$sql%'<\/span>;<\/span>
\n
\nSQL_ID        CHILD_NUMBER
\n-------------<\/span> ------------<\/span>
\nb229f8sznx4p1            0<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> --<\/span> Gera o relat\u00f3rio do<\/span> plano de execu\u00e7\u00e3o da consulta
\nSQL ><\/span> SELECT *<\/span> FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'&m_sql_id'<\/span>,&<\/span>m_child_no,<\/span>'typical iostats last'<\/span>)<\/span>)<\/span>;<\/span>
\nantigo   1<\/span>:<\/span> SELECT *<\/span> FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'&m_sql_id'<\/span>,&<\/span>m_child_no,<\/span>'typical iostats last'<\/span>)<\/span>)<\/span>
\nnovo   1<\/span>:<\/span> SELECT *<\/span> FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'b229f8sznx4p1'<\/span>,<\/span>         0<\/span>,<\/span>'typical iostats last'<\/span>)<\/span>)<\/span>
\n
\nPLAN_TABLE_OUTPUT
\n----------------------------------------------------------------------------------------------------------------------------------<\/span>
\nSQL_ID  b229f8sznx4p1,<\/span> child number 0<\/span>
\n-------------------------------------<\/span>
\nSELECT \/* DBTW-055.1 *\/<\/span> DISTINCT text   FROM (<\/span>SELECT text
\nFROM dbtw055          WHERE col1 =<\/span> :<\/span>"SYS_B_0"<\/span>            AND col2 =<\/span>
\n:<\/span>"SYS_B_1"<\/span>)<\/span>
\n
\nPlan hash<\/span><\/a> value:<\/span> 2734070855<\/span>
\n
\n---------------------------------------------------------------------------------------------------------------------------------<\/span>
\n|<\/span> Id  |<\/span> Operation          |<\/span> Name    |<\/span> Starts |<\/span> E-<\/span>Rows |<\/span>E-<\/span>Bytes|<\/span> Cost (<\/span>%<\/span>CPU)<\/span>|<\/span> E-<\/span>Time<\/span><\/a>   |<\/span> A-<\/span>Rows |<\/span>   A-<\/span>Time<\/span><\/a>   |<\/span> Buffers |<\/span> Reads  |<\/span>
\n---------------------------------------------------------------------------------------------------------------------------------<\/span>
\n|<\/span>   0<\/span> |<\/span> SELECT STATEMENT   |<\/span>         |<\/span>      1<\/span> |<\/span>        |<\/span>       |<\/span>  2328<\/span> (<\/span>100<\/span>)<\/span>|<\/span>          |<\/span>      1<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>04.74<\/span> |<\/span>    8405<\/span> |<\/span>   8402<\/span> |<\/span>
\n|<\/span>   1<\/span> |<\/span>  HASH<\/span><\/a> UNIQUE       |<\/span>         |<\/span>      1<\/span> |<\/span>      1<\/span> |<\/span>    51<\/span> |<\/span>  2328<\/span>   (<\/span>1<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>28<\/span> |<\/span>      1<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>04.74<\/span> |<\/span>    8405<\/span> |<\/span>   8402<\/span> |<\/span>
\n|*<\/span>  2<\/span> |<\/span>   TABLE ACCESS FULL|<\/span> DBTW055 |<\/span>      1<\/span> |<\/span>    200K|<\/span>  9960K|<\/span>  2323<\/span>   (<\/span>1<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>28<\/span> |<\/span>    200K|<\/span>00<\/span>:<\/span>00<\/span>:<\/span>04.72<\/span> |<\/span>    8405<\/span> |<\/span>   8402<\/span> |<\/span>
\n---------------------------------------------------------------------------------------------------------------------------------<\/span>
\n
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
\n---------------------------------------------------<\/span>
\n
\n   2<\/span> -<\/span> filter(<\/span>(<\/span>"COL1"<\/span>=:<\/span>SYS_B_0 AND "COL2"<\/span>=:<\/span>SYS_B_1)<\/span>)<\/span>
\n
\n
\n21<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/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>         leaf_blocks
\n  7<\/span>    from dba_indexes
\n  8<\/span>   where table_name =<\/span> 'DBTW055'<\/span>;<\/span>
\n
\nINDEX_NAME                         BLEVEL   NUM_ROWS DISTINCT_KEYS CLUSTERING_FACTOR LEAF_BLOCKS
\n------------------------------<\/span> ----------<\/span> ----------<\/span> -------------<\/span> -----------------<\/span> -----------<\/span>
\nDBTW055_COMP2_IDX                       2<\/span>    1000000<\/span>             5<\/span>             42197<\/span>        2315<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Verificando o plano de execu\u00e7\u00e3o acima constatamos que as linhas foram recuperadas da tabela utilizando a opera\u00e7\u00e3o TABLE ACCESS FULL, o Otimizador estimou corretamente que seriam acessadas 200 mil linhas da tabela (20%), ou seja a tabela contem 1 milh\u00e3o de linhas e 5 valores distintos, conforme consulta na vis\u00e3o de estat\u00edsticas, portanto cada valor vai selecionar 200 mil linhas.<\/span><\/p>\n

3) Alterar o \u00edndice composto incluindo mais uma coluna<\/span><\/h2>\n

Agora vamos imaginar que para melhorar a performance de uma outra consulta seria necess\u00e1rio que este \u00edndice possu\u00edsse mais uma coluna. Para incluir mais uma coluna no \u00edndice temos que elimina-lo e recria-lo com a coluna adicional.<\/span><\/p>\n

 <\/p>\n

1
2
3
4
5
6
7
8
9
10
11
12
13
14
<\/div><\/td>
SQL ><\/span> -------------------------<\/span>
\nSQL ><\/span> --<\/span> Cria\u00e7\u00e3o do<\/span> novo \u00edndice
\nSQL ><\/span> -------------------------<\/span>
\nSQL ><\/span>
\nSQL ><\/span> DROP INDEX dbtw055_comp2_idx;<\/span>
\n
\n\u00cdndice eliminado.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> CREATE INDEX dbtw055_comp3_idx ON dbtw055 (<\/span>col1,<\/span>col2,<\/span>col3)<\/span>;<\/span>
\n
\n\u00cdndice criado.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

4) Executar a consulta novamente e verificar o plano de execu\u00e7\u00e3o<\/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
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
<\/div><\/td>
SQL ><\/span> ALTER SESSION SET statistics_level =<\/span> ALL;<\/span>
\n
\nSess\u00e3o alterada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> SELECT \/* DBTW-055.2 *\/<\/span> DISTINCT text
\n  2<\/span>    FROM (<\/span>SELECT text
\n  3<\/span>            FROM dbtw055
\n  4<\/span>           WHERE col1 =<\/span> 0<\/span>
\n  5<\/span>             AND col2 =<\/span> 0<\/span>)<\/span>;<\/span>
\n
\nTEXT
\n--------------------------------------------<\/span>
\nDBTimeWizard -<\/span> Oracle Performance and Tuning
\n
\nSQL ><\/span>
\nSQL ><\/span> --<\/span> Recupera o SQL_ID e o CHILD_NUMBER  da consulta executada
\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-055.2%'<\/span>
\n  4<\/span>     AND sql_text NOT LIKE '%v$sql%'<\/span>;<\/span>
\n
\nSQL_ID        CHILD_NUMBER
\n-------------<\/span> ------------<\/span>
\ndfyurfj25bnq4            0<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> --<\/span> Gera o relat\u00f3rio do<\/span> plano de execu\u00e7\u00e3o da consulta
\nSQL ><\/span> SELECT *<\/span> FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'&m_sql_id'<\/span>,&<\/span>m_child_no,<\/span>'typical iostats last'<\/span>)<\/span>)<\/span>;<\/span>
\nantigo   1<\/span>:<\/span> SELECT *<\/span> FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'&m_sql_id'<\/span>,&<\/span>m_child_no,<\/span>'typical iostats last'<\/span>)<\/span>)<\/span>
\nnovo   1<\/span>:<\/span> SELECT *<\/span> FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'dfyurfj25bnq4'<\/span>,<\/span>         0<\/span>,<\/span>'typical iostats last'<\/span>)<\/span>)<\/span>
\n
\nPLAN_TABLE_OUTPUT
\n--------------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\nSQL_ID  dfyurfj25bnq4,<\/span> child number 0<\/span>
\n-------------------------------------<\/span>
\nSELECT \/* DBTW-055.2 *\/<\/span> DISTINCT text   FROM (<\/span>SELECT text
\nFROM dbtw055          WHERE col1 =<\/span> :<\/span>"SYS_B_0"<\/span>            AND col2 =<\/span>
\n:<\/span>"SYS_B_1"<\/span>)<\/span>
\n
\nPlan hash<\/span><\/a> value:<\/span> 264121747<\/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>  1807<\/span> (<\/span>100<\/span>)<\/span>|<\/span>          |<\/span>      1<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.12<\/span> |<\/span>    8961<\/span> |<\/span>
\n|<\/span>   1<\/span> |<\/span>  HASH<\/span><\/a> UNIQUE                 |<\/span>                   |<\/span>      1<\/span> |<\/span>      1<\/span> |<\/span>    51<\/span> |<\/span>  1807<\/span>   (<\/span>1<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>22<\/span> |<\/span>      1<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.12<\/span> |<\/span>    8961<\/span> |<\/span>
\n|<\/span>   2<\/span> |<\/span>   TABLE ACCESS BY INDEX ROWID|<\/span> DBTW055           |<\/span>      1<\/span> |<\/span>  40000<\/span> |<\/span>  1992K|<\/span>  1805<\/span>   (<\/span>1<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>22<\/span> |<\/span>    200K|<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.09<\/span> |<\/span>    8961<\/span> |<\/span>
\n|*<\/span>  3<\/span> |<\/span>    INDEX RANGE<\/span><\/a> SCAN          |<\/span> DBTW055_COMP3_IDX |<\/span>      1<\/span> |<\/span>  40000<\/span> |<\/span>       |<\/span>   123<\/span>   (<\/span>0<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>02<\/span> |<\/span>    200K|<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.03<\/span> |<\/span>     559<\/span> |<\/span>
\n--------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\n
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
\n---------------------------------------------------<\/span>
\n
\n   3<\/span> -<\/span> access(<\/span>"COL1"<\/span>=:<\/span>SYS_B_0 AND "COL2"<\/span>=:<\/span>SYS_B_1)<\/span>
\n
\n
\n22<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/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>         leaf_blocks
\n  7<\/span>    from dba_indexes
\n  8<\/span>   where table_name =<\/span> 'DBTW055'<\/span>;<\/span>
\n
\nINDEX_NAME                         BLEVEL   NUM_ROWS DISTINCT_KEYS CLUSTERING_FACTOR LEAF_BLOCKS
\n------------------------------<\/span> ----------<\/span> ----------<\/span> -------------<\/span> -----------------<\/span> -----------<\/span>
\nDBTW055_COMP3_IDX                       2<\/span>    1000000<\/span>       1000000<\/span>             42010<\/span>        3010<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Analisando o plano de execu\u00e7\u00e3o da consulta acima ap\u00f3s a altera\u00e7\u00e3o no \u00edndice podemos constatar que a tabela passou a ser acessada utilizando o \u00edndice, esta altera\u00e7\u00e3o no plano de execu\u00e7\u00e3o ocorreu pois o Otimizador errou no calculo da cardinalidade da opera\u00e7\u00e3o (estimou 40 mil linhas), este erro ocorreu pois ao incluir mais uma coluna no \u00edndice a sua estat\u00edstica passou a refletir a combina\u00e7\u00e3o das 3 colunas, mas a consulta que estamos avaliando apresenta somente duas colunas no filtro da cl\u00e1usula WHERE.<\/span><\/p>\n

Outra observa\u00e7\u00e3o importante \u00e9 que a consulta utilizando o \u00edndice\u00b4leva 12 cent\u00e9simos de segundos para ser conclu\u00edda, enquanto a consulta com a opera\u00e7\u00e3o TABLE ACCESS FULL demorou 4 segundos e 74 cent\u00e9simos, a explica\u00e7\u00e3o para esta diferen\u00e7a \u00e9 que no primeiro plano de execu\u00e7\u00e3o houve leitura f\u00edsica no disco (devido a opera\u00e7\u00e3o FULL SCAN), enquanto no segundo os blocos foram recuperados direto do Buffer Cache. (Blocos acessados por \u00edndices permanecem mais tempo no Buffer Cache)<\/span><\/p>\n

5) Criar estat\u00edstica de grupo de colunas<\/span><\/h2>\n

 <\/p>\n

Para ajudar o Otimizador a calcular a cardinalidade da opera\u00e7\u00e3o corretamente podemos criar uma estat\u00edstica de grupo de colunas (tamb\u00e9m conhecida como estat\u00edsticas estendidas) para as duas colunas presentes no filtro da 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
<\/div><\/td>
SQL ><\/span> exec<\/span><\/a> dbms_stats.<\/span>gather_table_stats(<\/span>ownname =><\/span> USER,<\/span>tabname =><\/span> 'DBTW055'<\/span>,<\/span>method_opt=><\/span>'for columns (col1,col2) size 1'<\/span>,<\/span>cascade=><\/span>true<\/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,<\/span> num_distinct,<\/span> num_nulls,<\/span> histogram,<\/span> extension
\n  2<\/span>    FROM user_tab_col_statistics TCS,<\/span>
\n  3<\/span>         user_stat_extensions TSE
\n  4<\/span>   WHERE TCS.<\/span>column_name=<\/span>TSE.<\/span>extension_name(<\/span>+<\/span>)<\/span>
\n  5<\/span>     AND TCS.<\/span>table_name=<\/span>'DBTW055'<\/span>;<\/span>
\n
\nCOLUMN_NAME                    NUM_DISTINCT  NUM_NULLS HISTOGRAM       EXTENSION
\n------------------------------<\/span> ------------<\/span> ----------<\/span> ---------------<\/span> ------------------------<\/span>
\nSYS_STUFLHATC5RBD6JHJZWT$X2AAH<\/span>            5<\/span>          0<\/span> NONE            (<\/span>"COL1"<\/span>,<\/span>"COL2"<\/span>)<\/span>
\nCOL1                                      5<\/span>          0<\/span> NONE
\nCOL3                                1000000<\/span>          0<\/span> NONE
\nCOL2                                      5<\/span>          0<\/span> NONE
\nTEXT                                      1<\/span>          0<\/span> NONE
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

6) Executar a consulta novamente e verificar que o plano de execu\u00e7\u00e3o<\/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
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
<\/div><\/td>
SQL ><\/span> ALTER SESSION SET statistics_level =<\/span> ALL;<\/span>
\n
\nSess\u00e3o alterada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> SELECT \/* DBTW-055.3 *\/<\/span> DISTINCT text
\n  2<\/span>    FROM (<\/span>SELECT text
\n  3<\/span>            FROM dbtw055
\n  4<\/span>           WHERE col1 =<\/span> 0<\/span>
\n  5<\/span>             AND col2 =<\/span> 0<\/span>)<\/span>;<\/span>
\n
\nTEXT
\n--------------------------------------------<\/span>
\nDBTimeWizard -<\/span> Oracle Performance and Tuning
\n
\nSQL ><\/span>
\nSQL ><\/span> --<\/span> Recupera o SQL_ID e o CHILD_NUMBER  da consulta executada
\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-055.3%'<\/span>
\n  4<\/span>     AND sql_text NOT LIKE '%v$sql%'<\/span>;<\/span>
\n
\nSQL_ID        CHILD_NUMBER
\n-------------<\/span> ------------<\/span>
\ndms6c908t4vs9            0<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> --<\/span> Gera o relat\u00f3rio do<\/span> plano de execu\u00e7\u00e3o da consulta
\nSQL ><\/span> SELECT *<\/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  dms6c908t4vs9,<\/span> child number 0<\/span>
\n-------------------------------------<\/span>
\nSELECT \/* DBTW-055.3 *\/<\/span> DISTINCT text   FROM (<\/span>SELECT text
\nFROM dbtw055          WHERE col1 =<\/span> :<\/span>"SYS_B_0"<\/span>            AND col2 =<\/span>
\n:<\/span>"SYS_B_1"<\/span>)<\/span>
\n
\nPlan hash<\/span><\/a> value:<\/span> 2734070855<\/span>
\n
\n---------------------------------------------------------------------------------------------------------------------------------<\/span>
\n|<\/span> Id  |<\/span> Operation          |<\/span> Name    |<\/span> Starts |<\/span> E-<\/span>Rows |<\/span>E-<\/span>Bytes|<\/span> Cost (<\/span>%<\/span>CPU)<\/span>|<\/span> E-<\/span>Time<\/span><\/a>   |<\/span> A-<\/span>Rows |<\/span>   A-<\/span>Time<\/span><\/a>   |<\/span> Buffers |<\/span> Reads  |<\/span>
\n---------------------------------------------------------------------------------------------------------------------------------<\/span>
\n|<\/span>   0<\/span> |<\/span> SELECT STATEMENT   |<\/span>         |<\/span>      1<\/span> |<\/span>        |<\/span>       |<\/span>  2328<\/span> (<\/span>100<\/span>)<\/span>|<\/span>          |<\/span>      1<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>01.04<\/span> |<\/span>    8405<\/span> |<\/span>   8402<\/span> |<\/span>
\n|<\/span>   1<\/span> |<\/span>  HASH<\/span><\/a> UNIQUE       |<\/span>         |<\/span>      1<\/span> |<\/span>      1<\/span> |<\/span>    51<\/span> |<\/span>  2328<\/span>   (<\/span>1<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>28<\/span> |<\/span>      1<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>01.04<\/span> |<\/span>    8405<\/span> |<\/span>   8402<\/span> |<\/span>
\n|*<\/span>  2<\/span> |<\/span>   TABLE ACCESS FULL|<\/span> DBTW055 |<\/span>      1<\/span> |<\/span>    200K|<\/span>  9960K|<\/span>  2323<\/span>   (<\/span>1<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>28<\/span> |<\/span>    200K|<\/span>00<\/span>:<\/span>00<\/span>:<\/span>01.02<\/span> |<\/span>    8405<\/span> |<\/span>   8402<\/span> |<\/span>
\n---------------------------------------------------------------------------------------------------------------------------------<\/span>
\n
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
\n---------------------------------------------------<\/span>
\n
\n   2<\/span> -<\/span> filter(<\/span>(<\/span>"COL1"<\/span>=:<\/span>SYS_B_0 AND "COL2"<\/span>=:<\/span>SYS_B_1)<\/span>)<\/span>
\n
\n
\n21<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Observando o plano de execu\u00e7\u00e3o acima verificamos que a estimativa de cardinalidade da opera\u00e7\u00e3o voltou a ser calculada corretamente e o numero de linhas estimadas (200 mil linhas) \u00e9 igual ao numero de linhas efetivamente selecionadas da tabela.<\/span><\/p>\n

 <\/p>\n

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

https:\/\/docs.oracle.com\/cd\/E29505_01\/server.1111<\/a><\/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":"

Quando um \u00edndice tem baixa seletividade a probabilidade do Otimizador utiliza-lo \u00e9 muito pequena, mas existe uma altera\u00e7\u00e3o que podemos aplicar ao \u00edndice que confunde o Otimizador quando este realiza o calculo de cardinalidade e como consequ\u00eancia o Otimizador passa a utilizar o \u00edndice no plano de execu\u00e7\u00e3o. O mais incr\u00edvel de tudo isso \u00e9 que o plano de execu\u00e7\u00e3o<\/p>\n","protected":false},"author":2,"featured_media":1782,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[71,62],"tags":[204,63,182,127,64],"class_list":["post-1775","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-estatisticas","category-indice","tag-column-group-statistics","tag-composite-index","tag-estatisticas-estendidas","tag-extended-statistics","tag-indice-composto"],"aioseo_notices":[],"_links":{"self":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/1775","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=1775"}],"version-history":[{"count":22,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/1775\/revisions"}],"predecessor-version":[{"id":2073,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/1775\/revisions\/2073"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/media\/1782"}],"wp:attachment":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/media?parent=1775"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/categories?post=1775"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/tags?post=1775"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}