Search My Oracle Blog

Custom Search

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.

2 comments:

tjay said...

Hi Hemant;

Once I issue:
alter table move
or
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 !

Hemant

Aggregated by orafaq.com

Aggregated by orafaq.com
This blog is being aggregated by orafaq.com

Top 50 Oracle SQL Blogs 2016

Top 50 Oracle SQL Blogs 2016
Top 50 Oracle SQL Blogs 2016