{"id":1690,"date":"2018-07-11T22:27:11","date_gmt":"2018-07-12T01:27:11","guid":{"rendered":"http:\/\/dbtimewizard.com.br\/blog\/?p=1690"},"modified":"2022-10-21T21:17:54","modified_gmt":"2022-10-22T00:17:54","slug":"4-tecnicas-para-modificar-um-plano-de-execucao-sem-alterar-a-instrucao-sql","status":"publish","type":"post","link":"http:\/\/dbtimewizard.com.br\/blog\/4-tecnicas-para-modificar-um-plano-de-execucao-sem-alterar-a-instrucao-sql\/","title":{"rendered":"4 t\u00e9cnicas para modificar um plano de execu\u00e7\u00e3o sem alterar a instru\u00e7\u00e3o SQL"},"content":{"rendered":"

Para melhorar de desempenho das aplica\u00e7\u00f5es muitas vezes precisamos fazer alguns ajustes nas instru\u00e7\u00f5es SQL e em algumas situa\u00e7\u00f5es n\u00e3o podemos alterar o c\u00f3digo pois a aplica\u00e7\u00e3o \u00e9 de terceiros ou apesar de ser um desenvolvimento interno da empresa os prazos para altera\u00e7\u00e3o s\u00e3o muito longos. J\u00e1 faz algum tempo que modificar um plano de execu\u00e7\u00e3o sem alterar a instru\u00e7\u00e3o SQL deixou de ser um grande obst\u00e1culo, neste artigo vamos apresentar 4 t\u00e9cnicas diferentes que podemos utilizar para isto e mostrar em qual tipo de licen\u00e7a do Oracle Database 11gR2 elas podem ser aplicadas<\/span>.<\/p>\n

1) Advanced Query Rewrite<\/span><\/h1>\n

A primeira e a mais antiga destas t\u00e9cnicas \u00e9 a Advanced Query Rewrite, esta op\u00e7\u00e3o pode ser utilizada em todas as licen\u00e7as do 11gR2 inclusive na Express Edition. Para exemplificar a sua aplica\u00e7\u00e3o vamos fazer a simula\u00e7\u00e3o com uma consulta que ser\u00e1 utilizada em todas as simula\u00e7\u00f5es das 4 t\u00e9cnicas abordadas neste artigo. Esta consulta foi criada para demonstra\u00e7\u00e3o das 4 t\u00e9cnicas e n\u00e3o representa as melhores pr\u00e1ticas na cria\u00e7\u00e3o de uma instru\u00e7\u00e3o SQL.<\/span><\/p>\n

Nesta simula\u00e7\u00e3o vamos:<\/span><\/p>\n

1.1) Criar um usu\u00e1rio com os privil\u00e9gios necess\u00e1rios<\/span>
\n1.2) Criar a tabela e \u00edndice da consulta<\/span>
\n1.3) Executar a consulta original da aplica\u00e7\u00e3o<\/span>
\n1.4) Executar a consulta com altera\u00e7\u00e3o para melhoria do desempenho<\/span>
\n1.5) Utilizar Query Rewrite para criar a equival\u00eancia entre as consultas<\/span>
\n1.6) Executar a consulta original ap\u00f3s a cria\u00e7\u00e3o da equival\u00eancia<\/span><\/p>\n

1.1) Criar um usu\u00e1rio com os privil\u00e9gios<\/span><\/h2>\n

Para criar o usu\u00e1rio CURSO02 com os privil\u00e9gios necess\u00e1rios para utilizar a op\u00e7\u00e3o Advanced Query Rewrite precisamos conectar no banco de dados com usu\u00e1rio SYS.<\/span><\/p>\n

 <\/p>\n

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
<\/div><\/td>
SQL><\/span> SELECT *<\/span> FROM V$VERSION<\/span> where rownum <<\/span> 2<\/span>;<\/span>
\n
\nBANNER
\n--------------------------------------------------------------------------------<\/span>
\nOracle Database 11g Express Edition Release 11.<\/span>2.<\/span>0.<\/span>2.<\/span>0 -<\/span> 64bit Production
\n
\nSQL><\/span> CREATE USER curso02 identified by curso02 default<\/span> tablespace users temporary tablespace temp;<\/span>
\n
\nUser created.<\/span>
\n
\nSQL><\/span> alter user curso02 quota unlimited on users;<\/span>
\n
\nUser altered.<\/span>
\n
\nSQL><\/span> grant connect to curso02;<\/span>
\n
\nGrant succeeded.<\/span>
\n
\nSQL><\/span> grant resource to curso02;<\/span>
\n
\nGrant succeeded.<\/span>
\n
\nSQL><\/span> grant query rewrite to curso02;<\/span>
\n
\nGrant succeeded.<\/span>
\n
\nSQL><\/span> GRANT CREATE MATERIALIZED VIEW TO curso02;<\/span>
\n
\nGrant succeeded.<\/span>
\n
\nSQL><\/span> grant execute on sys.<\/span>DBMS_ADVANCED_REWRITE to curso02;<\/span>
\n
\nGrant succeeded.<\/span>
\n
\nSQL><\/span> grant select any dictionary to curso02;<\/span>
\n
\nGrant succeeded.<\/span>
\n
\nSQL><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

1.2) Criar a tabela e \u00edndice da consulta<\/span><\/h2>\n

 <\/p>\n

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
<\/div><\/td>
SQL ><\/span> create table dbtw053 (<\/span>matr ,<\/span> category,<\/span> created,<\/span> descr)<\/span>
\n  2<\/span>      as<\/span>
\n  3<\/span>      select rownum,<\/span> mod(<\/span>rownum,<\/span> 1000<\/span>)<\/span>,<\/span> sysdate-<\/span>mod(<\/span>rownum,<\/span> 2000<\/span>)<\/span>,<\/span> 'DBTimeWizard - Performance and Tuning'<\/span> from dual connect by level <=<\/span> 1000000<\/span>;<\/span>
\n
\nTabela criada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> create index dbtw053_category_idx on dbtw053(<\/span>category)<\/span>;<\/span>
\n
\n\u00cdndice criado.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> exec<\/span><\/a> dbms_stats.<\/span>gather_table_stats(<\/span>ownname=><\/span> USER,<\/span> tabname=><\/span> 'DBTW053'<\/span>,<\/span> method_opt=><\/span>'for all columns size auto'<\/span>,<\/span>cascade=><\/span>true<\/span>,<\/span>force=><\/span>true<\/span>,<\/span>degree=><\/span>16<\/span>)<\/span>;<\/span>
\n
\nProcedimento PL\/<\/span>SQL conclu\u00eddo<\/span> com sucesso.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

1.3) Executar a consulta original da aplica\u00e7\u00e3o<\/span><\/h2>\n

A consulta que supostamente extraimos da aplica\u00e7\u00e3o possui um HINT que for\u00e7a o Otimizador a utilizar o \u00edndice DBTW053_CATEGORY_IDX.<\/span><\/p>\n

 <\/p>\n

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
<\/div><\/td>
SQL ><\/span> ALTER SESSION SET statistics_level =<\/span> ALL;<\/span>
\n
\nSess\u00e3o alterada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> SELECT \/*+ INDEX(A dbtw053_category_idx) *\/<\/span> \/* DBTW-053.1 *\/<\/span> category,<\/span> descr,<\/span> count<\/span><\/a>(<\/span>1<\/span>)<\/span> qtde FROM dbtw053 A WHERE category BETWEEN 455<\/span> AND 559<\/span> GROUP BY category,<\/span> descr;<\/span>
\n
\n  CATEGORY DESCR                                       QTDE
\n----------<\/span> -------------------------------------<\/span> ----------<\/span>
\n       460<\/span> DBTimeWizard -<\/span> Performance and Tuning       1000<\/span>
\n       472<\/span> DBTimeWizard -<\/span> Performance and Tuning       1000<\/span>
\n  .<\/span>
\n  .<\/span>
\n  .<\/span>
\n       559<\/span> DBTimeWizard -<\/span> Performance and Tuning       1000<\/span>
\n
\n105<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> --<\/span> Recupera o SQL_ID e o CHILD_NUMBER  da consulta executada
\nSQL ><\/span>
\nSQL ><\/span> column sql_id new_value m_sql_id
\nSQL ><\/span> column child_number new_value m_child_no
\nSQL ><\/span>
\nSQL ><\/span> SELECT sql_id,<\/span> child_number
\n  2<\/span>    FROM v$sql<\/span>
\n  3<\/span>   WHERE sql_text LIKE '%DBTW-053.1%'<\/span>
\n  4<\/span>     AND sql_text NOT LIKE '%v$sql%'<\/span>;<\/span>
\n
\nSQL_ID           CHILD_NUMBER
\n----------------<\/span> ------------<\/span>
\n669gr4xtnvqa1               0<\/span>
\n
\n1<\/span> linha selecionada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> --<\/span> Gera o relatorio do<\/span> plano de execucao da consulta
\nSQL ><\/span>
\nSQL ><\/span> SELECT *<\/span> FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'&m_sql_id'<\/span>,&<\/span>m_child_no,<\/span>'typical iostats last'<\/span>)<\/span>)<\/span>;<\/span>
\n
\nPLAN_TABLE_OUTPUT
\n--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\nSQL_ID  669gr4xtnvqa1,<\/span> child number 0<\/span>
\n-------------------------------------<\/span>
\nSELECT \/*+ INDEX(A dbtw053_category_idx) *\/<\/span> \/* DBTW-053.1 *\/<\/span> category,<\/span>
\ndescr,<\/span> count<\/span><\/a>(<\/span>1<\/span>)<\/span> qtde FROM dbtw053 A WHERE category BETWEEN 455<\/span> AND 559<\/span>
\nGROUP BY category,<\/span> descr
\n
\nPlan hash<\/span><\/a> value:<\/span> 1755005131<\/span>
\n
\n-----------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\n|<\/span> Id  |<\/span> Operation                    |<\/span> Name                 |<\/span> Starts |<\/span> E-<\/span>Rows |<\/span>E-<\/span>Bytes|<\/span> Cost (<\/span>%<\/span>CPU)<\/span>|<\/span> E-<\/span>Time<\/span><\/a>   |<\/span> A-<\/span>Rows |<\/span>   A-<\/span>Time<\/span><\/a>   |<\/span> Buffers |<\/span>
\n-----------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\n|<\/span>   0<\/span> |<\/span> SELECT STATEMENT             |<\/span>                      |<\/span>      1<\/span> |<\/span>        |<\/span>       |<\/span>   106K(<\/span>100<\/span>)<\/span>|<\/span>          |<\/span>    105<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.67<\/span> |<\/span>     105K|<\/span>
\n|<\/span>   1<\/span> |<\/span>  HASH<\/span><\/a> GROUP BY               |<\/span>                      |<\/span>      1<\/span> |<\/span>     75<\/span> |<\/span>  3150<\/span> |<\/span>   106K  (<\/span>1<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>21<\/span>:<\/span>17<\/span> |<\/span>    105<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.67<\/span> |<\/span>     105K|<\/span>
\n|<\/span>   2<\/span> |<\/span>   TABLE ACCESS BY INDEX ROWID|<\/span> DBTW053              |<\/span>      1<\/span> |<\/span>    106K|<\/span>  4351K|<\/span>   106K  (<\/span>1<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>21<\/span>:<\/span>17<\/span> |<\/span>    105K|<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.59<\/span> |<\/span>     105K|<\/span>
\n|*<\/span>  3<\/span> |<\/span>    INDEX RANGE<\/span><\/a> SCAN          |<\/span> DBTW053_CATEGORY_IDX |<\/span>      1<\/span> |<\/span>    106K|<\/span>       |<\/span>   225<\/span>   (<\/span>1<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>03<\/span> |<\/span>    105K|<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.08<\/span> |<\/span>     222<\/span> |<\/span>
\n-----------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\n
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
\n---------------------------------------------------<\/span>
\n
\n   3<\/span> -<\/span> access(<\/span>"CATEGORY"<\/span>>=<\/span>455<\/span> AND "CATEGORY"<\/span><=<\/span>559<\/span>)<\/span>
\n
\n
\n22<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

No plano de execu\u00e7\u00e3o acima podemos observar que foram lidas 105 mil linhas na tabela utilizando os ROWID selecionados no \u00edndice e o tempo total gasto para executar a consulta foi 67 cent\u00e9simos de segundo.<\/span><\/p>\n

1.4) Executar a consulta com altera\u00e7\u00e3o<\/span><\/h2>\n

O Otimizador do banco Oracle evolui muito e na vers\u00e3o 11gR2 disponibiliza planos de execu\u00e7\u00e3o muito eficientes quando as estat\u00edsticas do banco s\u00e3o mantidas utilizando as melhores pr\u00e1ticas. A inclus\u00e3o de HINT na consulta deve ser feita em situa\u00e7\u00f5es muito especiais, no nosso exemplo vamos excluir o HINT e deixar que o Otimizador decida qual \u00e9 a melhor op\u00e7\u00e3o para montagem do plano de execu\u00e7\u00e3o.<\/span><\/p>\n

 <\/p>\n

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
<\/div><\/td>
SQL ><\/span> SELECT \/* DBTW-053.2 *\/<\/span> category,<\/span> descr,<\/span> count<\/span><\/a>(<\/span>1<\/span>)<\/span> qtde FROM dbtw053 A WHERE category BETWEEN 455<\/span> AND 559<\/span> GROUP BY category,<\/span> descr;<\/span>
\n
\n  CATEGORY DESCR                                       QTDE
\n----------<\/span> -------------------------------------<\/span> ----------<\/span>
\n       460<\/span> DBTimeWizard -<\/span> Performance and Tuning       1000<\/span>
\n       472<\/span> DBTimeWizard -<\/span> Performance and Tuning       1000<\/span>
\n  .<\/span>
\n  .<\/span>
\n  .<\/span>
\n       559<\/span> DBTimeWizard -<\/span> Performance and Tuning       1000<\/span>
\n
\n105<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> --<\/span> Recupera o SQL_ID e o CHILD_NUMBER  da consulta executada
\nSQL ><\/span>
\nSQL ><\/span> column sql_id new_value m_sql_id
\nSQL ><\/span> column child_number new_value m_child_no
\nSQL ><\/span>
\nSQL ><\/span> SELECT sql_id,<\/span> child_number
\n  2<\/span>    FROM v$sql<\/span>
\n  3<\/span>   WHERE sql_text LIKE '%DBTW-053.2%'<\/span>
\n  4<\/span>     AND sql_text NOT LIKE '%v$sql%'<\/span>;<\/span>
\n
\nSQL_ID           CHILD_NUMBER
\n----------------<\/span> ------------<\/span>
\n46at379yy19gc               0<\/span>
\n
\n1<\/span> linha selecionada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> --<\/span> Gera o relatorio do<\/span> plano de execucao da consulta
\nSQL ><\/span>
\nSQL ><\/span> SELECT *<\/span> FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'&m_sql_id'<\/span>,&<\/span>m_child_no,<\/span>'typical iostats last'<\/span>)<\/span>)<\/span>;<\/span>
\n
\nPLAN_TABLE_OUTPUT
\n----------------------------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\nSQL_ID  46at379yy19gc,<\/span> child number 0<\/span>
\n-------------------------------------<\/span>
\nSELECT \/* DBTW-053.2 *\/<\/span> category,<\/span> descr,<\/span> count<\/span><\/a>(<\/span>1<\/span>)<\/span> qtde FROM dbtw053 A
\nWHERE category BETWEEN 455<\/span> AND 559<\/span> GROUP BY category,<\/span> descr
\n
\nPlan hash<\/span><\/a> value:<\/span> 3756543841<\/span>
\n
\n------------------------------------------------------------------------------------------------------------------------<\/span>
\n|<\/span> Id  |<\/span> Operation          |<\/span> Name    |<\/span> Starts |<\/span> E-<\/span>Rows |<\/span>E-<\/span>Bytes|<\/span> Cost (<\/span>%<\/span>CPU)<\/span>|<\/span> E-<\/span>Time<\/span><\/a>   |<\/span> A-<\/span>Rows |<\/span>   A-<\/span>Time<\/span><\/a>   |<\/span> Buffers |<\/span>
\n------------------------------------------------------------------------------------------------------------------------<\/span>
\n|<\/span>   0<\/span> |<\/span> SELECT STATEMENT   |<\/span>         |<\/span>      1<\/span> |<\/span>        |<\/span>       |<\/span>  2328<\/span> (<\/span>100<\/span>)<\/span>|<\/span>          |<\/span>    105<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.19<\/span> |<\/span>    8325<\/span> |<\/span>
\n|<\/span>   1<\/span> |<\/span>  HASH<\/span><\/a> GROUP BY     |<\/span>         |<\/span>      1<\/span> |<\/span>     75<\/span> |<\/span>  3150<\/span> |<\/span>  2328<\/span>   (<\/span>2<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>28<\/span> |<\/span>    105<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.19<\/span> |<\/span>    8325<\/span> |<\/span>
\n|*<\/span>  2<\/span> |<\/span>   TABLE ACCESS FULL|<\/span> DBTW053 |<\/span>      1<\/span> |<\/span>    106K|<\/span>  4351K|<\/span>  2320<\/span>   (<\/span>2<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>28<\/span> |<\/span>    105K|<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.12<\/span> |<\/span>    8325<\/span> |<\/span>
\n------------------------------------------------------------------------------------------------------------------------<\/span>
\n
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
\n---------------------------------------------------<\/span>
\n
\n   2<\/span> -<\/span> filter(<\/span>(<\/span>"CATEGORY"<\/span>>=<\/span>455<\/span> AND "CATEGORY"<\/span><=<\/span>559<\/span>)<\/span>)<\/span>
\n
\n
\n20<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Analisando o plano de execu\u00e7\u00e3o verificamos que o Otimizador escolheu fazer uma opera\u00e7\u00e3o TABLE FULL SCAN e o tempo de execu\u00e7\u00e3o caiu de 67 cent\u00e9simos de segunda para 19 cent\u00e9simos, um redu\u00e7\u00e3o de 71% no tempo de execu\u00e7\u00e3o.<\/span><\/p>\n

1.5) Utilizar Query Rewrite para criar a equival\u00eancia<\/span><\/h2>\n

Agora que sabemos que o HINT inclu\u00eddo na consulta esta prejudicando o seu desempenho, vamos utilizar o pacote DBMS_ADVANCED_REWRITE para gerar uma equival\u00eancia entre as duas consultas, ou seja, toda vez que a aplica\u00e7\u00e3o enviar ao banco a consulta com HINT o Otimizador vai executar a consulta sem o HINT.<\/span>
\nO pacote DBMS_ADVANCED_REWRITE exige que as consultas para as quais esta sendo gerada a equival\u00eancia sejam diferentes e n\u00e3o considera diferentes as consultas cuja diferen\u00e7a esteja somente no HINT, pois este ultimo \u00e9 considerado como um coment\u00e1rio. Por esta raz\u00e3o no nosso exemplo alem de excluir o HINT na consulta melhorada, adicionamos 0 ao campo CATEGORY que \u00e9 um campo num\u00e9rico e esta adi\u00e7\u00e3o n\u00e3o vai representar altera\u00e7\u00e3o no seu valor final.<\/span><\/p>\n

 <\/p>\n

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
<\/div><\/td>
SQL ><\/span> BEGIN
\n  2<\/span>     sys.<\/span>DBMS_ADVANCED_REWRITE.<\/span>DECLARE_REWRITE_EQUIVALENCE (<\/span>
\n  3<\/span>         name              =><\/span> 'rewrite_dbtw053'<\/span>,<\/span>
\n  4<\/span>         source_stmt       =><\/span> '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'<\/span>,<\/span>
\n  5<\/span>         destination_stmt  =><\/span> 'SELECT \/* DBTW-053.2 *\/ category, descr, count(1) qtde FROM dbtw053 A WHERE category+0 BETWEEN 455 AND 559 GROUP BY category, descr'<\/span>,<\/span>
\n  6<\/span>         validate          =><\/span> FALSE<\/span>,<\/span>
\n  7<\/span>         rewrite_mode      =><\/span> 'TEXT_MATCH'<\/span>)<\/span>;<\/span>
\n  8<\/span>  END<\/span><\/a>;<\/span>
\n  9<\/span>  \/<\/span>
\n
\nProcedimento PL\/<\/span>SQL conclu\u00eddo<\/span> com sucesso.<\/span>
\n
\nSQL ><\/span> set long 9999<\/span>
\nSQL ><\/span> select *<\/span> from DBA_REWRITE_EQUIVALENCES;<\/span>
\n
\nNAME                           SOURCE_STMT                                                                      DESTINATION_STMT
\n------------------------------<\/span> --------------------------------------------------------------------------------<\/span> --------------------------------------------------------------------------------<\/span>
\nREWRITE_MO
\n----------<\/span>
\nREWRITE_DBTW053                SELECT \/*+ INDEX(A dbtw053_category_idx) *\/<\/span> \/* DBTW-053.1 *\/<\/span> category,<\/span> descr,<\/span> co SELECT \/* DBTW-053.2 *\/<\/span> category,<\/span> descr,<\/span> count<\/span><\/a>(<\/span>1<\/span>)<\/span> qtde FROM dbtw053 A WHERE cate
\n                               unt(<\/span>1<\/span>)<\/span> qtde FROM dbtw053 A WHERE category BETWEEN 455<\/span> AND 559<\/span> GROUP BY category,<\/span> gory+<\/span>0<\/span> BETWEEN 455<\/span> AND 559<\/span> GROUP BY category,<\/span> descr
\n                                descr
\nTEXT_MATCH
\n
\n
\n
\n1<\/span> linha selecionada.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

1.6) Executar a consulta original ap\u00f3s a cria\u00e7\u00e3o da equival\u00eancia<\/span><\/h2>\n

Para maior clareza na analise da execu\u00e7\u00e3o da consulta ap\u00f3s a cria\u00e7\u00e3o da equival\u00eancia vamos limpar a SHARED POOL e para que a Advanced Query Rewrite funcione vamos alterar dois par\u00e2metros do banco a n\u00edvel de sess\u00e3o: QUERY_REWRITE_INTEGRITY e QUERY_REWRITE_ENABLED.\u00a0<\/span><\/p>\n

 <\/p>\n

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
<\/div><\/td>
SQL><\/span> --<\/span> Conectado com usuario SYS limpar a SHARED POOL
\nSQL><\/span> ALTER SYSTEM<\/span><\/a> flush<\/span><\/a> shared_pool;<\/span>
\n
\nSystem<\/span><\/a> altered.<\/span>
\n
\nSQL><\/span>
\nSQL ><\/span> --<\/span> De volta para a sess\u00e3o do<\/span> usuario CURSO02,<\/span> alterar na sess\u00e3o os parametros de QUERY_REWRITE
\nSQL ><\/span>
\nSQL ><\/span> ALTER SESSION SET statistics_level =<\/span> ALL;<\/span>
\n
\nSess\u00e3o alterada.<\/span>
\n
\nSQL ><\/span> ALTER SESSION SET query_rewrite_integrity =<\/span> TRUSTED;<\/span>
\n
\nSess\u00e3o alterada.<\/span>
\n
\nSQL ><\/span> ALTER SESSION SET query_rewrite_enabled =<\/span> TRUE<\/span>;<\/span>
\n
\nSess\u00e3o alterada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> SELECT \/*+ INDEX(A dbtw053_category_idx) *\/<\/span> \/* DBTW-053.1 *\/<\/span> category,<\/span> descr,<\/span> count<\/span><\/a>(<\/span>1<\/span>)<\/span> qtde FROM dbtw053 A WHERE category BETWEEN 455<\/span> AND 559<\/span> GROUP BY category,<\/span> de
\n
\n  CATEGORY DESCR                                       QTDE
\n----------<\/span> -------------------------------------<\/span> ----------<\/span>
\n       460<\/span> DBTimeWizard -<\/span> Performance and Tuning       1000<\/span>
\n       472<\/span> DBTimeWizard -<\/span> Performance and Tuning       1000<\/span>
\n  .<\/span>
\n  .<\/span>
\n  .<\/span>
\n       559<\/span> DBTimeWizard -<\/span> Performance and Tuning       1000<\/span>
\n
\n105<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> --<\/span> Recupera o SQL_ID e o CHILD_NUMBER  da consulta executada
\nSQL ><\/span>
\nSQL ><\/span> column sql_id new_value m_sql_id
\nSQL ><\/span> column child_number new_value m_child_no
\nSQL ><\/span>
\nSQL ><\/span> SELECT sql_id,<\/span> child_number
\n  2<\/span>    FROM v$sql<\/span>
\n  3<\/span>   WHERE sql_text LIKE '%DBTW-053.1%'<\/span>
\n  4<\/span>     AND sql_text NOT LIKE '%v$sql%'<\/span>;<\/span>
\n
\nSQL_ID           CHILD_NUMBER
\n----------------<\/span> ------------<\/span>
\n669gr4xtnvqa1               0<\/span>
\n
\n1<\/span> linha selecionada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> --<\/span> Gera o relatorio do<\/span> plano de execucao da consulta
\nSQL ><\/span>
\nSQL ><\/span> SELECT *<\/span> FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'&m_sql_id'<\/span>,&<\/span>m_child_no,<\/span>'typical iostats last'<\/span>)<\/span>)<\/span>;<\/span>
\n
\nPLAN_TABLE_OUTPUT
\n-------------------------------------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\nSQL_ID  669gr4xtnvqa1,<\/span> child number 0<\/span>
\n-------------------------------------<\/span>
\nSELECT \/*+ INDEX(A dbtw053_category_idx) *\/<\/span> \/* DBTW-053.1 *\/<\/span> category,<\/span>
\ndescr,<\/span> count<\/span><\/a>(<\/span>1<\/span>)<\/span> qtde FROM dbtw053 A WHERE category BETWEEN 455<\/span> AND 559<\/span>
\nGROUP BY category,<\/span> descr
\n
\nPlan hash<\/span><\/a> value:<\/span> 3756543841<\/span>
\n
\n------------------------------------------------------------------------------------------------------------------------<\/span>
\n|<\/span> Id  |<\/span> Operation          |<\/span> Name    |<\/span> Starts |<\/span> E-<\/span>Rows |<\/span>E-<\/span>Bytes|<\/span> Cost (<\/span>%<\/span>CPU)<\/span>|<\/span> E-<\/span>Time<\/span><\/a>   |<\/span> A-<\/span>Rows |<\/span>   A-<\/span>Time<\/span><\/a>   |<\/span> Buffers |<\/span>
\n------------------------------------------------------------------------------------------------------------------------<\/span>
\n|<\/span>   0<\/span> |<\/span> SELECT STATEMENT   |<\/span>         |<\/span>      1<\/span> |<\/span>        |<\/span>       |<\/span>  2335<\/span> (<\/span>100<\/span>)<\/span>|<\/span>          |<\/span>    105<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.25<\/span> |<\/span>    8328<\/span> |<\/span>
\n|<\/span>   1<\/span> |<\/span>  HASH<\/span><\/a> GROUP BY     |<\/span>         |<\/span>      1<\/span> |<\/span>     75<\/span> |<\/span>  3150<\/span> |<\/span>  2335<\/span>   (<\/span>2<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>29<\/span> |<\/span>    105<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.25<\/span> |<\/span>    8328<\/span> |<\/span>
\n|*<\/span>  2<\/span> |<\/span>   TABLE ACCESS FULL|<\/span> DBTW053 |<\/span>      1<\/span> |<\/span>    106K|<\/span>  4351K|<\/span>  2327<\/span>   (<\/span>2<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>28<\/span> |<\/span>    105K|<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.18<\/span> |<\/span>    8328<\/span> |<\/span>
\n------------------------------------------------------------------------------------------------------------------------<\/span>
\n
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
\n---------------------------------------------------<\/span>
\n
\n   2<\/span> -<\/span> filter(<\/span>(<\/span>"CATEGORY"<\/span>+<\/span>0<\/span>>=<\/span>455<\/span> AND "CATEGORY"<\/span>+<\/span>0<\/span><=<\/span>559<\/span>)<\/span>)<\/span>
\n
\n
\n21<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

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

2) SQL PATCH<\/span><\/h1>\n

A segunda t\u00e9cnica \u00e9 o SQL PATCH, esta op\u00e7\u00e3o tamb\u00e9m pode ser utilizada em todas as licen\u00e7as do 11gR2 inclusive na Express Edition. Para exemplificar a sua aplica\u00e7\u00e3o vamos fazer a simula\u00e7\u00e3o utilizando a mesma consulta e na mesma base Oracle Express Edition onde j\u00e1 criamos o usu\u00e1rio CURSO02.<\/span><\/p>\n

OBS: A equival\u00eancia criada na demonstra\u00e7\u00e3o anterior foi exclu\u00edda para n\u00e3o interferir nesta simula\u00e7\u00e3o.<\/span><\/p>\n

Nesta simula\u00e7\u00e3o vamos:<\/span><\/p>\n

2.1) Executar a consulta original da aplica\u00e7\u00e3o<\/span>
\n2.2) Criar um SQL PATCH para excluir o HINT da consulta<\/span>
\n2.3) Executar a consulta original novamente<\/span>
\n <\/p>\n

2.1) Executar a consulta da aplica\u00e7\u00e3o<\/span><\/h2>\n

 <\/p>\n

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
<\/div><\/td>
SQL ><\/span> SELECT *<\/span> FROM V$VERSION<\/span> where rownum <<\/span> 2<\/span>;<\/span>
\n
\nBANNER
\n--------------------------------------------------------------------------------<\/span>
\nOracle Database 11g Express Edition Release 11.<\/span>2.<\/span>0.<\/span>2.<\/span>0 -<\/span> 64bit Production
\n
\n1<\/span> linha selecionada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> ALTER SESSION SET statistics_level =<\/span> ALL;<\/span>
\n
\nSess\u00e3o alterada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> SELECT \/*+ INDEX(A dbtw053_category_idx) *\/<\/span> \/* DBTW-053.1 *\/<\/span> category,<\/span> descr,<\/span> count<\/span><\/a>(<\/span>1<\/span>)<\/span> qtde FROM dbtw053 A WHERE category BETWEEN 455<\/span> AND 559<\/span> GROUP BY category,<\/span> descr;<\/span>
\n
\n  CATEGORY DESCR                                       QTDE
\n----------<\/span> -------------------------------------<\/span> ----------<\/span>
\n       460<\/span> DBTimeWizard -<\/span> Performance and Tuning       1000<\/span>
\n       472<\/span> DBTimeWizard -<\/span> Performance and Tuning       1000<\/span>
\n  .<\/span>
\n  .<\/span>
\n  .<\/span>
\n       559<\/span> DBTimeWizard -<\/span> Performance and Tuning       1000<\/span>
\n
\n105<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> --<\/span> Recupera o SQL_ID e o CHILD_NUMBER  da consulta executada
\nSQL ><\/span>
\nSQL ><\/span> column sql_id new_value m_sql_id
\nSQL ><\/span> column child_number new_value m_child_no
\nSQL ><\/span>
\nSQL ><\/span> SELECT sql_id,<\/span> child_number
\n  2<\/span>    FROM v$sql<\/span>
\n  3<\/span>   WHERE sql_text LIKE '%DBTW-053.1%'<\/span>
\n  4<\/span>     AND sql_text NOT LIKE '%v$sql%'<\/span>;<\/span>
\n
\nSQL_ID           CHILD_NUMBER
\n----------------<\/span> ------------<\/span>
\n669gr4xtnvqa1               0<\/span>
\n
\n1<\/span> linha selecionada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> --<\/span> Gera o relatorio do<\/span> plano de execucao da consulta
\nSQL ><\/span>
\nSQL ><\/span> SELECT *<\/span> FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'&m_sql_id'<\/span>,&<\/span>m_child_no,<\/span>'typical iostats last'<\/span>)<\/span>)<\/span>;<\/span>
\n
\nPLAN_TABLE_OUTPUT
\n--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\nSQL_ID  669gr4xtnvqa1,<\/span> child number 0<\/span>
\n-------------------------------------<\/span>
\nSELECT \/*+ INDEX(A dbtw053_category_idx) *\/<\/span> \/* DBTW-053.1 *\/<\/span> category,<\/span>
\ndescr,<\/span> count<\/span><\/a>(<\/span>1<\/span>)<\/span> qtde FROM dbtw053 A WHERE category BETWEEN 455<\/span> AND 559<\/span>
\nGROUP BY category,<\/span> descr
\n
\nPlan hash<\/span><\/a> value:<\/span> 1755005131<\/span>
\n
\n-----------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\n|<\/span> Id  |<\/span> Operation                    |<\/span> Name                 |<\/span> Starts |<\/span> E-<\/span>Rows |<\/span>E-<\/span>Bytes|<\/span> Cost (<\/span>%<\/span>CPU)<\/span>|<\/span> E-<\/span>Time<\/span><\/a>   |<\/span> A-<\/span>Rows |<\/span>   A-<\/span>Time<\/span><\/a>   |<\/span> Buffers |<\/span>
\n-----------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\n|<\/span>   0<\/span> |<\/span> SELECT STATEMENT             |<\/span>                      |<\/span>      1<\/span> |<\/span>        |<\/span>       |<\/span>   106K(<\/span>100<\/span>)<\/span>|<\/span>          |<\/span>    105<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.67<\/span> |<\/span>     105K|<\/span>
\n|<\/span>   1<\/span> |<\/span>  HASH<\/span><\/a> GROUP BY               |<\/span>                      |<\/span>      1<\/span> |<\/span>     75<\/span> |<\/span>  3150<\/span> |<\/span>   106K  (<\/span>1<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>21<\/span>:<\/span>17<\/span> |<\/span>    105<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.67<\/span> |<\/span>     105K|<\/span>
\n|<\/span>   2<\/span> |<\/span>   TABLE ACCESS BY INDEX ROWID|<\/span> DBTW053              |<\/span>      1<\/span> |<\/span>    106K|<\/span>  4351K|<\/span>   106K  (<\/span>1<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>21<\/span>:<\/span>17<\/span> |<\/span>    105K|<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.59<\/span> |<\/span>     105K|<\/span>
\n|*<\/span>  3<\/span> |<\/span>    INDEX RANGE<\/span><\/a> SCAN          |<\/span> DBTW053_CATEGORY_IDX |<\/span>      1<\/span> |<\/span>    106K|<\/span>       |<\/span>   225<\/span>   (<\/span>1<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>03<\/span> |<\/span>    105K|<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.08<\/span> |<\/span>     222<\/span> |<\/span>
\n-----------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\n
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
\n---------------------------------------------------<\/span>
\n
\n   3<\/span> -<\/span> access(<\/span>"CATEGORY"<\/span>>=<\/span>455<\/span> AND "CATEGORY"<\/span><=<\/span>559<\/span>)<\/span>
\n
\n
\n22<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

No resultado da consulta podemos observar que foram lidas 105 mil linhas na tabela utilizando os ROWID selecionados no \u00edndice e o tempo total gasto para executar a consulta foi 67 cent\u00e9simos de segundo.\u00a0<\/span><\/p>\n

2.2) Criar um SQL PATCH para excluir o HINT da consulta<\/span><\/h2>\n

Antes de criarmos o SQL PATCH precisamos conectar com usu\u00e1rio SYS para atribuir o privil\u00e9gio EXECUTE nos pacotes: DBMS_SQLDIAG_INTERNAL e DBMS_SQLDIAG ao usu\u00e1rio CURSO02.<\/span><\/p>\n

 <\/p>\n

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
<\/div><\/td>
SQL><\/span> --<\/span> Conectar com usu\u00e1rio SYS para atribuir permiss\u00e3o de EXECUTE nos pacotes DBMS_SQLDIAG_INTERNAL e DBMS_SQLDIAG
\nSQL><\/span>
\nSQL><\/span> grant execute on DBMS_SQLDIAG_INTERNAL to curso02;<\/span>
\n
\nGrant succeeded.<\/span>
\n
\nSQL><\/span> grant execute on DBMS_SQLDIAG to curso02;<\/span>
\n
\nGrant succeeded.<\/span>
\n
\nSQL><\/span>
\nSQL><\/span> --<\/span> Conectar novamente com usu\u00e1rio CURSO02
\nSQL><\/span>
\nSQL><\/span> --<\/span> Recupera o SQL_ID e o CHILD_NUMBER  da consulta executada
\nSQL><\/span>
\nSQL><\/span> column sql_id new_value m_sql_id
\nSQL><\/span> column child_number new_value m_child_no
\nSQL><\/span>
\nSQL><\/span> SELECT sql_id,<\/span> child_number
\n  2<\/span>    FROM v$sql<\/span>
\n  3<\/span>   WHERE sql_text LIKE '%DBTW-053.1%'<\/span>
\n  4<\/span>     AND sql_text NOT LIKE '%v$sql%'<\/span>;<\/span>
\n
\nSQL_ID           CHILD_NUMBER
\n----------------<\/span> ------------<\/span>
\n669gr4xtnvqa1               0<\/span>
\n
\n1<\/span> linha selecionada.<\/span>
\n
\nSQL><\/span>
\nSQL><\/span> set serveroutput on size 9999<\/span>
\nSQL><\/span> declare<\/span>
\n  2<\/span>      m_clob  clob;<\/span>
\n  3<\/span>  begin
\n  4<\/span>      select sql_fulltext into m_clob
\n  5<\/span>        from v$sql<\/span>
\n  6<\/span>       where sql_id =<\/span> '&m_sql_id'<\/span>
\n  7<\/span>         and child_number =<\/span> &<\/span>m_child_no ;<\/span>
\n  8<\/span>
\n  9<\/span>      sys.<\/span>dbms_sqldiag_internal.<\/span>i_create_patch(<\/span>
\n 10<\/span>          sql_text    =><\/span> m_clob,<\/span>
\n 11<\/span>          hint_text   =><\/span> 'IGNORE_OPTIM_EMBEDDED_HINTS'<\/span>,<\/span>
\n 12<\/span>          name        =><\/span> 'Patch_&m_sql_id'<\/span>
\n 13<\/span>          )<\/span>;<\/span>
\n 14<\/span>  end<\/span><\/a>;<\/span>
\n 15<\/span>  \/<\/span>
\n
\nProcedimento PL\/<\/span>SQL conclu\u00eddo<\/span> com sucesso.<\/span>
\n
\nSQL><\/span>
\nSQL><\/span> set lines 200<\/span>
\nSQL><\/span> select NAME,<\/span> CREATED,<\/span>  SQL_TEXT from DBA_SQL_PATCHES;<\/span>
\n
\nNAME                           CREATED              SQL_TEXT
\n------------------------------<\/span> --------------------<\/span> --------------------------------------------------------------------------------<\/span>
\nPatch_669gr4xtnvqa1            04<\/span>\/<\/span>07<\/span>\/<\/span>18<\/span> 19<\/span>:<\/span>12<\/span>:<\/span>37<\/span>,<\/span>00<\/span> SELECT \/*+ INDEX(A dbtw053_category_idx) *\/<\/span> \/* DBTW-053.1 *\/<\/span> category,<\/span> descr,<\/span> co
\n                               0000<\/span>                 unt(<\/span>1<\/span>)<\/span> qtde FROM dbtw053 A WHERE category BETWEEN 455<\/span> AND 559<\/span> GROUP BY category,<\/span>
\n                                                     descr
\n
\n
\n1<\/span> linha selecionada.<\/span>
\n
\nSQL><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Observe que foi criado um SQL PATCH<\/span> para a consulta original onde foi inclu\u00eddo o HINT<\/span> IGNORE_OPTIM_EMBEDDED_HINTS<\/span> que for\u00e7ar\u00e1 o Otimizador a ignorar qualquer HINT<\/span> existente na consulta.<\/span><\/p>\n

2.3) Executar a consulta original novamente<\/span><\/h2>\n

 <\/p>\n

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
<\/div><\/td>
SQL><\/span> ALTER SESSION SET statistics_level =<\/span> ALL;<\/span>
\n
\nSess\u00e3o alterada.<\/span>
\n
\nSQL><\/span>
\nSQL><\/span> SELECT \/*+ INDEX(A dbtw053_category_idx) *\/<\/span> \/* DBTW-053.1 *\/<\/span> category,<\/span> descr,<\/span> count<\/span><\/a>(<\/span>1<\/span>)<\/span> qtde FROM dbtw053 A WHERE category BETWEEN 455<\/span> AND 559<\/span> GROUP BY category,<\/span> descr;<\/span>
\n
\n  CATEGORY DESCR                                       QTDE
\n----------<\/span> -------------------------------------<\/span> ----------<\/span>
\n       460<\/span> DBTimeWizard -<\/span> Performance and Tuning       1000<\/span>
\n       472<\/span> DBTimeWizard -<\/span> Performance and Tuning       1000<\/span>
\n  .<\/span>
\n  .<\/span>
\n  .<\/span>
\n       559<\/span> DBTimeWizard -<\/span> Performance and Tuning       1000<\/span>
\n
\n105<\/span> linhas selecionadas.<\/span>
\n
\nSQL><\/span>
\nSQL><\/span> --<\/span> Recupera o SQL_ID e o CHILD_NUMBER  da consulta executada
\nSQL><\/span>
\nSQL><\/span> column sql_id new_value m_sql_id
\nSQL><\/span> column child_number new_value m_child_no
\nSQL><\/span>
\nSQL><\/span> SELECT sql_id,<\/span> child_number
\n  2<\/span>    FROM v$sql<\/span>
\n  3<\/span>   WHERE sql_text LIKE '%DBTW-053.1%'<\/span>
\n  4<\/span>     AND sql_text NOT LIKE '%v$sql%'<\/span>;<\/span>
\n
\nSQL_ID           CHILD_NUMBER
\n----------------<\/span> ------------<\/span>
\n669gr4xtnvqa1               0<\/span>
\n
\n1<\/span> linha selecionada.<\/span>
\n
\nSQL><\/span>
\nSQL><\/span>
\nSQL><\/span> --<\/span> Gera o relatorio do<\/span> plano de execucao da consulta
\nSQL><\/span>
\nSQL><\/span> SELECT *<\/span> FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'&m_sql_id'<\/span>,&<\/span>m_child_no,<\/span>'typical iostats last'<\/span>)<\/span>)<\/span>;<\/span>
\n
\nPLAN_TABLE_OUTPUT
\n----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\nSQL_ID  669gr4xtnvqa1,<\/span> child number 0<\/span>
\n-------------------------------------<\/span>
\nSELECT \/*+ INDEX(A dbtw053_category_idx) *\/<\/span> \/* DBTW-053.1 *\/<\/span> category,<\/span>
\ndescr,<\/span> count<\/span><\/a>(<\/span>1<\/span>)<\/span> qtde FROM dbtw053 A WHERE category BETWEEN 455<\/span> AND 559<\/span>
\nGROUP BY category,<\/span> descr
\n
\nPlan hash<\/span><\/a> value:<\/span> 3756543841<\/span>
\n
\n------------------------------------------------------------------------------------------------------------------------<\/span>
\n|<\/span> Id  |<\/span> Operation          |<\/span> Name    |<\/span> Starts |<\/span> E-<\/span>Rows |<\/span>E-<\/span>Bytes|<\/span> Cost (<\/span>%<\/span>CPU)<\/span>|<\/span> E-<\/span>Time<\/span><\/a>   |<\/span> A-<\/span>Rows |<\/span>   A-<\/span>Time<\/span><\/a>   |<\/span> Buffers |<\/span>
\n------------------------------------------------------------------------------------------------------------------------<\/span>
\n|<\/span>   0<\/span> |<\/span> SELECT STATEMENT   |<\/span>         |<\/span>      1<\/span> |<\/span>        |<\/span>       |<\/span>  2328<\/span> (<\/span>100<\/span>)<\/span>|<\/span>          |<\/span>    105<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.18<\/span> |<\/span>    8325<\/span> |<\/span>
\n|<\/span>   1<\/span> |<\/span>  HASH<\/span><\/a> GROUP BY     |<\/span>         |<\/span>      1<\/span> |<\/span>     75<\/span> |<\/span>  3150<\/span> |<\/span>  2328<\/span>   (<\/span>2<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>28<\/span> |<\/span>    105<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.18<\/span> |<\/span>    8325<\/span> |<\/span>
\n|*<\/span>  2<\/span> |<\/span>   TABLE ACCESS FULL|<\/span> DBTW053 |<\/span>      1<\/span> |<\/span>    106K|<\/span>  4351K|<\/span>  2320<\/span>   (<\/span>2<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>28<\/span> |<\/span>    105K|<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.11<\/span> |<\/span>    8325<\/span> |<\/span>
\n------------------------------------------------------------------------------------------------------------------------<\/span>
\n
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
\n---------------------------------------------------<\/span>
\n
\n   2<\/span> -<\/span> filter(<\/span>(<\/span>"CATEGORY"<\/span>>=<\/span>455<\/span> AND "CATEGORY"<\/span><=<\/span>559<\/span>)<\/span>)<\/span>
\n
\nNote
\n-----<\/span>
\n   -<\/span> SQL patch "Patch_669gr4xtnvqa1"<\/span> used for<\/span> this statement
\n
\n
\n25<\/span> linhas selecionadas.<\/span>
\n
\nSQL><\/span>
\nSQL><\/span> exec<\/span><\/a> sys.<\/span>DBMS_SQLDIAG.<\/span>drop_sql_patch(<\/span>name =><\/span> 'Patch_669gr4xtnvqa1'<\/span>)<\/span>;<\/span>
\n
\nPL\/<\/span>SQL procedure successfully completed.<\/span>
\n
\nSQL><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Analisando o plano de execu\u00e7\u00e3o verificamos que o Otimizador escolheu fazer a opera\u00e7\u00e3o TABLE FULL SCAN apesar da consulta possuir o HINT que pede para utilizar o \u00edndice, alem disso podemos verificar na se\u00e7\u00e3o NOTE o coment\u00e1rio:
\n[SQL patch “Patch_669gr4xtnvqa1” used for this statement]<\/span><\/p>\n

3) SQL PLAN MANAGEMENT<\/span><\/h1>\n

A terceira t\u00e9cnica utiliza o SQL PLAN MANAGEMENT que pode ser utilizada somente com a licen\u00e7a Enterprise Edition do 11gR2. Nas licen\u00e7as Express Edition e Standard Edition os pacotes do SPM est\u00e3o dispon\u00edveis porem eles n\u00e3o funcionam adequadamente, no artigo<\/a><\/strong> publicado no blog ORACLE OPTIMIZER fica claro que o SPM est\u00e1 dispon\u00edvel para licen\u00e7a Enterprise Edition e n\u00e3o requer licen\u00e7as adicionais como as options Diagnostic & Tuning Pack.<\/span><\/p>\n

Para exemplificar a sua aplica\u00e7\u00e3o vamos fazer uma simula\u00e7\u00e3o utilizando a mesma consulta das simula\u00e7\u00f5es anteriores, porem agora vamos utilizar uma base Oracle 11gR2 Enterprise Edition.<\/span><\/p>\n

Nesta simula\u00e7\u00e3o vamos:<\/span><\/p>\n

3.1) Criar um usu\u00e1rio com os privil\u00e9gios necess\u00e1rios<\/span>
\n3.2) Criar a tabela e \u00edndice da consulta<\/span>
\n3.3) Executar a consulta original da aplica\u00e7\u00e3o<\/span>
\n3.4) Executar a consulta com altera\u00e7\u00e3o para melhoria do desempenho<\/span>
\n3.5) Criar a SQL PLAN BASELINE<\/span>
\n3.6) Incluir o plano original na BASELINE<\/span>
\n3.7) Executar a consulta original novamente<\/span><\/p>\n

 <\/p>\n

3.1) Criar um usu\u00e1rio com os privil\u00e9gios<\/span><\/h2>\n

 <\/p>\n

Para criar o usu\u00e1rio CURSO02 com os privil\u00e9gios necess\u00e1rios precisamos conectar no banco de dados com usu\u00e1rio SYS.<\/span><\/p>\n

 <\/p>\n

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
<\/div><\/td>
SQL><\/span> --<\/span> Conectar na base com usu\u00e1rio SYS
\nSQL><\/span>
\nSQL><\/span> SELECT *<\/span> FROM V$VERSION<\/span> where rownum <<\/span> 2<\/span>;<\/span>
\n
\nBANNER
\n--------------------------------------------------------------------------------<\/span>
\nOracle Database 11g Enterprise Edition Release 11.<\/span>2.<\/span>0.<\/span>4.<\/span>0 -<\/span> 64bit Production
\n
\nSQL><\/span> CREATE USER curso02 identified by curso02 default<\/span> tablespace users temporary tablespace temp;<\/span>
\n
\nUser created.<\/span>
\n
\nSQL><\/span> alter user curso02 quota unlimited on users;<\/span>
\n
\nUser altered.<\/span>
\n
\nSQL><\/span> grant dba to curso02;<\/span>
\n
\nGrant succeeded.<\/span>
\n
\nSQL><\/span> grant execute on DBMS_SPM to curso02;<\/span>
\n
\nGrant succeeded.<\/span>
\n
\nSQL><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

3.2) Criar a tabela e \u00edndice da consulta<\/span><\/h2>\n

 <\/p>\n

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
<\/div><\/td>
SQL ><\/span> create table dbtw053 (<\/span>matr ,<\/span> category,<\/span> created,<\/span> descr)<\/span>
\n  2<\/span>      as<\/span>
\n  3<\/span>      select rownum,<\/span> mod(<\/span>rownum,<\/span> 1000<\/span>)<\/span>,<\/span> sysdate-<\/span>mod(<\/span>rownum,<\/span> 2000<\/span>)<\/span>,<\/span> 'DBTimeWizard - Performance and Tuning'<\/span> from dual connect by level <=<\/span> 1000000<\/span>;<\/span>
\n
\nTabela criada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> create index dbtw053_category_idx on dbtw053(<\/span>category)<\/span>;<\/span>
\n
\n\u00cdndice criado.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> exec<\/span><\/a> dbms_stats.<\/span>gather_table_stats(<\/span>ownname=><\/span> USER,<\/span> tabname=><\/span> 'DBTW053'<\/span>,<\/span> method_opt=><\/span>'for all columns size auto'<\/span>,<\/span>cascade=><\/span>true<\/span>,<\/span>force=><\/span>true<\/span>,<\/span>degree=><\/span>16<\/span>)<\/span>;<\/span>
\n
\nProcedimento PL\/<\/span>SQL conclu\u00eddo<\/span> com sucesso.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

3.3) Executar a consulta original da aplica\u00e7\u00e3o<\/span><\/h2>\n

 <\/p>\n

A consulta que supostamente extra\u00edmos da aplica\u00e7\u00e3o possui um HINT que for\u00e7a o Otimizador a utilizar o \u00edndice DBTW053_CATEGORY_IDX.<\/span><\/p>\n

 <\/p>\n

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
<\/div><\/td>
SQL ><\/span> ALTER SESSION SET statistics_level =<\/span> ALL;<\/span>
\n
\nSess\u00e3o alterada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> SELECT \/*+ INDEX(A dbtw053_category_idx) *\/<\/span> \/* DBTW-053.1 *\/<\/span> category,<\/span> descr,<\/span> count<\/span><\/a>(<\/span>1<\/span>)<\/span> qtde FROM dbtw053 A WHERE category BETWEEN 455<\/span> AND 559<\/span> GROUP BY category,<\/span> descr;<\/span>
\n
\n  CATEGORY DESCR                                       QTDE
\n----------<\/span> -------------------------------------<\/span> ----------<\/span>
\n       460<\/span> DBTimeWizard -<\/span> Performance and Tuning       1000<\/span>
\n       472<\/span> DBTimeWizard -<\/span> Performance and Tuning       1000<\/span>
\n  .<\/span>
\n  .<\/span>
\n  .<\/span>
\n       559<\/span> DBTimeWizard -<\/span> Performance and Tuning       1000<\/span>
\n
\n105<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> --<\/span> Recupera o SQL_ID e o CHILD_NUMBER  da consulta executada
\nSQL ><\/span>
\nSQL ><\/span> column sql_id new_value m_sql_id
\nSQL ><\/span> column child_number new_value m_child_no
\nSQL ><\/span>
\nSQL ><\/span> SELECT sql_id,<\/span> child_number
\n  2<\/span>    FROM v$sql<\/span>
\n  3<\/span>   WHERE sql_text LIKE '%DBTW-053.1%'<\/span>
\n  4<\/span>     AND sql_text NOT LIKE '%v$sql%'<\/span>;<\/span>
\n
\nSQL_ID        CHILD_NUMBER
\n-------------<\/span> ------------<\/span>
\n1vtkt906qhga8            0<\/span>
\n
\n1<\/span> linha selecionada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> --<\/span> Gera o relatorio do<\/span> plano de execucao da consulta
\nSQL ><\/span>
\nSQL ><\/span> SELECT *<\/span> FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'&m_sql_id'<\/span>,&<\/span>m_child_no,<\/span>'typical iostats last'<\/span>)<\/span>)<\/span>;<\/span>
\n
\nPLAN_TABLE_OUTPUT
\n---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\nSQL_ID  1vtkt906qhga8,<\/span> child number 0<\/span>
\n-------------------------------------<\/span>
\nSELECT \/*+ INDEX(A dbtw053_category_idx) *\/<\/span> \/* DBTW-053.1 *\/<\/span> category,<\/span>
\ndescr,<\/span> count<\/span><\/a>(<\/span>:<\/span>"SYS_B_0"<\/span>)<\/span> qtde FROM dbtw053 A WHERE category BETWEEN
\n:<\/span>"SYS_B_1"<\/span> AND :<\/span>"SYS_B_2"<\/span> GROUP BY category,<\/span> descr
\n
\nPlan hash<\/span><\/a> value:<\/span> 1559473304<\/span>
\n
\n---------------------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\n|<\/span> Id  |<\/span> Operation                     |<\/span> Name                 |<\/span> Starts |<\/span> E-<\/span>Rows |<\/span>E-<\/span>Bytes|<\/span> Cost (<\/span>%<\/span>CPU)<\/span>|<\/span> E-<\/span>Time<\/span><\/a>   |<\/span> A-<\/span>Rows |<\/span>   A-<\/span>Time<\/span><\/a>   |<\/span> Buffers |<\/span> Reads  |<\/span>
\n---------------------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\n|<\/span>   0<\/span> |<\/span> SELECT STATEMENT              |<\/span>                      |<\/span>      1<\/span> |<\/span>        |<\/span>       |<\/span>   106K(<\/span>100<\/span>)<\/span>|<\/span>          |<\/span>    105<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>07.08<\/span> |<\/span>     105K|<\/span>   2088<\/span> |<\/span>
\n|<\/span>   1<\/span> |<\/span>  HASH<\/span><\/a> GROUP BY                |<\/span>                      |<\/span>      1<\/span> |<\/span>     75<\/span> |<\/span>  3150<\/span> |<\/span>   106K  (<\/span>1<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>04<\/span> |<\/span>    105<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>07.08<\/span> |<\/span>     105K|<\/span>   2088<\/span> |<\/span>
\n|*<\/span>  2<\/span> |<\/span>   FILTER                      |<\/span>                      |<\/span>      1<\/span> |<\/span>        |<\/span>       |<\/span>            |<\/span>          |<\/span>    105K|<\/span>00<\/span>:<\/span>00<\/span>:<\/span>07.06<\/span> |<\/span>     105K|<\/span>   2088<\/span> |<\/span>
\n|<\/span>   3<\/span> |<\/span>    TABLE ACCESS BY INDEX ROWID|<\/span> DBTW053              |<\/span>      1<\/span> |<\/span>    106K|<\/span>  4351K|<\/span>   106K  (<\/span>1<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>04<\/span> |<\/span>    105K|<\/span>00<\/span>:<\/span>00<\/span>:<\/span>07.05<\/span> |<\/span>     105K|<\/span>   2088<\/span> |<\/span>
\n|*<\/span>  4<\/span> |<\/span>     INDEX RANGE<\/span><\/a> SCAN          |<\/span> DBTW053_CATEGORY_IDX |<\/span>      1<\/span> |<\/span>    106K|<\/span>       |<\/span>   225<\/span>   (<\/span>1<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/span> |<\/span>    105K|<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.19<\/span> |<\/span>     222<\/span> |<\/span>    210<\/span> |<\/span>
\n---------------------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\n
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
\n---------------------------------------------------<\/span>
\n
\n   2<\/span> -<\/span> filter(<\/span>:<\/span>SYS_B_2>=:<\/span>SYS_B_1)<\/span>
\n   4<\/span> -<\/span> access(<\/span>"CATEGORY"<\/span>>=:<\/span>SYS_B_1 AND "CATEGORY"<\/span><=:<\/span>SYS_B_2)<\/span>
\n
\n
\n24<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

No plano de execu\u00e7\u00e3o acima podemos observar que foram lidas 105 mil linhas na tabela utilizando os ROWID selecionados no \u00edndice e o tempo total gasto para executar a consulta foi 7 segundos e 8 cent\u00e9simos.<\/span><\/p>\n

 <\/p>\n

3.4) Executar a consulta com altera\u00e7\u00e3o<\/span><\/h2>\n

 <\/p>\n

Vamos executar a consulta novamente sem o HINT para dar mais liberdade de decis\u00e3o ao Otimizador<\/span><\/p>\n

 <\/p>\n

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
<\/div><\/td>
SQL ><\/span> SELECT \/* DBTW-053.2 *\/<\/span> category,<\/span> descr,<\/span> count<\/span><\/a>(<\/span>1<\/span>)<\/span> qtde FROM dbtw053 A WHERE category BETWEEN 455<\/span> AND 559<\/span> GROUP BY category,<\/span> descr;<\/span>
\n
\n  CATEGORY DESCR                                       QTDE
\n----------<\/span> -------------------------------------<\/span> ----------<\/span>
\n       460<\/span> DBTimeWizard -<\/span> Performance and Tuning       1000<\/span>
\n       472<\/span> DBTimeWizard -<\/span> Performance and Tuning       1000<\/span>
\n  .<\/span>
\n  .<\/span>
\n  .<\/span>
\n       559<\/span> DBTimeWizard -<\/span> Performance and Tuning       1000<\/span>
\n
\n105<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> --<\/span> Recupera o SQL_ID e o CHILD_NUMBER  da consulta executada
\nSQL ><\/span>
\nSQL ><\/span> column sql_id new_value m_sql_id
\nSQL ><\/span> column child_number new_value m_child_no
\nSQL ><\/span>
\nSQL ><\/span> SELECT sql_id,<\/span> child_number
\n  2<\/span>    FROM v$sql<\/span>
\n  3<\/span>   WHERE sql_text LIKE '%DBTW-053.2%'<\/span>
\n  4<\/span>     AND sql_text NOT LIKE '%v$sql%'<\/span>;<\/span>
\n
\nSQL_ID        CHILD_NUMBER
\n-------------<\/span> ------------<\/span>
\naa2v8aq8t17w2            0<\/span>
\n
\n1<\/span> linha selecionada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> --<\/span> Gera o relatorio do<\/span> plano de execucao da consulta
\nSQL ><\/span>
\nSQL ><\/span> SELECT *<\/span> FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'&m_sql_id'<\/span>,&<\/span>m_child_no,<\/span>'typical iostats last'<\/span>)<\/span>)<\/span>;<\/span>
\n
\nPLAN_TABLE_OUTPUT
\n--------------------------------------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\nSQL_ID  aa2v8aq8t17w2,<\/span> child number 0<\/span>
\n-------------------------------------<\/span>
\nSELECT \/* DBTW-053.2 *\/<\/span> category,<\/span> descr,<\/span> count<\/span><\/a>(<\/span>:<\/span>"SYS_B_0"<\/span>)<\/span> qtde FROM
\ndbtw053 A WHERE category BETWEEN :<\/span>"SYS_B_1"<\/span> AND :<\/span>"SYS_B_2"<\/span> GROUP BY
\ncategory,<\/span> descr
\n
\nPlan hash<\/span><\/a> value:<\/span> 186720033<\/span>
\n
\n----------------------------------------------------------------------------------------------------------------------------------<\/span>
\n|<\/span> Id  |<\/span> Operation           |<\/span> Name    |<\/span> Starts |<\/span> E-<\/span>Rows |<\/span>E-<\/span>Bytes|<\/span> Cost (<\/span>%<\/span>CPU)<\/span>|<\/span> E-<\/span>Time<\/span><\/a>   |<\/span> A-<\/span>Rows |<\/span>   A-<\/span>Time<\/span><\/a>   |<\/span> Buffers |<\/span> Reads  |<\/span>
\n----------------------------------------------------------------------------------------------------------------------------------<\/span>
\n|<\/span>   0<\/span> |<\/span> SELECT STATEMENT    |<\/span>         |<\/span>      1<\/span> |<\/span>        |<\/span>       |<\/span>   297<\/span> (<\/span>100<\/span>)<\/span>|<\/span>          |<\/span>    105<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>01.77<\/span> |<\/span>    8319<\/span> |<\/span>   8316<\/span> |<\/span>
\n|<\/span>   1<\/span> |<\/span>  HASH<\/span><\/a> GROUP BY      |<\/span>         |<\/span>      1<\/span> |<\/span>     75<\/span> |<\/span>  3150<\/span> |<\/span>   297<\/span>  (<\/span>11<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/span> |<\/span>    105<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>01.77<\/span> |<\/span>    8319<\/span> |<\/span>   8316<\/span> |<\/span>
\n|*<\/span>  2<\/span> |<\/span>   FILTER            |<\/span>         |<\/span>      1<\/span> |<\/span>        |<\/span>       |<\/span>            |<\/span>          |<\/span>    105K|<\/span>00<\/span>:<\/span>00<\/span>:<\/span>01.75<\/span> |<\/span>    8319<\/span> |<\/span>   8316<\/span> |<\/span>
\n|*<\/span>  3<\/span> |<\/span>    TABLE ACCESS FULL|<\/span> DBTW053 |<\/span>      1<\/span> |<\/span>    106K|<\/span>  4351K|<\/span>   290<\/span>   (<\/span>9<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/span> |<\/span>    105K|<\/span>00<\/span>:<\/span>00<\/span>:<\/span>01.74<\/span> |<\/span>    8319<\/span> |<\/span>   8316<\/span> |<\/span>
\n----------------------------------------------------------------------------------------------------------------------------------<\/span>
\n
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
\n---------------------------------------------------<\/span>
\n
\n   2<\/span> -<\/span> filter(<\/span>:<\/span>SYS_B_2>=:<\/span>SYS_B_1)<\/span>
\n   3<\/span> -<\/span> filter(<\/span>(<\/span>"CATEGORY"<\/span>>=:<\/span>SYS_B_1 AND "CATEGORY"<\/span><=:<\/span>SYS_B_2)<\/span>)<\/span>
\n
\n
\n23<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Analisando o plano de execu\u00e7\u00e3o verificamos que o Otimizador escolheu fazer uma opera\u00e7\u00e3o TABLE FULL SCAN e o tempo de execu\u00e7\u00e3o caiu de 7 segundos e 8 cent\u00e9simos para 1 segundo e 77 cent\u00e9simos, um redu\u00e7\u00e3o de 75% no tempo de execu\u00e7\u00e3o.<\/span><\/p>\n

 <\/p>\n

3.5) Criar a SQL PLAN BASELINE<\/span><\/h2>\n

 <\/p>\n

Agora vamos criar uma SQL PLAN BASELINE manualmente onde vamos colocar o plano que faz a opera\u00e7\u00e3o TABLE FULL SCAN como o plano aceit\u00e1vel, para isso vamos utilizar o script cspb.sql<\/strong><\/a>.<\/span><\/p>\n

 <\/p>\n

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
<\/div><\/td>
SQL ><\/span> show parameters plan_baseline
\n
\nNAME                                 TYPE                             VALUE
\n------------------------------------<\/span> --------------------------------<\/span> ------------------------------<\/span>
\noptimizer_capture_sql_plan_baselines boolean                          FALSE<\/span>
\noptimizer_use_sql_plan_baselines     boolean                          TRUE<\/span>
\nSQL ><\/span>
\nSQL ><\/span> --<\/span> Obter SQL_ID,<\/span> CHILD_NUMBER e PLAN_HASH_VALUE das consultas executadas nas etapas 3<\/span> e 4<\/span>
\nSQL ><\/span>
\nSQL ><\/span> col sql_text for<\/span> a60
\nSQL ><\/span> SELECT sql_id,<\/span> child_number,<\/span> plan_hash_value,<\/span> substr<\/span><\/a>(<\/span>sql_text,<\/span>1<\/span>,<\/span>60<\/span>)<\/span> sql_text
\n  2<\/span>   FROM v$sql<\/span>
\n  3<\/span>  WHERE sql_text LIKE '%dbtw053%'<\/span>
\n  4<\/span>    AND sql_text NOT LIKE '%v$sql%'<\/span>;<\/span>
\n
\nSQL_ID        CHILD_NUMBER PLAN_HASH_VALUE SQL_TEXT
\n-------------<\/span> ------------<\/span> ---------------<\/span> ------------------------------------------------------------<\/span>
\n1vtkt906qhga8            0<\/span>      1559473304<\/span> SELECT \/*+ INDEX(A dbtw053_category_idx) *\/<\/span> \/* DBTW-053.1 *\/<\/span>
\naa2v8aq8t17w2            0<\/span>       186720033<\/span> SELECT \/* DBTW-053.2 *\/<\/span> category,<\/span> descr,<\/span> count<\/span><\/a>(<\/span>:<\/span>"SYS_B_0"<\/span>)<\/span> q
\n
\n2<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> --<\/span> Executar o script "cspb.sql"<\/span> para criar um SQL PLAN
\nSQL ><\/span>
\nSQL ><\/span> @<\/span>cspb.<\/span>sql;<\/span>
\nSQL_ID     Consulta original :<\/span> 1vtkt906qhga8
\nCHILD#     Consulta original : 0
\n<\/span>SQL_ID     Consulta alterada :<\/span> aa2v8aq8t17w2
\nPLAN_HASH# Consulta alterada : 186720033
\n<\/span>SELECT \/*+ INDEX(A dbtw053_category_idx) *\/<\/span> \/* DBTW-053.1 *\/<\/span> category,<\/span> descr,<\/span> count<\/span><\/a>(<\/span>:<\/span>"SYS_B_0"<\/span>)<\/span> qtde FROM dbtw053 A WHERE category BETWEEN :<\/span>"SYS_B_1"<\/span> AND :<\/span>"SYS_B_2"<\/span> GROUP BY category,<\/span> descr
\n1<\/span>
\n
\nProcedimento PL\/<\/span>SQL conclu\u00eddo<\/span> com sucesso.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> --<\/span> Obter informa\u00e7\u00f5es da SQL PLAN BASELINE criada
\nSQL ><\/span>
\nSQL ><\/span> SELECT SQL_HANDLE,<\/span> PLAN_NAME,<\/span> ENABLED,<\/span> ACCEPTED,<\/span> FIXED  FROM   DBA_SQL_PLAN_BASELINES;<\/span>
\n
\nSQL_HANDLE                     PLAN_NAME                      ENA ACC FIX
\n------------------------------<\/span> ------------------------------<\/span> ---<\/span> ---<\/span> ---<\/span>
\nSQL_cd29185c945f9cc5           SQL_PLAN_cua8sbka5z7655a5cb76a YES YES NO
\n
\n1<\/span> linha selecionada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> SELECT *<\/span> FROM TABLE(<\/span>DBMS_XPLAN.<\/span>DISPLAY_SQL_PLAN_BASELINE(<\/span>
\n  2<\/span>                         sql_handle=><\/span>'&sql_handle'<\/span>,<\/span>
\n  3<\/span>                         format=><\/span>'basic'<\/span>)<\/span>)<\/span>;<\/span>
\nInforme o valor para sql_handle:<\/span> SQL_cd29185c945f9cc5
\n
\nPLAN_TABLE_OUTPUT
\n------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\n
\n--------------------------------------------------------------------------------<\/span>
\nSQL handle:<\/span> SQL_cd29185c945f9cc5
\nSQL text:<\/span> SELECT \/*+ INDEX(A dbtw053_category_idx) *\/<\/span> \/* DBTW-053.1 *\/<\/span> category,<\/span>
\n          descr,<\/span> count<\/span><\/a>(<\/span>:<\/span>"SYS_B_0"<\/span>)<\/span> qtde FROM dbtw053 A WHERE category BETWEEN
\n          :<\/span>"SYS_B_1"<\/span> AND :<\/span>"SYS_B_2"<\/span> GROUP BY category,<\/span> descr
\n--------------------------------------------------------------------------------<\/span>
\n
\n--------------------------------------------------------------------------------<\/span>
\nPlan name:<\/span> SQL_PLAN_cua8sbka5z7655a5cb76a         Plan id:<\/span> 1516025706<\/span>
\nEnabled:<\/span> YES     Fixed:<\/span> NO      Accepted:<\/span> YES     Origin:<\/span> MANUAL-<\/span>LOAD
\n--------------------------------------------------------------------------------<\/span>
\n
\nPlan hash<\/span><\/a> value:<\/span> 186720033<\/span>
\n
\n---------------------------------------<\/span>
\n|<\/span> Id  |<\/span> Operation           |<\/span> Name    |<\/span>
\n---------------------------------------<\/span>
\n|<\/span>   0<\/span> |<\/span> SELECT STATEMENT    |<\/span>         |<\/span>
\n|<\/span>   1<\/span> |<\/span>  HASH<\/span><\/a> GROUP BY      |<\/span>         |<\/span>
\n|<\/span>   2<\/span> |<\/span>   FILTER            |<\/span>         |<\/span>
\n|<\/span>   3<\/span> |<\/span>    TABLE ACCESS FULL|<\/span> DBTW053 |<\/span>
\n---------------------------------------<\/span>
\n
\n23<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

3.6) Incluir o plano original na BASELINE<\/span><\/h2>\n

 <\/p>\n

Como verificamos na etapa anterior o plano com a opera\u00e7\u00e3o TABLE FULL SCAN foi inclu\u00eddo na BASELINE como ACCEPTED, agora vamos executar a consulta com HINT novamente para que o seu plano que utiliza o \u00edndice seja inclu\u00eddo na BASELINE como NOT ACCEPTED.<\/span><\/p>\n

 <\/p>\n

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
<\/div><\/td>
SQL ><\/span> SELECT \/*+ INDEX(A dbtw053_category_idx) *\/<\/span> \/* DBTW-053.1 *\/<\/span> category,<\/span> descr,<\/span> count<\/span><\/a>(<\/span>1<\/span>)<\/span> qtde FROM dbtw053 A WHERE category BETWEEN 455<\/span> AND 559<\/span> GROUP BY category,<\/span> descr;<\/span>
\n
\n  CATEGORY DESCR                                       QTDE
\n----------<\/span> -------------------------------------<\/span> ----------<\/span>
\n       460<\/span> DBTimeWizard -<\/span> Performance and Tuning       1000<\/span>
\n       472<\/span> DBTimeWizard -<\/span> Performance and Tuning       1000<\/span>
\n  .<\/span>
\n  .<\/span>
\n  .<\/span>
\n       559<\/span> DBTimeWizard -<\/span> Performance and Tuning       1000<\/span>
\n
\n105<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> --<\/span> Obter informa\u00e7\u00f5es da SQL PLAN BASELINES
\nSQL ><\/span>
\nSQL ><\/span> SELECT SQL_HANDLE,<\/span> PLAN_NAME,<\/span> ENABLED,<\/span> ACCEPTED,<\/span> FIXED  FROM   DBA_SQL_PLAN_BASELINES;<\/span>
\n
\nSQL_HANDLE                     PLAN_NAME                      ENA ACC FIX
\n------------------------------<\/span> ------------------------------<\/span> ---<\/span> ---<\/span> ---<\/span>
\nSQL_cd29185c945f9cc5           SQL_PLAN_cua8sbka5z7655a5cb76a YES YES NO
\nSQL_cd29185c945f9cc5           SQL_PLAN_cua8sbka5z7658b066ca3 YES NO  NO
\n
\n2<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span> SELECT *<\/span> FROM TABLE(<\/span>DBMS_XPLAN.<\/span>DISPLAY_SQL_PLAN_BASELINE(<\/span>
\n  2<\/span>                         sql_handle=><\/span>'&sql_handle'<\/span>,<\/span>
\n  3<\/span>                         format=><\/span>'basic'<\/span>)<\/span>)<\/span>;<\/span>
\nInforme o valor para sql_handle:<\/span> SQL_cd29185c945f9cc5
\n
\nPLAN_TABLE_OUTPUT
\n---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\n
\n--------------------------------------------------------------------------------<\/span>
\nSQL handle:<\/span> SQL_cd29185c945f9cc5
\nSQL text:<\/span> SELECT \/*+ INDEX(A dbtw053_category_idx) *\/<\/span> \/* DBTW-053.1 *\/<\/span> category,<\/span>
\n          descr,<\/span> count<\/span><\/a>(<\/span>:<\/span>"SYS_B_0"<\/span>)<\/span> qtde FROM dbtw053 A WHERE category BETWEEN
\n          :<\/span>"SYS_B_1"<\/span> AND :<\/span>"SYS_B_2"<\/span> GROUP BY category,<\/span> descr
\n--------------------------------------------------------------------------------<\/span>
\n
\n--------------------------------------------------------------------------------<\/span>
\nPlan name:<\/span> SQL_PLAN_cua8sbka5z7655a5cb76a         Plan id:<\/span> 1516025706<\/span>
\nEnabled:<\/span> YES     Fixed:<\/span> NO      Accepted:<\/span> YES     Origin:<\/span> MANUAL-<\/span>LOAD
\n--------------------------------------------------------------------------------<\/span>
\n
\nPlan hash<\/span><\/a> value:<\/span> 186720033<\/span>
\n
\n---------------------------------------<\/span>
\n|<\/span> Id  |<\/span> Operation           |<\/span> Name    |<\/span>
\n---------------------------------------<\/span>
\n|<\/span>   0<\/span> |<\/span> SELECT STATEMENT    |<\/span>         |<\/span>
\n|<\/span>   1<\/span> |<\/span>  HASH<\/span><\/a> GROUP BY      |<\/span>         |<\/span>
\n|<\/span>   2<\/span> |<\/span>   FILTER            |<\/span>         |<\/span>
\n|<\/span>   3<\/span> |<\/span>    TABLE ACCESS FULL|<\/span> DBTW053 |<\/span>
\n---------------------------------------<\/span>
\n
\n--------------------------------------------------------------------------------<\/span>
\nPlan name:<\/span> SQL_PLAN_cua8sbka5z7658b066ca3         Plan id:<\/span> 2332454051<\/span>
\nEnabled:<\/span> YES     Fixed:<\/span> NO      Accepted:<\/span> NO      Origin:<\/span> AUTO-<\/span>CAPTURE
\n--------------------------------------------------------------------------------<\/span>
\n
\nPlan hash<\/span><\/a> value:<\/span> 1559473304<\/span>
\n
\n--------------------------------------------------------------<\/span>
\n|<\/span> Id  |<\/span> Operation                     |<\/span> Name                 |<\/span>
\n--------------------------------------------------------------<\/span>
\n|<\/span>   0<\/span> |<\/span> SELECT STATEMENT              |<\/span>                      |<\/span>
\n|<\/span>   1<\/span> |<\/span>  HASH<\/span><\/a> GROUP BY                |<\/span>                      |<\/span>
\n|<\/span>   2<\/span> |<\/span>   FILTER                      |<\/span>                      |<\/span>
\n|<\/span>   3<\/span> |<\/span>    TABLE ACCESS BY INDEX ROWID|<\/span> DBTW053              |<\/span>
\n|<\/span>   4<\/span> |<\/span>     INDEX RANGE<\/span><\/a> SCAN          |<\/span> DBTW053_CATEGORY_IDX |<\/span>
\n--------------------------------------------------------------<\/span>
\n
\n40<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

3.7) Executar a consulta original novamente<\/span><\/h2>\n

 <\/p>\n

Com a BASELINE devidamente carregada vamos executar a consulta original e verificar que plano de execu\u00e7\u00e3o o Otimizador vai escolher.<\/span><\/p>\n

 <\/p>\n

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
<\/div><\/td>
SQL ><\/span> SELECT \/*+ INDEX(A dbtw053_category_idx) *\/<\/span> \/* DBTW-053.1 *\/<\/span> category,<\/span> descr,<\/span> count<\/span><\/a>(<\/span>1<\/span>)<\/span> qtde FROM dbtw053 A WHERE category BETWEEN 455<\/span> AND 559<\/span> GROUP BY category,<\/span> descr;<\/span>
\n
\n  CATEGORY DESCR                                       QTDE
\n----------<\/span> -------------------------------------<\/span> ----------<\/span>
\n       460<\/span> DBTimeWizard -<\/span> Performance and Tuning       1000<\/span>
\n       472<\/span> DBTimeWizard -<\/span> Performance and Tuning       1000<\/span>
\n  .<\/span>
\n  .<\/span>
\n  .<\/span>
\n       559<\/span> DBTimeWizard -<\/span> Performance and Tuning       1000<\/span>
\n
\n105<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> --<\/span> Recupera o SQL_ID e o CHILD_NUMBER  da consulta executada
\nSQL ><\/span>
\nSQL ><\/span> column sql_id new_value m_sql_id
\nSQL ><\/span> column child_number new_value m_child_no
\nSQL ><\/span>
\nSQL ><\/span> SELECT sql_id,<\/span> child_number
\n  2<\/span>    FROM v$sql<\/span>
\n  3<\/span>   WHERE sql_text LIKE '%DBTW-053.1%'<\/span>
\n  4<\/span>     AND sql_text NOT LIKE '%v$sql%'<\/span>;<\/span>
\n
\nSQL_ID        CHILD_NUMBER
\n-------------<\/span> ------------<\/span>
\n1vtkt906qhga8            0<\/span>
\n
\n1<\/span> linha selecionada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> --<\/span> Gera o relatorio do<\/span> plano de execucao da consulta
\nSQL ><\/span>
\nSQL ><\/span> SELECT *<\/span> FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'&m_sql_id'<\/span>,&<\/span>m_child_no,<\/span>'typical iostats last'<\/span>)<\/span>)<\/span>;<\/span>
\n
\nPLAN_TABLE_OUTPUT
\n------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\nSQL_ID  1vtkt906qhga8,<\/span> child number 0<\/span>
\n-------------------------------------<\/span>
\nSELECT \/*+ INDEX(A dbtw053_category_idx) *\/<\/span> \/* DBTW-053.1 *\/<\/span> category,<\/span>
\ndescr,<\/span> count<\/span><\/a>(<\/span>:<\/span>"SYS_B_0"<\/span>)<\/span> qtde FROM dbtw053 A WHERE category BETWEEN
\n:<\/span>"SYS_B_1"<\/span> AND :<\/span>"SYS_B_2"<\/span> GROUP BY category,<\/span> descr
\n
\nPlan hash<\/span><\/a> value:<\/span> 186720033<\/span>
\n
\n----------------------------------------------------------------------------------------------------------------------------------<\/span>
\n|<\/span> Id  |<\/span> Operation           |<\/span> Name    |<\/span> Starts |<\/span> E-<\/span>Rows |<\/span>E-<\/span>Bytes|<\/span> Cost (<\/span>%<\/span>CPU)<\/span>|<\/span> E-<\/span>Time<\/span><\/a>   |<\/span> A-<\/span>Rows |<\/span>   A-<\/span>Time<\/span><\/a>   |<\/span> Buffers |<\/span> Reads  |<\/span>
\n----------------------------------------------------------------------------------------------------------------------------------<\/span>
\n|<\/span>   0<\/span> |<\/span> SELECT STATEMENT    |<\/span>         |<\/span>      1<\/span> |<\/span>        |<\/span>       |<\/span>   297<\/span> (<\/span>100<\/span>)<\/span>|<\/span>          |<\/span>    105<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>01.65<\/span> |<\/span>    8319<\/span> |<\/span>   8316<\/span> |<\/span>
\n|<\/span>   1<\/span> |<\/span>  HASH<\/span><\/a> GROUP BY      |<\/span>         |<\/span>      1<\/span> |<\/span>     75<\/span> |<\/span>  3150<\/span> |<\/span>   297<\/span>  (<\/span>11<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/span> |<\/span>    105<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>01.65<\/span> |<\/span>    8319<\/span> |<\/span>   8316<\/span> |<\/span>
\n|*<\/span>  2<\/span> |<\/span>   FILTER            |<\/span>         |<\/span>      1<\/span> |<\/span>        |<\/span>       |<\/span>            |<\/span>          |<\/span>    105K|<\/span>00<\/span>:<\/span>00<\/span>:<\/span>01.63<\/span> |<\/span>    8319<\/span> |<\/span>   8316<\/span> |<\/span>
\n|*<\/span>  3<\/span> |<\/span>    TABLE ACCESS FULL|<\/span> DBTW053 |<\/span>      1<\/span> |<\/span>    106K|<\/span>  4351K|<\/span>   290<\/span>   (<\/span>9<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/span> |<\/span>    105K|<\/span>00<\/span>:<\/span>00<\/span>:<\/span>01.62<\/span> |<\/span>    8319<\/span> |<\/span>   8316<\/span> |<\/span>
\n----------------------------------------------------------------------------------------------------------------------------------<\/span>
\n
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
\n---------------------------------------------------<\/span>
\n
\n   2<\/span> -<\/span> filter(<\/span>:<\/span>SYS_B_2>=:<\/span>SYS_B_1)<\/span>
\n   3<\/span> -<\/span> filter(<\/span>(<\/span>"CATEGORY"<\/span>>=:<\/span>SYS_B_1 AND "CATEGORY"<\/span><=:<\/span>SYS_B_2)<\/span>)<\/span>
\n
\nNote
\n-----<\/span>
\n   -<\/span> SQL plan baseline SQL_PLAN_cua8sbka5z7655a5cb76a used for<\/span> this statement
\n
\n
\n27<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Analisando o plano de execu\u00e7\u00e3o verificamos que o Otimizador escolheu fazer uma opera\u00e7\u00e3o TABLE FULL SCAN apesar da consulta executada estar com o HINT, tamb\u00e9m podemos observar na se\u00e7\u00e3o NOTE a mensagem: <\/span><\/p>\n

[SQL plan baseline SQL_PLAN_cua8sbka5z7655a5cb76a used for this statement]<\/span><\/p>\n

 
\n <\/p>\n

4) SQL PROFILE<\/span><\/h1>\n

 <\/p>\n

A quarta e ultima t\u00e9cnica \u00e9 a SQL PROFILE, para utilizar esta op\u00e7\u00e3o no Oracle Database 11gR2 \u00e9 necess\u00e1ria a licen\u00e7a Enterprise Edition e as options DIAGNOSTIC & TUNING PACK.<\/span><\/p>\n

Para exemplificar a sua aplica\u00e7\u00e3o vamos utilizar o mesmo usu\u00e1rio CURSO02 da simula\u00e7\u00e3o anterior e vamos desativar o SQL PLAN MANAGEMENT a n\u00edvel de sess\u00e3o para que o Otimizador desconsidere a BASELINE criada anteriormente.<\/span><\/p>\n

Nesta simula\u00e7\u00e3o vamos:<\/span><\/p>\n

4.1) Executar a consulta original da aplica\u00e7\u00e3o<\/span>
\n4.2) Criar uma SQL_PROFILE manualmente da consulta original<\/span>
\n4.3) Executar a consulta sem o HINT<\/span>
\n4.4) Alterar a OUTLINE da SQL_PROFILE que define o tipo de acesso a tabela<\/span>
\n4.5) Executar a consulta original novamente<\/span><\/p>\n

 <\/p>\n

4.1) Executar a consulta original da aplica\u00e7\u00e3o<\/span><\/h2>\n

 <\/p>\n

Vamos executar a consulta com HINT que for\u00e7a a utiliza\u00e7\u00e3o do \u00edndice para acessar a tabela e desativar a SQL PLAN BASELINE gerada na simula\u00e7\u00e3o anterior, alterando o valor do par\u00e2metro OPTIMIZER_USE_SQL_PLAN_BASELINES para FALSE a n\u00edvel de sess\u00e3o.<\/span><\/p>\n

 <\/p>\n

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
<\/div><\/td>
SQL><\/span> SELECT *<\/span> FROM V$VERSION<\/span> where rownum <<\/span> 2<\/span>;<\/span>
\n
\nBANNER
\n--------------------------------------------------------------------------------<\/span>
\nOracle Database 11g Enterprise Edition Release 11.<\/span>2.<\/span>0.<\/span>4.<\/span>0 -<\/span> 64bit Production
\n
\n1<\/span> linha selecionada.<\/span>
\n
\nSQL><\/span>
\nSQL><\/span> show parameters pack<\/span><\/a>
\n
\nNAME                                 TYPE                             VALUE
\n------------------------------------<\/span> --------------------------------<\/span> -----------------------<\/span>
\ncontrol_management_pack_access       string                           DIAGNOSTIC+<\/span>TUNING
\n
\nSQL><\/span>
\nSQL><\/span> ALTER SESSION SET optimizer_use_sql_plan_baselines =<\/span> FALSE<\/span>;<\/span>
\n
\nSess\u00e3o alterada.<\/span>
\n
\nSQL><\/span> ALTER SESSION SET statistics_level =<\/span> ALL;<\/span>
\n
\nSess\u00e3o alterada.<\/span>
\n
\nSQL><\/span>
\nSQL><\/span> SELECT \/*+ INDEX(A dbtw053_category_idx) *\/<\/span> \/* DBTW-053.1 *\/<\/span> category,<\/span> descr,<\/span> count<\/span><\/a>(<\/span>1<\/span>)<\/span> qtde FROM dbtw053 A WHERE category BETWEEN 455<\/span> AND 559<\/span> GROUP BY category,<\/span> descr;<\/span>
\n
\n  CATEGORY DESCR                                       QTDE
\n----------<\/span> -------------------------------------<\/span> ----------<\/span>
\n       460<\/span> DBTimeWizard -<\/span> Performance and Tuning       1000<\/span>
\n       472<\/span> DBTimeWizard -<\/span> Performance and Tuning       1000<\/span>
\n  .<\/span>
\n  .<\/span>
\n  .<\/span>
\n       559<\/span> DBTimeWizard -<\/span> Performance and Tuning       1000<\/span>
\n
\n105<\/span> linhas selecionadas.<\/span>
\n
\nSQL><\/span>
\nSQL><\/span> --<\/span> Recupera o SQL_ID e o CHILD_NUMBER  da consulta executada
\nSQL><\/span>
\nSQL><\/span> column sql_id new_value m_sql_id
\nSQL><\/span> column child_number new_value m_child_no
\nSQL><\/span>
\nSQL><\/span> SELECT sql_id,<\/span> child_number
\n  2<\/span>    FROM v$sql<\/span>
\n  3<\/span>   WHERE sql_text LIKE '%DBTW-053.1%'<\/span>
\n  4<\/span>     AND sql_text NOT LIKE '%v$sql%'<\/span>;<\/span>
\n
\nSQL_ID        CHILD_NUMBER
\n-------------<\/span> ------------<\/span>
\n1vtkt906qhga8            0<\/span>
\n
\n1<\/span> linha selecionada.<\/span>
\n
\nSQL><\/span>
\nSQL><\/span> --<\/span> Gera o relatorio do<\/span> plano de execucao da consulta
\nSQL><\/span>
\nSQL><\/span> SELECT *<\/span> FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'&m_sql_id'<\/span>,&<\/span>m_child_no,<\/span>'typical iostats last'<\/span>)<\/span>)<\/span>;<\/span>
\n
\nPLAN_TABLE_OUTPUT
\n---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\n
\nSQL_ID  1vtkt906qhga8,<\/span> child number 0<\/span>
\n-------------------------------------<\/span>
\nSELECT \/*+ INDEX(A dbtw053_category_idx) *\/<\/span> \/* DBTW-053.1 *\/<\/span> category,<\/span>
\ndescr,<\/span> count<\/span><\/a>(<\/span>:<\/span>"SYS_B_0"<\/span>)<\/span> qtde FROM dbtw053 A WHERE category BETWEEN
\n:<\/span>"SYS_B_1"<\/span> AND :<\/span>"SYS_B_2"<\/span> GROUP BY category,<\/span> descr
\n
\nPlan hash<\/span><\/a> value:<\/span> 1559473304<\/span>
\n
\n---------------------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\n|<\/span> Id  |<\/span> Operation                     |<\/span> Name                 |<\/span> Starts |<\/span> E-<\/span>Rows |<\/span>E-<\/span>Bytes|<\/span> Cost (<\/span>%<\/span>CPU)<\/span>|<\/span> E-<\/span>Time<\/span><\/a>   |<\/span> A-<\/span>Rows |<\/span>   A-<\/span>Time<\/span><\/a>   |<\/span> Buffers |<\/span> Reads  |<\/span>
\n---------------------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\n|<\/span>   0<\/span> |<\/span> SELECT STATEMENT              |<\/span>                      |<\/span>      1<\/span> |<\/span>        |<\/span>       |<\/span>   106K(<\/span>100<\/span>)<\/span>|<\/span>          |<\/span>    105<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>17.17<\/span> |<\/span>     105K|<\/span>   1015<\/span> |<\/span>
\n|<\/span>   1<\/span> |<\/span>  HASH<\/span><\/a> GROUP BY                |<\/span>                      |<\/span>      1<\/span> |<\/span>     75<\/span> |<\/span>  3150<\/span> |<\/span>   106K  (<\/span>1<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>04<\/span> |<\/span>    105<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>17.17<\/span> |<\/span>     105K|<\/span>   1015<\/span> |<\/span>
\n|*<\/span>  2<\/span> |<\/span>   FILTER                      |<\/span>                      |<\/span>      1<\/span> |<\/span>        |<\/span>       |<\/span>            |<\/span>          |<\/span>    105K|<\/span>00<\/span>:<\/span>00<\/span>:<\/span>17.15<\/span> |<\/span>     105K|<\/span>   1015<\/span> |<\/span>
\n|<\/span>   3<\/span> |<\/span>    TABLE ACCESS BY INDEX ROWID|<\/span> DBTW053              |<\/span>      1<\/span> |<\/span>    106K|<\/span>  4351K|<\/span>   106K  (<\/span>1<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>04<\/span> |<\/span>    105K|<\/span>00<\/span>:<\/span>00<\/span>:<\/span>17.14<\/span> |<\/span>     105K|<\/span>   1015<\/span> |<\/span>
\n|*<\/span>  4<\/span> |<\/span>     INDEX RANGE<\/span><\/a> SCAN          |<\/span> DBTW053_CATEGORY_IDX |<\/span>      1<\/span> |<\/span>    106K|<\/span>       |<\/span>   225<\/span>   (<\/span>1<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/span> |<\/span>    105K|<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.53<\/span> |<\/span>     222<\/span> |<\/span>    212<\/span> |<\/span>
\n---------------------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\n
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
\n---------------------------------------------------<\/span>
\n
\n   2<\/span> -<\/span> filter(<\/span>:<\/span>SYS_B_2>=:<\/span>SYS_B_1)<\/span>
\n   4<\/span> -<\/span> access(<\/span>"CATEGORY"<\/span>>=:<\/span>SYS_B_1 AND "CATEGORY"<\/span><=:<\/span>SYS_B_2)<\/span>
\n
\n
\n24<\/span> linhas selecionadas.<\/span>
\n
\nSQL><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

4.2) Criar uma SQL_PROFILE manualmente da consulta original<\/span><\/h2>\n

 <\/p>\n

Vamos utilizar o script coe_xfr_sql_profile.sql<\/strong><\/a> para gerar um script no diret\u00f3rio “c:\\temp” que ao ser executado vai criar uma SQL PROFILE da consulta original executada na primeira etapa.<\/span><\/p>\n

 <\/p>\n

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
<\/div><\/td>
SQL><\/span> @<\/span>coe_xfr_sql_profile 1vtkt906qhga8 1559473304<\/span>
\n
\nParameter 1<\/span>:<\/span>
\nSQL_ID (<\/span>required)<\/span>
\n
\n
\n
\nPLAN_HASH_VALUE AVG_ET_SECS
\n---------------<\/span> -----------<\/span>
\n      186720033<\/span>       1<\/span>,<\/span>673<\/span>
\n     1559473304<\/span>      17<\/span>,<\/span>247<\/span>
\n
\nParameter 2<\/span>:<\/span>
\nPLAN_HASH_VALUE (<\/span>required)<\/span>
\n
\n
\nValues passed:<\/span>
\n~~~~~~~~~~~~~
\nSQL_ID         :<\/span> "1vtkt906qhga8"<\/span>
\nPLAN_HASH_VALUE:<\/span> "1559473304"<\/span>
\n
\n
\nExecute coe_xfr_sql_profile_1vtkt906qhga8_1559473304.<\/span>sql
\non TARGET system<\/span><\/a> in order to create a custom SQL Profile
\nwith plan 1559473304<\/span> linked to adjusted sql_text.<\/span>
\n
\n
\nCOE_XFR_SQL_PROFILE completed.<\/span>
\nSQL><\/span>
\nSQL><\/span> --<\/span> Executar script gerado no diret\u00f3rio "c:\\t<\/span>emp"<\/span>
\nSQL><\/span>
\nSQL>@<\/span>c:<\/span>\\temp\\coe_xfr_sql_profile_1vtkt906qhga8_1559473304.<\/span>sql;<\/span>
\nSQL><\/span>REM
\nSQL><\/span>REM $Header<\/span>:<\/span> 215187.1<\/span> coe_xfr_sql_profile_1vtkt906qhga8_1559473304.<\/span>sql 11.<\/span>4.<\/span>1.<\/span>4 2018<\/span>\/<\/span>07<\/span>\/<\/span>05<\/span> csierra $
\nSQL><\/span>REM
\nSQL><\/span>REM Copyright (<\/span>c)<\/span> 2000<\/span>-<\/span>2010<\/span>,<\/span> Oracle Corporation.<\/span> All rights reserved.<\/span>
\nSQL><\/span>REM
\nSQL><\/span>REM AUTHOR
\nSQL><\/span>REM   carlos.<\/span>sierra@<\/span>oracle.<\/span>com
\nSQL><\/span>REM
\nSQL><\/span>REM SCRIPT
\nSQL><\/span>REM   coe_xfr_sql_profile_1vtkt906qhga8_1559473304.<\/span>sql
\nSQL><\/span>REM
\nSQL><\/span>REM DESCRIPTION
\nSQL><\/span>REM   This script is generated by coe_xfr_sql_profile.<\/span>sql
\nSQL><\/span>REM   It contains the SQL*<\/span>Plus commands to create a custom
\nSQL><\/span>REM   SQL Profile for<\/span> SQL_ID 1vtkt906qhga8 based on plan hash<\/span><\/a>
\nSQL><\/span>REM   value 1559473304<\/span>.<\/span>
\nSQL><\/span>REM   The custom SQL Profile to be created by this script
\nSQL><\/span>REM   will affect plans for<\/span> SQL commands with signature
\nSQL><\/span>REM   matching the one for<\/span> SQL Text below.<\/span>
\nSQL><\/span>REM   Review SQL Text and adjust accordingly.<\/span>
\nSQL><\/span>REM
\nSQL><\/span>REM PARAMETERS
\nSQL><\/span>REM   None.<\/span>
\nSQL><\/span>REM
\nSQL><\/span>REM EXAMPLE
\nSQL><\/span>REM   SQL><\/span> START coe_xfr_sql_profile_1vtkt906qhga8_1559473304.<\/span>sql;<\/span>
\nSQL><\/span>REM
\nSQL><\/span>REM NOTES
\nSQL><\/span>REM   1<\/span>.<\/span> Should be run as<\/span> SYSTEM<\/span><\/a> or SYSDBA.<\/span>
\nSQL><\/span>REM   2<\/span>.<\/span> User must have CREATE ANY SQL PROFILE privilege.<\/span>
\nSQL><\/span>REM   3<\/span>.<\/span> SOURCE and TARGET systems can be the same or similar.<\/span>
\nSQL><\/span>REM   4<\/span>.<\/span> To drop this custom SQL Profile after it has been created:<\/span>
\nSQL><\/span>REM      EXEC<\/span><\/a> DBMS_SQLTUNE.<\/span>DROP_SQL_PROFILE(<\/span>'coe_1vtkt906qhga8_1559473304'<\/span>)<\/span>;<\/span>
\nSQL><\/span>REM   5<\/span>.<\/span> Be aware that using DBMS_SQLTUNE requires a license
\nSQL><\/span>REM      for<\/span> the Oracle Tuning Pack<\/span><\/a>.<\/span>
\nSQL><\/span>REM
\nSQL><\/span>WHENEVER SQLERROR EXIT<\/span><\/a> SQL.<\/span>SQLCODE;<\/span>
\nSQL><\/span>REM
\nSQL><\/span>VAR<\/span> signature NUMBER;<\/span>
\nSQL><\/span>REM
\nSQL><\/span>DECLARE<\/span>
\n  2<\/span>  sql_txt CLOB;<\/span>
\n  3<\/span>  h       SYS.<\/span>SQLPROF_ATTR;<\/span>
\n  4<\/span>  BEGIN
\n  5<\/span>  sql_txt :=<\/span> q'[
\n  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
\n  7  ]'<\/span>;<\/span>
\n  8<\/span>  h :=<\/span> SYS.<\/span>SQLPROF_ATTR(<\/span>
\n  9<\/span>  q'[BEGIN_OUTLINE_DATA]'<\/span>,<\/span>
\n 10<\/span>  q'[IGNORE_OPTIM_EMBEDDED_HINTS]'<\/span>,<\/span>
\n 11<\/span>  q'[OPTIMIZER_FEATURES_ENABLE('<\/span>11.<\/span>2.<\/span>0.<\/span>4')]'<\/span>,<\/span>
\n 12<\/span>  q'[DB_VERSION('<\/span>11.<\/span>2.<\/span>0.<\/span>4')]'<\/span>,<\/span>
\n 13<\/span>  q'[ALL_ROWS]'<\/span>,<\/span>
\n 14<\/span>  q'[OUTLINE_LEAF(@"SEL$1")]'<\/span>,<\/span>
\n 15<\/span>  q'[INDEX_RS_ASC(@"SEL$1" "A"@"SEL$1" ("DBTW053"."CATEGORY"))]'<\/span>,<\/span>
\n 16<\/span>  q'[USE_HASH_AGGREGATION(@"SEL$1")]'<\/span>,<\/span>
\n 17<\/span>  q'[END_OUTLINE_DATA]'<\/span>)<\/span>;<\/span>
\n 18<\/span>  :<\/span>signature :=<\/span> DBMS_SQLTUNE.<\/span>SQLTEXT_TO_SIGNATURE(<\/span>sql_txt)<\/span>;<\/span>
\n 19<\/span>  DBMS_SQLTUNE.<\/span>IMPORT_SQL_PROFILE (<\/span>
\n 20<\/span>  sql_text    =><\/span> sql_txt,<\/span>
\n 21<\/span>  profile     =><\/span> h,<\/span>
\n 22<\/span>  name        =><\/span> 'coe_1vtkt906qhga8_1559473304'<\/span>,<\/span>
\n 23<\/span>  description =><\/span> 'coe 1vtkt906qhga8 1559473304 '<\/span>||:<\/span>signature||<\/span>''<\/span>,<\/span>
\n 24<\/span>  category    =><\/span> 'DEFAULT'<\/span>,<\/span>
\n 25<\/span>  validate    =><\/span> TRUE<\/span>,<\/span>
\n 26<\/span>  replace     =><\/span> TRUE<\/span>,<\/span>
\n 27<\/span>  force_match =><\/span> FALSE<\/span> \/* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) *\/<\/span> )<\/span>;<\/span>
\n 28<\/span>  END<\/span><\/a>;<\/span>
\n 29<\/span>  \/<\/span>
\n
\nProcedimento PL\/<\/span>SQL conclu\u00eddo<\/span> com sucesso.<\/span>
\n
\nSQL><\/span>WHENEVER SQLERROR CONTINUE<\/span>
\nSQL><\/span>SET ECHO<\/span> OFF;<\/span>
\n
\n            SIGNATURE
\n---------------------<\/span>
\n 14783374037725715653<\/span>
\n
\n
\n...<\/span> manual custom SQL Profile has been created
\n
\n
\nCOE_XFR_SQL_PROFILE_1vtkt906qhga8_1559473304 completed
\nSQL><\/span>
\n
\nSQL><\/span>
\nSQL><\/span>set lines 200<\/span> pages 100<\/span>
\nSQL><\/span>select name,<\/span> created,<\/span> type,<\/span> status from DBA_SQL_PROFILES;<\/span>
\n
\nNAME                           CREATED                                                                     TYPE    STATUS
\n------------------------------<\/span> ---------------------------------------------------------------------------<\/span> -------<\/span> ----------<\/span>
\ncoe_1vtkt906qhga8_1559473304   05<\/span>\/<\/span>07<\/span>\/<\/span>18<\/span> 22<\/span>:<\/span>01<\/span>:<\/span>20<\/span>,<\/span>000000<\/span>                                                    MANUAL  ENABLED
\n
\nSQL><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

4.3) Executar a consulta sem o HINT<\/span><\/h2>\n

 <\/p>\n

Vamos executar a consulta novamente sem o HINT para obtermos as OUTLINES do plano de execu\u00e7\u00e3o que tem melhor desempenho.<\/span><\/p>\n

 <\/p>\n

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
<\/div><\/td>
SQL><\/span>SELECT \/* DBTW-053.2 *\/<\/span> category,<\/span> descr,<\/span> count<\/span><\/a>(<\/span>1<\/span>)<\/span> qtde FROM dbtw053 A WHERE category BETWEEN 455<\/span> AND 559<\/span> GROUP BY category,<\/span> descr;<\/span>
\n
\n  CATEGORY DESCR                                       QTDE
\n----------<\/span> -------------------------------------<\/span> ----------<\/span>
\n       460<\/span> DBTimeWizard -<\/span> Performance and Tuning       1000<\/span>
\n       472<\/span> DBTimeWizard -<\/span> Performance and Tuning       1000<\/span>
\n  .<\/span>
\n  .<\/span>
\n  .<\/span>
\n       559<\/span> DBTimeWizard -<\/span> Performance and Tuning       1000<\/span>
\n
\n105<\/span> linhas selecionadas.<\/span>
\n
\nSQL><\/span>
\nSQL>--<\/span> Recupera o SQL_ID e o CHILD_NUMBER  da consulta executada
\nSQL><\/span>
\nSQL><\/span>column sql_id new_value m_sql_id
\nSQL><\/span>column child_number new_value m_child_no
\nSQL><\/span>
\nSQL><\/span>SELECT sql_id,<\/span> child_number
\n  2<\/span>    FROM v$sql<\/span>
\n  3<\/span>   WHERE sql_text LIKE '%DBTW-053.2%'<\/span>
\n  4<\/span>     AND sql_text NOT LIKE '%v$sql%'<\/span>;<\/span>
\n
\nSQL_ID        CHILD_NUMBER
\n-------------<\/span> ------------<\/span>
\naa2v8aq8t17w2            0<\/span>
\n
\nSQL><\/span>
\nSQL>--<\/span> Gera o relatorio do<\/span> plano de execucao da consulta
\nSQL><\/span>
\nSQL><\/span>SELECT *<\/span> FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'&m_sql_id'<\/span>,&<\/span>m_child_no,<\/span>'advanced iostats last'<\/span>)<\/span>)<\/span>;<\/span>
\nantigo   1<\/span>:<\/span> SELECT *<\/span> FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'&m_sql_id'<\/span>,&<\/span>m_child_no,<\/span>'advanced iostats last'<\/span>)<\/span>)<\/span>
\nnovo   1<\/span>:<\/span> SELECT *<\/span> FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'aa2v8aq8t17w2'<\/span>,<\/span>         0<\/span>,<\/span>'advanced iostats last'<\/span>)<\/span>)<\/span>
\n
\nPLAN_TABLE_OUTPUT
\n-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\nSQL_ID  aa2v8aq8t17w2,<\/span> child number 0<\/span>
\n-------------------------------------<\/span>
\nSELECT \/* DBTW-053.2 *\/<\/span> category,<\/span> descr,<\/span> count<\/span><\/a>(<\/span>:<\/span>"SYS_B_0"<\/span>)<\/span> qtde FROM
\ndbtw053 A WHERE category BETWEEN :<\/span>"SYS_B_1"<\/span> AND :<\/span>"SYS_B_2"<\/span> GROUP BY
\ncategory,<\/span> descr
\n
\nPlan hash<\/span><\/a> value:<\/span> 186720033<\/span>
\n
\n----------------------------------------------------------------------------------------------------------------------------------<\/span>
\n|<\/span> Id  |<\/span> Operation           |<\/span> Name    |<\/span> Starts |<\/span> E-<\/span>Rows |<\/span>E-<\/span>Bytes|<\/span> Cost (<\/span>%<\/span>CPU)<\/span>|<\/span> E-<\/span>Time<\/span><\/a>   |<\/span> A-<\/span>Rows |<\/span>   A-<\/span>Time<\/span><\/a>   |<\/span> Buffers |<\/span> Reads  |<\/span>
\n----------------------------------------------------------------------------------------------------------------------------------<\/span>
\n|<\/span>   0<\/span> |<\/span> SELECT STATEMENT    |<\/span>         |<\/span>      1<\/span> |<\/span>        |<\/span>       |<\/span>   297<\/span> (<\/span>100<\/span>)<\/span>|<\/span>          |<\/span>    105<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>10.42<\/span> |<\/span>    8325<\/span> |<\/span>   1747<\/span> |<\/span>
\n|<\/span>   1<\/span> |<\/span>  HASH<\/span><\/a> GROUP BY      |<\/span>         |<\/span>      1<\/span> |<\/span>     75<\/span> |<\/span>  3150<\/span> |<\/span>   297<\/span>  (<\/span>11<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/span> |<\/span>    105<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>10.42<\/span> |<\/span>    8325<\/span> |<\/span>   1747<\/span> |<\/span>
\n|*<\/span>  2<\/span> |<\/span>   FILTER            |<\/span>         |<\/span>      1<\/span> |<\/span>        |<\/span>       |<\/span>            |<\/span>          |<\/span>    105K|<\/span>00<\/span>:<\/span>00<\/span>:<\/span>10.40<\/span> |<\/span>    8325<\/span> |<\/span>   1747<\/span> |<\/span>
\n|*<\/span>  3<\/span> |<\/span>    TABLE ACCESS FULL|<\/span> DBTW053 |<\/span>      1<\/span> |<\/span>    106K|<\/span>  4351K|<\/span>   290<\/span>   (<\/span>9<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/span> |<\/span>    105K|<\/span>00<\/span>:<\/span>00<\/span>:<\/span>10.39<\/span> |<\/span>    8325<\/span> |<\/span>   1747<\/span> |<\/span>
\n----------------------------------------------------------------------------------------------------------------------------------<\/span>
\n
\nQuery Block Name \/<\/span> Object Alias (<\/span>identified by operation id)<\/span>:<\/span>
\n-------------------------------------------------------------<\/span>
\n
\n   1<\/span> -<\/span> SEL$1<\/span>
\n   3<\/span> -<\/span> SEL$1<\/span> \/<\/span> A@<\/span>SEL$1<\/span>
\n
\nOutline Data
\n-------------<\/span>
\n
\n  \/*+
\n      BEGIN_OUTLINE_DATA
\n      IGNORE_OPTIM_EMBEDDED_HINTS
\n      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
\n      DB_VERSION('11.2.0.4')
\n      ALL_ROWS
\n      OUTLINE_LEAF(@"SEL$1")
\n      FULL(@"SEL$1" "A"@"SEL$1")
\n      USE_HASH_AGGREGATION(@"SEL$1")
\n      END_OUTLINE_DATA
\n  *\/<\/span>
\n
\nPeeked Binds (<\/span>identified by position)<\/span>:<\/span>
\n--------------------------------------<\/span>
\n
\n   2<\/span> -<\/span> :<\/span>SYS_B_1 (<\/span>NUMBER)<\/span>:<\/span> 455<\/span>
\n   3<\/span> -<\/span> :<\/span>SYS_B_2 (<\/span>NUMBER)<\/span>:<\/span> 559<\/span>
\n
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
\n---------------------------------------------------<\/span>
\n
\n   2<\/span> -<\/span> filter(<\/span>:<\/span>SYS_B_2>=:<\/span>SYS_B_1)<\/span>
\n   3<\/span> -<\/span> filter(<\/span>(<\/span>"CATEGORY"<\/span>>=:<\/span>SYS_B_1 AND "CATEGORY"<\/span><=:<\/span>SYS_B_2)<\/span>)<\/span>
\n
\nColumn Projection Information (<\/span>identified by operation id)<\/span>:<\/span>
\n-----------------------------------------------------------<\/span>
\n
\n   1<\/span> -<\/span> "CATEGORY"<\/span>[<\/span>NUMBER,<\/span>22<\/span>]<\/span>,<\/span> "DESCR"<\/span>[<\/span>CHARACTER,<\/span>37<\/span>]<\/span>,<\/span> COUNT<\/span><\/a>(<\/span>:<\/span>SYS_B_0)<\/span>[<\/span>22<\/span>]<\/span>
\n   2<\/span> -<\/span> "CATEGORY"<\/span>[<\/span>NUMBER,<\/span>22<\/span>]<\/span>,<\/span> "DESCR"<\/span>[<\/span>CHARACTER,<\/span>37<\/span>]<\/span>
\n   3<\/span> -<\/span> "CATEGORY"<\/span>[<\/span>NUMBER,<\/span>22<\/span>]<\/span>,<\/span> "DESCR"<\/span>[<\/span>CHARACTER,<\/span>37<\/span>]<\/span>
\n
\n
\n57<\/span> linhas selecionadas.<\/span>
\n
\nSQL><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

4.4) Alterar a OUTLINE da SQL_PROFILE<\/span><\/h2>\n

 <\/p>\n

Para que a SQL_PROFILE gerada na etapa 4.2 desta simula\u00e7\u00e3o force a utiliza\u00e7\u00e3o da opera\u00e7\u00e3o TABLE FULL SCAN no plano de execu\u00e7\u00e3o precisamos alterar a OUTLINE<\/span><\/p>\n

DE:<\/strong> INDEX_RS_ASC(@”SEL$1″ “A”@”SEL$1” (“DBTW053″.”CATEGORY”))<\/span>
\nPARA:<\/strong> FULL(@”SEL$1″ “A”@”SEL$1”)<\/span><\/p>\n

Esta altera\u00e7\u00e3o ser\u00e1 realizada utilizando o script fix_sql_profile_hint.sql<\/strong><\/a> e para verificar o resultado da altera\u00e7\u00e3o vamos utilizar o script sql_profile_hints11.sql<\/strong><\/a>.<\/span><\/p>\n

 <\/p>\n

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
<\/div><\/td>
SQL><\/span>conn sys\/<\/span>oracle@<\/span>lab11 as<\/span> sysdba
\nConnected.<\/span>
\nSQL><\/span>
\nSQL><\/span> @<\/span>fix_sql_profile_hint
\nEnter value for<\/span> profile_name:<\/span> coe_1vtkt906qhga8_1559473304
\nEnter value for<\/span> bad_hint:<\/span> INDEX_RS_ASC(<\/span>@<\/span>"SEL$1<\/span>"<\/span> "A"<\/span>@<\/span>"SEL$1<\/span>"<\/span> (<\/span>"DBTW053"<\/span>.<\/span>"CATEGORY"<\/span>)<\/span>)<\/span>
\nEnter value for<\/span> good_hint:<\/span> FULL(<\/span>@<\/span>"SEL$1<\/span>"<\/span> "A"<\/span>@<\/span>"SEL$1<\/span>"<\/span>)<\/span>
\n
\nProcedimento PL\/<\/span>SQL conclu\u00eddo<\/span> com sucesso.<\/span>
\n
\nSQL><\/span>
\nSQL><\/span> @<\/span>sql_profile_hints11
\nInforme o valor para name:<\/span> coe_1vtkt906qhga8_1559473304
\n
\nHINT
\n-----------------------------------------------------------------------------------------------<\/span>
\nBEGIN_OUTLINE_DATA
\nIGNORE_OPTIM_EMBEDDED_HINTS
\nOPTIMIZER_FEATURES_ENABLE(<\/span>'11.2.0.4'<\/span>)<\/span>
\nDB_VERSION(<\/span>'11.2.0.4'<\/span>)<\/span>
\nALL_ROWS
\nOUTLINE_LEAF(<\/span>@<\/span>"SEL$1<\/span>"<\/span>)<\/span>
\nFULL(<\/span>@<\/span>"SEL$1<\/span>"<\/span> "A"<\/span>@<\/span>"SEL$1<\/span>"<\/span>)<\/span>
\nUSE_HASH_AGGREGATION(<\/span>@<\/span>"SEL$1<\/span>"<\/span>)<\/span>
\nEND_OUTLINE_DATA
\n
\n9<\/span> linhas selecionadas.<\/span>
\n
\nSQL><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

4.5) Executar a consulta original novamente<\/span><\/h2>\n

 <\/p>\n

Com a SQL_PROFILE devidamente ajustada vamos executar a consulta original e verificar que plano de execu\u00e7\u00e3o o Otimizador vai escolher.<\/span><\/p>\n

 <\/p>\n

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
<\/div><\/td>
SQL><\/span> ALTER SESSION SET optimizer_use_sql_plan_baselines =<\/span> FALSE<\/span>;<\/span>
\n
\nSess\u00e3o alterada.<\/span>
\n
\nSQL><\/span>
\nSQL><\/span> ALTER SESSION SET statistics_level =<\/span> ALL;<\/span>
\n
\nSess\u00e3o alterada.<\/span>
\n
\nSQL><\/span>
\nSQL><\/span> SELECT \/*+ INDEX(A dbtw053_category_idx) *\/<\/span> \/* DBTW-053.1 *\/<\/span> category,<\/span> descr,<\/span> count<\/span><\/a>(<\/span>1<\/span>)<\/span> qtde FROM dbtw053 A WHERE category BETWEEN 455<\/span> AND 559<\/span> GROUP BY category,<\/span> descr;<\/span>
\n
\n  CATEGORY DESCR                                       QTDE
\n----------<\/span> -------------------------------------<\/span> ----------<\/span>
\n       460<\/span> DBTimeWizard -<\/span> Performance and Tuning       1000<\/span>
\n       472<\/span> DBTimeWizard -<\/span> Performance and Tuning       1000<\/span>
\n  .<\/span>
\n  .<\/span>
\n  .<\/span>
\n       559<\/span> DBTimeWizard -<\/span> Performance and Tuning       1000<\/span>
\n
\n105<\/span> linhas selecionadas.<\/span>
\n
\nSQL><\/span>
\nSQL><\/span> --<\/span> Recupera o SQL_ID e o CHILD_NUMBER  da consulta executada
\nSQL><\/span>
\nSQL><\/span> column sql_id new_value m_sql_id
\nSQL><\/span> column child_number new_value m_child_no
\nSQL><\/span>
\nSQL><\/span> SELECT sql_id,<\/span> child_number
\n  2<\/span>    FROM v$sql<\/span>
\n  3<\/span>   WHERE sql_text LIKE '%DBTW-053.1%'<\/span>
\n  4<\/span>     AND sql_text NOT LIKE '%v$sql%'<\/span>;<\/span>
\n
\nSQL_ID        CHILD_NUMBER
\n-------------<\/span> ------------<\/span>
\n1vtkt906qhga8            0<\/span>
\n1vtkt906qhga8            1<\/span>
\n
\nSQL><\/span>
\nSQL><\/span>
\nSQL><\/span> --<\/span> Gera o relatorio do<\/span> plano de execucao da consulta
\nSQL><\/span>
\nSQL><\/span> SELECT *<\/span> FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'&m_sql_id'<\/span>,&<\/span>m_child_no,<\/span>'typical iostats last'<\/span>)<\/span>)<\/span>;<\/span>
\n
\nPLAN_TABLE_OUTPUT
\n-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\nSQL_ID  1vtkt906qhga8,<\/span> child number 1<\/span>
\n-------------------------------------<\/span>
\nSELECT \/*+ INDEX(A dbtw053_category_idx) *\/<\/span> \/* DBTW-053.1 *\/<\/span> category,<\/span>
\ndescr,<\/span> count<\/span><\/a>(<\/span>:<\/span>"SYS_B_0"<\/span>)<\/span> qtde FROM dbtw053 A WHERE category BETWEEN
\n:<\/span>"SYS_B_1"<\/span> AND :<\/span>"SYS_B_2"<\/span> GROUP BY category,<\/span> descr
\n
\nPlan hash<\/span><\/a> value:<\/span> 186720033<\/span>
\n
\n----------------------------------------------------------------------------------------------------------------------------------<\/span>
\n|<\/span> Id  |<\/span> Operation           |<\/span> Name    |<\/span> Starts |<\/span> E-<\/span>Rows |<\/span>E-<\/span>Bytes|<\/span> Cost (<\/span>%<\/span>CPU)<\/span>|<\/span> E-<\/span>Time<\/span><\/a>   |<\/span> A-<\/span>Rows |<\/span>   A-<\/span>Time<\/span><\/a>   |<\/span> Buffers |<\/span> Reads  |<\/span>
\n----------------------------------------------------------------------------------------------------------------------------------<\/span>
\n|<\/span>   0<\/span> |<\/span> SELECT STATEMENT    |<\/span>         |<\/span>      1<\/span> |<\/span>        |<\/span>       |<\/span>   297<\/span> (<\/span>100<\/span>)<\/span>|<\/span>          |<\/span>    105<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>04.26<\/span> |<\/span>    8319<\/span> |<\/span>   8316<\/span> |<\/span>
\n|<\/span>   1<\/span> |<\/span>  HASH<\/span><\/a> GROUP BY      |<\/span>         |<\/span>      1<\/span> |<\/span>     75<\/span> |<\/span>  3150<\/span> |<\/span>   297<\/span>  (<\/span>11<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/span> |<\/span>    105<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>04.26<\/span> |<\/span>    8319<\/span> |<\/span>   8316<\/span> |<\/span>
\n|*<\/span>  2<\/span> |<\/span>   FILTER            |<\/span>         |<\/span>      1<\/span> |<\/span>        |<\/span>       |<\/span>            |<\/span>          |<\/span>    105K|<\/span>00<\/span>:<\/span>00<\/span>:<\/span>04.23<\/span> |<\/span>    8319<\/span> |<\/span>   8316<\/span> |<\/span>
\n|*<\/span>  3<\/span> |<\/span>    TABLE ACCESS FULL|<\/span> DBTW053 |<\/span>      1<\/span> |<\/span>    106K|<\/span>  4351K|<\/span>   290<\/span>   (<\/span>9<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/span> |<\/span>    105K|<\/span>00<\/span>:<\/span>00<\/span>:<\/span>04.22<\/span> |<\/span>    8319<\/span> |<\/span>   8316<\/span> |<\/span>
\n----------------------------------------------------------------------------------------------------------------------------------<\/span>
\n
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
\n---------------------------------------------------<\/span>
\n
\n   2<\/span> -<\/span> filter(<\/span>:<\/span>SYS_B_2>=:<\/span>SYS_B_1)<\/span>
\n   3<\/span> -<\/span> filter(<\/span>(<\/span>"CATEGORY"<\/span>>=:<\/span>SYS_B_1 AND "CATEGORY"<\/span><=:<\/span>SYS_B_2)<\/span>)<\/span>
\n
\nNote
\n-----<\/span>
\n   -<\/span> SQL profile coe_1vtkt906qhga8_1559473304 used for<\/span> this statement
\n
\n
\n27<\/span> linhas selecionadas.<\/span>
\n
\nSQL><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Analisando o plano de execu\u00e7\u00e3o verificamos que o Otimizador escolheu fazer uma opera\u00e7\u00e3o TABLE FULL SCAN apesar da consulta executada estar com o HINT, tamb\u00e9m podemos observar na se\u00e7\u00e3o NOTE a mensagem: <\/span><\/p>\n

[SQL profile coe_1vtkt906qhga8_1559473304 used for this statement]<\/span><\/p>\n

Conclus\u00e3o<\/span><\/h1>\n

Existem algumas op\u00e7\u00f5es para modificar o plano de execu\u00e7\u00e3o sem alterar o c\u00f3digo da aplica\u00e7\u00e3o, a melhor op\u00e7\u00e3o vai depender do tipo de altera\u00e7\u00e3o necess\u00e1ria e do licenciamento da base que estivermos trabalhando, no quadro abaixo podemos visualizar facilmente o tipo de licen\u00e7a necess\u00e1ria para cada uma das t\u00e9cnicas apresentadas neste artigo.<\/span><\/p>\n

 <\/p>\n

\"Licen\u00e7a\"<\/a><\/p>\n

 <\/p>\n

Refer\u00eancias<\/span><\/h1>\n

 <\/p>\n

https:\/\/gavinsoorma.com\/<\/a><\/span><\/strong><\/p>\n

http:\/\/kerryosborne.oracle-guy.com\/<\/a><\/span><\/strong><\/p>\n

https:\/\/carlos-sierra.net\/<\/a><\/span><\/strong><\/p>\n

 
\n 
\n 
\n\"Promo-D75L\"<\/a><\/p>\n

\"video-02\"<\/a>
\n 
\n 
\n 
\n 
\n 
\n 
\n 
\n 
\n 
\n 
\n 
\n <\/p>\n","protected":false},"excerpt":{"rendered":"

Para melhorar de desempenho das aplica\u00e7\u00f5es muitas vezes precisamos fazer alguns ajustes nas instru\u00e7\u00f5es SQL e em algumas situa\u00e7\u00f5es n\u00e3o podemos alterar o c\u00f3digo pois a aplica\u00e7\u00e3o \u00e9 de terceiros ou apesar de ser um desenvolvimento interno da empresa os prazos para altera\u00e7\u00e3o s\u00e3o muito longos. J\u00e1 faz algum tempo que modificar um plano de execu\u00e7\u00e3o sem alterar a instru\u00e7\u00e3o<\/p>\n","protected":false},"author":2,"featured_media":1688,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[152,156,115],"tags":[200,6,201,168,177,36,82,10,203,11,202,153,136,157,118],"class_list":["post-1690","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-sql-patch","category-sql-plan-management","category-sql-profile","tag-advanced-query-rewrite","tag-cbo","tag-dbms_advanced_rewrite","tag-dbms_sqldiag","tag-dbms_sqldiag_internal","tag-execution-plan","tag-optimizer","tag-otimizador","tag-outline","tag-plano-de-execucao","tag-spm","tag-sql-patch","tag-sql-plan-baseline","tag-sql-plan-management","tag-sql-profile"],"aioseo_notices":[],"_links":{"self":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/1690","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/comments?post=1690"}],"version-history":[{"count":44,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/1690\/revisions"}],"predecessor-version":[{"id":2373,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/1690\/revisions\/2373"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/media\/1688"}],"wp:attachment":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/media?parent=1690"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/categories?post=1690"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/tags?post=1690"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}