Search

Top 60 Oracle Blogs

Recent comments

January 2011

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.

clonedb

I’ve lost count of the number of times I’ve said something like: “Ideally you really need to do your testing on the production system”. Kevin Closson has recently written some interesting notes (with more to come) about clonedb – an 11.2 feature that may make it possible to get pretty close to this ideal.

Footnote: for purposes of removing any confusion, please note that actually testing on your production system is not a good idea

Prepared Quizzes, How Would You Answer that Question? 2

January 5, 2011 (Back to the Previous Post in the Series) (Forward to the Next Post in the Series) Since the readers of this blog produced fantastic answers for the previous multi-part quiz, I thought that it might be an interesting challenge to try another quiz.  The questions from this quiz seems to be aimed at [...]

Graphing packages: which are the best and why?

What web enabled graphing packages are the best and why?

Here is a good list of free charts
http://designtocoding.wordpress.com/2009/12/16/free-chart-scripts/
wondering what the pro’s and con’s are
at least for Google API for example you have to be connected to the internet to access the libraries hosted at Google which is a big drawback IMO.

Shared Server – 2

Although they are becoming increasingly rare (thanks, largely, to Web-based applications taking over the world) a few of the systems I get called in to review are still using Shared Server technology (formerly known as Mutli-threaded Server / MTS); and I have to say that there are a couple of nice “overview” features supporting this technology that I would like to see in the AWR or Statspack reports. These are the views which allow you to see how the workload is being shared out and what the time distribution looks like, and I’ll be taking a look at these views over the course of three or four blog notes.

The first view is v$reqdist – a view for which there is no match in dedicated server technology. This view gives you a simple histogram of how the shared servers have spent their time in the database. Here’s a simple query of the view, with the results from a system I was looking at a little while ago:


column bucket format 999,999,999,999

select	*
from	v$reqdist
order
	by bucket
;

    BUCKET            COUNT
---------- ----------------
         0      284,997,511
         1                0
         2          482,285
         3          348,456
         4          160,150
         5           37,206
         6            3,891
         7              313
         8               29
         9               33
        10               12
        11               28

According to the view definition, bucket N records the number of operations (for example a single “fetch” call) that took less than 4 * (2^N) hundredths of a second to complete. So this output shows 285 million operations took less than 4/100 seconds to complete, 482 thousand operations took less than 16/100 second to complete, and 28 operations took up to 81.92 seconds to complete. (In fact, anything taking longer than 81.92 seconds also falls into the last bucket.)

There are a few drawbacks to using this view, of course. The first is that it accumulates data since instance startup – and neither Statspack nor the AWR use it in their snapshots – so you have to write your own code to do interval analysis.

Secondly the choice of buckets isn’t ideal: a “fastest bucket” of 4 centiseconds doesn’t really help very much when you’ve got a system where you think most tasks are going to be very quick, and a “slowest bucket” of only 82 seconds doesn’t really highlight the massively slow jobs that you might want to know about. Perhaps this second loss of detail is deliberate – after all, everything you do through shared servers is supposed to be very quick, so perhaps all you really need to know is that some of your tasks are taking more than a few seconds - after which is doesn’t really matter how long they are taking.

The final problem with this view is that it loses data. Notice the zero that appears as the count in bucket 1 (the 4/100 to 8/100 second bucket) – that looks suspiciously out of place. Given the numbers in the buckets either side you can’t help feeling that there should have been some tasks falling into that bucket. And there is a bug (though not one I can find on MOS/Metalink): tasks taking 4/100 to 8/100 second fall into the 0 – 4/100 second bucket.

If you want to demonstrate this for yourself, and with your own platform and version of Oracle, it’s quite easy to do. From an SQL*plus session connected through a shared server simply query v$reqdist before and after running a script that looks like the following:


set serveroutput off

execute dbms_lock.sleep(0.06)
execute dbms_lock.sleep(0.06)
execute dbms_lock.sleep(0.06)
-- repeat for (say) 100 times
execute dbms_lock.sleep(0.06)
execute dbms_lock.sleep(0.06)
execute dbms_lock.sleep(0.06)

Pick a few variations in the sleep time – and see which bucket your sleeps are recorded in. (The call to disable serveroutput is there so that you don’t see the effects of the dbms_output calls that SQL*Plus would otherwise issue after each call to the database – these would show up in the 0-4/100 bucket.)

There is actually another little problem with with view – it’s another of those little glitches that appear when you experiment with the SYS account – the view doesn’t seem to record the time spent in the virtual circuit when you were connected as SYS (although other views would show the traffic).

Update: Having created a simple piece of demonstration code to show that bug with the bucket usage, it occurred to me recently to use it for at stress test – and I found another bug with v$reqdist when running 11.1.0.6 on Windows XP. It looks as if, with sufficient concurrent sessions (leading to a backup in the COMMON queue) Oracle loses track of how to compute round-trip time. I haven’t pursued this very closely yet, but it looks almost as if Oracle starts to use the wrong start and end times to calculate the round-trip time, intially subtracting the start time of message N-1 from the end time of message N and gradually slipping further and further out of sync until it’s using times from widely separated messages.

[Further reading on Shared Server / MTS]

Oracle XBRL Extension Ready for Download

The new ready to download Oracle XBRL Extension is a “no cost option” on top of the latest Oracle Database 11.2.0.2.0 release. With this added functionality you will get a database environment that contains

  • One or more back-end XBRL repositories based on Oracle Database, which provide XBRL storage and query-ability with a set of XBRL-specific services
  • An external XBRL processing engine (XPE)

The XBRL Extension to Oracle XML DB integrates easily with Oracle Business Intelligence Suite Enterprise Edition (OBIEE) for analytics and with interactive development environments (IDEs) and design tools for creating and editing XBRL taxonomies.

Architecture of XBRL Extension to Oracle XML DB

Oracle XBRL Extension

There is a generic “patch” (patch 10411201), p10411201_112020_generic.zip, which can be downloaded from http://support.oracle.com and installed to be used on top of the Oracle 11.2.0.2.0 XML DB. In addition, there are also two platform-specific patches (currently only available for Linux-x86-64 and Solaris-64 platforms):

For the Linux-x86 64 bit platform:

  • p10074437_112020_Linux-x86-64.zip
  • p10232225_112020_Linux-x86-64.zip

For the Solaris 64 bit platform:

  • p10074437_112020_SOLARIS64.zip
  • p10232225_112020_SOLARIS64.zip

Besides a installation readme, also a HTML/PDF document is available in these patches that have extensive info on how to use this XML DB XBRL extension and a demo based on the US GAAP XBRL taxonomy. On the Oracle XBRL Extension main page, you also can see the power of this architecture combined with web enabled application, the standard tools like Microsoft Excel, Word and analyses done on such an taxonomy database architecture via Oracle BI Server.

For more information see the Oracle XBRL Extension main page or the Oracle XML DB discussion forum.

M.

Where’s my money gone?

