28 January, 2009

Faulty Performance Diagnostics based on initial set of rows returned

Randolf Geist has written a test case to show what happens when a performance tuning exercise isn't continued because the Developer / DBA / Consultant only looks at the first set of rows returned by a query.
A front-end tool may present only the first N set of rows but if the query is part of an application code (eg copying/transforming a large set of rows), the application would actually be waiting for ALL the rows to be processed.


tjay said...

Hi Hemant;

Once I issue:
alter table move
rebuild index.
Is it necessary to analyze table or index?

Hemant K Chitale said...

When you do an ALTER TABLE MOVE you have to REBUILD the indexes.

It is not strictly necessary to GATHER_STATS again. Oracle retains previous stats. However, step back and think about why you might have done the MOVE and/or REBUILD.
Say you have deleted a very large portion of the rows, or the table contains data for multiple DEPARTMENTs and you have deleted data for one or more departments. Do you want the Table/Column/Index statistics to still reflect that deleted data ?
You have shrunk a table down from 1million rows and 50000 blocks to 200thousand rows and 10000 blocks. Do you want the statistics to reflect 1million rows and 50000 blocks ?

So, whether you need to GATHER_STATS depends on what has happened to the data !