Oracle数据库服务器CPU持续100%之等待事件asynch descriptor resize

2017年5月11日下午13点，一地市技术反应服务器响应慢，CPU长时间100%如图：

经观察数据库服务器资源监视器，发现是oracle进程导致的，登录数据库查询数据库等待事件，发现 asynch descriptor resize居高不下

按照等待事件类型查询对应的会话信息如下，有30多个会话同时执行同一条sql语句： b7rng1bdrzzkq

查询sql语句 b7rng1bdrzzkq对应的sql文本如下：

b7rng1bdrzzkq的执行计划如下：

PLAN_TABLE_OUTPUT 
--------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID b7rng1bdrzzkq, child number 0 
------------------------------------- 
select zdb.HOSPITAL_ID, 
zdh.HOSPITAL_NAME, 
zdb.hisid, 
zdb.HS_PATIENT_NAME as 
PATIENT_NAME, 
hs_area_code as 
DEPTNAME, 
admission_disease_name as 
RULE_NAME, 
round(zdb.TOTAL_COST/10000,4) as Total_Costs 
from zk_dw_bill zdb, zk_dim_hospital zdh 
where zdb.HOSPITAL_ID = zdh.HOSPITAL_ID_SZ 
and exists 
(select 1 from 
zk_dw_billdetail zdbd where zdb.HISID = zdbd.pid 
AND zdbd.item_date >= 
to_date('2017/5/11 00:00:00', 'yyyy/mm/dd HH24:mi:ss') 
AND zdbd.item_date <= 
to_date('2017/5/11 23:59:59', 'yyyy/mm/dd HH24: 
Plan hash value: 32811706 
------------------------------------------------------------------------------------------------------------------------------- 
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | 
------------------------------------------------------------------------------------------------------------------------------- 
| 0 | SELECT STATEMENT | | | | 13 (100)| | | | 
| 1 | SORT ORDER BY | | 1 | 424 | 13 (24)| 00:00:01 | | | 
| 2 | NESTED LOOPS | | | | | | | | 
| 3 | NESTED LOOPS | | 1 | 424 | 12 (17)| 00:00:01 | | | 
| 4 | MERGE JOIN CARTESIAN | | 1 | 362 | 12 (17)| 00:00:01 | | | 
| 5 | MERGE JOIN CARTESIAN | | 1 | 296 | 6 (17)| 00:00:01 | | | 
| 6 | SORT UNIQUE | | 1 | 261 | 2 (0)| 00:00:01 | | | 
| 7 | PARTITION RANGE SINGLE | | 1 | 261 | 2 (0)| 00:00:01 | 862 | 862 | 
|* 8 | TABLE ACCESS FULL | CLAIMDETAILHOSPITAL | 1 | 261 | 2 (0)| 00:00:01 | 862 | 862 | 
| 9 | BUFFER SORT | | 31 | 1085 | 4 (25)| 00:00:01 | | | 
| 10 | TABLE ACCESS FULL | DW_ZD_HOSPITAL_YB | 31 | 1085 | 3 (0)| 00:00:01 | | | 
| 11 | BUFFER SORT | | 1 | 66 | 9 (23)| 00:00:01 | | | 
| 12 | VIEW | VW_SQ_1 | 1 | 66 | 6 (17)| 00:00:01 | | | 
| 13 | HASH UNIQUE | | 1 | 2083 | | | | | 
|* 14 | HASH JOIN | | 1 | 2083 | 6 (17)| 00:00:01 | | | 
| 15 | PARTITION RANGE SINGLE | | 1 | 2077 | 2 (0)| 00:00:01 | 862 | 862 | 
|* 16 | TABLE ACCESS FULL | AUDITRESULT4HOSPITAL | 1 | 2077 | 2 (0)| 00:00:01 | 862 | 862 | 
| 17 | TABLE ACCESS FULL | GZ_LIST | 29 | 174 | 3 (0)| 00:00:01 | | | 
|* 18 | INDEX UNIQUE SCAN | PK_CLAIMHOSPITAL_HISID | 1 | | 0 (0)| | | | 
|* 19 | TABLE ACCESS BY GLOBAL INDEX ROWID| CLAIMHOSPITAL | 1 | 62 | 0 (0)| | ROWID | ROWID | 
------------------------------------------------------------------------------------------------------------------------------- 
Query Block Name / Object Alias (identified by operation id): 
------------------------------------------------------------- 
1 - SEL$817CBF02 
8 - SEL$817CBF02 / CLAIMDETAILHOSPITAL@SEL$5 
10 - SEL$817CBF02 / DW_ZD_HOSPITAL_YB@SEL$3 
12 - SEL$A7D54A5B / VW_SQ_1@SEL$E4B10583 
13 - SEL$A7D54A5B 
16 - SEL$A7D54A5B / AA@SEL$7 
17 - SEL$A7D54A5B / GL@SEL$7 
18 - SEL$817CBF02 / CLAIMHOSPITAL@SEL$2 
19 - SEL$817CBF02 / CLAIMHOSPITAL@SEL$2 
Outline Data 
------------- 
/*+ 
BEGIN_OUTLINE_DATA 
IGNORE_OPTIM_EMBEDDED_HINTS 
OPTIMIZER_FEATURES_ENABLE('11.2.0.1') 
DB_VERSION('11.2.0.1') 
OPT_PARAM('_optimizer_use_feedback' 'false') 
ALL_ROWS 
OUTLINE_LEAF(@"SEL$A7D54A5B") 
OUTLINE_LEAF(@"SEL$817CBF02") 
UNNEST(@"SEL$68B588A0") 
UNNEST(@"SEL$7286615E") 
OUTLINE(@"SEL$68B588A0") 
MERGE(@"SEL$7") 
OUTLINE(@"SEL$E4B10583") 
OUTLINE(@"SEL$7286615E") 
MERGE(@"SEL$5") 
OUTLINE(@"SEL$6") 
OUTLINE(@"SEL$7") 
OUTLINE(@"SEL$5428C7F1") 
MERGE(@"SEL$2") 
MERGE(@"SEL$3") 
OUTLINE(@"SEL$4") 
OUTLINE(@"SEL$5") 
OUTLINE(@"SEL$1") 
OUTLINE(@"SEL$2") 
OUTLINE(@"SEL$3") 
FULL(@"SEL$817CBF02" "CLAIMDETAILHOSPITAL"@"SEL$5") 
FULL(@"SEL$817CBF02" "DW_ZD_HOSPITAL_YB"@"SEL$3") 
NO_ACCESS(@"SEL$817CBF02" "VW_SQ_1"@"SEL$E4B10583") 
INDEX(@"SEL$817CBF02" "CLAIMHOSPITAL"@"SEL$2" ("CLAIMHOSPITAL"."HISID")) 
LEADING(@"SEL$817CBF02" "CLAIMDETAILHOSPITAL"@"SEL$5" "DW_ZD_HOSPITAL_YB"@"SEL$3" "VW_SQ_1"@"SEL$E4B10583" 
"CLAIMHOSPITAL"@"SEL$2") 
USE_MERGE_CARTESIAN(@"SEL$817CBF02" "DW_ZD_HOSPITAL_YB"@"SEL$3") 
USE_MERGE(@"SEL$817CBF02" "VW_SQ_1"@"SEL$E4B10583") 
USE_NL(@"SEL$817CBF02" "CLAIMHOSPITAL"@"SEL$2") 
NLJ_BATCHING(@"SEL$817CBF02" "CLAIMHOSPITAL"@"SEL$2") 
FULL(@"SEL$A7D54A5B" "AA"@"SEL$7") 
FULL(@"SEL$A7D54A5B" "GL"@"SEL$7") 
LEADING(@"SEL$A7D54A5B" "AA"@"SEL$7" "GL"@"SEL$7") 
USE_HASH(@"SEL$A7D54A5B" "GL"@"SEL$7") 
USE_HASH_AGGREGATION(@"SEL$A7D54A5B") 
END_OUTLINE_DATA 
*/ 

Predicate Information (identified by operation id): 
--------------------------------------------------- 
8 - filter("ITEM_DATE"<=TO_DATE(' 2017-05-11 23:59:59', 'syyyy-mm-dd hh24:mi:ss')) 
14 - access("GL"."ID"=TO_NUMBER("AA"."RULECODE")) 
16 - filter("AA"."ITEM_DATE"<=TO_DATE(' 2017-05-11 23:59:59', 'syyyy-mm-dd hh24:mi:ss')) 
18 - access("HISID"="PID") 
filter("ITEM_1"="HISID") 
19 - filter("HOSPITAL_ID"="HOSPITAL_ID_SZ") 
Column Projection Information (identified by operation id): 
----------------------------------------------------------- 
1 - (#keys=1) INTERNAL_FUNCTION("SETTLE_DATE")[7], "HOSPITAL_ID"[VARCHAR2,128], "HOSPITAL_NAME"[VARCHAR2,128], 
"HISID"[VARCHAR2,128], "HS_PATIENT_NAME"[VARCHAR2,200], "HS_AREA_CODE"[VARCHAR2,100], 
"ADMISSION_DISEASE_NAME"[VARCHAR2,128], ROUND("TOTAL_COST"/10000,4)[22] 
2 - "HOSPITAL_NAME"[VARCHAR2,128], "HISID"[VARCHAR2,128], "HOSPITAL_ID"[VARCHAR2,128], 
"ADMISSION_DISEASE_NAME"[VARCHAR2,128], "HS_AREA_CODE"[VARCHAR2,100], "TOTAL_COST"[NUMBER,22], 
"HS_PATIENT_NAME"[VARCHAR2,200], "SETTLE_DATE"[DATE,7] 
3 - "HOSPITAL_ID_SZ"[VARCHAR2,128], "HOSPITAL_NAME"[VARCHAR2,128], "CLAIMHOSPITAL".ROWID[ROWID,10], 
"HISID"[VARCHAR2,128] 
4 - "PID"[VARCHAR2,500], "HOSPITAL_ID_SZ"[VARCHAR2,128], "HOSPITAL_NAME"[VARCHAR2,128], "ITEM_1"[VARCHAR2,128] 
5 - "PID"[VARCHAR2,500], "HOSPITAL_ID_SZ"[VARCHAR2,128], "HOSPITAL_NAME"[VARCHAR2,128] 
6 - (#keys=1) "PID"[VARCHAR2,500] 
7 - "PID"[VARCHAR2,500] 
8 - "PID"[VARCHAR2,500] 
9 - (#keys=0) "HOSPITAL_ID_SZ"[VARCHAR2,128], "HOSPITAL_NAME"[VARCHAR2,128] 
10 - "HOSPITAL_ID_SZ"[VARCHAR2,128], "HOSPITAL_NAME"[VARCHAR2,128] 
11 - (#keys=0) "ITEM_1"[VARCHAR2,128] 
12 - "ITEM_1"[VARCHAR2,128] 
13 - "AA"."CLAIM_ID"[VARCHAR2,128] 
14 - (#keys=1) "AA"."CLAIM_ID"[VARCHAR2,128] 
15 - "AA"."CLAIM_ID"[VARCHAR2,128], "AA"."RULECODE"[VARCHAR2,4000] 
16 - "AA"."CLAIM_ID"[VARCHAR2,128], "AA"."RULECODE"[VARCHAR2,4000] 
17 - "GL"."ID"[NUMBER,22] 
18 - "CLAIMHOSPITAL".ROWID[ROWID,10], "HISID"[VARCHAR2,128] 
19 - "HOSPITAL_ID"[VARCHAR2,128], "ADMISSION_DISEASE_NAME"[VARCHAR2,128], "HS_AREA_CODE"[VARCHAR2,100], 
"TOTAL_COST"[NUMBER,22], "HS_PATIENT_NAME"[VARCHAR2,200], "SETTLE_DATE"[DATE,7] 
已选择144行。

由于服务器CPU100%，响应极慢，由于是select查询语句，与地市技术人员沟通后，决定查杀等待事件 asynch descriptor resize对应的会话进程：