Re: Buffer Gets question

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_at_xerox.com> 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
>
>
>

--
http://www.freelists.org/webpage/oracle-l