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

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
0
tanhnhi
Asked:
tanhnhi
  • 5
  • 5
  • 2
  • +1
3 Solutions
 
Guy Hengel [angelIII / a3]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
 
sujith80Commented:
Are the statistics uptodate? ( Analyze table)
How many rows are updated from the total set of records?
0
 
YANN0SCommented:
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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
 
sujith80Commented:
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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