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.

11 Comments

  1. Kerry,

    Nice follow-up.
    A further “corroborative” thought – “cardinality feedback” happens automatically, and works by creating SQL Baselines; so, whatever the intent of the licence document, your system may end up using SQL Baselines even when you (as a DBA) had never done anything with them.

    Nit-picking apology:
    Licence is a noun
    License is a verb

    I have to admit that I sometimes forget which is which, but then analogy is helpful:
    device is a noun
    devise is a verb

  2. osborne says:

    Ha, you Brits are so picky about grammar. In Texas most people can’t spell any version of licen(c/s)e, much less tell the difference between a noun and a verb (I’m proudly including myself in that group by the way). I learned something new today though. You are absolutely correct in your comments about the word “license”, but apparently somewhere along the way, whoever was in charge of grammar in America decided that having a word that was pronounced the same but spelled two different ways depending on whether it was a noun or a verb was Bullocks.

    I was taught in grade school that there was only one word (license). I always figured licence was just a difference in spelling used by people speaking the Queens English (UK and Canada – maybe Australia and New Zealand). I never had any idea that you guys had two versions of the word to keep straight. After working on a project for a couple of months in London I had trouble with a couple of words that started wanting to have British spelling. I still struggle with that a little bit now and again and actually “license” is one of those words I struggle with. But I never had any idea you guys used both spellings.

    Here’s a little excerpt from somewhere on the web citing differences in the language (so it must be true):

    -ce, -se

    Nouns ending in -ce with -se verb forms: American English and British English both retain the noun/verb distinction in advice / advise and device / devise, but American English has abandoned the distinction with licence / license and practice / practise (where the two words in each pair are homophones) that British spelling retains. American English uses practice and license for both meanings.

    American English has kept the Anglo-French spelling for defense and offense, which are usually defence and offence in British English; similarly there are the American pretense and British pretence; but derivatives such as defensive, offensive, and pretension are always thus spelled in both systems.

    Australian[52] and Canadian usage generally follows British.

    I once heard someone say that England and America were two countries separated by a common language. How true.

    • garyS says:

      I come from a part of the country (U.S.A.) where people use the term license as though it’s plural. “The officer asked for my license, so I handed them over.” That’s what I call a licentious use of the language.

  3. Martin says:

    I’ve checked with the 11gR2 License guide, and here as well only
    Automatic Plan Evolution of SQL Plan Management is listed as part of the tuning pack, so Manual Plan Evolution and Sql plan management itself seem to be part of the EE without additional option.

    Thank you for pointing to this interesting detail.

  4. Hi Kerry,

    I really liked your response :-) Very funny.

    Today I was not in mood to work and then (as usual) I started reading technical matter and your name strike to me. Now after reading this post, I am in good mood.

    Thanks!

    Neeraj

  5. [...] SQL profile It is useful with “force_matching” option for SQL with bad plan & NOT using bind variables. If you want to know it, read this blog. Be careful, profiles created by SQL Tuning Advisor may change plans when stats change. Also SQL profile requires a license. http://kerryosborne.oracle-guy.com/2009/04/oracle-sql-profiles/ http://kerryosborne.oracle-guy.com/2009/07/why-isnt-oracle-using-my-outline-profile-baseline/ http://kerryosborne.oracle-guy.com/2011/01/licensing-requirements-for-sql-profiles/ [...]

  6. frederic eveilleau says:

    I red your intersting researches about tuning and licencing.
    But, i observe somethings different.

    First, on my 11.2.0.2, control_management_pack_access was set to DIAG+TUN.
    I use one time the tuning advisor and implement PROFILE.
    For licencing compliance, we reset control_management_pack_access to NONE (before the end of 2011)…
    So, normaly, SQL Profile can’t be used because it’s a part of TUN.

    But, when i check dba_usage i get :
    select * from dba_feature_usage_statistics where currently_used = ‘TRUE’ and name = ‘SQL Profile’

    I obtain :
    3432156699 SQL Profile 11.2.0.2.0 5 15 TRUE 06/12/2011 23:46:55 03/01/2012 23:33:45 2 (HUGECLOB) 03/01/2012 23:33:45 602864 604800 SQL profiles have been used.

    So SQL Profile was used when management_pack is NONE…

    Last things, the column Feature_info give :
    Total so far: 2, Manual: 2, Auto: 0, Enabled: 2, Category count: 1

    So maybe there is a difference of licencing if you use it Manualy or automaticaly ?

    On the documentation for licencing :
    http://docs.oracle.com/cd/B28359_01/license.111/b28287/options.htm#CIHDDBCG

    there is no reference about SQL Profile….

    So…

    Great
    Fred

  7. osborne says:

    Hi Frederic,

    The link is for 11.1 documentation. 11.2 does reference DBMS_SQLTUNE package which is what I have used to create a manual SQL Profile.

    http://docs.oracle.com/cd/E11882_01/license.112/e10594.pdf

    I’ll go back and have a look at the behavior again and see what I come up with.

    Kerry

  8. Hi,
    The 12c documentation explicitely lists SQL Profiles in the features where licenses for Oracle Tuning Pack are required.
    http://docs.oracle.com/cd/E16655_01/license.121/e17614/options.htm#DBLIC170
    Regards,
    Franck.

  9. George says:

    I was hoping you would share your script to create an sql basline.

    SYS@SANDBOX1> @create_baseline

    Thanks a lot

  10. osborne says:

    Hi George,

    I have shared it. You can use the search box at the top of the blog to search for it and it will return a list of all the posts that have referenced it. Here’s a direct link for you so you don’t have to go looking:

    create_baseline.sql

    Kerry

Leave a Reply