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

não alterar codigo

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:

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
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
---------------- ------------
46at379yy19gc               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  46at379yy19gc, child number 0
-------------------------------------
SELECT /* DBTW-053.2 */ 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.19 |    8325 |
|   1 |  HASH GROUP BY     |         |      1 |     75 |  3150 |  2328   (2)| 00:00:28 |    105 |00:00:00.19 |    8325 |
|*  2 |   TABLE ACCESS FULL| DBTW053 |      1 |    106K|  4351K|  2320   (2)| 00:00:28 |    105K|00:00:00.12 |    8325 |
------------------------------------------------------------------------------------------------------------------------

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

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


20 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 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. 

 

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
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

 

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> 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
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 >

 

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.

 

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
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.

 

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
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.

 

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
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>

 

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 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.

 

Licença

 

Referências

 

https://gavinsoorma.com/

http://kerryosborne.oracle-guy.com/

https://carlos-sierra.net/

 
 
 
Promo-D75L

video-02
 
 
 
 
 
 
 
 
 
 
 
 

Deixe um comentário

O seu endereço de e-mail não será publicado.