Flippin’ Witch Hunt – adaptive direct path read

Plan flips… here seems to be general impression that most application performance issues are going to be caused by some sort of “plan flip” and that “plan flips” are bad.

  • If you don’t use plan stability features, should you really expect plan stability?
  • Even outside your stats job window whenever that is?
  • What if you’re using bind variables with histograms?
  • And you’ve got features like dynamic sampling which will probably get a slightly different sample for each hard parse?
  • And if you’ve also got cardinality feedback kicking in?

The old battle between stability and optimisation, I suppose.

I made a throwaway comment on a forum thread about having seen a performance problem caused by a sql statement which changed to doing direct path IO.

I thought it might be vaguely interesting to do a quick blog post and back that up with a tiny bit of “evidence”.

This was getting on for three weeks ago now (29th May) and there were bigger fish to fry at the time so some of the evidence and some of the accuracy of the evidence might wont a little but I love having a long AWR retention and the outputs below are what ASH & AWR can tell us now about what happened back then.

Problem:
A particular sql statment was running slowly.
Initial analysis had identified a change in the plan and that change was caused by cardinality feedback and changes in plans are bad of course ;)

Looking at DBA_HIST_SQLSTAT, I could see that the SQL regularly flip-flopped between two plans, one of which was the plan normally received after a hard parse, the other a change brought on by cardinality feedback.

But there really wasn’t much to choose between the plans in terms of statistics and averages.

In the execution plan, we can also skip specifics, the only relevant detail that there was a FULL TABLE SCAN which was the inner part (i.e. executed multiple times for each row in the driving / outer rowsource) of a NESTED LOOP – something which is rarely going to be a good thing, i.e.

NESTED LOOP
  some row source
  FULL TABLE SCAN

However, if we cross-reference DBA_HIST_ACTIVE_SESS_HISTORY, then we see the critical differences between the executions of this FULL TABLE SCAN on the 28th / 29th May and other days.

If you don’t like averages then I don’t blame you but you won’t like this query:

with ash as
(select trunc(ash.sample_time) snap_day
 ,      min(ash.snap_id) min_snap_id
 ,      max(ash.snap_id) max_snap_id
 ,      ash.current_obj# obj_id
 ,      ash.sql_id
 ,      ash.sql_plan_hash_value
 ,      ash.sql_plan_operation
 ,      ash.sql_plan_options
 ,      ash.current_obj#
 ,      ash.event
 ,      count(distinct ash.sql_exec_id) ash_cnt
 from   dba_hist_active_sess_history ash
 where  ash.sql_id              = 'drbkfkfxg6a6a'
 and    ash.sql_plan_operation  = 'TABLE ACCESS'
 and    ash.sql_plan_options    = 'FULL'
 and    ash.current_obj#        = 169748
 and    ash.sample_time        >= to_date('01/05/2012','DD/MM/YYYY')
 and    ash.event              is not null
 group by
        trunc(ash.sample_time)
 ,      ash.sql_plan_hash_value
 ,      ash.sql_id
 ,      ash.sql_plan_operation
 ,      ash.sql_plan_options
 ,      ash.current_obj#
 ,      ash.event)
select ash.snap_day
,      ash.event
,      ash.ash_cnt
,      (select round(sum(elapsed_time_delta) / sum(executions_delta) /1000/1000)
        from   dba_hist_sqlstat   st
        where  st.sql_id        = ash.sql_id
        and    st.snap_id between ash.min_snap_id
                          and     ash.max_snap_id) avg_elp
,      (select round(sum(rows_processed_delta) / sum(executions_delta))
        from   dba_hist_sqlstat   st
        where  st.sql_id        = ash.sql_id
        and    st.snap_id between ash.min_snap_id
                          and     ash.max_snap_id) avg_rws
,      round((1275068416 -
       (select sum(space_allocated_delta)
        from   dba_hist_seg_stat st
        where  st.obj#          = ash.obj_id
        and    st.snap_id      >= ash.min_snap_id))
      / 1024/1024) "SPACE_USED (ISH)"
,      (select min(bytes)/1024/1024
        from   dba_hist_sgastat st
        where  name             = 'buffer_cache'
        and    st.snap_id between ash.min_snap_id
                          and     ash.max_snap_id) min_bc
from   ash
order by snap_day;

What am I trying to see:

  • Historic running of a particular sql statment
  • Focusing on this FULL TABLE SCAN operation which was a problem on the 28th / 29th
  • Occurences in ASH (be very careful of reading too much into samples of samples and aggregates and averages)
  • What the statement tended to wait on and a feel for how much waiting (beware of sampled samples)
  • And finally a rough approximation of how big the object was relative to the buffer cache (we know what size it is now (which I’ve plugged into the query) so work back through the growth in segment stats. It’s using space allocated because I don’t have access to dbms_space to get space used and space allocated is going to be a high estimate of actual space used – it certainly shouldn’t be more than that)
SNAP_DAY  EVENT                               ASH_CNT    AVG_ELP SPACE_USED (ISH)     MIN_BC
--------- -------------------------------- ---------- ---------- ---------------- -------
01-MAY-12 db file scattered read                    1         19             1152      61696
01-MAY-12 db file sequential read                   1         19             1152      61696
01-MAY-12 db file sequential read                   1         25             1152      61696
02-MAY-12 db file sequential read                   1          8             1152      61696
02-MAY-12 db file scattered read                    1          8             1152      61696
07-MAY-12 db file scattered read                    1          7             1152      61696
08-MAY-12 db file scattered read                    1         24             1152      61696
08-MAY-12 db file sequential read                   1         24             1152      61696
09-MAY-12 db file sequential read                   1        100             1152      61696
09-MAY-12 db file scattered read                    1        100             1152      61696
10-MAY-12 db file sequential read                   1          6             1152      61696
10-MAY-12 db file scattered read                    1          6             1152      61696
11-MAY-12 db file sequential read                   1          7             1152      61696
11-MAY-12 db file scattered read                    1         13             1152      61696
11-MAY-12 db file sequential read                   1         13             1152      61696
15-MAY-12 db file scattered read                    1        148             1152      62208
16-MAY-12 db file scattered read                    1          8             1152      62464
16-MAY-12 db file sequential read                   2          5             1152      62464
17-MAY-12 db file scattered read                    1         17             1152      62208
17-MAY-12 db file sequential read                   1         17             1152      62208
18-MAY-12 db file sequential read                   2         17             1152      62208
18-MAY-12 db file scattered read                    2         17             1152      62208
21-MAY-12 db file scattered read                    1         16             1216      62208
21-MAY-12 db file scattered read                    1          6             1216      62208
22-MAY-12 db file scattered read                    1         19             1216      62208
23-MAY-12 db file scattered read                    3         17             1216      62208
23-MAY-12 read by other session                     1         15             1216      62208
24-MAY-12 db file scattered read                    1         28             1216      61696
25-MAY-12 db file scattered read                    1         67             1216      62208
25-MAY-12 db file sequential read                   1         67             1216      62208
28-MAY-12 direct path read                          3         45             1216      62464
28-MAY-12 direct path read                         96         70             1216      62464
29-MAY-12 direct path read                         10        484             1216      62464
29-MAY-12 direct path read                        137        643             1216      62464
29-MAY-12 enq: KO - fast object checkpoint          1        545             1216      62464

35 rows selected.

SQL> 

The FULL TABLE SCAN as the outer rowsource of a NESTED LOOP is a performance threat anyway but in summary, a performance problem, irrelevant plan stability and a key switch to direct path IO, not obviously caused by a significant growth in object size.

Regexp hungry for CPU? Real time sql monitoring shows just how

Not exactly a snappy blog post title…

First up, an execution plan showing the old problem of how the costs of a scalar subquery are not properly accounted for, even in the latest 11gR2 releases.
Read more of this post

ORA-00600 [kkqtSetOp.1] – Join Factorization

Just a quick note about an ORA-00600 that recently occurred following an upgrade to 11.2.0.3.

I’ve not been able to distill an isolated test case from the specific production code generating this error so I’ll skip the application-specific example.

The error message reported was:
Read more of this post

Timeslot SQL

A reminder about sql logic required to deal with express datetimes to the nearest x mins – for example, this particular question come up on an internal forum last week where the poster wanted to aggregate data per quarter hour.

There are two approaches depending on the exact requirement.

The first option is to generate the timeslots using a data generator and then join to the source data.

SQL>  alter session set nls_date_format = 'DD-MON-YYYY Hh24:MI';

Session altered.

SQL> with times as
  2  (select trunc(sysdate) + ((rownum-1)*15/60/24) slot
  3   from   dual
  4   connect by rownum <= 10)  --<-- just to keep the output short
  5  select * from times;

SLOT
-----------------
30-APR-2012 00:00
30-APR-2012 00:15
30-APR-2012 00:30
30-APR-2012 00:45
30-APR-2012 01:00
30-APR-2012 01:15
30-APR-2012 01:30
30-APR-2012 01:45
30-APR-2012 02:00
30-APR-2012 02:15

10 rows selected.

The second options is to round/floor/ceil the relevant existing date/timestamp in the table to the nearest x minutes.

For this solution, there is a generic approach as follows, demonstrated using sysdate:

SQL> select sysdate,
  2         trunc(sysdate)
  3       + floor(to_char(sysdate,'sssss') / 900) / 96 floor_slot,
  4         trunc(sysdate)
  5       + round(to_char(sysdate,'sssss') / 900) / 96 round_slot,
  6         trunc(sysdate)
  7       + ceil(to_char(sysdate,'sssss') / 900) / 96 ceil_slot
  8  from   dual;

SYSDATE           FLOOR_SLOT        ROUND_SLOT        CEIL_SLOT
----------------- ----------------- ----------------- -----------------
30-APR-2012 09:58 30-APR-2012 09:45 30-APR-2012 10:00 30-APR-2012 10:00

SQL> 

Where the basic forumla is:

 trunc(sysdate)
+ floor(to_char(sysdate,'sssss') / x ) / y 
  1. The date – example uses sysdate but this would probably be a column – truncated to start of day.
  2. The usage of FLOOR / ROUND / CEIL depends on the requirement.
  3. to_char(sysdate,’sssss’) is the number of seconds since midnight
  4. x is the number of seconds in the timeslot length we’re interested in – for the example, 15 minutes * 60 seconds per minute = 900
  5. Because date arithmetic is done in days, y is the number of timeslots in a day – for this example 4 timeslots per hour * 24 hours per day = 96

So, if we want five minute timeslots, x is 5*60=300; y is 12*24=288:

