Re: optimizer_ ???

Wolfgang,
Thank you for the response and thank you for the presentation. I didn't make the connection between the system statistics and the oica adjustments not having an impact anymore until that day. My testing had been on a data warehouse environment and the users were officially satisfied (for the moment) with query response time, so tweaking on this system had stopped.

I don't know if this is an 'acceptable' use of system stats, but I had gathered them on several different systems and noted that singleblock and multiblock read times were consistently and noticeably slower on our EMC systems. I'd been arguing that our performance issues were related to the durations of the reads, so I turned the numbers over to my boss and the systems guys. When I got back to work, I realized I hadn't gathered new system stats after the recent storage upgrade and I recollected the numbers. The post SAN swap numbers did get much better which I think proved the original point:

OLTP After
03-24-2005 13:00 to 15:00
SREADTIM 1.678
MREADTIM 5.664
CPUSPEED 198
MBRC 22
MAXTHR 209356800
SLAVETHR 143360 OLTP Before
10-29-2004 13:48 to 15:48
SREADTIM 3.022
MREADTIM 10.616
CPUSPEED 193
MBRC 17
MAXTHR 119790592
SLAVETHR 171008 OLAP After
03-25-2005 00:01 to 02:01
SREADTIM 2.288
MREADTIM 9.068
CPUSPEED 194
MBRC 21
MAXTHR 209356800
SLAVETHR 142336 OLAP Before
11-06-2004 00:01 to 02:01
SREADTIM 8.186
MREADTIM 20.09
CPUSPEED 191
MBRC 20
MAXTHR 141285376
SLAVETHR 151552  Can system stats be interpreted this way? Does it provide a real measure of the read time, cpu speed and throughput? Robyn

On 4/22/05, Wolfgang Breitling <breitliw_at_centrexcc.com> wrote:
> Robyn,
>
> that's how I remember it too.
> I don't deny that some people have had success lowering oica or raising
> oic - and for individual sql I have used them too ( I remember one case
> where I deliberately set oica to 1 so that differences in index costs
> would disappear and the index be determined by the tie-breaking rule
> which was the index I needed) but I really don't like the idea of
> 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
>
> >
> > Of course, if I've misremembered the presentation, someone please let me
kn=
> > ow.
> >
>
> --
> Regards
>
> Wolfgang Breitling
> Centrex Consulting Corporation
> www.centrexcc.com <http://www.centrexcc.com>
>

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