Oracle UPDATE performance

I have an Oracle table "T" with 3 columns A,B and C. A & B are indexed, C's not. I excuted the following query:
UPDATE T SET C='Hello' WHERE A='a' AND B='b'
It took 30s to complete knowing that there're about 400 rows in my table. It's ridiculous! I tried to remove the indexes on columns A & B and the query completes much more faster (about 3s). Why does updating a non-indexed column take so much time? Anybody has somes ideas? Thanks a lot!
Tuan Anh
tanhnhiAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
this looks like completely wrong indeed.

now, what data type are the columns A and B?
are the statistics on the table up-to-date?
what is the execution plan with and without the index(es)?
would you try with a single index on A and B:

CREATE INDEX IDX_TABLE_T ON T ( A, B );
0
 
SujithConnect With a Mentor Data ArchitectCommented:
Are the statistics uptodate? ( Analyze table)
How many rows are updated from the total set of records?
0
 
YANN0SConnect With a Mentor Commented:
I Agree that requiring 30 secs for the update is a little bit strange, but there are several issues you need to keep in mind:
1) Did you run the update only once or many times? Because if you ran it only once, there are some random events that may occured and delayed the first update. For example it may be the case that a new extent was required for the table, so what actually took time was the allocation of the extent and not the update itself. Or maybe your session was waiting for a lock release.
2) it would be very usefull to have the execution plan with and without the index(es) as angellli suggested.
3) If you have only 400 rows in your table then full table scan will be faster for sure. 400 rows probably fit in one block. So a full table scan will require 1 I/O operation, while using an index at least 2 (1 for the index block and one for the data block)
4) "A & B are indexed". Does that mean that you have one index on A and another one on B? Or do you have a combined index on (A,B)? Are the indexes on A and B B+Tree indexes or maybe bitmap?

Anyway, the main point is (1), execute the update many times, and even in different order (1st without the indexes and then with the indexes). If you still notice similar differences then check the execution plans.



0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
tanhnhiAuthor Commented:
This is the first time that I run the "explain plan" query, and there are 400 rows in the plan_table for my update query! Is this acceptable?
0
 
SujithData ArchitectCommented:
Truncate your "PLAN_TABLE" and explain plan again.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
could it be that T is actually a view?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
do you have any triggers on the table (if T is a table?)
0
 
tanhnhiAuthor Commented:
Sorry, someone has already filled the plan table. I've re-executed explain plan with a new plan_table, and the result was 3 rows
UPDATE STATEMENT
    UPDATE SEEAPP.T
        INDEX(UNIQUE SCAN) SEEAPP.T_PK

YANN0S:T_PK is the index created on A&B
0
 
tanhnhiAuthor Commented:
angelIII:Yes, there are triggers on my table T, which update other tables when T is updated!! But I think they will be executed AFTER my UPDATE query, and not within my query.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
does that update still take 30 seconds?
what is the specs of the server you run oracle on?
how much RAM?
how big is the PGA and the SGA?
archivelog mode?
...
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>But I think they will be executed AFTER my UPDATE query, and not within my query.
wrong. they are executed as part of your query!
so you will have to cross check the updates on those other tables, eventually those are "bad".

can you post the trigger code?
0
 
YANN0SCommented:
angellli is right, triggers are executed as part of your query, but normally leaving or dropping the index should not make any difference on their overhead.

Did you try multiple executions in the reverse order? Do you get the same elapsed times?
0
 
tanhnhiAuthor Commented:
I've just disabled those triggers and reexecuted the query, there was absolutely no impact on execution time.
YANN0S:If I dropped my index, the update time is dramatically changed (from 30 down to 3s). But I can't actually drop them because my table is supposed to be much more bigger in production. I didn't try multiple executions as you've suggested, cause I don't think that random events can delay a query up to more than 30s. But I'll give it a try when there's really no other way
0
 
tanhnhiAuthor Commented:
Hi everybody,
Thanks for all your help! Finally, we've decided to drop the problem and find another direction. I'll distribute the points to all of you. Thanks again
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.

All Courses

From novice to tech pro — start learning today.