Ant: System Statistics...do I need them? - (DBMS_STATS.GATHER_SYSTEM_STATS)

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: