Re: Reading/Interpreting 11g Statspack reports

From: Janine Sisk <>
Date: Mon, 22 Mar 2010 20:12:04 -0700
Message-Id: <>

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 **

Received on Mon Mar 22 2010 - 22:12:04 CDT

Original text of this message