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@xxxxxxxxxxxxx> 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

Other related posts: