Re: Reading/Interpreting 11g Statspack reports
- From: John Kanagaraj <john.kanagaraj@xxxxxxxxx>
- To: janine@xxxxxxxxxx
- Date: Mon, 15 Mar 2010 13:44:20 -0700
Hi Janine,
> 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 **
--
//www.freelists.org/webpage/oracle-l
Other related posts:
