接到同事电话之后，反馈说数据库CPU占用率居高不下， 上午9点以后基本在90%以上。零售前端操作较卡。
第一反应获取了一份AWR报告。

报告显示，DB Time与平时相比，达到了2，3倍之多。


从中可以看到有严重的I.O等待事件。
read by other session事件的相关信息：
转自：http://blog.csdn.net/tianlesoftware/article/details/7734551

关于Read by other session 的说明如下：
When informationis requested from the database, Oracle will first read the data from disk intothe database buffer cache. If two or more sessions request the sameinformation, the first session will read the data into the buffer cache whileother sessions wait. In previous versions this wait was classified under the"buffer busy waits" event. However, in Oracle 10.1 and higher thiswait time is now broken out into the "read by other session" waitevent. Excessive waits for this event are typically due to several processesrepeatedly reading the same blocks, e.g. many sessions scanning the same indexor performing full table scans on the same table. Tuning this issue is a matterof finding and eliminating this contention.
--当我们请求一个数据时，Oracle 第一次会从磁盘将数据读入buffer cache。如果有两个或者多个session 请求相同的信息，那么第一个session 会将这个信息读入buffer cache，其他的session 就会出现等待。 在10g之前，该等待事件还是在bufferbusy waits 之下，在Oracle 10g之后，单独将该事件拿出并命令为read by other session。
一般来说出现这种等待事件是因为多个进程重复的读取相同的blocks，比如一些session 扫描相同的index或者在相同的block上执行full table scan。解决这个等待事件最好是找到并优化相关的SQL语句。
 
       Readby other session 等待的出现也说明数据库存在读的竞争，所以该等待事件通常和db file sequential read或db file scattered read 同时出现。

但是从AWR报告当中未能定位到相关的Top SQL。

于是尝试查询视图：
v$session_wait, 从中也能够定位到大量的read by other session的相关等待。

紧接着，使用从v$session_wait中定位的sid, serial#信息定位到了几个SQL语句。
如：
SELECT (t_memcard_cash.cashno) AS cashno,
       (t_memcard_cash.memcardno) AS memcardno,
       (t_memcard_cash.busno) AS busno,
       (t_memcard_cash.cardlevel) AS cardlevel,
       (t_memcard_cash.integrala) AS integrala,
       (t_memcard_cash.integral) AS integral,
       (t_memcard_cash.status) AS status,
       (t_memcard_cash.execdate) AS execdate,
       (t_memcard_cash.createuser) AS createuser,
       (t_memcard_cash.createtime) AS createtime,
       (t_memcard_cash.notes) AS notes,
       (t_memcard_cash.maxsaleno) AS maxsaleno,
       (t_memcard_cash.pstid) AS pstid,
       (t_memcard_cash.pstname) AS pstname,
       (t_memcard_cash.integral_pst) AS integral_pst,
       (t_memcard_cash.pstqty) AS pstqty,
       (t_memcard_cash.lastmodify) AS lastmodify,
       (t_memcard_cash.lasttime) AS lasttime,
       (t_memcard_cash.pstcode) AS pstcode,
       (t_memcard_cash.makeno) AS makeno,
       (t_memcard_cash.stallno) AS stallno,
       (t_memcard_cash.pstbatno) AS pstbatno,
       (t.cardholder) AS cardholder,
       (t.mobile) AS mobile,
       (t.tel) AS tel,
       (t.cardaddress) AS cardaddress,
       t_ware.lastpurprice AS lastpurprice,
       t_memcard_cash.compid
  FROM t_memcard_cash t_memcard_cash
 INNER JOIN t_memcard_reg t
    ON t_memcard_cash.memcardno = t.memcardno
  LEFT JOIN t_ware t_ware
    ON t_ware.compid = t_memcard_cash.compid
   AND t_ware.wareid = t_memcard_cash.pstid
 WHERE t_memcard_cash.memcardno = '000001034236';

SELECT t_memcard_reg.compid as compid,
  t_memcard_reg.memcardno as memcardno,
  t_memcard_reg.busno as busno,
  t_memcard_reg.introducer as introducer,
  t_memcard_reg.cardtype as cardtype,
  t_memcard_reg.cardlevel as cardlevel,
  t_memcard_reg.password as password,
  t_memcard_reg.cardstatus as cardstatus,
  t_memcard_reg.saleamount as saleamount,
  t_memcard_reg.realamount as realamount,
  t_memcard_reg.puramount as puramount,
  t_memcard_reg.integral as integral,
  t_memcard_reg.integrala as integrala,
  t_memcard_reg.integralflag as integralflag,
  t_memcard_reg.cardholder as cardholder,
  t_memcard_reg.cardaddress as cardaddress,
  t_memcard_reg.sex as sex,
  t_memcard_reg.tel as tel,
  t_memcard_reg.mobile as mobile,
  t_memcard_reg.fax as fax,
  t_memcard_reg.createuser as createuser,
  t_memcard_reg.createtime as createtime,
  t_memcard_reg.notes as notes,
  t_memcard_reg.idcard as idcard,
  t_memcard_reg.birthday as birthday,
  t_memcard_reg.allowintegral as allowintegral,
  t_memcard_reg.apptype as apptype,
  t_memcard_reg.applytime as applytime,
  t_memcard_reg.invalidate as invalidate,
  t_memcard_reg.lastdate as lastdate,
  t_memcard_reg.changeamt as changeamt,
  t_memcard_reg.lastmodify as lastmodify,
  t_memcard_reg.lasttime as lasttime,
  t_memcard_reg.invalidate as invalidate FROM h2.t_memcard_reg t_memcard_reg 
 WHERE ((memcardno='13220839717' or idcard = '13220839717' or tel = '13220839717'  or mobile = '13220839717' ));


 delete from temp_printtxt where saleno ='1608211018028696'

这三个SQL均是零售前端零售时需要调用的SQL。看到SQL之后，突然恍然大悟，因为系统前端的SQL大量采用的是硬编码，而并未采用绑定变量，
故而以上这几个SQL， 尽管相关表上都缺乏需要的索引，但是在AWR报告上不能反映出来。

解决方式其实很简单，针对这几个SQL创建合适的索引即可
create index h2.idx_t_memcard_reg_idcard on h2.t_memcard_reg(idcard) tablespace h2_busi_indx;