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