SQL Profile: Como sobreviver sem ela?

SQL Plan Mngmt

Em nossas tarefas de “SQL Tuning” muitas vezes não temos como alterar o código da instrução SQL e nesses casos precisamos utilizar alguns recursos do banco de dados que permitem influenciar o Otimizador na montagem do plano de execução sem a necessidade de incluir um “HINT” no código da instrução SQL. Um desses recursos é a “SQL_Profile”, porem para utiliza-la necessitamos da “Option Tuning Pack”, essa restrição já foi abordada num artigo publicado anteriormente. Nesse artigo vamos mostrar como podemos alterar um plano de execução sem alterar o código da instrução SQL, utilizando o recurso “SQL Plan Management” que não requer “Options” para sua utilização.

O recurso “SQL Plan Management” (SPM) foi concebido para garantir a estabilidade dos planos de execução e impedir que o Otimizador utilize um plano de execução com tempo de resposta maior que os planos que já vinham sendo utilizados, para impedir a regressão de um plano de execução o SPM só aceita um plano de execução depois que ele for validado levando em consideração o tempo de execução desse novo plano. Sem o SPM o Otimizador faz a escolha do plano de execução pelo CUSTO do plano que em algumas situações não representa a melhor opção.

O SPM possui várias “procedures” para o seu gerenciamento, para demonstrar o que foi proposto inicialmente vamos utilizar a “procedure DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE” que permite a criação de um “SQL PLAN” manualmente.

Simulando a alteração de um plano de execução

Para realizar a simulação vamos executar os seguintes passos:

1) Criar uma tabela com um índice e coletar as estatísticas.
2) Executar uma consulta nessa tabela utilizando o índice.
3) Executar a mesma consulta com um “HINT” para forçar um “FULL TABLE SCAN” na tabela.
4) Criar um “SQL PLAN” manualmente para a consulta original forçando o “FULL TABLE SCAN”.
5) Executar a consulta original (sem HINT) que utilizará “SQL PLAN” com “FULL TABLE SCAN”.

1) Criando a tabela

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SQL > SELECT * FROM V$VERSION where rownum < 2;

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

SQL > CREATE TABLE MY_OBJECTS AS SELECT * FROM DBA_OBJECTS;

Table created.

SQL > CREATE INDEX IDX_MY_OBJECTS ON MY_OBJECTS(OBJECT_TYPE);

Index created.

SQL > exec dbms_stats.GATHER_TABLE_STATS(ownname => USER ,tabname =>'MY_OBJECTS',method_opt=>'FOR ALL INDEXED COLUMNS SIZE 254');

PL/SQL procedure successfully completed.

SQL >

 

2) Consulta com plano escolhido 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
SQL > ALTER SESSION SET statistics_level=ALL;

Session altered.

SQL >
SQL > variable B1 VARCHAR2(32)
SQL >
SQL > exec :B1 := 'LOB PARTITION';

PL/SQL procedure successfully completed.

SQL >
SQL > SELECT /* DBTW001 */ OBJECT_NAME FROM MY_OBJECTS WHERE OBJECT_TYPE = :B1;

OBJECT_NAME
--------------------------------------------------------------------------------------------------------------------------------
SYS_LOB0000011479C00008$$

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

SQL_ID        CHILD_NUMBER
------------- ------------
6k0ys0f922fbm            0

SQL >
SQL > SELECT *
  2    FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'allstats last'));
old   2:   FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'allstats last'))
new   2:   FROM TABLE (dbms_xplan.display_cursor ('6k0ys0f922fbm',         0,'allstats last'))

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  6k0ys0f922fbm, child number 0
-------------------------------------
SELECT /* DBTW001 */ OBJECT_NAME FROM MY_OBJECTS WHERE OBJECT_TYPE = :B1

Plan hash value: 1142617335

--------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                |      1 |        |      1 |00:00:00.01 |       4 |
|   1 |  TABLE ACCESS BY INDEX ROWID| MY_OBJECTS     |      1 |     12 |      1 |00:00:00.01 |       4 |
|*  2 |   INDEX RANGE SCAN          | IDX_MY_OBJECTS |      1 |     12 |      1 |00:00:00.01 |       3 |
--------------------------------------------------------------------------------------------------------

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

   2 - access("OBJECT_TYPE"=:B1)


19 rows selected.

SQL >

 

Observando o plano de execução verificamos que o Otimizador escolheu acessar os registros da tabela utilizando o índice.

3) Consulta forçando “FULL TABLE SCAN”

 

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
SQL > SELECT /*+ full(MY_OBJECTS) DBTW002 */  OBJECT_NAME FROM MY_OBJECTS WHERE OBJECT_TYPE = :B1;

OBJECT_NAME
--------------------------------------------------------------------------------------------------------------------------------
SYS_LOB0000011479C00008$$

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

SQL_ID        CHILD_NUMBER
------------- ------------
cgsc57rkrxkfh            0

SQL >
SQL >
SQL > SELECT *
  2    FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'allstats last'));
old   2:   FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'allstats last'))
new   2:   FROM TABLE (dbms_xplan.display_cursor ('cgsc57rkrxkfh',         0,'allstats last'))

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  cgsc57rkrxkfh, child number 0
-------------------------------------
SELECT /*+ full(MY_OBJECTS) DBTW002 */  OBJECT_NAME FROM MY_OBJECTS
WHERE OBJECT_TYPE = :B1

Plan hash value: 880823944

------------------------------------------------------------------------------------------
| Id  | Operation         | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |      1 |        |      1 |00:00:00.01 |    1899 |
|*  1 |  TABLE ACCESS FULL| MY_OBJECTS |      1 |     12 |      1 |00:00:00.01 |    1899 |
------------------------------------------------------------------------------------------

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

   1 - filter("OBJECT_TYPE"=:B1)


19 rows selected.

SQL >

 

A utilização do “HINT FULL()” influencia o Otimizador na montagem do plano de execução que fará a leitura de todos os registros da tabela.

4) Criar SQL PLAN forçando FTS

Para esta simulação podemos deixar os parâmetros do banco referentes ao “SQL PLAN MANAGEMENT” com seus respectivos valores padrões. Consultando a “library cache” encontramos as duas versões de consultas executadas.
 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
SQL > show parameters plan_baseline

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
optimizer_capture_sql_plan_baselines boolean                          FALSE
optimizer_use_sql_plan_baselines     boolean                          TRUE
SQL >
SQL > col sql_text for a60
SQL >
SQL > SELECT sql_id, child_number, plan_hash_value, substr(sql_text,1,60) sql_text
  2    FROM v$sql
  3   WHERE sql_text LIKE '%DBTW00%'
  4     AND sql_text NOT LIKE '%v$sql%';

SQL_ID        CHILD_NUMBER PLAN_HASH_VALUE SQL_TEXT
------------- ------------ --------------- ------------------------------------------------------------
cgsc57rkrxkfh            0       880823944 SELECT /*+ full(MY_OBJECTS) DBTW002 */  OBJECT_NAME FROM MY_
6k0ys0f922fbm            0      1142617335 SELECT /* DBTW001 */ OBJECT_NAME FROM MY_OBJECTS WHERE OBJEC

SQL >

 

Vamos executar o script “cspb.sql” para criar um “SQL PLAN” que forçará a execução do plano com FTS sempre que a consulta original for utilizada. (O código fonte desse script esta disponível no final desse artigo)

 

1
2
3
4
5
6
7
8
9
10
11
SQL > @cspb.sql;
SQL_ID     Consulta original : 6k0ys0f922fbm
CHILD#     Consulta original : 0
SQL_ID     Consulta alterada : cgsc57rkrxkfh
PLAN_HASH# Consulta alterada : 880823944
SELECT /* DBTW001 */ OBJECT_NAME FROM MY_OBJECTS WHERE OBJECT_TYPE = :B1
1

PL/SQL procedure successfully completed.

SQL >

 

Vamos executar a consulta original mais uma vez e verificar quantos “SQL PLAN” foram criados.

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SQL > SELECT /* DBTW001 */ OBJECT_NAME FROM MY_OBJECTS WHERE OBJECT_TYPE = :B1;

OBJECT_NAME
--------------------------------------------------------------------------------------------------------------------------------
SYS_LOB0000011479C00008$$

SQL > SELECT SQL_HANDLE, PLAN_NAME, ENABLED, ACCEPTED, FIXED  FROM   DBA_SQL_PLAN_BASELINES;

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX
------------------------------ ------------------------------ --- --- ---
SQL_4c1ebfe8f93aa46c           SQL_PLAN_4s7pzx3wmp93c77af7aad YES NO  NO
SQL_4c1ebfe8f93aa46c           SQL_PLAN_4s7pzx3wmp93c89405218 YES YES NO

SQL >

 

Quando consultamos a visão “DBA_SQL_PLAN_BASELINES” encontramos dois “SQL PLAN” para a consulta original, um criado manualmente pelo script “cspb.sql” que está como ACCEPTED=YES e o outro criado automaticamente quando executamos a consulta original pela ultima vez e sua condição é ACCEPTED=NO, ou seja ele não será utilizado pelo Otimizador até que seja validado e o seu tempo de execução seja melhor que o do plano que criamos manualmente.

Também podemos obter informações mais detalhadas da consulta original utilizando o pacote “DBMS_XPLAN”.

 

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
SQL > SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(
  2                          sql_handle=>'&sql_handle',
  3                          format=>'basic'));
Enter value for sql_handle: SQL_4c1ebfe8f93aa46c

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------
SQL handle: SQL_4c1ebfe8f93aa46c
SQL text: SELECT /* DBTW001 */ OBJECT_NAME FROM MY_OBJECTS WHERE OBJECT_TYPE = :B1
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
Plan name: SQL_PLAN_4s7pzx3wmp93c77af7aad         Plan id: 2007988909
Enabled: YES     Fixed: NO      Accepted: NO      Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------

Plan hash value: 1142617335

------------------------------------------------------
| Id  | Operation                   | Name           |
------------------------------------------------------
|   0 | SELECT STATEMENT            |                |
|   1 |  TABLE ACCESS BY INDEX ROWID| MY_OBJECTS     |
|   2 |   INDEX RANGE SCAN          | IDX_MY_OBJECTS |
------------------------------------------------------

--------------------------------------------------------------------------------
Plan name: SQL_PLAN_4s7pzx3wmp93c89405218         Plan id: 2302693912
Enabled: YES     Fixed: NO      Accepted: YES     Origin: MANUAL-LOAD
--------------------------------------------------------------------------------

Plan hash value: 880823944

----------------------------------------
| Id  | Operation         | Name       |
----------------------------------------
|   0 | SELECT STATEMENT  |            |
|   1 |  TABLE ACCESS FULL| MY_OBJECTS |
----------------------------------------

34 rows selected.

SQL >

 

5) Consulta original com SQL PLAN

 

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 > variable B1 VARCHAR2(32)
SQL >
SQL > exec :B1 := 'LOB PARTITION';

PL/SQL procedure successfully completed.

SQL >
SQL > SELECT /* DBTW001 */ OBJECT_NAME FROM MY_OBJECTS WHERE OBJECT_TYPE = :B1;

OBJECT_NAME
--------------------------------------------------------------------------------------------------------------------------------
SYS_LOB0000011479C00008$$

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

SQL_ID        CHILD_NUMBER
------------- ------------
6k0ys0f922fbm            0

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  6k0ys0f922fbm, child number 0
-------------------------------------
SELECT /* DBTW001 */ OBJECT_NAME FROM MY_OBJECTS WHERE OBJECT_TYPE = :B1

Plan hash value: 880823944

------------------------------------------------------------------------------------------
| Id  | Operation         | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |      1 |        |      1 |00:00:00.01 |    1899 |
|*  1 |  TABLE ACCESS FULL| MY_OBJECTS |      1 |     12 |      1 |00:00:00.01 |    1899 |
------------------------------------------------------------------------------------------

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

   1 - filter("OBJECT_TYPE"=:B1)

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


22 rows selected.

SQL >

 

Quando executamos a consulta original após a criação do “SQL PLAN” verificamos que o plano de execução utilizado realiza um “Full Table Scan” na tabela e na seção “Note” aparece o comentário: “SQL plan baseline SQL_PLAN_4s7pzx3wmp93c89405218 used for this statement”.

Conclusão

Nos Bancos de dados Oracle, a partir da versão 11G, onde não dispomos da licença “Tuning Pack” e consequentemente não podemos utilizar o recurso “SQL_PROFILE” podemos utilizar como alternativa o recurso “SQL PLAN MANAGEMENT” para fazer ajustes nos planos de execução sem alterar o código da aplicação.

Scripts

Abaixo o código fonte do script utilizado nesse artigo:

cspb

Referências

https://docs.oracle.com/cd/E11882_01/server.112/e41573/optplanmgmt.htm#PFGRF007

 
 
Promo-D75L

video-02
 
 
 
 
 
 
 
 
 
 
 
 
 
 

Deixe uma resposta

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