4 técnicas para modificar um plano de execução sem alterar a instrução SQL

julho 11, 2018 V. J.A. SQL Patch, SQL Plan Management, SQL Profile

Para melhorar de desempenho das aplicações muitas vezes precisamos fazer alguns ajustes nas instruções SQL e em algumas situações não podemos alterar o código pois a aplicação é de terceiros ou apesar de ser um desenvolvimento interno da empresa os prazos para alteração são muito longos. Já faz algum tempo que modificar um plano de execução sem alterar a instrução SQL deixou de ser um grande obstáculo, neste artigo vamos apresentar 4 técnicas diferentes que podemos utilizar para isto e mostrar em qual tipo de licença do Oracle Database 11gR2 elas podem ser aplicadas.

1) Advanced Query Rewrite

A primeira e a mais antiga destas técnicas é a Advanced Query Rewrite, esta opção pode ser utilizada em todas as licenças do 11gR2 inclusive na Express Edition. Para exemplificar a sua aplicação vamos fazer a simulação com uma consulta que será utilizada em todas as simulações das 4 técnicas abordadas neste artigo. Esta consulta foi criada para demonstração das 4 técnicas e não representa as melhores práticas na criação de uma instrução SQL.

Nesta simulação vamos:

1.1) Criar um usuário com os privilégios necessários
1.2) Criar a tabela e índice da consulta
1.3) Executar a consulta original da aplicação
1.4) Executar a consulta com alteração para melhoria do desempenho
1.5) Utilizar Query Rewrite para criar a equivalência entre as consultas
1.6) Executar a consulta original após a criação da equivalência

1.1) Criar um usuário com os privilégios

Para criar o usuário CURSO02 com os privilégios necessários para utilizar a opção Advanced Query Rewrite precisamos conectar no banco de dados com usuário SYS.

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

SQL> SELECT * FROM V$VERSION where rownum < 2;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

SQL> CREATE USER curso02 identified by curso02 default tablespace users temporary tablespace temp;

User created.

SQL> alter user curso02 quota unlimited on users;

User altered.

SQL> grant connect to curso02;

Grant succeeded.

SQL> grant resource to curso02;

Grant succeeded.

SQL> grant query rewrite to curso02;

Grant succeeded.

SQL> GRANT CREATE MATERIALIZED VIEW TO curso02;

Grant succeeded.

SQL> grant execute on sys.DBMS_ADVANCED_REWRITE to curso02;

Grant succeeded.

SQL> grant select any dictionary to curso02;

Grant succeeded.

SQL>

1.2) Criar a tabela e índice da consulta

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

SQL > create table dbtw053 (matr , category, created, descr)
2 as
3 select rownum, mod(rownum, 1000), sysdate-mod(rownum, 2000), 'DBTimeWizard - Performance and Tuning' from dual connect by level <= 1000000;

Tabela criada.

SQL >
SQL > create index dbtw053_category_idx on dbtw053(category);

Índice criado.

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

Procedimento PL/SQL concluído com sucesso.

SQL >

1.3) Executar a consulta original da aplicação

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

A consulta que supostamente extraimos da aplicação possui um HINT que força o Otimizador a utilizar o índice DBTW053_CATEGORY_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

SQL > ALTER SESSION SET statistics_level = ALL;

Sessão alterada.

SQL >
SQL > SELECT /*+ INDEX(A dbtw053_category_idx) */ /* DBTW-053.1 */ category, descr, count(1) qtde FROM dbtw053 A WHERE category BETWEEN 455 AND 559 GROUP BY category, descr;

CATEGORY DESCR QTDE
---------- ------------------------------------- ----------
460 DBTimeWizard - Performance and Tuning 1000
472 DBTimeWizard - Performance and Tuning 1000
.
.
.
559 DBTimeWizard - Performance and Tuning 1000

105 linhas selecionadas.

SQL >
SQL > -- Recupera o SQL_ID e o CHILD_NUMBER da consulta executada
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 '%DBTW-053.1%'
4 AND sql_text NOT LIKE '%v$sql%';

SQL_ID CHILD_NUMBER
---------------- ------------
669gr4xtnvqa1 0

1 linha selecionada.

SQL >
SQL > -- Gera o relatorio do plano de execucao da consulta
SQL >
SQL > SELECT * FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'typical iostats last'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 669gr4xtnvqa1, child number 0
-------------------------------------
SELECT /*+ INDEX(A dbtw053_category_idx) */ /* DBTW-053.1 */ category,
descr, count(1) qtde FROM dbtw053 A WHERE category BETWEEN 455 AND 559
GROUP BY category, descr

Plan hash value: 1755005131

-----------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 106K(100)| | 105 |00:00:00.67 | 105K|
| 1 | HASH GROUP BY | | 1 | 75 | 3150 | 106K (1)| 00:21:17 | 105 |00:00:00.67 | 105K|
| 2 | TABLE ACCESS BY INDEX ROWID| DBTW053 | 1 | 106K| 4351K| 106K (1)| 00:21:17 | 105K|00:00:00.59 | 105K|
|* 3 | INDEX RANGE SCAN | DBTW053_CATEGORY_IDX | 1 | 106K| | 225 (1)| 00:00:03 | 105K|00:00:00.08 | 222 |
-----------------------------------------------------------------------------------------------------------------------------------------------

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

3 - access("CATEGORY">=455 AND "CATEGORY"<=559)

22 linhas selecionadas.

SQL >

No plano de execução acima podemos observar que foram lidas 105 mil linhas na tabela utilizando os ROWID selecionados no índice e o tempo total gasto para executar a consulta foi 67 centésimos de segundo.

1.4) Executar a consulta com alteração

O Otimizador do banco Oracle evolui muito e na versão 11gR2 disponibiliza planos de execução muito eficientes quando as estatísticas do banco são mantidas utilizando as melhores práticas. A inclusão de HINT na consulta deve ser feita em situações muito especiais, no nosso exemplo vamos excluir o HINT e deixar que o Otimizador decida qual é a melhor opção para montagem do 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
59
60
61

Analisando o plano de execução verificamos que o Otimizador escolheu fazer uma operação TABLE FULL SCAN e o tempo de execução caiu de 67 centésimos de segunda para 19 centésimos, um redução de 71% no tempo de execução.

1.5) Utilizar Query Rewrite para criar a equivalência

Agora que sabemos que o HINT incluído na consulta esta prejudicando o seu desempenho, vamos utilizar o pacote DBMS_ADVANCED_REWRITE para gerar uma equivalência entre as duas consultas, ou seja, toda vez que a aplicação enviar ao banco a consulta com HINT o Otimizador vai executar a consulta sem o HINT.
O pacote DBMS_ADVANCED_REWRITE exige que as consultas para as quais esta sendo gerada a equivalência sejam diferentes e não considera diferentes as consultas cuja diferença esteja somente no HINT, pois este ultimo é considerado como um comentário. Por esta razão no nosso exemplo alem de excluir o HINT na consulta melhorada, adicionamos 0 ao campo CATEGORY que é um campo numérico e esta adição não vai representar alteração no seu valor final.

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

SQL > BEGIN
2 sys.DBMS_ADVANCED_REWRITE.DECLARE_REWRITE_EQUIVALENCE (
3 name => 'rewrite_dbtw053',
4 source_stmt => 'SELECT /*+ INDEX(A dbtw053_category_idx) */ /* DBTW-053.1 */ category, descr, count(1) qtde FROM dbtw053 A WHERE category BETWEEN 455 AND 559 GROUP BY category, descr',
5 destination_stmt => 'SELECT /* DBTW-053.2 */ category, descr, count(1) qtde FROM dbtw053 A WHERE category+0 BETWEEN 455 AND 559 GROUP BY category, descr',
6 validate => FALSE,
7 rewrite_mode => 'TEXT_MATCH');
8 END;
9 /

Procedimento PL/SQL concluído com sucesso.

SQL > set long 9999
SQL > select * from DBA_REWRITE_EQUIVALENCES;

NAME SOURCE_STMT DESTINATION_STMT
------------------------------ -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
REWRITE_MO
----------
REWRITE_DBTW053 SELECT /*+ INDEX(A dbtw053_category_idx) */ /* DBTW-053.1 */ category, descr, co SELECT /* DBTW-053.2 */ category, descr, count(1) qtde FROM dbtw053 A WHERE cate
unt(1) qtde FROM dbtw053 A WHERE category BETWEEN 455 AND 559 GROUP BY category, gory+0 BETWEEN 455 AND 559 GROUP BY category, descr
descr
TEXT_MATCH

1 linha selecionada.

SQL >

1.6) Executar a consulta original após a criação da equivalência

Para maior clareza na analise da execução da consulta após a criação da equivalência vamos limpar a SHARED POOL e para que a Advanced Query Rewrite funcione vamos alterar dois parâmetros do banco a nível de sessão: QUERY_REWRITE_INTEGRITY e QUERY_REWRITE_ENABLED.

SQL> -- Conectado com usuario SYS limpar a SHARED POOL
SQL> ALTER SYSTEM flush shared_pool;

System altered.

SQL>
SQL > -- De volta para a sessão do usuario CURSO02, alterar na sessão os parametros de QUERY_REWRITE
SQL >
SQL > ALTER SESSION SET statistics_level = ALL;

Sessão alterada.

SQL > ALTER SESSION SET query_rewrite_integrity = TRUSTED;

Sessão alterada.

SQL > ALTER SESSION SET query_rewrite_enabled = TRUE;

Sessão alterada.

SQL >
SQL > SELECT /*+ INDEX(A dbtw053_category_idx) */ /* DBTW-053.1 */ category, descr, count(1) qtde FROM dbtw053 A WHERE category BETWEEN 455 AND 559 GROUP BY category, de

CATEGORY DESCR QTDE
---------- ------------------------------------- ----------
460 DBTimeWizard - Performance and Tuning 1000
472 DBTimeWizard - Performance and Tuning 1000
.
.
.
559 DBTimeWizard - Performance and Tuning 1000

105 linhas selecionadas.

SQL >
SQL > -- Recupera o SQL_ID e o CHILD_NUMBER da consulta executada
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 '%DBTW-053.1%'
4 AND sql_text NOT LIKE '%v$sql%';

SQL_ID CHILD_NUMBER
---------------- ------------
669gr4xtnvqa1 0

1 linha selecionada.

SQL >
SQL >
SQL > -- Gera o relatorio do plano de execucao da consulta
SQL >
SQL > SELECT * FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'typical iostats last'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 669gr4xtnvqa1, child number 0
-------------------------------------
SELECT /*+ INDEX(A dbtw053_category_idx) */ /* DBTW-053.1 */ category,
descr, count(1) qtde FROM dbtw053 A WHERE category BETWEEN 455 AND 559
GROUP BY category, descr

Plan hash value: 3756543841

------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 2335 (100)| | 105 |00:00:00.25 | 8328 |
| 1 | HASH GROUP BY | | 1 | 75 | 3150 | 2335 (2)| 00:00:29 | 105 |00:00:00.25 | 8328 |
|* 2 | TABLE ACCESS FULL| DBTW053 | 1 | 106K| 4351K| 2327 (2)| 00:00:28 | 105K|00:00:00.18 | 8328 |
------------------------------------------------------------------------------------------------------------------------

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

2 - filter(("CATEGORY"+0>=455 AND "CATEGORY"+0<=559))

21 linhas selecionadas.

SQL >

Analisando o plano de execução verificamos que o Otimizador escolheu fazer uma operação TABLE FULL SCAN apesar de termos executado a consulta original com o HINT e para confirmar que houve a substituição da consulta no momento da execução podemos observar na sessão PREDICATE INFORMATION que o filtro CATEGORY possui a adição do valor 0.

2) SQL PATCH

A segunda técnica é o SQL PATCH, esta opção também pode ser utilizada em todas as licenças do 11gR2 inclusive na Express Edition. Para exemplificar a sua aplicação vamos fazer a simulação utilizando a mesma consulta e na mesma base Oracle Express Edition onde já criamos o usuário CURSO02.

OBS: A equivalência criada na demonstração anterior foi excluída para não interferir nesta simulação.

Nesta simulação vamos:

2.1) Executar a consulta original da aplicação
2.2) Criar um SQL PATCH para excluir o HINT da consulta
2.3) Executar a consulta original novamente

2.1) Executar a consulta da aplicaçã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

SQL > SELECT * FROM V$VERSION where rownum < 2;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

1 linha selecionada.

SQL >
SQL > ALTER SESSION SET statistics_level = ALL;

Sessão alterada.

SQL >
SQL > SELECT /*+ INDEX(A dbtw053_category_idx) */ /* DBTW-053.1 */ category, descr, count(1) qtde FROM dbtw053 A WHERE category BETWEEN 455 AND 559 GROUP BY category, descr;

CATEGORY DESCR QTDE
---------- ------------------------------------- ----------
460 DBTimeWizard - Performance and Tuning 1000
472 DBTimeWizard - Performance and Tuning 1000
.
.
.
559 DBTimeWizard - Performance and Tuning 1000

105 linhas selecionadas.

SQL >
SQL > -- Recupera o SQL_ID e o CHILD_NUMBER da consulta executada
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 '%DBTW-053.1%'
4 AND sql_text NOT LIKE '%v$sql%';

SQL_ID CHILD_NUMBER
---------------- ------------
669gr4xtnvqa1 0

1 linha selecionada.

SQL >
SQL > -- Gera o relatorio do plano de execucao da consulta
SQL >
SQL > SELECT * FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'typical iostats last'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 669gr4xtnvqa1, child number 0
-------------------------------------
SELECT /*+ INDEX(A dbtw053_category_idx) */ /* DBTW-053.1 */ category,
descr, count(1) qtde FROM dbtw053 A WHERE category BETWEEN 455 AND 559
GROUP BY category, descr

Plan hash value: 1755005131

-----------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 106K(100)| | 105 |00:00:00.67 | 105K|
| 1 | HASH GROUP BY | | 1 | 75 | 3150 | 106K (1)| 00:21:17 | 105 |00:00:00.67 | 105K|
| 2 | TABLE ACCESS BY INDEX ROWID| DBTW053 | 1 | 106K| 4351K| 106K (1)| 00:21:17 | 105K|00:00:00.59 | 105K|
|* 3 | INDEX RANGE SCAN | DBTW053_CATEGORY_IDX | 1 | 106K| | 225 (1)| 00:00:03 | 105K|00:00:00.08 | 222 |
-----------------------------------------------------------------------------------------------------------------------------------------------

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

3 - access("CATEGORY">=455 AND "CATEGORY"<=559)

22 linhas selecionadas.

SQL >

No resultado da consulta podemos observar que foram lidas 105 mil linhas na tabela utilizando os ROWID selecionados no índice e o tempo total gasto para executar a consulta foi 67 centésimos de segundo.

2.2) Criar um SQL PATCH para excluir o HINT da consulta

Antes de criarmos o SQL PATCH precisamos conectar com usuário SYS para atribuir o privilégio EXECUTE nos pacotes: DBMS_SQLDIAG_INTERNAL e DBMS_SQLDIAG ao usuário CURSO02.

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> -- Conectar com usuário SYS para atribuir permissão de EXECUTE nos pacotes DBMS_SQLDIAG_INTERNAL e DBMS_SQLDIAG
SQL>
SQL> grant execute on DBMS_SQLDIAG_INTERNAL to curso02;

Grant succeeded.

SQL> grant execute on DBMS_SQLDIAG to curso02;

Grant succeeded.

SQL>
SQL> -- Conectar novamente com usuário CURSO02
SQL>
SQL> -- Recupera o SQL_ID e o CHILD_NUMBER da consulta executada
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 '%DBTW-053.1%'
4 AND sql_text NOT LIKE '%v$sql%';

SQL_ID CHILD_NUMBER
---------------- ------------
669gr4xtnvqa1 0

1 linha selecionada.

SQL>
SQL> set serveroutput on size 9999
SQL> declare
2 m_clob clob;
3 begin
4 select sql_fulltext into m_clob
5 from v$sql
6 where sql_id = '&m_sql_id'
7 and child_number = &m_child_no ;
8
9 sys.dbms_sqldiag_internal.i_create_patch(
10 sql_text => m_clob,
11 hint_text => 'IGNORE_OPTIM_EMBEDDED_HINTS',
12 name => 'Patch_&m_sql_id'
13 );
14 end;
15 /

Procedimento PL/SQL concluído com sucesso.

SQL>
SQL> set lines 200
SQL> select NAME, CREATED, SQL_TEXT from DBA_SQL_PATCHES;

NAME CREATED SQL_TEXT
------------------------------ -------------------- --------------------------------------------------------------------------------
Patch_669gr4xtnvqa1 04/07/18 19:12:37,00 SELECT /*+ INDEX(A dbtw053_category_idx) */ /* DBTW-053.1 */ category, descr, co
0000 unt(1) qtde FROM dbtw053 A WHERE category BETWEEN 455 AND 559 GROUP BY category,
descr

1 linha selecionada.

SQL>

Observe que foi criado um SQL PATCH para a consulta original onde foi incluído o HINT IGNORE_OPTIM_EMBEDDED_HINTS que forçará o Otimizador a ignorar qualquer HINT existente na consulta.

2.3) Executar a consulta original novamente

SQL> ALTER SESSION SET statistics_level = ALL;

Sessão alterada.

SQL>
SQL> SELECT /*+ INDEX(A dbtw053_category_idx) */ /* DBTW-053.1 */ category, descr, count(1) qtde FROM dbtw053 A WHERE category BETWEEN 455 AND 559 GROUP BY category, descr;

CATEGORY DESCR QTDE
---------- ------------------------------------- ----------
460 DBTimeWizard - Performance and Tuning 1000
472 DBTimeWizard - Performance and Tuning 1000
.
.
.
559 DBTimeWizard - Performance and Tuning 1000

105 linhas selecionadas.

SQL>
SQL> -- Recupera o SQL_ID e o CHILD_NUMBER da consulta executada
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 '%DBTW-053.1%'
4 AND sql_text NOT LIKE '%v$sql%';

SQL_ID CHILD_NUMBER
---------------- ------------
669gr4xtnvqa1 0

1 linha selecionada.

SQL>
SQL>
SQL> -- Gera o relatorio do plano de execucao da consulta
SQL>
SQL> SELECT * FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'typical iostats last'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 669gr4xtnvqa1, child number 0
-------------------------------------
SELECT /*+ INDEX(A dbtw053_category_idx) */ /* DBTW-053.1 */ category,
descr, count(1) qtde FROM dbtw053 A WHERE category BETWEEN 455 AND 559
GROUP BY category, descr

Plan hash value: 3756543841

------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 2328 (100)| | 105 |00:00:00.18 | 8325 |
| 1 | HASH GROUP BY | | 1 | 75 | 3150 | 2328 (2)| 00:00:28 | 105 |00:00:00.18 | 8325 |
|* 2 | TABLE ACCESS FULL| DBTW053 | 1 | 106K| 4351K| 2320 (2)| 00:00:28 | 105K|00:00:00.11 | 8325 |
------------------------------------------------------------------------------------------------------------------------

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

2 - filter(("CATEGORY">=455 AND "CATEGORY"<=559))

Note
-----
- SQL patch "Patch_669gr4xtnvqa1" used for this statement

25 linhas selecionadas.

SQL>
SQL> exec sys.DBMS_SQLDIAG.drop_sql_patch(name => 'Patch_669gr4xtnvqa1');

PL/SQL procedure successfully completed.

SQL>

Analisando o plano de execução verificamos que o Otimizador escolheu fazer a operação TABLE FULL SCAN apesar da consulta possuir o HINT que pede para utilizar o índice, alem disso podemos verificar na seção NOTE o comentário:
[SQL patch “Patch_669gr4xtnvqa1” used for this statement]

3) SQL PLAN MANAGEMENT

A terceira técnica utiliza o SQL PLAN MANAGEMENT que pode ser utilizada somente com a licença Enterprise Edition do 11gR2. Nas licenças Express Edition e Standard Edition os pacotes do SPM estão disponíveis porem eles não funcionam adequadamente, no artigo publicado no blog ORACLE OPTIMIZER fica claro que o SPM está disponível para licença Enterprise Edition e não requer licenças adicionais como as options Diagnostic & Tuning Pack.

Para exemplificar a sua aplicação vamos fazer uma simulação utilizando a mesma consulta das simulações anteriores, porem agora vamos utilizar uma base Oracle 11gR2 Enterprise Edition.

Nesta simulação vamos:

3.1) Criar um usuário com os privilégios necessários
3.2) Criar a tabela e índice da consulta
3.3) Executar a consulta original da aplicação
3.4) Executar a consulta com alteração para melhoria do desempenho
3.5) Criar a SQL PLAN BASELINE
3.6) Incluir o plano original na BASELINE
3.7) Executar a consulta original novamente

3.1) Criar um usuário com os privilégios

Para criar o usuário CURSO02 com os privilégios necessários precisamos conectar no banco de dados com usuário SYS.

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

SQL> -- Conectar na base com usuário SYS
SQL>
SQL> SELECT * FROM V$VERSION where rownum < 2;

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

SQL> CREATE USER curso02 identified by curso02 default tablespace users temporary tablespace temp;

User created.

SQL> alter user curso02 quota unlimited on users;

User altered.

SQL> grant dba to curso02;

Grant succeeded.

SQL> grant execute on DBMS_SPM to curso02;

Grant succeeded.

SQL>

3.2) Criar a tabela e índice da consulta

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

3.3) Executar a consulta original da aplicação

A consulta que supostamente extraímos da aplicação possui um HINT que força o Otimizador a utilizar o índice DBTW053_CATEGORY_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

SQL > ALTER SESSION SET statistics_level = ALL;

Sessão alterada.

SQL >
SQL > SELECT /*+ INDEX(A dbtw053_category_idx) */ /* DBTW-053.1 */ category, descr, count(1) qtde FROM dbtw053 A WHERE category BETWEEN 455 AND 559 GROUP BY category, descr;

CATEGORY DESCR QTDE
---------- ------------------------------------- ----------
460 DBTimeWizard - Performance and Tuning 1000
472 DBTimeWizard - Performance and Tuning 1000
.
.
.
559 DBTimeWizard - Performance and Tuning 1000

105 linhas selecionadas.

SQL >
SQL > -- Recupera o SQL_ID e o CHILD_NUMBER da consulta executada
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 '%DBTW-053.1%'
4 AND sql_text NOT LIKE '%v$sql%';

SQL_ID CHILD_NUMBER
------------- ------------
1vtkt906qhga8 0

1 linha selecionada.

SQL >
SQL > -- Gera o relatorio do plano de execucao da consulta
SQL >
SQL > SELECT * FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'typical iostats last'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 1vtkt906qhga8, child number 0
-------------------------------------
SELECT /*+ INDEX(A dbtw053_category_idx) */ /* DBTW-053.1 */ category,
descr, count(:"SYS_B_0") qtde FROM dbtw053 A WHERE category BETWEEN
:"SYS_B_1" AND :"SYS_B_2" GROUP BY category, descr

Plan hash value: 1559473304

---------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads |
---------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 106K(100)| | 105 |00:00:07.08 | 105K| 2088 |
| 1 | HASH GROUP BY | | 1 | 75 | 3150 | 106K (1)| 00:00:04 | 105 |00:00:07.08 | 105K| 2088 |
|* 2 | FILTER | | 1 | | | | | 105K|00:00:07.06 | 105K| 2088 |
| 3 | TABLE ACCESS BY INDEX ROWID| DBTW053 | 1 | 106K| 4351K| 106K (1)| 00:00:04 | 105K|00:00:07.05 | 105K| 2088 |
|* 4 | INDEX RANGE SCAN | DBTW053_CATEGORY_IDX | 1 | 106K| | 225 (1)| 00:00:01 | 105K|00:00:00.19 | 222 | 210 |
---------------------------------------------------------------------------------------------------------------------------------------------------------

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

2 - filter(:SYS_B_2>=:SYS_B_1)
4 - access("CATEGORY">=:SYS_B_1 AND "CATEGORY"<=:SYS_B_2)

24 linhas selecionadas.

SQL >

No plano de execução acima podemos observar que foram lidas 105 mil linhas na tabela utilizando os ROWID selecionados no índice e o tempo total gasto para executar a consulta foi 7 segundos e 8 centésimos.

3.4) Executar a consulta com alteração

Vamos executar a consulta novamente sem o HINT para dar mais liberdade de decisão ao 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

SQL > SELECT /* DBTW-053.2 */ category, descr, count(1) qtde FROM dbtw053 A WHERE category BETWEEN 455 AND 559 GROUP BY category, descr;

CATEGORY DESCR QTDE
---------- ------------------------------------- ----------
460 DBTimeWizard - Performance and Tuning 1000
472 DBTimeWizard - Performance and Tuning 1000
.
.
.
559 DBTimeWizard - Performance and Tuning 1000

105 linhas selecionadas.

SQL >
SQL > -- Recupera o SQL_ID e o CHILD_NUMBER da consulta executada
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 '%DBTW-053.2%'
4 AND sql_text NOT LIKE '%v$sql%';

SQL_ID CHILD_NUMBER
------------- ------------
aa2v8aq8t17w2 0

1 linha selecionada.

SQL >
SQL > -- Gera o relatorio do plano de execucao da consulta
SQL >
SQL > SELECT * FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'typical iostats last'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID aa2v8aq8t17w2, child number 0
-------------------------------------
SELECT /* DBTW-053.2 */ category, descr, count(:"SYS_B_0") qtde FROM
dbtw053 A WHERE category BETWEEN :"SYS_B_1" AND :"SYS_B_2" GROUP BY
category, descr

Plan hash value: 186720033

----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads |
----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 297 (100)| | 105 |00:00:01.77 | 8319 | 8316 |
| 1 | HASH GROUP BY | | 1 | 75 | 3150 | 297 (11)| 00:00:01 | 105 |00:00:01.77 | 8319 | 8316 |
|* 2 | FILTER | | 1 | | | | | 105K|00:00:01.75 | 8319 | 8316 |
|* 3 | TABLE ACCESS FULL| DBTW053 | 1 | 106K| 4351K| 290 (9)| 00:00:01 | 105K|00:00:01.74 | 8319 | 8316 |
----------------------------------------------------------------------------------------------------------------------------------

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

2 - filter(:SYS_B_2>=:SYS_B_1)
3 - filter(("CATEGORY">=:SYS_B_1 AND "CATEGORY"<=:SYS_B_2))

23 linhas selecionadas.

SQL >

Analisando o plano de execução verificamos que o Otimizador escolheu fazer uma operação TABLE FULL SCAN e o tempo de execução caiu de 7 segundos e 8 centésimos para 1 segundo e 77 centésimos, um redução de 75% no tempo de execução.

3.5) Criar a SQL PLAN BASELINE

Agora vamos criar uma SQL PLAN BASELINE manualmente onde vamos colocar o plano que faz a operação TABLE FULL SCAN como o plano aceitável, para isso vamos utilizar o script cspb.sql.

SQL > show parameters plan_baseline

NAME TYPE VALUE
------------------------------------ -------------------------------- ------------------------------
optimizer_capture_sql_plan_baselines boolean FALSE
optimizer_use_sql_plan_baselines boolean TRUE
SQL >
SQL > -- Obter SQL_ID, CHILD_NUMBER e PLAN_HASH_VALUE das consultas executadas nas etapas 3 e 4
SQL >
SQL > col sql_text for a60
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 '%dbtw053%'
4 AND sql_text NOT LIKE '%v$sql%';

SQL_ID CHILD_NUMBER PLAN_HASH_VALUE SQL_TEXT
------------- ------------ --------------- ------------------------------------------------------------
1vtkt906qhga8 0 1559473304 SELECT /*+ INDEX(A dbtw053_category_idx) */ /* DBTW-053.1 */
aa2v8aq8t17w2 0 186720033 SELECT /* DBTW-053.2 */ category, descr, count(:"SYS_B_0") q

2 linhas selecionadas.

SQL >
SQL > -- Executar o script "cspb.sql" para criar um SQL PLAN
SQL >
SQL > @cspb.sql;
SQL_ID Consulta original : 1vtkt906qhga8
CHILD# Consulta original : 0
SQL_ID Consulta alterada : aa2v8aq8t17w2
PLAN_HASH# Consulta alterada : 186720033
SELECT /*+ INDEX(A dbtw053_category_idx) */ /* DBTW-053.1 */ category, descr, count(:"SYS_B_0") qtde FROM dbtw053 A WHERE category BETWEEN :"SYS_B_1" AND :"SYS_B_2" GROUP BY category, descr
1

Procedimento PL/SQL concluído com sucesso.

SQL >
SQL > -- Obter informações da SQL PLAN BASELINE criada
SQL >
SQL > SELECT SQL_HANDLE, PLAN_NAME, ENABLED, ACCEPTED, FIXED FROM DBA_SQL_PLAN_BASELINES;

SQL_HANDLE PLAN_NAME ENA ACC FIX
------------------------------ ------------------------------ --- --- ---
SQL_cd29185c945f9cc5 SQL_PLAN_cua8sbka5z7655a5cb76a YES YES NO

1 linha selecionada.

SQL >
SQL > SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(
2 sql_handle=>'&sql_handle',
3 format=>'basic'));
Informe o valor para sql_handle: SQL_cd29185c945f9cc5

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

--------------------------------------------------------------------------------
SQL handle: SQL_cd29185c945f9cc5
SQL text: SELECT /*+ INDEX(A dbtw053_category_idx) */ /* DBTW-053.1 */ category,
descr, count(:"SYS_B_0") qtde FROM dbtw053 A WHERE category BETWEEN
:"SYS_B_1" AND :"SYS_B_2" GROUP BY category, descr
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
Plan name: SQL_PLAN_cua8sbka5z7655a5cb76a Plan id: 1516025706
Enabled: YES Fixed: NO Accepted: YES Origin: MANUAL-LOAD
--------------------------------------------------------------------------------

Plan hash value: 186720033

---------------------------------------
| Id | Operation | Name |
---------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | HASH GROUP BY | |
| 2 | FILTER | |
| 3 | TABLE ACCESS FULL| DBTW053 |
---------------------------------------

23 linhas selecionadas.

SQL >

3.6) Incluir o plano original na BASELINE

Como verificamos na etapa anterior o plano com a operação TABLE FULL SCAN foi incluído na BASELINE como ACCEPTED, agora vamos executar a consulta com HINT novamente para que o seu plano que utiliza o índice seja incluído na BASELINE como NOT ACCEPTED.

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

SQL > SELECT /*+ INDEX(A dbtw053_category_idx) */ /* DBTW-053.1 */ category, descr, count(1) qtde FROM dbtw053 A WHERE category BETWEEN 455 AND 559 GROUP BY category, descr;

CATEGORY DESCR QTDE
---------- ------------------------------------- ----------
460 DBTimeWizard - Performance and Tuning 1000
472 DBTimeWizard - Performance and Tuning 1000
.
.
.
559 DBTimeWizard - Performance and Tuning 1000

105 linhas selecionadas.

SQL >
SQL > -- Obter informações da SQL PLAN BASELINES
SQL >
SQL > SELECT SQL_HANDLE, PLAN_NAME, ENABLED, ACCEPTED, FIXED FROM DBA_SQL_PLAN_BASELINES;

SQL_HANDLE PLAN_NAME ENA ACC FIX
------------------------------ ------------------------------ --- --- ---
SQL_cd29185c945f9cc5 SQL_PLAN_cua8sbka5z7655a5cb76a YES YES NO
SQL_cd29185c945f9cc5 SQL_PLAN_cua8sbka5z7658b066ca3 YES NO NO

2 linhas selecionadas.

SQL > SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(
2 sql_handle=>'&sql_handle',
3 format=>'basic'));
Informe o valor para sql_handle: SQL_cd29185c945f9cc5

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

--------------------------------------------------------------------------------
SQL handle: SQL_cd29185c945f9cc5
SQL text: SELECT /*+ INDEX(A dbtw053_category_idx) */ /* DBTW-053.1 */ category,
descr, count(:"SYS_B_0") qtde FROM dbtw053 A WHERE category BETWEEN
:"SYS_B_1" AND :"SYS_B_2" GROUP BY category, descr
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
Plan name: SQL_PLAN_cua8sbka5z7655a5cb76a Plan id: 1516025706
Enabled: YES Fixed: NO Accepted: YES Origin: MANUAL-LOAD
--------------------------------------------------------------------------------

Plan hash value: 186720033

---------------------------------------
| Id | Operation | Name |
---------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | HASH GROUP BY | |
| 2 | FILTER | |
| 3 | TABLE ACCESS FULL| DBTW053 |
---------------------------------------

--------------------------------------------------------------------------------
Plan name: SQL_PLAN_cua8sbka5z7658b066ca3 Plan id: 2332454051
Enabled: YES Fixed: NO Accepted: NO Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------

Plan hash value: 1559473304

--------------------------------------------------------------
| Id | Operation | Name |
--------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | HASH GROUP BY | |
| 2 | FILTER | |
| 3 | TABLE ACCESS BY INDEX ROWID| DBTW053 |
| 4 | INDEX RANGE SCAN | DBTW053_CATEGORY_IDX |
--------------------------------------------------------------

40 linhas selecionadas.

SQL >

3.7) Executar a consulta original novamente

Com a BASELINE devidamente carregada vamos executar a consulta original e verificar que plano de execução o Otimizador vai escolher.

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 /*+ INDEX(A dbtw053_category_idx) */ /* DBTW-053.1 */ category, descr, count(1) qtde FROM dbtw053 A WHERE category BETWEEN 455 AND 559 GROUP BY category, descr;

CATEGORY DESCR QTDE
---------- ------------------------------------- ----------
460 DBTimeWizard - Performance and Tuning 1000
472 DBTimeWizard - Performance and Tuning 1000
.
.
.
559 DBTimeWizard - Performance and Tuning 1000

105 linhas selecionadas.

SQL >
SQL > -- Recupera o SQL_ID e o CHILD_NUMBER da consulta executada
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 '%DBTW-053.1%'
4 AND sql_text NOT LIKE '%v$sql%';

SQL_ID CHILD_NUMBER
------------- ------------
1vtkt906qhga8 0

1 linha selecionada.

SQL >
SQL >
SQL > -- Gera o relatorio do plano de execucao da consulta
SQL >
SQL > SELECT * FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'typical iostats last'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 1vtkt906qhga8, child number 0
-------------------------------------
SELECT /*+ INDEX(A dbtw053_category_idx) */ /* DBTW-053.1 */ category,
descr, count(:"SYS_B_0") qtde FROM dbtw053 A WHERE category BETWEEN
:"SYS_B_1" AND :"SYS_B_2" GROUP BY category, descr

Plan hash value: 186720033

----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads |
----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 297 (100)| | 105 |00:00:01.65 | 8319 | 8316 |
| 1 | HASH GROUP BY | | 1 | 75 | 3150 | 297 (11)| 00:00:01 | 105 |00:00:01.65 | 8319 | 8316 |
|* 2 | FILTER | | 1 | | | | | 105K|00:00:01.63 | 8319 | 8316 |
|* 3 | TABLE ACCESS FULL| DBTW053 | 1 | 106K| 4351K| 290 (9)| 00:00:01 | 105K|00:00:01.62 | 8319 | 8316 |
----------------------------------------------------------------------------------------------------------------------------------

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

2 - filter(:SYS_B_2>=:SYS_B_1)
3 - filter(("CATEGORY">=:SYS_B_1 AND "CATEGORY"<=:SYS_B_2))

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

27 linhas selecionadas.

SQL >

Analisando o plano de execução verificamos que o Otimizador escolheu fazer uma operação TABLE FULL SCAN apesar da consulta executada estar com o HINT, também podemos observar na seção NOTE a mensagem:

[SQL plan baseline SQL_PLAN_cua8sbka5z7655a5cb76a used for this statement]

4) SQL PROFILE

A quarta e ultima técnica é a SQL PROFILE, para utilizar esta opção no Oracle Database 11gR2 é necessária a licença Enterprise Edition e as options DIAGNOSTIC & TUNING PACK.

Para exemplificar a sua aplicação vamos utilizar o mesmo usuário CURSO02 da simulação anterior e vamos desativar o SQL PLAN MANAGEMENT a nível de sessão para que o Otimizador desconsidere a BASELINE criada anteriormente.

Nesta simulação vamos:

4.1) Executar a consulta original da aplicação
4.2) Criar uma SQL_PROFILE manualmente da consulta original
4.3) Executar a consulta sem o HINT
4.4) Alterar a OUTLINE da SQL_PROFILE que define o tipo de acesso a tabela
4.5) Executar a consulta original novamente

4.1) Executar a consulta original da aplicação

Vamos executar a consulta com HINT que força a utilização do índice para acessar a tabela e desativar a SQL PLAN BASELINE gerada na simulação anterior, alterando o valor do parâmetro OPTIMIZER_USE_SQL_PLAN_BASELINES para FALSE a nível de sessão.

SQL> SELECT * FROM V$VERSION where rownum < 2;

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

1 linha selecionada.

SQL>
SQL> show parameters pack

NAME TYPE VALUE
------------------------------------ -------------------------------- -----------------------
control_management_pack_access string DIAGNOSTIC+TUNING

SQL>
SQL> ALTER SESSION SET optimizer_use_sql_plan_baselines = FALSE;

Sessão alterada.

SQL> ALTER SESSION SET statistics_level = ALL;

Sessão alterada.

SQL>
SQL> SELECT /*+ INDEX(A dbtw053_category_idx) */ /* DBTW-053.1 */ category, descr, count(1) qtde FROM dbtw053 A WHERE category BETWEEN 455 AND 559 GROUP BY category, descr;

CATEGORY DESCR QTDE
---------- ------------------------------------- ----------
460 DBTimeWizard - Performance and Tuning 1000
472 DBTimeWizard - Performance and Tuning 1000
.
.
.
559 DBTimeWizard - Performance and Tuning 1000

105 linhas selecionadas.

SQL>
SQL> -- Recupera o SQL_ID e o CHILD_NUMBER da consulta executada
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 '%DBTW-053.1%'
4 AND sql_text NOT LIKE '%v$sql%';

SQL_ID CHILD_NUMBER
------------- ------------
1vtkt906qhga8 0

1 linha selecionada.

SQL>
SQL> -- Gera o relatorio do plano de execucao da consulta
SQL>
SQL> SELECT * FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'typical iostats last'));

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

SQL_ID 1vtkt906qhga8, child number 0
-------------------------------------
SELECT /*+ INDEX(A dbtw053_category_idx) */ /* DBTW-053.1 */ category,
descr, count(:"SYS_B_0") qtde FROM dbtw053 A WHERE category BETWEEN
:"SYS_B_1" AND :"SYS_B_2" GROUP BY category, descr

Plan hash value: 1559473304

---------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads |
---------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 106K(100)| | 105 |00:00:17.17 | 105K| 1015 |
| 1 | HASH GROUP BY | | 1 | 75 | 3150 | 106K (1)| 00:00:04 | 105 |00:00:17.17 | 105K| 1015 |
|* 2 | FILTER | | 1 | | | | | 105K|00:00:17.15 | 105K| 1015 |
| 3 | TABLE ACCESS BY INDEX ROWID| DBTW053 | 1 | 106K| 4351K| 106K (1)| 00:00:04 | 105K|00:00:17.14 | 105K| 1015 |
|* 4 | INDEX RANGE SCAN | DBTW053_CATEGORY_IDX | 1 | 106K| | 225 (1)| 00:00:01 | 105K|00:00:00.53 | 222 | 212 |
---------------------------------------------------------------------------------------------------------------------------------------------------------

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

