1 2 3 Previous Next 42 Replies Latest reply on Oct 23, 2017 3:17 AM by Quanwen Zhao

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

    Quanwen Zhao

      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

      2) Top Activity

      5.png

      3) SQL Monitor - Last Hour

      7.png

      4) Monitored SQL Executions

      8.png

      5) Execution Statistics

      9.png

      6) Execution Plan

      10.png

      7) 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

        • 1. Re: How to optimize this SQL using DBLINK and execute long time and not complete now ?
          Paulzip

          Can you paste the execution plan for this...

           

          select /*+ driving_site(sd_res_id_case) */ *

          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

          1 person found this helpful
          • 2. Re: How to optimize this SQL using DBLINK and execute long time and not complete now ?
            Quanwen Zhao

            Hello,Paulzip

            I've no idea about the value of colon behind,such as ':"SYS_B_6"' or ':2' and etc.

             

            Best Regards,

            Quanwen Zhao

            • 3. Re: How to optimize this SQL using DBLINK and execute long time and not complete now ?
              Mustafa KALAYCI

              small addition to what Paul said, what indexes do you have on sd_res_id_case table at remote? maybe an index that includes is_school_check and delete_flag could be increase your performance.

              • 4. Re: How to optimize this SQL using DBLINK and execute long time and not complete now ?
                Paulzip

                I can only go by what you give me. Those are parameters, you can still explain plan with parameters, or simply replace with typical values. I don't have your db tables, data so you need to make that decision.

                • 5. Re: How to optimize this SQL using DBLINK and execute long time and not complete now ?
                  Mustafa KALAYCI

                  also one more thing: ":"SYS_B_5" = :"SYS_B_6"" what is this? if there is a condition to run this sql check it in your applicaiton first like using an if statement then run the query.

                  • 6. Re: How to optimize this SQL using DBLINK and execute long time and not complete now ?
                    Billy~Verreynne

                    Quanwen Zhao wrote:

                     

                    Hello,Paulzip

                    I've no idea about the value of colon behind,such as ':"SYS_B_6"' or ':2' and etc.

                    These are bind variables. Likely either string, or numeric.

                     

                    You can (in SQL*Plus) use the VAR command to define these bind variables, prior to running the EXPLAIN PLAN command.

                    • 7. Re: How to optimize this SQL using DBLINK and execute long time and not complete now ?
                      AndrewSayer

                      Mustafa KALAYCI wrote:

                       

                      also one more thing: ":"SYS_B_5" = :"SYS_B_6"" what is this? if there is a condition to run this sql check it in your applicaiton first like using an if statement then run the query.

                      Oracle will use those as the names for bind variables it replaces literals with when shared_cursor is force/similar.

                      The presense of the t.county_id = :1 suggests that actually the application is able to use bind variables so there should be no reason for shared_cursor to be anything other than exact.

                      • 8. Re: How to optimize this SQL using DBLINK and execute long time and not complete now ?
                        AndrewSayer

                        Quanwen Zhao wrote:

                         

                        Hello,Paulzip

                        I've no idea about the value of colon behind,such as ':"SYS_B_6"' or ':2' and etc.

                         

                        Best Regards,

                        Quanwen Zhao

                        Someone should know, probably the person who's running this silly SQL?

                         

                        As I've already said, the SYS_B.. are from forced cursor sharing, this means:

                         

                        SELECT  * 

                        FROM    sd_res_id_case@res r2 

                        WHERE   :"SYS_B_0" =:"SYS_B_1" 

                         

                        Really means something like

                         

                        select * from table where 1= 1

                        Or possibly where 1=0

                         

                        As you can already see, your live monitor and your plan are different, that's because the plan is an explain plan and obviously can't be trusted.

                         

                        AND rowid IN 

                                SELECT  MIN(rowid)

                         

                        MIN(rowid)?!?!?! that probably isn't what whoever wrote this silly SQL think it is.

                         

                        And if it is, then that whole bit can probably be written as an analytic, something like (untested)

                        FROM 

                                ( 

                                        SELECT  subject_id  

                                               ,version_id  

                                               ,year_mark   

                                               ,textbook_code 

                                               ,row_number() over (partition by school_id order by rowid) rown

                                               ,count(*) over (partition by school_id) cnt

                                        FROM    sd_res_id_case@res r

                                        WHERE   :"SYS_B_0" =:"SYS_B_1" 

                                        AND     r.delete_flag        =:"SYS_B_2" 

                                        AND     r.is_school_check=:"SYS_B_3" 

                                        AND     r.subject_id    IS NOT NULL 

                                        AND     r.version_id    IS NOT NULL 

                                        AND     r.textbook_code IS NOT NULL 

                                ) r where r.rown = 1 and r.cnt >= :"SYS_B_4"    

                         

                        Will mean that you only goto that remote table once

                         

                        I'm confident that those literal comparisons can be done better

                        I'm confident that cursor_sharing should be exact for this particular SQL.

                        1 person found this helpful
                        • 9. Re: How to optimize this SQL using DBLINK and execute long time and not complete now ?
                          Mustafa KALAYCI

                          Hi Andrew,

                           

                          I know about bind variables what I tried to ask is why 2 bind variables equality is being checked on SQL. probably in sql there is a line

                           

                          where ...

                          and :my_number = :my_Second_number

                           

                          etc.

                           

                          I am asking to OP why she/he doesn't check this on application instead adding this check to sql. checking two variables can be done by an if in application no need for sql.

                          • 10. Re: How to optimize this SQL using DBLINK and execute long time and not complete now ?
                            AndrewSayer

                            Mustafa KALAYCI wrote:

                             

                            Hi Andrew,

                             

                            I know about bind variables what I tried to ask is why 2 bind variables equality is being checked on SQL. probably in sql there is a line

                             

                            where ...

                            and :my_number = :my_Second_number

                             

                            etc.

                             

                            I am asking to OP why she/he doesn't check this on application instead adding this check to sql. checking two variables can be done by an if in application no need for sql.

                            Don't worry, I wouldn't doubt you knowing about bind variables. It just wasn't clear if the actual names of the bind variables had stuck out as being created by forced cursor sharing to replace literals that were actually submitted.

                            • 11. Re: How to optimize this SQL using DBLINK and execute long time and not complete now ?
                              Quanwen Zhao

                              Andrew Sayer 撰写:

                               

                              Quanwen Zhao wrote:

                               

                              Hello,Paulzip

                              I've no idea about the value of colon behind,such as ':"SYS_B_6"' or ':2' and etc.

                               

                              Best Regards,

                              Quanwen Zhao

                              Someone should know, probably the person who's running this silly SQL?

                               

                              As I've already said, the SYS_B.. are from forced cursor sharing, this means:

                               

                              SELECT *

                              FROM sd_res_id_case@res r2

                              WHERE :"SYS_B_0" =:"SYS_B_1"

                               

                              Really means something like

                               

                              select * from table where 1= 1

                              Or possibly where 1=0

                               

                              As you can already see, your live monitor and your plan are different, that's because the plan is an explain plan and obviously can't be trusted.

                               

                              AND rowid IN

                              (

                              SELECT MIN(rowid)

                               

                              MIN(rowid)?!?!?! that probably isn't what whoever wrote this silly SQL think it is.

                               

                              And if it is, then that whole bit can probably be written as an analytic, something like (untested)

                              FROM

                              (

                              SELECT subject_id

                              ,version_id

                              ,year_mark

                              ,textbook_code

                              ,row_number() over (partition by school_id order by rowid) rown

                              ,count(*) over (partition by school_id) cnt

                              FROM sd_res_id_case@res r

                              WHERE :"SYS_B_0" =:"SYS_B_1"

                              AND r.delete_flag =:"SYS_B_2"

                              AND r.is_school_check=:"SYS_B_3"

                              AND r.subject_id IS NOT NULL

                              AND r.version_id IS NOT NULL

                              AND r.textbook_code IS NOT NULL

                              ) r where r.rown = 1 and r.cnt >= :"SYS_B_4"

                               

                              Will mean that you only goto that remote table once

                               

                              I'm confident that those literal comparisons can be done better

                              I'm confident that cursor_sharing should be exact for this particular SQL.

                              Thank you very much,Andrew Sayer

                              Your advices is very nice!

                              Because I am a SQL beginner.Afterwards,i found the value of my oracle db parameter cursor_sharing is FORCE.

                              Therefore,there has existed this similar value (such as :"SYS_B_1") in SQL via my EMCC 12c.

                              Next,i changed parameter cursor_sharing to be EXACT.

                              After a period of time,i found a similar SQL statement same as above and all of this value (such as :"SYS_B_1") have become exact number(such as 0 or 1).

                              At the same time,its SQL_ID has become '4gd0562wb2n13', not original '83gn36c1fu9dw'.

                               

                              Please see my SQL statement found below,

                               

                              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   1          =1

                                                                                      AND rowid IN

                                                                                      (

                                                                                              SELECT  MIN(rowid)

                                                                                              FROM    sd_res_id_case@res r1

                                                                                              WHERE   r1.delete_flag        =0

                                                                                                      AND r1.is_school_check=2

                                                                                                      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(*)>=1

                                                                                      )

                                                                      )

                                                                      r

                                                                      LEFT JOIN base_school t

                                                                      ON      r.school_id = t.school_id

                                                              WHERE   1                   =1

                                                                      AND t.county_id     =:1

                                                      )

                                                      a

                                              WHERE   rownum<6

                                      )

                              WHERE   rownum <= :2;

                               

                              And its execution plan as follows,

                               

                              select * from table(dbms_xplan.display_cursor(sql_id => '4gd0562wb2n13'));

                               

                              SQL_ID  4gd0562wb2n13, child number 0

                              -------------------------------------

                              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 1=1 and  rowid  in (   select min(rowid) from sd_res_id_case@res

                              r1    where r1.delete_flag=0        and r1.is_school_check=2       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(*)>=1 )   )  r  left join base_school t on r.school_id =

                              t.school_id   where  1=1           and t.county_id=:1      ) a where

                              rownum<6 ) where rownum <= :2

                               

                              Plan hash value: 2069821418

                               

                              ----------------------------------------------------------------------------------------------------------------------

                              | Id  | Operation                       | Name               | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|

                              ----------------------------------------------------------------------------------------------------------------------

                              |   0 | SELECT STATEMENT                |                    |       |       |   682K(100)|          |        |      |

                              |*  1 |  COUNT STOPKEY                  |                    |       |       |            |          |        |      |

                              |   2 |   VIEW                          |                    |     5 |  1525 |   682K  (1)| 02:16:29 |        |      |

                              |*  3 |    COUNT STOPKEY                |                    |       |       |            |          |        |      |

                              |*  4 |     HASH JOIN                   |                    |  1001 |  9805K|   682K  (1)| 02:16:29 |        |      |

                              |   5 |      TABLE ACCESS BY INDEX ROWID| BASE_SCHOOL        |   193 | 20651 |   133   (0)| 00:00:02 |        |      |

                              |*  6 |       INDEX RANGE SCAN          | IDX_BASE_COUNTY_ID |   195 |       |     4   (0)| 00:00:01 |        |      |

                              |   7 |      NESTED LOOPS               |                    |  1156K|  5473M|   749K (10)| 02:30:00 |        |      |

                              |   8 |       VIEW                      | VW_NSO_1           | 11147 |    42M|    86   (2)| 00:00:02 |        |      |

                              |   9 |        REMOTE                   |                    |       |       |            |          |    RES | R->S |

                              |  10 |       REMOTE                    | SD_RES_ID_CASE     |   578K|   529M|   341K  (1)| 01:08:13 |    RES | R->S |

                              ----------------------------------------------------------------------------------------------------------------------

                               

                              Predicate Information (identified by operation id):

                              ---------------------------------------------------

                               

                                 1 - filter(ROWNUM<=:2)

                                 3 - filter(ROWNUM<6)

                                 4 - access("R2"."SCHOOL_ID"="T"."SCHOOL_ID")

                                 6 - access("T"."COUNTY_ID"=:1)

                               

                              Remote SQL Information (identified by operation id):

                              ----------------------------------------------------

                               

                                 9 - SELECT MIN("A1".ROWID) FROM "SD_RES_ID_CASE" "A1" WHERE "A1"."DELETE_FLAG"=0 AND

                                     "A1"."IS_SCHOOL_CHECK"=2 AND "A1"."SUBJECT_ID" IS NOT NULL AND "A1"."VERSION_ID" IS NOT NULL AND

                                     "A1"."TEXTBOOK_CODE" IS NOT NULL GROUP BY "A1"."SCHOOL_ID" HAVING COUNT(*)>=1 (accessing 'RES' )

                               

                                10 - SELECT ROWID,"SUBJECT_ID","TEXTBOOK_CODE","SCHOOL_ID","VERSION_ID","YEAR_MARK" FROM "SD_RES_ID_CASE"

                                      "R2" WHERE ROWID=:1 (accessing 'RES' )

                               

                              Remote Oracle DB's execution plan as follows,

                               

                              select * from table(dbms_xplan.display_cursor(sql_id => '7pbcm2rqbp7hs'));

                               

                               

                              SQL_ID  7pbcm2rqbp7hs, child number 0

                              -------------------------------------

                              SELECT MIN("A1".ROWID) FROM "SD_RES_ID_CASE" "A1" WHERE

                              "A1"."DELETE_FLAG"=0 AND "A1"."IS_SCHOOL_CHECK"=2 AND "A1"."SUBJECT_ID"

                              IS NOT NULL AND "A1"."VERSION_ID" IS NOT NULL AND "A1"."TEXTBOOK_CODE"

                              IS NOT NULL GROUP BY "A1"."SCHOOL_ID" HAVING COUNT(*)>=1

                               

                              Plan hash value: 766909583

                               

                              ----------------------------------------------------------------------------------------------

                              | Id  | Operation           | Name           | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |

                              ----------------------------------------------------------------------------------------------

                              |   0 | SELECT STATEMENT    |                |       |       |       |   613K(100)|          |

                              |*  1 |  FILTER             |                |       |       |       |            |          |

                              |   2 |   HASH GROUP BY     |                | 11148 |  1284K|  1156M|   613K  (1)| 02:02:40 |

                              |*  3 |    TABLE ACCESS FULL| SD_RES_ID_CASE |  8880K|   999M|       |   530K  (1)| 01:46:05 |

                              ----------------------------------------------------------------------------------------------

                               

                              Predicate Information (identified by operation id):

                              ---------------------------------------------------

                               

                                 1 - filter(COUNT(*)>=1)

                                 3 - filter(("A1"."IS_SCHOOL_CHECK"=2 AND "A1"."DELETE_FLAG"=0 AND

                                            "A1"."SUBJECT_ID" IS NOT NULL AND "A1"."VERSION_ID" IS NOT NULL AND

                                            "A1"."TEXTBOOK_CODE" IS NOT NULL))

                               

                              Wait a moment,according to your rewrite SQL method(Analytic function),i will try it.

                               

                              By the way,current SQL about execution plan using sql monitor to capture has been executed successfully and another about only post execution plan has not been executed successfully.Sorry,i had descripted unclearly last time.

                               

                              Best Regards,

                              Quanwen Zhao

                              • 12. Re: How to optimize this SQL using DBLINK and execute long time and not complete now ?
                                Quanwen Zhao

                                Paulzip 撰写:

                                 

                                Can you paste the execution plan for this...

                                 

                                select /*+ driving_site(sd_res_id_case) */ *

                                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

                                Thank you,Paulzip

                                I changed parameter cursor_sharing to be EXACT,from then on, this value (such as :"SYS_B_1") have not existed except real Bind Variable(such as :1 and :2).

                                Afterwards,i use SQLT to generate serial REPORT for SQL above(another SQL_ID is '4gd0562wb2n13' not originally),at the same time,i see some the value of bind variable ':1' and ':2'.

                                According to your advice,i rewrite an NEW SQL below,

                                 

                                select /*+ driving_site(sd_res_id_case) */ * 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 1=1 and  rowid  in (

                                  select min(rowid) from sd_res_id_case@res r1

                                  where r1.delete_flag=0

                                      and r1.is_school_check=2

                                      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(*)>=1 )

                                  )  r  left join base_school t on r.school_id = t.school_id

                                  where  1=1

                                  and t.county_id='36288003491df4b9014931007cb30b15') a where rownum<6 )

                                  where rownum <= 1000;

                                 

                                It spent 128.804 seconds to execute successfully and explain plan as follows,

                                1.png

                                Next,i try to execute another SQL lonely(a part of full SQL above to remote db ) on remote db,

                                 

                                SELECT  MIN("A1".ROWID)

                                FROM "SD_RES_ID_CASE" "A1"

                                WHERE "A1"."DELETE_FLAG"          =0

                                        AND "A1"."IS_SCHOOL_CHECK"=2

                                        AND "A1"."SUBJECT_ID"    IS NOT NULL

                                        AND "A1"."VERSION_ID"    IS NOT NULL

                                        AND "A1"."TEXTBOOK_CODE" IS NOT NULL

                                GROUP BY "A1"."SCHOOL_ID"

                                HAVING COUNT(*)>=1;

                                 

                                It spent 29.324 seconds to execute successfully and its explain plan as follows,

                                2.png

                                By the way,i executed (no hint) original SQL and spent 125.656 seconds to complete and its explain plan as follows(Comparison convenience).

                                3.png

                                Finally,i found that explain plan of adding HINT or not seemed to be same.Please Note.

                                 

                                Best Regards,

                                Quanwen Zhao

                                • 13. Re: How to optimize this SQL using DBLINK and execute long time and not complete now ?
                                  AndrewSayer

                                  Explain plan <> execution plan

                                   

                                  One tells you how Oracle executed the statement, the other is explain plan.

                                  1 person found this helpful
                                  • 14. Re: How to optimize this SQL using DBLINK and execute long time and not complete now ?
                                    Quanwen Zhao

                                    Andrew Sayer 撰写:

                                     

                                    Explain plan <> execution plan

                                     

                                    One tells you how Oracle executed the statement, the other is explain plan.

                                    Hi,Andrew Sayer

                                    According to your guide,my latest post to Paulzip should be explain plan(because i press F5 in my PL/SQL Developer after SQL statement executed successfully),

                                    actually spending time execute that SQL is short and my explain plan show very long,it estimated not correctly?

                                    Now, how to obtain real execution plan ? Thank you.

                                     

                                    Best Regards,

                                    Quanwen Zhao

                                    1 2 3 Previous Next