SQL Patch II
March 6, 2012 16 Comments
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.
OraStory 
Great post, thank You.
Do You think profiles will behave the same as baselines so You cant hnt bind_aware with profiles ?
Regards
GregG
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
Pingback: SQL Patch III plus Parallel Baselines « OraStory
Pingback: SQL Patch I « OraStory
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
Hi Jinwen,
Thanks for the comment (I need to moderate comments before they appear hence the delay).
Baselines and sql patches don’t need extra licensing other than EE.
What is the setting for parameter optimizer_use_sql_plan_baselines?
If it’s not parameter settings, then maybe try an SPM trace?
If you can’t get to the bottom of it and can’t paste in your test case, then feel free to email it to me – dombrooks@hotmail.com – and I’ll take a look.
Cheers,
Dominic
Hi Jinwen,
I received your test case.
As this was a long article already, I’ve broken it out into a separate post.
See: https://orastory.wordpress.com/2012/03/29/sql-patch-iv-or-part-ii-addendum-why-is-the-sql-patch-applied-but-not-effective/
I hope I’ve interpreted the issue correctly
Thanks for your interest.
Regards,
Dominic
Pingback: SQL Patch IV (or Part II Addendum) – Why is the SQL Patch applied but not effective? « OraStory
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
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
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
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?
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
Pingback: Dynamic Sampling – 2 | Oracle Scratchpad
Pingback: SQL Patch: Você já usou esse recurso? | DBTimeWizard
Pingback: Utilizar HINT em consulta ajuda ou atrapalha? | DBTimeWizard