Re: System statistics in Oracle 10.2

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

Thanks for your answer.
These were the values I found in Oracle 10.1:

      sreadtim = 10 + 8192 / 4096      = 12
      mreadtim = 10 + 16 * 8192 / 4096 = 42
      MBRC = 16

In Oracle 10.2 I have:
      SREADTIM = 3.107
      MREADTIM = 18.703
      MBRC     = 14

The SREADTIM and MREADTIM values are not very close. But some changes also may have been applied to the environment and now it is difficult to track the exact statistics that I had a couple of weeks ago... I will keep this formula in my notes and will carefully check if the problem happens again.
Many thanks,
Ana

Jonathan Lewis wrote:
> "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
Received on Mon Oct 16 2006 - 05:57:04 CDT