Licensing Requirements for SQL Profiles

Jonathan Lewis reminded me on my last post that using SQL Profiles (because they are part of the SQL Tuning Advisor) requires a license for Oracle Tuning Pack (which requires a license for the Diagnostics Pack). He also mentioned that Baselines did not require any additional license (at least creating and using Baselines on SQL statements). It’s been a while since I worked on a database that didn’t have both packs, but frankly I wasn’t sure I had a good handle of what was allowed and what wasn’t. So I thought it might be worthwhile to check. There is an easy way to check by the way. I did a post a while back on Tuning Pack and Diagnostic Pack license requirements for running AWR and how to check what was allowed and what wasn’t using the CONTROL_MANAGEMENT_PACK_ACCESS parameter. Here’s a link to the post:

Oracle Management Packs

Here’s an example using the same technique to show that SQL Profiles are indeed disabled by turning off the Diagnostic and Tuning Packs (at least on 11.2.02).

SQL*Plus: Release 11.2.0.2.0 Production on Fri Jan 7 21:15:21 2011
 
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
 
SYS@SANDBOX1> @parms
Enter value for parameter: management_pack
 
 
Enter value for isset: Enter value for show_hidden: 
NAME                                               VALUE                                                                  ISDEFAUL ISMODIFIED ISSET
-------------------------------------------------- ---------------------------------------------------------------------- -------- ---------- ----------
control_management_pack_access                     DIAGNOSTIC+TUNING                                                      TRUE     TRUE       TRUE
 
SYS@SANDBOX1> @flush_pool
 
System altered.
 
SYS@SANDBOX1> @avgskew
 
AVG(PK_COL)
-----------
 16093748.8
 
1 row selected.
 
SYS@SANDBOX1> /
 
AVG(PK_COL)
-----------
 16093748.8
 
1 row selected.
 
SYS@SANDBOX1> @dplan
Enter value for sql_id: 05cq2hb1r37tr
Enter value for child_no: 
 
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  05cq2hb1r37tr, child number 0
-------------------------------------
select avg(pk_col) from kso.skew a where col1 > 0
 
Plan hash value: 568322376
 
-----------------------------------------------------------------------------------
| Id  | Operation                  | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |      |       |       | 44486 (100)|          |
|   1 |  SORT AGGREGATE            |      |     1 |    11 |            |          |
|*  2 |   TABLE ACCESS STORAGE FULL| SKEW |    32M|   335M| 44486   (1)| 00:08:54 |
-----------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - storage("COL1">0)
       filter("COL1">0)
 
Note
-----
   - SQL profile PROF_05cq2hb1r37tr_568322376 used for this statement
 
 
24 rows selected.
 
SYS@SANDBOX1> @flush_pool
 
System altered.
 
SYS@SANDBOX1> alter system set control_management_pack_access='none';
 
System altered.
 
SYS@SANDBOX1> @avgskew
 
AVG(PK_COL)
-----------
 16093748.8
 
1 row selected.
 
SYS@SANDBOX1> /
 
AVG(PK_COL)
-----------
 16093748.8
 
1 row selected.
 
SYS@SANDBOX1> @dplan
Enter value for sql_id: 05cq2hb1r37tr
Enter value for child_no: 
 
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  05cq2hb1r37tr, child number 0
-------------------------------------
select avg(pk_col) from kso.skew a where col1 > 0
 
Plan hash value: 568322376
 
-----------------------------------------------------------------------------------
| Id  | Operation                  | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |      |       |       | 44486 (100)|          |
|   1 |  SORT AGGREGATE            |      |     1 |    11 |            |          |
|*  2 |   TABLE ACCESS STORAGE FULL| SKEW |    32M|   335M| 44486   (1)| 00:08:54 |
-----------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - storage("COL1">0)
       filter("COL1">0)
 
 
20 rows selected.
 
SYS@SANDBOX1> alter system set control_management_pack_access='DIAGNOSTIC+TUNING';
 
System altered.
 
SYS@SANDBOX1> @flush_pool
 
System altered.
 
SYS@SANDBOX1> @avgskew
 
AVG(PK_COL)
-----------
 16093748.8
 
1 row selected.
 
SYS@SANDBOX1> /
 
AVG(PK_COL)
-----------
 16093748.8
 
1 row selected.
 
SYS@SANDBOX1> @dplan
Enter value for sql_id: 05cq2hb1r37tr
Enter value for child_no: 
 
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  05cq2hb1r37tr, child number 0
-------------------------------------
select avg(pk_col) from kso.skew a where col1 > 0
 
Plan hash value: 568322376
 
-----------------------------------------------------------------------------------
| Id  | Operation                  | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |      |       |       | 44486 (100)|          |
|   1 |  SORT AGGREGATE            |      |     1 |    11 |            |          |
|*  2 |   TABLE ACCESS STORAGE FULL| SKEW |    32M|   335M| 44486   (1)| 00:08:54 |
-----------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - storage("COL1">0)
       filter("COL1">0)
 
Note
-----
   - SQL profile PROF_05cq2hb1r37tr_568322376 used for this statement
 
 
24 rows selected.

So as you can see, there was a SQL Profile on the statement that was used when Diagnostic and Tuning Packs were enabled, but when I set CONTROL_MANAGEMENT_PACK_ACCESS to NONE, the SQL Profile was ignored. Let’s try the same test with a Baseline.

 
SYS@SANDBOX1> @flush_pool
 
System altered.
 
SYS@SANDBOX1> @avgskew
 
AVG(PK_COL)
-----------
 16093748.8
 
SYS@SANDBOX1> @create_baseline
Enter value for sql_id: 05cq2hb1r37tr
Enter value for plan_hash_value: 568322376
Enter value for fixed (NO): 
Enter value for enabled (YES): 
Enter value for plan_name (SQLID_sqlid_planhashvalue): 
sql_id: 05cq2hb1r37tr
plan_hash_value: 568322376
fixed: NO
enabled: YES
 
Baseline SQLID_05cq2hb1r37tr_568322376 created.
 
PL/SQL procedure successfully completed.
 
SYS@SANDBOX1> @avgskew
 
AVG(PK_COL)
-----------
 16093748.8
 
SYS@SANDBOX1> /
 
AVG(PK_COL)
-----------
 16093748.8
 
SYS@SANDBOX1> @dplan
Enter value for sql_id: 05cq2hb1r37tr
Enter value for child_no: 
 
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  05cq2hb1r37tr, child number 0
-------------------------------------
select avg(pk_col) from kso.skew a where col1 > 0
 
Plan hash value: 568322376
 
-----------------------------------------------------------------------------------
| Id  | Operation                  | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |      |       |       | 44486 (100)|          |
|   1 |  SORT AGGREGATE            |      |     1 |    11 |            |          |
|*  2 |   TABLE ACCESS STORAGE FULL| SKEW |    32M|   335M| 44486   (1)| 00:08:54 |
-----------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - storage("COL1">0)
       filter("COL1">0)
 
Note
-----
   - SQL profile PROF_05cq2hb1r37tr_568322376 used for this statement
   - SQL plan baseline SQLID_05CQ2HB1R37TR_568322376 used for this statement
 
 
25 rows selected.
 
SYS@SANDBOX1> -- so Baseline and SQL Profile Used
SYS@SANDBOX1>
SYS@SANDBOX1> alter system set control_management_pack_access='none';
 
System altered.
 
SYS@SANDBOX1> @flush_pool
 
System altered.
 
SYS@SANDBOX1> @avgskew
 
AVG(PK_COL)
-----------
 16093748.8
 
SYS@SANDBOX1> /
 
AVG(PK_COL)
-----------
 16093748.8
 
SYS@SANDBOX1> @dplan
Enter value for sql_id: 05cq2hb1r37tr
Enter value for child_no: 
 
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  05cq2hb1r37tr, child number 0
-------------------------------------
select avg(pk_col) from kso.skew a where col1 > 0
 
Plan hash value: 568322376
 
-----------------------------------------------------------------------------------
| Id  | Operation                  | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |      |       |       | 44486 (100)|          |
|   1 |  SORT AGGREGATE            |      |     1 |    11 |            |          |
|*  2 |   TABLE ACCESS STORAGE FULL| SKEW |    32M|   335M| 44486   (1)| 00:08:54 |
-----------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - storage("COL1">0)
       filter("COL1">0)
 
Note
-----
   - SQL plan baseline SQLID_05CQ2HB1R37TR_568322376 used for this statement
 
 
24 rows selected.

So Baselines apparently do not require licenses for the Tuning and Diagnostics Packs, at least they aren’t disabled by setting the CONTROL_MANAGEMENT_PACK_ACCESS parameter to NONE.

Leave a Reply