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
