Re: 2 Interesting Questions regarding 12.1 dbms_sqldiag_internal.i_create_patch (not 12.2)
Date: Tue, 14 Sep 2021 08:28:48 +0000
Message-ID: <AM8P194MB1628D63243A909E91BCF80D985DA9_at_AM8P194MB1628.EURP194.PROD.OUTLOOK.COM>
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_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Mladen Gogala <gogala.mladen_at_gmail.com> Sent: 14 September 2021 03:49
To: oracle-l_at_freelists.org <oracle-l_at_freelists.org> 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_at_SEL$10
44 - SEL$63682743 / SRCL_at_SEL$10
So how I would hint this sql using the SQL Patch?
Would it be something like:
HINT_TEXT=>'FULL(_at_SEL$63682743 SRCL)'
Chris
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Sep 14 2021 - 10:28:48 CEST