Re: performance issue after upgrade from 10.2.0.5 to 11.2.0.2
- 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
section.
http://hoopercharles.wordpress.com/2010/06/17/_small_table_threshold-parameter-and-buffer-cache-what-is-wrong-with-this-quote/
Regards,
Saurabh Manroy
http://smanroy.wordpress.com
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
> http://www.uptime.be
> disclaimer: www.uptime.be/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
> To: ORACLE-L
> Subject: performance issue after upgrade from 10.2.0.5 to 11.2.0.2
>
> Hi,
> I upgraded database from 10.2.0.5 to 11.2.0.2.2 on AIX 5.3 platform
> without problem. Everything works perfectly.
> During performance testing we noticed much slower queries.
> We have two databases, one 10.2.0.5 and 11.2.0.2. 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--
> //www.freelists.org/webpage/oracle-l
>
Other related posts:
