| 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: System Statistics oracle9i
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
> 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.
>
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Jul 28 2005 - 22:40:10 CDT
 |
 |