Performance Degrade After analyze table

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

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

Quote:

---

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

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

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

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

Sanjay

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

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.

Sanjay

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

Quote:

---

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

Quote:

---

Originally posted by gnagesh
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

---

that is not full table scan, oracle is clever enoguh to stop after read first row

Quote:

---

Originally posted by pando
that is not full table scan, oracle is clever enoguh to stop after read first row

---

Thanks to all. Can you elaborate it Please.

elaborate what???

Quote:

---

Originally posted by pando
elaborate what???

---

Is it because of COUNT STOP KEY, you are saying it is not FTS? Please clear me If I am wrong?

Thanks

Quote:

---

Originally posted by pando
that is not full table scan, oracle is clever enoguh to stop after read first row

---

Hi Pando,
Why it is not a FTS ? The COUNT STOPKEY is I think because of the ROWNUM condition in the query?

Sanjay

well try it with a 100 million rows table and see how long it takes, and set autotrace on and see how much it reads

if you say it´s FTS you would expect lengthy time and tons of reads, but try it

You could play too with this two parameters:

optimizer_index_caching
optimizer_index_cost_adj

Their default values are not very goods for index accesing.

Check it at www.evdbt.com.

Cheers

Angel

Nagesh,

May be you can do this test.
Can you post the 'explain plan', 'time elapsed' and 'rows returned' with and without rownum condition in your where clause?

Sanjay

Code:

---

SQL> select * from cmsapmdw.estadisticas_midd partition (ESTADISTICAS_MIDD200201) ;

570201 rows selected.

Elapsed: 00:00:26.81

Execution Plan
----------------------------------------------------------
  0      SELECT STATEMENT Optimizer=CHOOSE (Cost=520 Card=570201 Bytes=18816633)

  1    0  TABLE ACCESS (FULL) OF 'ESTADISTICAS_MIDD' (Cost=520 Card=570201 Bytes=18816633)

Statistics
----------------------------------------------------------
          0  recursive calls
          4  db block gets
      41207  consistent gets
      3186  physical reads
          0  redo size
  71096799  bytes sent via SQL*Net to client
    6348720  bytes received via SQL*Net from client
      38015  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
    570201  rows processed


SQL> select * from cmsapmdw.estadisticas_midd partition (ESTADISTICAS_MIDD200201)
  2  where rownum < 2;

1 row selected.

Elapsed: 00:00:00.43

Execution Plan
----------------------------------------------------------
  0      SELECT STATEMENT Optimizer=CHOOSE (Cost=520 Card=570201 Bytes=18816633)

  1    0  COUNT (STOPKEY)
  2    1    TABLE ACCESS (FULL) OF 'ESTADISTICAS_MIDD' (Cost=520 Card=570201 Bytes=18816633)

Statistics
----------------------------------------------------------
          0  recursive calls
          4  db block gets
          1  consistent gets
          0  physical reads
          0  redo size
      1099  bytes sent via SQL*Net to client
        549  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

---

Quote:

---

Originally posted by pando

Code:

---

SQL> select * from cmsapmdw.estadisticas_midd partition (ESTADISTICAS_MIDD200201) ;

570201 rows selected.

Elapsed: 00:00:26.81

Execution Plan
----------------------------------------------------------
  0      SELECT STATEMENT Optimizer=CHOOSE (Cost=520 Card=570201 Bytes=18816633)

  1    0  TABLE ACCESS (FULL) OF 'ESTADISTICAS_MIDD' (Cost=520 Card=570201 Bytes=18816633)

Statistics
----------------------------------------------------------
          0  recursive calls
          4  db block gets
      41207  consistent gets
      3186  physical reads
          0  redo size
  71096799  bytes sent via SQL*Net to client
    6348720  bytes received via SQL*Net from client
      38015  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
    570201  rows processed


SQL> select * from cmsapmdw.estadisticas_midd partition (ESTADISTICAS_MIDD200201)
  2  where rownum < 2;

1 row selected.

Elapsed: 00:00:00.43

Execution Plan
----------------------------------------------------------
  0      SELECT STATEMENT Optimizer=CHOOSE (Cost=520 Card=570201 Bytes=18816633)

  1    0  COUNT (STOPKEY)
  2    1    TABLE ACCESS (FULL) OF 'ESTADISTICAS_MIDD' (Cost=520 Card=570201 Bytes=18816633)

Statistics
----------------------------------------------------------
          0  recursive calls
          4  db block gets
          1  consistent gets
          0  physical reads
          0  redo size
      1099  bytes sent via SQL*Net to client
        549  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

---

---

But ain't the cost and cardinality for FTS is same for both plans?

Sanjay

well cost means nothing

cardinality is obtained from statistics

Quote:

---

Originally posted by gnagesh
Thanks Sanjay.
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

---

The only improvement you might be able to get on the execution plan is to get Oracle to use a fast full index scan, maybe parallelized.

Alternatively you might modify your index. Having ACCOUNT_NO as the first column prevents the index being used in a conventional range scan for optimizing this query -- move it down the list, or onto a different index, and you may get a better response.

Quote:

---

Originally posted by SANJAY_G
But ain't the cost and cardinality for FTS is same for both plans?

Sanjay

---

As pando says oracle will estimate cost and cardinality BEFORE!!! real execution query fom stats tables, but not AFTER execution as result of query.
REAL statistics of execution (AFTER QUERY) u can see in

Statistics
----------------------------------------------------------
...
41207 consistent gets
3186 physical reads
...

and

Statistics
----------------------------------------------------------
...
1 consistent gets
0 physical reads
...
That is differrence between two plans.

Hi All Participants and Guru's

Thank you very much for your valuable suggestions and contributions. And I apologize for the delay in response. Finally I solved this performance issue by creating one more composite index on sale_status,status_id and value_left. Now the query elapsed time is almost 00:00:00.

Thanks Once again.

Best regards

Hi all,

We have seen a lot of suggestions but Iam curious to know as to why the column rownum has not been included in the concatenated index. Could someone explain this for me.

Regards,

K.Diwakar

Quote:

---

Originally posted by diwakar
Hi all,

We have seen a lot of suggestions but Iam curious to know as to why the column rownum has not been included in the concatenated index. Could someone explain this for me.

Regards,

K.Diwakar

---

Hi
ROWNUM is a pseudocolumn. A pseudocolumn behaves like a table column, but is not actually stored in the table. You can select from pseudocolumns, but you cannot insert, update, or delete their values.

You can use ROWNUM to limit the number of rows returned by a query.

Hope this helps.

Quote:

---

Originally posted by gnagesh
Hi
ROWNUM is a pseudocolumn. A pseudocolumn behaves like a table column, but is not actually stored in the table. You can select from pseudocolumns, but you cannot insert, update, or delete their values.

You can use ROWNUM to limit the number of rows returned by a query.

Hope this helps.

---

Thanks for your answer....

Regards,

K.Diwakar