Re: System Statistics oracle9i
- From: Deepak Sharma <sharmakdeep_oracle@xxxxxxxxx>
- To: Trevor.Williams@xxxxxxxxxx, "Oracle-L (E-mail)" <Oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 28 Jul 2005 20:38:09 -0700 (PDT)
I don't have much insight into gather system stats,
but a few days ago, as per one TAR we were supposed to
collect them.
In this TAR, we used "_optimizer_cost_model"=cpu at
session-level as a workaround, then one of the queries
in question, picked-up an index as it was supposed to.
After gathering system stats for about 30 mins, and
'not' specifying the above, the plan still showed
using the index. But, when we gathered the stats for 6
Hrs (today), the plan didn't pick up the index. When
we deleted the stats and specified the above
parameter, the index again got picked-up. So we are
still going back-and-forth with Oracle as to when and
how often do we collect system stats on our 9.2.0.6
database. We have a plan to go to 10G soon where I
believe this is done by default.
Thanks,
Deepak
--- "Williams, Trevor" <Trevor.Williams@xxxxxxxxxx>
wrote:
> Hi
>
>
>
> How do you handle system stats when your
> gather_system_stats returns
> MREADTIM < SREADTIM?
>
> Do you live with the optimizer using the adjusted
> dfmrc value?
>
> Apply some calculation to SREADTIM to generate a
> larger MREADTIM?
>
> Ignore all stats where MREADTIM<SREADTIM and pick
> some average for the
> remainder?
>
> Or what?
>
> We have dynamic memory buffer caching turned on here
> (HP-UX ia64
> itanium). Is this the likely reason why often
> MREADTIM<SREADTIM? Or do I
> blame the SAN?
>
>
>
> ... and ...
>
>
>
> My gathered system statistics are much more variable
> that I would have
> thought. For the same time period that is.
>
> Is it possible that the two instances on the same
> server will affect
> each other's system statistics?
>
> Is there likely to be a problem with gathering
> system stats for both
> instances at the same time?
>
> Even so, what do you suggest to handle this
> variation? Plan A is to take
> the average of all of the stats.
>
>
>
> Instance1: 08:30-14:30
>
>
>
> STATID SRDTM MRDTM CPU
> MBRC MXTHRD
>
> ---------- ---------- ---------- ----------
> ---------- ----------
>
> D27JUL0830 .995 1.13 1194
> 6 94699520
>
> D26JUL0830 1.378 1.649 1190
> 6 34324480
>
> D25JUL0830 1.482 1.088 1191
> 5 386048
>
> D22JUL0830 1.67 .723 1185
> 5 13148160
>
>
>
> Instance2: 08:30-14:30
>
>
>
> STATID SRDTM MRDTM CPU
> MBRC MXTHRD
>
> ---------- ---------- ---------- ----------
> ---------- ----------
>
> D27JUL0830 4.065 10.643 1120
> 30 68080640
>
> D26JUL0830 .659 7.064 1194
> 31 38184960
>
> D25JUL0830 .622 9.988 1198
> 28 23548928
>
> D22JUL0830 1.558 9.757 1195
> 29 1528832
>
>
>
> Thanks
>
> Trevor
>
>
>
>
>
>
>
>
>
> DISCLAIMER:
> Disclaimer. This e-mail is private and
> confidential. If you are not the intended recipient,
> please advise us by return e-mail immediately, and
> delete the e-mail and any attachments without using
> or disclosing the contents in any way. The views
> expressed in this e-mail are those of the author,
> and do not represent those of this company unless
> this is clearly indicated. You should scan this
> e-mail and any attachments for viruses. This company
> accepts no liability for any direct or indirect
> damage or loss resulting from the use of any
> attachments to this e-mail.
>
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
--
//www.freelists.org/webpage/oracle-l
Other related posts:
