Re: 10053 Trace File anomolies

mccmx_at_hotmail.com wrote in
news:1119945065.635042.278400_at_z14g2000cwz.googlegroups.com:

> We are using the following syntax for analyzing the schema:
>
> dbms_stats.gather_schema_stats(ownname=>'MATT',cascade=>TRUE,estimate_p
> ercent=>DBMS_STATS.AUTO_SAMPLE_SIZE);
>
> but by doing a full analyze as such:
>
> dbms_stats.gather_table_stats(ownname=>'SYSADM',tabname=>'<TNAME>'casca
> de=>TRUE) for each of the tables in the query, the problem dissapears
> and the response time returns to normal.
>
> I'm surprised that the estimate stats got it so wrong....
>
> Matt
>
>

Try the following:

 exec dbms_stats.gather_schema_stats( -

          ownname => USER,          - 
          estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, - 
          method_opt => 'FOR ALL INDEXED COLUMNS', - 
          granularity => 'ALL',  - 
          cascade => TRUE  - 
          );