Re: Reading/Interpreting 11g Statspack reports

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:

http://www-01.ibm.com/support/docview.wss?rs=3538&context=SSLKUM&uid=swg21322719&loc=en_US&cs=UTF-8&lang=en

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.

thanks,

janine

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
> COLUMNS SIZE AUTO".
> 
> 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 (
> 'DBSNMP', 'MGMT_VIEW', 'SYSMAN', 'TRACESVR',
> 'DBSNMP', 'MGMT_VIEW', 'SYSMAN', 'TRACESVR', 'AURORA$ORB$UNAUTHENTICATED',
> 'AURORA$JIS$UTILITY$', 'OSE$HTTP$ADMIN', 'MDSYS', 'MDDATA',
> 'ORDSYS', 'ORDPLUGINS', 'SI_INFORMTN_SCHEMA', 'CTXSYS',
> 'WKSYS', 'WKUSER', 'WK_TEST', 'REPADMIN', 'LBACSYS', 'DVF',
> 'DVSYS', 'ODM', 'ODM_MTR', 'DMSYS', 'OLAPSYS', 'WMSYS',
> 'ANONYMOUS', 'XDB', 'EXFSYS', 'DIP', 'TSMSYS',
> 'SYS','SYSTEM','OUTLN'
> )
> group by owner, table_name, column_name
> having count(*) > 2;
> 
> -- 
> 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


Other related posts: