Re: System statistics in Oracle 10.2

Ana Ribeiro wrote:
> Hi,
> I have a question concerning SYSTEM statistics generation. When I ran
> the following query in Oracle 10.1, the response time was 1 second and
> the execution plan showed me that the right indexes were being used.
>
> select id from indObj where lower(attrname) = 'name' and
> id in (select id from classificationObject
> where contains(descriptorid, '12610') > 0
> intersect
> select distinct id from indexedObject
> where contains(value, (select my_vars.get_var_value from dual)) > 0)
> order by stringvalue;
>
> I have never gathered system statistics in my Oracle 10.1 database, as
> you can see in the query below:
> SQL> select pname, pval1 , sname from sys.aux_stats$ where sname =
> 'SYSSTATS_MAIN';
>
> PNAME PVAL1 SNAME
> ------------------------------ ----------
> ------------------------------
> CPUSPEED SYSSTATS_MAIN
> CPUSPEEDNW 904.86697 SYSSTATS_MAIN
> IOSEEKTIM 10 SYSSTATS_MAIN
> IOTFRSPEED 4096 SYSSTATS_MAIN
> MAXTHR SYSSTATS_MAIN
> MBRC SYSSTATS_MAIN
> MREADTIM SYSSTATS_MAIN
> SLAVETHR SYSSTATS_MAIN
> SREADTIM SYSSTATS_MAIN
>
> Then I installed Oracle 10.2 in my computer, imported the same schema,
> generated the same object statistics (not system stats!), and the same
> query was taking more than 1 hour to execute. I checked the query plan
> and I found full table scans being executed... After I generated SYSTEM
> statistics my problem was fixed: the query returned in 1 second, the
> query plan was perfect.
>
> Does anyone know if in Oracle 10.2 we are "obliged" to have the system
> statistics? Why didn't the same problem happen on my Oracle 10.1
> environment (with NO system stats)?
>
> Many thanks!
> Ana

I'm not sure about 'obliged' but it is certainly a very good idea. As Jonathan Lewis has indicated many times ... the better the quality of information you provide the CBO the more likely it will make good decisions.

With each release of Oracle tweaks are made to the CBO. Don't expect the rules to be the same from version to version.

-- 
Daniel Morgan
Puget Sound Oracle Users Group