Skip to Main Content
  • Questions
  • Poor performance got worse after using alter table shrink space cascade, why?

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, MICHAEL.

Asked: August 04, 2017 - 12:36 pm UTC

Last updated: August 04, 2017 - 3:41 pm UTC

Version: 12g

Viewed 10K+ times! This question is

You Asked

We have an Oracle 12g database without partitioning (another issue due to cost) which has one table that got very large before we deleted unwanted rows.
The table grew to over 150 million rows and was over 50% of the database (200GB allocated).
Users complained of poor performance of an app (it does selects and no inserts) that uses the database.
We got the users to delete unwanted data and the table now contains 20 million rows, there was no performance improvement.

After using "alter table shrink space cascade" the database size is now only 70GB allocated.
And we re-built all the indexes on the table.
We have ran GATHER_SCHEMA_STATS(user , method_opt => 'FOR ALL COLUMNS', options => 'GATHER STALE')

The performance of the app (selects only) is now even worse.
Unfortunately our DBAs blame the app (3rd party) and the app support blame our database.

I expected some performance improvement of selects due to the reduced table size.
So any ideas as to why we have worse performance after shrinking the table and lowering the HWM?

Any other steps we should have done after "alter table" and "gather stats"?

and Chris said...

This is a great example of why you should understand why something is slow before you start making changes. You might "fix" the wrong thing!

So, when you shrank the table some of the rows will have moved to new data blocks.

Why's that a problem?

I'm guessing that your slow queries used an index. And after shrinking the table, the rows it accesses are spread across more data blocks. In particular the rows your query gets. Having to access more blocks => the database does more work. The database doing more work => the query is slower.

Of course, this is a guess on my part. It's possible there's something else going on. To really understand & help we need to see the execution plans for your SQL.

So if you need further help, get the execution plan for your query and post it here. Ensure this includes the actual rows and buffers stats!

If you're not sure how to do this, read the is blog post:

https://blogs.oracle.com/sql/how-to-create-an-execution-plan

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database