sql patches, sql profiles, outlines and baselines have different use cases and
each has its own validity.
outlines are deprecated. you should mostly upgrade to baselines
sql profiles are a cost option (tuninig pack) and generally rely on many hints
combined with cardinality adjustments (using opt_estimate, which can go stale
as they are adjusting statistics - which is why you can see them fail after a
while)
sql baselines are trying to recreate a specific plan (or set of plans). If they
cannot, the baseline fails. They are the most precise of the 3 options.
sql patches are just injecting a limited amount of your hints. They could
recreate what is happening with a sql profile if they use the same hints, but
not what happens with a baseline (re: plan validity).
You can use them all on the same SQL if you want - the use case for that is if
the baseline fails then you can fall be to using a sql profile or patch [note:
I've never tested doing that with a sql patch]
Neil Chandler
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on behalf
of Mladen Gogala <gogala.mladen@xxxxxxxxx>
Sent: 14 September 2021 03:49
To: oracle-l@xxxxxxxxxxxxx <oracle-l@xxxxxxxxxxxxx>
Subject: Re: 2 Interesting Questions regarding 12.1
dbms_sqldiag_internal.i_create_patch (not 12.2)
Hi Chris,
I would use baselines instead of SQL*Patch. With the baseline, you can
completely rewrite the SQL and associate the new plan.
Regards
On 9/13/21 13:17, Chris Taylor wrote:
Background: I'm looking over Maria Colgan's posts here:
https://blogs.oracle.com/optimizer/post/using-sql-patch-to-add-hints-to-a-packaged-application<https://emea01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fblogs.oracle.com%2Foptimizer%2Fpost%2Fusing-sql-patch-to-add-hints-to-a-packaged-application&data=04%7C01%7C%7C544288afd6964a9677fb08d9772a3d28%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637671845657346169%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=A3KCnwIBjL3M5XSHU%2BrTv5iZPEDlcnd6gD1pLN6tYp0%3D&reserved=0>
https://blogs.oracle.com/optimizer/post/additional-information-on-sql-patches<https://emea01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fblogs.oracle.com%2Foptimizer%2Fpost%2Fadditional-information-on-sql-patches&data=04%7C01%7C%7C544288afd6964a9677fb08d9772a3d28%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637671845657356124%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=xcRtlx5JpflFi20k0zYeqsj0kB6gv1wCQuC1JPaAxb8%3D&reserved=0>
I have 2 questions:
1.) Can I assign "sql_text=>" to v_sql where I load v_sql from dba_hist_sqltext
for a sql_id?
I assume the answer should be yes?
2.) The patch I need to apply is in a terrible SUBSELECT thats part of a CTE.
In the execution plan it looks like this:
|* 43 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED |
SPECIAL_REVERSED_CLAIM_LOG | 1 | 29 | 3 (0)| 00:00:01 | ROWID
| ROWID |
|* 44 | INDEX RANGE SCAN |
SPECIAL_REVERSED_CLAIM_LOG_IDX | 1 | | 2 (0)| 00:00:01 | |
Which is this in the outline information:
43 - SEL$63682743 / SRCL@SEL$10
44 - SEL$63682743 / SRCL@SEL$10
So how I would hint this sql using the SQL Patch?
Would it be something like:
HINT_TEXT=>'FULL(@SEL$63682743 SRCL)'
Chris
