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

From: Peter Alteheld <palteheld_at_yahoo.de>
Date: Thu, 30 Jun 2005 09:12:18 +0200 (CEST)
Message-ID: <20050630071218.60501.qmail@web26509.mail.ukl.yahoo.com>

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_at_collegeboard.org> 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

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

Received on Thu Jun 30 2005 - 03:17:44 CDT

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