Skip to Main Content

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,

2.png

At the same time,i use EMCC 12c(12.1.0.5.0) monitor platform to trace that FULL SQL execute process,

  1. Active Sessions,

6.png

  1. Top Activity

5.png

  1. SQL Monitor - Last Hour

7.png

  1. Monitored SQL Executions

8.png

  1. Execution Statistics

9.png

  1. Execution Plan

10.png

  1. another SQL execute completed,SQL Monitor some information

11.png

12.png

Now,how to optimize this complex SQL,please give me some guiding suggestions,Thanks!

Best Regards,

Quanwen Zhao

This post has been answered by Jonathan Lewis on Sep 29 2017
Jump to Answer

Comments

Processing
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Nov 19 2017
Added on Sep 22 2017
42 comments
5,688 views