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@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on behalf
of Stefan Koehler <contact@xxxxxxxx>
Sent: 03 May 2017 07:43
To: Amir.Hameed@xxxxxxxxx; 'ORACLE-L'
Subject: Re: Buffer Gets question
Hello Amir,
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: @OracleSK
Upcoming online seminar: http://tinyurl.com/17-06-13-Shared-Pool-Internals
--
//www.freelists.org/webpage/oracle-l
