System statistics in Oracle 10.2

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 Received on Thu Oct 05 2006 - 06:42:06 CDT