{"id":1659,"date":"2018-06-10T20:45:34","date_gmt":"2018-06-10T23:45:34","guid":{"rendered":"http:\/\/dbtimewizard.com.br\/blog\/?p=1659"},"modified":"2019-11-11T18:44:52","modified_gmt":"2019-11-11T21:44:52","slug":"um-truque-que-voce-nao-conhece-sobre-coleta-de-estatisticas","status":"publish","type":"post","link":"http:\/\/dbtimewizard.com.br\/blog\/um-truque-que-voce-nao-conhece-sobre-coleta-de-estatisticas\/","title":{"rendered":"Um truque que voc\u00ea n\u00e3o conhece sobre coleta de estat\u00edsticas"},"content":{"rendered":"

Neste artigo vamos verificar como a coleta do valor de CLUSTERING FACTOR<\/strong><\/a> de um \u00edndice pode influenciar de forma negativa\u00a0o plano de execu\u00e7\u00e3o de uma instru\u00e7\u00e3o SQL e o que podemos fazer para melhorar esta estat\u00edstica e ajudar o Otimizador a tomar uma decis\u00e3o mais assertiva.<\/span><\/p>\n

O CLUSTERING FACTOR<\/strong> \u00e9 uma das estat\u00edsticas mais importantes na tomada de decis\u00e3o pelo Otimizador quanto a viabilidade de utiliza\u00e7\u00e3o de um \u00edndice num plano de execu\u00e7\u00e3o, esta estat\u00edstica deveria refletir o qu\u00e3o bem ordenados os dados da tabela est\u00e3o em rela\u00e7\u00e3o \u00e0s entradas do \u00edndice. Portanto quanto melhor (menor) o CF, mais eficiente seria usar o \u00edndice, j\u00e1 que menos blocos da tabela seriam acessados para recuperar os dados.<\/span><\/p>\n

O CLUSTERING FACTOR<\/strong> \u00e9 calculado atrav\u00e9s de um FULL INDEX SCAN<\/strong> que analisa o ROWID<\/strong> de cada entrada do \u00edndice e compara com o da entrada anterior, o incremento do valor do CF ocorre da seguinte forma:<\/span><\/p>\n

1) Caso o bloco da tabela<\/strong> que est\u00e1 sendo referenciado no ROWID seja diferente<\/strong> daquele da entrada de \u00edndice anterior, o CF \u00e9 incrementado<\/strong>. <\/span>
\n2) Caso o bloco da tabela<\/strong> que est\u00e1 sendo referenciado no ROWID seja igual<\/strong> ao da entrada de \u00edndice anterior, o CF n\u00e3o \u00e9 incrementado<\/strong>.<\/span><\/p>\n

Este algoritmo pode apresentar problemas em algumas situa\u00e7\u00f5es, como neste caso: imagine uma tabela onde 200 linhas ser\u00e3o inseridas por duas sess\u00f5es simultaneamente, cada uma insere 100 linhas e uma coluna da linha que ser\u00e1 indexada recebe um valor num\u00e9rico que \u00e9 incrementado por uma SEQUENCE<\/strong>. Utilizando 2 FREELISTS<\/strong>, uma sess\u00e3o vai inserir as linhas em um bloco enquanto a outra vai inserir em um segundo bloco, com os valores da SEQUENCE<\/strong> sendo distribu\u00eddos aleatoriamente entre os dois blocos. Desta forma a coleta de estat\u00edsticas do \u00edndice vai registrar um valor de CF pr\u00f3ximo de 200, j\u00e1 que os valores sequenciais gerados pela SEQUENCE para a coluna deste \u00edndice s\u00e3o misturados ou alternados entre os blocos. Este valor de CF far\u00e1 com que o Otimizador desconsidere a utiliza\u00e7\u00e3o do \u00edndice, embora as entradas do \u00edndice estejam bem agrupadas nos blocos da tabela.<\/span><\/p>\n

Para facilitar a compreens\u00e3o deste problema, vamos realizar uma simula\u00e7\u00e3o pr\u00e1tica com as seguintes etapas:<\/span><\/p>\n

1) Criar uma tabela<\/strong>, uma sequence<\/strong> e uma procedure<\/strong> para inserir linhas na tabela<\/span>
\n2) Executar a procedure em 4 sess\u00f5es SQL*PLUS simultaneamente<\/strong><\/span>
\n3) Criar um \u00edndice, coletar as estat\u00edsticas e verificar o CLUSTERING FACTOR<\/strong><\/span>
\n4) Executar uma consulta na tabela criada e verificar o plano de execu\u00e7\u00e3o<\/span>
\n5) Alterar o par\u00e2metro TABLE_CACHED_BLOCKS<\/strong>, coletar as estat\u00edsticas e verificar o CLUSTERING FACTOR<\/strong><\/span>
\n6) Executar a mesma consulta novamente e verificar o plano de execu\u00e7\u00e3o<\/span><\/p>\n

1) Criar a tabela, sequence e a procedure<\/span><\/h2>\n

Vamos criar os objetos necess\u00e1rios para realizar a nossa simula\u00e7\u00e3o:\u00a0<\/span>
\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> create table dbtw052 (<\/span>matricula number,<\/span> Nome varchar2(<\/span>50<\/span>)<\/span>)<\/span>;<\/span>
\n
\nTabela criada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> create sequence dbtw052_seq order;<\/span>
\n
\nSequ\u00eancia criada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> CREATE OR REPLACE PROCEDURE proc_dbtw052 AS<\/span>
\n  2<\/span>  BEGIN
\n  3<\/span>     FOR<\/span> i IN 1<\/span>..<\/span>100000<\/span> LOOP
\n  4<\/span>         INSERT INTO dbtw052 VALUES (<\/span>dbtw052_seq.<\/span>NEXTVAL,<\/span> 'DBTimeWizard - Oracle Performance and Tuning'<\/span>)<\/span>;<\/span>
\n  5<\/span>         COMMIT;<\/span>
\n  6<\/span>     END<\/span><\/a> LOOP;<\/span>
\n  7<\/span>  END<\/span><\/a>;<\/span>
\n  8<\/span>  \/<\/span>
\n
\nProcedimento criado.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> SELECT table_name,<\/span>
\n  2<\/span>         T.<\/span>tablespace_name,<\/span>
\n  3<\/span>         segment_space_management
\n  4<\/span>    FROM dba_tables T,<\/span>
\n  5<\/span>         dba_tablespaces F
\n  6<\/span>   WHERE T.<\/span>tablespace_name =<\/span> F.<\/span>tablespace_name
\n  7<\/span>     AND table_name=<\/span>'DBTW052'<\/span>;<\/span>
\n
\nTABLE_NAME                     TABLESPACE_NAME                SEGMEN
\n------------------------------<\/span> ------------------------------<\/span> ------<\/span>
\nDBTW052                        DBTW                           AUTO
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

2) Executar a procedure em 4 sess\u00f5es simult\u00e2neas<\/span><\/h2>\n

Executando a procedure em sess\u00f5es paralelas e com a tabela definida numa TABLESPACE cujo gerenciamento dos seguimentos \u00e9 ASSM<\/strong>, cada sess\u00e3o vai inserir as linhas em um bloco diferente da outra, criando um cen\u00e1rio parecido com aquele descrito na introdu\u00e7\u00e3o do artigo.<\/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
<\/div><\/td>
SQL-<\/span>01<\/span> ><\/span> exec<\/span><\/a> proc_dbtw052;<\/span>
\n
\nProcedimento PL\/<\/span>SQL conclu\u00eddo<\/span> com sucesso.<\/span>
\n
\nDecorrido:<\/span> 00<\/span>:<\/span>01<\/span>:<\/span>08.95<\/span>
\nSQL-<\/span>01<\/span> ><\/span>
\nSQL-<\/span>02<\/span> ><\/span> exec<\/span><\/a> proc_dbtw052;<\/span>
\n
\nProcedimento PL\/<\/span>SQL conclu\u00eddo<\/span> com sucesso.<\/span>
\n
\nDecorrido:<\/span> 00<\/span>:<\/span>01<\/span>:<\/span>00.65<\/span>
\nSQL-<\/span>02<\/span> ><\/span>
\nSQL-<\/span>03<\/span> ><\/span> exec<\/span><\/a> proc_dbtw052;<\/span>
\n
\nProcedimento PL\/<\/span>SQL conclu\u00eddo<\/span> com sucesso.<\/span>
\n
\nDecorrido:<\/span> 00<\/span>:<\/span>01<\/span>:<\/span>09.03<\/span>
\nSQL-<\/span>03<\/span> ><\/span>
\nSQL-<\/span>04<\/span> ><\/span> exec<\/span><\/a> proc_dbtw052;<\/span>
\n
\nProcedimento PL\/<\/span>SQL conclu\u00eddo<\/span> com sucesso.<\/span>
\n
\nDecorrido:<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>57.83<\/span>
\nSQL-<\/span>04<\/span> ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

3) Criar um \u00edndice e coletar as estat\u00edsticas<\/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
<\/div><\/td>
SQL ><\/span> CREATE INDEX dbtw052_matr_idx ON dbtw052(<\/span>matricula)<\/span>;<\/span>
\n
\n\u00cdndice criado.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> EXEC<\/span><\/a> dbms_stats.<\/span>gather_table_stats(<\/span>ownname=><\/span>user,<\/span> tabname=><\/span>'DBTW052'<\/span>,<\/span> estimate_percent=><\/span> null<\/span>,<\/span> cascade=><\/span> true<\/span>,<\/span> method_opt=><\/span>'FOR ALL COLUMNS SIZE 1'<\/span>)<\/span>;<\/span>
\n
\nProcedimento PL\/<\/span>SQL conclu\u00eddo<\/span> com sucesso.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> SELECT T.<\/span>table_name,<\/span>
\n  2<\/span>         I.<\/span>index_name,<\/span>
\n  3<\/span>         T.<\/span>blocks,<\/span>
\n  4<\/span>         T.<\/span>num_rows,<\/span>
\n  5<\/span>         I.<\/span>clustering_factor
\n  6<\/span>    FROM user_tables T,<\/span>
\n  7<\/span>         user_indexes I
\n  8<\/span>   WHERE T.<\/span>table_name =<\/span> I.<\/span>table_name
\n  9<\/span>     AND I.<\/span>index_name =<\/span> 'DBTW052_MATR_IDX'<\/span>;<\/span>
\n
\nTABLE_NAME                     INDEX_NAME                         BLOCKS   NUM_ROWS CLUSTERING_FACTOR
\n------------------------------<\/span> ------------------------------<\/span> ----------<\/span> ----------<\/span> -----------------<\/span>
\nDBTW052                        DBTW052_MATR_IDX                     3142<\/span>     400000<\/span>            266796<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Analisando as estat\u00edsticas da tabela e do \u00edndice verificamos que a tabela tem<\/strong> 3142 blocos<\/strong> e o valor do CLUSTERING FACTOR \u00e9 266.796<\/strong>, muito pr\u00f3ximo do valor total de linhas da tabela que \u00e9 400.000<\/strong>.<\/span><\/p>\n

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

Vamos executar uma consulta na tabela e verificar a efici\u00eancia do plano de execu\u00e7\u00e3o:<\/span>
\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
<\/div><\/td>
SQL ><\/span> ALTER SESSION SET statistics_level=<\/span>ALL;<\/span>
\n
\nSess\u00e3o alterada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> SELECT \/* dbtw521 *\/<\/span> *<\/span>
\n  2<\/span>    FROM dbtw052
\n  3<\/span>   WHERE matricula BETWEEN 42<\/span> AND 319<\/span>;<\/span>
\n
\n MATRICULA NOME
\n----------<\/span> --------------------------------------------------<\/span>
\n       271<\/span> DBTimeWizard -<\/span> Oracle Performance and Tuning      
\n  .<\/span>
\n  .<\/span>
\n       270<\/span> DBTimeWizard -<\/span> Oracle Performance and Tuning
\n
\n278<\/span> linhas selecionadas.<\/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 '%dbtw521%'<\/span>
\n  4<\/span>     AND sql_text NOT LIKE '%v$sql%'<\/span>;<\/span>
\n
\nSQL_ID           CHILD_NUMBER
\n----------------<\/span> ------------<\/span>
\n6ab5c2j6jfk8m               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>'6ab5c2j6jfk8m'<\/span>,<\/span>         0<\/span>,<\/span>'ALLSTATS LAST'<\/span>)<\/span>)<\/span>
\n
\nPLAN_TABLE_OUTPUT
\n-----------------------------------------------------------------------------------------------------<\/span>
\n
\nSQL_ID  6ab5c2j6jfk8m,<\/span> child number 0<\/span>
\n-------------------------------------<\/span>
\nSELECT \/* dbtw521 *\/<\/span> *<\/span>   FROM dbtw052  WHERE matricula BETWEEN
\n:<\/span>"SYS_B_0"<\/span> AND :<\/span>"SYS_B_1"<\/span>
\n
\nPlan hash<\/span><\/a> value:<\/span> 2174329534<\/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>    278<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.71<\/span> |<\/span>    3109<\/span> |<\/span>
\n|*<\/span>  1<\/span> |<\/span>  FILTER            |<\/span>         |<\/span>      1<\/span> |<\/span>        |<\/span>    278<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.71<\/span> |<\/span>    3109<\/span> |<\/span>
\n|*<\/span>  2<\/span> |<\/span>   TABLE ACCESS FULL|<\/span> DBTW052 |<\/span>      1<\/span> |<\/span>    279<\/span> |<\/span>    278<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.71<\/span> |<\/span>    3109<\/span> |<\/span>
\n----------------------------------------------------------------------------------------<\/span>
\n
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
\n---------------------------------------------------<\/span>
\n
\n   1<\/span> -<\/span> filter(<\/span>:<\/span>SYS_B_1>=:<\/span>SYS_B_0)<\/span>
\n   2<\/span> -<\/span> filter(<\/span>(<\/span>"MATRICULA"<\/span><=:<\/span>SYS_B_1 AND "MATRICULA"<\/span>>=:<\/span>SYS_B_0)<\/span>)<\/span>
\n
\n
\n21<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Analisando o plano verificamos que foram selecionadas 278 linhas<\/strong> e o Otimizador decidiu fazer uma opera\u00e7\u00e3o TABLE FULL SCAN<\/strong> pois ele estimou que se utilizasse o \u00edndice teria que acessar um numero de blocos da tabela pr\u00f3ximo do numero de linhas selecionadas, isto baseado no valor do CLUSTERING FACTOR<\/strong>.<\/span><\/p>\n

5) Alterar o par\u00e2metro, coletar as estat\u00edsticas e verificar o CF<\/span><\/h2>\n

Para melhorar a assertividade do valor CF vamos alterar o par\u00e2metro TABLE_CACHED_BLOCKS de 1 para 38<\/strong> e coletar novas estat\u00edsticas<\/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
<\/div><\/td>
SQL ><\/span> COL TABLE_CACHED_BLOCKS FOR<\/span> A30;<\/span>
\nSQL ><\/span> SELECT table_name,<\/span>
\n  2<\/span>         DBMS_STATS.<\/span>GET_PREFS(<\/span>ownname=><\/span>USER,<\/span>tabname=><\/span>table_name,<\/span>pname=><\/span>'TABLE_CACHED_BLOCKS'<\/span>)<\/span> TABLE_CACHED_BLOCKS
\n  3<\/span>    FROM dba_tables
\n  4<\/span>   WHERE table_name =<\/span> 'DBTW052'<\/span>;<\/span>
\n
\nTABLE_NAME                     TABLE_CACHED_BLOCKS
\n------------------------------<\/span> ------------------------------<\/span>
\nDBTW052                        1<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> EXEC<\/span><\/a> DBMS_STATS.<\/span>SET_TABLE_PREFS(<\/span>OWNNAME=><\/span>user,<\/span> TABNAME=><\/span>'DBTW052'<\/span>,<\/span> PNAME=><\/span>'TABLE_CACHED_BLOCKS'<\/span>,<\/span> PVALUE=><\/span>38<\/span>)<\/span>;<\/span>
\n
\nProcedimento PL\/<\/span>SQL conclu\u00eddo<\/span> com sucesso.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> EXEC<\/span><\/a> DBMS_STATS.<\/span>GATHER_INDEX_STATS(<\/span>OWNNAME=><\/span>user,<\/span> INDNAME=><\/span>'DBTW052_MATR_IDX'<\/span>,<\/span> ESTIMATE_PERCENT=><\/span> null<\/span>)<\/span>;<\/span>
\n
\nProcedimento PL\/<\/span>SQL conclu\u00eddo<\/span> com sucesso.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> SELECT T.<\/span>table_name,<\/span>
\n  2<\/span>         I.<\/span>index_name,<\/span>
\n  3<\/span>         T.<\/span>blocks,<\/span>
\n  4<\/span>         T.<\/span>num_rows,<\/span>
\n  5<\/span>         I.<\/span>clustering_factor
\n  6<\/span>    FROM user_tables T,<\/span>
\n  7<\/span>         user_indexes I
\n  8<\/span>   WHERE T.<\/span>table_name =<\/span> I.<\/span>table_name
\n  9<\/span>     AND I.<\/span>index_name =<\/span> 'DBTW052_MATR_IDX'<\/span>;<\/span>
\n
\nTABLE_NAME                     INDEX_NAME                         BLOCKS   NUM_ROWS CLUSTERING_FACTOR
\n------------------------------<\/span> ------------------------------<\/span> ----------<\/span> ----------<\/span> -----------------<\/span>
\nDBTW052                        DBTW052_MATR_IDX                     3142<\/span>     400000<\/span>              3038<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Ap\u00f3s a coleta das novas estat\u00edsticas podemos observar que o CLUSTERING FACTOR (CF) diminuiu de 266796 para 3038<\/strong> que \u00e9 um valor bem pr\u00f3ximo do numero de blocos da tabela (3142)<\/strong>.<\/span><\/p>\n

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

Feito o ajuste no CF vamos executar a consulta novamente e verificar se o \u00edndice vai ser utilizado e se vamos ter algum ganho de desempenho na consulta.<\/span><\/p>\n

 <\/p>\n

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
<\/div><\/td>
SQL ><\/span> ALTER SESSION SET statistics_level=<\/span>ALL;<\/span>
\n
\nSess\u00e3o alterada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> SELECT \/* dbtw522 *\/<\/span> *<\/span>
\n  2<\/span>    FROM dbtw052
\n  3<\/span>   WHERE matricula BETWEEN 42<\/span> AND 319<\/span>;<\/span>
\n
\n MATRICULA NOME
\n----------<\/span> --------------------------------------------------<\/span>
\n        42<\/span> DBTimeWizard -<\/span> Oracle Performance and Tuning
\n  .<\/span>
\n  .<\/span>
\n       319<\/span> DBTimeWizard -<\/span> Oracle Performance and Tuning
\n
\n278<\/span> linhas selecionadas.<\/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 '%dbtw522%'<\/span>
\n  4<\/span>     AND sql_text NOT LIKE '%v$sql%'<\/span>;<\/span>
\n
\nSQL_ID           CHILD_NUMBER
\n----------------<\/span> ------------<\/span>
\ndzgjfpgbutxpp               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>'dzgjfpgbutxpp'<\/span>,<\/span>         0<\/span>,<\/span>'ALLSTATS LAST'<\/span>)<\/span>)<\/span>
\n
\nPLAN_TABLE_OUTPUT
\n------------------------------------------------------------------------------------------------------------<\/span>
\n
\nSQL_ID  dzgjfpgbutxpp,<\/span> child number 0<\/span>
\n-------------------------------------<\/span>
\nSELECT \/* dbtw522 *\/<\/span> *<\/span>   FROM dbtw052  WHERE matricula BETWEEN
\n:<\/span>"SYS_B_0"<\/span> AND :<\/span>"SYS_B_1"<\/span>
\n
\nPlan hash<\/span><\/a> value:<\/span> 2969862332<\/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>    278<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>      44<\/span> |<\/span>
\n|*<\/span>  1<\/span> |<\/span>  FILTER                      |<\/span>                  |<\/span>      1<\/span> |<\/span>        |<\/span>    278<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>      44<\/span> |<\/span>
\n|<\/span>   2<\/span> |<\/span>   TABLE ACCESS BY INDEX ROWID|<\/span> DBTW052          |<\/span>      1<\/span> |<\/span>    279<\/span> |<\/span>    278<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>      44<\/span> |<\/span>
\n|*<\/span>  3<\/span> |<\/span>    INDEX RANGE<\/span><\/a> SCAN          |<\/span> DBTW052_MATR_IDX |<\/span>      1<\/span> |<\/span>    279<\/span> |<\/span>    278<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>      22<\/span> |<\/span>
\n-----------------------------------------------------------------------------------------------------------<\/span>
\n
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
\n---------------------------------------------------<\/span>
\n
\n   1<\/span> -<\/span> filter(<\/span>:<\/span>SYS_B_1>=:<\/span>SYS_B_0)<\/span>
\n   3<\/span> -<\/span> access(<\/span>"MATRICULA"<\/span>>=:<\/span>SYS_B_0 AND "MATRICULA"<\/span><=:<\/span>SYS_B_1)<\/span>
\n
\n
\n22<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Verificando o plano de execu\u00e7\u00e3o observamos que o \u00edndice foi utilizado e o n\u00famero de Buffers acessados caiu de 3109 na primeira consulta para 44 nesta consulta que utilizou o \u00edndice.<\/span><\/p>\n

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

O par\u00e2metro TABLE_CACHED_BLOCKS<\/strong> foi uma melhoria implementada no pacote DBMS_STATS<\/strong> no Oracle Database 11.2.0.4<\/strong>, com ele podemos aprimorar a qualidade do CF e dar ao Otimizador uma vis\u00e3o mais consistente da distribui\u00e7\u00e3o das linhas na tabela e sua rela\u00e7\u00e3o com as entradas no \u00edndice. Quando estiver avaliando o desempenho de uma instru\u00e7\u00e3o SQL e constatar que um \u00edndice que poderia melhorar o desempenho n\u00e3o esta sendo utilizado, considere a possibilidade do CF n\u00e3o estar refletindo a real rela\u00e7\u00e3o de ordena\u00e7\u00e3o entre as entradas do \u00edndice e as linhas nos blocos da tabela.<\/span><\/p>\n

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

https:\/\/docs.oracle.com\/cd\/E11882_01\/server.112<\/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":"

Neste artigo vamos verificar como a coleta do valor de CLUSTERING FACTOR de um \u00edndice pode influenciar de forma negativa\u00a0o plano de execu\u00e7\u00e3o de uma instru\u00e7\u00e3o SQL e o que podemos fazer para melhorar esta estat\u00edstica e ajudar o Otimizador a tomar uma decis\u00e3o mais assertiva. O CLUSTERING FACTOR \u00e9 uma das estat\u00edsticas mais importantes na tomada de decis\u00e3o pelo<\/p>\n","protected":false},"author":2,"featured_media":1660,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[71],"tags":[6,178,42,163,10,199],"class_list":["post-1659","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-estatisticas","tag-cbo","tag-clustering-factor","tag-cost-base-optimizer","tag-dbms_stats","tag-otimizador","tag-table_cached_blocks"],"aioseo_notices":[],"_links":{"self":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/1659","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=1659"}],"version-history":[{"count":22,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/1659\/revisions"}],"predecessor-version":[{"id":2075,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/1659\/revisions\/2075"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/media\/1660"}],"wp:attachment":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/media?parent=1659"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/categories?post=1659"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/tags?post=1659"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}