RE: stats collection question

  • From: Michael Schmitt <mschmitt@xxxxxxxxxxxx>
  • To: "'hkchital@xxxxxxxxxxxxxx'" <hkchital@xxxxxxxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
  • Date: Thu, 29 Apr 2010 10:56:26 -0500

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


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

Thanks again, the testing was very informative


-----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 
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 !




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



Other related posts: