Re: 10g System statistics - single and multi
- From: Christo Kutrovsky <kutrovsky.oracle@xxxxxxxxx>
- To: Paul Drake <bdbafh@xxxxxxxxx>, Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>
- Date: Tue, 17 May 2005 16:50:22 -0400
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@xxxxxxxxx> wrote:
> On 5/17/05, Christo Kutrovsky <kutrovsky.oracle@xxxxxxxxx> 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
> > --
> > //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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
