Slow query accessing remote database tables
Date: Tue, 22 Dec 2020 13:28:20 -0500
Message-ID: <CAG67e6Sn3RBUfvDJpU3qGm9Xu=FMxuu-DD=G-S9BhZ-PgY=L=A_at_mail.gmail.com>
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.
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
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Dec 22 2020 - 19:28:20 CET