Solved

URGENT: Bad performance

Posted on 2001-07-01
19
465 Views
Last Modified: 2007-11-27
Hi,

When executing a batch on a customer db, inserting and updating about 50.000 records, it takes forever to complete.

This I think, because after each record the indexes are updated. Is there a way to prevent an index from updating untill the batch completes?

Are there any other solutions to get a better performance?
0
Comment
Question by:pfjluik
  • 5
  • 3
  • 2
  • +6
19 Comments
 
LVL 2

Expert Comment

by:noriegaa
ID: 6243852
You need to provide some further input.  If your batch job is inserting and updating 50,000 records (each time ?), you probably need to adjust the number of records committed at once.  If there are index columns  being updated, and these are non-critical for the cursors, can you drop the index and rebuild it after processing ?  If they involve foreign or primary keys, then my only suggestion is to explicitly set a hint with the index name on the column involved.  Try to tune your insert/update cursors, otherwise.  

Sometimes the size of your rollback segment is a factor.  I recommend to set up a large rollback segment and start your batch process with:

set transaction use rollback segment <rbs_name>;
Try to leave on-line only the rollback segment(s) involved.  This could be a great factor.  Monitor rbs closely.

If any of your cursors in the batch process uses a sort (order by clause), then check your sort_area_size and sort_area_retained_size parameters.  Check the view for V$system_event, v$system_wait, v$session_wait and V$long_ops for further details, and monitor top sessions in OEM as well.

Another important case could be a mutating table issue, upon updating if triggers are involved, or the common message snapshot too old, which sometimes could even cause for your batch process to fail in this type of batch processes.  It happened to me a few times.  




0
 
LVL 1

Author Comment

by:pfjluik
ID: 6243881
Thanx for your comment.

The batch will insert & update +/- 50.000-250.000 records each time.

After 50 records a commit is given. Dropping the index will result in a 'never ending' batch.. It was running from friday 10:00 till this morning.. I had to cancel it when 50.361 records where updated.

Friday I've dropped the indexes, but it seems to take longer than before.

No triggers are involved.
What I really want to know is if and how I can prevent the Indexes to be updated after each record.
0
 
LVL 1

Expert Comment

by:chdba
ID: 6244104


I can't imagine, that the indexes are such a big problem. Did you run explain plan

or what I can recommend is tkprof.

Might help you to figure out, if the update is really the problem. - About the commit: we usually do every 20.000 records (sometimes bigger) a commit and it does not take that long.

I've been told by ORACLE-Support to do about 1 commit per Extent.
--> avg(rec-size)*number of records should be >= extent size

I can't believe, that this change will make such a big difference in runtime.

I don't know if you can stop Indexes to be updated after each record.
0
 
LVL 2

Expert Comment

by:Hauke
ID: 6244377
You can speed up updates using the nologgin option.

ALTER INDEX <index name> NOLOGGING;


Before inserting and updating a great amount of rows, you should drop all indexes you don't need for the updates. After all you recreate the indexes.

0
 
LVL 1

Expert Comment

by:misho2000
ID: 6244461
Possible problems with updating could be if your where clause does not include column with existing index.
In this case your update statment will do full search ( may be). With the insters the things are more clear because after commiting 50 records you will release resources for the next inserts .
Is it possible to separate insert statements from update statements in order to have more clear vision?
Also it is important that this batch procedure ( i understand it is at OS external level) should have appropriate priority level ( in most cases batch procedures have lower level that normal processes in OS ).
What is your OS enviromnet ( NT , UNIX ...)
0
 
LVL 1

Author Comment

by:pfjluik
ID: 6244498
Thank ya all for your input.
Let's make the process clear..

First a datafile (plain text) will be read en inserted into a table int_data.. After that, the application will search for the ID's in another table and update the int_data with the id's.

without any indexes on int_data the load process is 22min faster. (Still takes 1,5 hours to complete)

The update took about 56 hours?
0
 
LVL 1

Expert Comment

by:misho2000
ID: 6244523
Are you using pl/sql  procedure or simple update sql statement?
I had the same problem a years ago and i solved it implementing pl/sql procedure.
the construction which i used is cursor like this:
 
DECLARE
   CURSOR c1 IS
      SELECT ename, sal, hiredate, deptno FROM emp;
   ...
