{"id":1144,"date":"2017-10-06T11:07:10","date_gmt":"2017-10-06T14:07:10","guid":{"rendered":"http:\/\/dbtimewizard.com.br\/blog\/?p=1144"},"modified":"2019-11-11T18:48:47","modified_gmt":"2019-11-11T21:48:47","slug":"o-que-voce-precisa-saber-sobre-o-uso-de-indices","status":"publish","type":"post","link":"http:\/\/dbtimewizard.com.br\/blog\/o-que-voce-precisa-saber-sobre-o-uso-de-indices\/","title":{"rendered":"O que voc\u00ea precisa saber sobre o uso de \u00edndices?"},"content":{"rendered":"

Existem muitos artigos na internet afirmando que a partir de um certo percentual de registros acessados em uma tabela o Otimizador do Oracle prefere utilizar uma opera\u00e7\u00e3o TABLE ACCESS FULL, este percentual na verdade n\u00e3o existe, para definir o tipo de opera\u00e7\u00e3o que vai utilizar, o Otimizador se baseia em uma s\u00e9rie de informa\u00e7\u00f5es que combinadas v\u00e3o determinar o tipo de opera\u00e7\u00e3o mais eficiente, neste artigo vamos abordar um destes fatores que tem um peso muito grande na tomada de decis\u00e3o pelo Otimizador. Vamos entender a import\u00e2ncia da estat\u00edstica CLUSTERING FACTOR e ver na pr\u00e1tica como ela determina que a leitura\u00a0 de 1% dos registros de uma tabela pode ser mais eficiente\u00a0tanto para uma opera\u00e7\u00e3o INDEX RANGE SCAN\u00a0como para uma opera\u00e7\u00e3o de TABLE ACCESS FULL.\u00a0<\/span><\/p>\n

 <\/p>\n

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

1) Criar duas tabelas para realizar as simula\u00e7\u00f5es<\/span><\/p>\n

2) Executar uma consulta na primeira tabela contando o numero de registros existentes para um determinado valor da coluna RESULT<\/span><\/p>\n

3) Apresentar os c\u00e1lculos que o Otimizador realizou para escolher o acesso a tabela utilizando a opera\u00e7\u00e3o TABLE ACCESS FULL<\/span><\/p>\n

4) Executar uma consulta na segunda tabela contando o numero de registros existentes para um determinado valor da coluna RESULT<\/span><\/p>\n

5) Apresentar os c\u00e1lculos que o Otimizador realizou para escolher o acesso a tabela utilizando a opera\u00e7\u00e3o INDEX RANGE SCAN<\/span><\/p>\n

1) Criar as tabelas para as simula\u00e7\u00f5es<\/span><\/h2>\n

