Quer mais performance? Faça upgrade para o 19c

Evolução do Otimizador

O Otimizador do Oracle vem sendo aprimorado a cada nova versão, a partir da 12c foram introduzidas muitas melhorias que tornaram o Otimizador muito mais eficente na escolha dos planos de execução, nas versões anteriores o Otimizador tinha algumas limitações que o impedia de escolher planos de execução melhores. Na versão 19c o Otimizador não alcançou a perfeição, mas houve um avanço gigantesco comparado com as versões 11g e anteriores.
 
 
Neste artigo vamos fazer uma demonstração prática de uma dessas melhorias, mostrando uma limitação do Otimizador existente na versão 11g e como ele superou esta limitação produzindo planos de execução com melhor desempenho na versão 18c.

 
 

Na primeira simulação prática vamos verificar a ineficiência do Otimizador na escolha do plano de execução na versão 11gR2:

1.1) Criar os objetos para simular a consulta
1.2) Executar a consulta com plano de execução ineficiente
1.3) Executar a mesma consulta com HINTs para melhorar o seu desempenho

Na segunda simulação prática vamos verificar como o Otimizador resolve esta limitação da versão 11gR2 produzindo um plano de execução com melhor desempenho na versão 18c:

2.1) Criar os objetos para simular a consulta
2.2) Executar a consulta com plano de execução eficiente

PRIMEIRA SIMULAÇÃO (11gR2)

1.1) Criar os objetos para simular a consulta

Vamos criar os objetos necessários para realizar a nossa simulação.

 

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
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
SQL > SELECT * FROM V$VERSION where rownum < 2;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SQL >
SQL > create table dbtw_grupo (key_id number, id_grupo number, nome_grupo varchar(32));

Tabela criada.

SQL >
SQL > Declare
  2
  3     num_lo01  INTEGER := 0;
  4     num_lo02  INTEGER := 0;
  5     num_ocor  INTEGER := 167;
  6     row_count INTEGER := 1;
  7
  8  begin
  9
 10     FOR c1 in (select trunc(dbms_random.value(0,1000)) num_dist from dual connect by level <= 1000)
 11     LOOP
 12        FOR num_lo02 in 0 .. num_ocor
 13        LOOP
 14          INSERT INTO dbtw_grupo values(row_count,c1.num_dist,'GRUPO_NUMERO-'||c1.num_dist);
 15          row_count := row_count + 1;
 16        END LOOP;
 17        num_ocor := num_ocor - 1;
 18        if num_ocor = 0 then
 19           num_ocor := 1;
 20        end if;
 21     END LOOP;
 22     commit;
 23
 24  end;
 25  /

Procedimento PL/SQL concluído com sucesso.

SQL >
SQL > create index dbtw_id_grupo_idx on dbtw_grupo(id_grupo);

Índice criado.

SQL >
SQL > exec dbms_stats.gather_table_stats(ownname => USER,tabname => 'DBTW_GRUPO',method_opt=>'for all columns size auto',cascade=>true,force=>true,degree=>16);

Procedimento PL/SQL concluído com sucesso.

SQL > exec dbms_stats.gather_table_stats(ownname => USER,tabname => 'DBTW_GRUPO',method_opt=>'FOR COLUMNS ID_GRUPO SIZE 254',cascade=>true,force=>true,degree=>16);

Procedimento PL/SQL concluído com sucesso.

SQL >
SQL > create table dbtw_usuario (login varchar(10), id_grupo number, status varchar(1), codigo number, note varchar(256));

Tabela criada.

SQL >
SQL > Declare
  2
  3     num_lo01  INTEGER := 0;
  4     num_ocor  INTEGER := 167;
  5     l_id_grupo INTEGER;
  6     log_id INTEGER := 1;
  7     l_key_id INTEGER;
  8
  9  begin
 10
 11     FOR num_lo01 in 1 .. 350000
 12     LOOP
 13       l_key_id := trunc(dbms_random.value(1,14999));
 14       select id_grupo into l_id_grupo from DBTW_GRUPO where key_id = l_key_id;
 15       INSERT INTO dbtw_usuario values('LOG_'||LPAD(log_id,6,0),l_id_grupo,'I',LPAD(log_id,6,0),LPAD('X',256,'X'));
 16       log_id := log_id + 1;
 17     END LOOP;
 18     commit;
 19
 20  end;
 21  /

Procedimento PL/SQL concluído com sucesso.

SQL >
SQL > create index dbtw_usuario_id_grupo_idx on dbtw_usuario(id_grupo);

Índice criado.

SQL > create index dbtw_usuario_status_idx on dbtw_usuario(status);

Índice criado.

SQL > create index dbtw_usuario_login_idx on dbtw_usuario(login);

Índice criado.

SQL >
SQL > update dbtw_usuario set status = 'N' where codigo between  60000 and 120000;

60001 linhas atualizadas.

SQL > update dbtw_usuario set status = 'F' where codigo between 150000 and 153000;

3001 linhas atualizadas.

SQL > update dbtw_usuario set status = 'A' where codigo between 210000 and 212000;

2001 linhas atualizadas.

SQL > update dbtw_usuario set status = 'R' where codigo between 280000 and 280500;

501 linhas atualizadas.

SQL >
SQL > commit;

Commit concluído.

SQL >
SQL >
SQL > exec dbms_stats.gather_table_stats(ownname => USER,tabname => 'DBTW_USUARIO',method_opt=>'for all columns size auto',cascade=>true,force=>true,degree=>16);

Procedimento PL/SQL concluído com sucesso.

SQL > exec dbms_stats.gather_table_stats(ownname => USER,tabname => 'DBTW_USUARIO',method_opt=>'FOR COLUMNS ID_GRUPO SIZE 254',cascade=>true,force=>true,degree=>16);

Procedimento PL/SQL concluído com sucesso.

SQL > exec dbms_stats.gather_table_stats(ownname => USER,tabname => 'DBTW_USUARIO',method_opt=>'FOR COLUMNS STATUS SIZE 254',cascade=>true,force=>true,degree=>16);

Procedimento PL/SQL concluído com sucesso.

SQL >

 

1.2) Executar a consulta com plano de execução ineficiente

 

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
SQL > ALTER SESSION SET statistics_level=ALL;

Sessão alterada.

SQL >
SQL > variable LOGIN   VARCHAR2(6);
SQL > variable SYS_B_3 VARCHAR2(32);
SQL > variable SYS_B_4 VARCHAR2(32);
SQL >
SQL > exec  :SYS_B_3 := 'I';

Procedimento PL/SQL concluído com sucesso.

SQL > exec  :SYS_B_4 := 'R';

Procedimento PL/SQL concluído com sucesso.

SQL > exec  :LOGIN := '076955';

Procedimento PL/SQL concluído com sucesso.

SQL >
SQL > select /* dbtw-062.1 */ u.codigo, u.login
  2   FROM dbtw_usuario u
  3  INNER JOIN dbtw_grupo g
  4     ON u.id_grupo = g.id_grupo
  5  WHERE u.status NOT IN (:"SYS_B_3", :"SYS_B_4")
  6    AND u.login like '%'||:LOGIN;

    CODIGO LOGIN
---------- ----------
     76955 LOG_076955
     76955 LOG_076955
     76955 LOG_076955
      ..   ..
     76955 LOG_076955

157 linhas selecionadas.

SQL >
SQL >
SQL > column sql_id new_value m_sql_id
SQL >
SQL > column child_number new_value m_child_no
SQL >
SQL > SELECT sql_id, child_number FROM v$sql WHERE sql_text LIKE '%dbtw-062.1%' AND sql_text NOT LIKE '%v$sql%';

SQL_ID        CHILD_NUMBER
------------- ------------
drc438b9c12ty            0

SQL >
SQL > SELECT * FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'BASIC ALLSTATS LAST'));
antigo   1: SELECT * FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'BASIC ALLSTATS LAST'))
novo   1: SELECT * FROM TABLE (dbms_xplan.display_cursor ('drc438b9c12ty',         0,'BASIC ALLSTATS LAST'))

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select /* dbtw-062.1 */ u.codigo, u.login  FROM dbtw_usuario u INNER
JOIN dbtw_grupo g    ON u.id_grupo = g.id_grupo WHERE u.status NOT IN
(:"SYS_B_3", :"SYS_B_4")   AND u.login like :"B0"||:LOGIN

