It seems that the sql plans aren't identical - plan_hash_value 3209742519
vs. 209742519...
From: "Hameed, Amir" <Amir.Hameed@xxxxxxxxx>
To: 'ORACLE-L' <oracle-l@xxxxxxxxxxxxx>,
Date: 03. 05. 2017 04:39
Subject: Buffer Gets question
Sent by: oracle-l-bounce@xxxxxxxxxxxxx
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
