AW: RE: Buffer Gets question

Hi Amir
I am on vacation and have only my mobile phone to look at.  It is difficult to analyse your sql text that way.However it seems to me that the source of the buffer get's  is one specif table. Some name like qp_price_attributes. I remember I was coming across a similar case once. One table was causing a lot of buffer get's.  When the table was reorganised the issue disappeared . It was only in one db, similar dbs did not show the issue. It was none of the obvious reasons like migrated rows or lobs stored outside of the row. Unfortunately I didn't have the time to do a proper research. 

Regards 
Lothar 

Von meinem Samsung Gerät gesendet.

• Ursprüngliche Nachricht -------- Von: "Hameed, Amir" <Amir.Hameed_at_xerox.com> Datum: 03.05.17 14:38 (GMT+01:00) An: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>, 'ORACLE-L' <oracle-l_at_freelists.org>, contact_at_soocs.de Betreff: RE: Buffer Gets question

I made a mistake while pasting statistics.  The query has the exact same plan hash value in both systems, which is 3209742519. The load profile is different in production and the load test environment in that the number of lines per contract in the latter is more. But, whatever we are testing in the load test environment is going to eventually start running in production in about two weeks, which means that we will most likely start seeing the same issue in production as well. We have a SEV-1 open with Oracle at the moment and their development is looking into the issue. I am attaching a file with the complete statement and statistics from TKP in case someone is interested in looking into this information.

-----Original Message-----

From: Jonathan Lewis [mailto:jonathan_at_jlcomp.demon.co.uk] Sent: Wednesday, May 03, 2017 4:03 AM
To: Hameed, Amir <Amir.Hameed_at_xerox.com>; 'ORACLE-L' <oracle-l_at_freelists.org>; contact_at_soocs.de Subject: Re: Buffer Gets question

Furthermore, while it's a clue based on far too little information, a larger number of rows for a smaller number of buffer gets has the flavour of Oracle switching to a hash join to get more data to avoid using a nested loop to acquire a "large" result set .

Regards
Jonathan Lewis

1. Your plan hash value is different between production and load test environment (3209742519 vs. 209742519)
2. 8237 avg buffer gets per execution vs. 70500 avg buffer gets per execution in regard to 1313 avg rows per execution vs. 90 avg rows per execution shows that you are doing something enormously different in both environments
3. But besides all this you also can have the same plan hash value but enormously different performance (e.g. different predicate section, hash collisions with scalar subquery caching for different data, etc.)

Best Regards
Stefan Koehler

Independent Oracle performance consultant and researcher Website: http://www.soocs.de
Twitter: _at_OracleSK
Upcoming online seminar: http://tinyurl.com/17-06-13-Shared-Pool-Internals

--

http://www.freelists.org/webpage/oracle-l Received on Wed May 03 2017 - 18:49:13 CEST