Re: Weird behaviour on a RAC

grgs27_at_gmail.com wrote:
> 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.

Sybrand gave the immediate answer.

> - Why plan instability

Because you aren't using plan stability? http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96533/outlines.htm#PFGRF007

>
> any idea...?

You should check the 9207 bugfixes, and gather some tracing. http://wedonotuse.blogspot.com and poke around on the oracle-l archives for the arguments about statistics gathering.

>
> thanks
> .g

jg

--
@home.com is bogus.
"I will personally rip your tits off"
http://www.signonsandiego.com/uniontrib/20060115/news_lz1n15legend.html
hey, it's a family newspaper.