Re: Reading/Interpreting 11g Statspack reports

  • From: Janine Sisk <janine@xxxxxxxxxx>
  • To: John Kanagaraj <john.kanagaraj@xxxxxxxxx>
  • Date: Mon, 22 Mar 2010 20:12:04 -0700

John (and anyone else who wants to jump in),

I ran your SQL on my 8i database and got no rows, which I think you were 
expecting.  In 11g it returned 41 rows - 30 owned by my application user, ten 
by APEX_030200 and 1 by ORDDATA.

By combining what you have said and the stats gathering advice found here:

I have done the following (in 11g):

SQL> exec dbms_stats.delete_schema_stats('my-user');
Reran your SQL - all rows owned by my user are gone
SQL> exec dbms_stats.set_param('METHOD_OPT', 'FOR ALL COLUMNS SIZE 1');
SQL> exec dbms_stats.set_param('ESTIMATE_PERCENT', '100');
SQL> exec dbms_stats.gather_database_stats(estimate_percent => 
dbms_stats.auto_sample_size, method_opt => 'FOR ALL COLUMNS SIZE 1', degree => 
3, cascade => true);
shut down and restart Oracle
Reran your SQL again - no rows found

Unfortunately, one thing I forgot to do was to capture the output from explain 
plan before doing this so I only have my numbers from JMeter to compare, which 
measure a lot more than just database performance.  But going by those, it 
doesn't seem like this helped any (it may even be slightly worse).

My question(s) - did I do this right, and is there anything else stats-related 
I should be doing here?

I have not yet dived (dove?) into statspack/10046 trace analysis;  I wanted to 
eliminate this as a possible issue before going there.



On Mar 11, 2010, at 3:25 PM, John Kanagaraj wrote:

> Hi Janine,
> Did you mention that the old database was 8i and the new one is 11g?
> One major pain for any upgrade from 8i/9i to 10g or 11g would be the
> auto generation of histograms, and resulting issues due to bind
> peeking. The inbuilt auto stats job (or task in 11g) collects
> histograms *without letting you know* on columns which appear in
> COL_USAGE$ because the METHOD_OPT default in 10g and above is "FOR ALL
> Can you compare the histograms on the old and new and report back?
> Tracing is good, but this is a quick check.
> use the following SQL:
> select owner, table_name, column_name, count(*) from dba_tab_histograms
> where owner not in (
> )
> group by owner, table_name, column_name
> having count(*) > 2;
> -- 
> John Kanagaraj <><
> (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 **


Other related posts: