p.santos000_at_gmail.com wrote:
> Mladen Gogala wrote:
>> p.santos000_at_gmail.com wrote:
>>> Well that's a good point, but here is the difficult thing about this
>>> system. Our customers
>>> upload data anytime they want at any point of the day. So in the
>>> morning COL1='X' could
>>> return 100 rows, but the same query in the evenning could yield a
>>> million rows.
>>>
>>> Most of the queries executed on our system take anywhere from 5 minutes
>>> to 30-45 minutes, so a couple of extra seconds is for the sampling is
>>> insignificant.
>>>
>>> -peter
>>>
>>
>> Peter, if that is Oracle 9i you can set tables to "MONITORING", in which
>> case DBA_TAB_MODIFICATIONS will be populated and statistics scanned
>> dynamically only for the tables with more then 10% of changed rows.
>>
>>
>> --
>> Mladen Gogala
>> http://www.mgogala.com
>
>
> This is 10g so the monitoring is done automatically.
> We also have scheduled jobs that run every 4hrs and look for "stale"
> objects and re-generate
> stats.
>
Peter, problem with the dynamic sampling is the problem of plan
stability. If Oracle re-samples statistics for every execution, you
will have potentially different plan for every execution. That is a
general problem with CBO: explain plan is useless. You cannot have
development database on a small system and production database on a
big box and be certain that plans are the same. That was the case with
RBO. CBO causes many more fluctuations. Dynamic sampling introduces
fluctuations where they're really not needed. I tend to compute
statistics monthly or even bi-monthly and make amends only if problem
arises. That is why I recommend permanent statistics. I don't know
anything about your company which means that I can't give you any advice.
--
Mladen Gogala
http://www.mgogala.com
Received on Fri Jun 02 2006 - 16:18:12 CDT
