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
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]
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)
I would use baselines instead of SQL*Patch. With the baseline, you can
completely rewrite the SQL and associate the new plan.
On 9/13/21 13:17, Chris Taylor wrote:
Background: I'm looking over Maria Colgan's posts here:
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: