Home » RDBMS Server » Performance Tuning » Update statement query tuning (9i)
Update statement query tuning [message #436117] Mon, 21 December 2009 11:30 Go to next message
shiva_jm
Messages: 9
Registered: November 2009
Junior Member
I have an update statement which updates more than a million records and have to CAST the <<WHERE CLAUSE>> to match the records (since one table has a DATE field, other table has a TIMESTAMP field). Any help on how to make the update run faster ?




       execute immediate  ' UPDATE ODS_STAGE.CMC_BLSB_SB_DETAIL a ' ||
			  ' SET (a.LAST_MOD_DTTM,a.LAST_MOD_USER_ID)=' ||
			  ' ( SELECT b.LAST_MOD_DTTM,b.LAST_MOD_USER_ID from ODS_STAGE.STOO_CMC_BLSB_SB_DETAIL b WHERE ' ||
			    ' a.BLEI_CK  =  b.BLEI_CK ' || 
  ' and TRUNC(a.BLBL_DUE_DT)  =  TRUNC(b.BLBL_DUE_DT) ' || 
  ' and a.CSPI_ID  =  b.CSPI_ID ' || 
  ' and a.PDPD_ID  =  b.PDPD_ID ' || 
  ' and a.PDBL_ID  =  b.PDBL_ID ' || 
  ' and TRUNC(a.BLSB_COV_DUE_DT)  =  TRUNC(b.BLSB_COV_DUE_DT) ' || 
  ' and TRUNC(a.BLSB_COV_FROM_DT)  =  TRUNC(b.BLSB_COV_FROM_DT) ' || 
  ' and a.BLSB_PREM_TYPE  =  b.BLSB_PREM_TYPE ' || 
  ' and a.SBSB_CK  =  b.SBSB_CK ' || 
  ' and a.MEME_CK  =  b.MEME_CK ' || 
  ' and TRUNC(a.BLSB_CREATE_DTM)  =  TRUNC(b.BLSB_CREATE_DTM) ' || 



' ) ';

Thank you
Re: Update statement query tuning [message #436118 is a reply to message #436117] Mon, 21 December 2009 11:32 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First don't use "execute immediate" when all things are static.

Then, read http://www.orafaq.com/forum/mv/msg/84315/433888/102589/#msg_433888 and post accordingly.

Regards
Michel
Re: Update statement query tuning [message #436120 is a reply to message #436117] Mon, 21 December 2009 11:34 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Why you using dyanamic sql when you can do this in a single update stament?

Moreover, INDEX is not used when you use a function over the column.

Please read the sticky link as provided by Michel Sir..


Regards,
Ved


Edit: Please read the sticky link as provided by Michel Sir..

[Updated on: Mon, 21 December 2009 11:36]

Report message to a moderator

Re: Update statement query tuning [message #436121 is a reply to message #436117] Mon, 21 December 2009 11:37 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
>Any help on how to make the update run faster ?

http://www.orafaq.com/forum/m/433888/136107/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) DDL for all tables & indexes
2) EXPLAIN PLAN
3) output from SQL_TRACE & tkprof
Re: Update statement query tuning [message #436125 is a reply to message #436117] Mon, 21 December 2009 11:52 Go to previous messageGo to next message
shiva_jm
Messages: 9
Registered: November 2009
Junior Member
Why you using dyanamic sql when you can do this in a single update stament?

Its auto-generated code (generated for more than 1000 tables)....had to run some other statements running before and after for logging time.

Example :
set serveroutput on;
DECLARE
   starting_time                                TIMESTAMP;
   ending_time                                  TIMESTAMP;
   log_date                                     DATE;
   sequence_number                              INTEGER;
   table_name                                   VARCHAR2(40);
   table_desc                                   VARCHAR2(40);
   status                                       VARCHAR2(1);

BEGIN
       DBMS_OUTPUT.put_line('Started creating table CMC_BLSB_SB_DETAIL');
       starting_time := SYSTIMESTAMP;
       ending_time := SYSTIMESTAMP;
       log_date      := SYSDATE;
       DBMS_OUTPUT.put_line('The starting time is: ' || starting_time);
       table_name := 'CMC_BLSB_SB_DETAIL';
       table_desc := 'UPDATE LASTMOD TABLES';
       status     := 'P';  

       execute immediate  'SELECT REP_LOG_SEQ.NEXTVAL FROM DUAL' into sequence_number;
       
       
       commit;

       execute immediate  ' INSERT INTO ODS_STAGE.REP_LOG ' || 
                          ' (SEQ_NO,TABLE_NAME,SCRIPT_DESC,START_DTM,END_DTM,LOG_DT,STATUS) VALUES ' ||  
                          ' (:1, :2, :3, :4, :5, :6, :7)'
                          using sequence_number, table_name, table_desc, starting_time, ending_time, log_date, status;
       commit;
       
       execute immediate  ' UPDATE ODS_STAGE.CMC_BLSB_SB_DETAIL a ' ||
			  ' SET (a.LAST_MOD_DTTM,a.LAST_MOD_USER_ID)=' ||
			  ' ( SELECT b.LAST_MOD_DTTM,b.LAST_MOD_USER_ID from ODS_STAGE.STOO_CMC_BLSB_SB_DETAIL b WHERE ' ||
			    ' a.BLEI_CK  =  b.BLEI_CK ' || 
  ' and TRUNC(a.BLBL_DUE_DT)  =  TRUNC(b.BLBL_DUE_DT) ' || 
  ' and a.CSPI_ID  =  b.CSPI_ID ' || 
  ' and a.PDPD_ID  =  b.PDPD_ID ' || 
  ' and a.PDBL_ID  =  b.PDBL_ID ' || 
  ' and TRUNC(a.BLSB_COV_DUE_DT)  =  TRUNC(b.BLSB_COV_DUE_DT) ' || 
  ' and TRUNC(a.BLSB_COV_FROM_DT)  =  TRUNC(b.BLSB_COV_FROM_DT) ' || 
  ' and a.BLSB_PREM_TYPE  =  b.BLSB_PREM_TYPE ' || 
  ' and a.SBSB_CK  =  b.SBSB_CK ' || 
  ' and a.MEME_CK  =  b.MEME_CK ' || 
  ' and TRUNC(a.BLSB_CREATE_DTM)  =  TRUNC(b.BLSB_CREATE_DTM) ' || 

			  ' ) ';

       commit; 
       
       status     := 'C'; 
       ending_time := SYSTIMESTAMP;
       execute immediate  ' UPDATE ODS_STAGE.REP_LOG  SET STATUS = :1, END_DTM = :2 WHERE SEQ_NO = :3 ' 
                            USING  status, ending_time, sequence_number;
                            
       commit;  

       DBMS_OUTPUT.put_line('The ending time is: ' || ending_time);
       DBMS_OUTPUT.put_line('Completed creating table CMC_BLSB_SB_DETAIL');

END;
/
Re: Update statement query tuning [message #436126 is a reply to message #436125] Mon, 21 December 2009 12:13 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Not a good idea to use dynamic sql for this.
Re: Update statement query tuning [message #436129 is a reply to message #436125] Mon, 21 December 2009 12:39 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Why you using dyanamic sql when you can do this in a single update stament?

Its auto-generated code (generated for more than 1000 tables)....had to run some other statements running before and after for logging time.

Still no reason for dynamic SQL in the code you posted.
Still not posting the requested information.

Regards
Michel

Re: Update statement query tuning [message #436203 is a reply to message #436129] Tue, 22 December 2009 01:49 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Dynamic SQL isn't your problem. Sure it's hard for us to read, but it's only being run once to update a million rows - the possible extra parse time for a single run is the least of your problems.

Your real problem is NESTED SQL. See this article for a detailed explanation and an appropriate fix.

Ross Leishman
Previous Topic: Memory Notification: Library Cache Object loaded into SGA
Next Topic: oracle instance performance
Goto Forum:
  


Current Time: Wed Sep 28 09:14:36 CDT 2016

Total time taken to generate the page: 0.13394 seconds