Re: 10g System statistics - single and multi

From: Paul Drake <bdbafh_at_gmail.com>
Date: Tue, 17 May 2005 16:18:04 -0400
Message-ID: <910046b4050517131839f8355e@mail.gmail.com>

On 5/17/05, Christo Kutrovsky <kutrovsky.oracle_at_gmail.com> wrote:
> Hello,

>=20

> I've been diagnosing some mis-used full table scans vs index access.

>=20

> Part of my troubleshooting involved trying to use system statistics to
> force oracle to consider full table scans more expensive.

>=20

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

>=20

> This puzzled me a lot and I played arround with the values.

>=20

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

>=20

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

>=20

> However Oracle doesn't handle this case at all. I find this to be a
> severe limitation. What do you think ?

>=20

> This is 10.1.0.4 RAC/ASM test environment.

>=20

> --=3D20
> Christo Kutrovsky
> Database/System Administrator
> The Pythian Group
> --
> http://www.freelists.org/webpage/oracle-l
>=20

Christo,

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.

Paul

SQL> select * from sys.aux_stats$;

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 13 rows selected.

SQL> show parameter db_file_multiblock_read_count

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----
db_file_multiblock_read_count        integer     16

SQL> show parameter compatible

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.

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

Received on Tue May 17 2005 - 16:22:44 CDT