Over the holiday period I got a credit card bill from Oracle Norway for £3230.12. I have no idea what this is for, and as far as I know I’ve never dealt with Oracle Norway before, so my bank are treating it as a credit card fraud case. :(

What’s more I have no idea how they got my card details because this isn’t a card I use for anything Oracle related, like travel etc.

I contacted Oracle Norway, who put me on to Oracle Romania, who have passed me on to a finance department, who have now passed me on to another finance department. As yet no indication of what I supposedly bought or did to warrant this charge. The initial issue was worrying, but what’s more worrying is the amount of time it’s taken to get any response. I guess the holidays have had an impact though.

In the short term I’m not that bothered because my bank have already refunded the cash, as is their way when investigating potential fraud cases, but I would certainly like to know how this happened in the first place so I can prevent it from happening again…

Cheers

Tim…

Oracle.com passwords

Just a quick note for anyone who has missed the Oracle Security alerts email. If you downloaded either Enterprise Manager 11g or Oracle Database 11.2.0.2 before November 17th last year then you downloaded a version of OUI that sent unencrypted passwords for your oracle.com SSO account to Oracle over the intertubes. Not Good. Oracle have [...]

Diff’ing AWR reports

I don’t know if you are ever asked to compare to AWR periods. AWR period comparison is pretty easy if you have access to the two periods in the same AWR repository. AWR in the same repository can be compared with

SELECT * FROM TABLE(
    dbms_workload_repository.awr_diff_report_text(
         [db_id ],
         [instance id],
         120, -- start snapshot id
         121, -- end snapshot id
         [db_id of target,
         [instance id] ,
         122, -- start snapshot id
         123  -- end snapshot id));

and it can be run for single instance as

SELECT * FROM TABLE(
       dbms_workload_repository.awr_diff_report_text(
              (select dbid from v$database),
              1,
              120, -- start snapshot id
              121, -- end snapshot id
              (select dbid from v$database),
              1,
              122, -- start snapshot id
              123  -- end snapshot id));

This puts out a bit of a messy but useful report.
A similar, but cleaner simpler report that I partially designed can be run from OEM

but what if someone sends you two AWR reports? How can they be compared? These days I’m receiving at least a couple a week to compare, so I put together a compare script.
usage:
udiffs.sh [type] file1 file2
where type

  • sevt = system events , ie wait events
  • stats = system statistics
  • load = load profile section
  • init = init.ora

for example

udiffs.sh sevt awr1.txt awr2.txt
... Statistics requested is load
... 1st report.txt
... 2nd report.txt

============================= load_psec ==============================
Name                               Ratio 1/2   Value1     Value2     Delta
Physical_reads:                   :    0.29:    266.20:    905.33:    639.13
Physical_writes:                  :    0.70:    585.32:    836.75:    251.43
Logons:                           :    0.86:      1.27:      1.48:      0.21
Logical_reads:                    :    1.04: 747342.68: 718259.28:  -29083.4
Redo_size:                        :    1.17:3516126.09:2995591.47:   -520535
Sorts:                            :    1.31:   3981.16:   3027.78:   -953.38
User_calls:                       :    1.38:  16476.53:  11948.71:  -4527.82
Parses:                           :    1.39:   4541.51:   3279.06:  -1262.45
Executes:                         :    1.44:  10619.75:   7350.55:   -3269.2
Hard_parses:                      :    1.89:      0.17:      0.09:     -0.08
Block_changes:                    :    2.38:  18936.62:   7942.27:  -10994.3

============================= load_ptrx ==============================
Name                               Ratio 1/2   Value1     Value2     Delta
Logons:                           :    0.00:      0.00:      0.01:      0.01
Physical_reads:                   :    0.11:      0.43:      3.94:      3.51
Physical_writes:                  :    0.26:      0.95:      3.64:      2.69
Logical_reads:                    :    0.39:   1218.11:   3123.70:   1905.59
Redo_size:                        :    0.44:   5730.99:  13027.80:   7296.81
Sorts:                            :    0.49:      6.49:     13.17:      6.68
User_calls:                       :    0.52:     26.86:     51.96:      25.1
Parses:                           :    0.52:      7.40:     14.26:      6.86
Executes:                         :    0.54:     17.31:     31.97:     14.66
Block_changes:                    :    0.89:     30.87:     34.54:      3.67

of course if your AWR report is an html file, then the current script won’t work. One workaround is to run the html through a text converter like
http://www.nirsoft.net/utils/htmlastext.html

Again the script is available here: udiffs.sh


This script was originally written back before statspack and was based on utlstat. If you look closely you will even see that the code is actually modified by Connie Dialeris, aka the writer of statspack. Before Connie put together statspack, she was looking at the usability of my scripts. I had written a couple of scripts, collect.sh and utlstat.sh. The idea of these scritps was to continuously looped collecting database statistics to flat files. Flat files were used to avoid an extra overhead of inserting data into the database. The data could be formatted the into a utlstat like report with utlstat.sh. Instead of writting a diff report on the raw data, I wrote a diff report that could be used for two different utlstat reports from customers as well as the raw data. This strategy was lucky because it was easy to update the diff script for statspack and AWR.

Oracle Security Training, Home For Christmas and a belated happy new year

I started to write this blog post more than two weeks ago when I returned home for Christmas after travelling and working away for most of the last 3 months or so teaching training classes, doing security audits and also....[Read More]

Posted by Pete On 04/01/11 At 07:21 PM