Weird behaviour on a RAC

Dear folks,

9.2.0.6 Oracle EE / RAC (two nodes) / SunOS 5.9 64 bits... Application running on it is mainly (70%) OLTP.

query were running fine, all in a sudden we had several performance problem toghether with weird Oracle CBO behaviour.

#1 same query with same literal (not bind variable) explained with a cost of 7 (seven) after 10 seconds explained again with cost > 15k (more than fifteen thousands)... no! statistics were not running and anyway the cost changes randomly

#2 a simple join between two tables with primary key: first plan is a poor idx range scan on table1 and full table access (!!) on table2.
We changed db_file_mblock_readcnt to 8 forcing to use an index range scan on the primary key... explained correctly, executed on sql plus correctly: original plan lasts 50minutes index range scan on pk is 13 seconds... Forced the parameter in the session Oracle exposes correctly an index range scan on v$sql_plan for that session, the session takes forever to complete whilst the longops shows a FTS going on with that session.

These are two of the major problem we are facing without a clue.

The only things I can say are:
- It seems that wverything started after a bounce of the instances
(but no parameters were changed whatsoever)
- Statistics has been collected correctly

• It seems that with problem #1 changing on table involved from IOT to Heap the problem disappears.
• "Normal" activity in global cache

What is not clear to me is (beside the IOT thing):
- Why now... things have been running fine for a month or so.

• Why plan instability

any idea...?

thanks
.g Received on Mon Jan 16 2006 - 10:04:38 CST