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 ** -- //www.freelists.org/webpage/oracle-l