Home » RDBMS Server » Performance Tuning » update statement issue (oracle9i)
update statement issue [message #422518] Thu, 17 September 2009 01:38 Go to next message
manoj12
Messages: 185
Registered: March 2008
Location: India
Senior Member
Dear sir

This update statement is taking a lot of time to execute .I am sending you the above update statement along with explain plan of the update statemen?

The total time taken to execute this update statement is 49 seconds.But after putting index the update statement took 52 seconds.?
SQL> update intcdr.inf_prop_security ips set ips.security_to = 2
  2  where trim(ips.proposal_facility_code) in
  3  (select trim(fd.cis_facility_code) from intcdr.inf_cbs_facility_detail fd where fd.is_unrat_bor
 = 1);

184805 rows updated.


Execution Plan
----------------------------------------------------------
   0      UPDATE STATEMENT Optimizer=CHOOSE
   1    0   UPDATE OF 'INF_PROP_SECURITY'
   2    1     MERGE JOIN
   3    2       SORT (JOIN)
   4    3         TABLE ACCESS (FULL) OF 'INF_PROP_SECURITY'
   5    2       SORT (JOIN)
   6    5         VIEW OF 'VW_NSO_1'
   7    6           SORT (UNIQUE)
   8    7             TABLE ACCESS (FULL) OF 'INF_CBS_FACILITY_DETAIL'


Statistics
----------------------------------------------------------
        128  recursive calls
     189127  db block gets
       6138  consistent gets
      39274  physical reads
   43836868  redo size
        616  bytes sent via SQL*Net to client
        698  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          3  sorts (disk)
     184805  rows processed


After putting index in is_unrat_bor
update statement took 52.05 seconds

SQL> update intcdr.inf_prop_security ips set ips.security_to = 2
  2  where trim(ips.proposal_facility_code) in
  3  (select trim(fd.cis_facility_code) from intcdr.inf_cbs_facility_detail fd where fd.is_unrat_bor
 = 1);

184805 rows updated.

Elapsed: 00:00:52.05

Execution Plan
----------------------------------------------------------
   0      UPDATE STATEMENT Optimizer=CHOOSE
   1    0   UPDATE OF 'INF_PROP_SECURITY'
   2    1     MERGE JOIN
   3    2       SORT (JOIN)
   4    3         TABLE ACCESS (FULL) OF 'INF_PROP_SECURITY'
   5    2       SORT (JOIN)
   6    5         VIEW OF 'VW_NSO_1'
   7    6           SORT (UNIQUE)
   8    7             TABLE ACCESS (BY INDEX ROWID) OF 'INF_CBS_FACILI
          TY_DETAIL'

   9    8               INDEX (RANGE SCAN) OF 'IDX_INF_CBS_DETAIL' (NO
          N-UNIQUE)

Statistics
----------------------------------------------------------
        128  recursive calls
     189107  db block gets
       6377  consistent gets
      38283  physical reads
   43741832  redo size
        623  bytes sent via SQL*Net to client
        698  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          3  sorts (disk)
     184805  rows processed


Appreciate your help on the above to resolve the above issue?
Re: update statement issue [message #422578 is a reply to message #422518] Thu, 17 September 2009 08:29 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Are your tables analyzed? There aren't any costs inthose explain Plans.
Previous Topic: Use of Bind variable
Next Topic: Query Performance issue
Goto Forum:
  


Current Time: Fri Dec 09 06:13:09 CST 2016

Total time taken to generate the page: 0.09883 seconds