{"id":2242,"date":"2020-12-08T11:55:56","date_gmt":"2020-12-08T14:55:56","guid":{"rendered":"http:\/\/dbtimewizard.com.br\/blog\/?p=2242"},"modified":"2020-12-08T11:55:56","modified_gmt":"2020-12-08T14:55:56","slug":"voce-nao-precisa-mais-criar-indices-no-banco-de-dados","status":"publish","type":"post","link":"http:\/\/dbtimewizard.com.br\/blog\/voce-nao-precisa-mais-criar-indices-no-banco-de-dados\/","title":{"rendered":"Voc\u00ea n\u00e3o precisa mais criar \u00edndices no banco de dados"},"content":{"rendered":"

O t\u00edtulo deste artigo \u00e9 uma das promessas do novo recurso AUTOMATIC INDEXING do banco de dados Oracle 19c, ele promete automatizar a cria\u00e7\u00e3o, elimina\u00e7\u00e3o e reorganiza\u00e7\u00e3o dos \u00edndices no banco de dados. Este recurso \u00e9 uma evolu\u00e7\u00e3o fant\u00e1stica em termos de desempenho para o banco de dados pois uma grande parcela dos problemas de desempenho das instru\u00e7\u00f5es SQL est\u00e3o relacionados a falta de \u00edndices ou \u00edndices inadequados, porem precisamos ter em mente que este recurso foi introduzido agora e vai evoluir nos pr\u00f3ximos meses e anos, como todo novo recurso ele precisa ser utilizado considerando as boas pr\u00e1ticas que um ambiente de produ\u00e7\u00e3o necessita.<\/span><\/p>\n

Neste artigo vamos ver na pr\u00e1tica como funciona o recurso AUTOMATIC INDEXING, vamos fazer uma simula\u00e7\u00e3o bem simples para entender como funciona o mecanismo de automatiza\u00e7\u00e3o da cria\u00e7\u00e3o de \u00edndices.<\/span><\/p>\n

 
\n <\/p>\n

O recurso AUTOMATIC INDEXING esta dispon\u00edvel somente para bases Enterprise Edition em ambientes Engineered Systems, para efeitos did\u00e1ticos vamos utilizar o par\u00e2metro “_exadata_feature_on” para testar este recurso numa base Enterprise Edition que N\u00c3O esta instalada num Exadata, esta op\u00e7\u00e3o n\u00e3o tem suporte da Oracle e n\u00e3o deve ser utilizada em ambientes de produ\u00e7\u00e3o.<\/span><\/p>\n

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

1) Configurar a base para realizar a simula\u00e7\u00e3o <\/span><\/strong>
\n2) Executar uma consulta no banco com filtro na cl\u00e1usula WHERE<\/span><\/strong>
\n3) Consultar o relat\u00f3rio de \u00edndices autom\u00e1ticos<\/span><\/strong>
\n4) Consultar novamente o relat\u00f3rio de \u00edndices autom\u00e1ticos ap\u00f3s 15 minutos<\/span><\/strong>
\n5) Executar a mesma consulta novamente<\/span><\/strong><\/p>\n

1) Configurar a base para realizar a simula\u00e7\u00e3o<\/span><\/h2>\n