Plan hash value: 2044621122

-----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                   |      1 |        |    157 |00:00:02.70 |   14230 |  14211 |       |       |          |
|*  1 |  HASH JOIN            |                   |      1 |    345K|    157 |00:00:02.70 |   14230 |  14211 |  1321K|  1321K|  721K (0)|
|*  2 |   TABLE ACCESS FULL   | DBTW_USUARIO      |      1 |   3242 |      1 |00:00:02.66 |   14181 |  14177 |       |       |          |
|   3 |   INDEX FAST FULL SCAN| DBTW_ID_GRUPO_IDX |      1 |  15861 |  15861 |00:00:00.04 |      49 |     34 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("U"."ID_GRUPO"="G"."ID_GRUPO")
   2 - filter(("U"."STATUS"<>:SYS_B_3 AND "U"."LOGIN" LIKE :B0||:LOGIN AND "U"."STATUS"<>:SYS_B_4))


23 linhas selecionadas.

SQL >

 

Observe no plano de execução acima que o Otimizador utilizou a operação HASH JOIN para juntar as duas tabelas quando a melhor opção seria uma operação NESTED LOOPS, uma vez que foi selecionada somente 1 linha na tabela DBTW_USUARIO (A-Rows = 1). O Otimizador escolheu a operação HASH JOIN pois a estimativa de linhas que seriam lidas da tabela DBTW_USUARIO seriam 3242 linhas (E-Rows = 3242), este erro de estimativa foi causado pela complexidade dos filtros da cláusula WHERE.

1.3) Executar a mesma consulta com HINTs para melhorar o seu desempenho

Com o conhecimento mais aprofundado dos dados da tabela DBTW_USUARIO sabemos que apesar da complexidade do filtro da cláusula WHERE, o numero de linhas selecionadas será sempre próximo de 1, logo podemos dizer para o Otimizador, através de HINTs, que estime 1 linha para cardinalidade da operação de leitura da tabela DBTW_USUARIO e utilize o indice DBTW_USUARIO_LOGIN_IDX.

 

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
SQL > ALTER SESSION SET statistics_level=ALL;

Sessão alterada.

SQL >
SQL > variable LOGIN   VARCHAR2(6);
SQL > variable SYS_B_3 VARCHAR2(32);
SQL > variable SYS_B_4 VARCHAR2(32);
SQL >
SQL > exec  :SYS_B_3 := 'I';

Procedimento PL/SQL concluído com sucesso.

SQL > exec  :SYS_B_4 := 'R';

Procedimento PL/SQL concluído com sucesso.

SQL > exec  :LOGIN := '076955';

Procedimento PL/SQL concluído com sucesso.

SQL >
SQL > select /*+  OPT_ESTIMATE(TABLE U ROWS=1) INDEX(U DBTW_USUARIO_LOGIN_IDX) */ /* dbtw-062.1 */ u.codigo, u.login
  2   FROM dbtw_usuario u
  3  INNER JOIN dbtw_grupo g
  4     ON u.id_grupo = g.id_grupo
  5  WHERE u.status NOT IN (:"SYS_B_3", :"SYS_B_4")
  6    AND u.login like '%'||:LOGIN;

    CODIGO LOGIN
---------- ----------
     76955 LOG_076955
     76955 LOG_076955
     76955 LOG_076955
     ..    ..
     76955 LOG_076955

157 linhas selecionadas.

SQL >
SQL >
SQL > column sql_id new_value m_sql_id
SQL >
SQL > column child_number new_value m_child_no
SQL >
SQL > SELECT sql_id, child_number FROM v$sql WHERE sql_text LIKE '%dbtw-062.1%' AND sql_text NOT LIKE '%v$sql%';

SQL_ID        CHILD_NUMBER
------------- ------------
a2dkyxr0qdc08            0

SQL >
SQL > SELECT * FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'BASIC ALLSTATS LAST'));
antigo   1: SELECT * FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'BASIC ALLSTATS LAST'))
novo   1: SELECT * FROM TABLE (dbms_xplan.display_cursor ('a2dkyxr0qdc08',         0,'BASIC ALLSTATS LAST'))

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select /*+  OPT_ESTIMATE(TABLE U ROWS=1) INDEX(U
DBTW_USUARIO_LOGIN_IDX) */
/* dbtw-062.1 */ u.codigo, u.login  FROM
dbtw_usuario u INNER JOIN dbtw_grupo g    ON u.id_grupo = g.id_grupo
WHERE u.status NOT IN (:"SYS_B_3", :"SYS_B_4")   AND u.login like
:"B0"||:LOGIN

Plan hash value: 3055727543

--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                   | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
--------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                        |      1 |        |    157 |00:00:00.62 |    1091 |     19 |
|   1 |  NESTED LOOPS                |                        |      1 |    107 |    157 |00:00:00.62 |    1091 |     19 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| DBTW_USUARIO           |      1 |      1 |      1 |00:00:00.62 |    1078 |     19 |
|*  3 |    INDEX RANGE SCAN          | DBTW_USUARIO_LOGIN_IDX |      1 |  17500 |      1 |00:00:00.61 |    1077 |     18 |
|*  4 |   INDEX RANGE SCAN           | DBTW_ID_GRUPO_IDX      |      1 |    107 |    157 |00:00:00.01 |      13 |      0 |
--------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(("U"."STATUS"<>:SYS_B_3 AND "U"."STATUS"<>:SYS_B_4))
   3 - access("U"."LOGIN" LIKE :B0||:LOGIN)
       filter("U"."LOGIN" LIKE :B0||:LOGIN)
   4 - access("U"."ID_GRUPO"="G"."ID_GRUPO")


28 linhas selecionadas.

SQL >

 

Após a execução da consulta podemos verificar no plano de execução acima que o desempenho melhorou significativamente, enquanto na primeira execução foram acessados 14.230 Buffers, nesta execução a quantidade Buffers acessados caiu para 1.091.

SEGUNDA SIMULAÇÃO (18C)

Agora vamos utilizar esta mesma consulta numa base 18c e verificar como o Otimizador lida com esta questão de predicados ou filtros da cláusula WHERE complexos.

2.1) Criar os objetos para simular a consulta

 

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
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
SQL > SELECT BANNER_FULL FROM V$VERSION where rownum < 2;

BANNER_FULL
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0


SQL > create table dbtw_grupo (key_id number, id_grupo number, nome_grupo varchar(32));

Tabela criada.

SQL >
SQL > Declare
  2
  3     num_lo01  INTEGER := 0;
  4     num_lo02  INTEGER := 0;
  5     num_ocor  INTEGER := 167;
  6     row_count INTEGER := 1;
  7
  8  begin
  9
 10     FOR c1 in (select trunc(dbms_random.value(0,1000)) num_dist from dual connect by level <= 1000)
 11     LOOP
 12        FOR num_lo02 in 0 .. num_ocor
 13        LOOP
 14          INSERT INTO dbtw_grupo values(row_count,c1.num_dist,'GRUPO_NUMERO-'||c1.num_dist);
 15          row_count := row_count + 1;
 16        END LOOP;
 17        num_ocor := num_ocor - 1;
 18        if num_ocor = 0 then
 19           num_ocor := 1;
 20        end if;
 21     END LOOP;
 22     commit;
 23
 24  end;
 25  /

Procedimento PL/SQL concluído com sucesso.

SQL >
SQL > create index dbtw_id_grupo_idx on dbtw_grupo(id_grupo);

Índice criado.

SQL >
SQL > exec dbms_stats.gather_table_stats(ownname => USER,tabname => 'DBTW_GRUPO',method_opt=>'for all columns size auto',cascade=>true,force=>true,degree=>16);

Procedimento PL/SQL concluído com sucesso.

SQL > exec dbms_stats.gather_table_stats(ownname => USER,tabname => 'DBTW_GRUPO',method_opt=>'FOR COLUMNS ID_GRUPO SIZE 254',cascade=>true,force=>true,degree=>16);

Procedimento PL/SQL concluído com sucesso.

SQL >
SQL > create table dbtw_usuario (login varchar(10), id_grupo number, status varchar(1), codigo number, note varchar(256));

Tabela criada.

SQL >
SQL > Declare
  2
  3     num_lo01  INTEGER := 0;
  4     num_ocor  INTEGER := 167;
  5     l_id_grupo INTEGER;
  6     log_id INTEGER := 1;
  7     l_key_id INTEGER;
  8
  9  begin
 10
 11     FOR num_lo01 in 1 .. 350000
 12     LOOP
 13       l_key_id := trunc(dbms_random.value(1,14999));
 14       select id_grupo into l_id_grupo from DBTW_GRUPO where key_id = l_key_id;
 15       INSERT INTO dbtw_usuario values('LOG_'||LPAD(log_id,6,0),l_id_grupo,'I',LPAD(log_id,6,0),LPAD('X',256,'X'));
 16       log_id := log_id + 1;
 17     END LOOP;
 18     commit;
 19
 20  end;
 21  /

Procedimento PL/SQL concluído com sucesso.

SQL >
SQL > create index dbtw_usuario_id_grupo_idx on dbtw_usuario(id_grupo);

Índice criado.

SQL > create index dbtw_usuario_status_idx on dbtw_usuario(status);

Índice criado.

SQL > create index dbtw_usuario_login_idx on dbtw_usuario(login);

Índice criado.

SQL >
SQL > update dbtw_usuario set status = 'N' where codigo between  60000 and 120000;

60001 linhas atualizadas.

SQL > update dbtw_usuario set status = 'F' where codigo between 150000 and 153000;

3001 linhas atualizadas.

SQL > update dbtw_usuario set status = 'A' where codigo between 210000 and 212000;

2001 linhas atualizadas.

SQL > update dbtw_usuario set status = 'R' where codigo between 280000 and 280500;

501 linhas atualizadas.

SQL >
SQL > commit;

Commit concluído.

SQL >
SQL >
SQL > exec dbms_stats.gather_table_stats(ownname => USER,tabname => 'DBTW_USUARIO',method_opt=>'for all columns size auto',cascade=>true,force=>true,degree=>16);

Procedimento PL/SQL concluído com sucesso.

SQL > exec dbms_stats.gather_table_stats(ownname => USER,tabname => 'DBTW_USUARIO',method_opt=>'FOR COLUMNS ID_GRUPO SIZE 254',cascade=>true,force=>true,degree=>16);

Procedimento PL/SQL concluído com sucesso.

SQL > exec dbms_stats.gather_table_stats(ownname => USER,tabname => 'DBTW_USUARIO',method_opt=>'FOR COLUMNS STATUS SIZE 254',cascade=>true,force=>true,degree=>16);

Procedimento PL/SQL concluído com sucesso.

SQL >

 

2.2) Executar a consulta com plano de execução eficiente

 

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
SQL > ALTER SESSION SET statistics_level=ALL;

Sessão alterada.

SQL >
SQL > variable LOGIN   VARCHAR2(6);
SQL > variable SYS_B_3 VARCHAR2(32);
SQL > variable SYS_B_4 VARCHAR2(32);
SQL >
SQL > exec  :SYS_B_3 := 'I';

Procedimento PL/SQL concluído com sucesso.

SQL > exec  :SYS_B_4 := 'R';

Procedimento PL/SQL concluído com sucesso.

SQL > exec  :LOGIN := '076955';

Procedimento PL/SQL concluído com sucesso.

SQL >
SQL > select /* dbtw-062.3 */ u.codigo, u.login
  2   FROM dbtw_usuario u
  3  INNER JOIN dbtw_grupo g
  4     ON u.id_grupo = g.id_grupo
  5  WHERE u.status NOT IN (:"SYS_B_3", :"SYS_B_4")
  6    AND u.login like '%'||:LOGIN;

    CODIGO LOGIN
---------- ----------
     76955 LOG_076955
     76955 LOG_076955
     ..    ..
     76955 LOG_076955

72 linhas selecionadas.

SQL >
SQL >
SQL > column sql_id new_value m_sql_id
SQL >
SQL > column child_number new_value m_child_no
SQL >
SQL > SELECT sql_id, child_number FROM v$sql WHERE sql_text LIKE '%dbtw-062.3%' AND sql_text NOT LIKE '%v$sql%';

SQL_ID        CHILD_NUMBER
------------- ------------
6v7qnmxnyf0ru            0

SQL >
SQL > SELECT * FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'ADAPTIVE ALLSTATS LAST'));
antigo   1: SELECT * FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'ADAPTIVE ALLSTATS LAST'))
novo   1: SELECT * FROM TABLE (dbms_xplan.display_cursor ('6v7qnmxnyf0ru',         0,'ADAPTIVE ALLSTATS LAST'))

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  6v7qnmxnyf0ru, child number 0
-------------------------------------
select /* dbtw-062.3 */ u.codigo, u.login  FROM dbtw_usuario u INNER
JOIN dbtw_grupo g    ON u.id_grupo = g.id_grupo WHERE u.status NOT IN
(:"SYS_B_3", :"SYS_B_4")   AND u.login like '%'||:LOGIN

Plan hash value: 1119022536

-----------------------------------------------------------------------------------------------------------------------------
|   Id  | Operation                              | Name                   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------------------
|     0 | SELECT STATEMENT                       |                        |      1 |        |     72 |00:00:00.06 |    1086 |
|- *  1 |  HASH JOIN                             |                        |      1 |    369K|     72 |00:00:00.06 |    1086 |
|     2 |   NESTED LOOPS                         |                        |      1 |    369K|     72 |00:00:00.06 |    1086 |
|-    3 |    STATISTICS COLLECTOR                |                        |      1 |        |      1 |00:00:00.06 |    1078 |
|  *  4 |     TABLE ACCESS BY INDEX ROWID BATCHED| DBTW_USUARIO           |      1 |   3271 |      1 |00:00:00.06 |    1078 |
|  *  5 |      INDEX RANGE SCAN                  | DBTW_USUARIO_LOGIN_IDX |      1 |  17500 |      1 |00:00:00.06 |    1077 |
|  *  6 |    INDEX RANGE SCAN                    | DBTW_ID_GRUPO_IDX      |      1 |    113 |     72 |00:00:00.01 |       8 |
|-    7 |   INDEX FAST FULL SCAN                 | DBTW_ID_GRUPO_IDX      |      0 |  15861 |      0 |00:00:00.01 |       0 |
-----------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("U"."ID_GRUPO"="G"."ID_GRUPO")
   4 - filter(("U"."STATUS"<>:SYS_B_3 AND "U"."STATUS"<>:SYS_B_4))
   5 - access("U"."LOGIN" LIKE '%'||:LOGIN)
       filter("U"."LOGIN" LIKE '%'||:LOGIN)
   6 - access("U"."ID_GRUPO"="G"."ID_GRUPO")

Note
-----
   - this is an adaptive plan (rows marked '-' are inactive)


34 linhas selecionadas.

SQL >

 

Observe no plano de execução acima, a seção NOTE apresenta a mensagem “this is an adaptive plan”, isto significa que o Otimizador utilizou um novo recurso chamado ADAPTIVE QUERY OPTIMIZATION que permite ao Otimizador alterar o plano de execução durante a execução da instrução SQL. Se você analisar o plano de execução com mais cuidado vai verificar que existem algumas operações que foram assinaladas com o caracter “-” na coluna ID, isto significa que estas operações foram desativadas ou desconsideradas. Como resultado podemos observar que o desempenho desta consulta foi igual ao desempenho da consulta executada na base 11g com os HINTs para melhoria de performance.

CONCLUSÃO

Esta é uma pequena amostra de como o Otimizador do Oracle Database evoluiu nas ultimas versões, se você ainda esta em dúvida se vale a pena fazer o Upgrade para a versão 19c, saiba que alem da manutenção do suporte e das melhorias de segurança, você esta perdendo a oportunidade de melhorar a performance de suas aplicações.

Referências

https://docs.oracle.com/database/121/

 
 
 
Promo-D75L

video-02
 
 
 
 
 
 
 
 
 
 
 
 

4 comments

Deixe uma resposta

O seu endereço de email não será publicado. Campos obrigatórios são marcados com *