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

Other related posts: