3 Replies Latest reply: Feb 28, 2014 1:53 AM by Purvesh K RSS

    Issue with the sql update query

    rock_001

      I have an issue with the below sql query which is taking more than 40mins for execution,

       

       

      UPDATE A o SET    id = ( SELECT s.id

                             FROM   B s WHERE  s.num = o.num AND    s.f_num = o.f_num )

      WHERE  o.id is null AND    o.date > (select (a_dt - 90) from C where dt_ind='Y') AND   

      EXISTS ( SELECT 1 FROM   B s WHERE  s.num = o.num AND    s.f_num = o.f_num);

       

       

      Upon checking the explain plan this is what it shows, Any suggestions on this is much apprecoated

       

      PLAN_TABLE_OUTPUT

      --------------------------------------------------------------------------------------------------------------
      | Id  | Operation                            | Name             | Rows  | Bytes | Cost (%CPU)| Pstart| Pstop |
      --------------------------------------------------------------------------------------------------------------
      |   0 | UPDATE STATEMENT                     |                  |     2 |    62 |   590   (1)|       |       |
      |   1 |  UPDATE                                        | A               |       |       |            |       |       |
      |   2 |   NESTED LOOPS SEMI                  |                  |     2 |    62 |   581   (1)|       |       |
      |   3 |    PARTITION RANGE ALL               |                  |     2 |    42 |   579   (1)|     1 |     8 |
      |*  4 |     TABLE ACCESS BY LOCAL INDEX ROWID | A  |     2 |    42 |   579   (1)|     1 |     8 |
      |*  5 |      INDEX RANGE SCAN                | XIE1A   |  9360 |       |    42   (0)|     1 |     8 |
      |   6 |       TABLE ACCESS BY INDEX ROWID    | B_DATE           |     1 |    10 |     1   (0)|       |       |

      PLAN_TABLE_OUTPUT
      --------------------------------------------------------------------------------------------------------------
      |   7 |        BITMAP CONVERSION TO ROWIDS   |                  |       |       |            |       |       |
      |*  8 |         BITMAP INDEX SINGLE VALUE    | XIE3B_DATE       |       |       |            |       |       |
      |*  9 |    INDEX UNIQUE SCAN                 | XPKACCOUNT       |    22M|   219M|     1   (0)|       |       |
      |  10 |   TABLE ACCESS BY INDEX ROWID        | B                |     1 |    16 |     3   (0)|       |       |
      |* 11 |    INDEX UNIQUE SCAN                 | XPKB             |     1 |       |     2   (0)|       |       |
      --------------------------------------------------------------------------------------------------------------


      Thanks

        • 1. Re: Issue with the sql update query
          K.S.I.

          Hi.

           

          Oracle version ?

           

          Pay attention to a line (9) and (7,8) of your plan

           

          If the version allows, then  generate ASH/AWR report and look  or using sql_monitoring.

           

          >= 10g

          please  show  output this sql

          SET LONG 1000000

          SET LONGCHUNKSIZE 1000000

          SET LINESIZE 1000

          SET PAGESIZE 0

          SET TRIM ON

          SET TRIMSPOOL ON

          SET ECHO OFF

          SET FEEDBACK OFF

           

          SPOOL report_sql_monitor.rep

          SELECT DBMS_SQLTUNE.report_sql_monitor(

            sql_id => 'YOUR SQL_ID',

            type => 'HTML'  or 'TEXT',

            report_level => 'ALL') AS report

          FROM dual;

          SPOOL OFF

          :

          • 2. Re: Issue with the sql update query
            Nimish Garg

            Try it with merge [Not Tested]

            merge into a

            using b

            on (b.num = a.num and b.f_num = a.f_num and a.date > (select (a_dt - 90) from C where dt_ind='Y'))

            when matched then

              set a.id = b.id;

             

            if it fails, then can you try it replacing "(select (a_dt - 90) from C where dt_ind='Y')" with its value as it seems constant every time

            • 3. Re: Issue with the sql update query
              Purvesh K

              rock_001 wrote:

               

              I have an issue with the below sql query which is taking more than 40mins for execution,

               

               

              UPDATE A o SET    id = ( SELECT s.id

                                     FROM   B s WHERE  s.num = o.num AND    s.f_num = o.f_num )

              WHERE  o.id is null AND    o.date > (select (a_dt - 90) from C where dt_ind='Y') AND   

              EXISTS ( SELECT 1 FROM   B s WHERE  s.num = o.num AND    s.f_num = o.f_num);

               

               

              Why do you need to have the Exists predicate when you are already adding a filter to pick the ID from table B?

               

              The Explain plan may not give you the correct estimates, try to look at the execution plan using DBMS_XPLAN.DISPLAY_CURSOR with SQL_ID for the particular statement. This has to be done when the SQL statement is in progress.

               

              It will be beneficial to post the explain plans in SQL format or in Quotes which increases the readability of the plans.

               

              Did you try to see which SQL statement is taking more time to execute, the Sub-query or the Corelated SQL?

               

              You also need to mention the row counts of the table and the one satisfying the Update querys where condition. Additionally, please go through the HOW TO: Post a SQL statement tuning request - template posting and post the mentioned information to help others help you.

               

              Regards,

              Purvesh.