查看AWR报告，top sql by buffer gets居然是一条insert  into table values语句，信息显示平均一次insert需要消耗1223 buffer gets

SQL ordered by Gets

• Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.
• Total Buffer Gets: 586,359,919
• Captured SQL account for 92.9% of Total

首先登陆查看该表结构，并没有lob字段，平均一行长度142bytes； 该表没有trigger，倒是有10个索引 ，但这也不足以导致这么高的buffer gets

该sql在V$sql中已经不存在，查看历史视图

SQL> select snap_id,ROWS_PROCESSED_TOTAL,ROWS_PROCESSED_DELTA,BUFFER_GETS_TOTAL,BUFFER_GETS_DELTA from DBA_HIST_SQLSTAT where SQL_ID='8pvcmmdkwrr2f' order by 1;

   SNAP_ID ROWS_PROCESSED_TOTAL ROWS_PROCESSED_DELTA BUFFER_GETS_TOTAL BUFFER_GETS_DELTA

---------- -------------------- -------------------- ----------------- -----------------

     29686             11106700             11106700         109949867         109949867

     29687             24558700             13452000         252722976         142773109

     29688             54874300             30307400         619604557         366786892

     29689             58911255              4036855         673206600          53600744

AWR做的是29687-29689时段，计算这一时段的累积buffer gets和row processed

SQL> select 13452000+30307400+4036855 from dual;

13452000+30307400+4036855

-------------------------

                 47796255

SQL> select 142773109+366786892+53600744 from dual;

142773109+366786892+53600744

----------------------------

                   563160745

SQL> select 563160745/47796255 from dual;

563160745/47796255

------------------

        11.7825287

平均下来一次insert消耗11个逻辑读 这就比较正常了

该sql应该是应用了batchjob，每100行提交一次，所以awr中才会显示这么高的Gets per Exec