Re: stats collection question
- From: Hemant K Chitale <hkchital@xxxxxxxxxxxxxx>
- To: mschmitt@xxxxxxxxxxxx, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 29 Apr 2010 23:03:15 +0800
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
