Solved

slow update

Posted on 2011-09-09
9
327 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
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 76

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
 
LVL 73

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
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
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 73

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 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 100 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

863 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

22 Experts available now in Live!

Get 1:1 Help Now