Re: performance issue after upgrade from 10.2.0.5 to 11.2.0.2

From: Pawel Smolarz <pawel.smolarz_at_nordea.com>
Date: Tue, 30 Aug 2011 13:09:56 +0200
Message-ID: <4E5CC504.3070204_at_nordea.com>

Thanks!. This probably explains the problem. These are the test databases and a size of "SGA / buffer cache" is smaller (3GB) than on the production database. If the access method is dependent on the greater or less than 2%, it is likely it will not occur in production :)

Pozdrawiam / Regards,
Paweł Smolarz

W dniu 30.08.2011 12:43, saurabh manroy pisze:
> 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_at_uptime.be <mailto:Freek.DHooge_at_uptime.be>> 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_at_uptime.be <mailto:freek.dhooge_at_uptime.be>
> tel +32(0)3 451 23 82 <tel:%2B32%280%293%20451%2023%2082>
> http://www.uptime.be
> disclaimer: www.uptime.be/disclaimer <http://www.uptime.be/disclaimer>
>
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org
> <mailto:oracle-l-bounce_at_freelists.org>
> [mailto:oracle-l-bounce_at_freelists.org
> <mailto:oracle-l-bounce_at_freelists.org>] 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--
> http://www.freelists.org/webpage/oracle-l
>
>

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

Received on Tue Aug 30 2011 - 06:09:56 CDT