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.
I have nt Analyzed the index after recreating it,but anyway i will do so using
Analyze Index <Indx name>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.
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
_________________________
Joe Ramsey
Senior Database Administrator
dbaDirect, Inc.
(877)687-3227
Reply With Quote
Bookmarks