Home » RDBMS Server » Performance Tuning » update statement takes too long (11.2.0.1.0)
update statement takes too long [message #632462] Sun, 01 February 2015 06:19 Go to next message
OraFerro
Messages: 433
Registered: July 2011
Senior Member
Hi All,

I have an update query that takes too long as shown in the below explain plan despite the fact that the normal select statement that joins the same two tables needed for the update runs fast.

Below is the plan for both cases showing the cost of each statement:
EXPLAIN PLAN 
    SET STATEMENT_ID = 'journal_update_inv' 
    INTO plan_table 
    FOR UPDATE iv_table CPA SET CPA.FK_JOURNAL_ENTRY =
        (
          SELECT JOR.JOURNAL_SERIAL FROM ac_jor_table JOR
          WHERE CPA.fk_v_date = JOR.fk_v_date
          AND CPA.fk_v_ser = JOR.fk_v_ser
          AND JOR.fk_maj_no = 1
          AND JOR.fk_act =4--
          and sign(cpa.AMOUNT) = sign(jor.AMOUNT)
        )
        WHERE CPA.TYPE_ID = 1;  

SELECT LPAD(' ',2*(LEVEL-1))||operation operation, options, 
object_name, position 
    FROM plan_table 
    START WITH id = 0 AND statement_id = 'journal_update_inv'
    CONNECT BY PRIOR id = parent_id AND statement_id = 'journal_update_inv';


and the result is:

OPERATION	OPTIONS	OBJECT_NAME	POSITION
UPDATE STATEMENT	(null)	(null)	97397628
  UPDATE	(null)	iv_table	1
    TABLE ACCESS	FULL	iv_table	1
    TABLE ACCESS	FULL	iv_table	1
    TABLE ACCESS	FULL	iv_table	1
    TABLE ACCESS	FULL	ac_jor_table	2
    TABLE ACCESS	FULL	ac_jor_table	2
    TABLE ACCESS	FULL	ac_jor_table	2
  UPDATE	(null)	iv_table	1
    TABLE ACCESS	FULL	iv_table	1
    TABLE ACCESS	FULL	iv_table	1
    TABLE ACCESS	FULL	iv_table	1
    TABLE ACCESS	FULL	ac_jor_table	2
    TABLE ACCESS	FULL	ac_jor_table	2
    TABLE ACCESS	FULL	ac_jor_table	2
  UPDATE	(null)	iv_table	1
    TABLE ACCESS	FULL	iv_table	1
    TABLE ACCESS	FULL	iv_table	1
    TABLE ACCESS	FULL	iv_table	1
    TABLE ACCESS	FULL	ac_jor_table	2
    TABLE ACCESS	FULL	ac_jor_table	2
    TABLE ACCESS	FULL	ac_jor_table	2
UPDATE STATEMENT	(null)	(null)	97397628
  UPDATE	(null)	iv_table	1
    TABLE ACCESS	FULL	iv_table	1
    TABLE ACCESS	FULL	iv_table	1
    TABLE ACCESS	FULL	iv_table	1
    TABLE ACCESS	FULL	ac_jor_table	2
    TABLE ACCESS	FULL	ac_jor_table	2
    TABLE ACCESS	FULL	ac_jor_table	2
  UPDATE	(null)	iv_table	1
    TABLE ACCESS	FULL	iv_table	1
    TABLE ACCESS	FULL	iv_table	1
    TABLE ACCESS	FULL	iv_table	1
    TABLE ACCESS	FULL	ac_jor_table	2
    TABLE ACCESS	FULL	ac_jor_table	2
    TABLE ACCESS	FULL	ac_jor_table	2
  UPDATE	(null)	iv_table	1
    TABLE ACCESS	FULL	iv_table	1
    TABLE ACCESS	FULL	iv_table	1
    TABLE ACCESS	FULL	iv_table	1
    TABLE ACCESS	FULL	ac_jor_table	2
    TABLE ACCESS	FULL	ac_jor_table	2
    TABLE ACCESS	FULL	ac_jor_table	2
UPDATE STATEMENT	(null)	(null)	97397628
  UPDATE	(null)	iv_table	1
    TABLE ACCESS	FULL	iv_table	1
    TABLE ACCESS	FULL	iv_table	1
    TABLE ACCESS	FULL	iv_table	1
    TABLE ACCESS	FULL	ac_jor_table	2
    TABLE ACCESS	FULL	ac_jor_table	2
    TABLE ACCESS	FULL	ac_jor_table	2
  UPDATE	(null)	iv_table	1
    TABLE ACCESS	FULL	iv_table	1
    TABLE ACCESS	FULL	iv_table	1
    TABLE ACCESS	FULL	iv_table	1
    TABLE ACCESS	FULL	ac_jor_table	2
    TABLE ACCESS	FULL	ac_jor_table	2
    TABLE ACCESS	FULL	ac_jor_table	2
  UPDATE	(null)	iv_table	1
    TABLE ACCESS	FULL	iv_table	1
    TABLE ACCESS	FULL	iv_table	1
    TABLE ACCESS	FULL	iv_table	1
    TABLE ACCESS	FULL	ac_jor_table	2
    TABLE ACCESS	FULL	ac_jor_table	2
    TABLE ACCESS	FULL	ac_jor_table	2


and the plan for the select statement is:


EXPLAIN PLAN 
    SET STATEMENT_ID = 'select_with_my_tables' 
    INTO plan_table 
    FOR 
      select jor.* 
        from iv_table cpa, ac_jor_table jor
        WHERE   cpa.type_id = 1
                and CPA.fk_v_date = JOR.fk_v_date
                AND CPA.fk_v_ser = JOR.fk_v_ser
                AND JOR.fk_maj_no = 1
                AND JOR.fk_act =4--
                and sign(cpa.AMOUNT) = sign(jor.AMOUNT);
          

SELECT LPAD(' ',2*(LEVEL-1))||operation operation, options, 
object_name, position 
    FROM plan_table 
    START WITH id = 0 AND statement_id = 'select_with_my_tables'
    CONNECT BY PRIOR id = parent_id AND statement_id = 'select_with_my_tables';




OPERATION	OPTIONS	OBJECT_NAME	POSITION
SELECT STATEMENT	(null)	(null)	1738
  HASH JOIN	(null)	(null)	1
    TABLE ACCESS	FULL	ac_jor_table	1
    TABLE ACCESS	FULL	iv_table	2



Thanks,
Ferro

[Updated on: Sun, 01 February 2015 06:21]

Report message to a moderator

Re: update statement takes too long [message #632464 is a reply to message #632462] Sun, 01 February 2015 06:26 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Use dbms_xplan.display to correctly display the explain plan.
For any performances question, please read http://www.orafaq.com/forum/mv/msg/84315/433888/102589/#msg_433888 and post the required information.


Re: update statement takes too long [message #632467 is a reply to message #632464] Sun, 01 February 2015 06:59 Go to previous messageGo to next message
OraFerro
Messages: 433
Registered: July 2011
Senior Member
HI Michel,

Here is the explain plan from dbms_xplain.display
PLAN_TABLE_OUTPUT
Plan hash value: 862353629
 
-------------------------------------------------------------------------------------------------
| Id  | Operation          | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT   |                            |   155K|  3634K|    97M  (1)|324:39:32 |
|   1 |  UPDATE            | iv_table |       |       |            |          |
|*  2 |   TABLE ACCESS FULL| iv_table |   155K|  3634K|  1108   (1)| 00:00:14 |
|*  3 |   TABLE ACCESS FULL| ac_jor_table          |     1 |    29 |   627   (1)| 00:00:08 |
-------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("CPA"."TYPE_ID"=1)
   3 - filter("JOR"."fk_v_date"=:B1 AND "JOR"."fk_v_ser"=:B2 AND 
              "JOR"."fk_act"=4 AND "JOR"."fk_maj_no"=1 AND 
              SIGN("JOR"."AMOUNT")=SIGN(:B3))

Re: update statement takes too long [message #632468 is a reply to message #632467] Sun, 01 February 2015 07:05 Go to previous messageGo to next message
OraFerro
Messages: 433
Registered: July 2011
Senior Member
For steps 3 and 4 in the guidelines, I cant post the actual table definition but I can post a custom table for the involved fields + PK (all join fields are NOT indexed).

For the trace while running the SQL, it take too long and really affect production, it will be great if the above information is enough for guiding me, else will run it and will send you the required output (but will have to wait for one day for that)!

Thanks,
Ferro
Re: update statement takes too long [message #632470 is a reply to message #632468] Sun, 01 February 2015 07:19 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Oracle believes that ac_jor_table contains only 1 row based upon what EXPLAIN PLAN shows.

Since NOBODY can optimize SQL just by looking at it, we need a few more details.
http://www.orafaq.com/forum/mv/msg/84315/433888/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) DDL for all tables & indexes
23) output from SQL_TRACE & tkprof
Re: update statement takes too long [message #632471 is a reply to message #632470] Sun, 01 February 2015 07:42 Go to previous messageGo to next message
OraFerro
Messages: 433
Registered: July 2011
Senior Member
Hi BlackSwan,

This is really strange:
select count(*) from ac_jor_table;

COUNT(*)
276604


It will be great if you can give me a hint why would Oracle believe that while I am waiting for the next possible time slot to run the trace

Thanks,
Ferro
Re: update statement takes too long [message #632472 is a reply to message #632471] Sun, 01 February 2015 07:48 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
It appears that statistics for objects are majorly incorrect.
Consider running DBMS_STATS to obtain current & valid statistics
Re: update statement takes too long [message #632477 is a reply to message #632470] Sun, 01 February 2015 09:14 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Quote:
Oracle believes that ac_jor_table contains only 1 row based upon what EXPLAIN PLAN shows.
No. Oracle believes that one row will be returned by the scan of ac_jor_table, after applying the filter. This is quite possible.
The problem is that you may be iterating the scan of ac_jor_table many times, in the worst case 155k times. You need to create a compound index that covers all the filter columns, and then see if you get index access.
Re: update statement takes too long [message #632494 is a reply to message #632477] Sun, 01 February 2015 21:39 Go to previous messageGo to next message
OraFerro
Messages: 433
Registered: July 2011
Senior Member
Hi John Watson,

Quote:

No. Oracle believes that one row will be returned by the scan of ac_jor_table, after applying the filter. This is quite possible.


Exactly, this was my understanding as it is an update statement and it has to return one matching row from ac_jor_table to be able to perform the update on iv_table.

My question is:
1- If the select statement (with the same filter) does not take time to run (actually it runs fast as illustrated above) then why does the update take that long?
2- I can add the index to enhance the update speed but I am afraid this would affect the performance of other operations on the table. I was seeking advice on how to re-write or enhance the update statement itself (or of course a clear problem in configuration...etc. that can be concluded from the trace).

Thanks,
Ferro
Re: update statement takes too long [message #632504 is a reply to message #632494] Mon, 02 February 2015 01:57 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
Try rewriting as a merge, I usually have better luck getting good results out of that with unindexed updates.
Re: update statement takes too long [message #632505 is a reply to message #632494] Mon, 02 February 2015 02:08 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Received as a PM:Quote:
Hi John,

Can you please have a look on the questions I added in reply to your last post? I really need help in this situation as I have to wait another day at least to be able to run the trace and provide results.

Thanks,
Ferro
Are you asking for consultancy services? I can ask my boss to send you a quotation.
Re: update statement takes too long [message #632515 is a reply to message #632505] Mon, 02 February 2015 05:19 Go to previous messageGo to next message
OraFerro
Messages: 433
Registered: July 2011
Senior Member
Hi John,
I was just asking you to follow the post and provide your feedback in case you are interested.

Greet me your boss Smile
Re: update statement takes too long [message #632623 is a reply to message #632515] Tue, 03 February 2015 13:11 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
Let us stop and think a little about what might happen in an update and where the costs could come from.

UPDATE iv_table CPA SET CPA.FK_JOURNAL_ENTRY =
        (
          SELECT JOR.JOURNAL_SERIAL FROM ac_jor_table JOR
          WHERE CPA.fk_v_date = JOR.fk_v_date
          AND CPA.fk_v_ser = JOR.fk_v_ser
          AND JOR.fk_maj_no = 1
          AND JOR.fk_act =4--
          and sign(cpa.AMOUNT) = sign(jor.AMOUNT)
        )
        WHERE CPA.TYPE_ID = 1;


Here are some thoughts.

1. first you need to find the rows you want to update (WHERE CPA.TYPE_ID = 1).  Is this being done efficiently?  If it is < 2% of the rows the use an index, otherwise you likely should be doing a table scan.

2. then you need to get the value to update with

        (
          SELECT JOR.JOURNAL_SERIAL FROM ac_jor_table JOR
          WHERE CPA.fk_v_date = JOR.fk_v_date
          AND CPA.fk_v_ser = JOR.fk_v_ser
          AND JOR.fk_maj_no = 1
          AND JOR.fk_act =4--
          and sign(cpa.AMOUNT) = sign(jor.AMOUNT)
        )

Is this being done efficiently?  Optimizing this query depends upon how many rows you find in the main query, and also what indexing exists.  You either need to treat this query as a precision query or a warehouse query (and this will partly depend upon how man rows you update).  If you are treating it as a precision query then indexing is important and you will need the right indexing to generate the right query plan for this sub-query.  this query is simple so I would suggest the following index on JOR (fk_maj_no,fk_act,fk_v_ser,vk_v_date,sign(amount),journal_serial).  This gives you a covering index that also exploits the maximum amount of ACCESS possible.  Read up on covering indexes and function based indexes if you want to know more.

As an aside, this is one of those places where Oracle's default query results cache could make a big difference.  It would keep results of the sub-query lookups so that duplicate lookups don't need to happen more than once (up to some limit anyway).  This works regardless of if you are using indexes or not, but is dependent upon the order in which value combinations come off the main query.  But you can't control it really so it is just an interesting piece of information that you can't do much with here.

3. then there is logging for the update.  this however is system tuning so you maybe not easily handled in this post.

4. then there is consistent read costs.  Each time you update a row, the block that rows sits on changes.  If your update was reading the same table you were updating, you could incur lots of consistent gets as the update creates more blocks that need to be "reconstructed".  But that is not the case here so we can ignore this cost for this update.


Please forward some additional info:

1. how many rows are you planning on updating?
2. how long does the sub-query take to run?
3. how long does the update take to run?
4. do you have the suggested index or not?


Kevin
Re: update statement takes too long [message #632803 is a reply to message #632504] Thu, 05 February 2015 18:55 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Roachcoach wrote on Mon, 02 February 2015 18:57
Try rewriting as a merge, I usually have better luck getting good results out of that with unindexed updates.

+1

The problem with that type of UPDATE statement is that it MUST nest. ie. It re-runs the sub-query for EVERY matching row in the updated table. This is fine if there are not many rows, but not so good if there are lots. A MERGE on the other hand can run the UPDATE as a proper join, which can be optimised for higher volumes.

**Untested code**

MERGE INTO (SELECT * FROM iv_table WHERE TYPE_ID = 1) cpa
USING (
    SELECT fk_v_date, JOR.fk_v_ser, jor.AMOUNT,  JOR.JOURNAL_SERIAL 
    FROM ac_jor_table JOR
    WHERE JOR.fk_maj_no = 1
    AND JOR.fk_act = 4
) JOR
ON  CPA.fk_v_date = JOR.fk_v_date
AND CPA.fk_v_ser = JOR.fk_v_ser
AND sign(cpa.AMOUNT) = sign(jor.AMOUNT)
WHEN MATCHED THEN SET CPA.FK_JOURNAL_ENTRY = JOR.JOURNAL_SERIAL


You may need to make some tweaks to handle:
- Cases where the output from the USING clause is many-to-one,
- Cases where there is no matching row in the USING clause.

I leave those as an exercise for you.

Ross Leishman
Re: update statement takes too long [message #632804 is a reply to message #632803] Thu, 05 February 2015 21:09 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
You guys have explained it way better then I did.

To understand what Ross and Roachcoach were saying, consider the differences in query plans between the two alternative updates (update vs. merge). In some sense, everything is a join. That goes for updates too since you have to pair together two rows in order to update one of them. With that in mind, note that the simple update is (though it does not explicitly show it) doing a NESTED LOOPS JOIN from outer table to inner table, whereas the merge code is doing a HASH JOIN in order to pair up rows. For lots of rows, the hash join will be more efficient. In my testing I updated stats of both tables to make them look like they each had 1 million rows (with no indexes or constraints on either).

TECHNICALLY HOWEVER: one must keep in mind that MERGE and UPDATE are not identical in behavior when it comes to missing matched rows. This update and the merge provided by Ross will not do the same thing when there is no match on AC_JOR_TABLE. The update will set FK_JOURNAL_ENTRY=null whereas the merge will simply skip the row in IV_TABLE and leave FK_JOURNAL_ENTRY alone. This is part of what Ross was pointing out when he noted there were details you (OP) would have to account for. In some cases (as seen here in fact?), there is no variation of MERGE that will do what the UPDATE does. Thus you need to know your data and your constraint rules before you decide to substitute merge for update. It is not just about performance. Unless an update is guaranteed to have a match for every row it seeks to update, a merge cannot be substituted for an update, and in the case of this update, there is no constraint setup between these two tables that would enforce this requirement. Therefore, replacing this update with a merge is invalid and can lead to incorrect (or at least different (one has to question if the update is actually correct to set its target to null)) results.

However to the however, I would expect that under the right circumstances, Oracle would internally convert the query plan for the update to a HASH JOIN type of plan since they are all equivalent if certain restrictions are applied which means eventually we would want to write the update and let oracle do more magic. But I cannot prove with my installed versions that it will do this today.

Boy this stuff can get deep...

21:36:46 SQL> explain plan for UPDATE iv_table CPA SET CPA.FK_JOURNAL_ENTRY =
21:37:12   2          (
21:37:12   3            SELECT JOR.JOURNAL_SERIAL FROM ac_jor_table JOR
21:37:12   4            WHERE CPA.fk_v_date = JOR.fk_v_date
21:37:12   5            AND CPA.fk_v_ser = JOR.fk_v_ser
21:37:12   6            AND JOR.fk_maj_no = 1
21:37:12   7            AND JOR.fk_act =4--
21:37:12   8            and sign(cpa.AMOUNT) = sign(jor.AMOUNT)
21:37:12   9          )
21:37:12  10  --        WHERE CPA.TYPE_ID = 1
21:37:12  11  /

Explained.

Elapsed: 00:00:00.00
21:37:12 SQL>
21:37:12 SQL> @showplan11gshort

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------
Plan hash value: 2536411157

-----------------------------------------------------------------------------------
| Id  | Operation          | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT   |              |  1000K|    45M|    45  (36)| 00:00:01 |
|   1 |  UPDATE            | IV_TABLE     |       |       |            |          |
|   2 |   TABLE ACCESS FULL| IV_TABLE     |  1000K|    45M|    45  (36)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| AC_JOR_TABLE |     1 |    74 |    50  (42)| 00:00:01 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("JOR"."FK_V_DATE"=:B1 AND "JOR"."FK_V_SER"=:B2 AND
              "JOR"."FK_MAJ_NO"=1 AND "JOR"."FK_ACT"=4 AND
              SIGN("JOR"."AMOUNT")=SIGN(:B3))

17 rows selected.

Elapsed: 00:00:00.04
21:37:12 SQL> explain plan for MERGE INTO (SELECT * FROM iv_table WHERE TYPE_ID = 1) cpa
21:37:25   2  USING (
21:37:25   3      SELECT fk_v_date, JOR.fk_v_ser, jor.AMOUNT,  JOR.JOURNAL_SERIAL
21:37:25   4      FROM ac_jor_table JOR
21:37:25   5      WHERE JOR.fk_maj_no = 1
21:37:25   6      AND JOR.fk_act = 4
21:37:25   7  ) JOR
21:37:25   8  ON (
21:37:25   9      CPA.fk_v_date = JOR.fk_v_date
21:37:25  10  AND CPA.fk_v_ser = JOR.fk_v_ser
21:37:25  11  AND sign(cpa.AMOUNT) = sign(jor.AMOUNT)
21:37:25  12     )
21:37:25  13  WHEN MATCHED THEN UPDATE SET CPA.FK_JOURNAL_ENTRY = JOR.JOURNAL_SERIAL
21:37:25  14  /

Explained.

Elapsed: 00:00:00.01
21:37:25 SQL>
21:37:25 SQL> @showplan11gshort

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------
Plan hash value: 3488386518

-------------------------------------------------------------------------------------
| Id  | Operation            | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | MERGE STATEMENT      |              |     1 |    39 |    97  (41)| 00:00:02 |
|   1 |  MERGE               | IV_TABLE     |       |       |            |          |
|   2 |   VIEW               |              |       |       |            |          |
|*  3 |    HASH JOIN         |              |     1 |   147 |    97  (41)| 00:00:02 |
|*  4 |     TABLE ACCESS FULL| AC_JOR_TABLE |   100 |  7400 |    49  (41)| 00:00:01 |
|*  5 |     TABLE ACCESS FULL| IV_TABLE     | 10000 |   712K|    47  (39)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("IV_TABLE"."FK_V_DATE"="FK_V_DATE" AND
              "IV_TABLE"."FK_V_SER"="JOR"."FK_V_SER" AND
              SIGN("IV_TABLE"."AMOUNT")=SIGN("JOR"."AMOUNT"))
   4 - filter("JOR"."FK_MAJ_NO"=1 AND "JOR"."FK_ACT"=4)
   5 - filter("TYPE_ID"=1)

21 rows selected.

Elapsed: 00:00:00.04


And this is when there are no indexes.

Add some constraints between tables or at least indexes on some of the columns and you could end up with a merge that does this.

22:07:28 SQL> @showplan11gshort

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 1151325015

-----------------------------------------------------------------------------------------------
| Id  | Operation                      | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | MERGE STATEMENT                |              |     1 |    39 |    48  (40)| 00:00:01 |
|   1 |  MERGE                         | IV_TABLE     |       |       |            |          |
|   2 |   VIEW                         |              |       |       |            |          |
|   3 |    NESTED LOOPS                |              |     1 |   147 |    48  (40)| 00:00:01 |
|*  4 |     TABLE ACCESS FULL          | IV_TABLE     | 10000 |   712K|    47  (39)| 00:00:01 |
|*  5 |     TABLE ACCESS BY INDEX ROWID| AC_JOR_TABLE |     1 |    74 |     0   (0)| 00:00:01 |
|*  6 |      INDEX UNIQUE SCAN         | SYS_C0015204 |     1 |       |     0   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter("TYPE_ID"=1)
   5 - filter("JOR"."FK_MAJ_NO"=1 AND "JOR"."FK_ACT"=4 AND
              SIGN("IV_TABLE"."AMOUNT")=SIGN("JOR"."AMOUNT"))
   6 - access("IV_TABLE"."FK_V_DATE"="FK_V_DATE" AND
              "IV_TABLE"."FK_V_SER"="JOR"."FK_V_SER")

22 rows selected.

Elapsed: 00:00:00.03

Which just puts you back where you started, a NESTED LOOPS JOIN from outer to inner. Fun stuff eh?

This of course takes us back to what I said earlier, everything is a join. You can readily see this in the two variations of the MERGE query plans. In order to update table IV_TABLE you have to pair together a row from IV_TABLE with a row from AC_JOR_TABLE. That means a join which is almost always a choice between HASH JOIN or a NESTED LOOPS JOIN, and Oracle will decide which is best based on its CARDINALITY ESTIMATES and available access methods, since joins are semantically equivalent. This in turn supports the assertion that an UPDATE can use HASH JOIN too, we just don't have a test case in front of us to show it.

Wait, found one.

Following the example from the link one needs to consider this variation of the update which is identical to the merge in functionality:

22:37:16 SQL> explain plan for UPDATE iv_table CPA SET CPA.FK_JOURNAL_ENTRY =
22:37:25   2          (
22:37:25   3            SELECT JOR.JOURNAL_SERIAL FROM ac_jor_table JOR
22:37:25   4            WHERE CPA.fk_v_date = JOR.fk_v_date
22:37:25   5            AND CPA.fk_v_ser = JOR.fk_v_ser
22:37:25   6            AND JOR.fk_maj_no = 1
22:37:25   7            AND JOR.fk_act =4--
22:37:25   8            and sign(cpa.AMOUNT) = sign(jor.AMOUNT)
22:37:25   9          )
22:37:25  10  WHERE CPA.TYPE_ID = 1
22:37:25  11  and exists
22:37:25  12          (
22:37:25  13            SELECT JOR.JOURNAL_SERIAL FROM ac_jor_table JOR
22:37:25  14            WHERE CPA.fk_v_date = JOR.fk_v_date
22:37:25  15            AND CPA.fk_v_ser = JOR.fk_v_ser
22:37:25  16            AND JOR.fk_maj_no = 1
22:37:25  17            AND JOR.fk_act =4--
22:37:25  18            and sign(cpa.AMOUNT) = sign(jor.AMOUNT)
22:37:25  19          )
22:37:25  20  /

Explained.

Elapsed: 00:00:00.01
22:37:25 SQL>
22:37:25 SQL> @showplan11gshort

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------
Plan hash value: 3336212106

--------------------------------------------------------------------------------------
| Id  | Operation             | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT      |              |     1 |   122 |   100  (42)| 00:00:02 |
|   1 |  UPDATE               | IV_TABLE     |       |       |            |          |
|*  2 |   HASH JOIN RIGHT SEMI|              |     1 |   122 |   100  (42)| 00:00:02 |
|*  3 |    TABLE ACCESS FULL  | AC_JOR_TABLE |   100 |  6100 |    50  (42)| 00:00:01 |
|*  4 |    TABLE ACCESS FULL  | IV_TABLE     | 10000 |   595K|    49  (41)| 00:00:01 |
|*  5 |   TABLE ACCESS FULL   | AC_JOR_TABLE |     1 |    74 |    50  (42)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("CPA"."FK_V_DATE"="JOR"."FK_V_DATE" AND
              "CPA"."FK_V_SER"="JOR"."FK_V_SER" AND SIGN("CPA"."AMOUNT")=SIGN("JOR"."AMOUNT"
              ))
   3 - filter("JOR"."FK_MAJ_NO"=1 AND "JOR"."FK_ACT"=4)
   4 - filter("CPA"."TYPE_ID"=1)
   5 - filter("JOR"."FK_V_DATE"=:B1 AND "JOR"."FK_V_SER"=:B2 AND
              "JOR"."FK_MAJ_NO"=1 AND "JOR"."FK_ACT"=4 AND SIGN("JOR"."AMOUNT")=SIGN(:B3))

23 rows selected.


From an optimization perspective, predicate line #5 is the most interesting since the row it seeks to find is the one it has just joined to (if any) and hence means there is no actual need to do this steps even though it is shown in the query plan. I wonder if Oracle knows this?, in which case we have achieved are result of using HASH JOIN without additional work. Indeed, this might even be a case where some kind of sub-query caching at the statement level (which oracle has been doing for years) would negate the cost of predicate #5 even if it is done. How can we test this?

Then again, I am still not satisfied since if a HASH JOIN RIGHT SEMI will do the job of the original update, why did Oracle not use it in the first place on the original update? Maybe stats?

So this is all cool stuff and suggests that eventually where we want to be is

1. have a fully defined data model so that constraints describe the data
2. have properly coded SQL
3. then let Oracle do the driving as it will decide what access and join strategies are best

Kevin

[Updated on: Thu, 05 February 2015 21:46]

Report message to a moderator

Re: update statement takes too long [message #632916 is a reply to message #632804] Fri, 06 February 2015 20:47 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Kevin Meade wrote on Fri, 06 February 2015 14:09
Unless an update is guaranteed to have a match for every row it seeks to update, a merge cannot be substituted for an update, and in the case of this update, there is no constraint setup between these two tables that would enforce this requirement. Therefore, replacing this update with a merge is invalid and can lead to incorrect (or at least different (one has to question if the update is actually correct to set its target to null)) results.


Once again, untested code. But I think it should work. The OP would still need to address the possibility of duplicate rows on the join key in ac_jor_table.
MERGE INTO iv_table  cpa
USING (
    SELECT iv.ROWID AS ROW_ID,  JOR.JOURNAL_SERIAL 
    FROM iv_table iv 
    LEFT JOIN ac_jor_table JOR 
        ON  iv.fk_v_date = JOR.fk_v_date
       AND  iv.fk_v_ser = JOR.fk_v_ser
       AND sign(iv.AMOUNT) = sign(jor.AMOUNT)
    WHERE JOR.fk_maj_no = 1
    AND JOR.fk_act = 4
    AND iv.TYPE_ID = 1
) NEW
ON  CPA.ROWID = NEW.ROW_ID
WHEN MATCHED THEN SET CPA.FK_JOURNAL_ENTRY = JOR.JOURNAL_SERIAL


Kevin Meade wrote on Fri, 06 February 2015 14:09

22:37:16 SQL> explain plan for UPDATE iv_table CPA SET CPA.FK_JOURNAL_ENTRY =
22:37:25   2          (
22:37:25   3            SELECT JOR.JOURNAL_SERIAL FROM ac_jor_table JOR
22:37:25   4            WHERE CPA.fk_v_date = JOR.fk_v_date
22:37:25   5            AND CPA.fk_v_ser = JOR.fk_v_ser
22:37:25   6            AND JOR.fk_maj_no = 1
22:37:25   7            AND JOR.fk_act =4--
22:37:25   8            and sign(cpa.AMOUNT) = sign(jor.AMOUNT)
22:37:25   9          )
22:37:25  10  WHERE CPA.TYPE_ID = 1
22:37:25  11  and exists
22:37:25  12          (
22:37:25  13            SELECT JOR.JOURNAL_SERIAL FROM ac_jor_table JOR
22:37:25  14            WHERE CPA.fk_v_date = JOR.fk_v_date
22:37:25  15            AND CPA.fk_v_ser = JOR.fk_v_ser
22:37:25  16            AND JOR.fk_maj_no = 1
22:37:25  17            AND JOR.fk_act =4--
22:37:25  18            and sign(cpa.AMOUNT) = sign(jor.AMOUNT)
22:37:25  19          )
22:37:25  20  /

Explained.

Elapsed: 00:00:00.01
22:37:25 SQL>
22:37:25 SQL> @showplan11gshort

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------
Plan hash value: 3336212106

--------------------------------------------------------------------------------------
| Id  | Operation             | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT      |              |     1 |   122 |   100  (42)| 00:00:02 |
|   1 |  UPDATE               | IV_TABLE     |       |       |            |          |
|*  2 |   HASH JOIN RIGHT SEMI|              |     1 |   122 |   100  (42)| 00:00:02 |
|*  3 |    TABLE ACCESS FULL  | AC_JOR_TABLE |   100 |  6100 |    50  (42)| 00:00:01 |
|*  4 |    TABLE ACCESS FULL  | IV_TABLE     | 10000 |   595K|    49  (41)| 00:00:01 |
|*  5 |   TABLE ACCESS FULL   | AC_JOR_TABLE |     1 |    74 |    50  (42)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("CPA"."FK_V_DATE"="JOR"."FK_V_DATE" AND
              "CPA"."FK_V_SER"="JOR"."FK_V_SER" AND SIGN("CPA"."AMOUNT")=SIGN("JOR"."AMOUNT"
              ))
   3 - filter("JOR"."FK_MAJ_NO"=1 AND "JOR"."FK_ACT"=4)
   4 - filter("CPA"."TYPE_ID"=1)
   5 - filter("JOR"."FK_V_DATE"=:B1 AND "JOR"."FK_V_SER"=:B2 AND
              "JOR"."FK_MAJ_NO"=1 AND "JOR"."FK_ACT"=4 AND SIGN("JOR"."AMOUNT")=SIGN(:B3))

23 rows selected.



I believe that this plan is hash joining at steps 3 and 4, but nesting to Line 5. Since Line 5 is a FULL scan, it would be pretty easy to test (though I am not going to) and demonstrate the results with TKPROF. In my experience, UPDATE SET will ALWAYS nest. I would be delighted to be proven wrong, though it would also mean I need to go back and edit some of my old articles.
Re: update statement takes too long [message #632929 is a reply to message #632916] Sat, 07 February 2015 09:20 Go to previous message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
NO I am pretty sure you are right Ross. The documentation suggests that the hash join is reserved for the CORRECLATED SUBQUERY and its variations. I was merely pointing out two possibilities:

1. it can always change in the future. There is no real reason why the update could not use hash join too.
2. these are duplicate operations and so there may be opportunity internally for query execution to skip the second one using sub-query caching results.

As you point out, neither of these have I tested. It was fun to explore possibilities.

Kevin
Previous Topic: what is ** TRANSIENT: 12029568
Next Topic: Oracle Database 11g 11.2.0.4.0 running slow
Goto Forum:
  


Current Time: Thu Mar 28 16:42:07 CDT 2024