RE: optimizer_ ???

OK, this just made me of something else we need (someone at @oracle.com watching I hope).

select /*+PARAMETER optimizer_max_permutations=3D20 optimizer_index_caching=3D80 optimizer_index_cost_adj=3D20 */   from blah where foo=3D123;

This would allow us to tweak session variables at the SQL statement level. Currently we have to use some sort of triggering event to determine which sessions might be about to run the SQL and then use that event to set the parameters at the session level. I like the method above much better.

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Wolfgang Breitling Sent: Friday, April 22, 2005 3:22 PM
To: robyn.sands_at_gmail.com
Cc: oracle-l_at_freelists.org
Subject: Re: optimizer_ ???

Robyn,

that's how I remember it too.
I don't deny that some people have had success lowering oica or raising=20 oic - and for individual sql I have used them too ( I remember one case=20 where I deliberately set oica to 1 so that differences in index costs=20 would disappear and the index be determined by the tie-breaking rule=20 which was the index I needed) but I really don't like the idea of=20 setting them system-wide.

Robyn wrote:
> I attended Wolfgang's presentation on oica and oic at hotsos, and
> towards the end of the presentation, he said using system statistics
> would provide better results than adjusting oica and oic. This was

>=20
> Of course, if I've misremembered the presentation, someone please let
me kn=3D
> ow.

>=20

--=20
Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com

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

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