• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 501
  • Last Modified:

URGENT: Bad performance

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
pfjluik
Asked:
pfjluik
  • 5
  • 3
  • 2
  • +6
1 Solution
 
noriegaaCommented:
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
 
pfjluikAuthor Commented:
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
 
chdbaCommented:


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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
HaukeCommented:
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
 
misho2000Commented:
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
 
pfjluikAuthor Commented:
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
 
misho2000Commented:
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
 
pfjluikAuthor Commented:
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
 
myerpzCommented:
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
 
jbeckstromCommented:
Is your where clause for the update using a good access path.
0
 
noriegaaCommented:
Can you post a macro algorithm of the process.  The I can help.
0
 
alx512Commented:
I think the index has a small selectivity.
Can you describe your table and indexes?
0
 
alx512Commented:
I think the index has a small selectivity.
Can you describe your table and indexes?
0
 
JeanaMCommented:
Are you using a cost-based optimizer?  If so, you could analyze your tables before you run your update.
0
 
JeanaMCommented:
Are you using a cost-based optimizer?  If so, you could analyze your tables before you run your update.
0
 
noriegaaCommented:
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
 
pfjluikAuthor Commented:
How can I devide the point into 3 parts?
0
 
pfjluikAuthor Commented:
How can I devide the point into 3 parts?
0
 
myerpzCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 3
  • 2
  • +6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now