Re: Slow query accessing remote database tables

You can try using DRIVING_SITE hint:

................

        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