Re: System statistics in Oracle 10.2

"Ana Ribeiro" <ana.ribeiro_at_reflective.com> wrote in message news:1160048526.077190.288040_at_c28g2000cwb.googlegroups.com...
> 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
>

You will have been using system statistics in 10.1, it's just that they would be derived from the "noworkload" figures you quoted.

When I last looked, it appeared that Oracle was creating values as:

sreadtim = ioseektim + db_block_size / iotfrspeed mreadtim = ioseektim + dbfile_multiblock_read_count * db_block_size / iotfrspeed
MBRC = db_file_multiblock_read_count.

Compare these with the actual values when you gathered system stats, and it may go some way to explaining the change in plan.

-- 
Regards

Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html