topic Performance of UPDATE statement in Application Development and Automation Discussions

Performance of UPDATE statement

Former Member — Thu, 23 Jul 2009 22:14:36 GMT

Hello. I have the following statement running against a table with about 55 million records. It has taken close to 36 hours to run:

update /xyz/t_transaction set state = ' ' where key = 3.

commit work.

The goal is to clear all values for this column for the entire table.

The table has about 35 columns and has two indexes on date fields. The date fields are heavily searched in other programs. The STATE field is a type CHAR 20 and stores at most 5 different character values.

Transaction DB02 shows more than 300,000,000 row scans, far more than the amount of records in the table. The underlying DB is Oracle on Solaris and we have STATS running 3 times a week on the entire DB.

Can anyone suggest tips for improving performance? There are no other keys in the table other than 3. Would removing the where clause improve performance? We also thought of temporarily removing the indexes and adding them back after the program runs.

Thanks in advance,

Anthony

Re: Performance of UPDATE statement

HermannGahm — Fri, 24 Jul 2009 07:34:17 GMT

Hi Anthony,


> Transaction DB02 shows more than 300,000,000 row scans, far more than the amount of records in the table.  The underlying DB is Oracle on Solaris and we have STATS running 3 times a week on the entire DB.
>

what do you mean by "row scans" ?

What i understood from your mail...:

the table /xyz/t_transaction has 55 million records and 35 collumns.

2 indexes on date fields are defined. the field state is NOT in any index and

the field key is NOT in any index. all records have key = 3.

If the above is true the update:


update /xyz/t_transaction set state = ' ' where key = 3.

should perform a full table scan updating the state column in all rows.

The runtime of such an update directly depends on the number of blocks allocated

for that table. All blocks up to the HWM (high water mark), including empty blocks if

there are some, have to be read and updated. No indexes will be touched (key is not

indexed and state is not indexed as well).

Therefore removing the indexes will not make any difference.

Removing the where condition from the update will not change anything as well

since all columns have key 3 a full table scan should be performed in any case.

Could you post your table satistics? How much blocks are allocated for the table?

I would assume the readtime of this query roughly as

nr of blocks allocated x time for reading one block (sequential multiblock i/o) + a little bit

of update overhead per block. Logging times will come on top of that (i ignored them in

the beginning).

the time per block should not be higher than 20 - 25 milli seconds (depending on your i/o subsystem).

How often do you have to run that query? Regularly or only once?

Try to get more details what causes that run time (i/O, logging, might be enques or something else as well, ...)

On 10g use the active session history for details on 9i you have to use extende sql trace.

Kind regards,

Hermann

Edited by: Hermann Gahm on Jul 24, 2009 2:47 PM

Re: Performance of UPDATE statement

Former Member — Mon, 27 Jul 2009 19:30:54 GMT

Hi Hermann

Thanks for your reply. My table stats are below:

Tablespace = PSAPSR3

Header File = 6

Header Blocks = 331,499.00

Size (MB) = 16,225.00

Extents = 442

Blocks = 2,076,800

We only run this infrequently but we still would like to understand why it performs this way.

The table currently has 58,250,000 records. It has 55 columns.

Does this seem within range of normal processing times or can it be improved?

Regards,

Anthony

Re: Performance of UPDATE statement

HermannGahm — Tue, 28 Jul 2009 08:02:10 GMT

Hi Anthony,

58250000 records in 36 hours = ~2,2 milli seconds per record. (quite good)

If you want to understand where the time is spent you have to use the active session

history in ORACLE 10g or the extended SQL trace in ORACLE 9i. I assume that the time

is mostly spent on I/O and or logging.

If I/O is the bottleneck Parallel Execution could be an option to run the query faster but here

you would need native sql.

If logging is the bottleneck the configuration (log files and log realted parameters) can be checked.

In any case you need the help of an DBA i guess.

Kind regards,

Hermann

Re: Performance of UPDATE statement

Former Member — Tue, 28 Jul 2009 15:33:41 GMT

Thanks Hermann!