4 Replies Latest reply: Mar 11, 2011 10:07 AM by 486215 RSS

    Update statement tuning

    486215
      I am working on Oracle 10.2.0.4 RAC on Solaris SPARC.

      I have the following update statement which takes around 40 minutes to execute
      update sample_tr2 set owner = 'sample' 
      WHERE SERVER = 'LOMSServer1' AND id is null 
      OR id = '5.1.49349' 
      OR id in (select id from sample_bl where owner = 'marissa.matthews');
      Explain plan is as follows
      ------------------------------------------------------------------------------------------------------
      | Id  | Operation                     | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
      ------------------------------------------------------------------------------------------------------
      |   0 | UPDATE STATEMENT              |                      |  9633 |   319K| 11350   (1)| 00:02:17 |
      |   1 |  UPDATE                       | SAMPLE_TR2           |       |       |            |          |
      |*  2 |   FILTER                      |                      |       |       |            |          |
      |   3 |    TABLE ACCESS FULL          | SAMPLE_TR2           |   111K|  3690K| 11350   (1)| 00:02:17 |
      |*  4 |    TABLE ACCESS BY INDEX ROWID| SAMPLE_BL            |     1 |    22 |   700   (1)| 00:00:09 |
      |*  5 |     INDEX RANGE SCAN          | IDX_SB_OWNER         |  1658 |       |     6   (0)| 00:00:01 |
      The same statement when executed as two update statements finishes in few seconds
      update sample_tr2 set owner = 'sample' 
      WHERE SERVER = 'LOMSServer1' AND id is null 
      OR id = '5.1.49349' ;
      
      Elapsed: 00:00:00.23
      
      update sample_tr2 set owner = 'sample' 
      WHERE id in (select id from sample_bl where owner = 'marissa.matthews');
      
      Elapsed: 00:00:00.21
      
      --------------------------------------------------------------------------------------------------------
      | Id  | Operation                      | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
      --------------------------------------------------------------------------------------------------------
      |   0 | UPDATE STATEMENT               |                       | 26620 |  1143K|  3911   (1)| 00:00:47 |
      |   1 |  UPDATE                        | SAMPLE_TR2            |       |       |            |          |
      |   2 |   NESTED LOOPS                 |                       | 26620 |  1143K|  3911   (1)| 00:00:47 |
      |   3 |    SORT UNIQUE                 |                       |  1615 | 35530 |   681   (1)| 00:00:09 |
      |   4 |     TABLE ACCESS BY INDEX ROWID| SAMPLE_BL             |  1615 | 35530 |   681   (1)| 00:00:09 |
      |*  5 |      INDEX RANGE SCAN          | IDX_SB_OWNER          |  1615 |       |     5   (0)| 00:00:01 |
      The operation taking the most time is FULL TABLE SCAN on SAMPLE_TR2.

      Is there any way to tune this query so that the operation can be performed efficiently in a single query?
        • 1. Re: Update statement tuning
          Vladimir Zakharychev
          Add an index to support your predicates. For example, an index on (ID,SERVER) would allow Oracle to
          a) quickly identify all rows where ID is NULL and SERVER is some particular value
          b) quickly identify all rows with particular ID value
          c) concatenate these two and perform single update on concatenated result set

          By the way, your update will affect

          - all rows with server being 'LOMSServer1' and NULL id
          - all rows with id of '5.1.49349' (regardless the SERVER value)
          - all rows with id returned by the subquery

          Is this the intended result? Remember that AND takes precedence over OR, so if you only wanted to update rows with SERVER being certain value and ID being NULL or certain value, you need to surround the OR with parentheses.
          • 2. Re: Update statement tuning
            Centinul
            Can you run the update statement again, with the GATHER_PLAN_STATISTICS hint like this:
            update /*+ gather_plan_statistics */ sample_tr2 set owner = 'sample' 
            WHERE SERVER = 'LOMSServer1' AND id is null 
            OR id = '5.1.49349' 
            OR id in (select id from sample_bl where owner = 'marissa.matthews');
            Then immediately following run this statement and post the output:
            SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
            That will give us the estimated values, and actual values for rows and the predicate information section.

            See these threads for more information:

            {message:id=1812597}

            {thread:id=863295}
            • 3. Re: Update statement tuning
              Jonathan Lewis
              Prabu M wrote:
              I am working on Oracle 10.2.0.4 RAC on Solaris SPARC.

              I have the following update statement which takes around 40 minutes to execute
              update sample_tr2 set owner = 'sample' 
              WHERE SERVER = 'LOMSServer1' AND id is null 
              OR id = '5.1.49349' 
              OR id in (select id from sample_bl where owner = 'marissa.matthews');
              Your basic problem is that the optimizer cannot handle +"or exists (subquery)"+ efficiently - see http://jonathanlewis.wordpress.com/2007/02/26/subquery-with-or/ for a solution where you are running a simple select.

              In your case you should be able to rewrite the query to update by rowid.
              Step 1 - write a select statement that selects the rowids from sample_tr2 for your conditions, using the blog item as an indication of how to convert the OR to a UNION ALL.
              Step 2 - write the update as
              update sample_tr2
              set owner = 'SAMPLE'
              where rowid in ( your union all select query);
              Regards
              Jonathan Lewis
              http://jonathanlewis.wordpress.com
              http://www.jlcomp.demon.co.uk

              A general reminder about "Forum Etiquette / Reward Points": http://forums.oracle.com/forums/ann.jspa?annID=718

              If you never mark your questions as answered people will eventually decide that it's not worth trying to answer you because they will never know whether or not their answer has been of any use, or whether you even bothered to read it.

              It is also important to mark answers that you thought helpful - again it lets other people know that you appreciate their help, but it also acts as a pointer for other people when they are researching the same question, moreover it means that when you mark a bad or wrong answer as helpful someone may be prompted to tell you (and the rest of the forum) what's so bad or wrong about the answer you found helpful.
              • 4. Re: Update statement tuning
                486215
                Thanks for the quick responses.

                Yes, I need the AND to take precendence and that was the intended result.

                The select itself was consuming around 25 minutes
                SELECT COUNT(*) FROM SAMPLE_TR2
                WHERE SERVER = 'LOMSServer1' AND id is null
                OR id = '5.1.49349'
                OR id in (select id from sample_bl where owner = 'marissa.matthews');
                I rewrote the query as
                SELECT COUNT(*) FROM sample_tr2 tr LEFT OUTER JOIN sample_bl bt
                ON tr.id = bt.id
                WHERE
                (server = 'LOMSServer1' AND tr.id IS NULL
                OR tr.id = '5.1.49349'
                OR bt.owner = 'marissa.matthews');
                --------------------------------------------------------------------------------------------------
                | Id  | Operation                 | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
                --------------------------------------------------------------------------------------------------
                |   0 | UPDATE STATEMENT          |                      | 10087 |   728K| 23441   (1)| 00:04:42 |
                |   1 |  UPDATE                   | SAMPLE_TR2           |       |       |            |          |
                |*  2 |   HASH JOIN RIGHT SEMI    |                      | 10087 |   728K| 23441   (1)| 00:04:42 |
                |   3 |    VIEW                   | VW_NSO_1             | 10087 |   512K| 12107   (1)| 00:02:26 |
                |*  4 |     FILTER                |                      |       |       |            |          |
                |*  5 |      HASH JOIN RIGHT OUTER|                      | 10087 |   551K| 12107   (1)| 00:02:26 |
                |   6 |       TABLE ACCESS FULL   | SAMPLE_BL            | 30558 |   656K|   756   (1)| 00:00:10 |
                |   7 |       TABLE ACCESS FULL   | SAMPLE_TR2           |   111K|  3690K| 11348   (1)| 00:02:17 |
                |   8 |    TABLE ACCESS FULL      | SAMPLE_TR            |   111K|  2388K| 11333   (1)| 00:02:16 |
                --------------------------------------------------------------------------------------------------
                Now the query completes in 20 seconds, in fact I have rebuilt the table without any indexes. But the old query when run still takes 20+ minutes.

                Though I have the solution now, I am not sure about the reason for such a huge difference.

                I was initially thinking it as a problem with index, but indexes didn't have any role to play here.