Re: Reading/Interpreting 11g Statspack reports

> Several of you mentioned stats collection, histograms and the like and you all have a very good point also;  on 8i I use a very old caveman script that just analyzes each table every week, but on a new installation of 11g there is just the automatically scheduled stats collection which I believe does not run very often.  So I will need to make sure that at least the basic stats gathering has been done before I start my timing tests.

This is the issue: Your "caveman" script may be using ANALYZE and probably does not include the "FOR ALL COLUMNS" clause - this means that Histograms are NOT collected. HOWEVER (and this is the cause of a lot of heartache), 11g (and 10g as well) uses the DBMS_STATS along with a *default* value for the METHOD_OPT parameters ("FOR ALL COLUMNS SIZE AUTO") which collects Histograms based on column usage (COL_USAGE$). Even if the caveman script used DBMS_STATS, you were still Ok on 9i because the default for METHOD_OPT was "FOR ALL COLUMNS SIZE 1". The presence of Histograms and SQL with bind variables can lead to some strange performance issues related to bind peeking, and that was the point of my post: That in case everything else remains the same (application code, adequate resources), you *may* face some issues related to performance because of this.

Note that this could be both positive as well as negative. 11g has ACS which mitigates the bind peeking issue with some initial pain. Just wanted to make you aware of this not-so-obvious issue :)

-- 
John Kanagaraj <><
http://www.linkedin.com/in/johnkanagaraj
http://jkanagaraj.wordpress.com (Sorry - not an Oracle blog!)
** The opinions and facts contained in this message are entirely mine
and do not reflect those of my employer or customers **
--
http://www.freelists.org/webpage/oracle-l