SQL> select sysdate,
  2         trunc(sysdate)
  3       + floor(to_char(sysdate,'sssss') / 300) / 288 floor_stamp,
  4         trunc(sysdate)
  5       + round(to_char(sysdate,'sssss') / 300) / 288 round_stamp,
  6         trunc(sysdate)
  7       + ceil(to_char(sysdate,'sssss') / 300) / 288 ceil_stamp
  8  from   dual;

SYSDATE           FLOOR_STAMP       ROUND_STAMP       CEIL_STAMP
----------------- ----------------- ----------------- -----------------
30-APR-2012 10:13 30-APR-2012 10:10 30-APR-2012 10:15 30-APR-2012 10:15

SQL> 

Or, for half hour timeslots, x is 60*30=1800; y is 2*24=48:

SQL> select sysdate,
  2         trunc(sysdate)
  3       + floor(to_char(sysdate,'sssss') / 1800) / 48 floor_stamp,
  4         trunc(sysdate)
  5       + round(to_char(sysdate,'sssss') / 1800) / 48 round_stamp,
  6         trunc(sysdate)
  7       + ceil(to_char(sysdate,'sssss') / 1800) / 48 ceil_stamp
  8  from   dual;

SYSDATE           FLOOR_STAMP       ROUND_STAMP       CEIL_STAMP
----------------- ----------------- ----------------- -----------------
30-APR-2012 10:19 30-APR-2012 10:00 30-APR-2012 10:30 30-APR-2012 10:30

SQL> 

SQL Patch IV – Why is the SQL Patch applied but not effective?

(or Part II Addendum)

In a comment on one of my previous articles about SQL Patches, a reader (hurrah!) effectively asks the question “Why is my SQL Patch being applied but the hint not obeyed?”

The original article itself was long enough without me adding a long comment so I’d like to break out into a fresh post.

Here is the test case.

Setup:

SQL> create table a_test as select * from dba_tables;

Table created.

SQL> create index a_test_idx on a_test(table_name);

Index created.

SQL> exec dbms_stats.gather_table_stats(user, 'a_test');

PL/SQL procedure successfully completed.

SQL> select table_name from a_test where table_name='xxx';

no rows selected

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  d4knkaxjhqpgw, child number 0
-------------------------------------
select table_name from a_test where table_name='xxx'

Plan hash value: 2434419982

-------------------------------------------------------------------------------
| Id  | Operation        | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |            |       |       |     1 (100)|          |
|*  1 |  INDEX RANGE SCAN| A_TEST_IDX |     1 |    18 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("TABLE_NAME"='xxx')

Create a patch to hint a FULL TABLE SCAN:

SQL> begin
  2     sys.dbms_sqldiag_internal.i_create_patch
  3     (sql_text  => 'select table_name from a_test a where table_name=''xxx''',
  4      hint_text => 'full(a)',
  5      name      => 'patch_test');
  6  end;
  7  /

PL/SQL procedure successfully completed.

See that the patch is applied but the hint apparently not obeyed:

SQL> select table_name from a_test a where table_name='xxx';

no rows selected

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------
SQL_ID  frmrwzdcc9p65, child number 0
-------------------------------------
select table_name from a_test a where table_name='xxx'

Plan hash value: 2434419982

-------------------------------------------------------------------------------
| Id  | Operation        | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |            |       |       |     1 (100)|          |
|*  1 |  INDEX RANGE SCAN| A_TEST_IDX |     1 |    18 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("TABLE_NAME"='xxx')

Note
-----
   - SQL patch "patch_test" used for this statement

The issue is that to use a SQL Patch to hint a statement, it seems that you have to be very careful to use the complete hint specification.

The complete hint specification is an ugly beast and should be considered a strong indicator that you really want to think twice before doing any hinting, and especially as Oracle versions march onwards and the Optimizer gets more complicated, the complete hint specification becomes ever more important.
See Jonathan Lewis’s How to Hint post for a good illustration.

Back to the test case…

If we drop the existing sql patch:

SQL> begin
  2   sys.dbms_sqldiag.drop_sql_patch('patch_test');
  3  end;
  4  /

PL/SQL procedure successfully completed.

Use the +OUTLINE format option for DBMS_XPLAN to get a nudge in the right direction:

SQL> select table_name from a_test a where table_name='xxx';

no rows selected

SQL> select * from table(dbms_xplan.display_cursor(format=> '+OUTLINE'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------
SQL_ID  frmrwzdcc9p65, child number 0
-------------------------------------
select table_name from a_test a where table_name='xxx'

Plan hash value: 2434419982

-------------------------------------------------------------------------------
| Id  | Operation        | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |            |       |       |     1 (100)|          |
|*  1 |  INDEX RANGE SCAN| A_TEST_IDX |     1 |    18 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      DB_VERSION('11.2.0.3')
      OPT_PARAM('optimizer_dynamic_sampling' 4)
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX(@"SEL$1" "A"@"SEL$1" ("A_TEST"."TABLE_NAME"))
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("TABLE_NAME"='xxx')

And create our patch with the complete specification:

SQL> begin
  2     sys.dbms_sqldiag_internal.i_create_patch
  3     (sql_text  => 'select table_name from a_test a where table_name=''xxx''',
  4      hint_text => 'FULL(@"SEL$1" "A"@"SEL$1")',
  5      name      => 'patch_test');
  6  end;
  7  /

PL/SQL procedure successfully completed.

We should find that it is more effective:

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------
SQL_ID  frmrwzdcc9p65, child number 0
-------------------------------------
select table_name from a_test a where table_name='xxx'

Plan hash value: 3679270240

----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |       |       |    40 (100)|          |
|*  1 |  TABLE ACCESS FULL| A_TEST |     1 |    18 |    40   (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("TABLE_NAME"='xxx')

Note
-----
   - SQL patch "patch_test" used for this statement


22 rows selected.

SQL> 

Bear in mind that the above is a test case only and consider what I said in the original article about what I consider to be the difference between a SQL Patch and a SQL Plan Baseline is and the different use cases.

Plan Problem with Partition Top N

Yesterday I was having issues with a poor choice of plan by the optimizer when trying to do a top N of a partitioned table.

SQL> CREATE TABLE t1
  2  (col1  varchar2(1)  not null
  3  ,col2  number       not null
  4  ,col3  varchar2(50) not null)
  5  PARTITION BY LIST (col1)
  6  (  
  7   PARTITION P1 VALUES ('A'),
  8   PARTITION P2 VALUES ('B'),
  9   PARTITION P3 VALUES ('C'),
 10   PARTITION P4 VALUES ('D')
 11  );

Table created.

SQL> INSERT 
  2  INTO   t1
  3  SELECT DECODE(MOD(ROWNUM,4),1,'A',2,'B',3,'C',0,'D') 
  4  ,      100000000+ROWNUM
  5  ,      LPAD('X',50,'X')
  6  FROM   DUAL
  7  CONNECT BY ROWNUM <= 1000000;

1000000 rows created.

SQL> CREATE UNIQUE INDEX i1 ON T1 (col2,col1) LOCAL;

Index created.

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'T1');

PL/SQL procedure successfully completed.

Now COL2 is unique but to be a unique index on a partitioned table, I have to include the partition key.

I want the top N of COL2 in descending order.

SQL> select /*+ gather_plan_statistics */
  2        *
  3  from (select col2 from t1 t order by col2 desc) 
  4  where rownum <=10;

      COL2
----------
 101000000
 100999999
 100999998
 100999997
 100999996
 100999995
 100999994
 100999993
 100999992
 100999991

10 rows selected.

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
SQL_ID  cwt7s3cmmw3vb, child number 0
-------------------------------------
select /*+ gather_plan_statistics */       * from (select col2 from t1
t order by col2 desc) where rownum <=10

Plan hash value: 738905059

-------------------------------------------------------------------------------------------
| Id  | Operation                | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |      |      1 |        |     10 |00:00:00.49 |    2698 |
|*  1 |  COUNT STOPKEY           |      |      1 |        |     10 |00:00:00.49 |    2698 |
|   2 |   VIEW                   |      |      1 |   1000K|     10 |00:00:00.49 |    2698 |
|*  3 |    SORT ORDER BY STOPKEY |      |      1 |   1000K|     10 |00:00:00.49 |    2698 |
|   4 |     PARTITION LIST ALL   |      |      1 |   1000K|   1000K|00:00:00.65 |    2698 |
|   5 |      INDEX FAST FULL SCAN| I1   |      4 |   1000K|   1000K|00:00:00.27 |    2698 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<=10)
   3 - filter(ROWNUM<=10)

Seems a strange option to me to do a FAST FULL SCAN and in the process visiting 2698 buffers.

Wouldn’t we much rather this did a descending index scan?

SQL> select /*+ gather_plan_statistics */
  2        *
  3  from (select /*+ index_desc (t i1) */ col2 from t1 t order by col2 desc) 
  4  where rownum <=10;

      COL2
----------
 101000000
 100999999
 100999998
 100999997
 100999996
 100999995
 100999994
 100999993
 100999992
 100999991

10 rows selected.

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
SQL_ID  f1ytxtp8bdgx6, child number 0
-------------------------------------
select /*+ gather_plan_statistics */       * from (select /*+
index_desc (t i1) */ col2 from t1 t order by col2 desc) where rownum
<=10

Plan hash value: 2521435439
-------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |      |      1 |        |     10 |00:00:00.01 |      12 |
|*  1 |  COUNT STOPKEY                  |      |      1 |        |     10 |00:00:00.01 |      12 |
|   2 |   VIEW                          |      |      1 |   1000K|     10 |00:00:00.01 |      12 |
|*  3 |    SORT ORDER BY STOPKEY        |      |      1 |   1000K|     10 |00:00:00.01 |      12 |
|   4 |     PARTITION LIST ALL          |      |      1 |   1000K|     40 |00:00:00.01 |      12 |
|*  5 |      COUNT STOPKEY              |      |      4 |        |     40 |00:00:00.01 |      12 |
|   6 |       INDEX FULL SCAN DESCENDING| I1   |      4 |   1000K|     40 |00:00:00.01 |      12 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<=10)
   3 - filter(ROWNUM<=10)
   5 - filter(ROWNUM<=10)

I’m glad that actually kicked in there as I wanted because on the real-world example that this relates to, it was being distinctly stubborn and at one point I thought I might have to resort to something distinctly ugly like:

SQL> select /*+ gather_plan_statistics */
  2         *
  3  from (select col2
  4        from   (select col2
  5                from   t1 partition (p1) t
  6                order by col2 desc) 
  7        where rownum <=10
  8        union all
  9        select col2
 10        from   (select col2
 11                from   t1 partition (p2) t
 12                order by col2 desc) 
 13        where rownum <=10
 14        union all
 15        select col2
 16        from   (select col2
 17                from   t1 partition (p3) t
 18                order by col2 desc) 
 19        where rownum <=10
 20        union all
 21        select col2
 22        from   (select col2
 23                from   t1 partition (p4) t
 24                order by col2 desc) 
 25        where rownum <=10
 26        order by col2 desc)
 27  where rownum <=10;

      COL2
----------
 101000000
 100999999
 100999998
 100999997
 100999996
 100999995
 100999994
 100999993
 100999992
 100999991

10 rows selected.

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------
SQL_ID  8y7d958mpah49, child number 0
-------------------------------------
select /*+ gather_plan_statistics */        * from (select col2
from   (select col2               from   t1 partition (p1) t
   order by col2 desc)       where rownum <=10       union all
select col2       from   (select col2               from   t1 partition
(p2) t               order by col2 desc)       where rownum <=10
union all       select col2       from   (select col2
from   t1 partition (p3) t               order by col2 desc)
where rownum <=10       union all       select col2       from
(select col2               from   t1 partition (p4) t
order by col2 desc)       where rownum <=10       order by col2 desc)
where rownum <=10

Plan hash value: 1726521473
---------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |      |      1 |        |     10 |00:00:00.01 |      12 |
|*  1 |  COUNT STOPKEY                    |      |      1 |        |     10 |00:00:00.01 |      12 |
|   2 |   VIEW                            |      |      1 |     40 |     10 |00:00:00.01 |      12 |
|*  3 |    SORT ORDER BY STOPKEY          |      |      1 |     40 |     10 |00:00:00.01 |      12 |
|   4 |     UNION-ALL                     |      |      1 |        |     40 |00:00:00.01 |      12 |
|*  5 |      COUNT STOPKEY                |      |      1 |        |     10 |00:00:00.01 |       3 |
|   6 |       VIEW                        |      |      1 |    250K|     10 |00:00:00.01 |       3 |
|   7 |        PARTITION LIST SINGLE      |      |      1 |    250K|     10 |00:00:00.01 |       3 |
|   8 |         INDEX FULL SCAN DESCENDING| I1   |      1 |    250K|     10 |00:00:00.01 |       3 |
|*  9 |      COUNT STOPKEY                |      |      1 |        |     10 |00:00:00.01 |       3 |
|  10 |       VIEW                        |      |      1 |    250K|     10 |00:00:00.01 |       3 |
|  11 |        PARTITION LIST SINGLE      |      |      1 |    250K|     10 |00:00:00.01 |       3 |
|  12 |         INDEX FULL SCAN DESCENDING| I1   |      1 |    250K|     10 |00:00:00.01 |       3 |
|* 13 |      COUNT STOPKEY                |      |      1 |        |     10 |00:00:00.01 |       3 |
|  14 |       VIEW                        |      |      1 |    250K|     10 |00:00:00.01 |       3 |
|  15 |        PARTITION LIST SINGLE      |      |      1 |    250K|     10 |00:00:00.01 |       3 |
|  16 |         INDEX FULL SCAN DESCENDING| I1   |      1 |    250K|     10 |00:00:00.01 |       3 |
|* 17 |      COUNT STOPKEY                |      |      1 |        |     10 |00:00:00.01 |       3 |
|  18 |       VIEW                        |      |      1 |     10 |     10 |00:00:00.01 |       3 |
|  19 |        PARTITION LIST SINGLE      |      |      1 |    250K|     10 |00:00:00.01 |       3 |
|  20 |         INDEX FULL SCAN DESCENDING| I1   |      1 |    250K|     10 |00:00:00.01 |       3 |
---------------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<=10)
   3 - filter(ROWNUM<=10)
   5 - filter(ROWNUM<=10)
   9 - filter(ROWNUM<=10)
  13 - filter(ROWNUM<=10)
  17 - filter(ROWNUM<=10)

But then the stubbornness disappeared and I couldn’t reproduce.
Conclusion – I must have been doing something stupid.

On a related note, whilst I was messing about, I noticed this.
Spot the trivial difference (not that it matters):

1
SQL> select *
  2  from (select col2 from t1 t order by col2 desc)
  3  where rownum <= 10;

........

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
SQL_ID  8uy80z5da45ct, child number 0
-------------------------------------
select * from (select col2 from t1 t order by col2 desc) where rownum
<= 10

Plan hash value: 3155368986

-------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |       |       |     3 (100)|          |
|*  1 |  COUNT STOPKEY               |      |       |       |            |          |
|   2 |   VIEW                       |      |    10 |   130 |     3   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN DESCENDING| I1   |  1000K|  6835K|     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<=10)


21 rows selected.

SQL> select *
  2  from (select /*+ index_desc(t i1) */ col2 from t1 t order by col2 desc)
  3  where rownum <= 10;

........

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
SQL_ID  3asrnmxg6bqsj, child number 0
-------------------------------------
select * from (select /*+ index_desc(t i1) */ col2 from t1 t order by
col2 desc) where rownum <= 10

Plan hash value: 3155368986

-------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |       |       |     3 (100)|          |
|*  1 |  COUNT STOPKEY               |      |       |       |            |          |
|   2 |   VIEW                       |      |    10 |   130 |     3   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN DESCENDING| I1   |    10 |    70 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<=10)


21 rows selected.

SQL> 

SQL Patch III / Parallel Baselines

From my previous investigation, I had one outstanding question at the back of my mind:

Can we apply more than one hint with a SQL Patch?

In addition, I had previously noted that SQL Plan baselines have issues if you have a plan that has to use a parallel hint to get parallel operations.

By investigating the latter question, we can answer the former.

Initial setup, same as examples in previous post.

SQL> create table t1  
  2  (col1  number  
  3  ,col2  varchar2(200)  
  4  ,flag  varchar2(1));  

Table created.

SQL> 
SQL> insert into t1  
  2  select rownum  
  3  ,      lpad('X',200,'X')  
  4  ,      case when mod(rownum,10000) = 0  
  5              then 'Y'  
  6              else 'N'  
  7         end  
  8  from   dual  
  9  connect by rownum <= 100000;  

100000 rows created.

SQL> create index i1 on t1 (col1);

Index created.

SQL> exec dbms_stats.gather_table_stats(USER,'T1'); 

PL/SQL procedure successfully completed.

SQL> 

Let’s run a piece of code that does an index access:

SQL> select * from t1 where col1 = 1;

      COL1 COL2                                                                  F
---------- --------------------------------------------------------------------- -
         1 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX N


SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
SQL_ID  81qv4d7vkb571, child number 0
-------------------------------------
select * from t1 where col1 = 1

Plan hash value: 1704772559

------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1   |     1 |   208 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | I1   |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("COL1"=1)


19 rows selected.

SQL> 

What about if I want to hint this to a parallel full table scan?

SQL> select /*+ full(t1) parallel(t1 4) */ * from t1 where col1 = 1;

      COL1 COL2                                                                  F
---------- --------------------------------------------------------------------- -
         1 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX N


SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  chhkmc32mdkak, child number 0
-------------------------------------
select /*+ full(t1) parallel(t1 4) */ * from t1 where col1 = 1

Plan hash value: 2494645258

----------------------------------------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |       |       |   264 (100)|          |        |      |            |
|   1 |  PX COORDINATOR      |          |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)| :TQ10000 |     1 |   208 |   264   (1)| 00:00:02 |  Q1,00 | P->S | QC
|   3 |    PX BLOCK ITERATOR |          |     1 |   208 |   264   (1)| 00:00:02 |  Q1,00 | PCWC |            |
|*  4 |     TABLE ACCESS FULL| T1       |     1 |   208 |   264   (1)| 00:00:02 |  Q1,00 | PCWP |            |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access(:Z>=:Z AND :Z<=:Z)
       filter("COL1"=1)


22 rows selected.

SQL> 

As expected.

Now, can I preserve this with a baseline?

SQL> declare     
  2    sqltext clob;     
  3    spm_op pls_integer;     
  4  begin     
  5    sqltext := 'select * from t1 where col1 = 1';     
  6    spm_op  :=     
  7    dbms_spm.load_plans_from_cursor_cache     
  8    (sql_id => 'chhkmc32mdkak',     
  9     plan_hash_value => 2494645258,     
 10     sql_text => sqltext);     
 11  end;     
 12  /

PL/SQL procedure successfully completed.

SQL> 

What happens when we re-run the original SQL?

SQL> select * from t1 where col1 = 1;

      COL1 COL2                                                                  F
---------- --------------------------------------------------------------------- -
         1 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX N


SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
SQL_ID  81qv4d7vkb571, child number 1
-------------------------------------
select * from t1 where col1 = 1

Plan hash value: 1704772559

------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1   |     1 |   208 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | I1   |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("COL1"=1)


19 rows selected.

SQL> 

Nothing. No baseline used.

The reason for this is that parallel is not in the plan hints but is essential to force our parallel plan and get the appropriate plan hash value.

Remember that a baseline must reproduce the stored plan hash or it will be ignored and the stored plan hash is SQLOBJ$.PLAN_ID which maps to the PLAN_HASH_2 value found in V$SQL_PLAN.OTHER_XML.

Let’s try to get the hints in the baseline:

SQL> select to_char(exact_matching_signature) from v$sql where sql_id = '81qv4d7vkb571';

TO_CHAR(EXACT_MATCHING_SIGNATURE)
----------------------------------------
9005682359107037619

SQL> select cast(extractvalue(value(x), '/hint') as varchar2(500)) as outline_hints  
  2  from   xmltable('/outline_data/hint'  
  3         passing (select xmltype(comp_data) xml  
  4                  from   sys.sqlobj$data  
  5                  where  signature = 9005682359107037619)) x; 
       passing (select xmltype(comp_data) xml
                *
ERROR at line 3:
ORA-01427: single-row subquery returns more than one row

SQL> 

Is that interesting? There’s now more than one baseline – should not be surprising if you are familiar with the baseline mechanism.

DBA_SQL_PLAN_BASELINES doesn’t give us everything we need to investigate so back under the covers:

SQL> SELECT TO_CHAR(so.signature) signature
  2  ,      so.plan_id
  3  ,      DECODE(ad.origin, 1, 'MANUAL-LOAD',
  4                        2, 'AUTO-CAPTURE',
  5                        3, 'MANUAL-SQLTUNE',
  6                        4, 'AUTO-SQLTUNE',
  7                        5, 'STORED-OUTLINE',
  8                           'UNKNOWN') origin
  9  ,      DECODE(BITAND(so.flags, 1), 1, 'YES', 'NO') enabled
 10  ,      DECODE(BITAND(so.flags, 2), 2, 'YES', 'NO') accepted
 11  ,      DECODE(BITAND(so.flags, 64), 64, 'NO', 'YES') reproduced
 12  FROM   sys.sqlobj$        so
 13  ,      sys.sqlobj$auxdata ad
 14  WHERE  ad.signature = so.signature 
 15  AND    ad.plan_id   = so.plan_id
 16  AND    so.signature = 9005682359107037619;

SIGNATURE                                   PLAN_ID ORIGIN         ENA ACC REP
---------------------------------------- ---------- -------------- --- --- ---
9005682359107037619                       263533726 MANUAL-LOAD    YES YES NO
9005682359107037619                      3860916006 AUTO-CAPTURE   YES NO  YES

SQL> 

The plan for our manually created baseline was not reproducible so the optimizer ignores it, generates its best plan and stores that as an AUTO_CAPTURE plan for future evolution and acceptance. The AUTO_CAPTURE plan gives the outline for the original index scan that makes the most sense but we’re not interested in that.

Here are the hints for our manually loaded baseline that failed to reproduce the plan:

SQL> select cast(extractvalue(value(x), '/hint') as varchar2(500)) as outline_hints  
  2  from   xmltable('/outline_data/hint'  
  3         passing (select xmltype(comp_data) xml  
  4                  from   sys.sqlobj$data  
  5                  where  signature = 9005682359107037619
  6                  and    plan_id   = 263533726)) x;  

OUTLINE_HINTS
---------------------------------------------------------------------------------------------
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
OPT_PARAM('optimizer_dynamic_sampling' 4)
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "T1"@"SEL$1")

7 rows selected.

SQL> 

You can see the FULL hint but no PARALLEL.
With no PARALLEL hint, we can’t get the hash for the parallel plan that our baseline requires.

Shall we just check a SPM trace to verify what we see when a baseline cannot be reproduced?

SQL> alter session set tracefile_identifier = 'dom_spm';

Session altered.

SQL> alter session set events 'trace[RDBMS.SQL_Plan_Management.*]';

Session altered.

SQL> 
SQL> select * from t1 where col1 = 1;

      COL1 COL2                                                                  F
---------- --------------------------------------------------------------------- -
         1 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX N


SQL> 
SQL> alter session set events 'trace[RDBMS.SQL_Plan_Management.*] off';

Session altered.

SQL> 

And in the trace:

SPM: statement found in SMB
SPM: planId's of plan baseline are: 263533726
SPM: using qksan to reproduce, cost and select accepted plan, sig = 9005682359107037619
SPM: plan reproducibility round 1 (plan outline + session OFE)
SPM: using qksan to reproduce accepted plan, planId = 263533726
SPM: planId in plan baseline = 263533726, planId of reproduced plan = 3688435342
------- START SPM Plan Dump -------
SPM: failed to reproduce the plan using the following info:
  parse_schema name        : RIMS
  plan_baseline signature  : 9005682359107037619
  plan_baseline plan_id    : 263533726
  plan_baseline hintset    :
    hint num  1 len 27 text: IGNORE_OPTIM_EMBEDDED_HINTS
    hint num  2 len 37 text: OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
    hint num  3 len 22 text: DB_VERSION('11.2.0.3')
    hint num  4 len 41 text: OPT_PARAM('optimizer_dynamic_sampling' 4)
    hint num  5 len  8 text: ALL_ROWS
    hint num  6 len 22 text: OUTLINE_LEAF(@"SEL$1")
    hint num  7 len 27 text: FULL(@"SEL$1" "T1"@"SEL$1")
SPM: generated non-matching plan:
...
SPM: plan reproducibility round 2 (hinted OFE only)
SPM: using qksan to reproduce accepted plan, planId = 263533726
SPM: planId in plan baseline = 263533726, planId of reproduced plan = 3860916006
------- START SPM Plan Dump -------
SPM: failed to reproduce the plan using the following info:
  parse_schema name        : RIMS
  plan_baseline signature  : 9005682359107037619
  plan_baseline plan_id    : 263533726
  plan_baseline hintset    :
    hint num  1 len 37 text: OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
SPM: generated non-matching plan:
...
------- END SPM Plan Dump -------
SPM: couldn't reproduce any enabled+accepted plan so using the cost-based plan, planId = 3860916006

The Optimizer has two attempts are reproducing the required plan – the first one with the full set of hints, the second with just the OFE hint before reverting back to the best cost plan and storing that as mentioned as an AUTO-CAPTURE plan.

So, it seems as if using just a baseline to preserve parallelism does not work.

And perhaps that’s not surprising if you know what the PARALLEL hint does – it doesn’t force parallelism.

I understand what’s going here but but I’m not convinced by this baseline behaviour.

If a PARALLEL hint is required to get a specific plan, I’d expect it to be preserved in the baseline but the bottom line, I suppose, is that it’s not preserved in the hints in V$SQL_PLAN.OTHER_XML.

So, can SQL Patch help here?

Leaving our baseline in place and just adding a SQL Patch (i’ve said before that a SQL Patch can seem quite fussy
about using the proper hint syntax):

SQL> begin  
  2    sys.dbms_sqldiag_internal.i_create_patch  
  3    (sql_text  => 'select * from t1 where col1 = 1',  
  4     hint_text => 'PARALLEL(@"SEL$1" "T1"@"SEL$1" 4)',  
  5     name      => 'patch_test');   
  6  end;  
  7  /  

PL/SQL procedure successfully completed.

SQL> 

Shall we try the SQL again?

SQL> select * from t1 where col1 = 1;

      COL1 COL2                                                                  F
---------- --------------------------------------------------------------------- -
         1 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX N


SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
SQL_ID  81qv4d7vkb571, child number 0
-------------------------------------
select * from t1 where col1 = 1

Plan hash value: 1704772559

------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1   |     1 |   208 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | I1   |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("COL1"=1)

Note
-----
   - SQL patch "patch_test" used for this statement


23 rows selected.

SQL> 

So SQL Patch + SQL Baseline does not work as desired – the Baseline by itself can’t reproduce the desired plan and the Patch hint is irrelevant once the best cost plan is picked.

So, how about just using a SQL Patch with multiple hints? Can that work?

SQL> declare
  2   l_op pls_integer;
  3  begin
  4   l_op :=
  5   dbms_spm.drop_sql_plan_baseline('SQL_7cfa9c643693a9b3');
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> begin  
  2    sys.dbms_sqldiag.drop_sql_patch('patch_test');   
  3  end;  
  4  / 

PL/SQL procedure successfully completed.

SQL> begin  
  2    sys.dbms_sqldiag_internal.i_create_patch  
  3    (sql_text  => 'select * from t1 where col1 = 1',  
  4     hint_text => 'PARALLEL(@"SEL$1" "T1"@"SEL$1" 4) FULL(@"SEL$1" "T1"@"SEL$1")',  
  5     name      => 'patch_test');   
  6  end;  
  7  / 

PL/SQL procedure successfully completed.

SQL> select * from t1 where col1 = 1;  

      COL1 COL2                                                                  F
---------- --------------------------------------------------------------------- -
         1 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX N

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  81qv4d7vkb571, child number 0
-------------------------------------
select * from t1 where col1 = 1

Plan hash value: 2494645258

----------------------------------------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |       |       |   264 (100)|          |        |      |            |
|   1 |  PX COORDINATOR      |          |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)| :TQ10000 |     1 |   208 |   264   (1)| 00:00:02 |  Q1,00 | P->S | QC
|   3 |    PX BLOCK ITERATOR |          |     1 |   208 |   264   (1)| 00:00:02 |  Q1,00 | PCWC |            |
|*  4 |     TABLE ACCESS FULL| T1       |     1 |   208 |   264   (1)| 00:00:02 |  Q1,00 | PCWP |            |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access(:Z>=:Z AND :Z<=:Z)
       filter("COL1"=1)

Note
-----
   - SQL patch "patch_test" used for this statement


26 rows selected.

SQL>

Success!

So, in summary, it looks like SQL Plan baselines cannot preserve PARALLEL but a SQL Patch can apply it.

And, at the same time, this shows that you can apply multiple hints in one SQL Patch.

By the way, above I made a throwaway comment above that the PARALLEL hint is not preserved in V$SQL_PLAN.OTHER_XML which is why it’s not preserved in a baseline. We can use the ‘+OUTLINE’ format to prove this from that last example above with the SQL Patch:

SQL> select * from table(dbms_xplan.display_cursor(format=>'+OUTLINE'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  81qv4d7vkb571, child number 0
-------------------------------------
select * from t1 where col1 = 1

Plan hash value: 2494645258

----------------------------------------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |       |       |   264 (100)|          |        |      |            |
|   1 |  PX COORDINATOR      |          |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)| :TQ10000 |     1 |   208 |   264   (1)| 00:00:02 |  Q1,00 | P->S | QC
|   3 |    PX BLOCK ITERATOR |          |     1 |   208 |   264   (1)| 00:00:02 |  Q1,00 | PCWC |            |
|*  4 |     TABLE ACCESS FULL| T1       |     1 |   208 |   264   (1)| 00:00:02 |  Q1,00 | PCWP |            |
----------------------------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      DB_VERSION('11.2.0.3')
      OPT_PARAM('optimizer_dynamic_sampling' 4)
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "T1"@"SEL$1")
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access(:Z>=:Z AND :Z<=:Z)
       filter("COL1"=1)

Note
-----
   - SQL patch "patch_test" used for this statement


41 rows selected.

SQL> 

And finally…

If you’ve been looking at the undocumented DBMS_SQLDIAG_INTERNAL package that you need to create a SQL Patch, you might have noticed that alongside I_CREATE_PATCH there is an I_CREATE_HINTSET procedure.

This isn’t another new type of SQL Object but an internal api that probably all these SQL Objects use. So, if you try to create a hintset with I_CREATE_HINTSET then by default it will create a SQL Profile that can be dropped via DBMS_SQLTUNE.DROP_SQL_PROFILE.
Note that DBMS_SPM_INTERNAL has an interface to GET_SPM_HINTSET that amongst others takes an OBJ_TYPE parameter.

Follow

Get every new post delivered to your Inbox.

Join 69 other followers