O processo de Tuning de instruções SQL não é uma tarefa fácil, existem ótimas ferramentas para auxiliar nesse trabalho, aquelas que requerem investimento normalmente não estão a nossa disposição nos ambientes onde trabalhamos. Neste artigo vamos listar 9 ferramentas de uso gratuito que podem ser baixadas e utilizadas sem custo,…
Como modificar um plano de execução sem alterar a instrução SQL?
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
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:
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
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/
