Thanks for the question, Jonathan.
Asked: November 23, 2015 - 12:35 pm UTC
Last updated: November 23, 2015 - 1:01 pm UTC
Version: 11.2.0.4
Viewed 1000+ times
You Asked
Hi Tom
I have a table that is normally very small > 1MB, holding only a few hundred records.
We have a process that runs updates on these transitory records very frequently, the execution plan for the update runs a full table scan. (As I'd expect on a table with a small number of records).
Normally this update runs in 0.006 seconds.
We had an issue recently that caused the table to balloon to 1500MB.
The number of records is now the same as normal but the update table 0.3 seconds.
I believe this is because Oracle's full table scan will read the empty blocks in the table.
The database is on standard edition so I can't perform any online activities to coalese and shrink the table.
Do you have any suggestions that can "clean up" this type of sparse table on a busy 24/7 database, or improve update performance on spare tables.
I've run stats updates and ruled out many other possible causes for this update slowdown.
Thanks
Jon Marsh
and Connor said...
Two things you could consider
1) create index MY_INDEX on MY_TABLE ( all_of_the_table_columns )
This gives you an index which will be much smaller than the sparse table, and could be used as a triage step (ie, to make do until you get a chance to reorganize the table). Whether this is appropriate depends on balance between the time to *scan* the table, versus the time to *update* the rows once they are found - the index might make things better, it might make things worse. You'd need to test carefully.
2) You cant use DBMS_REDEFINITION because its Standard Edition, but that doesnt mean you can't "roll your own" version - it just means more work. A redef is effectively:
- create a copy of the table
- add triggers to capture all the deltas
- setup all the appropriate indexes / constraints etc on the new table
then take a tiny outage and flip over all the dictionary definitions. The 'outage' could almost be zero - depending on how many dependencies you have (plsql and the like)
For a table so small, this could be overkill - because if its taking 0.3 seconds to scan the table, surely it wont take much longer than that to do an 'alter table move' (because its only a few hundred rows to move ?)
Is this answer out of date? If it is, please let us know via a Comment