Thanks for that.
The set of hints you showed in the latest patch do look suitable - though
some of the outline, outline_leaf and merge hints MIGHT be redundant.
The key details that give the "big picture" of the plan were the unnest(),
the leading() and use_nl() - but maybe some of the merge() and outline_leaf
hints were also necessary to make sure that Oracle reached the right query
block names that could make the hints (as specified) become relevant.
Regards
Jonathan Lewis
On Wed, 22 Nov 2023 at 21:22, Amit Saroha <eramitsaroha@xxxxxxxxx> wrote:
Hi Jonathan,
I have enclosed the patch and the new plan for your review. Please let me
know if you need any other info from my side.
Best Regards,
AMIT
On Wed, Nov 22, 2023 at 2:59 PM Jonathan Lewis <jlewisoracle@xxxxxxxxx>
wrote:
If you've managed to sort out hints that get the path you want it would
be rather nice if you showed us the final solution, viz: section of SQL
with the hints in place, execution plan including Outline Data (and notes
so that we can see that there aren't further hints in an SQL Patch etc.)
Regards
Jonathan Lewis
On Thu, 16 Nov 2023 at 17:49, Amit Saroha <eramitsaroha@xxxxxxxxx> wrote:
Hi Timur and Jonathan,
After adding HINTs one at a time, I was able to create the proper plan
as Timur showed with a faster execution time. Thank you for all of your
assistance and advice in addressing my problem.
Could you help me understand why my system has a bad plan and whether I
need to reorganize my tables and rebuild my indexes?
Best Regards,
AMIT
On Thu, Nov 16, 2023 at 11:41 AM Amit Saroha <eramitsaroha@xxxxxxxxx>
wrote:
Hi Timur,
I tried all of the hints, but nothing changed in the plan. According to
the included report, it appears that some HINTS fail and I couldn't figure
out why they failed even after several attempts.
*Hint Report (identified by operation id / Query Block Name / Object
Alias):Total hints for statement: 16 (E - Syntax error
(3))---------------------------------------------------------------------------
0 - SEL$11 - MERGE(@SEL$11 >SEL$10) 0 - SEL$14 -
MERGE(@SEL$14 >SEL$13) 0 - SEL$17 - MERGE(@SEL$17
SEL$16) 0 - SEL$1CF66C63 - MERGE(@SEL$1CF66C63 >SEL$12)0 - SEL$2 - MERGE(@SEL$2 >SEL$1) 0 - SEL$285A8194
- MERGE(@SEL$285A8194 >SEL$9) 0 - SEL$5 - MERGE(@SEL$5
SEL$4) 0 - SEL$7286615E - MERGE(@SEL$7286615E >SEL$3) 0- SEL$8 - MERGE(@SEL$8 >SEL$7) 0 - SEL$8A3193DA -
MERGE(@SEL$8A3193DA >SEL$6) 0 - SEL$97CCBC9E -
MERGE(@SEL$97CCBC9E >SEL$15) 0 - SEL$F5BB74E1 -
MERGE(@SEL$F5BB74E1 >UPD$1) 0 - UPD$1 E - OU E -
TLINE E - X_RS_ASC 5 - SET$FCA7A018 -
UNNEST(@SET$1 UNNEST_INNERJ_DISTINCT_VIEW) Note----- - SQL patch
"7wh1j104vstdg" used for this statement*
Best Regards,
AMIT
On Thu, Nov 16, 2023 at 4:18 AM Timur Akhmadeev <
timur.akhmadeev@xxxxxxxxx> wrote:
Hi,
I've the following plan in my ebs db:
https://pastebin.com/raw/2z3Kh7sj
Can you try using all the hints from the outline and see how the plan
in your db looks like?
On Wed, Nov 15, 2023 at 1:52 AM Amit Saroha <eramitsaroha@xxxxxxxxx>
wrote:
Hi All,
I have included a tiny update statement and plan in which I am
attempting to include two hints; unfortunately, no matter what I try, the
hints are never used.
Please have a look and let me know what I'm missing and why hints
aren't being used.
Thank you in advance for all of your feedback.
Best Regards,
AMIT
--
Regards
Timur Akhmadeev
