Thank you, Laurentiu. I'll try with the hint you have suggested. However,
because there are multiple remote tables used in the query, will it take
care of the whole join itself with other remote tables by adding a hint in
only one table in this case PO_HEADERS_ALL?
Best Regards,
AMIT SAROHA
On Tue, Dec 22, 2020 at 2:43 PM Laurentiu Oprea <laurentiu.oprea06@xxxxxxxxx>
wrote:
You can try using DRIVING_SITE hint:
................
new_order_placement_date
FROM
(
SELECT /*+ DRIVING_SITE(poh)*/
trunc(to_date(sysdate))
issue_date,
mp.organization_code
ship_to_location,
......................
) > xss.max_update_date )
UNION
SELECT /*+ DRIVING_SITE(poh)*/
trunc(to_date(sysdate))
issue_date,
mp.organization_code
ship_to_location,
...........................................
În mar., 22 dec. 2020 la 20:29, Amit Saroha <eramitsaroha@xxxxxxxxx> a
scris:
Hi All,
I apologize but I couldn't find a way to send the execution plan
details in an email because every time I send my post didn't show up in the
forum. I have posted the detail at PASTEBIN at the below location. It seems
there is some character limit for posting in the forum.
https://pastebin.com/QsiDZFXC
I am looking for your expert advice to improve the performance of the
below query which is running nearly 3 hours every day. This query is
accessing data from multiple tables from the remote database over the DB
link and with my minimum understanding of the execution plan, I surmise
PO_HEADERS_ALL table data is coming into the local database and is the main
issue. I have also studied that the REMOTE hint can be used in such
scenarios but I couldn't figure out how and where to place the hint in the
below query.
Do let me know if it can be re-written any other way to run time and the
below information is enough for you to comment on the issue?
Database details -
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit
Production
PL/SQL Release 12.1.0.2.0 - Production
"CORE 12.1.0.2.0 Production"
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production
Thank you in advance for all your inputs in this regard.
Regards,
Amit
