RE: Reading/Interpreting 11g Statspack reports
- From: "Brooks, Dominic (London)(c)" <dbrooks@xxxxxxxxxxxxxxxxxx>
- To: <janine@xxxxxxxxxx>, "oracle-l" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 23 Mar 2010 09:58:51 -0000
You might find Greg's article interesting:
http://structureddata.org/2008/03/26/choosing-an-optimal-stats-gathering
-strategy/
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Janine Sisk
Sent: 23 March 2010 03:12
To: John Kanagaraj
Cc: oracle-l L
Subject: 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=swg
21322719&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'); exec
SQL> dbms_stats.gather_database_stats(estimate_percent =>
SQL> dbms_stats.auto_sample_size, method_opt => 'FOR ALL COLUMNS SIZE
SQL> 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
**********************************************************************
Please consider the environment before printing this email or its attachments.
The contents of this email are for the named addressees only. It contains
information which may be confidential and privileged. If you are not the
intended recipient, please notify the sender immediately, destroy this email
and any attachments and do not otherwise disclose or use them. Email
transmission is not a secure method of communication and Man Investments cannot
accept responsibility for the completeness or accuracy of this email or any
attachments. Whilst Man Investments makes every effort to keep its network free
from viruses, it does not accept responsibility for any computer virus which
might be transferred by way of this email or any attachments. This email does
not constitute a request, offer, recommendation or solicitation of any kind to
buy, subscribe, sell or redeem any investment instruments or to perform other
such transactions of any kind. Man Investments reserves the right to monitor,
record and retain all electronic communications through its network to ensure
the integrity of its systems, for record keeping and regulatory purposes.
Visit us at: www.maninvestments.com
TG0908
**********************************************************************
--
//www.freelists.org/webpage/oracle-l
Other related posts:
