Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: 10g System statistics - single and multi

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US