2 - filter(:SYS_B_2>=:SYS_B_1)
4 - access("CATEGORY">=:SYS_B_1 AND "CATEGORY"<=:SYS_B_2)

24 linhas selecionadas.

SQL>

4.2) Criar uma SQL_PROFILE manualmente da consulta original

Vamos utilizar o script coe_xfr_sql_profile.sql para gerar um script no diretório “c:\temp” que ao ser executado vai criar uma SQL PROFILE da consulta original executada na primeira etapa.

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

SQL> @coe_xfr_sql_profile 1vtkt906qhga8 1559473304

Parameter 1:
SQL_ID (required)

PLAN_HASH_VALUE AVG_ET_SECS
--------------- -----------
186720033 1,673
1559473304 17,247

Parameter 2:
PLAN_HASH_VALUE (required)

Values passed:
~~~~~~~~~~~~~
SQL_ID : "1vtkt906qhga8"
PLAN_HASH_VALUE: "1559473304"

Execute coe_xfr_sql_profile_1vtkt906qhga8_1559473304.sql
on TARGET system in order to create a custom SQL Profile
with plan 1559473304 linked to adjusted sql_text.

COE_XFR_SQL_PROFILE completed.
SQL>
SQL> -- Executar script gerado no diretório "c:\temp"
SQL>
SQL>@c:\temp\coe_xfr_sql_profile_1vtkt906qhga8_1559473304.sql;
SQL>REM
SQL>REM $Header: 215187.1 coe_xfr_sql_profile_1vtkt906qhga8_1559473304.sql 11.4.1.4 2018/07/05 csierra $
SQL>REM
SQL>REM Copyright (c) 2000-2010, Oracle Corporation. All rights reserved.
SQL>REM
SQL>REM AUTHOR
SQL>REM carlos.sierra@oracle.com
SQL>REM
SQL>REM SCRIPT
SQL>REM coe_xfr_sql_profile_1vtkt906qhga8_1559473304.sql
SQL>REM
SQL>REM DESCRIPTION
SQL>REM This script is generated by coe_xfr_sql_profile.sql
SQL>REM It contains the SQL*Plus commands to create a custom
SQL>REM SQL Profile for SQL_ID 1vtkt906qhga8 based on plan hash
SQL>REM value 1559473304.
SQL>REM The custom SQL Profile to be created by this script
SQL>REM will affect plans for SQL commands with signature
SQL>REM matching the one for SQL Text below.
SQL>REM Review SQL Text and adjust accordingly.
SQL>REM
SQL>REM PARAMETERS
SQL>REM None.
SQL>REM
SQL>REM EXAMPLE
SQL>REM SQL> START coe_xfr_sql_profile_1vtkt906qhga8_1559473304.sql;
SQL>REM
SQL>REM NOTES
SQL>REM 1. Should be run as SYSTEM or SYSDBA.
SQL>REM 2. User must have CREATE ANY SQL PROFILE privilege.
SQL>REM 3. SOURCE and TARGET systems can be the same or similar.
SQL>REM 4. To drop this custom SQL Profile after it has been created:
SQL>REM EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE('coe_1vtkt906qhga8_1559473304');
SQL>REM 5. Be aware that using DBMS_SQLTUNE requires a license
SQL>REM for the Oracle Tuning Pack.
SQL>REM
SQL>WHENEVER SQLERROR EXIT SQL.SQLCODE;
SQL>REM
SQL>VAR signature NUMBER;
SQL>REM
SQL>DECLARE
2 sql_txt CLOB;
3 h SYS.SQLPROF_ATTR;
4 BEGIN
5 sql_txt := q'[
6 SELECT /*+ INDEX(A dbtw053_category_idx) */ /* DBTW-053.1 */ category, descr, count(:"SYS_B_0") qtde FROM dbtw053 A WHERE category BETWEEN :"SYS_B_1" AND :"SYS_B_2" GROUP BY category, descr
7 ]';
8 h := SYS.SQLPROF_ATTR(
9 q'[BEGIN_OUTLINE_DATA]',
10 q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
11 q'[OPTIMIZER_FEATURES_ENABLE('11.2.0.4')]',
12 q'[DB_VERSION('11.2.0.4')]',
13 q'[ALL_ROWS]',
14 q'[OUTLINE_LEAF(@"SEL$1")]',
15 q'[INDEX_RS_ASC(@"SEL$1" "A"@"SEL$1" ("DBTW053"."CATEGORY"))]',
16 q'[USE_HASH_AGGREGATION(@"SEL$1")]',
17 q'[END_OUTLINE_DATA]');
18 :signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt);
19 DBMS_SQLTUNE.IMPORT_SQL_PROFILE (
20 sql_text => sql_txt,
21 profile => h,
22 name => 'coe_1vtkt906qhga8_1559473304',
23 description => 'coe 1vtkt906qhga8 1559473304 '||:signature||'',
24 category => 'DEFAULT',
25 validate => TRUE,
26 replace => TRUE,
27 force_match => FALSE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ );
28 END;
29 /

Procedimento PL/SQL concluído com sucesso.

SQL>WHENEVER SQLERROR CONTINUE
SQL>SET ECHO OFF;

SIGNATURE
---------------------
14783374037725715653

... manual custom SQL Profile has been created

COE_XFR_SQL_PROFILE_1vtkt906qhga8_1559473304 completed
SQL>

SQL>
SQL>set lines 200 pages 100
SQL>select name, created, type, status from DBA_SQL_PROFILES;

NAME CREATED TYPE STATUS
------------------------------ --------------------------------------------------------------------------- ------- ----------
coe_1vtkt906qhga8_1559473304 05/07/18 22:01:20,000000 MANUAL ENABLED

SQL>

4.3) Executar a consulta sem o HINT

Vamos executar a consulta novamente sem o HINT para obtermos as OUTLINES do plano de execução que tem melhor desempenho.

SQL>SELECT /* DBTW-053.2 */ category, descr, count(1) qtde FROM dbtw053 A WHERE category BETWEEN 455 AND 559 GROUP BY category, descr;

CATEGORY DESCR QTDE
---------- ------------------------------------- ----------
460 DBTimeWizard - Performance and Tuning 1000
472 DBTimeWizard - Performance and Tuning 1000
.
.
.
559 DBTimeWizard - Performance and Tuning 1000

105 linhas selecionadas.

SQL>
SQL>-- Recupera o SQL_ID e o CHILD_NUMBER da consulta executada
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 '%DBTW-053.2%'
4 AND sql_text NOT LIKE '%v$sql%';

SQL_ID CHILD_NUMBER
------------- ------------
aa2v8aq8t17w2 0

SQL>
SQL>-- Gera o relatorio do plano de execucao da consulta
SQL>
SQL>SELECT * FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'advanced iostats last'));
antigo 1: SELECT * FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'advanced iostats last'))
novo 1: SELECT * FROM TABLE (dbms_xplan.display_cursor ('aa2v8aq8t17w2', 0,'advanced iostats last'))

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID aa2v8aq8t17w2, child number 0
-------------------------------------
SELECT /* DBTW-053.2 */ category, descr, count(:"SYS_B_0") qtde FROM
dbtw053 A WHERE category BETWEEN :"SYS_B_1" AND :"SYS_B_2" GROUP BY
category, descr

Plan hash value: 186720033

----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads |
----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 297 (100)| | 105 |00:00:10.42 | 8325 | 1747 |
| 1 | HASH GROUP BY | | 1 | 75 | 3150 | 297 (11)| 00:00:01 | 105 |00:00:10.42 | 8325 | 1747 |
|* 2 | FILTER | | 1 | | | | | 105K|00:00:10.40 | 8325 | 1747 |
|* 3 | TABLE ACCESS FULL| DBTW053 | 1 | 106K| 4351K| 290 (9)| 00:00:01 | 105K|00:00:10.39 | 8325 | 1747 |
----------------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

1 - SEL$1
3 - SEL$1 / A@SEL$1

Outline Data
-------------

/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "A"@"SEL$1")
USE_HASH_AGGREGATION(@"SEL$1")
END_OUTLINE_DATA
*/

Peeked Binds (identified by position):
--------------------------------------

2 - :SYS_B_1 (NUMBER): 455
3 - :SYS_B_2 (NUMBER): 559

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

2 - filter(:SYS_B_2>=:SYS_B_1)
3 - filter(("CATEGORY">=:SYS_B_1 AND "CATEGORY"<=:SYS_B_2))

Column Projection Information (identified by operation id):
-----------------------------------------------------------

1 - "CATEGORY"[NUMBER,22], "DESCR"[CHARACTER,37], COUNT(:SYS_B_0)[22]
2 - "CATEGORY"[NUMBER,22], "DESCR"[CHARACTER,37]
3 - "CATEGORY"[NUMBER,22], "DESCR"[CHARACTER,37]

57 linhas selecionadas.

SQL>

4.4) Alterar a OUTLINE da SQL_PROFILE

Para que a SQL_PROFILE gerada na etapa 4.2 desta simulação force a utilização da operação TABLE FULL SCAN no plano de execução precisamos alterar a OUTLINE

DE: INDEX_RS_ASC(@”SEL$1″ “A”@”SEL$1” (“DBTW053″.”CATEGORY”))
PARA: FULL(@”SEL$1″ “A”@”SEL$1”)

Esta alteração será realizada utilizando o script fix_sql_profile_hint.sql e para verificar o resultado da alteração vamos utilizar o script sql_profile_hints11.sql.

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

SQL>conn sys/oracle@lab11 as sysdba
Connected.
SQL>
SQL> @fix_sql_profile_hint
Enter value for profile_name: coe_1vtkt906qhga8_1559473304
Enter value for bad_hint: INDEX_RS_ASC(@"SEL$1" "A"@"SEL$1" ("DBTW053"."CATEGORY"))
Enter value for good_hint: FULL(@"SEL$1" "A"@"SEL$1")

Procedimento PL/SQL concluído com sucesso.

SQL>
SQL> @sql_profile_hints11
Informe o valor para name: coe_1vtkt906qhga8_1559473304

HINT
-----------------------------------------------------------------------------------------------
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "A"@"SEL$1")
USE_HASH_AGGREGATION(@"SEL$1")
END_OUTLINE_DATA

9 linhas selecionadas.

SQL>

4.5) Executar a consulta original novamente

Com a SQL_PROFILE devidamente ajustada vamos executar a consulta original e verificar que plano de execução o Otimizador vai escolher.

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

SQL> ALTER SESSION SET optimizer_use_sql_plan_baselines = FALSE;

Sessão alterada.

SQL>
SQL> ALTER SESSION SET statistics_level = ALL;

Sessão alterada.

SQL>
SQL> SELECT /*+ INDEX(A dbtw053_category_idx) */ /* DBTW-053.1 */ category, descr, count(1) qtde FROM dbtw053 A WHERE category BETWEEN 455 AND 559 GROUP BY category, descr;

CATEGORY DESCR QTDE
---------- ------------------------------------- ----------
460 DBTimeWizard - Performance and Tuning 1000
472 DBTimeWizard - Performance and Tuning 1000
.
.
.
559 DBTimeWizard - Performance and Tuning 1000

105 linhas selecionadas.

SQL>
SQL> -- Recupera o SQL_ID e o CHILD_NUMBER da consulta executada
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 '%DBTW-053.1%'
4 AND sql_text NOT LIKE '%v$sql%';

SQL_ID CHILD_NUMBER
------------- ------------
1vtkt906qhga8 0
1vtkt906qhga8 1

SQL>
SQL>
SQL> -- Gera o relatorio do plano de execucao da consulta
SQL>
SQL> SELECT * FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'typical iostats last'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 1vtkt906qhga8, child number 1
-------------------------------------
SELECT /*+ INDEX(A dbtw053_category_idx) */ /* DBTW-053.1 */ category,
descr, count(:"SYS_B_0") qtde FROM dbtw053 A WHERE category BETWEEN
:"SYS_B_1" AND :"SYS_B_2" GROUP BY category, descr

Plan hash value: 186720033

----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads |
----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 297 (100)| | 105 |00:00:04.26 | 8319 | 8316 |
| 1 | HASH GROUP BY | | 1 | 75 | 3150 | 297 (11)| 00:00:01 | 105 |00:00:04.26 | 8319 | 8316 |
|* 2 | FILTER | | 1 | | | | | 105K|00:00:04.23 | 8319 | 8316 |
|* 3 | TABLE ACCESS FULL| DBTW053 | 1 | 106K| 4351K| 290 (9)| 00:00:01 | 105K|00:00:04.22 | 8319 | 8316 |
----------------------------------------------------------------------------------------------------------------------------------

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

2 - filter(:SYS_B_2>=:SYS_B_1)
3 - filter(("CATEGORY">=:SYS_B_1 AND "CATEGORY"<=:SYS_B_2))

Note
-----
- SQL profile coe_1vtkt906qhga8_1559473304 used for this statement

27 linhas selecionadas.

SQL>

[SQL profile coe_1vtkt906qhga8_1559473304 used for this statement]

Conclusão

Existem algumas opções para modificar o plano de execução sem alterar o código da aplicação, a melhor opção vai depender do tipo de alteração necessária e do licenciamento da base que estivermos trabalhando, no quadro abaixo podemos visualizar facilmente o tipo de licença necessária para cada uma das técnicas apresentadas neste artigo.