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
