Re: [External] Slow query accessing remote database tables

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_at_rsiz.com> 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_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *Laurentiu Oprea
> *Sent:* Tuesday, December 22, 2020 3:38 PM
> *To:* Beckstrom, Jeffrey
> *Cc:* eramitsaroha_at_gmail.com; ORACLE-L (oracle-l_at_freelists.org)
> *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_at_gcrta.org>
> 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_at_freelists.org <oracle-l-bounce_at_freelists.org> *On
> Behalf Of *Amit Saroha
> *Sent:* Tuesday, December 22, 2020 3:02 PM
> *To:* Laurentiu Oprea <laurentiu.oprea06_at_gmail.com>
> *Cc:* ORACLE-L (oracle-l_at_freelists.org) <oracle-l_at_freelists.org>
> *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_at_gmail.com> 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_at_gmail.com> 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
>
>

--
http://www.freelists.org/webpage/oracle-l