Solved

slow update

Posted on 2011-09-09
9
326 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
  • 4
  • 3
  • 2
9 Comments
 
LVL 73

Accepted Solution

by:
sdstuber earned 400 total points
Comment Utility
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 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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
Comment Utility
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
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 18

Author Comment

by:sventhan
Comment Utility
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 73

Expert Comment

by:sdstuber
Comment Utility
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 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 100 total points
Comment Utility
>>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
Comment Utility
Nice to see you both. Thanks for all the help.
0
 
LVL 18

Author Comment

by:sventhan
Comment Utility
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Delphi selector screen 2 56
oracle- 10.2.04 3 42
Case Statement in SQL Delminted Query 9 55
levels for reporting 5 47
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…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

762 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now