Performance Degrade After analyze table

[gnagesh]

Hi Guru's

I have a table with 10Million rows. I analyzed the same table using analyze table estimate statistics; after analyzing this table I found performance degrade.

Do I need to rebuild the indexes as well, along with analyze table? If I delete statistics, do I get the previous performance which is more better than after analyze? Please advice.

Please treat this an urgent.

I am using Oracle 8.1.7. Optimizer Mode CHOOSE. Block Size 4K

Regards

[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

[gnagesh]

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.

Regards

[SANJAY_G]

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.

Sanjay

[gnagesh]

Thanks Sanjay,

If I am not wrong, analyzing tables will do the analyze indexes also? Right! still do I need to analyze indexes separately?

Regards
Nagesh

[SANJAY_G]

Yes you are right ANALYZE TABLE does analyze indexes on the table as well.

Sanjay

[gnagesh]

Thanks Sanjay.

I have below query, taking 1 min over to fetch a record from over 10Million rows table. Which is not considerable.

SELECT USER_NO,VALUE_LEFT,EXPIRE_DATE
FROM MST_USER
WHERE ACCOUNT_NO != '7594299380771992' /*'3839059713682615'*/
AND SALE_STATUS='V'
AND (MST_USER.STATUS_ID='A' OR MST_USER.STATUS_ID='C')
AND VALUE_LEFT = 0
AND ROWNUM <= 1

I have an composite Index on this table based on ACCOUNT_NO,SALE_STATUS, STATUS_ID, VALUE_LEFT. If I see explain plan for this statement it is still FTS. Help me to tune this statement.

Explain Plan:
LEVEL ID OPERATION OPTIONS OBJECT POSITION
---------- --- ------------------------------ ------------------ -------------------- ----------
1 0 SELECT STATEMENT cost=9232
2 1 COUNT STOPKEY
3 2 TABLE ACCESS FULL MST_USER


If I use INDEX Hint :

LEVEL ID OPERATION OPTIONS OBJECT POSITION
---------- --- ------------------------------ ------------------ -------------------- ----------
1 0 SELECT STATEMENT cost=56057
2

2 1 COUNT STOPKEY
3 2 TABLE ACCESS BY INDEX ROWID MST_USER
4 3 INDEX FULL SCAN MSTUSERIDX


Can you please help me to tune this statement.

Regards

[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.

[SANJAY_G]

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.

Sanjay

PS: grjohnson, your typing speed seems to be improved Cheers..

[gnagesh]

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.

Thanks.

SQL> show parameter db_file

NAME TYPE VALUE
------------------------------------ ------- ------------------------------
db_file_direct_io_count integer 64
db_file_multiblock_read_count integer 8
db_file_name_convert string
db_files integer 200
SQL>

Do I need to specify explicitly db_file_multiblock_read_count = 8 in parameter file.

Regards