Evite surpresas desagradáveis no desempenho das consultas

surpresa

Você já ouviu alguém dizer: Minha aplicação ficou lenta e nada foi alterado. Este problema relativamente comum pode ser evitado se você implementar no banco Oracle o recurso SQL PLAN MANAGEMENT (SPM), este recurso esta disponível a partir versão 11G e não requer a licença das Options Diagnostic & Tuning Pack. Basicamente o SPM é um mecanismo preventivo que permite ao Otimizador gerenciar automaticamente os planos de execução, garantindo que o banco de dados use apenas planos que tenham desempenho igual ou melhor aos planos de execução utilizados anteriormente, este mecanismo impede que uma consulta sofra uma regressão no seu desempenho se não houver alterações nas estruturas dos objetos e parâmetros do banco de dados.

 
 

A seguir apresentamos uma pequena introdução de como funciona a SQL PLAN BASELINE que é o principal mecanismo do SPM e na sequência vamos fazer uma simulação prática de como funciona o SPM.

 

Quando uma instrução SQL é submetida ao processo de HARD PARSE, o Otimizador produz vários planos de execução e seleciona aquele com o menor custo. Se esta instrução SQL estiver presente na SQL PLAN BASELINE e o plano gerado no HARD PARSE for igual a um dos planos existente na BASELINE, o Otimizador vai verificar se este plano foi aceito, se SIM o plano será utilizado na execução, se no entanto o plano não foi aceito, o Otimizador vai verificar a existência de outro plano aceito com custo menor e utilizar este plano da BASELINE na execução da instrução SQL, se não existir outro plano aceito na BASELINE o plano gerado originalmente no HARD PARSE será utilizado.

O fluxograma abaixo representa a explicação acima

Baseline 1

Outro fluxo alternativo ao apresentado anteriormente seria após o HARD PARSE, se esta instrução SQL estiver presente na SQL PLAN BASELINE e o plano gerado no HARD PARSE NÃO for igual a um dos planos existente na BASELINE, neste caso o Otimizador vai comparar o custo desse novo plano com o custo dos planos aceitos, se o custo do novo plano for menor serão geradas BASELINE HINTS para ele, se no entanto estas BASELINES HINTS não conseguirem reproduzir o plano gerado no HARD PARSE, o Otimizador vai verificar a existência de outro plano aceito com o menor custo e utilizar este plano da BASELINE na execução da instrução SQL, se as BASELINES HINTS conseguirem reproduzir o plano gerado no HARD PARSE, o novo plano será adicionado a BASELINE como não aceito e um plano da BASELINE com menor custo será utilizado na execução da instrução SQL.

Baseline 2

Para ajudar no entendimento de como funcionam as BASELINES vamos realizar uma simulação do processo de inclusão e evolução dos planos de execução de uma instrução SQL sob o controle do recurso SQL PLAN MANAGEMENT:

01) Habilitar o uso do SQL PLAN MANAGEMENT
02) Executar uma consulta com SPM habilitado
03) Verificar o plano de execução na SQL PLAN BASELINE
04) Executar a consulta novamente usando a BASELINE
05) Criar um índice para melhorar o desempenho da consulta
06) Verificar se a consulta vai usar o índice
07) Verificar existência de mais um plano na BASELINE
08) Verificar qual plano será utilizado numa nova execução
09) Executar o procedimento de evolução do SPM
10) Executar a consulta novamente com o novo plano
11) Eliminar a BASELINE criada

 

01) Habilitar o uso do SQL PLAN MANAGEMENT

Para utilizar o SQL PLAN MANAGEMENT precisamos habilitar o parâmetro OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES.

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
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


1 linha selecionada.

SQL >
SQL > SHOW PARAMETER OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean     FALSE
SQL >
SQL > ALTER SYSTEM SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=TRUE;

Sistema alterado.

SQL >

 

02) Executar uma consulta com SPM habilitado

 

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

Sessão alterada.

SQL > ALTER SESSION SET current_schema=SH;

Sessão alterada.

SQL >
SQL > SELECT /* dbtw-066 */
  2         c.cust_id, c.cust_first_name, c.cust_last_name
  3    FROM sh.customers c
  4   WHERE c.cust_id >=100
  5     AND c.cust_id <=400
  6     AND c.cust_last_name like 'Her%';

   CUST_ID CUST_FIRST_NAME      CUST_LAST_NAME
---------- -------------------- ----------------------------------------
       167 Eve                  Herd
       161 Linda                Hermann

2 linhas selecionadas.

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

SQL_ID           CHILD_NUMBER
---------------- ------------
1xj5b63mpbz67               0

1 linha selecionada.

SQL >
SQL > SELECT *
  2    FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'typical iostats last'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------
SQL_ID  1xj5b63mpbz67, child number 0
-------------------------------------
SELECT /* dbtw-066 */        c.cust_id, c.cust_first_name,
c.cust_last_name   FROM sh.customers c  WHERE c.cust_id >=100    AND
c.cust_id <=400    AND c.cust_last_name like 'Her%'

Plan hash value: 2008213504

-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation         | Name      | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |      1 |        |       |    95 (100)|          |      2 |00:00:00.01 |    1520 |
|*  1 |  TABLE ACCESS FULL| CUSTOMERS |      1 |      1 |    20 |    95  (24)| 00:00:01 |      2 |00:00:00.01 |    1520 |
-------------------------------------------------------------------------------------------------------------------------

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

   1 - filter(("C"."CUST_ID"<=400 AND "C"."CUST_LAST_NAME" LIKE 'Her%' AND "C"."CUST_ID">=100))


20 linhas selecionadas.

SQL >

 

Observe no plano de execução que o Otimizador utilizou a operação TABLE ACCESS FULL para acessar a tabela CUSTOMERS do esquema SH.

03) Verificar o plano de execução na SQL PLAN BASELINE

 

Para obter informações sobre as consultas que estão sendo gerenciadas pelo SPM basta consultar a visão DBA_SQL_PLAN_BASELINES.

 

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
SQL > SELECT sql_handle, plan_name, enabled, accepted
  2    FROM dba_sql_plan_baselines
  3   WHERE sql_text LIKE '%dbtw-066%'
  4     AND sql_text NOT LIKE '%sql_text%';

SQL_HANDLE                     PLAN_NAME                        ENA ACC
------------------------------ -------------------------------- --- ---
SQL_15042ba17146e187           SQL_PLAN_1a11bn5sndsc764541f84   YES YES

1 linha selecionada.

SQL >

==============================================================================================================

SET LONG 10000

SELECT *
FROM   TABLE(DBMS_XPLAN.display_sql_plan_baseline(plan_name=>'SQL_PLAN_1a11bn5sndsc764541f84'));

==============================================================================================================

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
SQL handle: SQL_15042ba17146e187
SQL text: SELECT /* dbtw-066 */        c.cust_id, c.cust_first_name,
          c.cust_last_name   FROM sh.customers c  WHERE c.cust_id >=100    AND
          c.cust_id <=400    AND c.cust_last_name like 'Her%'
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
Plan name: SQL_PLAN_1a11bn5sndsc764541f84         Plan id: 1683234692
Enabled: YES     Fixed: NO      Accepted: YES     Origin: AUTO-CAPTURE
Plan rows: From dictionary
--------------------------------------------------------------------------------

Plan hash value: 2008213504

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |     1 |    20 |    95  (24)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| CUSTOMERS |     1 |    20 |    95  (24)| 00:00:01 |
-------------------------------------------------------------------------------

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

   1 - filter("C"."CUST_ID"<=400 AND "C"."CUST_LAST_NAME" LIKE 'Her%'
              AND "C"."CUST_ID">=100)

28 linhas selecionadas.

SQL >

 

No resultado da consulta podemos verificar que na BASELINE temos uma instrução SQL sendo gerenciada (SQL_15042ba17146e187) e esta instrução tem somente um plano de execução (SQL_PLAN_1a11bn5sndsc764541f84). Este plano esta habilitado e aceito para ser utilizado na execução desta instrução SQL.
Utilizando o procedure DISPLAY_SQL_PLAN_BASELINE do pacote DBMS_XPLAN podemos conferir que o plano armazenado na BASELINE é o mesmo plano que foi utilizado na execução da consulta.

 

04) Executar a consulta novamente usando a BASELINE

Vamos executar a mesma consulta novamente e verificar se a BASELINE será utilizada.
 

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
SQL > SELECT /* dbtw-066 */
  2         c.cust_id, c.cust_first_name, c.cust_last_name
  3    FROM sh.customers c
  4   WHERE c.cust_id >=100
  5     AND c.cust_id <=400
  6     AND c.cust_last_name like 'Her%';

   CUST_ID CUST_FIRST_NAME      CUST_LAST_NAME
---------- -------------------- ----------------------------------------
       167 Eve                  Herd
       161 Linda                Hermann

2 linhas selecionadas.

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

SQL_ID           CHILD_NUMBER
---------------- ------------
1xj5b63mpbz67               0
1xj5b63mpbz67               1

2 linhas selecionadas.

SQL >
SQL > SELECT *
  2    FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'typical iostats last'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  1xj5b63mpbz67, child number 1
-------------------------------------
SELECT /* dbtw-066 */        c.cust_id, c.cust_first_name,
c.cust_last_name   FROM sh.customers c  WHERE c.cust_id >=100    AND
c.cust_id <=400    AND c.cust_last_name like 'Her%'

Plan hash value: 2008213504

-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation         | Name      | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |      1 |        |       |    95 (100)|          |      2 |00:00:00.01 |    1520 |
|*  1 |  TABLE ACCESS FULL| CUSTOMERS |      1 |      1 |    20 |    95  (24)| 00:00:01 |      2 |00:00:00.01 |    1520 |
-------------------------------------------------------------------------------------------------------------------------

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

   1 - filter(("C"."CUST_ID"<=400 AND "C"."CUST_LAST_NAME" LIKE 'Her%' AND "C"."CUST_ID">=100))

Note
-----
   - SQL plan baseline SQL_PLAN_1a11bn5sndsc764541f84 used for this statement


24 linhas selecionadas.

SQL >

 

Observe na seção NOTE do plano de execução que aparece a mensagem: “SQL plan baseline SQL_PLAN_1a11bn5sndsc764541f84 used for this statement”. Esta mensagem confirma que o plano de execução utilizado foi o que esta na BASELINE.

 

05) Criar um índice para melhorar o desempenho da consulta

Como a consulta que estamos executando possui o campo CUST_LAST_NAME no filtro da cláusula WHERE e não existe um índice desta coluna para a tabela CUSTOMERS, vamos criar um índice neste campo para melhorar o desempenho desta consulta.

 

1
2
3
4
5
SQL > create index sh.CUST_NAME_IDX on sh.CUSTOMERS(CUST_LAST_NAME);

Índice criado.

SQL >

 

06) Verificar se a consulta vai usar o índice

Após a criação do índice vamos executar a consulta novamente para verificar se o índice será utilizado no plano de execuçã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
SQL > SELECT /* dbtw-066 */
  2         c.cust_id, c.cust_first_name, c.cust_last_name
  3    FROM sh.customers c
  4   WHERE c.cust_id >=100
  5     AND c.cust_id <=400
  6     AND c.cust_last_name like 'Her%';

   CUST_ID CUST_FIRST_NAME      CUST_LAST_NAME
---------- -------------------- ----------------------------------------
       167 Eve                  Herd
       161 Linda                Hermann

2 linhas selecionadas.

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

SQL_ID           CHILD_NUMBER
---------------- ------------
1xj5b63mpbz67               0

1 linha selecionada.

SQL >
SQL > SELECT *
  2    FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'typical iostats last'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  1xj5b63mpbz67, child number 0
-------------------------------------
SELECT /* dbtw-066 */        c.cust_id, c.cust_first_name,
c.cust_last_name   FROM sh.customers c  WHERE c.cust_id >=100    AND
c.cust_id <=400    AND c.cust_last_name like 'Her%'

Plan hash value: 2008213504

-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation         | Name      | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |      1 |        |       |    95 (100)|          |      2 |00:00:00.01 |    1520 |
|*  1 |  TABLE ACCESS FULL| CUSTOMERS |      1 |      1 |    20 |    95  (24)| 00:00:01 |      2 |00:00:00.01 |    1520 |
-------------------------------------------------------------------------------------------------------------------------

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

   1 - filter(("C"."CUST_ID"<=400 AND "C"."CUST_LAST_NAME" LIKE 'Her%' AND "C"."CUST_ID">=100))


20 linhas selecionadas.

SQL >

 

Observando o plano de execução acima, constatamos que o índice não foi utilizado, o Otimizador continua utilizando o mesmo plano utilizado nas execuções anteriores. O novo plano com a utilização do índice não foi utilizado pois o SPM está atuando junto ao Otimizador, ele identificou que existe um novo plano com um CUSTO menor do que aquele que está ACEITO na BASELINE, porem ele não vai utilizar este novo plano até que o seu desempenho seja validado.

 

07) Verificar existência de mais um plano na BASELINE

Vamos consultar a visão DBA_SQL_PLAN_BASELINES novamente e verificar se realmente o SPM já registrou a existência de um novo plano com a utilização do índice que criamos anteriormente.

 

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
SQL > SELECT sql_handle, plan_name, enabled, accepted
  2    FROM dba_sql_plan_baselines
  3   WHERE sql_text LIKE '%dbtw-066%'
  4     AND sql_text NOT LIKE '%sql_text%';

SQL_HANDLE                     PLAN_NAME                        ENA ACC
------------------------------ -------------------------------- --- ---
SQL_15042ba17146e187           SQL_PLAN_1a11bn5sndsc71a248297   YES NO
SQL_15042ba17146e187           SQL_PLAN_1a11bn5sndsc764541f84   YES YES

2 linhas selecionadas.

SQL >

==============================================================================================================

SET LONG 10000

SELECT *
FROM   TABLE(DBMS_XPLAN.display_sql_plan_baseline(plan_name=>'SQL_PLAN_1a11bn5sndsc71a248297'));

==============================================================================================================

SQL > SET LONG 10000
SQL >
SQL > SELECT *
  2  FROM   TABLE(DBMS_XPLAN.display_sql_plan_baseline(plan_name=>'SQL_PLAN_1a11bn5sndsc71a248297'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL handle: SQL_15042ba17146e187
SQL text: SELECT /* dbtw-066 */        c.cust_id, c.cust_first_name,
          c.cust_last_name   FROM sh.customers c  WHERE c.cust_id >=100    AND
          c.cust_id <=400    AND c.cust_last_name like 'Her%'
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
Plan name: SQL_PLAN_1a11bn5sndsc71a248297         Plan id: 438600343
Enabled: YES     Fixed: NO      Accepted: NO      Origin: AUTO-CAPTURE
Plan rows: From dictionary
--------------------------------------------------------------------------------

Plan hash value: 203008431

-----------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |               |     1 |    20 |     6  (34)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| CUSTOMERS     |     1 |    20 |     6  (34)| 00:00:01 |
|   2 |   BITMAP CONVERSION TO ROWIDS       |               |       |       |            |          |
|   3 |    BITMAP AND                       |               |       |       |            |          |
|   4 |     BITMAP CONVERSION FROM ROWIDS   |               |       |       |            |          |
|   5 |      SORT ORDER BY                  |               |       |       |            |          |
|*  6 |       INDEX RANGE SCAN              | CUST_NAME_IDX |    61 |       |     2   (0)| 00:00:01 |
|   7 |     BITMAP CONVERSION FROM ROWIDS   |               |       |       |            |          |
|   8 |      SORT ORDER BY                  |               |       |       |            |          |
|*  9 |       INDEX RANGE SCAN              | CUSTOMERS_PK  |    61 |       |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

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

   6 - access("C"."CUST_LAST_NAME" LIKE 'Her%')
       filter("C"."CUST_LAST_NAME" LIKE 'Her%' AND "C"."CUST_LAST_NAME" LIKE 'Her%')
   9 - access("C"."CUST_ID">=100 AND "C"."CUST_ID"<=400)

37 linhas selecionadas.

SQL >

 

Verificando o relatório acima podemos constatar que um novo plano (SQL_PLAN_1a11bn5sndsc71a248297) utilizando o índice foi inserido para a consulta (SQL_15042ba17146e187) e está com o campo ACCEPTED igual NO, por esta razão ele não será utilizado até que o seu desempenho seja validado e esse campo passe a ser igual a YES.

 

08) Verificar qual plano será utilizado numa nova execução

Vamos executar a consulta novamente para confirmar que o novo plano de execução utilizando o índice não será utilizado.

 

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
SQL > ALTER SYSTEM FLUSH SHARED_POOL;

Sistema alterado.

SQL >
SQL > SELECT /* dbtw-066 */
  2         c.cust_id, c.cust_first_name, c.cust_last_name
  3    FROM sh.customers c
  4   WHERE c.cust_id >=100
  5     AND c.cust_id <=400
  6     AND c.cust_last_name like 'Her%';

   CUST_ID CUST_FIRST_NAME      CUST_LAST_NAME
---------- -------------------- ----------------------------------------
       167 Eve                  Herd
       161 Linda                Hermann

2 linhas selecionadas.

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

SQL_ID           CHILD_NUMBER
---------------- ------------
1xj5b63mpbz67               1

1 linha selecionada.

SQL >
SQL > SELECT *
  2    FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'typical iostats last'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  1xj5b63mpbz67, child number 1
-------------------------------------
SELECT /* dbtw-066 */        c.cust_id, c.cust_first_name,
c.cust_last_name   FROM sh.customers c  WHERE c.cust_id >=100    AND
c.cust_id <=400    AND c.cust_last_name like 'Her%'

Plan hash value: 2008213504

-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation         | Name      | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |      1 |        |       |    95 (100)|          |      2 |00:00:00.01 |    1520 |
|*  1 |  TABLE ACCESS FULL| CUSTOMERS |      1 |      1 |    20 |    95  (24)| 00:00:01 |      2 |00:00:00.01 |    1520 |
-------------------------------------------------------------------------------------------------------------------------

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

   1 - filter(("C"."CUST_ID"<=400 AND "C"."CUST_LAST_NAME" LIKE 'Her%' AND "C"."CUST_ID">=100))

Note
-----
   - SQL plan baseline SQL_PLAN_1a11bn5sndsc764541f84 used for this statement


24 linhas selecionadas.

SQL >

 

Observe na seção NOTE do plano de execução que aparece a mensagem: “SQL plan baseline SQL_PLAN_1a11bn5sndsc764541f84 used for this statement”. Esta mensagem confirma que o plano de execução utilizado foi aquele que acessa a tabela CUSTOMERS usando operação TABLE ACCESS FULL.

 

09) Executar o procedimento de evolução do SPM

Ao habilitar o gerenciamento dos planos de execução utilizando o SPM garantimos que nenhum plano de execução será substituído por outro sem que antes o Oracle verifique se o desempenho deste novo plano é melhor que aqueles já utilizados, este mecanismo impede a regressão de desempenho na execução das instruções SQL, para evoluir o plano da nossa consulta vamos executar a procedure EVOLVE_SQL_PLAN_BASELINE do pacote DBMS_SPM.

 

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
136
137
SQL > SET SERVEROUTPUT ON TAB OFF
SQL > SET LONG 10000
SQL >
SQL > DECLARE
  2     report clob;
  3  BEGIN
  4     report := dbms_spm.evolve_sql_plan_baseline('SQL_15042ba17146e187','SQL_PLAN_1a11bn5sndsc71a248297',VERIFY=>'YES',COMMIT=>'YES');
  5     DBMS_OUTPUT.PUT_LINE(report);
  6  END;
  7  /
GENERAL INFORMATION SECTION
---------------------------------------------------------------------------------------------

 Task Information:

---------------------------------------------
 Task Name            : TAREFA_171
 Task Owner           : SYSTEM
 Execution Name       : EXEC_951
 Execution Type       : SPM EVOLVE
 Scope                : COMPREHENSIVE
 Status               : COMPLETED
 Started              : 09/14/2020 20:05:17
 Finished             : 09/14/2020 20:05:17
 Last Updated         : 09/14/2020 20:05:17
 Global Time Limit    : 2147483646
 Per-Plan Time Limit  : UNUSED
 Number of Errors     : 0

---------------------------------------------------------------------------------------------

SUMMARY
SECTION
---------------------------------------------------------------------------------------------
  Number of plans processed  : 1
  Number of findings         : 2
  Number of recommendations  : 1
  Number of errors           : 0
---------------------------------------------------------------------------------------------

DETAILS
SECTION
---------------------------------------------------------------------------------------------
 Object ID          : 2
 Test Plan Name     : SQL_PLAN_1a11bn5sndsc71a248297
 Base Plan Name     : SQL_PLAN_1a11bn5sndsc764541f84
 SQL Handle         : SQL_15042ba17146e187
 Parsing Schema     : SH
 Test Plan Creator  : SYSTEM
 SQL Text           : SELECT /* dbtw-066 */ c.cust_id, c.cust_first_name,
                    c.cust_last_name FROM sh.customers c WHERE c.cust_id >=100
                    AND c.cust_id <=400 AND c.cust_last_name like 'Her%'

Execution Statistics:
-----------------------------
                    Base Plan                     Test Plan
                    ----------------------------  ----------------------------
 Elapsed Time (s):  .000192                       .000019
 CPU Time (s):      .000198                       .000015
 Buffer Gets:       151                           0
 Optimizer Cost:    95                            6
 Disk Reads:        0         0
 Direct Writes:     0                             0
 Rows Processed:    0                             0
 Executions:        10                            10


FINDINGS
SECTION
---------------------------------------------------------------------------------------------

Findings (2):
-----------------------------
 1. O plano foi verificado em 0.03500 segundos. Ele passou o critério de
    benefício porque seu desempenho verificado foi 252.55568 vezes melhor do que a do plano base.
 2. O plano foi aceito automaticamente.

Recommendation:
-----------------------------
 Consider accepting the plan.



EXPLAIN PLANS SECTION
---------------------------------------------------------------------------------------------

Baseline Plan
-----------------------------
 Plan Id          : 301

Plan Hash Value  : 1683234692

--------------------------------------------------------------------------
| Id  | Operation           | Name      | Rows | Bytes | Cost | Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |           |    1 |    20 |   95 | 00:00:01 |
| * 1 |   TABLE ACCESS FULL | CUSTOMERS |    1 |    20 |   95 | 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 1 - filter("C"."CUST_ID"<=400 AND "C"."CUST_LAST_NAME" LIKE 'Her%' AND "C"."CUST_ID">=100)


Test Plan
-----------------------------
 Plan Id          : 302
 Plan Hash Value  : 438600343


------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name          | Rows | Bytes | Cost | Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |               |    1 |    20 |    6 | 00:00:01 |
|   1 |   TABLE ACCESS BY INDEX ROWID BATCHED | CUSTOMERS     |    1 |    20 |    6 | 00:00:01 |
|   2 |    BITMAP CONVERSION TO ROWIDS        |               |      |       |      |          |
|   3 |     BITMAP AND                        |               |      |       |      |          |
|   4 |      BITMAP CONVERSION FROM ROWIDS    |               |      |       |      |          |
|   5 |       SORT ORDER BY                   |               |      |       |      |          |
| * 6 |        INDEX RANGE SCAN               | CUST_NAME_IDX |      |       |    2 | 00:00:01 |
|   7 |      BITMAP CONVERSION FROM ROWIDS    |               |      |       |      |          |
|   8 |       SORT ORDER BY                   |               |      |       |      |          |
| * 9 |        INDEX RANGE SCAN               | CUSTOMERS_PK  |      |       |    2 | 00:00:01 |
------------------------------------------------------------------------------------------------

Predicate Information
(identified by operation id):
------------------------------------------
* 6 - access("C"."CUST_LAST_NAME" LIKE 'Her%')
* 6 - filter("C"."CUST_LAST_NAME" LIKE 'Her%' AND "C"."CUST_LAST_NAME" LIKE 'Her%')
* 9 - access("C"."CUST_ID">=100 AND "C"."CUST_ID"<=400)

---------------------------------------------------------------------------------------------

Procedimento PL/SQL concluído com sucesso.

SQL >

 

Observe no relatório na seção EXECUTION STATISTICS que o SPM simula a execução do plano não aceito e compara as estatísticas de desempenho dos dois planos, o que estava ACEITO na BASELINE e o novo plano. Na sequência temos a seção FINDINGS onde o SPM indica que o novo plano foi aceito.

 

10) Executar a consulta novamente com o novo plano

Vamos executar a consulta novamente e verficar se houve alguma evolução no plano de execução utilizado pelo Otimizador.

 

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
SQL > SELECT /* dbtw-066 */
  2         c.cust_id, c.cust_first_name, c.cust_last_name
  3    FROM sh.customers c
  4   WHERE c.cust_id >=100
  5     AND c.cust_id <=400
  6     AND c.cust_last_name like 'Her%';

   CUST_ID CUST_FIRST_NAME      CUST_LAST_NAME
---------- -------------------- ----------------------------------------
       167 Eve                  Herd
       161 Linda                Hermann

2 linhas selecionadas.

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

SQL_ID           CHILD_NUMBER
---------------- ------------
1xj5b63mpbz67               0

1 linha selecionada.

SQL >
SQL > SELECT *
  2    FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'typical iostats last'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  1xj5b63mpbz67, child number 0
-------------------------------------
SELECT /* dbtw-066 */        c.cust_id, c.cust_first_name,
c.cust_last_name   FROM sh.customers c  WHERE c.cust_id >=100    AND
c.cust_id <=400    AND c.cust_last_name like 'Her%'

Plan hash value: 203008431

-----------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name          | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |               |      1 |        |       |     6 (100)|          |      2 |00:00:00.01 |       6 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| CUSTOMERS     |      1 |      1 |    20 |     6  (34)| 00:00:01 |      2 |00:00:00.01 |       6 |
|   2 |   BITMAP CONVERSION TO ROWIDS       |               |      1 |        |       |            |          |      2 |00:00:00.01 |       4 |
|   3 |    BITMAP AND                       |               |      1 |        |       |            |          |      1 |00:00:00.01 |       4 |
|   4 |     BITMAP CONVERSION FROM ROWIDS   |               |      1 |        |       |            |          |      1 |00:00:00.01 |       2 |
|   5 |      SORT ORDER BY                  |               |      1 |        |       |            |          |     82 |00:00:00.01 |       2 |
|*  6 |       INDEX RANGE SCAN              | CUST_NAME_IDX |      1 |     61 |       |     2   (0)| 00:00:01 |     82 |00:00:00.01 |       2 |
|   7 |     BITMAP CONVERSION FROM ROWIDS   |               |      1 |        |       |            |          |      1 |00:00:00.01 |       2 |
|   8 |      SORT ORDER BY                  |               |      1 |        |       |            |          |    301 |00:00:00.01 |       2 |
|*  9 |       INDEX RANGE SCAN              | CUSTOMERS_PK  |      1 |     61 |       |     2   (0)| 00:00:01 |    301 |00:00:00.01 |       2 |
-----------------------------------------------------------------------------------------------------------------------------------------------

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

   6 - access("C"."CUST_LAST_NAME" LIKE 'Her%')
       filter(("C"."CUST_LAST_NAME" LIKE 'Her%' AND "C"."CUST_LAST_NAME" LIKE 'Her%'))
   9 - access("C"."CUST_ID">=100 AND "C"."CUST_ID"<=400)

Note
-----
   - SQL plan baseline SQL_PLAN_1a11bn5sndsc71a248297 used for this statement


34 linhas selecionadas.

SQL >

 

Analisando o plano de execução constatamos que o índice CUST_NAME_IDX criado na etapa 05 foi utilizado, este plano de execução utilizando índice é muito mais eficiente, o plano que fazia FULL SCAN na tabela CUSTOMERS estava lendo 1.520 Buffers, o novo plano leu somente 6 Buffers na Memoria do banco.

 

11) Eliminar a BASELINE criada

Vamos eliminar a BASELINE e o ÍNDICE para deixar o esquema SH como estava antes da simulação.

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
SQL > SET SERVEROUTPUT ON
SQL > DECLARE
  2    l_plans_dropped  PLS_INTEGER;
  3  BEGIN
  4    l_plans_dropped := DBMS_SPM.drop_sql_plan_baseline (
  5      sql_handle => 'SQL_15042ba17146e187');
  6
  7    DBMS_OUTPUT.put_line(l_plans_dropped);
  8  END;
  9  /
2

Procedimento PL/SQL concluído com sucesso.

SQL >
SQL >
SQL > drop index sh.CUST_NAME_IDX;

Índice eliminado.

SQL >

 

Referências

 

https://docs.oracle.com/en/database/oracle/oracle-database/19/tgsql/overview-of-sql-plan-management.html

 
 
 
Promo-D75L

video-02
 
 
 
 
 
 
 
 
 
 
 
 

Deixe uma resposta

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