Alem de criar a tabela que ser\u00e1 utilizada na simula\u00e7\u00e3o, vamos desativar o recurso SQL PLAN BASELINE para simplificar o processo de utiliza\u00e7\u00e3o do novo \u00edndice, a utiliza\u00e7\u00e3o do recurso SQL PLAN BASELINE \u00e9 uma boa pr\u00e1tica e pode ser utilizado simultaneamente com o recurso AUTOMATIC INDEXING. <\/span>
\n 
\nPara esta simula\u00e7\u00e3o vamos utilizar o usu\u00e1rio DBTW e atrav\u00e9s do pacote DBMS_AUTO_INDEX vamos configurar o recurso AUTOMATIC INDEXING para atuar somente neste esquema.<\/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
<\/div><\/td>
SQL ><\/span> SELECT banner_full FROM v$version<\/span> WHERE rownum <<\/span> 2<\/span>;<\/span>
\n
\nBANNER_FULL
\n---------------------------------------------------------------------------------------<\/span>
\nOracle Database 19c Enterprise Edition Release 19.<\/span>0.<\/span>0.<\/span>0.<\/span>0 -<\/span> Production
\nVersion 19.<\/span>3.<\/span>0.<\/span>0.<\/span>0
\n
\n
\nSQL ><\/span>
\nSQL ><\/span> ALTER SESSION SET OPTIMIZER_USE_SQL_PLAN_BASELINES=<\/span>FALSE<\/span>;<\/span>
\n
\nSess\u00e3o alterada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> EXEC<\/span><\/a> DBMS_AUTO_INDEX.<\/span>CONFIGURE(<\/span>'AUTO_INDEX_SCHEMA'<\/span>,<\/span> 'DBTW'<\/span>,<\/span> allow =><\/span> TRUE<\/span>)<\/span>;<\/span>
\n
\nProcedimento PL\/<\/span>SQL conclu\u00eddo<\/span> com sucesso.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> COL parameter_name FOR<\/span> A40
\nSQL ><\/span> COL parameter_value FOR<\/span> A15
\nSQL ><\/span>
\nSQL ><\/span> SELECT con_id,<\/span> parameter_name,<\/span> parameter_value
\n  2<\/span>    FROM cdb_auto_index_config
\n  3<\/span>   ORDER BY 1<\/span>,<\/span>2<\/span>;<\/span>
\n
\n    CON_ID PARAMETER_NAME                           PARAMETER_VALUE
\n----------<\/span> ----------------------------------------<\/span> ---------------<\/span>
\n         3<\/span> AUTO_INDEX_COMPRESSION                   OFF
\n         3<\/span> AUTO_INDEX_DEFAULT_TABLESPACE
\n         3<\/span> AUTO_INDEX_MODE                          IMPLEMENT
\n         3<\/span> AUTO_INDEX_REPORT_RETENTION              31<\/span>
\n         3<\/span> AUTO_INDEX_RETENTION_FOR_AUTO            373<\/span>
\n         3<\/span> AUTO_INDEX_RETENTION_FOR_MANUAL
\n         3<\/span> AUTO_INDEX_SCHEMA                        schema IN (<\/span>DBTW)<\/span>
\n         3<\/span> AUTO_INDEX_SPACE_BUDGET                  50<\/span>
\n
\n8<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> CREATE TABLE dbtw067 AS<\/span> SELECT rownum id,<\/span> S.*<\/span> FROM sh.<\/span>sales S;<\/span>
\n
\nTabela criada.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Observe acima que a consulta na vis\u00e3o CDB_AUTO_INDEX_CONFIG mostra que o recurso AUTOMATIC INDEXING esta ativado na modalidade “IMPLEMENT”, isto significa que os \u00edndices identificados pelo AUTOMATIC INDEXING ser\u00e3o criados e estar\u00e3o acess\u00edveis para utiliza\u00e7\u00e3o pelo Otimizador.<\/span><\/p>\n

2) Executar uma consulta no banco com filtro na cl\u00e1usula WHERE<\/span><\/h2>\n

Quando executamos uma consulta na tabela criada para esta simula\u00e7\u00e3o, o Otimizador vai utilizar a opera\u00e7\u00e3o TABLE ACCESS FULL pois n\u00e3o foi criado nenhum \u00edndice para esta 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
<\/div><\/td>
SQL ><\/span> ALTER SESSION SET statistics_level=<\/span>ALL;<\/span>
\n
\nSess\u00e3o alterada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> SELECT \/* DBTW067.1 *\/<\/span> *<\/span>
\n  2<\/span>    FROM dbtw067
\n  3<\/span>   WHERE id=<\/span>4921<\/span>;<\/span>
\n
\n        ID    PROD_ID    CUST_ID TIME_ID  CHANNEL_ID   PROMO_ID QUANTITY_SOLD AMOUNT_SOLD
\n----------<\/span> ----------<\/span> ----------<\/span> --------<\/span> ----------<\/span> ----------<\/span> -------------<\/span> -----------<\/span>
\n      4921<\/span>         25<\/span>       6273<\/span> 23<\/span>\/<\/span>01<\/span>\/<\/span>98<\/span>          2<\/span>        999<\/span>             1<\/span>      128<\/span>,<\/span>32<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> COL sql_id       NEW_VALUE m_sql_id
\nSQL ><\/span> COL child_number NEW_VALUE m_child_no
\nSQL ><\/span> SELECT sql_id,<\/span> child_number
\n  2<\/span>    FROM v$sql<\/span>
\n  3<\/span>   WHERE sql_text LIKE '%DBTW067.1%'<\/span>
\n  4<\/span>     AND sql_text NOT LIKE '%v$sql%'<\/span>;<\/span>
\n
\nSQL_ID        CHILD_NUMBER
\n-------------<\/span> ------------<\/span>
\n81y9auy1d4b52            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>'iostats 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>'iostats last'<\/span>)<\/span>)<\/span>
\nnovo   2<\/span>:<\/span>   FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'81y9auy1d4b52'<\/span>,<\/span>         0<\/span>,<\/span>'iostats last'<\/span>)<\/span>)<\/span>
\n
\nPLAN_TABLE_OUTPUT
\n-------------------------------------------------------------------------------------------------<\/span>
\n
\nSQL_ID  81y9auy1d4b52,<\/span> child number 0<\/span>
\n-------------------------------------<\/span>
\nSELECT \/* DBTW067.1 *\/<\/span> *<\/span>   FROM dbtw067  WHERE id=<\/span>4921<\/span>
\n
\nPlan hash<\/span><\/a> value:<\/span> 979757283<\/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>
\n------------------------------------------------------------------------------------------------<\/span>
\n|<\/span>   0<\/span> |<\/span> SELECT STATEMENT  |<\/span>         |<\/span>      1<\/span> |<\/span>        |<\/span>      1<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.42<\/span> |<\/span>    5071<\/span> |<\/span>   5067<\/span> |<\/span>
\n|*<\/span>  1<\/span> |<\/span>  TABLE ACCESS FULL|<\/span> DBTW067 |<\/span>      1<\/span> |<\/span>      1<\/span> |<\/span>      1<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.42<\/span> |<\/span>    5071<\/span> |<\/span>   5067<\/span> |<\/span>
\n------------------------------------------------------------------------------------------------<\/span>
\n
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
\n---------------------------------------------------<\/span>
\n
\n   1<\/span> -<\/span> filter(<\/span>"ID"<\/span>=<\/span>4921<\/span>)<\/span>
\n
\n
\n18<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

3) Consultar o relat\u00f3rio de \u00edndices autom\u00e1ticos<\/span><\/h2>\n

O recurso AUTOMATIC INDEXING executa um processo a cada 15 minutos que realiza algumas tarefas:<\/span>
\n1) Identifica potenciais \u00edndices candidatos com base nas colunas que s\u00e3o utilizadas como filtro na cl\u00e1usula WHERE.<\/span>
\n2) Cria os \u00edndices candidatos como invis\u00edveis para serem utilizados em planos de execu\u00e7\u00e3o. O nome dos \u00edndices incluem o prefixo “SYS_AI”.<\/span>
\n3) Testa as instru\u00e7\u00f5es SQL com os \u00edndices autom\u00e1ticos invis\u00edveis para verificar se houve melhora no desempenho. Se resultado for positivo os \u00edndices s\u00e3o alterados para vis\u00edveis, caso contr\u00e1rio os \u00edndices autom\u00e1ticos s\u00e3o marcados como “UNUSUABLE” e removidos posteriormente.<\/span>
\n 
\nA consulta abaixo foi realizada logo ap\u00f3s a execu\u00e7\u00e3o da consulta da etapa anterior e antes do processo descrito acima ser executado, por esta raz\u00e3o podemos observar que o relat\u00f3rio n\u00e3o registra ainda nenhum \u00edndice candidato.<\/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
<\/div><\/td>
SET LONG 1000000<\/span> PAGESIZE 0<\/span>
\n
\nSELECT DBMS_AUTO_INDEX.<\/span>REPORT_LAST_ACTIVITY(<\/span>)<\/span> FROM dual;<\/span>
\n
\nSET pagesize 100<\/span>
\n
\n=========================================================================================================================================<\/span>
\n
\nSQL ><\/span> SET LONG 1000000<\/span> PAGESIZE 0<\/span>
\nSQL ><\/span>
\nSQL ><\/span> SELECT DBMS_AUTO_INDEX.<\/span>REPORT_LAST_ACTIVITY(<\/span>)<\/span> FROM dual;<\/span>
\nGENERAL INFORMATION
\n-------------------------------------------------------------------------------<\/span>
\n Activity start               :<\/span> 20<\/span>-<\/span>NOV-<\/span>2020<\/span> 10<\/span>:<\/span>49<\/span>:<\/span>48<\/span>
\n Activity end<\/span><\/a>                 :<\/span> 20<\/span>-<\/span>NOV-<\/span>2020<\/span> 10<\/span>:<\/span>50<\/span>:<\/span>06<\/span>
\n Executions completed         :<\/span> 1<\/span>
\n Executions interrupted       :<\/span> 0<\/span>
\n Executions with fatal error  :<\/span> 0<\/span>
\n-------------------------------------------------------------------------------<\/span>
\n
\nSUMMARY (<\/span>AUTO INDEXES)<\/span>
\n-------------------------------------------------------------------------------<\/span>
\n Index candidates            :<\/span> 0<\/span>
\n Indexes created             :<\/span> 0<\/span>
\n Space used                  :<\/span> 0<\/span> B
\n Indexes dropped             :<\/span> 0<\/span>
\n SQL statements verified     :<\/span> 0<\/span>
\n SQL statements improved     :<\/span> 0<\/span>
\n SQL plan baselines created  :<\/span> 0<\/span>
\n Overall improvement factor  :<\/span> 0x
\n-------------------------------------------------------------------------------<\/span>
\n
\nSUMMARY (<\/span>MANUAL INDEXES)<\/span>
\n-------------------------------------------------------------------------------<\/span>
\n Unused indexes    :<\/span> 0<\/span>
\n Space used        :<\/span> 0<\/span> B
\n Unusable indexes  :<\/span> 0<\/span>
\n-------------------------------------------------------------------------------<\/span>
\n
\nERRORS
\n--------------------------------------------------------------------------------<\/span>
\nNo errors found.<\/span>
\n--------------------------------------------------------------------------------<\/span>
\n
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

4) Consultar novamente o relat\u00f3rio de \u00edndices autom\u00e1ticos ap\u00f3s 15 minutos<\/span><\/h2>\n

 <\/p>\n

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
<\/div><\/td>
SQL ><\/span> SET LONG 1000000<\/span> PAGESIZE 0<\/span>
\nSQL ><\/span>
\nSQL ><\/span> SELECT DBMS_AUTO_INDEX.<\/span>REPORT_LAST_ACTIVITY(<\/span>)<\/span> FROM dual;<\/span>
\nGENERAL INFORMATION
\n-------------------------------------------------------------------------------<\/span>
\n Activity start               :<\/span> 20<\/span>-<\/span>NOV-<\/span>2020<\/span> 11<\/span>:<\/span>04<\/span>:<\/span>50<\/span>
\n Activity end<\/span><\/a>                 :<\/span> 20<\/span>-<\/span>NOV-<\/span>2020<\/span> 11<\/span>:<\/span>05<\/span>:<\/span>21<\/span>
\n Executions completed         :<\/span> 1<\/span>
\n Executions interrupted       :<\/span> 0<\/span>
\n Executions with fatal error  :<\/span> 0<\/span>
\n-------------------------------------------------------------------------------<\/span>
\n
\nSUMMARY (<\/span>AUTO INDEXES)<\/span>
\n-------------------------------------------------------------------------------<\/span>
\n Index candidates                              :<\/span> 0<\/span>
\n Indexes created (<\/span>visible \/<\/span> invisible)<\/span>         :<\/span> 1<\/span> (<\/span>1<\/span> \/<\/span> 0<\/span>)<\/span>
\n Space used (<\/span>visible \/<\/span> invisible)<\/span>              :<\/span> 17<\/span>,<\/span>83<\/span> MB (<\/span>17<\/span>,<\/span>83<\/span> MB \/<\/span> 0<\/span> B)<\/span>
\n Indexes dropped                               :<\/span> 0<\/span>
\n SQL statements verified                       :<\/span> 1<\/span>
\n SQL statements improved (<\/span>improvement factor)<\/span>  :<\/span> 1<\/span> (<\/span>5599x)<\/span>
\n SQL plan baselines created                    :<\/span> 0<\/span>
\n Overall improvement factor                    :<\/span> 5599x
\n-------------------------------------------------------------------------------<\/span>
\n
\nSUMMARY (<\/span>MANUAL INDEXES)<\/span>
\n-------------------------------------------------------------------------------<\/span>
\n Unused indexes    :<\/span> 0<\/span>
\n Space used        :<\/span> 0<\/span> B
\n Unusable indexes  :<\/span> 0<\/span>
\n-------------------------------------------------------------------------------<\/span>
\n
\nINDEX DETAILS
\n-------------------------------------------------------------------------------<\/span>
\n1<\/span>.<\/span> The following indexes were created:<\/span>
\n-------------------------------------------------------------------------------<\/span>
\n----------------------------------------------------------------------<\/span>
\n|<\/span> Owner |<\/span> Table   |<\/span> Index                |<\/span> Key<\/span><\/a> |<\/span> Type   |<\/span> Properties |<\/span>
\n----------------------------------------------------------------------<\/span>
\n|<\/span> DBTW  |<\/span> DBTW067 |<\/span> SYS_AI_d4ch93a7aqtxt |<\/span> ID  |<\/span> B-<\/span>TREE |<\/span> NONE       |<\/span>
\n----------------------------------------------------------------------<\/span>
\n-------------------------------------------------------------------------------<\/span>
\n
\nVERIFICATION DETAILS
\n-------------------------------------------------------------------------------<\/span>
\n1<\/span>.<\/span> The performance of the following statements improved:<\/span>
\n-------------------------------------------------------------------------------<\/span>
\n Parsing Schema Name  :<\/span> DBTW
\n
\n SQL ID               :<\/span> 81y9auy1d4b52
\n
\n SQL Text             :<\/span> SELECT \/* DBTW067.1 *\/<\/span> *<\/span> FROM dbtw067 WHERE id=<\/span>4921<\/span>
\n
\n Improvement Factor   :<\/span> 5599x
\n
\n
\nExecution Statistics:<\/span>
\n-----------------------------<\/span>
\n                    Original Plan                 Auto Index Plan
\n                    ----------------------------<\/span>  ----------------------------<\/span>
\n Elapsed Time<\/span><\/a> (<\/span>s)<\/span>:<\/span>  858450<\/span>                        11531<\/span>
\n CPU Time<\/span><\/a> (<\/span>s)<\/span>:<\/span>      160498<\/span>                        401<\/span>
\n Buffer Gets:<\/span>       11202<\/span>                         4<\/span>
\n Optimizer Cost:<\/span>    1405<\/span>                          4<\/span>
\n Disk Reads:<\/span>        10157<\/span>                         2<\/span>
\n Direct Writes:<\/span>     0<\/span>                             0<\/span>
\n Rows Processed:<\/span>    2<\/span>                             1<\/span>
\n Executions:<\/span>        2<\/span>                             1<\/span>
\n
\n
\nPLANS SECTION
\n--------------------------------------------------------------------------------<\/span>
\n
\n-<\/span> Original
\n-----------------------------<\/span>
\n Plan Hash<\/span><\/a> Value  :<\/span> 979757283<\/span>
\n
\n-----------------------------------------------------------------------<\/span>
\n|<\/span> Id |<\/span> Operation           |<\/span> Name    |<\/span> Rows |<\/span> Bytes |<\/span> Cost |<\/span> Time<\/span><\/a>     |<\/span>
\n-----------------------------------------------------------------------<\/span>
\n|<\/span>  0<\/span> |<\/span> SELECT STATEMENT    |<\/span>         |<\/span>      |<\/span>       |<\/span> 1405<\/span> |<\/span>          |<\/span>
\n|<\/span>  1<\/span> |<\/span>   TABLE ACCESS FULL |<\/span> DBTW067 |<\/span>    1<\/span> |<\/span>    34<\/span> |<\/span> 1405<\/span> |<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/span> |<\/span>
\n-----------------------------------------------------------------------<\/span>
\n
\n-<\/span> With Auto Indexes
\n-----------------------------<\/span>
\n Plan Hash<\/span><\/a> Value  :<\/span> 1043730720<\/span>
\n
\n-------------------------------------------------------------------------------------------------------<\/span>
\n|<\/span> Id  |<\/span> Operation                             |<\/span> Name                 |<\/span> Rows |<\/span> Bytes |<\/span> Cost |<\/span> Time<\/span><\/a>     |<\/span>
\n-------------------------------------------------------------------------------------------------------<\/span>
\n|<\/span>   0<\/span> |<\/span> SELECT STATEMENT                      |<\/span>                      |<\/span>    1<\/span> |<\/span>    34<\/span> |<\/span>    4<\/span> |<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/span> |<\/span>
\n|<\/span>   1<\/span> |<\/span>   TABLE ACCESS BY INDEX ROWID BATCHED |<\/span> DBTW067              |<\/span>    1<\/span> |<\/span>    34<\/span> |<\/span>    4<\/span> |<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/span> |<\/span>
\n|<\/span> *<\/span> 2<\/span> |<\/span>    INDEX RANGE<\/span><\/a> SCAN                   |<\/span> SYS_AI_d4ch93a7aqtxt |<\/span>    1<\/span> |<\/span>       |<\/span>    3<\/span> |<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/span> |<\/span>
\n-------------------------------------------------------------------------------------------------------<\/span>
\n
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
\n------------------------------------------<\/span>
\n*<\/span> 2<\/span> -<\/span> access(<\/span>"ID"<\/span>=<\/span>4921<\/span>)<\/span>
\n
\n
\nNotes
\n-----<\/span>
\n-<\/span> Dynamic sampling used for<\/span> this statement (<\/span> level =<\/span> 11<\/span> )<\/span>
\n
\n
\n-------------------------------------------------------------------------------<\/span>
\n
\nERRORS
\n--------------------------------------------------------------------------------<\/span>
\nNo errors found.<\/span>
\n--------------------------------------------------------------------------------<\/span>
\n
\n
\nSQL ><\/span>
\nSQL ><\/span> SET pagesize 100<\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

O novo relat\u00f3rio gerado mostra que o recurso AUTOMATIC INDEXING criou um \u00edndice e testou a execu\u00e7\u00e3o da consulta utilizando este \u00edndice, o plano de execu\u00e7\u00e3o alternativo com a utiliza\u00e7\u00e3o do \u00edndice apresenta um custo e um tempo de execu\u00e7\u00e3o (ELAPSED TIME) muito menor.<\/span><\/p>\n

 <\/p>\n

5) Executar a mesma consulta novamente<\/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
<\/div><\/td>
SQL ><\/span> SELECT \/* DBTW067.2 *\/<\/span> *<\/span>
\n  2<\/span>    FROM dbtw067
\n  3<\/span>   WHERE id=<\/span>4866<\/span>;<\/span>
\n
\n        ID    PROD_ID    CUST_ID TIME_ID  CHANNEL_ID   PROMO_ID QUANTITY_SOLD AMOUNT_SOLD
\n----------<\/span> ----------<\/span> ----------<\/span> --------<\/span> ----------<\/span> ----------<\/span> -------------<\/span> -----------<\/span>
\n      4866<\/span>         25<\/span>       7849<\/span> 16<\/span>\/<\/span>01<\/span>\/<\/span>98<\/span>          3<\/span>        999<\/span>             1<\/span>      126<\/span>,<\/span>55<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> COL sql_id       NEW_VALUE m_sql_id
\nSQL ><\/span> COL child_number NEW_VALUE m_child_no
\nSQL ><\/span> SELECT sql_id,<\/span> child_number
\n  2<\/span>    FROM v$sql<\/span>
\n  3<\/span>   WHERE sql_text LIKE '%DBTW067.2%'<\/span>
\n  4<\/span>     AND sql_text NOT LIKE '%v$sql%'<\/span>;<\/span>
\n
\nSQL_ID        CHILD_NUMBER
\n-------------<\/span> ------------<\/span>
\n5gcm57nxn3k1b            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>'iostats 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>'iostats last'<\/span>)<\/span>)<\/span>
\nnovo   2<\/span>:<\/span>   FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'5gcm57nxn3k1b'<\/span>,<\/span>         0<\/span>,<\/span>'iostats last'<\/span>)<\/span>)<\/span>
\n
\nPLAN_TABLE_OUTPUT
\n----------------------------------------------------------------------------------------------------------------------------<\/span>
\nSQL_ID  5gcm57nxn3k1b,<\/span> child number 0<\/span>
\n-------------------------------------<\/span>
\nSELECT \/* DBTW067.2 *\/<\/span> *<\/span>   FROM dbtw067  WHERE id=<\/span>4866<\/span>
\n
\nPlan hash<\/span><\/a> value:<\/span> 1043730720<\/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>      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 BATCHED|<\/span> DBTW067              |<\/span>      1<\/span> |<\/span>      1<\/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> SYS_AI_d4ch93a7aqtxt |<\/span>      1<\/span> |<\/span>      1<\/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>"ID"<\/span>=<\/span>4866<\/span>)<\/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 da consulta podemos constatar que o Otimizador utilizou o \u00edndice criado e o tempo de execu\u00e7\u00e3o caiu para 1 cent\u00e9simo de segundo, contra 42 cent\u00e9simo de segundo da primeira execu\u00e7\u00e3o onde n\u00e3o foi utilizado o \u00edndice.<\/span><\/p>\n

 <\/p>\n

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

https:\/\/docs.oracle.com\/en\/database\/oracle\/oracle-database\/19\/admin\/managing-indexes.html<\/a><\/strong><\/p>\n

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

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

O t\u00edtulo deste artigo \u00e9 uma das promessas do novo recurso AUTOMATIC INDEXING do banco de dados Oracle 19c, ele promete automatizar a cria\u00e7\u00e3o, elimina\u00e7\u00e3o e reorganiza\u00e7\u00e3o dos \u00edndices no banco de dados. Este recurso \u00e9 uma evolu\u00e7\u00e3o fant\u00e1stica em termos de desempenho para o banco de dados pois uma grande parcela dos problemas de desempenho das instru\u00e7\u00f5es SQL est\u00e3o<\/p>\n","protected":false},"author":2,"featured_media":2243,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[62],"tags":[243,245,244],"class_list":["post-2242","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-indice","tag-automatic-indexing","tag-dbms_auto_index","tag-indices-automaticos"],"aioseo_notices":[],"_links":{"self":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/2242","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=2242"}],"version-history":[{"count":11,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/2242\/revisions"}],"predecessor-version":[{"id":2258,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/2242\/revisions\/2258"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/media\/2243"}],"wp:attachment":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/media?parent=2242"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/categories?post=2242"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/tags?post=2242"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}