{"id":1942,"date":"2019-01-10T22:45:58","date_gmt":"2019-01-11T00:45:58","guid":{"rendered":"http:\/\/dbtimewizard.com.br\/blog\/?p=1942"},"modified":"2019-11-11T18:35:38","modified_gmt":"2019-11-11T21:35:38","slug":"nem-so-de-estatisticas-vive-o-otimizador","status":"publish","type":"post","link":"http:\/\/dbtimewizard.com.br\/blog\/nem-so-de-estatisticas-vive-o-otimizador\/","title":{"rendered":"Nem s\u00f3 de estat\u00edsticas vive o Otimizador"},"content":{"rendered":"

As estat\u00edsticas do banco de dados Oracle<\/a><\/strong> s\u00e3o a mat\u00e9ria prima que o Otimizador utiliza para gerar o plano de execu\u00e7\u00e3o de uma instru\u00e7\u00e3o SQL, quanto melhor for a qualidade das estat\u00edsticas maior ser\u00e1 o desempenho do plano de execu\u00e7\u00e3o, porem o Otimizador trabalha com outras informa\u00e7\u00f5es al\u00e9m das estat\u00edsticas, ele utiliza alguns par\u00e2metros de inicializa\u00e7\u00e3o do banco, ele pode utilizar informa\u00e7\u00f5es de alguns recursos que influenciam em suas decis\u00f5es (SQL_Profile<\/a><\/strong>, SQL_Patch<\/a><\/strong>, SQL Plan Baseline<\/a><\/strong>, Hints<\/a><\/strong>, etc…), mas os que nem todos sabem \u00e9 que o Otimizador utiliza informa\u00e7\u00f5es das constraints para definir que tipo de opera\u00e7\u00e3o ser\u00e1 utilizada para acessar uma tabela. <\/span>
\n 
\nNeste artigo vamos mostrar um exemplo de uma consulta que deveria utilizar um \u00edndice mas devido a aus\u00eancia de uma CONSTRAINT o Otimizador escolhe fazer uma opera\u00e7\u00e3o TABLE ACCESS FULL.<\/span><\/p>\n

Para realizar esta simula\u00e7\u00e3o vamos passar pelas seguintes etapas:<\/span><\/p>\n

1) Criar uma tabela com um \u00edndice <\/span><\/strong>
\n2) Executar uma consulta na tabela que faz um COUNT() na coluna utilizada no \u00edndice<\/span><\/strong>
\n3) Verificar porque o Otimizador n\u00e3o utilizou o \u00edndice<\/span><\/strong>
\n4) Executar a consulta novamente utilizando o \u00edndice<\/span><\/strong><\/p>\n

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

Vamos criar uma tabela com 500 mil registros, criar um \u00edndice na coluna CATEGORY e coletar as estat\u00edsticas.<\/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
<\/div><\/td>
SQL ><\/span> SELECT *<\/span> FROM V$VERSION<\/span> where rownum <<\/span> 2<\/span>;<\/span>
\n
\nBANNER                                                                               CON_ID
\n--------------------------------------------------------------------------------<\/span> ----------<\/span>
\nOracle Database 12c Enterprise Edition Release 12.<\/span>2.<\/span>0.<\/span>1.<\/span>0 -<\/span> 64bit Production              0<\/span>
\n
\n1<\/span> linha selecionada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> create table dbtw058 (<\/span>id ,<\/span> category,<\/span> text1,<\/span> text2,<\/span> text3 )<\/span>
\n  2<\/span>      as<\/span>
\n  3<\/span>      select rownum,<\/span> 'MOD-'<\/span>||<\/span>mod(<\/span>rownum,<\/span> 10<\/span>)<\/span>,<\/span> 'DBTimeWizard - Performance and Tuning'<\/span>,<\/span> DBMS_RANDOM.<\/span>STRING(<\/span>'A'<\/span>,<\/span> 100<\/span>)<\/span>,<\/span> DBMS_RANDOM.<\/span>STRING(<\/span>'A'<\/span>,<\/span> 100<\/span>)<\/span>
\n  4<\/span>        from dual connect by level <=<\/span> 500000<\/span>;<\/span>
\n
\nTabela criada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> create index dbtw058_idx on dbtw058(<\/span>category)<\/span>;<\/span>
\n
\n\u00cdndice criado.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> exec<\/span><\/a> dbms_stats.<\/span>gather_table_stats(<\/span>ownname=><\/span> USER,<\/span> tabname=><\/span> 'DBTW058'<\/span>,<\/span> cascade=><\/span> true<\/span>,<\/span> method_opt=><\/span> 'FOR ALL COLUMNS SIZE AUTO'<\/span>,<\/span>force=><\/span> true<\/span>,<\/span>degree=><\/span> 16<\/span>)<\/span>;<\/span>
\n
\nProcedimento PL\/<\/span>SQL conclu\u00eddo<\/span> com sucesso.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

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

 <\/p>\n

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
<\/div><\/td>
SQL ><\/span> ALTER SESSION SET statistics_level=<\/span>ALL;<\/span>
\n
\nSess\u00e3o alterada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> SELECT \/* dbtw-058.1 *\/<\/span> category,<\/span> count<\/span><\/a>(<\/span>1<\/span>)<\/span>
\n  2<\/span>    FROM dbtw058
\n  3<\/span>   GROUP BY category
\n  4<\/span>   ORDER BY category;<\/span>
\n
\nCATEGORY                                       COUNT<\/span><\/a>(<\/span>1<\/span>)<\/span>
\n--------------------------------------------<\/span> ----------<\/span>
\nMOD-<\/span>0<\/span>                                             50000<\/span>
\nMOD-<\/span>1<\/span>                                             50000<\/span>
\nMOD-<\/span>2<\/span>                                             50000<\/span>
\nMOD-<\/span>3<\/span>                                             50000<\/span>
\nMOD-<\/span>4<\/span>                                             50000<\/span>
\nMOD-<\/span>5<\/span>                                             50000<\/span>
\nMOD-<\/span>6<\/span>                                             50000<\/span>
\nMOD-<\/span>7<\/span>                                             50000<\/span>
\nMOD-<\/span>8<\/span>                                             50000<\/span>
\nMOD-<\/span>9<\/span>                                             50000<\/span>
\n
\n10<\/span> linhas selecionadas.<\/span>
\n
\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 '%dbtw-058.1%'<\/span>
\n  4<\/span>     AND sql_text NOT LIKE '%v$sql%'<\/span>;<\/span>
\n
\nSQL_ID        CHILD_NUMBER
\n-------------<\/span> ------------<\/span>
\n2gt3ddpgpjp5y            0<\/span>
\n
\n1<\/span> linha selecionada.<\/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>
\n
\nPLAN_TABLE_OUTPUT
\n---------------------------------------------------------------------------------------------------------------------------------<\/span>
\nSQL_ID  2gt3ddpgpjp5y,<\/span> child number 0<\/span>
\n-------------------------------------<\/span>
\nSELECT \/* dbtw-058.1 *\/<\/span> category,<\/span> count<\/span><\/a>(<\/span>1<\/span>)<\/span>   FROM dbtw058  GROUP BY
\ncategory  ORDER BY category
\n
\nPlan hash<\/span><\/a> value:<\/span> 3195600276<\/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> Reads  |<\/span>  OMem |<\/span>  1Mem |<\/span> Used-<\/span>Mem |<\/span>
\n-----------------------------------------------------------------------------------------------------------------------------<\/span>
\n|<\/span>   0<\/span> |<\/span> SELECT STATEMENT    |<\/span>         |<\/span>      1<\/span> |<\/span>        |<\/span>     10<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.21<\/span> |<\/span>   17902<\/span> |<\/span>  17858<\/span> |<\/span>       |<\/span>       |<\/span>          |<\/span>
\n|<\/span>   1<\/span> |<\/span>  SORT<\/span><\/a> ORDER BY      |<\/span>         |<\/span>      1<\/span> |<\/span>     10<\/span> |<\/span>     10<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.21<\/span> |<\/span>   17902<\/span> |<\/span>  17858<\/span> |<\/span>  2048<\/span> |<\/span>  2048<\/span> |<\/span> 2048<\/span>  (<\/span>0<\/span>)<\/span>|<\/span>
\n|<\/span>   2<\/span> |<\/span>   HASH<\/span><\/a> GROUP BY     |<\/span>         |<\/span>      1<\/span> |<\/span>     10<\/span> |<\/span>     10<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.21<\/span> |<\/span>   17902<\/span> |<\/span>  17858<\/span> |<\/span>  1484K|<\/span>  1484K|<\/span> 1021K (<\/span>0<\/span>)<\/span>|<\/span>
\n|<\/span>   3<\/span> |<\/span>    TABLE ACCESS FULL|<\/span> DBTW058 |<\/span>      1<\/span> |<\/span>    500K|<\/span>    500K|<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.16<\/span> |<\/span>   17902<\/span> |<\/span>  17858<\/span> |<\/span>       |<\/span>       |<\/span>          |<\/span>
\n-----------------------------------------------------------------------------------------------------------------------------<\/span>
\n
\n
\n16<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Observe no plano de execu\u00e7\u00e3o que apesar de termos criado um \u00edndice na coluna CATEGORY, o Otimizador resolveu acessar os registros na tabela utilizando uma opera\u00e7\u00e3o TABLE ACCESS FULL.\u00a0<\/span><\/p>\n

3) Verificar porque o Otimizador n\u00e3o utilizou o \u00edndice<\/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
<\/div><\/td>
SQL ><\/span> COL index_name FOR<\/span> a20
\nSQL ><\/span> SELECT index_name,<\/span>
\n  2<\/span>         num_rows,<\/span>
\n  3<\/span>         distinct_keys,<\/span>
\n  4<\/span>         clustering_factor,<\/span>
\n  5<\/span>         status,<\/span>
\n  6<\/span>         visibility,<\/span>
\n  7<\/span>         last_analyzed
\n  8<\/span>    FROM user_indexes
\n  9<\/span>   WHERE table_name =<\/span> 'DBTW058'<\/span>;<\/span>
\n
\nINDEX_NAME             NUM_ROWS DISTINCT_KEYS CLUSTERING_FACTOR STATUS     VISIBILIT LAST_ANA
\n--------------------<\/span> ----------<\/span> -------------<\/span> -----------------<\/span> ----------<\/span> ---------<\/span> --------<\/span>
\nDBTW058_IDX              500000<\/span>            10<\/span>            178606<\/span> VALID      VISIBLE   04<\/span>\/<\/span>01<\/span>\/<\/span>19<\/span>
\n
\n1<\/span> linha selecionada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> COL column_name FOR<\/span> a20
\nSQL ><\/span> SELECT column_name,<\/span>
\n  2<\/span>         num_distinct,<\/span>
\n  3<\/span>         density,<\/span>
\n  4<\/span>         num_nulls,<\/span>
\n  5<\/span>         avg_col_len,<\/span>
\n  6<\/span>         histogram
\n  7<\/span>    FROM user_tab_col_statistics
\n  8<\/span>   WHERE table_name =<\/span> 'DBTW058'<\/span>;<\/span>
\n
\nCOLUMN_NAME          NUM_DISTINCT      DENSITY  NUM_NULLS AVG_COL_LEN HISTOGRAM
\n--------------------<\/span> ------------<\/span> ------------<\/span> ----------<\/span> -----------<\/span> ---------------<\/span>
\nID                         500000<\/span>   .000002000<\/span>          0<\/span>           5<\/span> NONE
\nCATEGORY                       10<\/span>   .100000000<\/span>          0<\/span>           6<\/span> NONE
\nTEXT1                           1<\/span>  1.000000000<\/span>          0<\/span>          38<\/span> NONE
\nTEXT2                      500000<\/span>   .000002000<\/span>          0<\/span>         101<\/span> NONE
\nTEXT3                      500000<\/span>   .000002000<\/span>          0<\/span>         101<\/span> NONE
\n
\n5<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> SET lines 60<\/span>
\nSQL ><\/span> DESC dbtw058
\n Nome                          Nulo?    Tipo
\n -----------------------------<\/span> --------<\/span> --------------------<\/span>
\n ID                                     NUMBER
\n CATEGORY                               VARCHAR2(<\/span>44<\/span>)<\/span>
\n TEXT1                                  CHAR(<\/span>37<\/span>)<\/span>
\n TEXT2                                  VARCHAR2(<\/span>4000<\/span>)<\/span>
\n TEXT3                                  VARCHAR2(<\/span>4000<\/span>)<\/span>
\n
\nSQL ><\/span> SET lines 200<\/span>
\nSQL ><\/span>
\nSQL ><\/span> COL segment_name FOR<\/span> a20
\nSQL ><\/span> SELECT segment_name,<\/span> segment_type,<\/span> ROUND<\/span><\/a>(<\/span>bytes\/<\/span>1024<\/span>\/<\/span>1024<\/span>)<\/span> MB
\n  2<\/span>    FROM user_segments
\n  3<\/span>   WHERE segment_name IN (<\/span>'DBTW058'<\/span>,<\/span>'DBTW058_IDX'<\/span>)<\/span>;<\/span>
\n
\nSEGMENT_NAME         SEGMENT_TYPE               MB
\n--------------------<\/span> ------------------<\/span> ----------<\/span>
\nDBTW058              TABLE                     144<\/span>
\nDBTW058_IDX          INDEX                      10<\/span>
\n
\n2<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Investigando o motivo pelo qual o Otimizador n\u00e3o utilizou o \u00edndice no plano de execu\u00e7\u00e3o podemos chegar a algumas conclus\u00f5es baseadas nas informa\u00e7\u00f5es da tabela e do \u00edndice acima:<\/span><\/p>\n

1) A consulta trabalha somente com a coluna CATEGORY, logo isso n\u00e3o seria obst\u00e1culo para utiliza\u00e7\u00e3o do \u00edndice<\/span>
\n2) N\u00e3o h\u00e1 registros nulos na coluna CATEGORY, logo a quantidade de registros no \u00edndice \u00e9 igual a quantidade de registro na tabela<\/span>
\n3) A descri\u00e7\u00e3o das colunas da tabela mostra que a coluna CATEGORY n\u00e3o possui uma CONSTRAINT NOT NULL<\/span><\/p>\n

Como n\u00e3o existe uma CONSTRAINT NOT NULL na coluna CATEGORY o Otimizador n\u00e3o tem como saber se existe algum registro com valor nulo nesta coluna, pois apesar das estat\u00edsticas da coluna CATEGORY indicarem que n\u00e3o existem valores nulos, precisamos lembrar que a atualiza\u00e7\u00e3o das estat\u00edsticas n\u00e3o \u00e9 din\u00e2mica, ela reflete uma posi\u00e7\u00e3o em um determinado momento, ap\u00f3s a ultima coleta de estat\u00edsticas pode ter sido inserido um registro nesta tabela com valor nulo na coluna CATEGORY.<\/span><\/p>\n

Portanto, a menos que seja criada uma CONSTRAINT NOT NULL na coluna CATEGORY, o Otimizador n\u00e3o utilizar\u00e1 o \u00edndice criado nesta coluna, pois ele n\u00e3o tem como saber COM PRECIS\u00c3O se o \u00edndice tem a mesma quantidade de registros existentes na tabela naquele momento.<\/span><\/p>\n

 <\/p>\n

4) Executar a consulta novamente utilizando o \u00edndice<\/span><\/h2>\n

Para permitir a utiliza\u00e7\u00e3o do \u00edndice vamos criar a CONSTRAINT NOT NULL na coluna CATEGORY e verificar se o Otimizador vai utilizar o \u00edndice 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
<\/div><\/td>
SQL ><\/span> ALTER TABLE dbtw058 MODIFY (<\/span>category NOT NULL<\/span>)<\/span>;<\/span>
\n
\nTabela alterada.<\/span>
\n
\nSQL ><\/span> ALTER SESSION SET statistics_level=<\/span>ALL;<\/span>
\n
\nSess\u00e3o alterada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> SELECT \/* dbtw-058.2 *\/<\/span> category,<\/span> count<\/span><\/a>(<\/span>1<\/span>)<\/span>
\n  2<\/span>    FROM dbtw058
\n  3<\/span>   GROUP BY category
\n  4<\/span>   ORDER BY category;<\/span>
\n
\nCATEGORY                                       COUNT<\/span><\/a>(<\/span>1<\/span>)<\/span>
\n--------------------------------------------<\/span> ----------<\/span>
\nMOD-<\/span>0<\/span>                                             50000<\/span>
\nMOD-<\/span>1<\/span>                                             50000<\/span>
\nMOD-<\/span>2<\/span>                                             50000<\/span>
\nMOD-<\/span>3<\/span>                                             50000<\/span>
\nMOD-<\/span>4<\/span>                                             50000<\/span>
\nMOD-<\/span>5<\/span>                                             50000<\/span>
\nMOD-<\/span>6<\/span>                                             50000<\/span>
\nMOD-<\/span>7<\/span>                                             50000<\/span>
\nMOD-<\/span>8<\/span>                                             50000<\/span>
\nMOD-<\/span>9<\/span>                                             50000<\/span>
\n
\n10<\/span> linhas selecionadas.<\/span>
\n
\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 '%dbtw-058.2%'<\/span>
\n  4<\/span>     AND sql_text NOT LIKE '%v$sql%'<\/span>;<\/span>
\n
\nSQL_ID        CHILD_NUMBER
\n-------------<\/span> ------------<\/span>
\nbwt9h7a7ttk33            0<\/span>
\n
\n1<\/span> linha selecionada.<\/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>
\n
\nPLAN_TABLE_OUTPUT
\n-------------------------------------------------------------------------------------------------------------------------------<\/span>
\nSQL_ID  bwt9h7a7ttk33,<\/span> child number 0<\/span>
\n-------------------------------------<\/span>
\nSELECT \/* dbtw-058.2 *\/<\/span> category,<\/span> count<\/span><\/a>(<\/span>1<\/span>)<\/span>   FROM dbtw058  GROUP BY
\ncategory  ORDER BY category
\n
\nPlan hash<\/span><\/a> value:<\/span> 3494643104<\/span>
\n
\n---------------------------------------------------------------------------------------------------------------------------<\/span>
\n|<\/span> Id  |<\/span> Operation              |<\/span> Name        |<\/span> Starts |<\/span> E-<\/span>Rows |<\/span> A-<\/span>Rows |<\/span>   A-<\/span>Time<\/span><\/a>   |<\/span> Buffers |<\/span>  OMem |<\/span>  1Mem |<\/span> Used-<\/span>Mem |<\/span>
\n---------------------------------------------------------------------------------------------------------------------------<\/span>
\n|<\/span>   0<\/span> |<\/span> SELECT STATEMENT       |<\/span>             |<\/span>      1<\/span> |<\/span>        |<\/span>     10<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.09<\/span> |<\/span>    1198<\/span> |<\/span>       |<\/span>       |<\/span>          |<\/span>
\n|<\/span>   1<\/span> |<\/span>  SORT<\/span><\/a> ORDER BY         |<\/span>             |<\/span>      1<\/span> |<\/span>     10<\/span> |<\/span>     10<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.09<\/span> |<\/span>    1198<\/span> |<\/span>  2048<\/span> |<\/span>  2048<\/span> |<\/span> 2048<\/span>  (<\/span>0<\/span>)<\/span>|<\/span>
\n|<\/span>   2<\/span> |<\/span>   HASH<\/span><\/a> GROUP BY        |<\/span>             |<\/span>      1<\/span> |<\/span>     10<\/span> |<\/span>     10<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.09<\/span> |<\/span>    1198<\/span> |<\/span>  1484K|<\/span>  1484K|<\/span> 1008K (<\/span>0<\/span>)<\/span>|<\/span>
\n|<\/span>   3<\/span> |<\/span>    INDEX FAST FULL SCAN|<\/span> DBTW058_IDX |<\/span>      1<\/span> |<\/span>    500K|<\/span>    500K|<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.04<\/span> |<\/span>    1198<\/span> |<\/span>       |<\/span>       |<\/span>          |<\/span>
\n---------------------------------------------------------------------------------------------------------------------------<\/span>
\n
\n
\n16<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Analisando o plano de execu\u00e7\u00e3o podemos verificar que o Otimizador passou a utilizar o \u00edndice, o numero de buffers acessados caiu de 17K para 1K e o tempo de execu\u00e7\u00e3o caiu de 21 cent\u00e9simos de segundo para 9 cent\u00e9simos.<\/span><\/p>\n

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

Este \u00e9 apenas um exemplo de como a falta de CONSTRAINTS em uma tabela pode afetar de forma negativa o plano de execu\u00e7\u00e3o de uma instru\u00e7\u00e3o SQL, portanto \u00e9 imperativo que durante a cria\u00e7\u00e3o de uma base de dados as CONSTRAINTS PRIMARY KEY, UNIQUE KEY, NOT NULL, etc… sejam corretamente definidas para que o Otimizador consiga entregar um plano de execu\u00e7\u00e3o com melhor desempenho.<\/span><\/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":"

As estat\u00edsticas do banco de dados Oracle s\u00e3o a mat\u00e9ria prima que o Otimizador utiliza para gerar o plano de execu\u00e7\u00e3o de uma instru\u00e7\u00e3o SQL, quanto melhor for a qualidade das estat\u00edsticas maior ser\u00e1 o desempenho do plano de execu\u00e7\u00e3o, porem o Otimizador trabalha com outras informa\u00e7\u00f5es al\u00e9m das estat\u00edsticas, ele utiliza alguns par\u00e2metros de inicializa\u00e7\u00e3o do banco, ele pode<\/p>\n","protected":false},"author":2,"featured_media":1943,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[62],"tags":[212,213,10,11],"class_list":["post-1942","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-indice","tag-contraints","tag-index-fast-full-scan","tag-otimizador","tag-plano-de-execucao"],"aioseo_notices":[],"_links":{"self":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/1942","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=1942"}],"version-history":[{"count":14,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/1942\/revisions"}],"predecessor-version":[{"id":2070,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/1942\/revisions\/2070"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/media\/1943"}],"wp:attachment":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/media?parent=1942"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/categories?post=1942"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/tags?post=1942"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}