SQL Profile: Como sobreviver sem ela?

outubro 2, 2016 V. J.A. SQL Plan Management

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 >

Title of the document

Ajudo DBAs e analistas de sistema a se destacarem em suas empresas

e obter um crescimento mais acelerado em suas carreiras, quer saber mais click no link abaixo:

https://dbtimewizard.com.br/mentoria

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.