Re: Performance impact of MONITORING and GATHER_STALE
- From: Robyn <robyn.sands@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Tue, 15 Mar 2005 09:47:32 -0500
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@xxxxxxxxxxx> 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
>
> --
> //www.freelists.org/webpage/oracle-l
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
