SQL Profiles Disable Automatic Dynamic Sampling

I had an interesting email exchange with a fellow Oracle practitioner, Bryan Grenn, about differences between SQL Profiles and Baselines last week. Basically Bryan observed that SQL Profiles appeared to disable Dynamic Sampling on Global Temporary Tables while Baselines did not. This caused the optimizer’s cost calculations (and therefore the estimated elapsed runtimes) to be incorrect – sometime grossly incorrect. So I did a little follow up testing with a GTT. I used 2 test cases inserting either one row or 100,000 rows into the GTT. With Dynamic Sampling the plans were different (as you might expect). I then tested with Profiles to see how the statement behaved. Here’s is some of the output I generated during the test (note this test was done on an Exadata but non-Exadata environments exhibit the same behavior):

-bash-3.2$ sqlplus / as sysdba
 
SQL*Plus: Release 11.2.0.2.0 Production on Sat Jan 1 09:42:39 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: dynamic
Enter value for isset: 
Enter value for show_hidden: 
 
NAME                                               VALUE                                                                  ISDEFAUL ISMODIFIED ISSET
-------------------------------------------------- ---------------------------------------------------------------------- -------- ---------- ----------
optimizer_dynamic_sampling                         2                                                                      TRUE     FALSE      FALSE
 
SYS@SANDBOX1> !cat e.sql
@flush_pool
set echo on
insert into skew_gtt select * from kso.skew where pk_col = 13635;
select count(*) from kso.skew s , skew_gtt g where s.pk_col = g.pk_col;
select count(*) from kso.skew s , skew_gtt g where s.pk_col = g.pk_col;
rollback;
set echo off
 
SYS@SANDBOX1> !cat e2.sql
@flush_pool
set echo on
insert into skew_gtt select * from kso.skew where rownum < 100000;
select count(*) from kso.skew s , skew_gtt g where s.pk_col = g.pk_col;
select count(*) from kso.skew s , skew_gtt g where s.pk_col = g.pk_col;
rollback;
set echo off
 
SYS@SANDBOX1> @e
 
System altered.
 
SYS@SANDBOX1> insert into skew_gtt select * from kso.skew where pk_col = 13635;
 
1 row created.
 
SYS@SANDBOX1> select count(*) from kso.skew s , skew_gtt g where s.pk_col = g.pk_col;
 
  COUNT(*)
----------
         1
 
SYS@SANDBOX1> select count(*) from kso.skew s , skew_gtt g where s.pk_col = g.pk_col;
 
  COUNT(*)
----------
         1
 
SYS@SANDBOX1> rollback;
 
Rollback complete.
 
SYS@SANDBOX1> set echo off
SYS@SANDBOX1> @fsx
Enter value for sql_text: select count(*) from kso.skew s , skew_gtt g where s.pk_col = g.pk_col
Enter value for sql_id: 
Enter value for inst_id: 
 
 INST SQL_ID         CHILD  PLAN_HASH      EXECS     AVG_ETIME AVG_PX OFFLOADABLE IO_SAVED_% SQL_TEXT
----- ------------- ------ ---------- ---------- ------------- ------ ----------- ---------- ----------------------------------------------------------------------
    1 1jp7sjmt0wp1j      0 1853478750          2           .00      0 No                 .00 select count(*) from kso.skew s , skew_gtt g where s.pk_col = g.pk_col
 
1 row selected.
 
SYS@SANDBOX1> @dplan
Enter value for sql_id: 1jp7sjmt0wp1j
Enter value for child_no: 
 
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  1jp7sjmt0wp1j, child number 0
-------------------------------------
select count(*) from kso.skew s , skew_gtt g where s.pk_col = g.pk_col
 
Plan hash value: 1853478750
 
--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |       |       |     3 (100)|          |
|   1 |  SORT AGGREGATE             |              |     1 |    19 |            |          |
|   2 |   NESTED LOOPS              |              |     1 |    19 |     3   (0)| 00:00:01 |
|   3 |    TABLE ACCESS STORAGE FULL| SKEW_GTT     |     1 |    13 |     2   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN        | SYS_C0011230 |     1 |     6 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("S"."PK_COL"="G"."PK_COL")
 
Note
-----
   - dynamic sampling used for this statement (level=2)
 
 
25 rows selected.
 
SYS@SANDBOX1> -- Got NL plan
SYS@SANDBOX1> 
SYS@SANDBOX1> @e2
 
System altered.
 
SYS@SANDBOX1> insert into skew_gtt select * from kso.skew where rownum < 100000;
 
99999 rows created.
 
SYS@SANDBOX1> select count(*) from kso.skew s , skew_gtt g where s.pk_col = g.pk_col;
 
  COUNT(*)
----------
     99999
 
1 row selected.
 
SYS@SANDBOX1> select count(*) from kso.skew s , skew_gtt g where s.pk_col = g.pk_col;
 
  COUNT(*)
----------
     99999
 
1 row selected.
 
SYS@SANDBOX1> rollback;
 
Rollback complete.
 
SYS@SANDBOX1> set echo off
SYS@SANDBOX1> @dplan
Enter value for sql_id: 1jp7sjmt0wp1j
Enter value for child_no: 
 
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  1jp7sjmt0wp1j, child number 0
-------------------------------------
select count(*) from kso.skew s , skew_gtt g where s.pk_col = g.pk_col
 
Plan hash value: 4093035962
 
-------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name         | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |              |       |       |       | 48466 (100)|          |
|   1 |  SORT AGGREGATE                |              |     1 |    19 |       |            |          |
|*  2 |   HASH JOIN                    |              | 90961 |  1687K|  2224K| 48466   (2)| 00:09:42 |
|   3 |    TABLE ACCESS STORAGE FULL   | SKEW_GTT     | 90961 |  1154K|       |   130   (1)| 00:00:02 |
|   4 |    INDEX STORAGE FAST FULL SCAN| SYS_C0011230 |    32M|   183M|       | 20693   (2)| 00:04:09 |
-------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("S"."PK_COL"="G"."PK_COL")
 
Note
-----
   - dynamic sampling used for this statement (level=2)
 
 
25 rows selected.
 
SYS@SANDBOX1> -- Got Hash plan

Note that the the plans both clearly show that Dynamic Sampling was done at level 2. This is how the optimizer knew there were 999,999 rows in the GTT during one execution and 1 row in the GTT in the other. So at this point I decided to create a SQL Profile on the cursor with the Hash Join plan and see what happened when I ran it with 100K rows in the GTT again.

 
SYS@SANDBOX1> @create_sql_profile
Enter value for sql_id: 1jp7sjmt0wp1j
Enter value for child_no (0): 
Enter value for profile_name (PROF_sqlid_planhash): 
Enter value for category (DEFAULT): 
Enter value for force_matching (FALSE): 
 
SQL Profile PROF_1jp7sjmt0wp1j_4093035962 created.
 
SYS@SANDBOX1> @e2
 
System altered.
 
SYS@SANDBOX1> insert into skew_gtt select * from kso.skew where rownum < 100000;
 
99999 rows created.
 
SYS@SANDBOX1> select count(*) from kso.skew s , skew_gtt g where s.pk_col = g.pk_col;
 
  COUNT(*)
----------
     99999
 
1 row selected.
 
SYS@SANDBOX1> select count(*) from kso.skew s , skew_gtt g where s.pk_col = g.pk_col;
 
  COUNT(*)
----------
     99999
 
1 row selected.
 
SYS@SANDBOX1> rollback;
 
Rollback complete.
 
SYS@SANDBOX1> set echo off
SYS@SANDBOX1> @dplan
Enter value for sql_id: 1jp7sjmt0wp1j
Enter value for child_no: 
 
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  1jp7sjmt0wp1j, child number 0
-------------------------------------
select count(*) from kso.skew s , skew_gtt g where s.pk_col = g.pk_col
 
Plan hash value: 4093035962
 
-----------------------------------------------------------------------------------------------
| Id  | Operation                      | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |              |       |       | 20874 (100)|          |
|   1 |  SORT AGGREGATE                |              |     1 |    19 |            |          |
|*  2 |   HASH JOIN                    |              |  8168 |   151K| 20874   (2)| 00:04:11 |
|   3 |    TABLE ACCESS STORAGE FULL   | SKEW_GTT     |  8168 |   103K|    29   (0)| 00:00:01 |
|   4 |    INDEX STORAGE FAST FULL SCAN| SYS_C0011230 |    32M|   183M| 20693   (2)| 00:04:09 |
-----------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("S"."PK_COL"="G"."PK_COL")
 
Note
-----
   - SQL profile PROF_1jp7sjmt0wp1j_4093035962 used for this statement
 
 
25 rows selected.

So the cardinality calculation is definitely wrong now. Notice in step 2 and 3 of the plan the estimated number of rows is 8168 instead of 999,999. This is the default calculated value if no stats are available and Dynamic Sampling is turned off. Note also that the Note section of the plan output does not mention Dynamic Sampling (because it wasn’t done). But the plan was the same even though the calculation was incorrect. That’s because the Profile contained all the hints it needed to coerce the optimizer into producing the desired plan (even though the cardinality was way off).

 
SYS@SANDBOX1> -- cardinality now wrong due to Profile - but still used correct plan
SYS@SANDBOX1> -- let's see hints
SYS@SANDBOX1> 
SYS@SANDBOX1> @sql_profile_hints 
Enter value for profile_name: PROF_1jp7sjmt0wp1j_4093035962
 
HINT
------------------------------------------------------------------------------------------------------------------------------------------------------
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.2')
DB_VERSION('11.2.0.2')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "G"@"SEL$1")
INDEX_FFS(@"SEL$1" "S"@"SEL$1" ("SKEW"."PK_COL"))
LEADING(@"SEL$1" "G"@"SEL$1" "S"@"SEL$1")
USE_HASH(@"SEL$1" "S"@"SEL$1")
 
9 rows selected.
 
SYS@SANDBOX1> -- So as you can see, the hints force the HASH JOIN plan
SYS@SANDBOX1> -- let's drop the profile and turn off Dynamic_Sampling        
SYS@SANDBOX1> -- this should make the same error on cardinality calc and probably change backto NL plan
SYS@SANDBOX1> 
SYS@SANDBOX1> @drop_sql_profile
Enter value for profile_name: PROF_1jp7sjmt0wp1j_4093035962
 
PL/SQL procedure successfully completed.
 
SYS@SANDBOX1> alter session set optimizer_dynamic_sampling=0;
 
Session altered.
 
SYS@SANDBOX1> @e2
 
System altered.
 
SYS@SANDBOX1> insert into skew_gtt select * from kso.skew where rownum < 100000;
 
99999 rows created.
 
SYS@SANDBOX1> select count(*) from kso.skew s , skew_gtt g where s.pk_col = g.pk_col;
 
  COUNT(*)
----------
     99999
 
1 row selected.
 
SYS@SANDBOX1> select count(*) from kso.skew s , skew_gtt g where s.pk_col = g.pk_col;
 
  COUNT(*)
----------
     99999
 
1 row selected.
 
SYS@SANDBOX1> rollback;
 
Rollback complete.
 
SYS@SANDBOX1> set echo off
SYS@SANDBOX1> @dplan
Enter value for sql_id: 1jp7sjmt0wp1j
Enter value for child_no: 
 
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  1jp7sjmt0wp1j, child number 0
-------------------------------------
select count(*) from kso.skew s , skew_gtt g where s.pk_col = g.pk_col
 
Plan hash value: 1853478750
 
--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |       |       |  8201 (100)|          |
|   1 |  SORT AGGREGATE             |              |     1 |    19 |            |          |
|   2 |   NESTED LOOPS              |              |  8168 |   151K|  8201   (1)| 00:01:39 |
|   3 |    TABLE ACCESS STORAGE FULL| SKEW_GTT     |  8168 |   103K|    29   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN        | SYS_C0011230 |     1 |     6 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("S"."PK_COL"="G"."PK_COL")
 
SQL_ID  1jp7sjmt0wp1j, child number 1
-------------------------------------
select count(*) from kso.skew s , skew_gtt g where s.pk_col = g.pk_col
 
Plan hash value: 4093035962
 
-------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name         | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |              |       |       |       | 48377 (100)|          |
|   1 |  SORT AGGREGATE                |              |     1 |    19 |       |            |          |
|*  2 |   HASH JOIN                    |              | 99999 |  1855K|  2448K| 48377   (2)| 00:09:41 |
|   3 |    TABLE ACCESS STORAGE FULL   | SKEW_GTT     | 99999 |  1269K|       |    29   (0)| 00:00:01 |
|   4 |    INDEX STORAGE FAST FULL SCAN| SYS_C0011230 |    32M|   183M|       | 20693   (2)| 00:04:09 |
-------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("S"."PK_COL"="G"."PK_COL")
 
Note
-----
   - cardinality feedback used for this statement
 
 
46 rows selected.
 
SYS@SANDBOX1> -- oops cardinality feedback kicked in

Oddly enough I got two child cursors on my two executions of the statement. The first dropped back to the NL plan as expected, but on the second execution Cardinality Feedback kicked in and pushed it back to the HASH Join (which is what it should be doing with 999,999 records in the GTT). Cardinality Feedback is an interesting new feature but since we’re probably already close to the limits of your attention span we’ll leave that for another day.

Bryan also mentioned that he had not seen this issue with Baselines so I thought I’d give that a quick try as well. By the way, here is a link to Bryan’s blog post on the issue. Sure enough he was right.

SYS@SANDBOX1> @e
 
System altered.
 
SYS@SANDBOX1> insert into skew_gtt select * from kso.skew where pk_col = 13635;
 
1 row created.
 
SYS@SANDBOX1> select count(*) from kso.skew s , skew_gtt g where s.pk_col = g.pk_col;
 
  COUNT(*)
----------
         1
 
1 row selected.
 
SYS@SANDBOX1> select count(*) from kso.skew s , skew_gtt g where s.pk_col = g.pk_col;
 
  COUNT(*)
----------
         1
 
1 row selected.
 
SYS@SANDBOX1> rollback;
 
Rollback complete.
 
SYS@SANDBOX1> set echo off
SYS@SANDBOX1> @dplan
Enter value for sql_id: 1jp7sjmt0wp1j
Enter value for child_no: 
 
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  1jp7sjmt0wp1j, child number 0
-------------------------------------
select count(*) from kso.skew s , skew_gtt g where s.pk_col = g.pk_col
 
Plan hash value: 1853478750
 
--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |       |       |     3 (100)|          |
|   1 |  SORT AGGREGATE             |              |     1 |    19 |            |          |
|   2 |   NESTED LOOPS              |              |     1 |    19 |     3   (0)| 00:00:01 |
|   3 |    TABLE ACCESS STORAGE FULL| SKEW_GTT     |     1 |    13 |     2   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN        | SYS_C0011230 |     1 |     6 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("S"."PK_COL"="G"."PK_COL")
 
Note
-----
   - dynamic sampling used for this statement (level=2)
 
 
25 rows selected.
 
SYS@SANDBOX1> -- Now let's create a baseline and see if it behaves the same way.
SYS@SANDBOX1> @create_baseline
Enter value for sql_id: 1jp7sjmt0wp1j
Enter value for plan_hash_value: 1853478750
Enter value for fixed (NO): 
Enter value for enabled (YES): 
Enter value for plan_name (SQLID_sqlid_planhashvalue): 
sql_id: 1jp7sjmt0wp1j
plan_hash_value: 1853478750
fixed: NO
enabled: YES
plan_name: SQLID_1jp7sjmt0wp1j_1853478750
sql_handle: SQL_936b37ad684d18d4
Baseline SQLID_1jp7sjmt0wp1j_1853478750 created.
 
PL/SQL procedure successfully completed.
 
SYS@SANDBOX1> @e
 
System altered.
 
SYS@SANDBOX1> insert into skew_gtt select * from kso.skew where pk_col = 13635;
 
1 row created.
 
SYS@SANDBOX1> select count(*) from kso.skew s , skew_gtt g where s.pk_col = g.pk_col;
 
  COUNT(*)
----------
         1
 
1 row selected.
 
SYS@SANDBOX1> select count(*) from kso.skew s , skew_gtt g where s.pk_col = g.pk_col;
 
  COUNT(*)
----------
         1
 
1 row selected.
 
SYS@SANDBOX1> rollback;
 
Rollback complete.
 
SYS@SANDBOX1> set echo off
SYS@SANDBOX1> @dplan
Enter value for sql_id: 1jp7sjmt0wp1j
Enter value for child_no: 
 
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  1jp7sjmt0wp1j, child number 0
-------------------------------------
select count(*) from kso.skew s , skew_gtt g where s.pk_col = g.pk_col
 
Plan hash value: 1853478750
 
--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |       |       |     3 (100)|          |
|   1 |  SORT AGGREGATE             |              |     1 |    19 |            |          |
|   2 |   NESTED LOOPS              |              |     1 |    19 |     3   (0)| 00:00:01 |
|   3 |    TABLE ACCESS STORAGE FULL| SKEW_GTT     |     1 |    13 |     2   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN        | SYS_C0011230 |     1 |     6 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("S"."PK_COL"="G"."PK_COL")
 
Note
-----
   - dynamic sampling used for this statement (level=2)
   - SQL plan baseline SQLID_1JP7SJMT0WP1J_1853478750 used for this statement
 
 
26 rows selected.

So it doesn’t appear that Baselines suffer from the same quirk. That is to say that Dynamic Sampling seems to work fine with Baselines as you can see from the cardinality estimate in lines 2 and 3 of the plan along with the Note section.

So why is this important? Well it may not actually be all that important, because the hints in my simple tests (and in Bryan’s production system for that matter), were sufficient to enforce the desired plan, despite the fact that Dynamic Sampling was disabled. However, it’s possible that this could have other negative side effects. For example, 11gR2 has the ability to automatically parallelize long running queries via setting PARALLEL_DEGREE_POLICY to AUTO. By default, any query that the optimizer estimates will run longer than 10 seconds will be evaluated and a DOP will be automatically calculated for that statement. This issue could affect how that feature behaves by affecting the estimated run time of the statements. I have not had a chance to play with that yet though. So much to learn, so little time.

========================================
1/7/2011 – Update due to Dominic’s comments:
========================================

Here’s some output showing that the dbms_xplan.display_sql_plan_baseline function does not show stored cardinality values (at least that’s what I think it shows).

SYS@LAB11202> @parms       
Enter value for parameter: dynamic
Enter value for isset: 
Enter value for show_hidden: 
 
NAME                                               VALUE                                                                  ISDEFAUL ISMODIFIED ISSET
-------------------------------------------------- ---------------------------------------------------------------------- -------- ---------- ----------
optimizer_dynamic_sampling                         2                                                                      TRUE     TRUE       TRUE
 
1 row selected.
 
SYS@LAB11202> !cat a.sql
select count(*) from kso.skew s , skew_gtt g where s.pk_col = g.pk_col;
 
SYS@LAB11202> @a
 
  COUNT(*)
----------
         0
 
1 row selected.
 
SYS@LAB11202> /
 
  COUNT(*)
----------
         0
 
1 row selected.
 
SYS@LAB11202> @dplan
Enter value for sql_id: 1jp7sjmt0wp1j
Enter value for child_no: 
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  1jp7sjmt0wp1j, child number 0
-------------------------------------
select count(*) from kso.skew s , skew_gtt g where s.pk_col = g.pk_col
 
Plan hash value: 2943048660
 
------------------------------------------------------------------------------------
| Id  | Operation           | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |              |       |       |     3 (100)|          |
|   1 |  SORT AGGREGATE     |              |     1 |    19 |            |          |
|   2 |   NESTED LOOPS      |              |     1 |    19 |     3   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| SKEW_GTT     |     1 |    13 |     2   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN| SYS_C0011258 |     1 |     6 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("S"."PK_COL"="G"."PK_COL")
 
Note
-----
   - dynamic sampling used for this statement (level=5)
 
 
25 rows selected.

So that’s interesting that Dynamic Sampling level was automatically adjusted up to 5. I have no idea why at this point, but that’s a project for another day. By the way, here’s what the 10053 trace file had to say about that:

Dynamic sampling level auto-adjusted from 2 to 5

Now I’ll create a baseline on that statement and run my query again.

SYS@LAB11202> @create_baseline
Enter value for sql_id: 1jp7sjmt0wp1j
Enter value for plan_hash_value: 2943048660
Enter value for fixed (NO): 
Enter value for enabled (YES): 
Enter value for plan_name (ID_sqlid_planhashvalue): 
 
Baseline created.
 
SYS@LAB11202> @a
 
  COUNT(*)
----------
         0
 
1 row selected.
 
SYS@LAB11202> /
 
  COUNT(*)
----------
         0
 
1 row selected.
 
SYS@LAB11202> @dplan
Enter value for sql_id: 1jp7sjmt0wp1j
Enter value for child_no: 
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  1jp7sjmt0wp1j, child number 0
-------------------------------------
select count(*) from kso.skew s , skew_gtt g where s.pk_col = g.pk_col
 
Plan hash value: 2943048660
 
------------------------------------------------------------------------------------
| Id  | Operation           | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |              |       |       |     3 (100)|          |
|   1 |  SORT AGGREGATE     |              |     1 |    19 |            |          |
|   2 |   NESTED LOOPS      |              |     1 |    19 |     3   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| SKEW_GTT     |     1 |    13 |     2   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN| SYS_C0011258 |     1 |     6 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("S"."PK_COL"="G"."PK_COL")
 
Note
-----
   - dynamic sampling used for this statement (level=5)
   - SQL plan baseline SQL_PLAN_96utrppn4u66ncefcc71f used for this statement
 
 
26 rows selected.

So Dynamic Sampling was still used and the Baseline did not affect the cardinality. But let’s see what happens if we use the dbms_xplan.display_sql_plan_baseline function to view the “expected” plan.

 
SYS@LAB11202> @baselines
Enter value for sql_text: %gtt%
Enter value for handle_name: 
Enter value for plan_name: 
 
SQL_HANDLE               PLAN_NAME                      SQL_TEXT                                           ENABLED ACC FIX LAST_EXECUTED
------------------------ ------------------------------ -------------------------------------------------- ------- --- --- ----------------
SQL_936b37ad684d18d4     SQL_PLAN_96utrppn4u66ncefcc71f select count(*) from kso.skew s , skew_gtt g where YES     YES NO  07-jan-11 10:12
 
1 row selected.
 
SYS@LAB11202> !cat dplan_baseline.sql
set lines 150
select * from table(dbms_xplan.display_sql_plan_baseline('&sql_handle','&plan_name','typical'))
/
 
SYS@LAB11202> @dplan_baseline
Enter value for sql_handle: SQL_936b37ad684d18d4
Enter value for plan_name: SQL_PLAN_96utrppn4u66ncefcc71f
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
 
--------------------------------------------------------------------------------
SQL handle: SQL_936b37ad684d18d4
SQL text: select count(*) from kso.skew s , skew_gtt g where s.pk_col = g.pk_col
--------------------------------------------------------------------------------
 
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_96utrppn4u66ncefcc71f         Plan id: 3472672543
Enabled: YES     Fixed: NO      Accepted: YES     Origin: MANUAL-LOAD
--------------------------------------------------------------------------------
 
Plan hash value: 2943048660
 
------------------------------------------------------------------------------------
| Id  | Operation           | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |              |     1 |    19 |  8200   (1)| 00:00:02 |
|   1 |  SORT AGGREGATE     |              |     1 |    19 |            |          |
|   2 |   NESTED LOOPS      |              |  8168 |   151K|  8200   (1)| 00:00:02 |
|   3 |    TABLE ACCESS FULL| SKEW_GTT     |  8168 |   103K|    29   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN| SYS_C0011258 |     1 |     6 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("S"."PK_COL"="G"."PK_COL")
 
27 rows selected.
 
SYS@LAB11202> @baseline_hints
Enter value for baseline_plan_name: SQL_PLAN_96utrppn4u66ncefcc71f
 
OUTLINE_HINTS
------------------------------------------------------------------------------------------------------------------------------------------------------
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.2')
DB_VERSION('11.2.0.2')
OPT_PARAM('optimizer_dynamic_sampling' 5)
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "G"@"SEL$1")
INDEX(@"SEL$1" "S"@"SEL$1" ("SKEW"."PK_COL"))
LEADING(@"SEL$1" "G"@"SEL$1" "S"@"SEL$1")
USE_NL(@"SEL$1" "S"@"SEL$1")
 
10 rows selected.

Note that the cardinality displayed is the default calculated value we would get if Dynamic Sampling was turned off. So my guess is that the dbms_xplan.display_sql_plan_baseline function does something like Explain Plan, coming up with an “expected” plan. It probably disables some things that it thinks could throw it off like Dynamic Sampling, Cardinality Feedback, Bind Variable Peeking, etc… Note, this is just a guess though as I haven’t got the time to test that right now.

I will add that although Profiles do appear to explicitly disable Dynamic Sampling, I still see them as being more flexible than Baselines because of the ease with which we can manipulate them via the dbms_sqltune.import_sql_profile procedure. If this issue of disabling Dyanmic Sampling causes any undesirable side affects, it would be a simple matter to create a Baseline on top of the Profile and drop the Profile, or manually add a DYNAMIC_SAMPLING hint to your Profile.

7 Comments

  1. Interesting.

    As I understand it (mainly from Christian Antognini’s explanations in “Troubleshooting Oracle Performance”), how/when SQL profiles and SQL baselines are evaluated are significantly different.

    At hard parse SQL profile hints are fetched and the execution plan generated.

    Whereas with a baseline, an execution plan is generated without the baseline and then the plan with the baseline is evaulated.

    So, it raises a couple of questions which I can’t look into right now, but for example:

    1. A SQL profile is effectively just a set of hints. What exactly is stored for a SQL plan baseline? It’s more than just hints – as shown by dbms_xplan.display_sql_plan_baseline.
    It includes cardinalities, right?
    Does it also include information on the dynamic sampling used for the plan put in the baseline?
    (Just to illustrate what I’m thinking – if you generated a baseline plan with dynamic sampling level 4 and then ran the sql with a level of 8 (and these levels produced different cardinalities), does the execution plan show the cardinalities & the dynamic sampling level from the baseline or from the runtime setting?).

    2. What does a 10053 trace show in these cases? (And a 10046 trace to confirm the dynamic sampling queries). There are normally useful lines in the 10053 saying why things like dynamic sampling are/aren’t considered aren’t there?

  2. Kerry,

    I have to say that I’m not particularly surprised by this difference. It comes back to my opinion that SQL Profiles are about arithmetic and SQL Baselines (and outlines) are about action.

    If a profile is generated “legally” I think it would include table_stats() and possibly column_stats() entries about any global temporary tables in the query – which would make dynamic sampling of GTTs not just redundant, but a possible threat if the optimizer took the sample as gospel but still applied all the other profile corrections.

  3. osborne says:

    Dominic,

    Thanks for the comments.

    1. I don’t believe Baselines store cardinality information. They definitely have a plan_hash_value (not the same as in v$sql) and hints, but I haven’t found anything else (like cardinality values for example). I did a quick test and it appears that dbms_xplan.display_sql_plan_baseline spits out dynamically generated plans based on the text and hints but with dynamic sampling disabled. When you look at the plans generated by actually running the statements with a Baseline though, the cardinality estimates changed based on the level of Dynamic Sampling, even though the same baseline is being used. I’ll add a little more output on this to the bottom of the post.

    2. Yes there are notes of dynamic sampling usage in the 10053 trace files. In fact, in the case I just tested the Dynamic Sampling level was changed from 2 to 5 “automagically”. I don’t really have any idea why at this point but don’t really have time to follow up at the moment.

    One Caveat, I haven’t had much time to look at 11.2.0.2 yet, so there may be some changes from 11.2.0.1 which is where I’ve done most of my testing.

  4. osborne says:

    Jonathan,

    Yes – I agree that’s probably why Dynamic Sampling is disabled by default on Profiles. I do feel better about using Profiles in this way since running across the Metalink reference I talked about in my previous post:

    Oracle Support Sanctions Manually Created SQL Profiles!

  5. Kerry,

    I can’t remember if I’ve said this in the past – but if you use SQL Profiles you have to have purchased a separate licence. But I think you can create and use SQL Baselines without a separate licence (although there are some features of baselines that you have to pay for).

  6. Mahesh says:

    Kerry
    I have seen the number 8168 pop in many plans where statistics were not available. 8192-24. Was wondering if in a 16k database it would be 16384-?.
    Mahesh

  7. osborne says:

    Hi Mahesh,

    I don’t know the answer to that one. Unfortunately I don’t have the time to try it out right now. If you have time to try it, let me know how it comes out.

    Kerry

Leave a Reply