{"id":849,"date":"2016-10-02T22:29:56","date_gmt":"2016-10-03T01:29:56","guid":{"rendered":"http:\/\/dbtimewizard.com.br\/blog\/?p=849"},"modified":"2019-11-11T18:54:09","modified_gmt":"2019-11-11T21:54:09","slug":"sql-profile-como-sobreviver-sem-ela","status":"publish","type":"post","link":"http:\/\/dbtimewizard.com.br\/blog\/sql-profile-como-sobreviver-sem-ela\/","title":{"rendered":"SQL Profile: Como sobreviver sem ela?"},"content":{"rendered":"

Em nossas tarefas de “SQL Tuning” muitas vezes n\u00e3o temos como alterar o c\u00f3digo da instru\u00e7\u00e3o SQL e nesses casos precisamos utilizar alguns recursos do banco de dados que permitem influenciar o Otimizador na montagem do plano de execu\u00e7\u00e3o sem a necessidade de incluir um “HINT” no c\u00f3digo da instru\u00e7\u00e3o SQL. Um desses recursos \u00e9 a “SQL_Profile”, porem para utiliza-la necessitamos da “Option Tuning Pack”, essa restri\u00e7\u00e3o j\u00e1 foi abordada num artigo<\/a><\/strong> publicado anteriormente. Nesse artigo vamos mostrar como podemos alterar um plano de execu\u00e7\u00e3o sem alterar o c\u00f3digo da instru\u00e7\u00e3o SQL, utilizando o recurso “SQL Plan Management” que n\u00e3o requer “Options” para sua utiliza\u00e7\u00e3o.<\/span><\/p>\n

O recurso “SQL Plan Management” (SPM) foi concebido para garantir a estabilidade dos planos de execu\u00e7\u00e3o e impedir que o Otimizador utilize um plano de execu\u00e7\u00e3o com tempo de resposta maior que os planos que j\u00e1 vinham sendo utilizados, para impedir a regress\u00e3o de um plano de execu\u00e7\u00e3o o SPM s\u00f3 aceita um plano de execu\u00e7\u00e3o depois que ele for validado levando em considera\u00e7\u00e3o o tempo de execu\u00e7\u00e3o desse novo plano. Sem o SPM o Otimizador faz a escolha do plano de execu\u00e7\u00e3o pelo CUSTO do plano que em algumas situa\u00e7\u00f5es n\u00e3o representa a melhor op\u00e7\u00e3o.<\/span><\/p>\n

O SPM possui v\u00e1rias “procedures” para o seu gerenciamento, para demonstrar o que foi proposto inicialmente vamos utilizar a “procedure DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE” que permite a cria\u00e7\u00e3o de um “SQL PLAN” manualmente.<\/span><\/p>\n

Simulando a altera\u00e7\u00e3o de um plano de execu\u00e7\u00e3o<\/span><\/h2>\n

Para realizar a simula\u00e7\u00e3o vamos executar os seguintes passos:<\/span><\/p>\n

1) Criar uma tabela com um \u00edndice e coletar as estat\u00edsticas.<\/span>
\n2) Executar uma consulta nessa tabela utilizando o \u00edndice.<\/span>
\n3) Executar a mesma consulta com um “HINT” para for\u00e7ar um “FULL TABLE SCAN” na tabela.<\/span>
\n4) Criar um “SQL PLAN” manualmente para a consulta original for\u00e7ando o “FULL TABLE SCAN”.<\/span>
\n5) Executar a consulta original (sem HINT) que utilizar\u00e1 “SQL PLAN” com “FULL TABLE SCAN”.<\/span><\/p>\n

1) Criando a tabela<\/span><\/h3>\n

 <\/p>\n

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
<\/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
\nSQL ><\/span> CREATE TABLE MY_OBJECTS AS<\/span> SELECT *<\/span> FROM DBA_OBJECTS;<\/span>
\n
\nTable created.<\/span>
\n
\nSQL ><\/span> CREATE INDEX IDX_MY_OBJECTS ON MY_OBJECTS(<\/span>OBJECT_TYPE)<\/span>;<\/span>
\n
\nIndex created.<\/span>
\n
\nSQL ><\/span> exec<\/span><\/a> dbms_stats.<\/span>GATHER_TABLE_STATS(<\/span>ownname =><\/span> USER ,<\/span>tabname =><\/span>'MY_OBJECTS'<\/span>,<\/span>method_opt=><\/span>'FOR ALL INDEXED COLUMNS SIZE 254'<\/span>)<\/span>;<\/span>
\n
\nPL\/<\/span>SQL procedure successfully completed.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

2) Consulta com plano escolhido pelo Otimizador<\/span><\/h3>\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
<\/div><\/td>
SQL ><\/span> ALTER SESSION SET statistics_level=<\/span>ALL;<\/span>
\n
\nSession altered.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> variable B1 VARCHAR2(<\/span>32<\/span>)<\/span>
\nSQL ><\/span>
\nSQL ><\/span> exec<\/span><\/a> :<\/span>B1 :=<\/span> 'LOB PARTITION'<\/span>;<\/span>
\n
\nPL\/<\/span>SQL procedure successfully completed.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> SELECT \/* DBTW001 *\/<\/span> OBJECT_NAME FROM MY_OBJECTS WHERE OBJECT_TYPE =<\/span> :<\/span>B1;<\/span>
\n
\nOBJECT_NAME
\n--------------------------------------------------------------------------------------------------------------------------------<\/span>
\nSYS_LOB0000011479C00008$$
\n
\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 '%DBTW001%'<\/span>
\n  4<\/span>     AND sql_text NOT LIKE '%v$sql%'<\/span>;<\/span>
\n
\nSQL_ID        CHILD_NUMBER
\n-------------<\/span> ------------<\/span>
\n6k0ys0f922fbm            0<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> SELECT *<\/span>
\n  2<\/span>    FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'&m_sql_id'<\/span>,&<\/span>m_child_no,<\/span>'allstats last'<\/span>)<\/span>)<\/span>;<\/span>
\nold   2<\/span>:<\/span>   FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'&m_sql_id'<\/span>,&<\/span>m_child_no,<\/span>'allstats last'<\/span>)<\/span>)<\/span>
\nnew<\/span>   2<\/span>:<\/span>   FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'6k0ys0f922fbm'<\/span>,<\/span>         0<\/span>,<\/span>'allstats last'<\/span>)<\/span>)<\/span>
\n
\nPLAN_TABLE_OUTPUT
\n--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\nSQL_ID  6k0ys0f922fbm,<\/span> child number 0<\/span>
\n-------------------------------------<\/span>
\nSELECT \/* DBTW001 *\/<\/span> OBJECT_NAME FROM MY_OBJECTS WHERE OBJECT_TYPE =<\/span> :<\/span>B1
\n
\nPlan hash<\/span><\/a> value:<\/span> 1142617335<\/span>
\n
\n--------------------------------------------------------------------------------------------------------<\/span>
\n|<\/span> Id  |<\/span> Operation                   |<\/span> Name           |<\/span> Starts |<\/span> E-<\/span>Rows |<\/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>      1<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>       4<\/span> |<\/span>
\n|<\/span>   1<\/span> |<\/span>  TABLE ACCESS BY INDEX ROWID|<\/span> MY_OBJECTS     |<\/span>      1<\/span> |<\/span>     12<\/span> |<\/span>      1<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>       4<\/span> |<\/span>
\n|*<\/span>  2<\/span> |<\/span>   INDEX RANGE<\/span><\/a> SCAN          |<\/span> IDX_MY_OBJECTS |<\/span>      1<\/span> |<\/span>     12<\/span> |<\/span>      1<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>       3<\/span> |<\/span>
\n--------------------------------------------------------------------------------------------------------<\/span>
\n
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
\n---------------------------------------------------<\/span>
\n
\n   2<\/span> -<\/span> access(<\/span>"OBJECT_TYPE"<\/span>=:<\/span>B1)<\/span>
\n
\n
\n19<\/span> rows selected.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Observando o plano de execu\u00e7\u00e3o verificamos que o Otimizador escolheu acessar os registros da tabela utilizando o \u00edndice.<\/span><\/p>\n

3) Consulta for\u00e7ando “FULL TABLE SCAN”<\/span><\/h3>\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
<\/div><\/td>
SQL ><\/span> SELECT \/*+ full(MY_OBJECTS) DBTW002 *\/<\/span>  OBJECT_NAME FROM MY_OBJECTS WHERE OBJECT_TYPE =<\/span> :<\/span>B1;<\/span>
\n
\nOBJECT_NAME
\n--------------------------------------------------------------------------------------------------------------------------------<\/span>
\nSYS_LOB0000011479C00008$$
\n
\nSQL ><\/span>
\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 '%DBTW002%'<\/span>
\n  4<\/span>     AND sql_text NOT LIKE '%v$sql%'<\/span>;<\/span>
\n
\nSQL_ID        CHILD_NUMBER
\n-------------<\/span> ------------<\/span>
\ncgsc57rkrxkfh            0<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> SELECT *<\/span>
\n  2<\/span>    FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'&m_sql_id'<\/span>,&<\/span>m_child_no,<\/span>'allstats last'<\/span>)<\/span>)<\/span>;<\/span>
\nold   2<\/span>:<\/span>   FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'&m_sql_id'<\/span>,&<\/span>m_child_no,<\/span>'allstats last'<\/span>)<\/span>)<\/span>
\nnew<\/span>   2<\/span>:<\/span>   FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'cgsc57rkrxkfh'<\/span>,<\/span>         0<\/span>,<\/span>'allstats last'<\/span>)<\/span>)<\/span>
\n
\nPLAN_TABLE_OUTPUT
\n--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\nSQL_ID  cgsc57rkrxkfh,<\/span> child number 0<\/span>
\n-------------------------------------<\/span>
\nSELECT \/*+ full(MY_OBJECTS) DBTW002 *\/<\/span>  OBJECT_NAME FROM MY_OBJECTS
\nWHERE OBJECT_TYPE =<\/span> :<\/span>B1
\n
\nPlan hash<\/span><\/a> value:<\/span> 880823944<\/span>
\n
\n------------------------------------------------------------------------------------------<\/span>
\n|<\/span> Id  |<\/span> Operation         |<\/span> Name       |<\/span> Starts |<\/span> E-<\/span>Rows |<\/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>      1<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>    1899<\/span> |<\/span>
\n|*<\/span>  1<\/span> |<\/span>  TABLE ACCESS FULL|<\/span> MY_OBJECTS |<\/span>      1<\/span> |<\/span>     12<\/span> |<\/span>      1<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>    1899<\/span> |<\/span>
\n------------------------------------------------------------------------------------------<\/span>
\n
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
\n---------------------------------------------------<\/span>
\n
\n   1<\/span> -<\/span> filter(<\/span>"OBJECT_TYPE"<\/span>=:<\/span>B1)<\/span>
\n
\n
\n19<\/span> rows selected.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

A utiliza\u00e7\u00e3o do “HINT FULL()” influencia o Otimizador na\u00a0montagem do plano de execu\u00e7\u00e3o que far\u00e1 a leitura de todos os registros da tabela.<\/span><\/p>\n

4) Criar SQL PLAN for\u00e7ando FTS<\/span><\/h3>\n

Para esta simula\u00e7\u00e3o podemos deixar os par\u00e2metros do banco referentes ao “SQL PLAN MANAGEMENT” com seus respectivos valores padr\u00f5es. Consultando a “library cache” encontramos as duas vers\u00f5es de consultas executadas.<\/span>
\n <\/p>\n

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
<\/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> col sql_text for<\/span> a60
\nSQL ><\/span>
\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 '%DBTW00%'<\/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>
\ncgsc57rkrxkfh            0<\/span>       880823944<\/span> SELECT \/*+ full(MY_OBJECTS) DBTW002 *\/<\/span>  OBJECT_NAME FROM MY_
\n6k0ys0f922fbm            0<\/span>      1142617335<\/span> SELECT \/* DBTW001 *\/<\/span> OBJECT_NAME FROM MY_OBJECTS WHERE OBJEC
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Vamos executar o script “cspb.sql” para criar um “SQL PLAN” que for\u00e7ar\u00e1 a execu\u00e7\u00e3o do plano com FTS sempre que a consulta original for utilizada. (O c\u00f3digo fonte desse script esta dispon\u00edvel no final desse artigo) <\/span><\/p>\n

 <\/p>\n

1
2
3
4
5
6
7
8
9
10
11
<\/div><\/td>
SQL ><\/span> @<\/span>cspb.<\/span>sql;<\/span>
\nSQL_ID     Consulta original :<\/span> 6k0ys0f922fbm
\nCHILD#     Consulta original : 0
\n<\/span>SQL_ID     Consulta alterada :<\/span> cgsc57rkrxkfh
\nPLAN_HASH# Consulta alterada : 880823944
\n<\/span>SELECT \/* DBTW001 *\/<\/span> OBJECT_NAME FROM MY_OBJECTS WHERE OBJECT_TYPE =<\/span> :<\/span>B1
\n1<\/span>
\n
\nPL\/<\/span>SQL procedure successfully completed.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Vamos executar a consulta original mais uma vez e verificar quantos “SQL PLAN” foram criados.<\/span><\/p>\n

 <\/p>\n

1
2
3
4
5
6
7
8
9
10
11
12
13
14
<\/div><\/td>
SQL ><\/span> SELECT \/* DBTW001 *\/<\/span> OBJECT_NAME FROM MY_OBJECTS WHERE OBJECT_TYPE =<\/span> :<\/span>B1;<\/span>
\n
\nOBJECT_NAME
\n--------------------------------------------------------------------------------------------------------------------------------<\/span>
\nSYS_LOB0000011479C00008$$
\n
\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_4c1ebfe8f93aa46c           SQL_PLAN_4s7pzx3wmp93c77af7aad YES NO  NO
\nSQL_4c1ebfe8f93aa46c           SQL_PLAN_4s7pzx3wmp93c89405218 YES YES NO
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Quando consultamos a vis\u00e3o “DBA_SQL_PLAN_BASELINES” encontramos dois “SQL PLAN” para a consulta original, um criado manualmente pelo script “cspb.sql” que est\u00e1 como ACCEPTED=YES e o outro criado automaticamente quando executamos a consulta original pela ultima vez e sua condi\u00e7\u00e3o \u00e9 ACCEPTED=NO, ou seja ele n\u00e3o ser\u00e1 utilizado pelo Otimizador at\u00e9 que seja validado e o seu tempo de execu\u00e7\u00e3o seja melhor que o do plano que criamos manualmente.<\/span><\/p>\n

Tamb\u00e9m podemos obter informa\u00e7\u00f5es mais detalhadas da consulta original utilizando o\u00a0pacote\u00a0“DBMS_XPLAN”.<\/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
<\/div><\/td>
SQL ><\/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>
\nEnter value for<\/span> sql_handle:<\/span> SQL_4c1ebfe8f93aa46c
\n
\nPLAN_TABLE_OUTPUT
\n--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\n
\n--------------------------------------------------------------------------------<\/span>
\nSQL handle:<\/span> SQL_4c1ebfe8f93aa46c
\nSQL text:<\/span> SELECT \/* DBTW001 *\/<\/span> OBJECT_NAME FROM MY_OBJECTS WHERE OBJECT_TYPE =<\/span> :<\/span>B1
\n--------------------------------------------------------------------------------<\/span>
\n
\n--------------------------------------------------------------------------------<\/span>
\nPlan name:<\/span> SQL_PLAN_4s7pzx3wmp93c77af7aad         Plan id:<\/span> 2007988909<\/span>
\nEnabled:<\/span> YES     Fixed:<\/span> NO      Accepted:<\/span> NO      Origin:<\/span> AUTO-<\/span>CAPTURE
\n--------------------------------------------------------------------------------<\/span>
\n
\nPlan hash<\/span><\/a> value:<\/span> 1142617335<\/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>  TABLE ACCESS BY INDEX ROWID|<\/span> MY_OBJECTS     |<\/span>
\n|<\/span>   2<\/span> |<\/span>   INDEX RANGE<\/span><\/a> SCAN          |<\/span> IDX_MY_OBJECTS |<\/span>
\n------------------------------------------------------<\/span>
\n
\n--------------------------------------------------------------------------------<\/span>
\nPlan name:<\/span> SQL_PLAN_4s7pzx3wmp93c89405218         Plan id:<\/span> 2302693912<\/span>
\nEnabled:<\/span> YES     Fixed:<\/span> NO      Accepted:<\/span> YES     Origin:<\/span> MANUAL-<\/span>LOAD
\n--------------------------------------------------------------------------------<\/span>
\n
\nPlan hash<\/span><\/a> value:<\/span> 880823944<\/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>  TABLE ACCESS FULL|<\/span> MY_OBJECTS |<\/span>
\n----------------------------------------<\/span>
\n
\n34<\/span> rows selected.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

5) Consulta original com SQL PLAN<\/span><\/h3>\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
<\/div><\/td>
SQL ><\/span> variable B1 VARCHAR2(<\/span>32<\/span>)<\/span>
\nSQL ><\/span>
\nSQL ><\/span> exec<\/span><\/a> :<\/span>B1 :=<\/span> 'LOB PARTITION'<\/span>;<\/span>
\n
\nPL\/<\/span>SQL procedure successfully completed.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> SELECT \/* DBTW001 *\/<\/span> OBJECT_NAME FROM MY_OBJECTS WHERE OBJECT_TYPE =<\/span> :<\/span>B1;<\/span>
\n
\nOBJECT_NAME
\n--------------------------------------------------------------------------------------------------------------------------------<\/span>
\nSYS_LOB0000011479C00008$$
\n
\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 '%DBTW001%'<\/span>
\n  4<\/span>     AND sql_text NOT LIKE '%v$sql%'<\/span>;<\/span>
\n
\nSQL_ID        CHILD_NUMBER
\n-------------<\/span> ------------<\/span>
\n6k0ys0f922fbm            0<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> SELECT *<\/span>
\n  2<\/span>    FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'&m_sql_id'<\/span>,&<\/span>m_child_no,<\/span>'allstats last'<\/span>)<\/span>)<\/span>;<\/span>
\n
\nPLAN_TABLE_OUTPUT
\n--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\nSQL_ID  6k0ys0f922fbm,<\/span> child number 0<\/span>
\n-------------------------------------<\/span>
\nSELECT \/* DBTW001 *\/<\/span> OBJECT_NAME FROM MY_OBJECTS WHERE OBJECT_TYPE =<\/span> :<\/span>B1
\n
\nPlan hash<\/span><\/a> value:<\/span> 880823944<\/span>
\n
\n------------------------------------------------------------------------------------------<\/span>
\n|<\/span> Id  |<\/span> Operation         |<\/span> Name       |<\/span> Starts |<\/span> E-<\/span>Rows |<\/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>      1<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>    1899<\/span> |<\/span>
\n|*<\/span>  1<\/span> |<\/span>  TABLE ACCESS FULL|<\/span> MY_OBJECTS |<\/span>      1<\/span> |<\/span>     12<\/span> |<\/span>      1<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>    1899<\/span> |<\/span>
\n------------------------------------------------------------------------------------------<\/span>
\n
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
\n---------------------------------------------------<\/span>
\n
\n   1<\/span> -<\/span> filter(<\/span>"OBJECT_TYPE"<\/span>=:<\/span>B1)<\/span>
\n
\nNote
\n-----<\/span>
\n   -<\/span> SQL plan baseline SQL_PLAN_4s7pzx3wmp93c89405218 used for<\/span> this statement
\n
\n
\n22<\/span> rows selected.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Quando executamos a consulta original ap\u00f3s a cria\u00e7\u00e3o do “SQL PLAN” verificamos que o plano de execu\u00e7\u00e3o utilizado realiza um “Full Table Scan” na tabela e na se\u00e7\u00e3o “Note” aparece o coment\u00e1rio: “SQL plan baseline SQL_PLAN_4s7pzx3wmp93c89405218 used for this statement”.<\/span><\/p>\n

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

Nos Bancos de dados Oracle, a partir da vers\u00e3o 11G, onde n\u00e3o dispomos da licen\u00e7a “Tuning Pack” e consequentemente n\u00e3o podemos utilizar o recurso “SQL_PROFILE” podemos utilizar como alternativa o recurso “SQL PLAN MANAGEMENT” para fazer ajustes nos planos de execu\u00e7\u00e3o sem alterar o c\u00f3digo da aplica\u00e7\u00e3o.<\/span><\/p>\n

Scripts<\/span><\/h2>\n

Abaixo o c\u00f3digo fonte do script utilizado nesse artigo:<\/span><\/p>\n

cspb<\/a><\/strong><\/span><\/p>\n

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

https:\/\/docs.oracle.com\/cd\/E11882_01\/server.112\/e41573\/optplanmgmt.htm#PFGRF007<\/a><\/strong><\/span><\/p>\n

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

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

Em nossas tarefas de “SQL Tuning” muitas vezes n\u00e3o temos como alterar o c\u00f3digo da instru\u00e7\u00e3o SQL e nesses casos precisamos utilizar alguns recursos do banco de dados que permitem influenciar o Otimizador na montagem do plano de execu\u00e7\u00e3o sem a necessidade de incluir um “HINT” no c\u00f3digo da instru\u00e7\u00e3o SQL. Um desses recursos \u00e9 a “SQL_Profile”, porem para utiliza-la<\/p>\n","protected":false},"author":2,"featured_media":850,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[156],"tags":[159,158,157,118],"class_list":["post-849","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-sql-plan-management","tag-dbms_spm","tag-load_plans_from_cursor_cache","tag-sql-plan-management","tag-sql-profile"],"aioseo_notices":[],"_links":{"self":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/849","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=849"}],"version-history":[{"count":19,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/849\/revisions"}],"predecessor-version":[{"id":2090,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/849\/revisions\/2090"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/media\/850"}],"wp:attachment":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/media?parent=849"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/categories?post=849"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/tags?post=849"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}