Como modificar um plano de execução sem alterar a instrução SQL?

SQL Profile

Quando estamos realizando um trabalho de tuning SQL muitas vezes nos deparamos com situações em que não podemos alterar o código da aplicação pois esta pertence a terceiros. Essa limitação pode ser superada facilmente utilizando o recurso SQL PROFILE do banco Oracle, disponível a partir da versão 10g. Neste artigo vamos fazer uma demonstração de uma situação hipotética onde vamos utilizar SQL PROFILE para alterar o plano de execução de uma instrução SQL sem alterar o código da instrução.

Descrição da situação hipotética

 

Cenário:

  • O fornecedor da Aplicação pede um prazo de seis meses para fazer qualquer alteração na aplicação.
  • A aplicação é antiga e quando foi escrita o fornecedor inseriu HINTs nas instruções SQL.

Problema:

  • Existe uma consulta com um HINT FULL SCAN em uma tabela que impede o Otimizador de gerar um plano de execução melhor.

 

Solução:

  • Vamos utilizar o recurso de SQL PROFILE para permitir que Otimizador utilize um plano de execução melhor.

 

Simulação da situação hipotética

 

A seguir vamos simular uma instrução SQL que esta sendo executada num banco de dados Oracle com um HINT FULL SCAN, na sequência vamos criar uma SQL PROFILE dessa instrução, alterar a SQL PROFILE dessa instrução e comparar a eficiência dos dois planos de execução.

Criação das tabelas

Primeiro vamos criar 3 tabelas que serão utilizadas na consulta da nossa simulaçã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
SQL> SELECT * FROM V$VERSION where rownum < 2;

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

SQL>
SQL>
SQL> create table dbtw_segments as select * from dba_segments;

Table created.

SQL>
SQL> create index dbtw_seg_name on dbtw_segments(segment_name);

Index created.

SQL>
SQL> exec dbms_stats.gather_table_stats(ownname=> USER, tabname=> 'DBTW_SEGMENTS', cascade=> true, estimate_percent=> null, method_opt=> 'FOR ALL COLUMNS SIZE 254');

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> create table dbtw_objects as select * from dba_objects;

Table created.

SQL>
SQL> create index dbtw_obj_name on dbtw_objects(object_name);

Index created.

SQL>
SQL> create index dbtw_obj_owner on dbtw_objects(owner);

Index created.

SQL>
SQL> exec dbms_stats.gather_table_stats(ownname=> USER, tabname=> 'DBTW_OBJECTS', cascade=> true, estimate_percent=> null, method_opt=> 'FOR ALL COLUMNS SIZE 254');

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> create table dbtw_tables as select * from dba_tables;

Table created.

SQL>
SQL> create index dbtw_tab_name on dbtw_tables(table_name);

Index created.

SQL>
SQL> exec dbms_stats.gather_table_stats(ownname=> USER, tabname=> 'DBTW_TABLES', cascade=> true, estimate_percent=> null, method_opt=> 'FOR ALL COLUMNS SIZE 254');

PL/SQL procedure successfully completed.

SQL>

 

Execução da consulta

Agora vamos executar a consulta com o HINT FULL SCAN e verificar o plano de execução gerado pelo Otimizador.

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
SQL> ALTER SESSION SET statistics_level=ALL;

Session altered.

SQL>
SQL>
SQL> select /*+ FULL(O) */ t.table_name, t.TABLESPACE_NAME, O.CREATED, round(S.bytes/1024) KB
  2    from dbtw_objects O,
  3         dbtw_tables T,
  4         dbtw_segments S
  5   where o.object_name = t.table_name
  6     and t.table_name = s.segment_name
  7     and s.segment_name = o.object_name
  8     and o.owner = 'SCOTT'
  9     and s.segment_type = 'TABLE';

TABLE_NAME                     TABLESPACE_NAME                CREATED           KB
------------------------------ ------------------------------ --------- ----------
DEPT                           USERS                          25-AUG-13         64
EMP                            USERS                          25-AUG-13         64
EMP                            USERS                          25-AUG-13         64
EMP                            USERS                          25-AUG-13         64
EMP                            USERS                          25-AUG-13         64
SALGRADE                       USERS                          25-AUG-13         64

6 rows selected.

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 '%FULL%'
  4     AND sql_text LIKE '%dbtw_objects%'
  5     AND sql_text NOT LIKE '%v$sql%';

SQL_ID        CHILD_NUMBER
------------- ------------
4phc2ztdt60y6            0

SQL>
SQL>
SQL> SELECT *
  2    FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'advanced iostats last'));
old   2:   FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'advanced iostats last'))
new   2:   FROM TABLE (dbms_xplan.display_cursor ('4phc2ztdt60y6',         0,'advanced iostats last'))

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

SQL_ID  4phc2ztdt60y6, child number 0
-------------------------------------
select /*+ FULL(O) */ t.table_name, t.TABLESPACE_NAME, O.CREATED,
round(S.bytes/1024) KB   from dbtw_objects O,        dbtw_tables T,
   dbtw_segments S  where o.object_name = t.table_name    and
t.table_name = s.segment_name    and s.segment_name = o.object_name
and o.owner = 'SCOTT'    and s.segment_type = 'TABLE'

Plan hash value: 2684391254

---------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name          | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |
---------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |               |      1 |        |       |   355 (100)|          |      6 |00:00:00.01 |    1269 |   1239 |
|   1 |  NESTED LOOPS                  |               |      1 |      1 |   101 |   355   (1)| 00:00:05 |      6 |00:00:00.01 |    1269 |   1239 |
|   2 |   NESTED LOOPS                 |               |      1 |      1 |   101 |   355   (1)| 00:00:05 |      6 |00:00:00.01 |    1263 |   1239 |
|   3 |    NESTED LOOPS                |               |      1 |      1 |    74 |   353   (1)| 00:00:05 |      4 |00:00:00.01 |    1256 |   1239 |
|*  4 |     TABLE ACCESS FULL          | DBTW_OBJECTS  |      1 |      6 |   234 |   346   (1)| 00:00:05 |      6 |00:00:00.01 |    1242 |   1239 |
|*  5 |     TABLE ACCESS BY INDEX ROWID| DBTW_SEGMENTS |      6 |      1 |    35 |     2   (0)| 00:00:01 |      4 |00:00:00.01 |      14 |      0 |
|*  6 |      INDEX RANGE SCAN          | DBTW_SEG_NAME |      6 |      1 |       |     1   (0)| 00:00:01 |      6 |00:00:00.01 |      10 |      0 |
|*  7 |    INDEX RANGE SCAN            | DBTW_TAB_NAME |      4 |      1 |       |     1   (0)| 00:00:01 |      6 |00:00:00.01 |       7 |      0 |
|   8 |   TABLE ACCESS BY INDEX ROWID  | DBTW_TABLES   |      6 |      1 |    27 |     2   (0)| 00:00:01 |      6 |00:00:00.01 |       6 |      0 |
---------------------------------------------------------------------------------------------------------------------------------------------------

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

   1 - SEL$1
   4 - SEL$1 / O@SEL$1
   5 - SEL$1 / S@SEL$1
   6 - SEL$1 / S@SEL$1
   7 - SEL$1 / T@SEL$1
   8 - SEL$1 / T@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" "O"@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "S"@"SEL$1" ("DBTW_SEGMENTS"."SEGMENT_NAME"))
      INDEX(@"SEL$1" "T"@"SEL$1" ("DBTW_TABLES"."TABLE_NAME"))
      LEADING(@"SEL$1" "O"@"SEL$1" "S"@"SEL$1" "T"@"SEL$1")
      USE_NL(@"SEL$1" "S"@"SEL$1")
      USE_NL(@"SEL$1" "T"@"SEL$1")
      NLJ_BATCHING(@"SEL$1" "T"@"SEL$1")
      END_OUTLINE_DATA
  */


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

   4 - filter("O"."OWNER"='SCOTT')
   5 - filter("S"."SEGMENT_TYPE"='TABLE')
   6 - access("S"."SEGMENT_NAME"="O"."OBJECT_NAME")
   7 - access("T"."TABLE_NAME"="S"."SEGMENT_NAME")
       filter("O"."OBJECT_NAME"="T"."TABLE_NAME")

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

   1 - "O"."CREATED"[DATE,7], "S"."BYTES"[NUMBER,22], "T"."TABLE_NAME"[VARCHAR2,30], "T"."TABLESPACE_NAME"[VARCHAR2,30]
   2 - "O"."CREATED"[DATE,7], "S"."BYTES"[NUMBER,22], "T".ROWID[ROWID,10], "T"."TABLE_NAME"[VARCHAR2,30]
   3 - "O"."OBJECT_NAME"[VARCHAR2,128], "O"."CREATED"[DATE,7], "S"."SEGMENT_NAME"[VARCHAR2,81], "S"."BYTES"[NUMBER,22]
   4 - "O"."OBJECT_NAME"[VARCHAR2,128], "O"."CREATED"[DATE,7]
   5 - "S"."SEGMENT_NAME"[VARCHAR2,81], "S"."BYTES"[NUMBER,22]
   6 - "S".ROWID[ROWID,10], "S"."SEGMENT_NAME"[VARCHAR2,81]
   7 - "T".ROWID[ROWID,10], "T"."TABLE_NAME"[VARCHAR2,30]
   8 - "T"."TABLESPACE_NAME"[VARCHAR2,30]


75 rows selected.

SQL>

 

Podemos observar que o plano de execução gerado pelo Otimizador utiliza 3 operações NESTED LOOPS e faz um FULL TABLE SCAN na tabela DBTW_OBJECTS, é nesse momento que o plano de execução fica oneroso pois esta operação realiza 1242 leituras de Buffers de um total de 1269 Buffers lidos por todo o plano de execução.

Criação da SQL PROFILE manualmente

Vamos utilizar o script “coe_xfr_sql_profile.sql” para gerar um script no diretório “c:\temp”, ao executar esse script será criada uma SQL PROFILE para a consulta acima:

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
SQL> @coe_xfr_sql_profile 4phc2ztdt60y6 2684391254

Parameter 1:
SQL_ID (required)



PLAN_HASH_VALUE AVG_ET_SECS
--------------- -----------
     2684391254        .028

Parameter 2:
PLAN_HASH_VALUE (required)


Values passed:
~~~~~~~~~~~~~
SQL_ID         : "4phc2ztdt60y6"
PLAN_HASH_VALUE: "2684391254"


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


COE_XFR_SQL_PROFILE completed.
SQL>
SQL>
SQL>
SQL>@c:\temp\coe_xfr_sql_profile_4phc2ztdt60y6_2684391254.sql;
SQL>REM
SQL>REM $Header: 215187.1 coe_xfr_sql_profile_4phc2ztdt60y6_2684391254.sql 11.4.1.4 2016/04/24 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_4phc2ztdt60y6_2684391254.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 4phc2ztdt60y6 based on plan hash
SQL>REM   value 2684391254.
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_4phc2ztdt60y6_2684391254.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_4phc2ztdt60y6_2684391254');
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 /*+ FULL(O) */ t.table_name, t.TABLESPACE_NAME, O.CREATED, round(S.bytes/1024) KB
  7  from dbtw_objects O,
  8  dbtw_tables T,
  9  dbtw_segments S
 10  where o.object_name = t.table_name
 11  and t.table_name = s.segment_name
 12  and s.segment_name = o.object_name
 13  and o.owner = '
SCOTT'
 14  and s.segment_type = '
TABLE'
 15  ]'
;
 16  h := SYS.SQLPROF_ATTR(
 17  q'[BEGIN_OUTLINE_DATA]',
 18  q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
 19  q'[OPTIMIZER_FEATURES_ENABLE('11.2.0.4')]',
 20  q'[DB_VERSION('11.2.0.4')]',
 21  q'[ALL_ROWS]',
 22  q'[OUTLINE_LEAF(@"SEL$1")]',
 23  q'[FULL(@"SEL$1" "O"@"SEL$1")]',
 24  q'[INDEX_RS_ASC(@"SEL$1" "S"@"SEL$1" ("DBTW_SEGMENTS"."SEGMENT_NAME"))]',
 25  q'[INDEX(@"SEL$1" "T"@"SEL$1" ("DBTW_TABLES"."TABLE_NAME"))]',
 26  q'[LEADING(@"SEL$1" "O"@"SEL$1" "S"@"SEL$1" "T"@"SEL$1")]',
 27  q'[USE_NL(@"SEL$1" "S"@"SEL$1")]',
 28  q'[USE_NL(@"SEL$1" "T"@"SEL$1")]',
 29  q'[NLJ_BATCHING(@"SEL$1" "T"@"SEL$1")]',
 30  q'[END_OUTLINE_DATA]');
 31  :signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt);
 32  DBMS_SQLTUNE.IMPORT_SQL_PROFILE (
 33  sql_text    => sql_txt,
 34  profile     => h,
 35  name        => 'coe_4phc2ztdt60y6_2684391254',
 36  description => 'coe 4phc2ztdt60y6 2684391254 '||:signature||'',
 37  category    => 'DEFAULT',
 38  validate    => TRUE,
 39  replace     => TRUE,
 40  force_match => FALSE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ );
 41  END;
 42  /

PL/SQL procedure successfully completed.

SQL>WHENEVER SQLERROR CONTINUE
SQL>SET ECHO OFF;

            SIGNATURE
---------------------
  9130450230344665996


... manual custom SQL Profile has been created


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

NAME                           CREATED                                                                     TYPE    STATUS
------------------------------ --------------------------------------------------------------------------- ------- --------
coe_4phc2ztdt60y6_2684391254   24-APR-16 04.25.42.000000 PM                                                MANUAL  ENABLED

SQL>

 

Execução da consulta sem HINT FULL SCAN

Vamos executar a mesma consulta retirando o HINT FULL SCAN e verificar a qualidade do plano de execução gerado:

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
SQL>ALTER SESSION SET statistics_level=ALL;

Session altered.

SQL>
SQL>
SQL>select /* dbtw101 */ t.table_name, t.TABLESPACE_NAME, O.CREATED, round(S.bytes/1024) KB
  2    from dbtw_objects O,
  3         dbtw_tables T,
  4         dbtw_segments S
  5   where o.object_name = t.table_name
  6     and t.table_name = s.segment_name
  7     and s.segment_name = o.object_name
  8     and o.owner = 'SCOTT'
  9     and s.segment_type = 'TABLE';

TABLE_NAME                     TABLESPACE_NAME                CREATED           KB
------------------------------ ------------------------------ --------- ----------
DEPT                           USERS                          25-AUG-13         64
EMP                            USERS                          25-AUG-13         64
EMP                            USERS                          25-AUG-13         64
EMP                            USERS                          25-AUG-13         64
EMP                            USERS                          25-AUG-13         64
SALGRADE                       USERS                          25-AUG-13         64

6 rows selected.

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 '%dbtw101%'
  4     AND sql_text NOT LIKE '%v$sql%';

SQL_ID        CHILD_NUMBER
------------- ------------
491h6ndhvqucm            0

SQL>
SQL>
SQL>SELECT *
  2    FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'advanced iostats last'));
old   2:   FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'advanced iostats last'))
new   2:   FROM TABLE (dbms_xplan.display_cursor ('491h6ndhvqucm',         0,'advanced iostats last'))

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

SQL_ID  491h6ndhvqucm, child number 0
-------------------------------------
select /* dbtw101 */ t.table_name, t.TABLESPACE_NAME, O.CREATED,
round(S.bytes/1024) KB   from dbtw_objects O,        dbtw_tables T,
   dbtw_segments S  where o.object_name = t.table_name    and
t.table_name = s.segment_name    and s.segment_name = o.object_name
and o.owner = 'SCOTT'    and s.segment_type = 'TABLE'

Plan hash value: 1886115670

----------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name           | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |
----------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                |      1 |        |       |    11 (100)|          |      6 |00:00:00.01 |      32 |      1 |
|   1 |  NESTED LOOPS                  |                |      1 |      1 |   101 |    11   (0)| 00:00:01 |      6 |00:00:00.01 |      32 |      1 |
|   2 |   NESTED LOOPS                 |                |      1 |      1 |   101 |    11   (0)| 00:00:01 |      6 |00:00:00.01 |      26 |      1 |
|   3 |    NESTED LOOPS                |                |      1 |      1 |    74 |     9   (0)| 00:00:01 |      4 |00:00:00.01 |      19 |      1 |
|   4 |     TABLE ACCESS BY INDEX ROWID| DBTW_OBJECTS   |      1 |      6 |   234 |     2   (0)| 00:00:01 |      6 |00:00:00.01 |       5 |      1 |
|*  5 |      INDEX RANGE SCAN          | DBTW_OBJ_OWNER |      1 |      6 |       |     1   (0)| 00:00:01 |      6 |00:00:00.01 |       3 |      0 |
|*  6 |     TABLE ACCESS BY INDEX ROWID| DBTW_SEGMENTS  |      6 |      1 |    35 |     2   (0)| 00:00:01 |      4 |00:00:00.01 |      14 |      0 |
|*  7 |      INDEX RANGE SCAN          | DBTW_SEG_NAME  |      6 |      1 |       |     1   (0)| 00:00:01 |      6 |00:00:00.01 |      10 |      0 |
|*  8 |    INDEX RANGE SCAN            | DBTW_TAB_NAME  |      4 |      1 |       |     1   (0)| 00:00:01 |      6 |00:00:00.01 |       7 |      0 |
|   9 |   TABLE ACCESS BY INDEX ROWID  | DBTW_TABLES    |      6 |      1 |    27 |     2   (0)| 00:00:01 |      6 |00:00:00.01 |       6 |      0 |
----------------------------------------------------------------------------------------------------------------------------------------------------

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

   1 - SEL$1
   4 - SEL$1 / O@SEL$1
   5 - SEL$1 / O@SEL$1
   6 - SEL$1 / S@SEL$1
   7 - SEL$1 / S@SEL$1
   8 - SEL$1 / T@SEL$1
   9 - SEL$1 / T@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")
      INDEX_RS_ASC(@"SEL$1" "O"@"SEL$1" ("DBTW_OBJECTS"."OWNER"))
      INDEX_RS_ASC(@"SEL$1" "S"@"SEL$1" ("DBTW_SEGMENTS"."SEGMENT_NAME"))
      INDEX(@"SEL$1" "T"@"SEL$1" ("DBTW_TABLES"."TABLE_NAME"))
      LEADING(@"SEL$1" "O"@"SEL$1" "S"@"SEL$1" "T"@"SEL$1")
      USE_NL(@"SEL$1" "S"@"SEL$1")
      USE_NL(@"SEL$1" "T"@"SEL$1")
      NLJ_BATCHING(@"SEL$1" "T"@"SEL$1")
      END_OUTLINE_DATA
  */


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

   5 - access("O"."OWNER"='SCOTT')
   6 - filter("S"."SEGMENT_TYPE"='TABLE')
   7 - access("S"."SEGMENT_NAME"="O"."OBJECT_NAME")
   8 - access("T"."TABLE_NAME"="S"."SEGMENT_NAME")
       filter("O"."OBJECT_NAME"="T"."TABLE_NAME")

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

   1 - "O"."CREATED"[DATE,7], "S"."BYTES"[NUMBER,22], "T"."TABLE_NAME"[VARCHAR2,30], "T"."TABLESPACE_NAME"[VARCHAR2,30]
   2 - "O"."CREATED"[DATE,7], "S"."BYTES"[NUMBER,22], "T".ROWID[ROWID,10], "T"."TABLE_NAME"[VARCHAR2,30]
   3 - "O"."OBJECT_NAME"[VARCHAR2,128], "O"."CREATED"[DATE,7], "S"."SEGMENT_NAME"[VARCHAR2,81], "S"."BYTES"[NUMBER,22]
   4 - "O"."OBJECT_NAME"[VARCHAR2,128], "O"."CREATED"[DATE,7]
   5 - "O".ROWID[ROWID,10]
   6 - "S"."SEGMENT_NAME"[VARCHAR2,81], "S"."BYTES"[NUMBER,22]
   7 - "S".ROWID[ROWID,10], "S"."SEGMENT_NAME"[VARCHAR2,81]
   8 - "T".ROWID[ROWID,10], "T"."TABLE_NAME"[VARCHAR2,30]
   9 - "T"."TABLESPACE_NAME"[VARCHAR2,30]


78 rows selected.

SQL>

 

No plano de execução acima podemos observar que o Otimizador passou a utilizar o índice DBTW_OBJ_OWNER para acessar a tabela DBTW_OBJECTS, essa alteração tornou o plano de execução mais eficiente, na primeira execução da consulta o plano de execução leu 1269 Buffers, agora o plano de execução leu somente 32 Buffers.

Alteração da SQL PROFILE

Conseguimos identificar um plano de execução mais eficiente para a nossa consulta, o grande desafio agora é alterar a SQL PROFILE que criamos para que ela reproduza o plano de execução acima.

Para garantir que o Otimizador despreze o HINT da instrução SQL precisamos alterar a SQL PROFILE criada ou seja vamos substituir a OUTLINE:

FULL(@”SEL$1″ “O”@”SEL$1”)

por

INDEX_RS_ASC(@”SEL$1″ “O”@”SEL$1” (“DBTW_OBJECTS”.”OWNER”))

Para isso vamos utilizar o script “fix_sql_profile_hint.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
SQL>conn sys/oracle@lab11 as sysdba;
Conectado.
SQL>@fix_sql_profile_hint
Enter value for profile_name: coe_4phc2ztdt60y6_2684391254
Enter value for bad_hint: FULL(@"SEL$1" "O"@"SEL$1")
Enter value for good_hint: INDEX_RS_ASC(@"SEL$1" "O"@"SEL$1" ("DBTW_OBJECTS"."OWNER"))
antigo  15:    'select replace(attr_val,''&&bad_hint'',''&&good_hint'') as outline_hints '||
novo  15:    'select replace(attr_val,''FULL(@"SEL$1" "O"@"SEL$1")'',''INDEX_RS_ASC(@"SEL$1" "O"@"SEL$1" ("DBTW_OBJECTS"."OWNER"))'') as outline_hints '||
antigo  18:    'and name like (''&&profile_name'') '||
novo  18:    'and name like (''coe_4phc2ztdt60y6_2684391254'') '||
antigo  27:    'select replace(hint,''&&bad_hint'',''&&good_hint'') as outline_hints '||
novo  27:    'select replace(hint,''FULL(@"SEL$1" "O"@"SEL$1")'',''INDEX_RS_ASC(@"SEL$1" "O"@"SEL$1" ("DBTW_OBJECTS"."OWNER"))'') as outline_hints '||
antigo  36:    'and p.name like (''&&profile_name'')) '||
novo  36:    'and p.name like (''coe_4phc2ztdt60y6_2684391254'')) '||
antigo  49: where name like ('&&profile_name');
novo  49: where name like ('coe_4phc2ztdt60y6_2684391254');
antigo  60: , name => '&&profile_name'
novo  60: , name => 'coe_4phc2ztdt60y6_2684391254'

Procedimento PL/SQL concluído com sucesso.

SQL>@sql_profile_hints11
Informe o valor para name: coe_4phc2ztdt60y6_2684391254
antigo  11: and p.name like nvl('&name',name)
novo  11: and p.name like nvl('coe_4phc2ztdt60y6_2684391254',name)

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")
INDEX_RS_ASC(@"SEL$1" "O"@"SEL$1" ("DBTW_OBJECTS"."OWNER"))
INDEX_RS_ASC(@"SEL$1" "S"@"SEL$1" ("DBTW_SEGMENTS"."SEGMENT_NAME"))
INDEX(@"SEL$1" "T"@"SEL$1" ("DBTW_TABLES"."TABLE_NAME"))
LEADING(@"SEL$1" "O"@"SEL$1" "S"@"SEL$1" "T"@"SEL$1")
USE_NL(@"SEL$1" "S"@"SEL$1")
USE_NL(@"SEL$1" "T"@"SEL$1")
NLJ_BATCHING(@"SEL$1" "T"@"SEL$1")
END_OUTLINE_DATA

14 linhas selecionadas.

SQL>

 

Execução da consulta novamente

Criamos manualmente a SQL Profile da consulta com o HINT FULL SCAN, alteramos a SQL Profile para que ela despreze o HINT e utilize um índice para acessar a tabela, agora vamos executar a consulta novamente e verificar se a SQL Profile vai ser utilizada.

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
SQL>ALTER SYSTEM FLUSH SHARED_POOL;

System altered.

SQL>
SQL>ALTER SESSION SET statistics_level=ALL;

Session altered.

SQL>
SQL>
SQL>select /*+ FULL(O) */ t.table_name, t.TABLESPACE_NAME, O.CREATED, round(S.bytes/1024) KB
  2    from dbtw_objects O,
  3         dbtw_tables T,
  4         dbtw_segments S
  5   where o.object_name = t.table_name
  6     and t.table_name = s.segment_name
  7     and s.segment_name = o.object_name
  8     and o.owner = 'SCOTT'
  9     and s.segment_type = 'TABLE';

TABLE_NAME                     TABLESPACE_NAME                CREATED           KB
------------------------------ ------------------------------ --------- ----------
DEPT                           USERS                          25-AUG-13         64
EMP                            USERS                          25-AUG-13         64
EMP                            USERS                          25-AUG-13         64
EMP                            USERS                          25-AUG-13         64
EMP                            USERS                          25-AUG-13         64
SALGRADE                       USERS                          25-AUG-13         64

6 rows selected.

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 '%FULL%'
  4     AND sql_text LIKE '%dbtw_objects%'
  5     AND sql_text NOT LIKE '%v$sql%';

SQL_ID        CHILD_NUMBER
------------- ------------
4phc2ztdt60y6            0

SQL>
SQL>
SQL>SELECT *
  2    FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'advanced iostats last'));
old   2:   FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'advanced iostats last'))
new   2:   FROM TABLE (dbms_xplan.display_cursor ('4phc2ztdt60y6',         0,'advanced iostats last'))

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

SQL_ID  4phc2ztdt60y6, child number 0
-------------------------------------
select /*+ FULL(O) */ t.table_name, t.TABLESPACE_NAME, O.CREATED,
round(S.bytes/1024) KB   from dbtw_objects O,        dbtw_tables T,
   dbtw_segments S  where o.object_name = t.table_name    and
t.table_name = s.segment_name    and s.segment_name = o.object_name
and o.owner = 'SCOTT'    and s.segment_type = 'TABLE'

Plan hash value: 1886115670

-------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name           | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                |      1 |        |       |    11 (100)|          |      6 |00:00:00.01 |      32 |
|   1 |  NESTED LOOPS                  |                |      1 |      1 |   101 |    11   (0)| 00:00:01 |      6 |00:00:00.01 |      32 |
|   2 |   NESTED LOOPS                 |                |      1 |      1 |   101 |    11   (0)| 00:00:01 |      6 |00:00:00.01 |      26 |
|   3 |    NESTED LOOPS                |                |      1 |      1 |    74 |     9   (0)| 00:00:01 |      4 |00:00:00.01 |      19 |
|   4 |     TABLE ACCESS BY INDEX ROWID| DBTW_OBJECTS   |      1 |      6 |   234 |     2   (0)| 00:00:01 |      6 |00:00:00.01 |       5 |
|*  5 |      INDEX RANGE SCAN          | DBTW_OBJ_OWNER |      1 |      6 |       |     1   (0)| 00:00:01 |      6 |00:00:00.01 |       3 |
|*  6 |     TABLE ACCESS BY INDEX ROWID| DBTW_SEGMENTS  |      6 |      1 |    35 |     2   (0)| 00:00:01 |      4 |00:00:00.01 |      14 |
|*  7 |      INDEX RANGE SCAN          | DBTW_SEG_NAME  |      6 |      1 |       |     1   (0)| 00:00:01 |      6 |00:00:00.01 |      10 |
|*  8 |    INDEX RANGE SCAN            | DBTW_TAB_NAME  |      4 |      1 |       |     1   (0)| 00:00:01 |      6 |00:00:00.01 |       7 |
|   9 |   TABLE ACCESS BY INDEX ROWID  | DBTW_TABLES    |      6 |      1 |    27 |     2   (0)| 00:00:01 |      6 |00:00:00.01 |       6 |
-------------------------------------------------------------------------------------------------------------------------------------------

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

   1 - SEL$1
   4 - SEL$1 / O@SEL$1
   5 - SEL$1 / O@SEL$1
   6 - SEL$1 / S@SEL$1
   7 - SEL$1 / S@SEL$1
   8 - SEL$1 / T@SEL$1
   9 - SEL$1 / T@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")
      INDEX_RS_ASC(@"SEL$1" "O"@"SEL$1" ("DBTW_OBJECTS"."OWNER"))
      INDEX_RS_ASC(@"SEL$1" "S"@"SEL$1" ("DBTW_SEGMENTS"."SEGMENT_NAME"))
      INDEX(@"SEL$1" "T"@"SEL$1" ("DBTW_TABLES"."TABLE_NAME"))
      LEADING(@"SEL$1" "O"@"SEL$1" "S"@"SEL$1" "T"@"SEL$1")
      USE_NL(@"SEL$1" "S"@"SEL$1")
      USE_NL(@"SEL$1" "T"@"SEL$1")
      NLJ_BATCHING(@"SEL$1" "T"@"SEL$1")
      END_OUTLINE_DATA
  */


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

   5 - access("O"."OWNER"='SCOTT')
   6 - filter("S"."SEGMENT_TYPE"='TABLE')
   7 - access("S"."SEGMENT_NAME"="O"."OBJECT_NAME")
   8 - access("T"."TABLE_NAME"="S"."SEGMENT_NAME")
       filter("O"."OBJECT_NAME"="T"."TABLE_NAME")

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

   1 - "O"."CREATED"[DATE,7], "S"."BYTES"[NUMBER,22], "T"."TABLE_NAME"[VARCHAR2,30], "T"."TABLESPACE_NAME"[VARCHAR2,30]
   2 - "O"."CREATED"[DATE,7], "S"."BYTES"[NUMBER,22], "T".ROWID[ROWID,10], "T"."TABLE_NAME"[VARCHAR2,30]
   3 - "O"."OBJECT_NAME"[VARCHAR2,128], "O"."CREATED"[DATE,7], "S"."SEGMENT_NAME"[VARCHAR2,81], "S"."BYTES"[NUMBER,22]
   4 - "O"."OBJECT_NAME"[VARCHAR2,128], "O"."CREATED"[DATE,7]
   5 - "O".ROWID[ROWID,10]
   6 - "S"."SEGMENT_NAME"[VARCHAR2,81], "S"."BYTES"[NUMBER,22]
   7 - "S".ROWID[ROWID,10], "S"."SEGMENT_NAME"[VARCHAR2,81]
   8 - "T".ROWID[ROWID,10], "T"."TABLE_NAME"[VARCHAR2,30]
   9 - "T"."TABLESPACE_NAME"[VARCHAR2,30]

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


82 rows selected.

SQL>

 

Podemos verificar que a consulta utilizou a SQL PROFILE, na linha 137 da log acima na seção NOTE temos um comentário que indica isso e o Otimizador ignorou o HINT de FULL SCAN na tabela DBTW_OBJECTS, pois ele utilizou as OUTLINE DATA da SQL PROFILE para montar o plano de execução que utilizou o índice para acessar as linhas da tabela.

Conclusão

A utilização do recurso SQL Profile no processo de Tuning abre um leque de possibilidades, esse recurso agiliza o processo de Tuning pois não necessita de alteração no código da aplicação para ajudar o Otimizador a montar planos de execução mais eficientes.

Recomendamos que esse processo de Tuning seja aplicado primeiro num ambiente de Homologação antes de aplicar em Produção.

 

Scripts

coe_xfr_sql_profile

fix_sql_profile_hint

sql_profile_hints11

 

Referências

http://kerryosborne.oracle-guy.com/2009/07/why-isnt-oracle-using-my-outline-profile-baseline/

https://carlos-sierra.net/2012/04/09/custom-sql-profile-and-plan-stability-on-10g/

 

 

Mídia social

 

 

 

Deixe uma resposta

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