Re: 10g System statistics - single and multi

From: Christo Kutrovsky <kutrovsky.oracle_at_gmail.com>
Date: Tue, 17 May 2005 16:50:22 -0400
Message-ID: <52a152eb0505171350640b121b@mail.gmail.com>

I just re-read by email, and I would like to add small correction. Sometimes i forget to put all the words that I am thinking about.

This:
> What i've discovered was something very strange. When I had both
> values to 1

"both" refers to "SREADTIM" and "MREADTIM". From this point i refer to "SINGLE" which is SREADTIM and "MULTI" which is MREADTIM.

Paul, yes i find that having multi been half of single is perfectly fine and valid. In fact, i would be surprised if it is not.

Ane the more I think about it, the more I think this was done as some kind of workarround, as Oracle would consider full table scans to be too cheap.

On 5/17/05, Paul Drake <bdbafh_at_gmail.com> wrote:
> On 5/17/05, Christo Kutrovsky <kutrovsky.oracle_at_gmail.com> wrote:
> > Hello,
> >
> > I've been diagnosing some mis-used full table scans vs index access.
> >
> > Part of my troubleshooting involved trying to use system statistics to
> > force oracle to consider full table scans more expensive.
> >
> > What i've discovered was something very strange. When I had both
> > values to 1, my cost of a FTS would be ~1400. When I set multi to 2,
> > the cost would go DOWN to ~1050. When I set it to 3, it would go back
> > to ~1400.
> >
> > This puzzled me a lot and I played arround with the values.
> >
> > What I've discovered is that when SINGLE >=3D3D MULTI, then oracle uses=
 N
> > * SINGLE for full table scans some formula independant from the value
> > of MULTI.
> >
> > In a way, this makes sense, as it is not possible to have 1 read to be
> > slower then MANY reads. But, this is an average value, thus it is
> > quite possible (and likelly on many systems) that multi-block reads
> > are faster then single block reads.
> >
> > However Oracle doesn't handle this case at all. I find this to be a
> > severe limitation. What do you think ?
> >
> > This is 10.1.0.4 RAC/ASM test environment.
> >
> > --=3D20
> > Christo Kutrovsky
> > Database/System Administrator
> > The Pythian Group
> > --
> > http://www.freelists.org/webpage/oracle-l
> >

>=20

> Christo,

>=20

> I've also seen the value for single to be twice that of multi in 10.1.0.3
> (non-RAC, ext3 filesystems).
> I wish that I had more detail as to what was occurring in the system
> during the gathering sysstats interval.
> This particular db should be on 10.1.0.4 (RHEL 3.0 ES Update 4) by the
> end of the week.

>=20

> Paul

>=20

> SQL> select * from sys.aux_stats$;

>=20


> SNAME           PNAME                PVAL1 PVAL2

> --------------- --------------- ---------- --------------------

> SYSSTATS_INFO   STATUS                     COMPLETED

> SYSSTATS_INFO   DSTART                     05-13-2005 15:24

> SYSSTATS_INFO   DSTOP                      05-13-2005 16:10

> SYSSTATS_INFO   FLAGS                    1

> SYSSTATS_MAIN   CPUSPEEDNW      589.797817

> SYSSTATS_MAIN   IOSEEKTIM               10

> SYSSTATS_MAIN   IOTFRSPEED            4096

> SYSSTATS_MAIN   SREADTIM             6.012

> SYSSTATS_MAIN   MREADTIM             3.112

> SYSSTATS_MAIN   CPUSPEED               581

> SYSSTATS_MAIN   MBRC                    17

> SYSSTATS_MAIN   MAXTHR            23611392

> SYSSTATS_MAIN SLAVETHR

>=20

> 13 rows selected.

>=20

> SQL> show parameter db_file_multiblock_read_count

>=20


> NAME                                 TYPE        VALUE

> ------------------------------------ ----------- -----

> db_file_multiblock_read_count        integer     16

>=20

> SQL> show parameter compatible

>=20


> NAME                                 TYPE        VALUE

> ------------------------------------ ----------- ----------

> compatible                           string      10.1.0.3.0

>=20

> --
> #/etc/init.d/init.cssd stop
> -- f=3Dma, divide by 1, convert to moles.
>=20

--=20
Christo Kutrovsky
Database/System Administrator
The Pythian Group

--
http://www.freelists.org/webpage/oracle-l

Received on Tue May 17 2005 - 16:55:21 CDT