RE: Buffer Gets question
Date: Wed, 3 May 2017 12:37:23 +0000
Message-ID: <AF02C941134B1A4AB5F61A726D08DCED202158DF_at_USA7109MB012.na.xerox.net>
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
From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Stefan Koehler <contact_at_soocs.de> Sent: 03 May 2017 07:43
To: Amir.Hameed_at_xerox.com; 'ORACLE-L' Subject: Re: Buffer Gets question
Hello Amir,
- Your plan hash value is different between production and load test environment (3209742519 vs. 209742519)
- 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
- 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-lReceived on Wed May 03 2017 - 14:37:23 CEST
- text/plain attachment: sql.txt