Re: many blocks read while unique index in use
From: Tanel Poder <tanel_at_tanelpoder.com>
Date: Thu, 14 Jul 2011 00:55:28 +0300
Message-ID: <CAMHX9JJC35um0axUYw2nezOTUX0A3oSc-q1JrQx3KrdVcakuwQ_at_mail.gmail.com>
9i has a prefetching problem, could be a bug.... in statspack, search for "prefetch clients" stats ... or query X$KCBKPFS (or run my prefetch.sql from tpt_public.zip) to see whether prefetching is happening:
Date: Thu, 14 Jul 2011 00:55:28 +0300
Message-ID: <CAMHX9JJC35um0axUYw2nezOTUX0A3oSc-q1JrQx3KrdVcakuwQ_at_mail.gmail.com>
9i has a prefetching problem, could be a bug.... in statspack, search for "prefetch clients" stats ... or query X$KCBKPFS (or run my prefetch.sql from tpt_public.zip) to see whether prefetching is happening:
SQL> _at_prefetch
ID NAME BLOCK_SIZE TIMESTAMP OPS BLOCKS
BLOCKS/OP
---------- -------------------- ---------- ---------- ---------- ----------
---------
3 DEFAULT 8192 4294119730 179 2141
12.0
3 DEFAULT 8192 4294125740 95 1460
15.4
3 DEFAULT 8192 4294408820 1744 30486
17.5
-- Tanel Poder http://blog.tanelpoder.com On Wed, Jul 13, 2011 at 12:30 PM, Remigiusz Sokolowski < remigiusz.sokolowski_at_nordea.com> wrote:Received on Wed Jul 13 2011 - 16:55:28 CDT
> hi,
>
> this is about 9.2.0.8 on windows
>
> Have You any idea what could be a reason for the following scenario?
>
> We have an update, which should use an unique index in order to perform
> that update. The average row length is 145.
>
> However statspack shows that during an update we read (and those are
> physical reads, buffer gets are even higher) sometimes 4000blks
> sometimes even 18000 per execution.
> The plan for this query is perfectly normal - uniqe scan - IO cost IIRC 3.
>
> What could be the reason for such anomaly? bad accounting?
>
> Regards
> Remigiusz
>
> --
> Pole nakazi
>
> ----------------------------------------------------------------------
> Remigiusz Sokolowski <remigiusz.sokolowski_at_nordea.com>
> pos : DBA at DIiUSI
> addr : Nordea Bank Polska SA, Luzycka 6A Street, 81-537 Gdynia, Poland
> phone : +48 58 667 17 43
> mobile: +48 602 42 42 77
> oraclh
> Nordea Bank Polska S.A. z siedzibą w Gdyni, ul. Kielecka 2, 81-303 Gdynia,
> wpisaną do Rejestru Przedsiębiorców Krajowego Rejestru Sądowego pod
> numerem: 0000021828,
> dla której dokumentację przechowuje Sąd Rejonowy Gdańsk - Północ w Gdańsku,
> VIII Wydział Gospodarczy Krajowego Rejestru Sądowego,
> o kapitale zakładowym i wpłaconym w wysokości: 277.493.500,00 złotych,
> NIP: 586-000-78-20, REGON: 190024711--
> http://www.freelists.org/webpage/oracle-l
>
>
>
-- http://www.freelists.org/webpage/oracle-l