If you’re familiar with SQL Profiles and SQL Baselines you may also know about SQL Patches – a feature that allows you to construct hints that you can attach to SQL statements at run-time without changing the code. Oracle 12c Release 2 introduces a couple of important changes to this feature:
- It’s now official – the feature had been copied from package dbms_sqldiag_internal to package dbms_sqldiag.
- The limitation of 500 characters has been removed from the hint text – it’s now a CLOB column [ed: but see Threat Warning]
H/T to Nigel Bayliss of Oracle Corp. for including this detail in his presentation to the UKOUG last week, and pointing out that it’s also available for Standard Edition.
There are a couple of other little changes as you can see below from the two extract from the 12.2 declarations of dbms_sqldiag and dbms_sqldiag_internal below:
dbms_sqldiag ------------ FUNCTION CREATE_SQL_PATCH RETURNS VARCHAR2 Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- SQL_TEXT CLOB IN HINT_TEXT CLOB IN NAME VARCHAR2 IN DEFAULT DESCRIPTION VARCHAR2 IN DEFAULT CATEGORY VARCHAR2 IN DEFAULT VALIDATE BOOLEAN IN DEFAULT FUNCTION CREATE_SQL_PATCH RETURNS VARCHAR2 Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- SQL_ID VARCHAR2 IN HINT_TEXT CLOB IN NAME VARCHAR2 IN DEFAULT DESCRIPTION VARCHAR2 IN DEFAULT CATEGORY VARCHAR2 IN DEFAULT VALIDATE BOOLEAN IN DEFAULT dbms_sqldiag_internal --------------------- FUNCTION I_CREATE_PATCH RETURNS VARCHAR2 Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- SQL_ID VARCHAR2 IN HINT_TEXT CLOB IN CREATOR VARCHAR2 IN NAME VARCHAR2 IN DEFAULT DESCRIPTION VARCHAR2 IN DEFAULT CATEGORY VARCHAR2 IN DEFAULT VALIDATE BOOLEAN IN DEFAULT FUNCTION I_CREATE_PATCH RETURNS VARCHAR2 Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- SQL_TEXT CLOB IN HINT_TEXT CLOB IN CREATOR VARCHAR2 IN NAME VARCHAR2 IN DEFAULT DESCRIPTION VARCHAR2 IN DEFAULT CATEGORY VARCHAR2 IN DEFAULT VALIDATE BOOLEAN IN DEFAULT
- The function names change from i_create_patch to create_sql_patch when exposed in dbms_sqldiag.
- There are two versions of the function – one that requires you to supply the exact SQL text, and a new version that allows you to supply an SQL ID.
- The internal function also adds a creator to the existing parameter list – and it doesn’t have a default so if you’ve already got some code to use the internal version it’s not going to work on an upgrade to 12.2 until you change it.
I was prompted to write this note by a tweet asking me if there’s any SQL available to see the contents of an SQL Profile in 11g and 12c. (I published some simple code several years ago for 10g, (both before accepting and after accepting) but Oracle changed the base tables in 11g). The answer is yes, probably on the Internet somewhere, but here’s some code I wrote a couple of years ago to report SQL Profiles in more recent versions of Oracle:
rem rem sql_profile_baseline_11g.sql rem J.P.Lewis rem July 2010 rem set pagesize 60 set linesize 132 set trimspool on column hint format a70 wrap word column signature format 999,999,999,999,999,999,999 break on signature skip 1 on opt_type skip 1 on plan_id skip 1 spool sql_profile_baseline_11g select prf.signature, decode( obj_type, 1,'Profile', 2,'Baseline', 3,'Patch', 4,'Quarantine', -- appeared in 19c 'Other' ) opt_type, prf.plan_id, extractvalue(value(tab),'.') hint from ( select /*+ no_eliminate_oby */ * from sqlobj$data where comp_data is not null order by signature, obj_type, plan_id ) prf, table( xmlsequence( extract(xmltype(prf.comp_data),'/outline_data/hint') ) ) tab ;
This will report the hints associated with SQL Baselines, SQL Profiles, and SQL Patches – all three store the data in the same base table. As a minor variation I also have a query (possibly used as the basis for MOS Doc ID 2543795.1) that will report a named profile/baseline/patch, but this requires a join to the sqlobj$ table. As you can see from the substitution variable near the end of the text, the script will prompt you for an object name.
rem rem Script: sql_profile_baseline_11g_1.sql rem Dated: July 2010 rem Author: Jonathan Lewis rem set pagesize 60 set linesize 180 set trimspool on column plan_name format a32 column signature format 999,999,999,999,999,999,999 column category format a10 column hint format a70 wrap word break on plan_name skip 1 on signature skip 1 on opt_type skip 1 on category skip 1 on plan_id skip 1 spool sql_profile_baseline_11g select prf.plan_name, prf.signature, decode( obj_type, 1,'Profile', 2,'Baseline', 3,'Patch', 'Other' ) opt_type, prf.category, prf.plan_id, extractvalue(value(hnt),'.') hint from ( select /*+ no_eliminate_oby */ so.name plan_name, so.signature, so.category, so.obj_type, so.plan_id, sod.comp_data from sqlobj$ so, sqlobj$data sod where so.name = '&m_plan_name' -- this will prompt for a plan name and sod.signature = so.signature and sod.category = so.category and sod.obj_type = so.obj_type and sod.plan_id = so.plan_id order by signature, obj_type, plan_id ) prf, table ( select xmlsequence( extract(xmltype(prf.comp_data),'/outline_data/hint') ) from dual ) hnt ;
Lagniappe:
One of the enhancements that appeared in 12c for SQL Baselines was that the plan the baseline was supposed to produce was stored in the database so that Oracle could check that the baseline would still reproduce the expected plan before applying it the DBA could see the plan the baseline had been producing before Oracle stopped using it. (Currently Oracle stores the plan’s hash value, and stops using the baseline if it starts to produce a different hash value. Storing the plan as well gives the DBA a chance of working out how to reproduce the desired plan and create a new baseline to get to it.)
These plans (also generated for Profiles but not necessarily Patches) are stored in the table sqlobj$plan, and the dbms_xplan package has been enhanced with three new functions to report them:
FUNCTION DISPLAY_SQL_PATCH_PLAN RETURNS DBMS_XPLAN_TYPE_TABLE Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- NAME VARCHAR2 IN FORMAT VARCHAR2 IN DEFAULT FUNCTION DISPLAY_SQL_PLAN_BASELINE RETURNS DBMS_XPLAN_TYPE_TABLE Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- SQL_HANDLE VARCHAR2 IN DEFAULT PLAN_NAME VARCHAR2 IN DEFAULT FORMAT VARCHAR2 IN DEFAULT FUNCTION DISPLAY_SQL_PROFILE_PLAN RETURNS DBMS_XPLAN_TYPE_TABLE Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- NAME VARCHAR2 IN FORMAT VARCHAR2 IN DEFAULT e.g. SQL> select * from table(dbms_xplan.display_sql_profile_plan('SYS_SQLPROF_015c9bd3bceb0000')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------- SQL text: select t1.id, t2.id from t1, t2 where t1.id between 10000 and 20000 and t2.n1 = t1.n1 and t2.n1 = t2.v2 -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- SQL Profile Name: SYS_SQLPROF_015c9bd3bceb0000 Status: ENABLED Plan rows: From dictionary -------------------------------------------------------------------------------- Plan hash value: 3683239666 ----------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib | ----------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 10501 | 287K| 248 (4)| 00:00:01 | | | | | 1 | PX COORDINATOR | | | | 0 (0)| | | | | | 2 | PX SEND QC (RANDOM) | :TQ10002 | 10501 | 287K| 248 (4)| 00:00:01 | Q1,02 | P->S | QC (RAND) | |* 3 | HASH JOIN BUFFERED | | 10501 | 287K| 248 (4)| 00:00:01 | Q1,02 | PCWP | | | 4 | PX RECEIVE | | 10002 | 97K| 123 (3)| 00:00:01 | Q1,02 | PCWP | | | 5 | PX SEND HASH | :TQ10000 | 10002 | 97K| 123 (3)| 00:00:01 | Q1,00 | P->P | HASH | | 6 | PX BLOCK ITERATOR | | 10002 | 97K| 123 (3)| 00:00:01 | Q1,00 | PCWC | | |* 7 | TABLE ACCESS FULL| T1 | 10002 | 97K| 123 (3)| 00:00:01 | Q1,00 | PCWP | | | 8 | PX RECEIVE | | 104K| 1845K| 124 (4)| 00:00:01 | Q1,02 | PCWP | | | 9 | PX SEND HASH | :TQ10001 | 104K| 1845K| 124 (4)| 00:00:01 | Q1,01 | P->P | HASH | | 10 | PX BLOCK ITERATOR | | 104K| 1845K| 124 (4)| 00:00:01 | Q1,01 | PCWC | | |* 11 | TABLE ACCESS FULL| T2 | 104K| 1845K| 124 (4)| 00:00:01 | Q1,01 | PCWP | | ----------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("T2"."N1"="T1"."N1") 7 - filter("T1"."ID"=10000) 11 - filter("T2"."N1"=TO_NUMBER("T2"."V2")) Note ----- - automatic DOP: Computed Degree of Parallelism is 2
Disclaimer – I’ve checked only the SQL_PROFILE function call on 12.2, after creating a profile to check that my old 11g report still worked in 12c.
Update Aug 2017
I have just rediscovered a note I made (though I don’t have a reference to the source) that Patch 17203284 backports the visibility of create_sql_patch to dbms_sqldiag in 12.1.0.2. The description for the patch is: Enhancements for dbms_sqldiag_internal.i_create_patch but the “Bugs resolved by this patch” link on the patch details screen leads to the “Requested bug could not be displayed” page.
[Update: Oct 2017,(and see comment below) – this patch doesn’t make public a procedure create_sql_patch, it simply adds a version of i_create_patch that takes a CLOB hint text to dbms_sqldiag_internal.]
Threat Warning (Feb 2022)
Although you can pass a CLOB to the call to create patch, the code to store the patch converts it into a variable of type sqlprof_attr, which defined as an “array(2000) of varchar2(500)” – and the conversion simply cuts the clob into pieces every 500 bytes, breaking some of the hints! See this comment below from Vladimir Andreev – which I should have highlighted at the time.
As with so many little details, I had forgotten that I had been warned about this until Christian Antognini published a blog note about it, including some sample code to work around the problem.
Chris Antognini has posted a tweet [Feb 2023] to let us know the bug has been fixed and that patch 34940767 is now available, and he’s tested the 19.18 patch.
Jonathan, hi
Thanks for this note.
Unfortunately, Oracle still didn`t update Docs. No DISPLAY_SQL_PATCH_PLAN or DISPLAY_SQL_PROFILE_PLAN there.
https://docs.oracle.com/database/122/ARPLS/DBMS_XPLAN.htm#ARPLS74741
Comment by Pavel Trishin — June 24, 2017 @ 10:42 am BST Jun 24,2017 |
Pavel,
Thanks for the comment.
In fact the two new functions are barely mentioned in $ORACLE_HOME/rdbms/admin/dbmsxpln.sql where the package is created – and I find fairly frequently that the creation scripts report some documentation long before the manuals for a package are updated.
Comment by Jonathan Lewis — June 30, 2017 @ 8:26 am BST Jun 30,2017 |
Hi Jonathan,
This statement is not correct: “One of the enhancements that appeared in 12c for SQL Baselines was that the plan the baseline was supposed to produce was stored in the database so that Oracle could check that the baseline would still reproduce the expected plan before applying it.”
This new stored plan is not used to check that the reproduced plan matches the expected plan before applying it; the enhancement (in Bug 12588179) was added only to improve diagnostics by having DBMS_XPLAN report the original plan rather than the reproduced plan. Note that the reproduced plan has always been checked against the expected plan since 11.1 via the plan_id. 12c does the same.
Mark Jefferys
Oracle Support
Comment by Mark Jefferys — June 28, 2017 @ 9:10 pm BST Jun 28,2017 |
Mark,
Thanks for the correction – which I’ve now put into the article.
It was something I knew, but knowing something doesn’t always stop me from making silly mistakes when I write things down :(
Comment by Jonathan Lewis — June 30, 2017 @ 8:29 am BST Jun 30,2017 |
Hi Jonathan,
Case in point: In your correction, “the plan’s hash value” strongly implies that SPM uses the plan_hash_value for verification, when it doesn’t. The plan_hash_value is not always stable if you move to a different database (such as test to production) or another platform.
Mark Jefferys
Oracle Support
Comment by Mark Jefferys — June 30, 2017 @ 7:42 pm BST Jun 30,2017 |
Mark,
That’s a fair cop.
I couldn’t remember which hash value the optimizer used and what it compared it with so I was deliberately fudging the issue. Looking for something completely difference I just happened to turn up this comment on another posting of mine that supplies the answer from Mohamed Houri:
Bear in mind as well that when it comes to SPM plan reproducibility it is PHV2 of the CBO plan that should be compared to the PlanId of the SPM plan and not the plan_hash_value because this one does not include the predicate part. For the sake of completness here’s a CBO-SPM plan interaction: https://hourim.wordpress.com/2015/10/01/oracle-optimizer-and-spm-plan-interaction/
Comment by Jonathan Lewis — July 1, 2017 @ 11:46 am BST Jul 1,2017
Hi Jonathan,
Mohamed is right, except that neither the plan_hash_value nor the plan_id/PHV2 include predicate information. This means that you can have cases like an INDEX RANGE SCAN slowing down because one of the endpoints changed from an access to a filter predicate even though the plans are the same as measured by the PHV or PHV2. As a result, SPM is also incapable of distinguishing between them.
Mark Jefferys
Oracle Support
Comment by Mark Jefferys — July 3, 2017 @ 6:00 pm BST Jul 3,2017
Regarding Patch 17203284: I just installed it in a 12.1.0.2 database, and it turns out it doesn’t backport the visibility of dbms_sqldiag.create_sql_patch as documented in the 12.2 documentation. It only redefines dbms_sqldiag_internal.i_create_patch to accept CLOB for the hint_text parameter and provides an overload taking sql_id instead of sql_text. Trying to use the variant that takes sql_id results in the mysterious error ORA-56975 (even with SQL*Plus running from the server ORACLE_HOME). So – in 12.1.0.2 – the i_create_patch procedures remain very much invisible and only half-documented even with the backport patch installed.
Unpatched 12.1.0.2:
12.1.0.2 with Patch 17203284:
And just a nitpick:
“The function names change from i_create_patch to create_sql_patch when exposed in dbms_sqldiag.”
Cheers!
Flado
Comment by Vladimir Andreev — October 4, 2017 @ 4:29 pm BST Oct 4,2017 |
Vladimir,
Thanks for the correction (and the nitpick correction, which I’ve now fixed).
Comment by Jonathan Lewis — October 4, 2017 @ 9:20 pm BST Oct 4,2017 |
Patch 17203284 looks a lot like a half-hearted effort. In addition to the ORA-56975 you get when trying to use the SQL_ID parameter and the SQL is currently not present in the library cache, the patch splits long hint strings at character position 500 – hard, as in, in the middle of a hint or parameter. If you follow Maria Colgan’s advice and put the entire outline section produced by dbms_xplan.display*() there, you’ll have to be very lucky for the split to fall in such a way as to not destroy a hint. It almost drove me crazy until I figured it out. The 10053 trace is of limited help here since the hints supplied via SQL Patches do not get dumped at the end (or anywhere else – with the possible exception of transformation hints – at least in my case USE_CONCAT appeared verbatim in the section for OR-Expansion).
What did help was adapting the query given here: https://dbamarco.wordpress.com/2015/11/09/sql-patches-what-do-they-do/ to see where hint string got split and adjust the whitespace before the split in such a way that character 500 is eiter a space or a delimiter.
I haven’t investigated (yet) whether only the destroyed hint gets ignored, or whether all the following hints get the axe as well. My suspicion is that a delimiter – maybe a space or a newline – gets inserted between the two 500-character parts when reassembling the hint string for parsing, in which case the subsequent hints should work, but my experiments so far only confirm that when there are no destroyed hints, everything works as expected.
I hope this helps someone avoid the frustrating search for the reason why only some part of an SQL patch seems to work.
Comment by Vladimir Andreev — November 29, 2017 @ 5:05 pm GMT Nov 29,2017 |
Oops, I note now that I could have used your query above to see the hints for the SQL patch – but I guess in my frantic search for such a query I found the site linked above first.
Comment by Vladimir Andreev — November 29, 2017 @ 5:20 pm GMT Nov 29,2017 |
Vladimir,
Thanks for the update and warning.
Comment by Jonathan Lewis — November 29, 2017 @ 5:27 pm GMT Nov 29,2017 |
[…] it’s not trivial to get it right so if you do need to block the feature the smart hint (or SQL Patch) would be “opt_param(‘_optimizer_join_factorization’ […]
Pingback by Join Factorization | Oracle Scratchpad — February 14, 2018 @ 3:39 pm GMT Feb 14,2018 |
[…] The mantra “if you can hint it, baseline it” is now out of date. There is a downside to baselines – once a query has a baseline the optimizer will be able to create new baselines for it (even if the parameter optimizer_capture_sql_baselines is set to false), and in 12.2 there’s an auto-evolve task that runs overnight. So creating SQL Plan baselines is an activity to avoid. Instead the mantra is “if you can hint it, Patch it” – using a technology that was publicised by the Oracle Optimiser blog even though it was (at the time) in an undocumented pacakge: the package is now official. […]
Pingback by How to hint – 2 | Oracle Scratchpad — June 24, 2018 @ 9:37 am BST Jun 24,2018 |
[…] you want to test on a clone (or accurate model) of the tables you may find that you can create an sql_patch that works (even though the necessary SQL will be optimised as SYS – though so far I’ve […]
Pingback by Validate FK | Oracle Scratchpad — July 10, 2018 @ 11:23 am BST Jul 10,2018 |
[…] a newer version of Oracle you may find that create_sql_patch is now a function of dbms_sqldiag. See this note for more […]
Pingback by Dynamic Sampling – 2 | Oracle Scratchpad — April 12, 2019 @ 11:52 am BST Apr 12,2019 |
[…] tested this on 12.2.0.1 and 19.3.0.0, but for earlier versions of Oracle, and depending what patches you’ve applied, you will need to modify the […]
Pingback by sql_patch | Oracle Scratchpad — August 21, 2019 @ 4:49 pm BST Aug 21,2019 |
[…] session level, or through an opt_param() hint at the statement level (possibly injected through an SQL_Patch. The other option is to set a fix_control, again at the system, session, or statement level – […]
Pingback by Group by Elimination | Oracle Scratchpad — January 17, 2020 @ 12:57 pm GMT Jan 17,2020 |
is it possible to create sql patch using wild card as sql text so it can be used for all alike sqls?
like ‘select emp from %’ as sql_text
Comment by Ash — February 14, 2020 @ 4:39 pm GMT Feb 14,2020 |
Ash,
I don’t think I’ve seen anything that says explicitly that this is not possible, but I don’t think it will be.
SQL*Patches (et al.) depend on (a version of) the SQL_ID and that depends on the actual text of the statement, and I haven’t seen any suggestion that Oracle can “factor out” the name of an normal to generate an SQL_ID.
Your question is related to a much older question that used to be asked quite frequently – “can you use a bind variable as the name for a table?” – to which the answer was “no”.
Regards
Jonathan Lewis
Comment by Jonathan Lewis — February 17, 2020 @ 5:25 pm GMT Feb 17,2020 |
Thanks Jonathan. I tried using wild charactor with sql patch, but didn’t work. as you said it’s only for sql id.
we have a situation where this app has 15 different where conditions and users can use any combination and it creates different sql id for each so not able to freeze any good plan. we upgrading to 12.2 from 11g so when adding hint optimizer_features_enable=11.2.0.4, things running faster. but I didn’t want to enable at instance level so hoping to add hint to these queries. This third party app doesn’t allow any hint in the code so was hopping to do some magic with sql patch.
Comment by Anonymous — February 18, 2020 @ 2:29 pm GMT Feb 18,2020 |
This is a relatively common problem on upgrades – some key set of SQL statement performs badly until you set the optimizer features backwards, and there’s no way to modify the code. There are two strategies that might help you to avoid taking the whole system back to an earlier OFE
a) Is there one specific user that suffers from the problem code. If so you might be able to set up a logon trigger that adjusts the OFE only for that user
b) if you can identify the basci change in execution plan between features you may be able to identify what it is that allows the optimizer to pick a different plan in 12.2.
b i) It may be something about the statistics – in which case there may be a way of tweaking the statistics that will get you the plans you want.
b ii) It may be that there’s a specific optimizer feature that has been enabled by parameter that makes the change happen – in which case you could set that specifici parameter back to its 11g value (or to an “off” value)
b iii) It’s possible that a bug fix has introduced the change and there’s a “fix control” that will give you the original plan, in which case – like the parameter option – you could include a setting for the fix control in your startup file.
Comment by Jonathan Lewis — February 18, 2020 @ 2:44 pm GMT Feb 18,2020 |
[…] be investigated(2) – if (1) works, could we achieve the same result by using an SQL Patch to attach the hint to the main query text without editing the main […]
Pingback by Inline Hint | Oracle Scratchpad — October 9, 2020 @ 12:46 pm BST Oct 9,2020 |
[…] notice I’ve issued a call to the dbms_sqldiag package to drop an SQL patch called “driving_site” just to make sure I don’t […]
Pingback by Driving site patch | Oracle Scratchpad — May 9, 2021 @ 12:23 pm BST May 9,2021 |
Hi, I know this is old thread. I beg you pardon. and one of the best outhere.
My question is to you Guys. Can I use sqlpatch to hinted parallel and enable parallel dml?. I’m using 12.1.0.2 on linux.
Best Regards.
Comment by William Laverde — July 18, 2021 @ 1:05 am BST Jul 18,2021 |
William,
Apologies, I missed this when it first appeared.
I’ve never managed to get a SQL_patch to enable parallel DML (even though there is a hint to enable parallel dml available), and I think that when I’ve tried to do it there have been indications that the releveant part of the patch has been deliberately ignored.
.
You can add parallel() hints – though it might have to be shared() depending on circumstances. But that can only affect the SELECT parts of DML.
Regards
Jonathan Lewis
Comment by Jonathan Lewis — August 3, 2021 @ 5:34 pm BST Aug 3,2021 |
[…] you don’t actually have to edit the text; the hint could be attached to the query through an SQL Patch (or by abusing the SQL Profile or SQL Plan Baseline […]
Pingback by Hash Aggregation – 1 | Oracle Scratchpad — January 17, 2022 @ 1:15 pm GMT Jan 17,2022 |
[…] showing that the fix control works by hinting the next step should be to create an SQL Patch (using dbms_sqldiag.create_sql_patch) attached to the (forced) bind-variable version of the […]
Pingback by Index Wildcard | Oracle Scratchpad — July 15, 2022 @ 11:51 am BST Jul 15,2022 |
[…] This took me back a couple of years to an occasion when I was asked if it was possible to make an SQL Patch “force match” – the answer was “yes” and the method is pretty simple […]
Pingback by force_match patch | Oracle Scratchpad — August 19, 2023 @ 2:56 pm BST Aug 19,2023 |
[…] the only problem is the choice of join mechanism we can patch the code (by hand or with an sql_patch) to bypass the problem. Given the simple pattern that produces the problem I should be able to find […]
Pingback by Case Study | Oracle Scratchpad — September 26, 2023 @ 1:09 pm BST Sep 26,2023 |
[…] use_nl(), index()), and write the entire edited outline data into an SQL Patch watching out for a recently fixed defect in the SQL patch […]
Pingback by Swap_Join_Inputs | Oracle Scratchpad — November 6, 2023 @ 2:47 pm GMT Nov 6,2023 |