RE: stats collection question

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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Hemant K Chitale Sent: Thursday, April 29, 2010 10:03 AM
To: Michael Schmitt; oracle-l_at_freelists.org 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