Re: performance issue after upgrade from to

  • From: saurabh manroy <saurabhmanroy@xxxxxxxxx>
  • To: "pawel.smolarz@xxxxxxxxxx" <pawel.smolarz@xxxxxxxxxx>
  • Date: Tue, 30 Aug 2011 12:43:33 +0200

From description, it looks like : 10949 event has same impact as the
parameter _serial_direct_read.
Its likely that, the behavior of parameter "_serial_direct_read" coupled
with "_small_table_threshold" parameter in 11gR2 triggers the use direct
path reads. Both parameters were present in earlier versions of Oracle, but
in 11gR2 the behavior is different. You may like to have a look at following
blog by Charles Hooper and testcase posted by Kyle Hailey in comments

Saurabh Manroy

On Tue, Aug 30, 2011 at 12:27 PM, D'Hooge Freek <Freek.DHooge@xxxxxxxxx>wrote:

> Pawel,
> In 11.2, the db has the possibility to send the blocks from a full table
> scan directly to the server process memory (pga), bypassing the buffer
> cache.
> This would explain the higher "db direct read" you are seeing.
> I also have the impression that this can cause a higer pga usage on 11g
> then on 10g.
> Regards,
> Freek D'Hooge
> Uptime
> Oracle Database Administrator
> email: freek.dhooge@xxxxxxxxx
> tel +32(0)3 451 23 82
> disclaimer:
> -----Original Message-----
> From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
> On Behalf Of Pawel Smolarz
> Sent: dinsdag 30 augustus 2011 12:05
> Subject: performance issue after upgrade from to
> Hi,
> I upgraded database from to on AIX 5.3 platform
> without problem. Everything works perfectly.
> During performance testing we noticed much slower queries.
> We have two databases, one and Both databases are the
> same (data and parameters). The first 10g based on physical lun, second
> 11g based on snapshot from physical lun.
> After investigation I found that 11g execution plan is different than on
> 10g. OK. No problem. I saved execution plan from 10g (sqlset) and loaded
> to 11g, and on both databases execution plan is the same.
> But. On 11g I see that optimizer prefers sequential read and db direct
> read  than scattered read (10g). This causes the query execution time is
> longer- ~ 4x. Maybe the reason is snapshot??.
> We solved this problem by setting the events '10949', but maybe someone
> knows where is the cause.
> --
> Pozdrawiam / Regards,
> Paweł Smolarz
> 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--

Other related posts: