SQL Patch II

Following on from my previous post, let’s look at some of the workings of a SQL patch to try to get a bigger picture about this little-known feature.

Warning – this is quite a long post.

Let’s start with a similar example to that on the Oracle Optimizer blog article.

First, let’s setup a table that might work with the BIND_AWARE hint as per the Optimizer blog example.

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

Table created.

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> select flag, count(*) from t1 group by flag;  

F   COUNT(*)
- ----------
Y         10
N      99990

SQL> create index i1 on t1 (flag);

Index created.

SQL> exec dbms_stats.gather_table_stats(USER,'T1',method_opt=>'FOR COLUMNS flag SIZE 5');

PL/SQL procedure successfully completed.

And now a query with some bind variables.
Let’s execute this three or four times per bind variable:

SQL> var n varchar2(1)
SQL> exec :n := 'N'

PL/SQL procedure successfully completed.

SQL> select count(*), max(col2) from t1 where flag = :n;

  COUNT(*) MAX(COL2)
---------- -------------------------------------------------------------------
     99990 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

SQL> /
.... etc...

SQL> exec :n := 'Y';

PL/SQL procedure successfully completed.

SQL> select count(*), max(col2) from t1 where flag = :n;

  COUNT(*) MAX(COL2)
---------- -------------------------------------------------------------------
        10 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

SQL> /
.... etc...

Let’s double check that we have a sql statement that the BIND_AWARE hint might be applicable to:

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

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------
SQL_ID  731b98a8u0knf, child number 1
-------------------------------------
select count(*), max(col2) from t1 where flag = :n

Plan hash value: 3625400295

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

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

   3 - access("FLAG"=:N)


20 rows selected.

SQL> select sql_id, is_bind_aware, to_char(exact_matching_signature) sig, executions
  2  from   v$sql
  3  where  sql_id = '731b98a8u0knf';

SQL_ID        I SIG                                      EXECUTIONS
------------- - ---------------------------------------- ----------
731b98a8u0knf N 1292784087274697613                               6
731b98a8u0knf Y 1292784087274697613                               1

So far so good – we know we have a table and a statement that can be bind aware.

The next steps – as per the Optimizer blog – are to show how we can get the BIND_AWARE hint to apply from the first execution.

So, how about we flush this statement above and use the SQL Patch functionality to inject the BIND_AWARE hint.

SQL> select sql_id, sql_text, is_bind_aware, to_char(exact_matching_signature) sig, executions
  2  from   v$sql
  3  where  sql_id = '731b98a8u0knf';

no rows selected

SQL> begin
  2   sys.dbms_sqldiag_internal.i_create_patch
  3   (sql_text  => 'select count(*), max(col2) from t1 where flag = :n',
  4    hint_text => 'BIND_AWARE',
  5    name      => 'patch_test'); 
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> select count(*), max(col2) from t1 where flag = :n;

  COUNT(*) MAX(COL2)
---------- -------------------------------------------------------------------
        10 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
SQL_ID  731b98a8u0knf, child number 0
-------------------------------------
select count(*), max(col2) from t1 where flag = :n

Plan hash value: 3625400295

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

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

   3 - access("FLAG"=:N)

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


24 rows selected.

SQL> select sql_id, sql_text, is_bind_aware, to_char(exact_matching_signature) sig, executions
  2  from   v$sql
  3  where  sql_id = '731b98a8u0knf';

SQL_ID        I SIG                                      EXECUTIONS
------------- - ---------------------------------------- ----------
731b98a8u0knf Y 1292784087274697613                               1

SQL> 

Great – it all works as per the Optimizer team told us it would.

The execution plan has one of those invaluable Notes confirming that a SQL patch was used.

So, next up, why can’t we use a baseline to do this?

Well, as mentioned previously, it’s not because baselines can’t change execution plans in a similar way.

Au contraire, they can and, in most circumstances, they are probably the best placed feature to do this using DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE.

What they can’t do is inject the BIND_AWARE hint.

Allow me to demonstrate.

The approach with a baseline is slightly more complicated because we have to generate the required plan with one bit of sql and then, while it’s still in the cursor cache, transfer the whole plan into a baseline for the target sql statement.

First, let me delete that SQL Patch and verify that nothing is affecting our target statement.

SQL> exec dbms_sqldiag.drop_sql_patch('patch_test');

PL/SQL procedure successfully completed.

SQL> select count(*), max(col2) from t1 t1 where flag = :n;

  COUNT(*) MAX(COL2)
---------- -------------------------------------------------------------------
        10 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX


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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  5ya8tff6fdrsa, child number 0
-------------------------------------
select count(*), max(col2) from t1 t1 where flag = :n

Plan hash value: 3625400295

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

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

   3 - access("FLAG"=:N)


20 rows selected.

SQL> 

Then let’s get a plan for a statement with the BIND_AWARE hint.

SQL> select /*+ bind_aware */ count(*), max(col2) from t1 t1 where flag = :n;

  COUNT(*) MAX(COL2)
---------- -------------------------------------------------------------------
        10 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX


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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  7qzu8gp22qr0v, child number 0
-------------------------------------
select /*+ bind_aware */ count(*), max(col2) from t1 t1 where flag = :n

Plan hash value: 3625400295

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

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

   3 - access("FLAG"=:N)


20 rows selected.

SQL> select sql_id, sql_text, is_bind_aware, to_char(exact_matching_signature) sig, executions
  2  from   v$sql
  3  where  sql_id in ('731b98a8u0knf','7qzu8gp22qr0v');

SQL_ID        I SIG                                      EXECUTIONS
------------- - ---------------------------------------- ----------
7qzu8gp22qr0v Y 17219147247362199511                              1

SQL> 

Our new hinted statement is instantly bind aware when hinted as such.

Now, let’s load this plan from the cursor cache to the unhinted target statement using the sql_id and plan_hash_value from the DBMS_XPLAN output above.

SQL> declare   
  2    sqltext clob;   
  3    spm_op pls_integer;   
  4  begin   
  5    sqltext := 'select count(*), max(col2) from t1 where flag = :n';   
  6    spm_op  :=   
  7    dbms_spm.load_plans_from_cursor_cache   
  8    (sql_id => '7qzu8gp22qr0v',   
  9     plan_hash_value => 3625400295,   
 10     sql_text => sqltext);   
 11  end;   
 12  /   

PL/SQL procedure successfully completed.

And let’s see what difference the baseline makes:

SQL> select count(*), max(col2) from t1 where flag = :n;

  COUNT(*) MAX(COL2)
---------- -------------------------------------------------------------------
        10 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX


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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  731b98a8u0knf, child number 0
-------------------------------------
select count(*), max(col2) from t1 where flag = :n

Plan hash value: 3625400295

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

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

   3 - access("FLAG"=:N)

Note
-----
   - SQL plan baseline SQL_PLAN_13w748wknkcwd8576eb1f used for this statement


24 rows selected.

Great! So our baseline is in full effect.

But, is the statement bind aware as it was the SQL Patch was applied?

SQL> select sql_id, sql_text, is_bind_aware, to_char(exact_matching_signature) sig, executions
  2  from   v$sql
  3  where  sql_id in ('731b98a8u0knf');

SQL_ID        I SIG                                      EXECUTIONS
------------- - ---------------------------------------- ----------
731b98a8u0knf N 1292784087274697613                               1

No, it’s not.

Now, why should this be?

Let’s recap the different intentions of SQL Baselines and SQL Patches

SQL Baselines exist to reproduce a specific plan. In fact, a plan hash exists as part of the baseline.
If, on application of the baseline, the Optimizer is unable to reproduce the desired plan, the baseline is rejected outright.

On the other hand, SQL Patches have been developed primarily to get the Optimizer to avoid a particular problem path in an execution plan, specifically to avoid failures due to certain access methods, join methods, etc.

Or to put it another way, the answer is in the internals.

Let me recreate the patch again:

SQL> begin
  2   sys.dbms_sqldiag_internal.i_create_patch
  3   (sql_text  => 'select count(*), max(col2) from t1 where flag = :n',
  4    hint_text => 'BIND_AWARE',
  5    name      => 'patch_test'); 
  6  end;
  7  /

PL/SQL procedure successfully completed.

You could look at DBA_SQL_PATCHES but it doesn’t tell you very much.

Or at least it does – for example the FORCE_MATCHING column indicates that SQL Patches have the same force matching ability as SQL Profiles and which Baselines don’t have – but it doesn’t show what I want you to see.

It can be a bit more revealing to look around under the hood at SYS.SQLOBJ$, SYS.SQLOBJ$DATA and SYS.SQLOBJ$AUXDATA.

Using the signature from V$SQL above:

SQL> select signature, obj_type, plan_id, name, flags, last_executed
  2  from sys.sqlobj$ 
  3  where signature = 1292784087274697613;

 SIGNATURE   OBJ_TYPE    PLAN_ID NAME                                FLAGS LAST_EXECUTED
---------- ---------- ---------- ------------------------------ ---------- -------------------------
1.2928E+18          2 2239163167 SQL_PLAN_13w748wknkcwd8576eb1f         11 06-MAR-12 03.01.19.000000 PM
1.2928E+18          3          0 patch_test                              1

Profiles have an OBJ_TYPE of 1.

Our Baseline has a type of 2.

The Patch has a type of 3.

The flags translate to ENABLED/DISABLED for SQL Profiles and SQL Patches and to values for ENABLED, ACCEPTED, FIXED, REPRODUCED and AUTOPURGE for SQL Plan Baselines.

Another important aspect is that PLAN_ID.
I mentioned that Baselines are designed to reproduce a specific plan and if the optimizer is unable to reproduce the specific plan when applying the baseline then it won’t use it. Plan_id is another hash, not plan_hash_value but plan_hash_2 from V$SQL.OTHER_XML.

There’s a contrast of information elsewhere.
For example, SQLOBJ$AUXDATA will highlight the performance metrics that are tracked for Baselines to aid evolution.

But it’s the hints that really should be interesting us, for that we need to look at SQLOBJ$DATA.

Here’s what our SQL Patch uses:

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 = 1292784087274697613
  6                  and    obj_type  = 3)) x;

OUTLINE_HINTS
-------------------------------------------------------------------------------------------
BIND_AWARE

SQL> 

Just what we asked it to do – only inject BIND_AWARE

And our baseline contains all that is required to reproduce the exact plan it was asked to baseline.

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 = 1292784087274697613
  6                  and    obj_type  = 2)) 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")
INDEX_RS_ASC(@"SEL$1" "T1"@"SEL$1" ("T1"."FLAG"))

7 rows selected.

SQL> 

No sign of BIND_AWARE – only the hints that are required to reproduce the exact plan we asked for. And in fact BIND_AWARE might be a threat to that plan stability.

And so to the question of whether a SQL Patch and a Baseline can work together?

And the answer is yes PROVIDED that the hints are such that the pair can happily co-exist:

SQL> select count(*), max(col2) from t1 where flag = :n;

  COUNT(*) MAX(COL2)
---------- -------------------------------------------------------------------
        10 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX


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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  731b98a8u0knf, child number 0
-------------------------------------
select count(*), max(col2) from t1 where flag = :n

Plan hash value: 3625400295

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

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

   3 - access("FLAG"=:N)

Note
-----
   - SQL patch "patch_test" used for this statement
   - SQL plan baseline SQL_PLAN_13w748wknkcwd8576eb1f used for this statement


25 rows selected.

SQL> select sql_id, sql_text, is_bind_aware, to_char(exact_matching_signature) sig, executions
  2  from   v$sql
  3  where  sql_id in ('731b98a8u0knf');

SQL_ID        I SIG                                      EXECUTIONS
------------- - ---------------------------------------- ----------
731b98a8u0knf Y 1292784087274697613                               1

SQL> 

What happens if we try to create a SQL Patch that conflicts with the instructions of the Baseline?

Well, first note that only one SQL Patch can exist for statement at any one time (in any one category as per sql profile functionality):

SQL> begin
  2   sys.dbms_sqldiag_internal.i_create_patch
  3   (sql_text  => 'select count(*), max(col2) from t1 where flag = :n',
  4    hint_text => 'FULL(t1)',
  5    name      => 'patch_test2'); 
  6  end;
  7  /
begin
*
ERROR at line 1:
ORA-13830: SQL profile with category DEFAULT already exists for this SQL statement
ORA-06512: at "SYS.DBMS_SQLTUNE_INTERNAL", line 16167
ORA-06512: at "SYS.DBMS_SQLDIAG_INTERNAL", line 204
ORA-06512: at line 2

So, let’s delete the existing SQL Patch and check that we’ve still got a baseline working (I’ve recreated the baseline since the demo started so it’s got a different name):

SQL> exec dbms_sqldiag.drop_sql_patch('patch_test');

PL/SQL procedure successfully completed.

SQL> select count(*), max(col2) from t1 where flag = :n;

  COUNT(*) MAX(COL2)
---------- -------------------------------------------------------------------
        10 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX


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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  731b98a8u0knf, child number 0
-------------------------------------
select count(*), max(col2) from t1 where flag = :n

Plan hash value: 3625400295

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

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

   3 - access("FLAG"=:N)

Note
-----
   - SQL plan baseline SQL_PLAN_13w748wknkcwd8576eb1f used for this statement


24 rows selected.

SQL> 

And let’s try to create one that will conflict with the index hints in the Baseline (note that you seem to have to be very particular about the full and proper hint syntax, i.e. just a FULL(t1) wasn’t cutting it):

SQL> begin
  2   sys.dbms_sqldiag_internal.i_create_patch
  3   (sql_text  => 'select count(*), max(col2) from t1 where flag = :n',
  4    hint_text => 'FULL(@"SEL$1" "T1"@"SEL$1")',
  5    name      => 'patch_test2'); 
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> 

And in this scenario, it seems that Patch trumps Baseline:

SQL> select count(*), max(col2) from t1 where flag = :n;

  COUNT(*) MAX(COL2)
---------- -------------------------------------------------------------------
        10 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX


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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  731b98a8u0knf, child number 0
-------------------------------------
select count(*), max(col2) from t1 where flag = :n

Plan hash value: 3724264953

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   956 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |   104 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   |    18 |  1872 |   956   (2)| 00:00:05 |
---------------------------------------------------------------------------

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

   2 - filter("FLAG"=:N)

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


23 rows selected.

which is probably what we’d expect.

That’ll do for now.

About these ads

14 Responses to SQL Patch II

  1. goryszewskig says:

    Great post, thank You.
    Do You think profiles will behave the same as baselines so You cant hnt bind_aware with profiles ?
    Regards
    GregG

    • Dom Brooks says:

      Hi Greg,

      That’s an interesting question.

      In the underlying SQLOBJ$ tables, SQL profiles have a plan_id of 0 – i.e. it’s only Baselines that care about the actual plan that’s generated.

      In general, SQL Profiles use hints like OPT_ESTIMATE to adjust optimizer estimates so you’d never have a system-generated profile that used something like a BIND_AWARE hint.

      But we could try the dbms_sqltune.import_sql_profile to manually create one to see what happens.

      Profiles (and outlines) are not features that I’ve ever spent much time with despite spending most of my time over the past 15 years tuning sql.

      I can see really useful niches for Baselines – particularly for upgrades and priority performance changes without changing the source code – and for Patches – to avoid critical failures from certain executions paths

      I’m not sure what the future is for Profiles…

      And of course, it’s worth mentioning that Profiles require a Diagnostic and Tuning pack (or at least they used to) whereas baselines and patches are standard features of EE – a strange anomaly.

      Cheers,
      Dominic

  2. Pingback: SQL Patch III plus Parallel Baselines « OraStory

  3. Pingback: SQL Patch I « OraStory

  4. Jinwen Zou says:

    Hi Dominic,

    Interesting post.
    As you tested: some of the hints will not be picked up by baselines. So I plan to use sql patch to add some hints into a packaged application.

    The result seems to be that the hints was injected, but will not been used.

    Is there any special requirement to use SQL patch or profiles?

    Notes: there is no baseline for the select statement, and control_management_pack_access=DIAGNOSTIC+TUNING, running hp-ux with Oracle Release 11.2.0.1.0

    Thanks,
    Jinwen Zou

  5. Pingback: SQL Patch IV (or Part II Addendum) – Why is the SQL Patch applied but not effective? « OraStory

  6. Wenju Weng says:

    Hi Dominic, Great post! I think it’s helping me big time since we are running into bug with plan baselines or SQL Profile causing high version count in 11.2.0.2.6 for certain queries. And I just tried replacing it with SQL Patch for a simple query, and so far it seems to be working! Since we need to enforce optimal execution plans for certain queries (optimizer is unable to derive the best plan), we really need such plan enforcing methods.
    I have a question. How do we concatenate multiple hints together for the hint_text?
    thanks in advance for your help!! Wenju Weng

    • Dom Brooks says:

      Hi,

      Thanks for commenting.
      As far as I remember, you just specify a longer hint text – e.g. “hint1 hint2″ – but I’ve not got a DB to hand to verify.

      Cheers,
      Dominic

  7. Wenju Weng says:

    Thanks, Dominic. I’ve used concatenation of hints for a couple of queries in the SQL Patches, and it seems to be working fine. I’ve modified a few top queries that we ran into high version count with plan baselines or sql profiles to use SQL patches instead in our production systems, and so far they are working great. Hopefully there’s no known issues (?) with SQL patches since I’m about to convert all our plan baselines/SQL Profiles into SQL Patches for queries that we need to enforce the optimal execution plans. Wenju

    • Dom Brooks says:

      Converting everyting to a SQL Patch sounds unwise but needs must I suppose.
      SQL Patches lack the plan enforcement functionality of SQL Plan Baselines.
      Can you not expedite a patch for the underlying bug causing the high version count?

      I’m slightly surprised that the bug would cause issues with Baselines and Profiles but not SQL Patches.
      Do you have a bug number?

      • Wenju Weng says:

        Hi, Dom, we have been working with Oracle Support on this, and they devised a backported patch for us which didn’t resolve the high version count issue we have. So far, I have only applied SQL Patches to a handful of most critical queries and the system performance has already been improved back to before we had encountered this bug (introduced in SBP 11.2.0.2.3).
        I understand that SQL Patches are not appropriate for queries that we want Optimizer to evolve plans for and can not act as replacement for SQL Plan baselines. I plan on converting 300+ SQL queries that we have enforced optimal execution plans with SQL Profile to use SQL Patches instead. For rest of queries (we have more than 10K distinct application queries and binding variables are used, it’s a SAP environment), I will leave the plan baselines on them for plan stability. Meanwhile, I set up a job to check Cursor Mutex waits which are the product of high version count when certain thresholds reached. When the cursor mutex waits reach over certain limits, then the job simply drops the sql profile or plan baselines.
        This would be our solution until Oracle can come up with a more permanent solution to fix the bug associated with plan baselines or sql profiles.
        These are relevant bugs – 14029891, 12902872. The backported patch Oracle devised for us is 14641531.
        Cheers, Wenju

  8. Pingback: Dynamic Sampling – 2 | Oracle Scratchpad

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 75 other followers

%d bloggers like this: