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
Bluesky
