SQL Profile: Será que posso usar esse recurso?

maio 22, 2016 V. J.A. SQL Profile

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

A leitura do documento de licença da Oracle não é muito clara quanto ao fato de que a “Feature SQL Profile” faz parte da “Option Tuning Pack”, para validar essa informação vamos realizar uma simulação com uma consulta ativando e desativando a “Option Tuning Pack” para verificar se ela realmente faz parte dessa Option.

Para aqueles que não dispõem das licenças “Diagnostic and Tuning Pack” em sua base Oracle Enterprise Edition, uma alternativa é a utilização da SQL Plan Management (SPM), cujos recursos são bem parecidos com SQL Profile.

Simulando a utilização da SQL Profile

Para validar a necessidade da licença “Diagnostic and Tuning Pack” quando da utilização da “Feature SQL Profile” vamos realizar o seguinte procedimento:

1) Vamos verificar se as “Options Diagnostic and Tuning Pack” estão habilitadas consultando o valor do parâmetro “control_management_pack_access”;

2) Com as Options habilitadas vamos executar uma consulta que possui uma SQL Profile definida;

3) Utilizando o pacote DBMS_XPLAN vamos exibir o plano de execução e verificar seção “Note” se a SQL Profile esta sendo utilizada;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60

SQL > SELECT * FROM V$VERSION where rownum < 2;

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

SQL >
SQL > col name for a40
SQL > col value for a30
SQL > select name, value, isdefault, ismodified from v$parameter where name = 'control_management_pack_access';

NAME VALUE ISDEFAULT ISMODIFIED
---------------------------------------- ------------------------------ --------- ----------
control_management_pack_access DIAGNOSTIC+TUNING TRUE FALSE

SQL >
SQL >
SQL > alter system flush shared_pool;

System altered.

SQL >
SQL >
SQL > select count(1) from dbtw_objects;

COUNT(1)
----------
87034

SQL >
SQL >
SQL > SELECT * FROM TABLE (dbms_xplan.display_cursor ('2cfxx1aqrc3kx',null,'typical iostats last'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID 2cfxx1aqrc3kx, child number 0
-------------------------------------
select count(1) from dbtw_objects

Plan hash value: 2190313608

----------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)| E-Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 347 (100)| |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| DBTW_OBJECTS | 103K| 347 (1)| 00:00:05 |
----------------------------------------------------------------------------

Note
-----
- SQL profile coe_2cfxx1aqrc3kx_2190313608 used for this statement
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level

21 rows selected.

SQL >

Title of the document

Ajudo DBAs e analistas de sistema a se destacarem em suas empresas

e obter um crescimento mais acelerado em suas carreiras, quer saber mais click no link abaixo:

https://dbtimewizard.com.br/mentoria

Na seção “Note” do plano de execução podemos observar a existência da mensagem “SQL profile coe_2cfxx1aqrc3kx_2190313608 used for this statement” que indica a utilização da SQL Profile.

4) Vamos desativar as “Options Diagnostic and Tuning Pack” alterando o parâmetro “control_management_pack_access” para “none”;

5) Limpar a Shared_pool;

6) Com as Options desativadas vamos executar a mesma consulta novamente;

7) Utilizando o pacote DBMS_XPLAN vamos exibir o plano de execução e verificar seção “Note” se a SQL Profile esta sendo utilizada;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53

SQL > alter system set control_management_pack_access='none';

System altered.

SQL >
SQL > select name, value, isdefault, ismodified from v$parameter where name = 'control_management_pack_access';

NAME VALUE ISDEFAULT ISMODIFIED
---------------------------------------- ------------------------------ --------- ----------
control_management_pack_access none TRUE SYSTEM_MOD

SQL >
SQL > alter system flush shared_pool;

System altered.

SQL >
SQL > select count(1) from dbtw_objects;

COUNT(1)
----------
87034

SQL >
SQL > SELECT * FROM TABLE (dbms_xplan.display_cursor ('2cfxx1aqrc3kx',null,'typical iostats last'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------
SQL_ID 2cfxx1aqrc3kx, child number 0
-------------------------------------
select count(1) from dbtw_objects

Plan hash value: 2190313608

----------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)| E-Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 347 (100)| |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| DBTW_OBJECTS | 79266 | 347 (1)| 00:00:05 |
----------------------------------------------------------------------------

Note
-----
- dynamic sampling used for this statement (level=2)
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level

21 rows selected.

SQL >

Agora a mensagem de utilização da SQL Profile não aparece na seção “Note” do plano de execução, indicando que a SQL Profile não foi utilizada, ou seja quando as “Options Diagnostic and Tuning Pack” estão desativadas o Otimizador não utiliza SQL Profile o que demonstra a dependência da licença dessas Options.

8) Vamos criar uma SQL Plan baseline e verificar se ela depende da licença das “Options Diagnostic and Tuning Pack”;

9) Limpar a Shared_pool;

10) Com as Options desativadas vamos executar a mesma consulta novamente;

11) Utilizando o pacote DBMS_XPLAN vamos exibir o plano de execução e verificar seção “Note” se a SQL Plan baseline esta sendo utilizada;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65

SQL > @create_baseline;
Enter value for sql_id: 2cfxx1aqrc3kx
Enter value for plan_hash_value: 2190313608
Enter value for fixed (NO):
Enter value for enabled (YES):
Enter value for plan_name (ID_sqlid_planhashvalue):
Baseline SQLID_2CFXX1AQRC3KX_2190313608 created.
SQL >
SQL > alter system set control_management_pack_access='none';

System altered.

SQL >
SQL > select name, value, isdefault, ismodified from v$parameter where name = 'control_management_pack_access';

NAME VALUE ISDEFAULT ISMODIFIED
---------------------------------------- ------------------------------ --------- ----------
control_management_pack_access none FALSE SYSTEM_MOD

1 row selected.

SQL >
SQL > alter system flush shared_pool;

System altered.

SQL >
SQL > select count(1) from dbtw_objects;

COUNT(1)
----------
87034

1 row selected.

SQL >
SQL > SELECT * FROM TABLE (dbms_xplan.display_cursor ('2cfxx1aqrc3kx',null,'typical iostats last'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------
SQL_ID 2cfxx1aqrc3kx, child number 0
-------------------------------------
select count(1) from dbtw_objects

Plan hash value: 2190313608

----------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)| E-Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 347 (100)| |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| DBTW_OBJECTS | 87034 | 347 (1)| 00:00:05 |
----------------------------------------------------------------------------

Note
-----
- SQL plan baseline SQLID_2CFXX1AQRC3KX_2190313608 used for this statement
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level

21 rows selected.

SQL >

Na seção “Note” do plano de execução podemos observar a existência da mensagem “SQL plan baseline SQLID_2CFXX1AQRC3KX_2190313608 used for this statement” que indica a utilização da SQL Plan baseline, como as Options estão desativadas podemos constatar que a utilização de “SQL Plan baseline” (SPM) não depende da licença das “Options Diagnostic and Tuning Pack”;

Conclusão

Precisamos verificar quais são as options que possuem licença nas bases que estamos trabalhando pois podemos estar utilizando options que não foram adquiridas e isso pode causar problemas para a empresa numa auditoria de licenças da Oracle. Para verificar as “features” que estão sendo utilizadas na base basta consultar a visão DBA_FEATURE_USAGE_STATISTICS.