BEGIN
   FOR emp_rec IN c1 LOOP
      ...
      salary_total :=  salary_total + emp_rec.sal;
   END LOOP;
very powerful construction.

0
 
LVL 1

Author Comment

by:pfjluik
ID: 6244574
Thanx misho, but implementing this solution will require a almost complete rebuild of the application.

I will propose this to our customer, but I'm sure it's not an option.
0
 
LVL 3

Expert Comment

by:myerpz
ID: 6245795
1.5 hours to load 50-250k records seems a very long time.

Apologies if I am asking the obvious, but are you using the "direct load" option in SQL*Loader ?
Or perhaps you would like to, but are doing some SQL operations inside the control file, which precludes the use of the direct option. If so, can you somehow postpone the SQL processing till your update routines ?

I regularly load 250k large records into tables, and it takes 10 minutes or so with a direct SQL load.

You can have a direct load onto a table with plenty of indexes : the indexes are suspended before the load, then rebuilt afterwards. All this happens automatically. With the indexes in place, your update should hopefully then be quicker than 56 hours.

How complex is your update procedure ?

Are you using a cursor based on a "SELECT ... for update" ?

e.g. cursor c1 is select x,y,z, from t for update;

And then your update clause could specify :-

   UPDATE table_name
   SET    column1 = value1
   ...
   WHERE CURRENT of cursor_name;

This construct uses rowid to update your current row, and is the fastest way possible to update. ( you wouldn't need an index on your load table ! )

If the tables where you go looking for information in order to update "int_table" are suitably indexed, your load procedure should find the info. quicker.

If you are max-ed out on Rollback Segment space, then commit every 80k records. if that doesn't work, try 40k. Then 20, then 10 etc till you are in the clear, and stick with that.

Good luck getting the timings down.

HTH
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.

 

Expert Comment

by:jbeckstrom
ID: 6245878
Is your where clause for the update using a good access path.
0
 
LVL 2

Expert Comment

by:noriegaa
ID: 6246078
Can you post a macro algorithm of the process.  The I can help.
0
 
LVL 1

Expert Comment

by:alx512
ID: 6247391
I think the index has a small selectivity.
Can you describe your table and indexes?
0
 
LVL 1

Expert Comment

by:alx512
ID: 6247423
I think the index has a small selectivity.
Can you describe your table and indexes?
0
 

Expert Comment

by:JeanaM
ID: 6252992
Are you using a cost-based optimizer?  If so, you could analyze your tables before you run your update.
0
 

Expert Comment

by:JeanaM
ID: 6252993
Are you using a cost-based optimizer?  If so, you could analyze your tables before you run your update.
0
 
LVL 2

Accepted Solution

by:
noriegaa earned 300 total points
ID: 6254353
I have experience with this type of large, multiple transactions and the outcome of performance tuning success typically goes to:

- tuning and monitoring rbs (very important), and using the appropriate rbs for running main/larger transactions.
- tuning sql queries and cursors accordingly
- making the appropriate hints such that the optimizer to choose the best parsing
- tuning the shared pool ( very critical)
- looking at concurrent processes running over objects used.
- resolving any type of row chaining prior to a large batch process.
- validating all indexes accordingly, and possibly rebuild them if necessary.
- setting up the appropriate sort_area_size if a driving cursor is sorting the information.
- pining reusable objects, and caching look-up tables helps a lot.
- Setting autonomous_transactions where appropriate to optimize control over a work to be committed, and utilize the appropriate pragmas whenever convenient, also good development ideas. Also close any open cursor that no longer needs to be opened ( a very common neglect even among very good pl/sql developers).  This a critical cause to make even a web server crash when the server load could not be handled anymore or cause latency in large batch processes, where they more likely would break the process.
0
 
LVL 1

Author Comment

by:pfjluik
ID: 6697509
How can I devide the point into 3 parts?
0
 
LVL 1

Author Comment

by:pfjluik
ID: 6697510
How can I devide the point into 3 parts?
0
 
LVL 3

Expert Comment

by:myerpz
ID: 6698280
to divide the points into three, amend this question so its points are divided by three, and award the points to MemberA

then add two new questions with the same number of points ( i.e. one third the original ) entitled "Points for MemberB" and "Points for MemberC" and then when MemberB and MemberC add a comment to their questions, you can award them the points.

hth
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

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

758 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

19 Experts available now in Live!

Get 1:1 Help Now