RE: optimizer_ ???

Niall

>showing me that if you change a parameter the plan will change tells
>me very little about performance - it does tell me about a bit about
>behaviour, showing me response time does and - so far as I can tell -
>there aren't any examples of improved response time from setting these
>parameters. Its as if the tuning goal has become 'favour nested loops'
>rather than 'impove end-user experience'.

In the example I posted some hours ago shows that:

SQL> ALTER SESSION SET OPTIMIZER_INDEX_CACHING=3D0; SQL> SELECT count(*)
  2 FROM t t1, t t2
  3 WHERE t1.id =3D3D t2.col1(+) AND t2.col1(+) > 900;

Elapsed: 00:00:00.07

SQL> ALTER SESSION SET OPTIMIZER_INDEX_CACHING=3D90; SQL> SELECT count(*)
  2 FROM t t1, t t2
  3 WHERE t1.id =3D3D t2.col1(+) AND t2.col1(+) > 900;

Elapsed: 00:00:01.05

This means that just by setting OIC there is a difference of factor = *15*.

Chris

--
http://www.freelists.org/webpage/oracle-l