Re: Performance impact of MONITORING and GATHER_STALE

Leng,

I've been using monitoring and gather stale to maintain statistics on our data warehouse for about 9 months and it has worked out well for this system. Reports are written out to the file system so I can see the level of change on the objects and know which ones will be captured by the weekly gather stale job. Although only a small percent of the objects will be reanalyzed each week, I still export the statistics in case the new stats have a negative impact on query performance. The tables that change most often are highly used for reporting on this db so they are the 'usual suspects' in performance issues anyway. I haven't seen any indications that the monitoring has negatively impacted performance and for this system, being able to identify the tables that were frequently changing was important.

Robyn

On Tue, 15 Mar 2005 16:50:45 +1100, Leng Kaing <Leng.Kaing_at_hsntech.com> wrote:
>
> Hi guys,
>
> Apologies if I'm revisiting a beaten path but I've tried to search the
> archive, metalink and google and couldn't find my answer (or it may have
> been hiding). So I'll ask the question (again)...
>
> What is the performance impact of turning on MONITORING at the table
> level? Ie. ALTER TABLE x MONITORING. Will it have a negative impact on
> our production system?
>
> We'd like to make use the GATHER_STALE option rather than just blindly
> re-analyzing the entire schema. Is GATHER_STALE any more efficient than
> the normal estimate using dbms_stats.gather_table_stats command?
>
> Are you using MONITORING and GATHER_STALE in your production systems in
> the first place?
>
> TIA,
>
> Leng.
>
> ---------------------------------------------------
>
> Leng Kaing
>
> Hansen Technologies
>
> Phone: +61-3-9840-3832
>
> --
> http://www.freelists.org/webpage/oracle-l
>

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