| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: optimizer_ ???
Hi Chris,
Sure. What I, and maybe Niall too, am looking for are examples that show response time reduction by changing of execution plan due to the tweaking of oic or oica (and maybe even dfmrc) from default values to those which should be optimal.
How would you approach that? Is it experience, intuition or do you have any rules for finding such examples?
Peter
Christian Antognini <Christian.Antognini_at_trivadis.com> wrote:
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=0;
SQL> SELECT count(*)
2 FROM t t1, t t2
3 WHERE t1.id =3D t2.col1(+) AND t2.col1(+) > 900;
Elapsed: 00:00:00.07
SQL> ALTER SESSION SET OPTIMIZER_INDEX_CACHING=90;
SQL> SELECT count(*)
2 FROM t t1, t t2
3 WHERE t1.id =3D 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-lReceived on Wed Apr 27 2005 - 04:06:59 CDT
 |
 |