This presentation by Mauro may give some ideas of where to start looking:
https://www.slideshare.net/mobile/MauroPagano3/same-plan-different-performance
On 3 May 2017 04:39, "Hameed, Amir" <Amir.Hameed@xxxxxxxxx> wrote:
We have two environments, one production and the other a copy of
production (about three weeks old). These are Oracle EBS 12.1.3
environments and are setup identically in terms of SGA settings, optimizer
parameters, etc. The database version is 12.1.0.2. The OS is RHL 6. The H/W
is identical for both databases (UCS blades with 16-cores on each database
server).
The environment, which is a copy of production, is the load test
environment. There is a standard Oracle query which is part of the
Contracts module that is running optimally in production but is taking
almost five times longer in the load test environment. The query has the
same plan hash value and cost in both environments. Based on information in
the AWR report, 10046 trace and from V$SQL, the reason the statement is
running longer in the load-test environment is because it is doing a lot
more buffer gets in that environment than in production. Below are database
stats from both production and the load test environment:
*Production*
Disk Direct Buffer User IO
Wait Rows CPU Time Elapsed Physical SQL Plan
Optimzr
SQL Id Execs Reads Writes Gets Time
(micro) Processed (msecs) Time (msecs) Read (bytes) Hash
Value Cost
------------- -------- ------------ ----------- --------------
--------------- ----------- ---------- ------------ -------------------
----------- --------
8752wtxdch988 749 11,705 0 6,169,428
43,689,599 983,756 39,935 83,239 95,887,360
3209742519 45
*Load test*
Disk Direct Buffer User IO
Wait Rows CPU Time Elapsed Physical SQL Plan
Optimzr
SQL Id Execs Reads Writes Gets Time
(micro) Processed (msecs) Time (msecs) Read (bytes) Hash
Value Cost
------------- ---------- ------------ ----------- --------------
--------------- ----------- ----------- ------------ ------------------
----------- --------
8752wtxdch988 1,714 4,376 0 120,837,353
18,988,036 154,148 1,583,597 1,607,511
6,971,392 209742519 45
I am trying to understand why a statement with the same execution plan is
doing a lot more buffer gets in two different environments which are almost
identical in terms of data volume. I said “almost identical” because the
load test copy has slightly less volume than in production because it is
about 3 weeks old.
Thanks,
Amir
