URGENT HELP!!!

Hi all,
I am facing some problem with the performance of the application.Yesterday,there a problem with an index,ie.Maxextent of an index was reached,So i dropped and recreated the index with a different storage clause.Now today we are facing that was application has become slow.Can anyone please let me know step by step process to speed up the application again.

Regards,
Vinod Kumar.

Vinod,

Have you analyzed the index after re-creating it...? . If not please do a compute statistics for that.

Hope it will solve your problem.

...Thomas

hi aji,

I have nt Analyzed the index after recreating it,but anyway i will do so using

Analyze Index Compute statistics;

Can u tell me what is the next step..of it.I know that this above statment will put some of the statistics in dba_indexes.Can u please tell me how to find out whether the index is ok or not.if it is not ok,then what should i do to solve it.

Thanks,
Vinod.

I concur, make sure that you analyze your index again.
Also, if the column contains data that is highly skewed
you'll need to create a histogram on the column. If
the data is skewed and you don't have a histogram then
Oracle will assume a flat distribution and the chances are
it may not use the index or use it inappropriately.

Just in case the syntax is...

ANALYZE INDEX index_name compute statistics;

you can also use estimate statistics sample # rows or
sample # percent. The estimate is much faster and is
usually sufficient as long as you give it enough data
to get a good sampling. If you have a pretty uniform
distribution then you can get away with a lower number.
If you have a non-uniform distribution, then increase
the number. If unsure, be safe and do a compute.

To do a histogram if your column values are highly skewed:

ANALYZE TABLE table_name compute statistics
for columns column_names SIZE number of bands.
Number of bands, from what I can gather is similar to
number of buckets. The default is 75. If you have,
say, 8 distinct values in the column then set bands =8.

ANALYZE TABLE table_name compute statistics
for columns column_names SIZE 8;


Joe