OraFAQ Forum: Performance Tuning » update statement takes too long

Home » RDBMS Server » Performance Tuning » update statement takes too long (11.2.0.1.0)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

update statement takes too long [message #632462]

Sun, 01 February 2015 06:19

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]