Received: (qmail 8804 invoked from network); 30 Aug 2011 05:45:15 -0500 Received: from freelists-180.iquest.net (HELO turing.freelists.org) (206.53.239.180) by static-ip-85-25-126-90.inaddr.ip-pool.com with SMTP; 30 Aug 2011 05:45:05 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id EE741E3D249; Tue, 30 Aug 2011 06:45:02 -0400 (EDT) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=freelists.org; s=turing; t=1314701103; bh=KYgxIyW+Vzp9lAY1GmuTImUE9YoTA7B02B+AJfZd wgE=; h=MIME-Version:In-Reply-To:References:Date:Message-ID:Subject: From:To:Cc:Content-Type:Sender:Reply-To:List-help:List-unsubscribe: List-Id:List-subscribe:List-owner:List-post:List-archive; b=TUbA/b txKPwbuzX1p5eDqlnbQj2rDzaJRdSvbN0YBSc4mj3IFu2zp2/mXX64TsCkbaV4oe2ap yPav4aSMubIw3OQShjIYPi5WUWUN6H2Nx+tFPtTg/zuU32A2k1DqqJVIdJTRvSF00iR s3TNv3u4w1trcRcAdXeE/y7ShE85nwE= X-Virus-Scanned: Debian amavisd-new at localhost.localdomain Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id sP8+0XzT1gnP; Tue, 30 Aug 2011 06:45:02 -0400 (EDT) Received: from turing.freelists.org (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 3A709E3D19E; Tue, 30 Aug 2011 06:44:18 -0400 (EDT) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=freelists.org; s=turing; t=1314701098; bh=KYgxIyW+Vzp9lAY1GmuTImUE9YoTA7B02B+AJfZd wgE=; h=MIME-Version:In-Reply-To:References:Date:Message-ID:Subject: From:To:Cc:Content-Type:Sender:Reply-To:List-help:List-unsubscribe: List-Id:List-subscribe:List-owner:List-post:List-archive; b=CDuO6K jdvgg//4Wg99IVfsdbbkI5PxtJxk2EsdW9hPvJWAjMFZVj7fQ1c+jIpuqN8j0RVcfiu FD4tDn/tWTGlkQySIouKotIqbDjac/kI8GkGcBxAQxzZwFy+oInf/0mXsobrHhVsoN2 D9kyBFFlOjbDnJV+51R8FcMU8MMdmU0= Received: with ECARTIS (v1.0.0; list oracle-l); Tue, 30 Aug 2011 06:43:37 -0400 (EDT) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 41E76E3CBD2 for ; Tue, 30 Aug 2011 06:43:36 -0400 (EDT) Authentication-Results: turing.freelists.org; dkim=pass (1024-bit key) header.i=@gmail.com Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id O8asivvFc+dv for ; Tue, 30 Aug 2011 06:43:36 -0400 (EDT) Received: from mail-iy0-f179.google.com (mail-iy0-f179.google.com [209.85.210.179]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id A8422E3B9D4 for ; Tue, 30 Aug 2011 06:43:35 -0400 (EDT) Received: by iamu38 with SMTP id u38so1464658iam.10 for ; Tue, 30 Aug 2011 03:43:35 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type; bh=sQXoWGCBTcq29Tiw1MKEgUpYazd9rzhnWGeI3dbjN/s=; b=YiKRDzwte2aKgAvw5gSBAZa4aL/5akOBWBmfS8NaZykSrJWW2tGdmzom9aSCNvle4s 5CyWoE9hdwKdbwd1wlts5lebMNsp7gPpH6T2Uf2/aUTJhfZsVYcP2PSzfvKaFDERlu/O xr65Y8tBt4Oh0JCee9GC7A00joDua8jPGeXiU= MIME-Version: 1.0 Received: by 10.231.85.37 with SMTP id m37mr10674372ibl.94.1314701013790; Tue, 30 Aug 2011 03:43:33 -0700 (PDT) Received: by 10.231.11.196 with HTTP; Tue, 30 Aug 2011 03:43:33 -0700 (PDT) In-Reply-To: <4814386347E41145AAE79139EAA39898150E4F4A02@ws03-exch07.iconos.be> References: <4E5CB5CF.3040003@nordea.com> <4814386347E41145AAE79139EAA39898150E4F4A02@ws03-exch07.iconos.be> Date: Tue, 30 Aug 2011 12:43:33 +0200 Message-ID: Subject: Re: performance issue after upgrade from 10.2.0.5 to 11.2.0.2 From: saurabh manroy To: "pawel.smolarz@nordea.com" Cc: =?UTF-8?B?T1JBQ0xFLUzvu7/vu7/vu7/vu78=?= , Freek.DHooge@uptime.be Content-Type: multipart/alternative; boundary=000e0cd5c83ee9b05204abb6ae67 X-archive-position: 38434 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: saurabhmanroy@gmail.com Precedence: normal Reply-To: saurabhmanroy@gmail.com List-help: List-unsubscribe: List-software: Ecartis version 1.0.0 List-Id: oracle-l X-List-ID: oracle-l List-subscribe: List-owner: List-post: List-archive: X-list: oracle-l --000e0cd5c83ee9b05204abb6ae67 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable 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 followin= g blog by Charles Hooper and testcase posted by Kyle Hailey in comments section. http://hoopercharles.wordpress.com/2010/06/17/_small_table_threshold-parame= ter-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 wro= te: > 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@uptime.be > tel +32(0)3 451 23 82 > http://www.uptime.be > disclaimer: www.uptime.be/disclaimer > > -----Original Message----- > From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org= ] > On Behalf Of Pawel Smolarz > Sent: dinsdag 30 augustus 2011 12:05 > To: ORACLE-L=EF=BB=BF=EF=BB=BF=EF=BB=BF=EF=BB=BF > 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=C5=82 Smolarz > > > Nordea Bank Polska S.A. z siedzib=C4=85 w Gdyni, ul. Kielecka 2, 81-303 G= dynia, > wpisan=C4=85 do Rejestru Przedsi=C4=99biorc=C3=B3w Krajowego Rejestru S= =C4=85dowego pod > numerem: 0000021828, > dla kt=C3=B3rej dokumentacj=C4=99 przechowuje S=C4=85d Rejonowy Gda=C5=84= sk - P=C3=B3=C5=82noc w Gda=C5=84sku, > VIII Wydzia=C5=82 Gospodarczy Krajowego Rejestru S=C4=85dowego, > o kapitale zak=C5=82adowym i wp=C5=82aconym w wysoko=C5=9Bci: 277.493.500= ,00 z=C5=82otych, > NIP: 586-000-78-20, REGON: 190024711-- > http://www.freelists.org/webpage/oracle-l > --000e0cd5c83ee9b05204abb6ae67 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable From description, it looks like : 10949 event has same impact as the parame= ter _serial_direct_read.
Its likely that, the behavior of parameter &qu= ot;_serial_direct_read" coupled with "_small_table_threshold"= ; parameter in 11gR2 triggers the use direct path reads. Both parameters we= re present in earlier versions of Oracle, but in 11gR2 the behavior is diff= erent. You may like to have a look at following blog by Charles Hooper and = testcase posted by Kyle Hailey in comments section.



Regards,
Saurabh Manroy



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

In 11.2, the db has the possibility to send the blocks from a full table sc= an 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 the= n on 10g.


Regards,


Freek D'Hooge
Uptime
Oracle Database Administrator
email: freek.dh= ooge@uptime.be
tel +32(0)3 451 23 82
http://www.uptime.be=
disclaimer: w= ww.uptime.be/disclaimer

-----Original Message-----
From: or= acle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org] On Behal= f Of Pawel Smolarz
Sent: dinsdag 30 augustus 2011 12:05
To: ORACLE-L=EF=BB=BF=EF=BB=BF=EF=BB=BF=EF=BB=BF
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 =C2=A0than 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 som= eone
knows where is the cause.

--
Pozdrawiam / Regards,
Pawe=C5=82 Smolarz


Nordea Bank Polska S.A. z siedzib=C4=85 w Gdyni, ul. Kielecka 2, 81-303 Gdy= nia,
wpisan=C4=85 do Rejestru Przedsi=C4=99biorc=C3=B3w Krajowego Rejestru S=C4= =85dowego pod numerem: 0000021828,
dla kt=C3=B3rej dokumentacj=C4=99 przechowuje S=C4=85d Rejonowy Gda=C5=84sk= - P=C3=B3=C5=82noc w Gda=C5=84sku,
VIII Wydzia=C5=82 Gospodarczy Krajowego Rejestru S=C4=85dowego,
o kapitale zak=C5=82adowym i wp=C5=82aconym w wysoko=C5=9Bci: 277.493.500,0= 0 z=C5=82otych,
NIP: 586-000-78-20, REGON: 190024711--
htt= p://www.freelists.org/webpage/oracle-l

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