Re: Reading/Interpreting 11g Statspack reports
Date: Mon, 22 Mar 2010 20:12:04 -0700
Message-Id: <BB5DF8F2-FB92-42E9-8B40-9CF4D0CA2F05_at_furfly.net>
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.
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-lReceived on Mon Mar 22 2010 - 22:12:04 CDT