Ant: System Statistics...do I need them? - (DBMS_STATS.GATHER_SYSTEM_STATS)
- From: Peter Alteheld <palteheld@xxxxxxxx>
- To: cmarquez@xxxxxxxxxxxxxxxx, oracle-l@xxxxxxxxxxxxx
- Date: Thu, 30 Jun 2005 09:12:18 +0200 (CEST)
Chris,
we turned some databases from crawling to running by gathering system
statistics.
In pre-Oracle9i you had to set optimizer_index_cost_adj (oica) to adjust the
index access costs. You mentioned the article 'Search for Intelligent Life in
the CBO' in another thread - there you do find more information on this. After
having generated system statistics the db might run well (usually does) without
adjusting oica. But, I think Jonathan Lewis wrote some discussion about this on
his web page - 'Problems with System Statistics'. And Christian Antognini gave
an interesting presentation on the Hotsos conference 'CBO: A cponfiguration
roadmap' - you'll find it on the web.
There are some interesting points: system statistics are not gathered
automatically by the
gather-stats-job in 10g. If the measured sreadtim is larger than mreadtim the
statistics are not used (there is a column in sys.aux_stats$ which states
this). If you delete system statistics, they are not really deleted, just
flagged out and you see in the costs that they are still used. Some system
statistics seem to be never changing - ioseektim and the io..-value.
Some dbas feel that their power of control is taken by using system statistics.
Because they fear that after gathering new system statistics a change in
execution plan occurs which makes a fast query suddenly run slow.
Peter
"Marquez, Chris" <cmarquez@xxxxxxxxxxxxxxxx> wrote:
Any one have advise, references/links/docs, and procedures for using,
colleting, and maintaining System Statistics (DBMS_STATS.GATHER_SYSTEM_STATS).
I have never used them but am looking to se if I can *help* further improve the
performance of database application code.
I will not re-write (the application) bad sql but as a DBA I do feel obligated
to maintain and improve the database or create the best playing filed possible
(for the bad sql ;o) ).
By not having System Statistics am I not giving the database code a better
chance?
Thanks
PS
I believe (know) nothing is free in life nor with Oracle...there are no "silver
bullets".
Having any stats is an attempt and will change sql plans...for better *OR* for
worse.
I assume this rule applies to having System Statistics, no?
Simply...what is the chance things run worse with System Statistics?
PPS I found these;
Doc ID: Note:149560.1
Subject: Collect and Display System Statistics (CPU and IO) for CBO usage
Doc ID: Note:153761.1
Subject: Scaling the System to Improve CBO optimizer
Chris Marquez
Oracle DBA
---------------------------------
Gesendet von Yahoo! Mail - Jetzt mit 1GB kostenlosem Speicher
Other related posts:
- » Ant: System Statistics...do I need them? - (DBMS_STATS.GATHER_SYSTEM_STATS)
