RE: stats collection question

Hi Hermant,

That is great information, thank you so much.  I figured we were defaulting to 
the same method_opt, but I did not know Oracle was looking at sys.col_usage$ to 
determine which histograms to generate.

Based on your testing, it sounds like having User A run the following will help 
resolve our issue

exec 
dbms_stats.gather_schema_stats('USERA',estimate_percent=>100,method_opt=>'FOR 
ALL COLUMNS SIZE SKEWONLY');

Our database is small enough that the estimate percent of 100 will not be a big 
deal

Thanks again, the testing was very informative

Mike

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of Hemant K Chitale
Sent: Thursday, April 29, 2010 10:03 AM
To: Michael Schmitt; oracle-l@xxxxxxxxxxxxx
Subject: Re: stats collection question


Your GATHER_STATS is running with the default method_opt=>'FOR ALL 
COLUMNS SIZE AUTO'.
The first run immediately after the data is refreshed is "unaware" of 
query patterns because no query against the tables have been 
executed.  So it is unable to determine columns to create histograms on.
Having run the 2minute query, when you next run GATHER_STATS, it is 
"aware" of query patterns and creates histograms.
The execution plan changes for the better !

See 
<http://hemantoracledba.blogspot.com/2009/11/sample-sizes-table-level-and-column.html>http://hemantoracledba.blogspot.com/2009/11/sample-sizes-table-level-and-column.html
 

and 
<http://hemantoracledba.blogspot.com/2008/08/testing-gather-stats-behaviour-based-on.html>http://hemantoracledba.blogspot.com/2008/08/testing-gather-stats-behaviour-based-on.html
 

and 
<http://hemantoracledba.blogspot.com/2008/08/more-tests-of-colusage.html>http://hemantoracledba.blogspot.com/2008/08/more-tests-of-colusage.html
 


At 10:22 PM Thursday, Michael Schmitt wrote:
>Hi All,
>
>I have a quick question related to stats collection.  This is for a 
>10203 database on Linux.
>
>We have application (that is not designed very well), that drops all 
>its tables, loads data into the tables, collects stats via (exec 
>dbms_stats.gather_schema_stats('<userA>',30,CASCADE=true)), and then 
>compiles a number of views.  This process runs as UserA.  After the 
>process completes, I can run a query that joins view+view+view and 
>it takes about 2minutes.  However, right after that query takes 2 
>minutes if I run the same stats collection procedure as SYS ( exec 
>dbms_stats.gather_schema_stats('<userA>',30,CASCADE=true)) the query 
>now returns in under 10 seconds (and selects a different plan) until 
>the nightly processing runs again and it goes back to taking 2 minutes again.
>
>Any thoughts?
>


Hemant K Chitale

http://hemantoracledba.blogspot.com




--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l


Other related posts: