?
Solved

slow update

Posted on 2011-09-09
9
Medium Priority
?
332 Views
Last Modified: 2012-05-12
Dear Experts -

I've a update process takes 2 hours to update 100K rows. The table involved is partitioned and has 50mil rows on it. Is there any better way of doing this?

Thanks for your time.

~sve
WHENEVER SQLERROR EXIT SQL.SQLCODE;

SET TIMING ON;
SET ECHO ON;
SET TERMOUT ON;
SET FEEDBACK ON;
SET SERVEROUTPUT ON;

DECLARE
   L_TOTAL              NUMBER := 0;
   L_COUNT              NUMBER := 1;
   L_TIME_MAX    NUMBER := NVL(&&2,60);
   L_ROW_MAX          NUMBER := NVL(&&3,1000000);
   L_SEQUENCE           NUMBER;
   L_UPDATE_MAX       NUMBER := NVL(&&1,10000);
   L_SQL_CODE           NUMBER;
   L_SQL_MSG            VARCHAR2(200);
   L_START_TIME         DATE := SYSDATE;
   L_TIMEOUT            CHAR(1) := 'N';
BEGIN
   -- GET SEQUENCE ID
   SELECT SEQ_MDN_PROC_ID.NEXTVAL
     INTO L_SEQUENCE
     FROM DUAL;




   WHILE (L_COUNT > 0 AND L_TIMEOUT = 'N') 
   LOOP
      UPDATE LCACHE -- partition table about 50 million rows
         SET STATUS  = DECODE(RC,'PB','P','V'),
             PROC_ID = L_SEQUENCE
       WHERE EXPIRATION_DATE > TO_DATE('2019-01-01','YYYY-MM-DD') 
         AND RC in ('PB','VB')
         AND STATUS = 'D' 
         AND ROWNUM <= L_UPDATE_MAX;

      L_COUNT := SQL%ROWCOUNT;
      L_TOTAL := L_TOTAL + L_COUNT;
      

      IF ((SYSDATE - L_START_TIME)*24*60 >= L_TIME_MAX OR L_TOTAL >= L_ROW_MAX)
      THEN
         L_TIMEOUT := 'Y';
      END IF;  
   END LOOP;

   DBMS_OUTPUT.PUT_LINE ('Updated a total of ' || L_TOTAL || ' rows.');


EXCEPTION
   WHEN OTHERS
   THEN
      L_SQL_CODE := SQLCODE;
      L_SQL_MSG  := SUBSTR(SQLERRM, 1, 200);  
      

      -- RAISE EXCEPTION
      RAISE_APPLICATION_ERROR( -20001, 'SQL CODE: ' || L_SQL_CODE ||'. SQL MSG: '|| L_SQL_MSG );

END;
/

Open in new window

0
Comment
Question by:sventhan
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
9 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 1600 total points
ID: 36513099
what are you using for l_update_max   ?  10000 rows  isn't very many,  I'd use 100000 or more for a simple update.


also, what indexes and partitioning do you have?  Is it something your where clause can take advantage of?

and finally,  are you sure the update it taking a long time to do the work?  or are you waiting on other sessions to release locks so you're not really executing something the whole time
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36513108
What is the purpose of the loop?  If I read it right you can easily do the same update many times.
0
 
LVL 18

Author Comment

by:sventhan
ID: 36513121
There is a non-unique index on expiration_date. No other indexes on column involved in the where clause. The PK but its on different columns.
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 74

Expert Comment

by:sdstuber
ID: 36513167
if the only index you have is on expiration_date,  is that index helpful?

how many blocks will have the query have to read to find all of your data?

0
 
LVL 18

Author Comment

by:sventhan
ID: 36513168
Thanks experts.

@sligthtVW

You're right. We can run this many times, its a controlled update.

@SD

Its a range partition based on some range of numbers. only 10 to 15 partitions.

I'll check for locks and get back to you. Do you think adding indexes will help?

0
 
LVL 74

Expert Comment

by:sdstuber
ID: 36513186
will adding indexes help?  - you tell me,  how many rows/blocks can you narrow the result set down to?

also,  your loop doesn't have a COMMIT,  so breaking the update into chunks updating a subset of the rows each time doesn't actually help you conserve rollback/undo.  In fact, you're simply consuming more.

either add the commit on each iteration,  or remove the loop altogether and update all of the rows in a single pass
0
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 400 total points
ID: 36513188
>>You're right. We can run this many times, its a controlled update.

But you aren't committing between the loops.
0
 
LVL 18

Author Closing Comment

by:sventhan
ID: 36513713
Nice to see you both. Thanks for all the help.
0
 
LVL 18

Author Comment

by:sventhan
ID: 36513719
The problem is there is an row level trigger on this big table. I get 2mins without the trigger for 100k rows.

There was a commit in the loop and I missed it when I altered the code for posting. Sorry!
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question