Vamos criar uma tabela com 10.000.000 de registros, sendo que a coluna RESULT possui somente 100 valores distintos e uma segunda tabela\u00a0clone da\u00a0primeira classificando os registros pela coluna RESULT.<\/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
<\/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
\nDecorrido:<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>00.04<\/span>
\nSQL ><\/span>
\nSQL ><\/span> ALTER SESSION SET WORKAREA_SIZE_POLICY=<\/span>MANUAL;<\/span>
\n
\nSess\u00e3o alterada.<\/span>
\n
\nDecorrido:<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span>
\nSQL ><\/span>
\nSQL ><\/span> ALTER SESSION SET SORT_AREA_SIZE=<\/span>2000000000<\/span>;<\/span>
\n
\nSess\u00e3o alterada.<\/span>
\n
\nDecorrido:<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span>
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> create table dbtw101 (<\/span>id ,<\/span> result,<\/span> text )<\/span>
\n  2<\/span>      as<\/span>
\n  3<\/span>      select rownum,<\/span> mod(<\/span>rownum,<\/span> 100<\/span>)<\/span>,<\/span> 'DBTimeWizard - Performance and Tuning'<\/span>
\n  4<\/span>        from dual connect by level <=<\/span> 10000000<\/span>;<\/span>
\n
\nTabela criada.<\/span>
\n
\nDecorrido:<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>12.84<\/span>
\nSQL ><\/span>
\nSQL ><\/span> create index dbtw01_idx on dbtw101(<\/span>result)<\/span>;<\/span>
\n
\n\u00cdndice criado.<\/span>
\n
\nDecorrido:<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>14.18<\/span>
\nSQL ><\/span>
\nSQL ><\/span> exec<\/span><\/a> dbms_stats.<\/span>gather_table_stats(<\/span>ownname=><\/span> USER,<\/span> tabname=><\/span> 'DBTW101'<\/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
\nDecorrido:<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>26.80<\/span>
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> create table dbtw102 (<\/span>id ,<\/span> result,<\/span> text )<\/span>
\n  2<\/span>      as<\/span>
\n  3<\/span>      select *<\/span>
\n  4<\/span>        from dbtw101
\n  5<\/span>       order by result;<\/span>
\n
\nTabela criada.<\/span>
\n
\nDecorrido:<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>16.48<\/span>
\nSQL ><\/span>
\nSQL ><\/span> create index dbtw02_idx on dbtw102(<\/span>result)<\/span>;<\/span>
\n
\n\u00cdndice criado.<\/span>
\n
\nDecorrido:<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>10.53<\/span>
\nSQL ><\/span>
\nSQL ><\/span> exec<\/span><\/a> dbms_stats.<\/span>gather_table_stats(<\/span>ownname=><\/span> USER,<\/span> tabname=><\/span> 'DBTW102'<\/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
\nDecorrido:<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>13.52<\/span>
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

2) Executar a consulta na primeira tabela<\/span><\/h2>\n

Agora vamos executar uma consulta na primeira tabela, acessando 1% do numero de linhas dela e verificar que o Otimizador vai escolher acessar a tabela utilizando uma opera\u00e7\u00e3o TABLE ACCESS FULL.<\/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
<\/div><\/td>
SQL ><\/span> ALTER SESSION SET statistics_level =<\/span> ALL;<\/span>
\n
\nSess\u00e3o alterada.<\/span>
\n
\nDecorrido:<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span>
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> SELECT \/* DBTW001 *\/<\/span> count<\/span><\/a>(<\/span>ID)<\/span>
\n  2<\/span>    FROM dbtw101
\n  3<\/span>   WHERE result =<\/span> 67<\/span>;<\/span>
\n
\n COUNT<\/span><\/a>(<\/span>ID)<\/span>
\n----------<\/span>
\n    100000<\/span>
\n
\nDecorrido:<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>16.88<\/span>
\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 '%DBTW001%'<\/span>
\n  4<\/span>     AND sql_text NOT LIKE '%v$sql%'<\/span>;<\/span>
\n
\nSQL_ID        CHILD_NUMBER
\n-------------<\/span> ------------<\/span>
\nd30cb6crgfyx1            0<\/span>
\n
\nDecorrido:<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>00.21<\/span>
\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>'d30cb6crgfyx1'<\/span>,<\/span>         0<\/span>,<\/span>'typical iostats last'<\/span>)<\/span>)<\/span>
\n
\nPLAN_TABLE_OUTPUT
\n--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\nSQL_ID  d30cb6crgfyx1,<\/span> child number 0<\/span>
\n-------------------------------------<\/span>
\nSELECT \/* DBTW001 *\/<\/span> count<\/span><\/a>(<\/span>ID)<\/span>   FROM dbtw101  WHERE result =<\/span> :<\/span>"SYS_B_0"<\/span>
\n
\nPlan hash<\/span><\/a> value:<\/span> 3256843722<\/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>  2466<\/span> (<\/span>100<\/span>)<\/span>|<\/span>          |<\/span>      1<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>16.84<\/span> |<\/span>   71843<\/span> |<\/span>  71839<\/span> |<\/span>
\n|<\/span>   1<\/span> |<\/span>  SORT<\/span><\/a> AGGREGATE<\/span><\/a>    |<\/span>         |<\/span>      1<\/span> |<\/span>      1<\/span> |<\/span>     9<\/span> |<\/span>            |<\/span>          |<\/span>      1<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>16.84<\/span> |<\/span>   71843<\/span> |<\/span>  71839<\/span> |<\/span>
\n|*<\/span>  2<\/span> |<\/span>   TABLE ACCESS FULL|<\/span> DBTW101 |<\/span>      1<\/span> |<\/span>    100K|<\/span>   878K|<\/span>  2466<\/span>   (<\/span>9<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/span> |<\/span>    100K|<\/span>00<\/span>:<\/span>00<\/span>:<\/span>16.84<\/span> |<\/span>   71843<\/span> |<\/span>  71839<\/span> |<\/span>
\n---------------------------------------------------------------------------------------------------------------------------------<\/span>
\n
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
\n---------------------------------------------------<\/span>
\n
\n   2<\/span> -<\/span> filter(<\/span>"RESULT"<\/span>=:<\/span>SYS_B_0)<\/span>
\n
\n
\n19<\/span> linhas selecionadas.<\/span>
\n
\nDecorrido:<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>00.71<\/span>
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

3) Verificar c\u00e1lculos utilizados pelo Otimizador<\/span><\/h2>\n

Na primeira tabela o CLUSTERING FACTOR do \u00edndice da tabela \u00e9 muito ruim, o valor \u00e9 bem pr\u00f3ximo do numero total de linhas da tabela\u00a07.287.656, isso faz com que a recupera\u00e7\u00e3o de linhas da tabela utilizando \u00edndice cause um volume de acesso a bloco de dados muito alto, inviabilizando a utiliza\u00e7\u00e3o do \u00edndice.<\/span><\/p>\n

 <\/p>\n

Na figura abaixo temos uma representa\u00e7\u00e3o gr\u00e1fica de um CLUSTERING FACTOR ruim, podemos observar que os blocos de \u00edndices est\u00e3o totalmente desorganizados em rela\u00e7\u00e3o aos blocos de dados, nesta situa\u00e7\u00e3o as linhas com um determinado valor para uma coluna referenciada no \u00edndice podem estar espalhadas em v\u00e1rios blocos da tabela tornando o acesso com o uso do \u00edndice muito caro.<\/span><\/p>\n

\"CF2\"<\/a><\/p>\n

 <\/p>\n

C\u00e1lculos para acessar a tabela usando \u00edndice<\/span><\/h3>\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
<\/div><\/td>
SQL ><\/span> select INDEX_NAME,<\/span> blevel,<\/span> num_rows,<\/span> distinct_keys,<\/span> CLUSTERING_FACTOR,<\/span> LEAF_BLOCKS from dba_indexes where table_name =<\/span> 'DBTW101'<\/span>;<\/span>
\n
\nINDEX_NAME                         BLEVEL   NUM_ROWS DISTINCT_KEYS CLUSTERING_FACTOR LEAF_BLOCKS
\n------------------------------<\/span> ----------<\/span> ----------<\/span> -------------<\/span> -----------------<\/span> -----------<\/span>
\nDBTW01_IDX                              2<\/span>   10139378<\/span>           100<\/span>           7287656<\/span>       19794<\/span>
\n
\n1<\/span> linha selecionada.<\/span>
\n
\nDecorrido:<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>00.82<\/span>
\nSQL ><\/span>
\n
\nSQL ><\/span> SELECT TABLE_NAME,<\/span> COLUMN_NAME,<\/span> DENSITY FROM dba_tab_col_statistics WHERE table_name =<\/span> 'DBTW101'<\/span> AND COLUMN_NAME=<\/span>'RESULT'<\/span>;<\/span>
\n
\nTABLE_NAME                     COLUMN_NAME                         DENSITY
\n------------------------------<\/span> ------------------------------<\/span> ------------<\/span>
\nDBTW101                        RESULT                           .010000000<\/span>
\n
\n1<\/span> linha selecionada.<\/span>
\n
\nDecorrido:<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>00.18<\/span>
\nSQL ><\/span>
\n
\nFormula para calcular custo da opera\u00e7\u00e3o leitura do<\/span> indice:<\/span>
\n
\nBLEVEL +<\/span> (<\/span>LEAF BLOCKS X DENSITY)<\/span> =<\/span> 2<\/span> +<\/span> (<\/span>19794<\/span> X 0<\/span>,<\/span>01<\/span>)<\/span> =<\/span> 2<\/span> +<\/span> 198<\/span> =<\/span> 200<\/span>
\n
\nFormula para calcular custo da opera\u00e7\u00e3o leitura da tabela pelo rowid:<\/span>
\n
\nDENSITY *<\/span> CLUSTERING_FACTOR =<\/span> 0<\/span>,<\/span>01<\/span> *<\/span> 7287656<\/span> =<\/span>  72.876<\/span>
\n
\nCUSTO DO<\/span> ACESSO A TABELA UTILIZANDO \u00cdNDICE =<\/span> 200<\/span> +<\/span> 72.876<\/span> =<\/span> 73.006<\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Na primeira consulta realizada acima o Otimizador escolheu fazer uma opera\u00e7\u00e3o TABLE ACCESS FULL pois o custo dessa opera\u00e7\u00e3o era menor que o da opera\u00e7\u00e3o INDEX RANGE SCAN. Nas formulas acima aplicando-se os valores estat\u00edsticos da tabela e do \u00edndice, verificamos que o custo total para acessar as linhas solicitadas pela consulta seria 73.006.<\/span><\/p>\n

Obs: As formulas acima s\u00e3o aplicadas pelo Otimizador quando a consulta \u00e9 realizada em apenas uma tabela e somente um filtro na cl\u00e1usula WHERE, se a consulta for um join de duas ou mais tabelas e\/ou houverem mais filtros na cl\u00e1usula WHERE o Otimizador utilizar\u00e1 outras formulas.<\/span><\/p>\n

 <\/p>\n

C\u00e1lculos para acessar a tabela usando TABLE ACCESS FULL<\/span><\/h3>\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
<\/div><\/td>
SQL ><\/span> select NUM_ROWS,<\/span> BLOCKS FROM DBA_TABLES WHERE TABLE_NAME =<\/span> 'DBTW101'<\/span>;<\/span>
\n
\n  NUM_ROWS     BLOCKS
\n----------<\/span> ----------<\/span>
\n  10000000<\/span>      72226<\/span>
\n
\n1<\/span> linha selecionada.<\/span>
\n
\nDecorrido:<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01.18<\/span>
\nSQL ><\/span>
\n
\nSQL ><\/span> SELECT *<\/span> FROM SYS.<\/span>AUX_STATS$;<\/span>
\n
\nSNAME                          PNAME                               PVAL1 PVAL2
\n------------------------------<\/span> ------------------------------<\/span> ----------<\/span> ------------------------------<\/span>
\nSYSSTATS_INFO                  STATUS                                    COMPLETED
\nSYSSTATS_INFO                  DSTART                                    09<\/span>-<\/span>29<\/span>-<\/span>2017<\/span> 21<\/span>:<\/span>51<\/span>
\nSYSSTATS_INFO                  DSTOP                                     09<\/span>-<\/span>29<\/span>-<\/span>2017<\/span> 21<\/span>:<\/span>51<\/span>
\nSYSSTATS_INFO                  FLAGS                                   1<\/span>
\nSYSSTATS_MAIN                  CPUSPEEDNW                     3074<\/span>,<\/span>07407<\/span>
\nSYSSTATS_MAIN                  IOSEEKTIM                              10<\/span>
\nSYSSTATS_MAIN                  IOTFRSPEED                           4096<\/span>
\nSYSSTATS_MAIN                  SREADTIM                                5<\/span>
\nSYSSTATS_MAIN                  MREADTIM                               10<\/span>
\nSYSSTATS_MAIN                  CPUSPEED                             2618<\/span>
\nSYSSTATS_MAIN                  MBRC                                   64<\/span>
\nSYSSTATS_MAIN                  MAXTHR                             262144<\/span>
\nSYSSTATS_MAIN                  SLAVETHR                            16384<\/span>
\n
\n13<\/span> linhas selecionadas.<\/span>
\n
\nDecorrido:<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>00.15<\/span>
\nSQL ><\/span>
\n
\nFormula para calcular custo da opera\u00e7\u00e3o leitura da tabela (<\/span>TABLE ACCESS FULL)<\/span>:<\/span>
\n
\nTOTAL BLOCKS \/<\/span> MBRC *<\/span> (<\/span>MREADTIM\/<\/span>SREADTIM)<\/span> =<\/span> 72226<\/span> \/<\/span> 64<\/span> *<\/span> (<\/span>10<\/span>\/<\/span>5<\/span>)<\/span>  =<\/span> 2257<\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Quando se trata do custo da opera\u00e7\u00e3o TABLE ACCESS FULL, ao contr\u00e1rio da leitura do \u00edndice usando a opera\u00e7\u00e3o INDEX RANGE SCAN, que faz a leitura de um bloco de cada vez, nesta opera\u00e7\u00e3o s\u00e3o feitas leituras de\u00a0 m\u00faltiplos blocos. Na formula acima temos o numero de blocos da tabela dividido pelo MBRC que corresponde a quantidade de blocos acessados em cada leitura multipla. O tempo de leitura de um bloco para Otimizador equivale ao custo 1, como o tempo de leitura de multiplos blocos (MREADTIM) \u00e9 o dobro do tempo da leitura de um bloco (SREADTIM) o Otimizador considerar\u00e1 cada leitura de m\u00faltiplos blocos com o custo 2. Como resultado da formula acima temos um custo de 2.257 que \u00e9 muito inferior ao custo da leitura utilizando \u00edndice 73.006, por isto o Otimizador escolheu fazer a leitura da tabela utilizando a opera\u00e7\u00e3o TABLE ACCESS FULL.<\/p>\n

4) Executar a consulta na\u00a0segunda tabela<\/span><\/h2>\n

Agora vamos executar uma consulta na\u00a0segunda tabela que \u00e9 um clone da primeira, acessando os mesmos 1% de linhas dela e verificar que o Otimizador desta vez vai\u00a0 escolher acessar a tabela utilizando\u00a0a opera\u00e7\u00e3o INDEX RANGE SCAN.<\/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
<\/div><\/td>
SQL ><\/span> ALTER SESSION SET statistics_level =<\/span> ALL;<\/span>
\n
\nSess\u00e3o alterada.<\/span>
\n
\nDecorrido:<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>00.00<\/span>
\nSQL ><\/span>
\nSQL ><\/span> SELECT \/* DBTW002 *\/<\/span> count<\/span><\/a>(<\/span>ID)<\/span>
\n  2<\/span>    FROM dbtw102
\n  3<\/span>   WHERE result =<\/span> 67<\/span>;<\/span>
\n
\n COUNT<\/span><\/a>(<\/span>ID)<\/span>
\n----------<\/span>
\n    100000<\/span>
\n
\nDecorrido:<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>00.82<\/span>
\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 '%DBTW002%'<\/span>
\n  4<\/span>     AND sql_text NOT LIKE '%v$sql%'<\/span>;<\/span>
\n
\nSQL_ID        CHILD_NUMBER
\n-------------<\/span> ------------<\/span>
\n1fagpjtw47nfm            0<\/span>
\n
\nDecorrido:<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>00.08<\/span>
\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>'1fagpjtw47nfm'<\/span>,<\/span>         0<\/span>,<\/span>'typical iostats last'<\/span>)<\/span>)<\/span>
\n
\nPLAN_TABLE_OUTPUT
\n--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\nSQL_ID  1fagpjtw47nfm,<\/span> child number 0<\/span>
\n-------------------------------------<\/span>
\nSELECT \/* DBTW002 *\/<\/span> count<\/span><\/a>(<\/span>ID)<\/span>   FROM dbtw102  WHERE result =<\/span> :<\/span>"SYS_B_0"<\/span>
\n
\nPlan hash<\/span><\/a> value:<\/span> 2285397273<\/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>  1115<\/span> (<\/span>100<\/span>)<\/span>|<\/span>          |<\/span>      1<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.81<\/span> |<\/span>     918<\/span> |<\/span>    917<\/span> |<\/span>
\n|<\/span>   1<\/span> |<\/span>  SORT<\/span><\/a> AGGREGATE<\/span><\/a>              |<\/span>            |<\/span>      1<\/span> |<\/span>      1<\/span> |<\/span>     9<\/span> |<\/span>            |<\/span>          |<\/span>      1<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.81<\/span> |<\/span>     918<\/span> |<\/span>    917<\/span> |<\/span>
\n|<\/span>   2<\/span> |<\/span>   TABLE ACCESS BY INDEX ROWID|<\/span> DBTW102    |<\/span>      1<\/span> |<\/span>    100K|<\/span>   878K|<\/span>  1115<\/span>   (<\/span>1<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/span> |<\/span>    100K|<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.81<\/span> |<\/span>     918<\/span> |<\/span>    917<\/span> |<\/span>
\n|*<\/span>  3<\/span> |<\/span>    INDEX RANGE<\/span><\/a> SCAN          |<\/span> DBTW02_IDX |<\/span>      1<\/span> |<\/span>    101K|<\/span>       |<\/span>   201<\/span>   (<\/span>1<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/span> |<\/span>    100K|<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.21<\/span> |<\/span>     199<\/span> |<\/span>    198<\/span> |<\/span>
\n----------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\n
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
\n---------------------------------------------------<\/span>
\n
\n   3<\/span> -<\/span> access(<\/span>"RESULT"<\/span>=:<\/span>SYS_B_0)<\/span>
\n
\n
\n20<\/span> linhas selecionadas.<\/span>
\n
\nDecorrido:<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>00.29<\/span>
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

5) Verificar c\u00e1lculos utilizados pelo Otimizador<\/span><\/h2>\n

Na\u00a0segunda tabela o CLUSTERING FACTOR do \u00edndice da tabela \u00e9 muito bom, o valor \u00e9 bem pr\u00f3ximo do numero total de\u00a0blocos da tabela 91.303, isso faz com que um acesso a tabela utilizando \u00edndice\u00a0seja muito eficiente.<\/span><\/p>\n

 <\/p>\n

Na figura abaixo temos uma representa\u00e7\u00e3o gr\u00e1fica de um CLUSTERING FACTOR muito bom, podemos observar que os blocos de \u00edndices est\u00e3o totalmente organizados em rela\u00e7\u00e3o aos blocos de dados, nesta situa\u00e7\u00e3o os registros com um determinado valor para uma coluna referenciada no \u00edndice estar\u00e3o agrupados em poucos blocos da tabela tornando o acesso \u00e1 tabela com o uso do \u00edndice muito eficiente.<\/span><\/p>\n

 
\n\"CF1\"<\/a>
\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
<\/div><\/td>
SQL ><\/span> select INDEX_NAME,<\/span> blevel,<\/span> num_rows,<\/span> distinct_keys,<\/span> CLUSTERING_FACTOR,<\/span> LEAF_BLOCKS from dba_indexes where table_name =<\/span> 'DBTW102'<\/span>;<\/span>
\n
\nINDEX_NAME                         BLEVEL   NUM_ROWS DISTINCT_KEYS CLUSTERING_FACTOR LEAF_BLOCKS
\n------------------------------<\/span> ----------<\/span> ----------<\/span> -------------<\/span> -----------------<\/span> -----------<\/span>
\nDBTW02_IDX                              2<\/span>   10135438<\/span>           100<\/span>             91303<\/span>       19780<\/span>
\n
\n1<\/span> linha selecionada.<\/span>
\n
\nDecorrido:<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>00.16<\/span>
\nSQL ><\/span>
\nSQL ><\/span> SELECT TABLE_NAME,<\/span> COLUMN_NAME,<\/span> DENSITY FROM dba_tab_col_statistics WHERE table_name =<\/span> 'DBTW102'<\/span> AND COLUMN_NAME=<\/span>'RESULT'<\/span>;<\/span>
\n
\nTABLE_NAME                     COLUMN_NAME                       DENSITY
\n------------------------------<\/span> ------------------------------<\/span> ----------<\/span>
\nDBTW102                        RESULT                                ,<\/span>01<\/span>
\n
\n1<\/span> linha selecionada.<\/span>
\n
\nDecorrido:<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>00.27<\/span>
\nSQL ><\/span>
\n
\nFormula para calcular custo da opera\u00e7\u00e3o leitura do<\/span> indice:<\/span>
\n
\nBLEVEL +<\/span> (<\/span>LEAF BLOCKS X DENSITY)<\/span> =<\/span> 2<\/span> +<\/span> (<\/span>19780<\/span> X 0<\/span>,<\/span>01<\/span>)<\/span> =<\/span> 2<\/span> +<\/span> 198<\/span> =<\/span> 200<\/span>
\n
\nFormula para calcular custo da opera\u00e7\u00e3o leitura da tabela pelo rowid:<\/span>
\n
\nDENSITY *<\/span> CLUSTERING_FACTOR =<\/span> 0<\/span>,<\/span>01<\/span> *<\/span> 91303<\/span> =<\/span>  913<\/span>
\n
\nCUSTO DO<\/span> ACESSO A TABELA UTILIZANDO \u00cdNDICE =<\/span> 200<\/span> +<\/span> 913<\/span> =<\/span> 1.113<\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Aplicando as formulas do Otimizador e utilizando os valores estat\u00edsticos da tabela e do \u00edndice, verificamos que o custo do acesso utilizando \u00cdNDICE \u00e9 igual 1.113, Um pouco menor do que aparece no plano de execu\u00e7\u00e3o pois neste valor n\u00e3o foi computado o custo CPU.<\/span><\/p>\n

 <\/p>\n

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

Esta \u00e9 uma demonstra\u00e7\u00e3o simples para mostrar que a escolha\u00a0pelo Otimizador da opera\u00e7\u00e3o INDEX RANGE SCAN\u00a0ou a TABLE ACCESS FULL nem sempre depende da porcentagem de linhas que uma consulta deve recuperar. Em vez disso, depende de fatores cr\u00edticos como a distribui\u00e7\u00e3o de dados, o n\u00famero de leaf blocks no \u00edndice, o n\u00famero m\u00e9dio de linhas em um bloco de tabela, o n\u00famero m\u00e9dio de entradas nos leaf blocks do \u00edndice, o clustering factor do \u00edndice e o valor do par\u00e2metro db_file_multiblock_read_count, simplesmente n\u00e3o existe uma porcentagem m\u00e1gica de linhas que fa\u00e7a o Otimizador escolher uma\u00a0opera\u00e7\u00e3o INDEX RANGE SCAN\u00a0ou TABLE ACCESS FULL.<\/span><\/p>\n

 <\/p>\n

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

http:\/\/docs.oracle.com\/cd\/E25178_01\/server.1111\/e16638\/optimops.htm#i82433<\/a><\/strong><\/span><\/p>\n

 
\n 
\n\"Promo-D75L\"<\/a><\/p>\n

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

Existem muitos artigos na internet afirmando que a partir de um certo percentual de registros acessados em uma tabela o Otimizador do Oracle prefere utilizar uma opera\u00e7\u00e3o TABLE ACCESS FULL, este percentual na verdade n\u00e3o existe, para definir o tipo de opera\u00e7\u00e3o que vai utilizar, o Otimizador se baseia em uma s\u00e9rie de informa\u00e7\u00f5es que combinadas v\u00e3o determinar o tipo<\/p>\n","protected":false},"author":2,"featured_media":1143,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[62],"tags":[178,82,10,29],"class_list":["post-1144","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-indice","tag-clustering-factor","tag-optimizer","tag-otimizador","tag-performance-tuning"],"aioseo_notices":[],"_links":{"self":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/1144","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=1144"}],"version-history":[{"count":43,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/1144\/revisions"}],"predecessor-version":[{"id":2081,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/1144\/revisions\/2081"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/media\/1143"}],"wp:attachment":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/media?parent=1144"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/categories?post=1144"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/tags?post=1144"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}