业务说恒生自营估值系统有性能问题：
分析awr top 1 是 read by other session
关于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,发现就是因为执行计划不正确导致的多个会话对同样的表进行全表扫描
（其中一个表没有统计信息，SQL还采用了动态采样）
SQL_ID 2mjjvcuhvu15g
--------------------
INSERT INTO TCJHB(L_BH,L_ZTBH,D_RQ,VC_GDDM,
VC_XWDM,L_YWLB,L_LBMXBH,L_SCLB, L_ZQNM,VC_ZQDM,
L_CJSL,EN_CJJG,EN_CJJE,VC_CJBM,VC_SQBH, C_MM,
EN_LX,L_TZLX,VC_YSZQDM,VC_BZ,C_SJLY,L_ZQLB,
VC_WTFS,VC_QSBH,L_QSSD,L_DZJYBZ) SELECT SEQ_TCJHB_ID.NEXTVAL,:B2 ,:B1
,C.ZQZH, C.XWH2,C.L_YWLB,-1,1, NVL(F.L_ZQNM,-1),NVL(F.VC_ZQDM,C.VC_ZQDM)
, ABS(C.CJSL),C.JG1,ABS(C.QSJE),C.JSBH,C.JSBH, C.MMBZ, 0,DECODE(:B7
,'1',DECODE(:B6 ,'1',:B5 ,D.L_TZLX),0),C.VC_ZQDM,C.VC_BZ,'001',NVL(F.L_Z
QLB,-1), '01',D.VC_QSBH,0,0 FROM (SELECT /*+ no_merge(b) leading(b) */
A.ZQDM1 VC_ZQDM,B.L_YWLB,B.VC_BZ,A.JSBH,A.MMBZ, MIN(A.ZQZH)
ZQZH,MIN(A.XWH2) XWH2,SUM(A.CJSL) CJSL,MAX(A.JG1) JG1,SUM(A.QSJE) QSJE
FROM (SELECT * FROM JSMX WHERE L_ZTBH = :B2 AND QSRQ = :B3 AND D_YWRQ =
:B1 AND JGDM = '0000') A , (SELECT T.* FROM V_JYQS_YWLBYJSMX T WHERE
L_YWLB IN (2604)) B WHERE NVL(TRIM(A.JLLX),'空') =
DECODE(B.JLLX,'任意',NVL(TRIM(A.JLLX),'空'),B.JLLX) AND
NVL(TRIM(A.JYFS),'空') = DECODE(B.JYFS,'任意',NVL(TRIM(A.JYFS),'空'),B.JYFS)
AND NVL(TRIM(A.YWLX),'空') = DECODE(B.YWLX,'任意',NVL(TRIM(A.YWLX),'空'),B.
YWLX) AND NVL(TRIM(A.QSBZ),'空') = DECODE(B.QSBZ,'任意',NVL(TRIM(A.QSBZ),'空
'),B.QSBZ) AND NVL(TRIM(A.GHLX),'空') =
DECODE(B.GHLX,'任意',NVL(TRIM(A.GHLX),'空'),B.GHLX) AND
NVL(TRIM(A.QYLB),'空') = DECODE(B.QYLB,'任意',NVL(TRIM(A.QYLB),'空'),B.QYLB)
AND NVL(TRIM(A.ZQLB),'空') = DECODE(B.ZQLB,'任意',NVL(TRIM(A.ZQLB),'空'),B.
ZQLB) AND NVL(TRIM(A.MMBZ),'空') = DECODE(B.MMBZ,'任意',NVL(TRIM(A.MMBZ),'空
'),B.MMBZ) GROUP BY A.ZQDM1,B.L_YWLB,B.VC_BZ, A.JSBH,A.MMBZ ) C,
(SELECT * FROM TGDZTDY WHERE L_ZTBH = :B2 AND L_SCLB = 1) D, (SELECT *
FROM TZQXX WHERE L_SCLB = 1 AND L_ZQLB = 15 AND D_JGRQ >= :B1 AND
D_FXRQ <= :B1 ) F WHERE ((:B4 = '1' AND C.ZQZH = D.VC_GDDM AND C.XWH2 =
DECODE(D.VC_BIND_SEAT,'任意',C.XWH2,D.VC_BIND_SEAT)) OR (:B4 = '2' AND
C.ZQZH = DECODE(D.VC_GDDM,'任意',C.ZQZH,D.VC_GDDM) AND C.XWH2 =
D.VC_BIND_SEAT) OR (:B4 = '3' AND C.ZQZH = D.VC_GDDM AND C.XWH2 =
D.VC_BIND_SEAT) ) AND C.VC_ZQDM = F.VC_ISIN(+)
Plan hash value: 4284221524
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | 33670 (100)| |
| 1 | LOAD TABLE CONVENTIONAL | | | | | |
| 2 | SEQUENCE | SEQ_TCJHB_ID | | | | |
| 3 | NESTED LOOPS OUTER | | 1 | 164 | 33670 (1)| 00:06:45 |
| 4 | NESTED LOOPS | | 1 | 132 | 33589 (1)| 00:06:44 |
| 5 | VIEW | | 1 | 100 | 33587 (1)| 00:06:44 |
| 6 | SORT GROUP BY | | 1 | 182 | 33587 (1)| 00:06:44 |
| 7 | NESTED LOOPS | | 1 | 182 | 33586 (1)| 00:06:44 |
| 8 | VIEW | | 70 | 4900 | 4 (0)| 00:00:01 |
| 9 | VIEW | V_JYQS_YWLBYJSMX | 70 | 4900 | 4 (0)| 00:00:01 |
| 10 | UNION-ALL | | | | | |
| 11 | FILTER | | | | | |
| 12 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 13 | FILTER | | | | | |
| 14 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 15 | FILTER | | | | | |
| 16 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 17 | FILTER | | | | | |
| 18 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 19 | FILTER | | | | | |
| 20 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 21 | FILTER | | | | | |
| 22 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 23 | FILTER | | | | | |
| 24 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 25 | FILTER | | | | | |
| 26 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 27 | FILTER | | | | | |
| 28 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 29 | FILTER | | | | | |
| 30 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 31 | FILTER | | | | | |
| 32 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 33 | FILTER | | | | | |
| 34 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 35 | FILTER | | | | | |
| 36 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 37 | FILTER | | | | | |
| 38 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 39 | FILTER | | | | | |
| 40 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 41 | FILTER | | | | | |
| 42 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 43 | FILTER | | | | | |
| 44 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 45 | FILTER | | | | | |
| 46 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 47 | FILTER | | | | | |
| 48 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 49 | FILTER | | | | | |
| 50 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 51 | FILTER | | | | | |
| 52 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 53 | FILTER | | | | | |
| 54 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 55 | FILTER | | | | | |
| 56 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 57 | FILTER | | | | | |
| 58 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 59 | FILTER | | | | | |
| 60 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 61 | FILTER | | | | | |
| 62 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 63 | FILTER | | | | | |
| 64 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 65 | FILTER | | | | | |
| 66 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 67 | FILTER | | | | | |
| 68 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 69 | FILTER | | | | | |
| 70 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 71 | FILTER | | | | | |
| 72 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 73 | FILTER | | | | | |
| 74 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 75 | FILTER | | | | | |
| 76 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 77 | FILTER | | | | | |
| 78 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 79 | FILTER | | | | | |
| 80 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 81 | FILTER | | | | | |
| 82 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 83 | FILTER | | | | | |
| 84 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 85 | FILTER | | | | | |
| 86 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 87 | FILTER | | | | | |
| 88 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 89 | FILTER | | | | | |
| 90 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 91 | FILTER | | | | | |
| 92 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 93 | FILTER | | | | | |
| 94 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 95 | FILTER | | | | | |
| 96 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 97 | FILTER | | | | | |
| 98 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 99 | FILTER | | | | | |
| 100 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 101 | FILTER | | | | | |
| 102 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 103 | FILTER | | | | | |
| 104 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 105 | FILTER | | | | | |
| 106 | TABLE ACCESS BY INDEX ROWID| TZQXX | 1 | 14 | 92 (0)| 00:00:02 |
| 107 | INDEX RANGE SCAN | IDX_TZQXX_UNIQUE | 263 | | 1 (0)| 00:00:01 |
| 108 | FILTER | | | | | |
| 109 | TABLE ACCESS BY INDEX ROWID| TZQXX | 1 | 14 | 92 (0)| 00:00:02 |
| 110 | INDEX RANGE SCAN | IDX_TZQXX_UNIQUE | 263 | | 1 (0)| 00:00:01 |
| 111 | FILTER | | | | | |
| 112 | TABLE ACCESS BY INDEX ROWID| TZQXX | 1 | 14 | 92 (0)| 00:00:02 |
| 113 | INDEX RANGE SCAN | IDX_TZQXX_UNIQUE | 263 | | 1 (0)| 00:00:01 |
| 114 | FILTER | | | | | |
| 115 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 116 | FILTER | | | | | |
| 117 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 118 | FILTER | | | | | |
| 119 | TABLE ACCESS BY INDEX ROWID| TZQXX | 2 | 24 | 92 (0)| 00:00:02 |
| 120 | INDEX RANGE SCAN | IDX_TZQXX_UNIQUE | 263 | | 1 (0)| 00:00:01 |
| 121 | FILTER | | | | | |
| 122 | TABLE ACCESS BY INDEX ROWID| TZQXX | 2 | 24 | 92 (0)| 00:00:02 |
| 123 | INDEX RANGE SCAN | IDX_TZQXX_UNIQUE | 263 | | 1 (0)| 00:00:01 |
| 124 | FILTER | | | | | |
| 125 | TABLE ACCESS BY INDEX ROWID| TZQXX | 2 | 24 | 92 (0)| 00:00:02 |
| 126 | INDEX RANGE SCAN | IDX_TZQXX_UNIQUE | 263 | | 1 (0)| 00:00:01 |
| 127 | FILTER | | | | | |
| 128 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 129 | FILTER | | | | | |
| 130 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 131 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 132 | FILTER | | | | | |
| 133 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 134 | FILTER | | | | | |
| 135 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 136 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 137 | FILTER | | | | | |
| 138 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 139 | FILTER | | | | | |
| 140 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 141 | FILTER | | | | | |
| 142 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 143 | FILTER | | | | | |
| 144 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 145 | FILTER | | | | | |
| 146 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 147 | FILTER | | | | | |
| 148 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 149 | FILTER | | | | | |
| 150 | TABLE ACCESS BY INDEX ROWID| TZQXX | 1 | 14 | 92 (0)| 00:00:02 |
| 151 | INDEX RANGE SCAN | IDX_TZQXX_UNIQUE | 263 | | 1 (0)| 00:00:01 |
| 152 | FILTER | | | | | |
| 153 | TABLE ACCESS BY INDEX ROWID| TZQXX | 1 | 14 | 92 (0)| 00:00:02 |
| 154 | INDEX RANGE SCAN | IDX_TZQXX_UNIQUE | 263 | | 1 (0)| 00:00:01 |
| 155 | FILTER | | | | | |
| 156 | TABLE ACCESS BY INDEX ROWID| TZQXX | 1 | 14 | 92 (0)| 00:00:02 |
| 157 | INDEX RANGE SCAN | IDX_TZQXX_UNIQUE | 263 | | 1 (0)| 00:00:01 |
| 158 | TABLE ACCESS FULL | JSMX | 1 | 112 | 480 (1)| 00:00:06 |
| 159 | TABLE ACCESS BY INDEX ROWID | TGDZTDY | 1 | 32 | 2 (0)| 00:00:01 |
| 160 | INDEX RANGE SCAN | PK_TGDZTDY | 2 | | 1 (0)| 00:00:01 |
| 161 | TABLE ACCESS BY INDEX ROWID | TZQXX | 1 | 32 | 81 (0)| 00:00:01 |
| 162 | INDEX RANGE SCAN | IDX_TZQXX_ZQLB | 1581 | | 4 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
转载于:https://blog.51cto.com/gldbhome/1346969
4466
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
