{"id":1393,"date":"2018-03-04T22:34:32","date_gmt":"2018-03-05T01:34:32","guid":{"rendered":"http:\/\/dbtimewizard.com.br\/blog\/?p=1393"},"modified":"2019-11-11T18:47:06","modified_gmt":"2019-11-11T21:47:06","slug":"por-que-100-linhas-a-mais-numa-tabela-podem-prejudicar-o-desempenho-de-uma-consulta","status":"publish","type":"post","link":"http:\/\/dbtimewizard.com.br\/blog\/por-que-100-linhas-a-mais-numa-tabela-podem-prejudicar-o-desempenho-de-uma-consulta\/","title":{"rendered":"Por que 100 linhas a mais numa tabela podem prejudicar o desempenho de uma consulta?"},"content":{"rendered":"

Voc\u00ea acredita que uma consulta numa tabela com mais de 300 mil linhas possa ter seu plano de execu\u00e7\u00e3o<\/strong><\/a> alterado pelo Otimizador quando inserimos mais 100 linhas na tabela? Neste artigo vamos ver uma simula\u00e7\u00e3o onde isto ocorre, explicar por que ocorre e quais s\u00e3o as alternativas para evitar este tipo de situa\u00e7\u00e3o.<\/span><\/p>\n

Nossa simula\u00e7\u00e3o utilizar\u00e1 uma consulta muito simples que faz a jun\u00e7\u00e3o em duas tabelas<\/strong><\/a>, uma com 300 mil linhas e outra com 1 milh\u00e3o de linhas e como resultado da consulta temos 62 linhas, o plano de execu\u00e7\u00e3o original utiliza a opera\u00e7\u00e3o NESTED LOOPS JOIN. Ap\u00f3s inserirmos 100 linhas na tabela de 300 mil linhas o Otimizador utilizar\u00e1 um novo plano de execu\u00e7\u00e3o que passar\u00e1 a utilizar a opera\u00e7\u00e3o HASH JOIN, com esta mudan\u00e7a no plano de execu\u00e7\u00e3o o tempo de resposta da consulta vai aumentar 128%.<\/span><\/p>\n

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

1) Criar 2 tabelas com um \u00edndice numa das tabelas
\n2) Verificar o custo atribu\u00eddo pelo Otimizador para acessar uma linha da tabela
\n3) Executar a consulta que faz jun\u00e7\u00e3o das duas tabelas e verificar a estat\u00edsticas de performance
\n4) Executar a opera\u00e7\u00e3o INSERT para adicionar mais 100 linhas numa das tabelas
\n5) Verificar o custo atribu\u00eddo pelo Otimizador para acessar uma linha da tabela ap\u00f3s a opera\u00e7\u00e3o de INSERT
\n6) Executar a consulta de jun\u00e7\u00e3o novamente e verificar o novo plano de execu\u00e7\u00e3o gerado pelo Otimizador<\/span><\/p>\n

1) Criar as tabelas 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
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 Release 11.<\/span>2.<\/span>0.<\/span>4.<\/span>0 -<\/span> 64bit Production
\n
\nSQL ><\/span>
\nSQL ><\/span> DROP TABLE dbtw002 purge;<\/span>
\n
\nTabela eliminada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> CREATE TABLE dbtw002
\n  2<\/span>  AS<\/span>
\n  3<\/span>  SELECT rownum col1,<\/span>
\n  4<\/span>         mod(<\/span>rownum,<\/span>100<\/span>)<\/span> col2,<\/span>
\n  5<\/span>         'dbtimewizard.com.br'<\/span> col3
\n  6<\/span>  FROM dual
\n  7<\/span>  CONNECT BY level <=<\/span> 302355<\/span>;<\/span>
\n
\nTabela criada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> create index dbtw002_idx on dbtw002(<\/span>col1)<\/span>;<\/span>
\n
\n\u00cdndice criado.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> EXEC<\/span><\/a> dbms_stats.<\/span>gather_table_stats(<\/span>USER,<\/span>'DBTW002'<\/span>,<\/span>method_opt=><\/span>'for all columns size auto'<\/span>,<\/span>cascade=><\/span>true<\/span>,<\/span>force=><\/span>true<\/span>)<\/span>;<\/span>
\n
\nProcedimento PL\/<\/span>SQL conclu\u00eddo<\/span> com sucesso.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> select blevel,<\/span> leaf_blocks,<\/span> clustering_factor from dba_indexes where index_name=<\/span>'DBTW002_IDX'<\/span>;<\/span>
\n
\n    BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR
\n----------<\/span> -----------<\/span> -----------------<\/span>
\n         1<\/span>         672<\/span>              1380<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> DROP TABLE dbtw004 purge;<\/span>
\n
\nTabela eliminada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> CREATE TABLE dbtw004
\n  2<\/span>  AS<\/span>
\n  3<\/span>  SELECT rownum col1,<\/span>
\n  4<\/span>         mod(<\/span>rownum,<\/span>10000<\/span>)<\/span> col2,<\/span>
\n  5<\/span>         '*DBTIMEWIZARD*'<\/span> col3
\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> EXEC<\/span><\/a> dbms_stats.<\/span>gather_table_stats(<\/span>USER,<\/span>'DBTW004'<\/span>,<\/span>method_opt=><\/span>'for all columns size auto'<\/span>,<\/span>cascade=><\/span>true<\/span>,<\/span>force=><\/span>true<\/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

Observando acima a consulta na vis\u00e3o DBA_INDEXES verificamos que o \u00edndice criado tem BLEVEL=1 e LEAF_BLOCKS=672, isto significa que o \u00edndice tem somente um bloco (ROOT_BLOCK) que endere\u00e7a os 672 LEAF BLOCKS, consequentemente para obter o ROW_ID de uma linha na tabela o banco vai acessar 2 blocos (ROOT BLOCK e 1 LEAF BLOCK).<\/span><\/p>\n

2) Verificar o CUSTO para acessar uma linha da tabela<\/span><\/h2>\n

Vamos realizar uma consulta na tabela onde criamos o \u00edndice e selecionar apenas uma linha para verificar quantos blocos ser\u00e3o acessados pelo banco e qual o custo atribu\u00eddo pelo Otimizador.<\/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
<\/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 \/* DBTW111 *\/<\/span> *<\/span>
\n  2<\/span>    FROM dbtw002
\n  3<\/span>   WHERE col1 =<\/span> 51<\/span>;<\/span>
\n
\n      COL1       COL2 COL3
\n----------<\/span> ----------<\/span> -------------------<\/span>
\n        51<\/span>         51<\/span> dbtimewizard.<\/span>com.<\/span>br
\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 '%DBTW111%'<\/span>
\n  4<\/span>     AND sql_text NOT LIKE '%v$sql%'<\/span>;<\/span>
\n
\nSQL_ID        CHILD_NUMBER
\n-------------<\/span> ------------<\/span>
\n2wj8c06ccrcuy            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>'2wj8c06ccrcuy'<\/span>,<\/span>         0<\/span>,<\/span>'typical iostats last'<\/span>)<\/span>)<\/span>
\n
\nPLAN_TABLE_OUTPUT
\n--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\nSQL_ID  2wj8c06ccrcuy,<\/span> child number 0<\/span>
\n-------------------------------------<\/span>
\nSELECT \/* DBTW111 *\/<\/span> *<\/span>   FROM dbtw002  WHERE col1 =<\/span> :<\/span>"SYS_B_0"<\/span>
\n
\nPlan hash<\/span><\/a> value:<\/span> 3681438309<\/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>     2<\/span> (<\/span>100<\/span>)<\/span>|<\/span>          |<\/span>      1<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>       4<\/span> |<\/span>
\n|<\/span>   1<\/span> |<\/span>  TABLE ACCESS BY INDEX ROWID|<\/span> DBTW002     |<\/span>      1<\/span> |<\/span>      1<\/span> |<\/span>    28<\/span> |<\/span>     2<\/span>   (<\/span>0<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/span> |<\/span>      1<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>       4<\/span> |<\/span>
\n|*<\/span>  2<\/span> |<\/span>   INDEX RANGE<\/span><\/a> SCAN          |<\/span> DBTW002_IDX |<\/span>      1<\/span> |<\/span>      1<\/span> |<\/span>       |<\/span>     1<\/span>   (<\/span>0<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/span> |<\/span>      1<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>       3<\/span> |<\/span>
\n-------------------------------------------------------------------------------------------------------------------------------------<\/span>
\n
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
\n---------------------------------------------------<\/span>
\n
\n   2<\/span> -<\/span> access(<\/span>"COL1"<\/span>=:<\/span>SYS_B_0)<\/span>
\n
\n
\n19<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Verificando o plano de execu\u00e7\u00e3o acima constatamos que a opera\u00e7\u00e3o ID 2 (INDEX RANGE SCAN) acessa 3 Blocos no Buffer Cache ( 1 ROOT BLOCK, 1 LEAF BLOCK e como o \u00edndice criado n\u00e3o \u00e9 UNIQUE um terceiro bloco para ver se existe mais alguma linha com o valor especificado na cl\u00e1usula WHERE). O custo atribu\u00eddo pelo Otimizador para esta opera\u00e7\u00e3o foi 1 e para recuperar a linha na tabela ser\u00e1 necess\u00e1rio acessar mais um bloco com custo de opera\u00e7\u00e3o igual a 1, portanto teremos um custo total de 2 para recuperar 1 linha da tabela.<\/span><\/p>\n

3) Executar a consulta de jun\u00e7\u00e3o das tabelas<\/span><\/h2>\n

Agora vamos executar uma consulta com a jun\u00e7\u00e3o das duas tabelas e verificar as estat\u00edsticas no plano de execu\u00e7\u00e3o.<\/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
<\/div><\/td>
SQL ><\/span> ALTER SESSION SET statistics_level =<\/span> ALL;<\/span>
\n
\nSess\u00e3o alterada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> SELECT \/* DBTW112 *\/<\/span> *<\/span>
\n  2<\/span>    FROM dbtw004 A,<\/span>
\n  3<\/span>         dbtw002 B
\n  4<\/span>   WHERE A.<\/span>col1=<\/span>B.<\/span>col1
\n  5<\/span>     AND A.<\/span>col2 in (<\/span>51<\/span>,<\/span>151<\/span>)<\/span>;<\/span>
\n
\n      COL1       COL2 COL3                 COL1       COL2 COL3
\n----------<\/span> ----------<\/span> --------------<\/span> ----------<\/span> ----------<\/span> -------------------<\/span>
\n        51<\/span>         51<\/span> *<\/span>DBTIMEWIZARD*<\/span>         51<\/span>         51<\/span> dbtimewizard.<\/span>com.<\/span>br
\n       151<\/span>        151<\/span> *<\/span>DBTIMEWIZARD*<\/span>        151<\/span>         51<\/span> dbtimewizard.<\/span>com.<\/span>br
\n     10051<\/span>         51<\/span> *<\/span>DBTIMEWIZARD*<\/span>      10051<\/span>         51<\/span> dbtimewizard.<\/span>com.<\/span>br
\n     10151<\/span>        151<\/span> *<\/span>DBTIMEWIZARD*<\/span>      10151<\/span>         51<\/span> dbtimewizard.<\/span>com.<\/span>br
\n     .<\/span>
\n     .<\/span>
\n     .<\/span>
\n    300151<\/span>        151<\/span> *<\/span>DBTIMEWIZARD*<\/span>     300151<\/span>         51<\/span> dbtimewizard.<\/span>com.<\/span>br
\n
\n62<\/span> linhas selecionadas.<\/span>
\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 '%DBTW112%'<\/span>
\n  4<\/span>     AND sql_text NOT LIKE '%v$sql%'<\/span>;<\/span>
\n
\nSQL_ID        CHILD_NUMBER
\n-------------<\/span> ------------<\/span>
\n5v1qcshkcgm74            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>'5v1qcshkcgm74'<\/span>,<\/span>         0<\/span>,<\/span>'typical iostats last'<\/span>)<\/span>)<\/span>
\n
\nPLAN_TABLE_OUTPUT
\n--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\nSQL_ID  5v1qcshkcgm74,<\/span> child number 0<\/span>
\n-------------------------------------<\/span>
\nSELECT \/* DBTW112 *\/<\/span> *<\/span>   FROM dbtw004 A,<\/span>        dbtw002 B  WHERE
\nA.<\/span>col1=<\/span>B.<\/span>col1    AND A.<\/span>col2 in (<\/span>:<\/span>"SYS_B_0"<\/span>,:<\/span>"SYS_B_1"<\/span>)<\/span>
\n
\nPlan hash<\/span><\/a> value:<\/span> 1144161524<\/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>  1386<\/span> (<\/span>100<\/span>)<\/span>|<\/span>          |<\/span>     62<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.07<\/span> |<\/span>    4146<\/span> |<\/span>
\n|<\/span>   1<\/span> |<\/span>  NESTED LOOPS                |<\/span>             |<\/span>      1<\/span> |<\/span>    200<\/span> |<\/span> 10400<\/span> |<\/span>  1386<\/span>   (<\/span>2<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/span> |<\/span>     62<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.07<\/span> |<\/span>    4146<\/span> |<\/span>
\n|<\/span>   2<\/span> |<\/span>   NESTED LOOPS               |<\/span>             |<\/span>      1<\/span> |<\/span>    200<\/span> |<\/span> 10400<\/span> |<\/span>  1386<\/span>   (<\/span>2<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/span> |<\/span>     62<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.07<\/span> |<\/span>    4104<\/span> |<\/span>
\n|*<\/span>  3<\/span> |<\/span>    TABLE ACCESS FULL         |<\/span> DBTW004     |<\/span>      1<\/span> |<\/span>    200<\/span> |<\/span>  4800<\/span> |<\/span>  1125<\/span>   (<\/span>2<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/span> |<\/span>    200<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.07<\/span> |<\/span>    4025<\/span> |<\/span>
\n|*<\/span>  4<\/span> |<\/span>    INDEX RANGE<\/span><\/a> SCAN          |<\/span> DBTW002_IDX |<\/span>    200<\/span> |<\/span>      1<\/span> |<\/span>       |<\/span>     1<\/span>   (<\/span>0<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/span> |<\/span>     62<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>      79<\/span> |<\/span>
\n|<\/span>   5<\/span> |<\/span>   TABLE ACCESS BY INDEX ROWID|<\/span> DBTW002     |<\/span>     62<\/span> |<\/span>      1<\/span> |<\/span>    28<\/span> |<\/span>     2<\/span>   (<\/span>0<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/span> |<\/span>     62<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>      42<\/span> |<\/span>
\n--------------------------------------------------------------------------------------------------------------------------------------<\/span>
\n
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
\n---------------------------------------------------<\/span>
\n
\n   3<\/span> -<\/span> filter(<\/span>(<\/span>"A"<\/span>.<\/span>"COL2"<\/span>=:<\/span>SYS_B_0 OR "A"<\/span>.<\/span>"COL2"<\/span>=:<\/span>SYS_B_1)<\/span>)<\/span>
\n   4<\/span> -<\/span> access(<\/span>"A"<\/span>.<\/span>"COL1"<\/span>=<\/span>"B"<\/span>.<\/span>"COL1"<\/span>)<\/span>
\n
\n
\n24<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Analisando o plano de execu\u00e7\u00e3o verificamos que a consulta retornou 62 linhas, o plano de execu\u00e7\u00e3o escolhido pelo Otimizador faz a jun\u00e7\u00e3o das duas tabelas utilizando a opera\u00e7\u00e3o NESTED LOOPS e o tempo de dura\u00e7\u00e3o da consulta foi 7 cent\u00e9simos de segundo.<\/span><\/p>\n

4) Executar a opera\u00e7\u00e3o INSERT<\/span><\/h2>\n

Abaixo vamos inserir mais 100 linhas na tabela DBTW002 e verificar o BLEVEL do \u00edndice.<\/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
<\/div><\/td>
SQL ><\/span> insert into dbtw002 select rownum+<\/span>302355<\/span>,<\/span> mod(<\/span>rownum,<\/span>100<\/span>)<\/span>,<\/span> 'dbtimewizard.com.br'<\/span> from dual connect by level <=<\/span>100<\/span>;<\/span>
\n
\n100<\/span> linhas criadas.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> commit;<\/span>
\n
\nCommit conclu\u00eddo<\/span>.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> EXEC<\/span><\/a> dbms_stats.<\/span>gather_table_stats(<\/span>USER,<\/span>'DBTW002'<\/span>,<\/span>method_opt=><\/span>'for all columns size auto'<\/span>,<\/span>cascade=><\/span>true<\/span>,<\/span>force=><\/span>true<\/span>)<\/span>;<\/span>
\n
\nProcedimento PL\/<\/span>SQL conclu\u00eddo<\/span> com sucesso.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> select blevel,<\/span> leaf_blocks,<\/span> clustering_factor from dba_indexes where index_name=<\/span>'DBTW002_IDX'<\/span>;<\/span>
\n
\n    BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR
\n----------<\/span> -----------<\/span> -----------------<\/span>
\n         2<\/span>         673<\/span>              1381<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Consultando a estrutura do \u00edndice ap\u00f3s a inser\u00e7\u00e3o das 100 linhas verificamos que o BLEVEL foi alterado para 2, ou seja a estrutura do \u00edndice agora \u00e9 formada por 1 ROOT BLOCK, 2 BRANCH BLOCKS e 673 LEAF BLOCKS.<\/span><\/p>\n

5) Verificar o CUSTO para acessar uma linha da tabela ap\u00f3s INSERT<\/span><\/h2>\n

Vamos realizar a consulta selecionando\u00a0 uma linha da tabela DBTW002 novamente e verificar qual foi o custo atribu\u00eddo pelo Otimizador para a consulta ap\u00f3s a inser\u00e7\u00e3o de 100 linhas na tabela.<\/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
<\/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 \/* DBTW113 *\/<\/span> *<\/span>
\n  2<\/span>    FROM dbtw002
\n  3<\/span>   WHERE col1 =<\/span> 51<\/span>;<\/span>
\n
\n      COL1       COL2 COL3
\n----------<\/span> ----------<\/span> -------------------<\/span>
\n        51<\/span>         51<\/span> dbtimewizard.<\/span>com.<\/span>br
\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 '%DBTW113%'<\/span>
\n  4<\/span>     AND sql_text NOT LIKE '%v$sql%'<\/span>;<\/span>
\n
\nSQL_ID        CHILD_NUMBER
\n-------------<\/span> ------------<\/span>
\ng74180qy0pmtp            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>'g74180qy0pmtp'<\/span>,<\/span>         0<\/span>,<\/span>'typical iostats last'<\/span>)<\/span>)<\/span>
\n
\nPLAN_TABLE_OUTPUT
\n--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\nSQL_ID  g74180qy0pmtp,<\/span> child number 0<\/span>
\n-------------------------------------<\/span>
\nSELECT \/* DBTW113 *\/<\/span> *<\/span>   FROM dbtw002  WHERE col1 =<\/span> :<\/span>"SYS_B_0"<\/span>
\n
\nPlan hash<\/span><\/a> value:<\/span> 3681438309<\/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>     4<\/span> (<\/span>100<\/span>)<\/span>|<\/span>          |<\/span>      1<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>       5<\/span> |<\/span>
\n|<\/span>   1<\/span> |<\/span>  TABLE ACCESS BY INDEX ROWID|<\/span> DBTW002     |<\/span>      1<\/span> |<\/span>      1<\/span> |<\/span>    28<\/span> |<\/span>     4<\/span>   (<\/span>0<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/span> |<\/span>      1<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>       5<\/span> |<\/span>
\n|*<\/span>  2<\/span> |<\/span>   INDEX RANGE<\/span><\/a> SCAN          |<\/span> DBTW002_IDX |<\/span>      1<\/span> |<\/span>      1<\/span> |<\/span>       |<\/span>     3<\/span>   (<\/span>0<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/span> |<\/span>      1<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>       4<\/span> |<\/span>
\n-------------------------------------------------------------------------------------------------------------------------------------<\/span>
\n
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
\n---------------------------------------------------<\/span>
\n
\n   2<\/span> -<\/span> access(<\/span>"COL1"<\/span>=:<\/span>SYS_B_0)<\/span>
\n
\n
\n19<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Observe no plano de execu\u00e7\u00e3o acima que opera\u00e7\u00e3o ID 2 (INDEX RANGE SCAN) agora passou a acessar 4 blocos no Buffer Cache e o custo que era igual a 1 antes do INSERT das 100 linhas, agora \u00e9 3 e o custo total da consulta passou de 2 para 4.\u00a0<\/span><\/p>\n

6) Executar a consulta de jun\u00e7\u00e3o das tabelas novamente<\/span><\/h2>\n

Para finalizar vamos executar a mesma consulta de jun\u00e7\u00e3o das tabelas ap\u00f3s a inser\u00e7\u00e3o das 100 linhas e verificar qual foi o impacto desta altera\u00e7\u00e3o no plano de execu\u00e7\u00e3o.<\/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
<\/div><\/td>
SQL ><\/span> ALTER SESSION SET statistics_level =<\/span> ALL;<\/span>
\n
\nSess\u00e3o alterada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> SELECT \/* DBTW114 *\/<\/span> *<\/span>
\n  2<\/span>    FROM dbtw004 A,<\/span>
\n  3<\/span>         dbtw002 B
\n  4<\/span>   WHERE A.<\/span>col1=<\/span>B.<\/span>col1
\n  5<\/span>     AND A.<\/span>col2 in (<\/span>51<\/span>,<\/span>151<\/span>)<\/span>;<\/span>
\n
\n      COL1       COL2 COL3                 COL1       COL2 COL3
\n----------<\/span> ----------<\/span> --------------<\/span> ----------<\/span> ----------<\/span> -------------------<\/span>
\n        51<\/span>         51<\/span> *<\/span>DBTIMEWIZARD*<\/span>         51<\/span>         51<\/span> dbtimewizard.<\/span>com.<\/span>br
\n       151<\/span>        151<\/span> *<\/span>DBTIMEWIZARD*<\/span>        151<\/span>         51<\/span> dbtimewizard.<\/span>com.<\/span>br
\n     10051<\/span>         51<\/span> *<\/span>DBTIMEWIZARD*<\/span>      10051<\/span>         51<\/span> dbtimewizard.<\/span>com.<\/span>br
\n     .<\/span>
\n     .<\/span>
\n     .<\/span>
\n    300151<\/span>        151<\/span> *<\/span>DBTIMEWIZARD*<\/span>     300151<\/span>         51<\/span> dbtimewizard.<\/span>com.<\/span>br
\n
\n62<\/span> linhas selecionadas.<\/span>
\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 '%DBTW114%'<\/span>
\n  4<\/span>     AND sql_text NOT LIKE '%v$sql%'<\/span>;<\/span>
\n
\nSQL_ID        CHILD_NUMBER
\n-------------<\/span> ------------<\/span>
\n61j8yfydn1ud4            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>'61j8yfydn1ud4'<\/span>,<\/span>         0<\/span>,<\/span>'typical iostats last'<\/span>)<\/span>)<\/span>
\n
\nPLAN_TABLE_OUTPUT
\n--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\nSQL_ID  61j8yfydn1ud4,<\/span> child number 0<\/span>
\n-------------------------------------<\/span>
\nSELECT \/* DBTW114 *\/<\/span> *<\/span>   FROM dbtw004 A,<\/span>        dbtw002 B  WHERE
\nA.<\/span>col1=<\/span>B.<\/span>col1    AND A.<\/span>col2 in (<\/span>:<\/span>"SYS_B_0"<\/span>,:<\/span>"SYS_B_1"<\/span>)<\/span>
\n
\nPlan hash<\/span><\/a> value:<\/span> 248469155<\/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>  1531<\/span> (<\/span>100<\/span>)<\/span>|<\/span>          |<\/span>     62<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.16<\/span> |<\/span>    5428<\/span> |<\/span>
\n|*<\/span>  1<\/span> |<\/span>  HASH<\/span><\/a> JOIN<\/span><\/a>         |<\/span>         |<\/span>      1<\/span> |<\/span>    200<\/span> |<\/span> 10400<\/span> |<\/span>  1531<\/span>   (<\/span>2<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/span> |<\/span>     62<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.16<\/span> |<\/span>    5428<\/span> |<\/span>
\n|*<\/span>  2<\/span> |<\/span>   TABLE ACCESS FULL|<\/span> DBTW004 |<\/span>      1<\/span> |<\/span>    200<\/span> |<\/span>  4800<\/span> |<\/span>  1125<\/span>   (<\/span>2<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/span> |<\/span>    200<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.07<\/span> |<\/span>    4020<\/span> |<\/span>
\n|<\/span>   3<\/span> |<\/span>   TABLE ACCESS FULL|<\/span> DBTW002 |<\/span>      1<\/span> |<\/span>    302K|<\/span>  8270K|<\/span>   404<\/span>   (<\/span>1<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/span> |<\/span>    302K|<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.03<\/span> |<\/span>    1408<\/span> |<\/span>
\n------------------------------------------------------------------------------------------------------------------------<\/span>
\n
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
\n---------------------------------------------------<\/span>
\n
\n   1<\/span> -<\/span> access(<\/span>"A"<\/span>.<\/span>"COL1"<\/span>=<\/span>"B"<\/span>.<\/span>"COL1"<\/span>)<\/span>
\n   2<\/span> -<\/span> filter(<\/span>(<\/span>"A"<\/span>.<\/span>"COL2"<\/span>=:<\/span>SYS_B_0 OR "A"<\/span>.<\/span>"COL2"<\/span>=:<\/span>SYS_B_1)<\/span>)<\/span>
\n
\n
\n22<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Analisando o plano de execu\u00e7\u00e3o verificamos que a opera\u00e7\u00e3o de jun\u00e7\u00e3o escolhida pelo Otimizador passou a ser um HASH JOIN, o Otimizador escolheu este plano pois o custo do plano utilizando a opera\u00e7\u00e3o NESTED LOOPS passou de 1386 (antes do INSERT) para 1786 (ap\u00f3s o INSERT), a diferen\u00e7a de 400 foi adicionada ao custo total pois o custo para acessar uma linha no indice passou de 1 para 3 conforme demonstrado nas etapas 2 e 5. Como o custo total do plano com HASH JOIN foi de 1531 o Otimizador escolheu este plano pois o valor \u00e9 menor que 1786.<\/span><\/p>\n

Observamos tamb\u00e9m que o tempo de reposta da consulta com o novo plano de execu\u00e7\u00e3o aumentou para 16 cent\u00e9simos de segundo, um aumento consider\u00e1vel de 128%. Estamos falando aqui de um aumento de 9 cent\u00e9simos de segundos em uma consulta, isso parece insignificante, mas temos que lembrar que algumas consultas podem executar milh\u00f5es de vezes num banco e se uma destas apresentar este tipo de situa\u00e7\u00e3o podemos ter um grande impacto no banco.<\/span><\/p>\n

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

Por incr\u00edvel que possa parecer o tipo de tabela utilizada nesse artigo \u00e9 muito comum nos banco de dados, s\u00e3o as famosas tabelas de par\u00e2metros das aplica\u00e7\u00f5es que s\u00e3o alteradas com pouca frequ\u00eancia, mas que podem apresentar uma situa\u00e7\u00e3o semelhante a que utilizamos nesta simula\u00e7\u00e3o. Para prevenir este tipo de problema existem algumas op\u00e7\u00f5es, podemos utilizar o SQL PLAN MANAGEMENT<\/strong><\/a> que vai controlar as mudan\u00e7as nos plano de execu\u00e7\u00e3o e impedir uma regress\u00e3o de performance, podemos fixar as estat\u00edsticas das tabelas impedindo que sejam atualizadas, podemos utilizar SQL PROFILE<\/strong><\/a> para fixar o plano de execu\u00e7\u00e3o. Voc\u00ea pode escolher qual a melhor op\u00e7\u00e3o para as bases que administra.<\/span><\/p>\n

 
\n 
\n 
\n<\/a><\/p>\n

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

Voc\u00ea acredita que uma consulta numa tabela com mais de 300 mil linhas possa ter seu plano de execu\u00e7\u00e3o alterado pelo Otimizador quando inserimos mais 100 linhas na tabela? Neste artigo vamos ver uma simula\u00e7\u00e3o onde isto ocorre, explicar por que ocorre e quais s\u00e3o as alternativas para evitar este tipo de situa\u00e7\u00e3o. Nossa simula\u00e7\u00e3o utilizar\u00e1 uma consulta muito simples<\/p>\n","protected":false},"author":2,"featured_media":1394,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[62],"tags":[192,194,193],"class_list":["post-1393","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-indice","tag-blevel","tag-estabilidade-do-plano-de-execucao","tag-execution-plan-stability"],"aioseo_notices":[],"_links":{"self":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/1393","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=1393"}],"version-history":[{"count":30,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/1393\/revisions"}],"predecessor-version":[{"id":2078,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/1393\/revisions\/2078"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/media\/1394"}],"wp:attachment":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/media?parent=1393"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/categories?post=1393"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/tags?post=1393"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}