Dear All,
The issue is fixed by constructing a view in the remote database and now
the query run time is decreased from 3.30 hours to 3 seconds.
Thank you all for your amazing inputs.
Best Regards,
Amit
On Wed, Dec 23, 2020 at 10:32 AM Mark W. Farnham <mwf@xxxxxxxx> wrote:
Since your query involves a possibly remote UNION, the first thing I would
try is to deliver the pieces of the UNION into two temporary tables with
the appropriate column definitions and then do the UNION locally between
the two temporary tables.
After that, possibly you will need driving site hints, but on the basis of
least human work to get a tolerable result, I would try the local pieces of
the UNION first in the hope that the CBO does the correct thing with the
separate pieces of the execution plan all by itself. Other than first
executing the query both ways and then comparing the results, I’m not sure
how Oracle would be able to develop statistics about where to do the mutual
sorting of the query pieces to minimize total network transmission of
resolving the UNION.
Now, if you know LOGICALLY that your data is such that duplicated rows
cannot be produced by the separate pieces of the UNION, you might also
substitute a UNION ALL for the UNION. But that verification of
non-duplication is then on you, not the database engine. Quite often UNIONs
can be converted to UNION ALL via knowledge of logical constraints that are
not actually present in the database.
Good luck. It is entirely possible this will not help at all, but the
effort required to test it is tiny compared to possible improvements and
even compared to reading your plan. Without looking at the plan or even the
query except that it contains a UNION, I wild ass guess your odds at better
than 50-50.
It is possible you will need to evaluate the types produced by the query
halves to instantiate the temp table. An easy way to do that is CTAS of the
query piece with rownum < 1.
Good luck,
mwf
*From:* oracle-l-bounce@xxxxxxxxxxxxx [mailto:
oracle-l-bounce@xxxxxxxxxxxxx] *On Behalf Of *Laurentiu Oprea
*Sent:* Tuesday, December 22, 2020 3:38 PM
*To:* Beckstrom, Jeffrey
*Cc:* eramitsaroha@xxxxxxxxx; ORACLE-L (oracle-l@xxxxxxxxxxxxx)
*Subject:* Re: [External] Slow query accessing remote database tables
In theory the hint should be enough to instruct oracle to use spp_to_ebs
database to process all the joins localy and call remotely for
XXSPP01W_SPFST_STG.It might be the case that we still need to see how
table XXSPP01W_SPFST_STG is integrated in the execution plan. Based on that
fact that all tables except one are located in the remote DB it might be
possible to run faster but in reality I have no idea is a matter of trying
and let us know
În mar., 22 dec. 2020 la 22:08, Beckstrom, Jeffrey <jbeckstrom@xxxxxxxxx>
a scris:
You could try taking your select and converting it into a view on the
remote database. Then select from that view. That has worked for us in the
past when doing joins on remote tables.
*From:* oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> *On
Behalf Of *Amit Saroha
*Sent:* Tuesday, December 22, 2020 3:02 PM
*To:* Laurentiu Oprea <laurentiu.oprea06@xxxxxxxxx>
*Cc:* ORACLE-L (oracle-l@xxxxxxxxxxxxx) <oracle-l@xxxxxxxxxxxxx>
*Subject:* Re: [External] Slow query accessing remote database tables
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
