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.comOn Tue, Aug 30, 2011 at 12:27 PM, D'Hooge Freek <Freek.DHooge@xxxxxxxxx <mailto: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 <mailto:freek.dhooge@xxxxxxxxx> 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@xxxxxxxxxxxxx <mailto:oracle-l-bounce@xxxxxxxxxxxxx> [mailto: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