Home » RDBMS Server » Performance Tuning » Update Statement - Performance Tuning
Update Statement - Performance Tuning [message #259077] Tue, 14 August 2007 06:21 Go to next message
mymot
Messages: 225
Registered: July 2005
Senior Member
Hi,

Below sql took 4 hours to update records.

TMP_HISTORY = 2010030 (records)
HISTORY = 6200187 (records)

2 indexes on HISTORY table : I_ID varchar2(10)
e_dte date

Platform : Unix, Sun solaris, 5.8
Oracle 9i -9.2.0.6


UPDATE ( 
  SELECT ORDERED PARALLEL(t 4)
      t.I_ID     t_I_ID 
    , t.s_dte    t_s_dte 
    , t.e_dte    t_end_dte 
    , s.I_ID     S_I_ID 
    , s.s_dte    t_s_dte 
    , TO_DATE(DECODE(r_cde,'U','&PREV_day_DTE','&SYS_DTE'), 
                    'YYYYMMDD') e_dte 
  FROM 
       TMP_HISTORY s, 
       HISTORY t
  WHERE 
        s.I_ID = t.I_ID 
  AND   t.e_dte = TO_DATE('20091231','YYYYMMDD') 
  AND   s.r_cde IN ('U','D') 
  ) tp
  SET 
      t_end_dte = e_dte




Any suggestions to improve performance of above SQL.

Thanks

[Updated on: Tue, 14 August 2007 06:22]

Report message to a moderator

Re: Update Statement - Performance Tuning [message #259086 is a reply to message #259077] Tue, 14 August 2007 06:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, read the sticky and post the relevant information.

Regards
Michel
Re: Update Statement - Performance Tuning [message #259096 is a reply to message #259086] Tue, 14 August 2007 06:50 Go to previous messageGo to next message
mymot
Messages: 225
Registered: July 2005
Senior Member
Can you be very specific.

Sticky bit gives guidelines/reference.

Thanks
Re: Update Statement - Performance Tuning [message #259117 is a reply to message #259096] Tue, 14 August 2007 08:02 Go to previous messageGo to next message
cbruhn2
Messages: 41
Registered: January 2007
Member
Hi Mymot

Couldn't you try to run the statement at least with set autot trace exp, so we at leas could see the execution plan.
That might even give yourself an idea about how to tune the statement
15:00:15 SQL> set autot trace exp
15:00:23 SQL> update a set navn = 'lars' ;

1 row updated.

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 3279622862

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | UPDATE STATEMENT   |      |     1 |     4 |     3   (0)| 00:00:01 |
|   1 |  UPDATE            | A    |       |       |            |          |
|   2 |   TABLE ACCESS FULL| A    |     1 |     4 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

15:00:36 SQL>


best regards
Carl Bruhn
Re: Update Statement - Performance Tuning [message #259121 is a reply to message #259096] Tue, 14 August 2007 08:13 Go to previous message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Quote:
SELECT ORDERED PARALLEL(t 4)
I think if you are giving a hint to the optimiser it should be enclosed within "/*+ */". How many records you expect to be updated? Are the stats updated? Did you see the plan and other information like consistent gets, physical reads, latches etc. by doing tkprof on the trace file. Hopefully the trace file should give you more information why your update is taking so long.

Good luck

Regards

Raj
Previous Topic: how to find out the I/O and CPU bottleneks
Next Topic: v$sga_target_advice view
Goto Forum:
  


Current Time: Tue Dec 06 08:10:49 CST 2016

Total time taken to generate the page: 0.10077 seconds