SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

How to optimize this SQL using DBLINK and execute long time and not complete now ?

Quanwen ZhaoSep 22 2017 — edited Oct 22 2017

Hello,experts

There has a SQL using DBLINK and its SQL_ID is '83gn36c1fu9dw' on my Oracle Data Guard 11.2.0.4.0 primary DB,

sql_fulltext is as follows,

SELECT *

FROM

    (

            SELECT  \*

            FROM

                    (

                            SELECT  t.school\_id  ,

                                    t.school\_name,

                                    t.photo\_path ,

                                    r.subject\_id ,

                                    r.version\_id ,

                                    r.year\_mark  ,

                                    r.textbook\_code

                            FROM

                                    (

                                            SELECT  \*

                                            FROM    sd\_res\_id\_case@res r2

                                            WHERE   :"SYS\_B\_0" =:"SYS\_B\_1"

                                                    **AND** rowid IN

                                                    (

                                                            SELECT  **MIN**(rowid)

                                                            FROM    sd\_res\_id\_case@res r1

                                                            WHERE   r1.delete\_flag        =:"SYS\_B\_2"

                                                                    **AND** r1.is\_school\_check=:"SYS\_B\_3"

                                                                    **AND** r1.subject\_id    **IS NOT NULL**

                                                                    **AND** r1.version\_id    **IS NOT NULL**

                                                                    **AND** r1.textbook\_code **IS NOT NULL**

                                                            GROUP BY r1.school\_id

                                                            HAVING **COUNT**(\*)\>=:"SYS\_B\_4"

                                                    )

                                    )

                                    r

                                    LEFT JOIN base\_school t

                                    ON      r.school\_id = t.school\_id

                            WHERE   :"SYS\_B\_5"          =:"SYS\_B\_6"

                                    **AND** t.county\_id     =:1

                    )

                    a

            WHERE   rownum\<:"SYS\_B\_7"

    )

WHERE rownum <= :2

and SQL code above using DBLINK part, it's another Oracle DB,i found it's LOAD has some high,please see my snapshot below,