If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

You do not need to rebuild indexes after analyzing table. Try analyzing indexes also. or..
May be your queries work better in Rule based optimizer. If you delete statistics the queries will use RBO again.

Originally posted by SANJAY_G You do not need to rebuild indexes after analyzing table. Try analyzing indexes also. or..
May be your queries work better in Rule based optimizer. If you delete statistics the queries will use RBO again.

Sanjay

Thanks Sanjay. Exactly I did the same. Now performance is back to normal. And it is fine.

One more Doubt, Which analyze table option Estimate/Compute is the best? If I use estimate, will it analyze indexes also? Please guide me. And If I want my queries to use CBO, Do I need to set any parameters? Please advice.

When you use ESTIMATE you can define sample size as percent or rows while COMPUTE analyze all the rows for a table.
Generally ESTIMATE results are very close to accurate.

You need to analyze your tables and indexes separately or you can use DBMS_STAT package to analyze entire SCHEMA or DATABASE.

The parameters to use CBO are FIRST_ROWS and ALL_ROWS but usually it is not set in the init.ora instead the default setting CHOOSE is used. When your optimizer mode is choose Oracle usees CBO if the statistics for the object is present else it uses RBO.

Check you DB_FILE_MULTIBLOCK_READ_COUNT parameter.. now that you've analyzed your tablesa, the CBO probably thinks that a FTS is cheaper than and index lookup. Sometimes the DB__FILE_MULTIBLOCK_READ_COUNT can be set to high making the CBO choose FTS's. Try a setting of 8.

Since you are using != for ACCOUNT_NO, an index on this will not help.
Your SALE_STATUS and STATUS_ID also seems like low cardinality columns, so any b-tree index on this column also won't be of any help.
To me, it seems like FTS is inevitable here, as suggested by grjohnson try tuning the FTS by using higher value for DB_FILE_MULTIBLOCK_READ_COUNT. Would like to see more clues from others.

Originally posted by grjohnson Check you DB_FILE_MULTIBLOCK_READ_COUNT parameter.. now that you've analyzed your tablesa, the CBO probably thinks that a FTS is cheaper than and index lookup. Sometimes the DB__FILE_MULTIBLOCK_READ_COUNT can be set to high making the CBO choose FTS's. Try a setting of 8.