{"id":751,"date":"2016-05-22T22:58:09","date_gmt":"2016-05-23T01:58:09","guid":{"rendered":"http:\/\/dbtimewizard.com.br\/blog\/?p=751"},"modified":"2020-09-21T19:23:54","modified_gmt":"2020-09-21T22:23:54","slug":"sql-profile-sera-que-posso-usar-esse-recurso","status":"publish","type":"post","link":"http:\/\/dbtimewizard.com.br\/blog\/sql-profile-sera-que-posso-usar-esse-recurso\/","title":{"rendered":"SQL Profile: Ser\u00e1 que posso usar esse recurso?"},"content":{"rendered":"

Quando a Oracle lan\u00e7a uma nova vers\u00e3o de banco de dados no mercado todos os profissionais da \u00e1rea ficam curiosos para conhecer as novas funcionalidades (features), e algumas delas proporcionam recursos fant\u00e1sticos para facilitar a vida de quem trabalha com isso, porem muitas vezes n\u00e3o fica claro que algumas dessas novas funcionalidades requerem uma licen\u00e7a adicional. Esse \u00e9 o caso do “Feature SQL Profile”, para utiliz\u00e1-la alem da licen\u00e7a do Oracle Database Enterprise Edition precisamos adquirir a “Option Tuning Pack<\/a><\/strong>” que por sua vez requer a “Option Diagnostic Pack<\/a><\/strong>“.<\/span><\/p>\n

A leitura do documento de licen\u00e7a da Oracle n\u00e3o \u00e9 muito clara quanto ao fato de que a “Feature SQL Profile” faz parte da “Option Tuning Pack”, para validar essa informa\u00e7\u00e3o vamos realizar uma simula\u00e7\u00e3o com uma consulta ativando e desativando a “Option Tuning Pack” para verificar se ela realmente faz parte dessa Option.<\/span><\/p>\n

Para aqueles que n\u00e3o disp\u00f5em das licen\u00e7as “Diagnostic and Tuning Pack” em sua base Oracle Enterprise Edition, uma alternativa \u00e9 a utiliza\u00e7\u00e3o da SQL Plan Management (SPM), cujos recursos s\u00e3o bem parecidos com SQL Profile. <\/p>\n

Simulando a utiliza\u00e7\u00e3o da SQL Profile<\/span><\/h2>\n

Para validar a necessidade da licen\u00e7a “Diagnostic and Tuning Pack” quando da\u00a0utiliza\u00e7\u00e3o da “Feature SQL Profile” vamos realizar o seguinte procedimento:<\/span><\/p>\n

 <\/p>\n

1)<\/strong> Vamos verificar se as “Options Diagnostic and Tuning Pack” est\u00e3o habilitadas consultando o valor do par\u00e2metro “control_management_pack_access”;<\/span><\/p>\n

2)<\/strong> Com as Options habilitadas vamos executar uma consulta que possui uma SQL Profile definida;<\/span><\/p>\n

3)<\/strong> Utilizando o pacote DBMS_XPLAN vamos exibir o plano de execu\u00e7\u00e3o e verificar se\u00e7\u00e3o “Note” se a SQL Profile esta sendo utilizada;<\/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
<\/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> Production
\n
\nSQL ><\/span>
\nSQL ><\/span> col name for<\/span> a40
\nSQL ><\/span> col value for<\/span> a30
\nSQL ><\/span> select name,<\/span> value,<\/span> isdefault,<\/span> ismodified from v$parameter<\/span> where name =<\/span> 'control_management_pack_access'<\/span>;<\/span>
\n
\nNAME                                     VALUE                          ISDEFAULT ISMODIFIED
\n----------------------------------------<\/span> ------------------------------<\/span> ---------<\/span> ----------<\/span>
\ncontrol_management_pack_access           DIAGNOSTIC+<\/span>TUNING              TRUE<\/span>      FALSE<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> alter system<\/span><\/a> flush<\/span><\/a> shared_pool;<\/span>
\n
\nSystem<\/span><\/a> altered.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> select count<\/span><\/a>(<\/span>1<\/span>)<\/span> from dbtw_objects;<\/span>
\n
\n  COUNT<\/span><\/a>(<\/span>1<\/span>)<\/span>
\n----------<\/span>
\n     87034<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> SELECT *<\/span> FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'2cfxx1aqrc3kx'<\/span>,<\/span>null<\/span>,<\/span>'typical iostats last'<\/span>)<\/span>)<\/span>;<\/span>
\n
\nPLAN_TABLE_OUTPUT
\n--------------------------------------------------------------------------------------------------------------<\/span>
\nSQL_ID  2cfxx1aqrc3kx,<\/span> child number 0<\/span>
\n-------------------------------------<\/span>
\nselect count<\/span><\/a>(<\/span>1<\/span>)<\/span> from dbtw_objects
\n
\nPlan hash<\/span><\/a> value:<\/span> 2190313608<\/span>
\n
\n----------------------------------------------------------------------------<\/span>
\n|<\/span> Id  |<\/span> Operation          |<\/span> Name         |<\/span> E-<\/span>Rows |<\/span> Cost (<\/span>%<\/span>CPU)<\/span>|<\/span> E-<\/span>Time<\/span><\/a>   |<\/span>
\n----------------------------------------------------------------------------<\/span>
\n|<\/span>   0<\/span> |<\/span> SELECT STATEMENT   |<\/span>              |<\/span>        |<\/span>   347<\/span> (<\/span>100<\/span>)<\/span>|<\/span>          |<\/span>
\n|<\/span>   1<\/span> |<\/span>  SORT<\/span><\/a> AGGREGATE<\/span><\/a>    |<\/span>              |<\/span>      1<\/span> |<\/span>            |<\/span>          |<\/span>
\n|<\/span>   2<\/span> |<\/span>   TABLE ACCESS FULL|<\/span> DBTW_OBJECTS |<\/span>    103K|<\/span>   347<\/span>   (<\/span>1<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>05<\/span> |<\/span>
\n----------------------------------------------------------------------------<\/span>
\n
\nNote
\n-----<\/span>
\n   -<\/span> SQL profile coe_2cfxx1aqrc3kx_2190313608 used for<\/span> this statement
\n   -<\/span> Warning:<\/span> basic plan statistics not available.<\/span> These are only collected when:<\/span>
\n       *<\/span> hint 'gather_plan_statistics'<\/span> is used for<\/span> the statement or
\n       *<\/span> parameter 'statistics_level'<\/span> is set to 'ALL'<\/span>,<\/span> at session or system<\/span><\/a> level
\n
\n
\n21<\/span> rows selected.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Na se\u00e7\u00e3o “Note” do plano de execu\u00e7\u00e3o podemos observar a exist\u00eancia da mensagem “SQL profile coe_2cfxx1aqrc3kx_2190313608 used for this statement” que indica a utiliza\u00e7\u00e3o da SQL Profile.<\/span><\/p>\n

 <\/p>\n

4)<\/strong> Vamos desativar as “Options Diagnostic and Tuning Pack” alterando o par\u00e2metro “control_management_pack_access” para “none”;<\/span><\/p>\n

5)<\/strong> Limpar a Shared_pool;<\/span><\/p>\n

6)<\/strong> Com as Options desativadas vamos executar a mesma consulta novamente;<\/span><\/p>\n

7)<\/strong> Utilizando o pacote DBMS_XPLAN vamos exibir o plano de execu\u00e7\u00e3o e verificar se\u00e7\u00e3o “Note” se a SQL Profile esta sendo utilizada;<\/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
<\/div><\/td>
SQL ><\/span> alter system<\/span><\/a> set control_management_pack_access=<\/span>'none'<\/span>;<\/span>
\n
\nSystem<\/span><\/a> altered.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> select name,<\/span> value,<\/span> isdefault,<\/span> ismodified from v$parameter<\/span> where name =<\/span> 'control_management_pack_access'<\/span>;<\/span>
\n
\nNAME                                     VALUE                          ISDEFAULT ISMODIFIED
\n----------------------------------------<\/span> ------------------------------<\/span> ---------<\/span> ----------<\/span>
\ncontrol_management_pack_access           none                           TRUE<\/span>      SYSTEM_MOD
\n
\nSQL ><\/span>
\nSQL ><\/span> alter system<\/span><\/a> flush<\/span><\/a> shared_pool;<\/span>
\n
\nSystem<\/span><\/a> altered.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> select count<\/span><\/a>(<\/span>1<\/span>)<\/span> from dbtw_objects;<\/span>
\n
\n  COUNT<\/span><\/a>(<\/span>1<\/span>)<\/span>
\n----------<\/span>
\n     87034<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> SELECT *<\/span> FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'2cfxx1aqrc3kx'<\/span>,<\/span>null<\/span>,<\/span>'typical iostats last'<\/span>)<\/span>)<\/span>;<\/span>
\n
\nPLAN_TABLE_OUTPUT
\n----------------------------------------------------------------------------------------------------------------------------<\/span>
\nSQL_ID  2cfxx1aqrc3kx,<\/span> child number 0<\/span>
\n-------------------------------------<\/span>
\nselect count<\/span><\/a>(<\/span>1<\/span>)<\/span> from dbtw_objects
\n
\nPlan hash<\/span><\/a> value:<\/span> 2190313608<\/span>
\n
\n----------------------------------------------------------------------------<\/span>
\n|<\/span> Id  |<\/span> Operation          |<\/span> Name         |<\/span> E-<\/span>Rows |<\/span> Cost (<\/span>%<\/span>CPU)<\/span>|<\/span> E-<\/span>Time<\/span><\/a>   |<\/span>
\n----------------------------------------------------------------------------<\/span>
\n|<\/span>   0<\/span> |<\/span> SELECT STATEMENT   |<\/span>              |<\/span>        |<\/span>   347<\/span> (<\/span>100<\/span>)<\/span>|<\/span>          |<\/span>
\n|<\/span>   1<\/span> |<\/span>  SORT<\/span><\/a> AGGREGATE<\/span><\/a>    |<\/span>              |<\/span>      1<\/span> |<\/span>            |<\/span>          |<\/span>
\n|<\/span>   2<\/span> |<\/span>   TABLE ACCESS FULL|<\/span> DBTW_OBJECTS |<\/span>  79266<\/span> |<\/span>   347<\/span>   (<\/span>1<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>05<\/span> |<\/span>
\n----------------------------------------------------------------------------<\/span>
\n
\nNote
\n-----<\/span>
\n   -<\/span> dynamic sampling used for<\/span> this statement (<\/span>level=<\/span>2<\/span>)<\/span>
\n   -<\/span> Warning:<\/span> basic plan statistics not available.<\/span> These are only collected when:<\/span>
\n       *<\/span> hint 'gather_plan_statistics'<\/span> is used for<\/span> the statement or
\n       *<\/span> parameter 'statistics_level'<\/span> is set to 'ALL'<\/span>,<\/span> at session or system<\/span><\/a> level
\n
\n
\n21<\/span> rows selected.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Agora a mensagem de utiliza\u00e7\u00e3o da SQL Profile n\u00e3o aparece na se\u00e7\u00e3o “Note” do plano de execu\u00e7\u00e3o, indicando que a SQL Profile n\u00e3o foi utilizada, ou seja quando as “Options Diagnostic and Tuning Pack” est\u00e3o desativadas o Otimizador n\u00e3o utiliza SQL Profile o que demonstra a depend\u00eancia da licen\u00e7a dessas Options.<\/span><\/p>\n

 <\/p>\n

8)<\/strong> Vamos criar uma SQL Plan baseline e verificar se ela depende da licen\u00e7a das “Options Diagnostic and Tuning Pack”;<\/span><\/p>\n

9)<\/strong> Limpar a Shared_pool;<\/span><\/p>\n

10)<\/strong> Com as Options desativadas vamos executar a mesma consulta novamente;<\/span><\/p>\n

11)<\/strong> Utilizando o pacote DBMS_XPLAN vamos exibir o plano de execu\u00e7\u00e3o e verificar se\u00e7\u00e3o “Note” se a SQL Plan baseline esta sendo utilizada;<\/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
<\/div><\/td>
SQL ><\/span> @<\/span>create_baseline;<\/span>
\nEnter value for<\/span> sql_id:<\/span> 2cfxx1aqrc3kx
\nEnter value for<\/span> plan_hash_value:<\/span> 2190313608<\/span>
\nEnter value for<\/span> fixed (<\/span>NO)<\/span>:<\/span>
\nEnter value for<\/span> enabled (<\/span>YES)<\/span>:<\/span>
\nEnter value for<\/span> plan_name (<\/span>ID_sqlid_planhashvalue)<\/span>:<\/span>
\nBaseline SQLID_2CFXX1AQRC3KX_2190313608 created.<\/span>
\nSQL ><\/span>
\nSQL ><\/span> alter system<\/span><\/a> set control_management_pack_access=<\/span>'none'<\/span>;<\/span>
\n
\nSystem<\/span><\/a> altered.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> select name,<\/span> value,<\/span> isdefault,<\/span> ismodified from v$parameter<\/span> where name =<\/span> 'control_management_pack_access'<\/span>;<\/span>
\n
\nNAME                                     VALUE                          ISDEFAULT ISMODIFIED
\n----------------------------------------<\/span> ------------------------------<\/span> ---------<\/span> ----------<\/span>
\ncontrol_management_pack_access           none                           FALSE<\/span>     SYSTEM_MOD
\n
\n1<\/span> row selected.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> alter system<\/span><\/a> flush<\/span><\/a> shared_pool;<\/span>
\n
\nSystem<\/span><\/a> altered.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> select count<\/span><\/a>(<\/span>1<\/span>)<\/span> from dbtw_objects;<\/span>
\n
\n  COUNT<\/span><\/a>(<\/span>1<\/span>)<\/span>
\n----------<\/span>
\n     87034<\/span>
\n
\n1<\/span> row selected.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> SELECT *<\/span> FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'2cfxx1aqrc3kx'<\/span>,<\/span>null<\/span>,<\/span>'typical iostats last'<\/span>)<\/span>)<\/span>;<\/span>
\n
\nPLAN_TABLE_OUTPUT
\n-----------------------------------------------------------------------------------------------------------------------------<\/span>
\nSQL_ID  2cfxx1aqrc3kx,<\/span> child number 0<\/span>
\n-------------------------------------<\/span>
\nselect count<\/span><\/a>(<\/span>1<\/span>)<\/span> from dbtw_objects
\n
\nPlan hash<\/span><\/a> value:<\/span> 2190313608<\/span>
\n
\n----------------------------------------------------------------------------<\/span>
\n|<\/span> Id  |<\/span> Operation          |<\/span> Name         |<\/span> E-<\/span>Rows |<\/span> Cost (<\/span>%<\/span>CPU)<\/span>|<\/span> E-<\/span>Time<\/span><\/a>   |<\/span>
\n----------------------------------------------------------------------------<\/span>
\n|<\/span>   0<\/span> |<\/span> SELECT STATEMENT   |<\/span>              |<\/span>        |<\/span>   347<\/span> (<\/span>100<\/span>)<\/span>|<\/span>          |<\/span>
\n|<\/span>   1<\/span> |<\/span>  SORT<\/span><\/a> AGGREGATE<\/span><\/a>    |<\/span>              |<\/span>      1<\/span> |<\/span>            |<\/span>          |<\/span>
\n|<\/span>   2<\/span> |<\/span>   TABLE ACCESS FULL|<\/span> DBTW_OBJECTS |<\/span>  87034<\/span> |<\/span>   347<\/span>   (<\/span>1<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>05<\/span> |<\/span>
\n----------------------------------------------------------------------------<\/span>
\n
\nNote
\n-----<\/span>
\n   -<\/span> SQL plan baseline SQLID_2CFXX1AQRC3KX_2190313608 used for<\/span> this statement
\n   -<\/span> Warning:<\/span> basic plan statistics not available.<\/span> These are only collected when:<\/span>
\n       *<\/span> hint 'gather_plan_statistics'<\/span> is used for<\/span> the statement or
\n       *<\/span> parameter 'statistics_level'<\/span> is set to 'ALL'<\/span>,<\/span> at session or system<\/span><\/a> level
\n
\n
\n21<\/span> rows selected.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Na se\u00e7\u00e3o “Note” do plano de execu\u00e7\u00e3o podemos observar a exist\u00eancia da mensagem “SQL plan baseline SQLID_2CFXX1AQRC3KX_2190313608 used for this statement” que indica a utiliza\u00e7\u00e3o da SQL Plan baseline, como as Options est\u00e3o desativadas podemos constatar que a utiliza\u00e7\u00e3o de “SQL Plan baseline” (SPM) n\u00e3o depende da licen\u00e7a das “Options Diagnostic and Tuning Pack”;<\/span><\/p>\n

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

Precisamos verificar quais s\u00e3o as options que possuem licen\u00e7a nas bases que estamos trabalhando pois podemos estar utilizando options que n\u00e3o foram adquiridas e isso pode causar problemas para a empresa numa auditoria de licen\u00e7as da Oracle. Para verificar as “features” que est\u00e3o sendo utilizadas na base basta consultar a vis\u00e3o DBA_FEATURE_USAGE_STATISTICS.<\/span><\/p>\n

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

Abaixo a lista de scripts utilizados nesse artigo:<\/span><\/p>\n

create_baseline.sql<\/a><\/strong><\/p>\n

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

http:\/\/kerryosborne.oracle-guy.com\/2011\/01\/licensing-requirements-for-sql-profiles\/<\/a><\/strong><\/p>\n

https:\/\/blogs.oracle.com\/optimizer\/entry\/does_the_use_of_sql<\/a><\/strong>
\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":"

Quando a Oracle lan\u00e7a uma nova vers\u00e3o de banco de dados no mercado todos os profissionais da \u00e1rea ficam curiosos para conhecer as novas funcionalidades (features), e algumas delas proporcionam recursos fant\u00e1sticos para facilitar a vida de quem trabalha com isso, porem muitas vezes n\u00e3o fica claro que algumas dessas novas funcionalidades requerem uma licen\u00e7a adicional. Esse \u00e9 o caso<\/p>\n","protected":false},"author":2,"featured_media":752,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[115],"tags":[140,137,138,139,136,118],"class_list":["post-751","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-sql-profile","tag-licenca","tag-license","tag-option-diagnostic-pack","tag-option-tuning-pack","tag-sql-plan-baseline","tag-sql-profile"],"aioseo_notices":[],"_links":{"self":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/751","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=751"}],"version-history":[{"count":20,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/751\/revisions"}],"predecessor-version":[{"id":2217,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/751\/revisions\/2217"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/media\/752"}],"wp:attachment":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/media?parent=751"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/categories?post=751"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/tags?post=751"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}