经观察数据库服务器资源监视器，发现是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对应的会话进程：
如图所示，会话查杀后，服务器CPU恢复正常水平。 后期处理： 需要开发或实施测试人员优化对应的sql语句 网上有说关闭操作系统磁盘异步IO，由于时间关系来不及验证：alter system set disk_asynch_io=false scope=spfile;