ORACLE SQL調優之執行計劃與隱藏引數_complex_view_merging
最近，赤峰windows 版的11.2.0.3的oracle資料庫出現一條sql語句執行非常慢，需要1天的時間還出不來，但是觀察伺服器的IO和CPU都是很空閒，並且將
該sql語句涉及的物件全部匯出，然後分別匯入其他機器oracle資料庫與出現效能問題的資料庫，發現其他機器資料庫執行非常快而問題資料庫依舊很慢。嘗試
收集方案及表的統計資訊後，再次執行sql語句還是沒有改善，在問題處理的過程中，注意到一個很明顯的問題，就是不管收集統計資訊還是給sql加並行、使
用強制sql走hash，其執行計劃都是原來的巢狀迴圈執行計劃並且出現view字樣。最終定位到資料庫啟動初始化引數_complex_view_merging。
問題sql：
SELECT SUBSTR(F.PJ_CANTONCODE_CH, 0, 6) BMI_CODE,
A.HP_HOSREGISTERCODE_VC || REPLACE(M.MD_ICDCODE_VC, '.', '') || TO_CHAR(C.HR_INHOSDATE_DT, 'yyyyMMdd') || TO_CHAR(C.HR_OUTHOSDATE_DT, 'yyyyMMdd') || C.HR_COMPENSATIONTYPE_VC KEY_1,
A.HP_HOSREGISTERCODE_VC || REPLACE(M.MD_ICDCODE_VC, '.', '') || TO_CHAR(C.HR_INHOSDATE_DT, 'yyyyMMdd') || TO_CHAR(C.HR_OUTHOSDATE_DT, 'yyyyMMdd') || C.HR_COMPENSATIONTYPE_VC HISID,
B.HCM_SETTLEMENTDATE_DT BILLDATE,
C.HR_INSTITUTIONCODE_CH HOSPITAL_ID,
D.ME_INSTITUTINNAME_VC HOSPITAL_NAME,
C.HR_INSTITUTIONCODE_CH HOSPITAL_FEE_ID,
D.ME_INSTITUTINNAME_VC HOSPITAL_FEE_NAME,
C.HR_PERSONALCODE_VC PATIENT_ID,
C.HR_NAME PATIENT_NAME,
C.HR_COMPENSATIONTYPE_VC CLAIM_TYPE,
DECODE(NVL(C.HR_SEX, '-1'), '男', '1', '女', '0', '-1') PATIENT_SEX,
TO_DATE(NVL(E.PR_BRITHDAY_VC,
'1900-01-01'),
'yyyy-MM-dd') PATIENT_BIRTH,
M.MD_ICDCODE_VC ADMISSION_DISEASE_ID,
DECODE(C.HR_OUTHOSSTATUS_VC,
'1',
'治癒',
'2',
'好轉',
'3',
'未愈',
'4',
'死亡',
'9',
'其他',
'其他') DISCHARGE_REASON,
C.HR_INHOSDATE_DT ADMISSION_DATE,
C.HR_OUTHOSDATE_DT DISCHARGE_DATE,
C.HR_INHOSDATE_DT FIRST_DATE,
A.HP_HOSREGISTERCODE_VC || REPLACE(M.MD_ICDCODE_VC, '.', '') || TO_CHAR(C.HR_INHOSDATE_DT, 'yyyyMMdd') || TO_CHAR(C.HR_OUTHOSDATE_DT, 'yyyyMMdd') || C.HR_COMPENSATIONTYPE_VC BILL_NO,
SUM(NVL(A.HP_PRESCRIPTIONFEE_DEC, 0)) TOTAL_AMOUNT,
SUM(NVL(A.HP_ALLOWEDCOMP_DEC, 0)) BMI_CONVERED_AMOUNT,
A.HP_HOSREGISTERCODE_VC || REPLACE(M.MD_ICDCODE_VC, '.', '') ||
TO_CHAR(C.HR_INHOSDATE_DT, 'yyyyMMdd') || TO_CHAR(C.HR_OUTHOSDATE_DT,'yyyyMMdd') || C.HR_COMPENSATIONTYPE_VC TRADENO
FROM COMP_HOSPRESCRIPTION A
LEFT JOIN COMP_HOSCOSTMAIN B
ON A.HP_HOSREGISTERCODE_VC = B.HCM_HOSREGISTERCODE_VC
LEFT JOIN COMP_HOSREGISTER C
ON A.HP_HOSREGISTERCODE_VC = C.HR_HOSREGISTERCODE_VC
LEFT JOIN CFG_MEDICALESTABLISHMENT D
ON C.HR_INSTITUTIONCODE_CH = D.ME_INSTITUTIONCODE_VC
LEFT JOIN JOIN_PERSONALRECORD E
ON C.HR_PERSONALCODE_VC = E.PR_PERSONALCODE_VC
LEFT JOIN JOIN_PERSONAJOIN F
ON F.PJ_PERSONALCODE_VC = E.PR_PERSONALCODE_VC
LEFT JOIN CFG_MAINTAINDISEASE M
ON M.MD_DISEASEID_VC = C.HR_DISEASECODE_VC
WHERE TO_CHAR(B.HCM_SETTLEMENTDATE_DT, 'yyyy-MM-dd') > '2017-03-31'
AND TO_CHAR(B.HCM_SETTLEMENTDATE_DT, 'yyyy-MM-dd') < '2017-05-31'
AND A.DELETEFLAG_CH = 'N'
AND B.DELETEFLAG_CH = 'N'
AND C.DELETEFLAG_CH = 'N'
AND D.DELETEFLAG_CH = 'N'
AND M.DELETEFLAG_CH = 'N'
GROUP BY A.HP_HOSREGISTERCODE_VC || REPLACE(M.MD_ICDCODE_VC, '.', '') ||
TO_CHAR(C.HR_INHOSDATE_DT, 'yyyyMMdd') ||
TO_CHAR(C.HR_OUTHOSDATE_DT, 'yyyyMMdd') || C.HR_COMPENSATIONTYPE_VC,
B.HCM_SETTLEMENTDATE_DT,
F.PJ_CANTONCODE_CH,
C.HR_INSTITUTIONCODE_CH,
D.ME_INSTITUTINNAME_VC,
C.HR_INSTITUTIONCODE_CH,
D.ME_INSTITUTINNAME_VC,
C.HR_PERSONALCODE_VC,
C.HR_COMPENSATIONTYPE_VC,
C.HR_NAME,
C.HR_SEX,
E.PR_BRITHDAY_VC,
M.MD_ICDCODE_VC，C.HR_OUTHOSSTATUS_VC,
C.HR_INHOSDATE_DT,
C.HR_OUTHOSDATE_DT,
C.HR_INHOSDATE_DT;
其執行計劃：
Plan hash value: 1942484934
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 18E(100)| |
| 1 | HASH GROUP BY | | 25G| 6024G| 15E| 18E (0)|999:59:59 |
| 2 | VIEW | | 18E| 15E| | 18E (0)|999:59:59 |
| 3 | NESTED LOOPS | | 18E| 15E| | 18E (0)|999:59:59 |
| 4 | VIEW | | 441P| 15E| | 18E (0)|999:59:59 |
| 5 | NESTED LOOPS OUTER | | 441P| 15E| | 18E (0)|999:59:59 |
| 6 | VIEW | | 441P| 15E| | 112P (1)|999:59:59 |
| 7 | NESTED LOOPS OUTER | | 441P| 15E| | 112P (1)|999:59:59 |
| 8 | VIEW | | 12T| 2601T| | 8133G (1)|999:59:59 |
| 9 | NESTED LOOPS | | 12T| 2613T| | 8133G (1)|999:59:59 |
| 10 | VIEW | | 440G| 77T| | 624G (1)|999:59:59 |
| 11 | NESTED LOOPS | | 440G| 77T| | 624G (1)|999:59:59 |
| 12 | VIEW | | 889M| 43G| | 909M (1)|999:59:59 |
| 13 | NESTED LOOPS | | 889M| 43G| | 909M (1)|999:59:59 |
|* 14 | TABLE ACCESS FULL | COMP_HOSPRESCRIPTION | 1258K| 49M| | 11529 (1)| 00:02:19 |
|* 15 | VIEW | | 707 | 8484 | | 723 (1)| 00:00:09 |
|* 16 | TABLE ACCESS FULL| COMP_HOSCOSTMAIN | 707 | 16968 | | 723 (1)| 00:00:09 |
|* 17 | VIEW | | 495 | 69795 | | 700 (1)| 00:00:09 |
|* 18 | TABLE ACCESS FULL | COMP_HOSREGISTER | 495 | 75735 | | 700 (1)| 00:00:09 |
|* 19 | VIEW | | 29 | 899 | | 17 (0)| 00:00:01 |
|* 20 | TABLE ACCESS FULL | CFG_MEDICALESTABLISHMENT | 29 | 1102 | | 17 (0)| 00:00:01 |
| 21 | VIEW | | 34547 | 1551K| | 8824 (1)| 00:01:46 |
|* 22 | TABLE ACCESS FULL | JOIN_PERSONALRECORD | 34547 | 1012K| | 8824 (1)| 00:01:46 |
| 23 | VIEW | | 1 | 8 | | 15777 (1)| 00:03:10 |
|* 24 | TABLE ACCESS FULL | JOIN_PERSONAJOIN | 1 | 32 | | 15777 (1)| 00:03:10 |
|* 25 | VIEW | | 317 | 3170 | | 137 (1)| 00:00:02 |
|* 26 | TABLE ACCESS FULL | CFG_MAINTAINDISEASE | 317 | 5706 | | 137 (1)| 00:00:02 |
--------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$13
2 - SEL$27C75F45 / from$_subquery$_013@SEL$13
3 - SEL$27C75F45
4 - SEL$10 / from$_subquery$_011@SEL$12
5 - SEL$10
6 - SEL$8 / from$_subquery$_009@SEL$10
7 - SEL$8
8 - SEL$D28406B9 / from$_subquery$_007@SEL$8
9 - SEL$D28406B9
10 - SEL$E875D2C0 / from$_subquery$_005@SEL$6
11 - SEL$E875D2C0
12 - SEL$D186B18B / from$_subquery$_003@SEL$4
13 - SEL$D186B18B
14 - SEL$D186B18B / A@SEL$2
15 - SEL$1 / from$_subquery$_014@SEL$2
16 - SEL$1 / B@SEL$1
17 - SEL$3 / from$_subquery$_015@SEL$4
18 - SEL$3 / C@SEL$3
19 - SEL$5 / from$_subquery$_016@SEL$6
20 - SEL$5 / D@SEL$5
21 - SEL$7 / from$_subquery$_017@SEL$8
22 - SEL$7 / E@SEL$7
23 - SEL$9 / from$_subquery$_018@SEL$10
24 - SEL$9 / F@SEL$9
25 - SEL$11 / from$_subquery$_019@SEL$12
26 - SEL$11 / M@SEL$11
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
OPT_PARAM('_complex_view_merging' 'false')
OPT_PARAM('_optimizer_use_feedback' 'false')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
OUTLINE_LEAF(@"SEL$D186B18B")
OUTER_JOIN_TO_INNER(@"SEL$2" "from$_subquery$_014"@"SEL$2")
OUTLINE_LEAF(@"SEL$3")
OUTLINE_LEAF(@"SEL$E875D2C0")
OUTER_JOIN_TO_INNER(@"SEL$4" "from$_subquery$_015"@"SEL$4")
OUTLINE_LEAF(@"SEL$5")
OUTLINE_LEAF(@"SEL$D28406B9")
OUTER_JOIN_TO_INNER(@"SEL$6" "from$_subquery$_016"@"SEL$6")
OUTLINE_LEAF(@"SEL$7")
OUTLINE_LEAF(@"SEL$8")
OUTLINE_LEAF(@"SEL$9")
OUTLINE_LEAF(@"SEL$10")
OUTLINE_LEAF(@"SEL$11")
OUTLINE_LEAF(@"SEL$27C75F45")
OUTER_JOIN_TO_INNER(@"SEL$12" "from$_subquery$_019"@"SEL$12")
OUTLINE_LEAF(@"SEL$13")
OUTLINE(@"SEL$2")
OUTLINE(@"SEL$4")
OUTLINE(@"SEL$6")
OUTLINE(@"SEL$12")
NO_ACCESS(@"SEL$13" "from$_subquery$_013"@"SEL$13")
USE_HASH_AGGREGATION(@"SEL$13")
NO_ACCESS(@"SEL$27C75F45" "from$_subquery$_011"@"SEL$12")
NO_ACCESS(@"SEL$27C75F45" "from$_subquery$_019"@"SEL$12")
LEADING(@"SEL$27C75F45" "from$_subquery$_011"@"SEL$12" "from$_subquery$_019"@"SEL$12")
USE_NL(@"SEL$27C75F45" "from$_subquery$_019"@"SEL$12")
NO_ACCESS(@"SEL$10" "from$_subquery$_009"@"SEL$10")
NO_ACCESS(@"SEL$10" "from$_subquery$_018"@"SEL$10")
LEADING(@"SEL$10" "from$_subquery$_009"@"SEL$10" "from$_subquery$_018"@"SEL$10")
USE_NL(@"SEL$10" "from$_subquery$_018"@"SEL$10")
FULL(@"SEL$11" "M"@"SEL$11")
NO_ACCESS(@"SEL$8" "from$_subquery$_007"@"SEL$8")
NO_ACCESS(@"SEL$8" "from$_subquery$_017"@"SEL$8")
LEADING(@"SEL$8" "from$_subquery$_007"@"SEL$8" "from$_subquery$_017"@"SEL$8")
USE_NL(@"SEL$8" "from$_subquery$_017"@"SEL$8")
FULL(@"SEL$9" "F"@"SEL$9")
NO_ACCESS(@"SEL$D28406B9" "from$_subquery$_005"@"SEL$6")
NO_ACCESS(@"SEL$D28406B9" "from$_subquery$_016"@"SEL$6")
LEADING(@"SEL$D28406B9" "from$_subquery$_005"@"SEL$6" "from$_subquery$_016"@"SEL$6")
USE_NL(@"SEL$D28406B9" "from$_subquery$_016"@"SEL$6")
FULL(@"SEL$7" "E"@"SEL$7")
NO_ACCESS(@"SEL$E875D2C0" "from$_subquery$_003"@"SEL$4")
NO_ACCESS(@"SEL$E875D2C0" "from$_subquery$_015"@"SEL$4")
LEADING(@"SEL$E875D2C0" "from$_subquery$_003"@"SEL$4" "from$_subquery$_015"@"SEL$4")
USE_NL(@"SEL$E875D2C0" "from$_subquery$_015"@"SEL$4")
FULL(@"SEL$5" "D"@"SEL$5")
FULL(@"SEL$D186B18B" "A"@"SEL$2")
NO_ACCESS(@"SEL$D186B18B" "from$_subquery$_014"@"SEL$2")
LEADING(@"SEL$D186B18B" "A"@"SEL$2" "from$_subquery$_014"@"SEL$2")
USE_NL(@"SEL$D186B18B" "from$_subquery$_014"@"SEL$2")
FULL(@"SEL$3" "C"@"SEL$3")
FULL(@"SEL$1" "B"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
14 - filter("A"."DELETEFLAG_CH"='N')
15 - filter((TO_CHAR(INTERNAL_FUNCTION("B"."HCM_SETTLEMENTDATE_DT"),'yyyy-MM-dd')>'2017-03-31' AND
TO_CHAR(INTERNAL_FUNCTION("B"."HCM_SETTLEMENTDATE_DT"),'yyyy-MM-dd')<'2017-05-31' AND
"B"."DELETEFLAG_CH"='N'))
16 - filter("A"."HP_HOSREGISTERCODE_VC"="B"."HCM_HOSREGISTERCODE_VC")
17 - filter("C"."DELETEFLAG_CH"='N')
18 - filter("A"."HP_HOSREGISTERCODE_VC"="C"."HR_HOSREGISTERCODE_VC")
19 - filter("D"."DELETEFLAG_CH"='N')
20 - filter("C"."HR_INSTITUTIONCODE_CH"="D"."ME_INSTITUTIONCODE_VC")
22 - filter("C"."HR_PERSONALCODE_VC"="E"."PR_PERSONALCODE_VC")
24 - filter("F"."PJ_PERSONALCODE_VC"="E"."PR_PERSONALCODE_VC")
25 - filter("M"."DELETEFLAG_CH"='N')
26 - filter("M"."MD_DISEASEID_VC"="C"."HR_DISEASECODE_VC")
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "A"."HP_HOSREGISTERCODE_VC"||REPLACE("M"."MD_ICDCODE_VC",'.','')||TO_CHAR(INTERNAL_FUNCTION("C"."HR_I
NHOSDATE_DT"),'yyyyMMdd')||TO_CHAR(INTERNAL_FUNCTION("C"."HR_OUTHOSDATE_DT"),'yyyyMMdd')||"C"."HR_COMPENSATI
ONTYPE_VC"[51], "B"."HCM_SETTLEMENTDATE_DT"[DATE,7], "F"."PJ_CANTONCODE_CH"[VARCHAR2,12],
"C"."HR_INSTITUTIONCODE_CH"[VARCHAR2,14], "D"."ME_INSTITUTINNAME_VC"[VARCHAR2,50],
"C"."HR_INSTITUTIONCODE_CH"[VARCHAR2,14], "D"."ME_INSTITUTINNAME_VC"[VARCHAR2,50],
"C"."HR_PERSONALCODE_VC"[VARCHAR2,30], "C"."HR_COMPENSATIONTYPE_VC"[VARCHAR2,5],
"C"."HR_NAME"[VARCHAR2,50], "C"."HR_SEX"[VARCHAR2,5], "E"."PR_BRITHDAY_VC"[VARCHAR2,50],
"M"."MD_ICDCODE_VC"[VARCHAR2,10], "C"."HR_OUTHOSSTATUS_VC"[VARCHAR2,100], "C"."HR_INHOSDATE_DT"[DATE,7],
"C"."HR_OUTHOSDATE_DT"[DATE,7], "C"."HR_INHOSDATE_DT"[DATE,7], SUM(NVL("A"."HP_ALLOWEDCOMP_DEC",0))[22],
SUM(NVL("A"."HP_PRESCRIPTIONFEE_DEC",0))[22]
2 - "A"."HP_HOSREGISTERCODE_VC"[VARCHAR2,20], "A"."HP_PRESCRIPTIONFEE_DEC"[NUMBER,22],
"A"."HP_ALLOWEDCOMP_DEC"[NUMBER,22], "B"."HCM_SETTLEMENTDATE_DT"[DATE,7],
"C"."HR_PERSONALCODE_VC"[VARCHAR2,30], "C"."HR_NAME"[VARCHAR2,50], "C"."HR_SEX"[VARCHAR2,5],
"C"."HR_INSTITUTIONCODE_CH"[VARCHAR2,14], "C"."HR_COMPENSATIONTYPE_VC"[VARCHAR2,5],
"C"."HR_INHOSDATE_DT"[DATE,7], "C"."HR_OUTHOSSTATUS_VC"[VARCHAR2,100], "C"."HR_OUTHOSDATE_DT"[DATE,7],
"D"."ME_INSTITUTINNAME_VC"[VARCHAR2,50], "E"."PR_BRITHDAY_VC"[VARCHAR2,50],
"F"."PJ_CANTONCODE_CH"[VARCHAR2,12], "M"."MD_ICDCODE_VC"[VARCHAR2,10]
3 - "from$_subquery$_011"."HP_HOSREGISTERCODE_VC"[VARCHAR2,20],
"from$_subquery$_011"."HP_PRESCRIPTIONFEE_DEC"[NUMBER,22],
"from$_subquery$_011"."HP_ALLOWEDCOMP_DEC"[NUMBER,22],
"from$_subquery$_011"."HCM_SETTLEMENTDATE_DT"[DATE,7],
"from$_subquery$_011"."HR_PERSONALCODE_VC"[VARCHAR2,30], "from$_subquery$_011"."HR_NAME"[VARCHAR2,50],
"from$_subquery$_011"."HR_SEX"[VARCHAR2,5], "from$_subquery$_011"."HR_INSTITUTIONCODE_CH"[VARCHAR2,14],
"from$_subquery$_011"."HR_COMPENSATIONTYPE_VC"[VARCHAR2,5],
"from$_subquery$_011"."HR_INHOSDATE_DT"[DATE,7], "from$_subquery$_011"."HR_OUTHOSSTATUS_VC"[VARCHAR2,100],
"from$_subquery$_011"."HR_OUTHOSDATE_DT"[DATE,7], "from$_subquery$_011"."ME_INSTITUTINNAME_VC"[VARCHAR2,50],
"from$_subquery$_011"."PR_BRITHDAY_VC"[VARCHAR2,50],
"from$_subquery$_011"."PJ_CANTONCODE_CH"[VARCHAR2,12], "M"."MD_ICDCODE_VC"[VARCHAR2,10]
4 - "from$_subquery$_011"."HP_HOSREGISTERCODE_VC"[VARCHAR2,20],
"from$_subquery$_011"."HP_PRESCRIPTIONFEE_DEC"[NUMBER,22],
"from$_subquery$_011"."HP_ALLOWEDCOMP_DEC"[NUMBER,22],
"from$_subquery$_011"."HCM_SETTLEMENTDATE_DT"[DATE,7],
"from$_subquery$_011"."HR_PERSONALCODE_VC"[VARCHAR2,30], "from$_subquery$_011"."HR_NAME"[VARCHAR2,50],
"from$_subquery$_011"."HR_SEX"[VARCHAR2,5], "from$_subquery$_011"."HR_INSTITUTIONCODE_CH"[VARCHAR2,14],
"from$_subquery$_011"."HR_COMPENSATIONTYPE_VC"[VARCHAR2,5], "C"."HR_DISEASECODE_VC"[VARCHAR2,10],
"from$_subquery$_011"."HR_INHOSDATE_DT"[DATE,7], "from$_subquery$_011"."HR_OUTHOSSTATUS_VC"[VARCHAR2,100],
"from$_subquery$_011"."HR_OUTHOSDATE_DT"[DATE,7], "from$_subquery$_011"."ME_INSTITUTINNAME_VC"[VARCHAR2,50],
"from$_subquery$_011"."PR_BRITHDAY_VC"[VARCHAR2,50], "from$_subquery$_011"."PJ_CANTONCODE_CH"[VARCHAR2,12]
5 - "from$_subquery$_009"."HP_HOSREGISTERCODE_VC"[VARCHAR2,20],
"from$_subquery$_009"."HP_PRESCRIPTIONFEE_DEC"[NUMBER,22],
"from$_subquery$_009"."HP_ALLOWEDCOMP_DEC"[NUMBER,22],
"from$_subquery$_009"."HCM_SETTLEMENTDATE_DT"[DATE,7],
"from$_subquery$_009"."HR_PERSONALCODE_VC"[VARCHAR2,30], "from$_subquery$_009"."HR_NAME"[VARCHAR2,50],
"from$_subquery$_009"."HR_SEX"[VARCHAR2,5], "from$_subquery$_009"."HR_INSTITUTIONCODE_CH"[VARCHAR2,14],
"from$_subquery$_009"."HR_COMPENSATIONTYPE_VC"[VARCHAR2,5],
"from$_subquery$_009"."HR_DISEASECODE_VC"[VARCHAR2,10], "from$_subquery$_009"."HR_INHOSDATE_DT"[DATE,7],
"from$_subquery$_009"."HR_OUTHOSSTATUS_VC"[VARCHAR2,100], "from$_subquery$_009"."HR_OUTHOSDATE_DT"[DATE,7],
"from$_subquery$_009"."ME_INSTITUTINNAME_VC"[VARCHAR2,50],
"from$_subquery$_009"."PR_BRITHDAY_VC"[VARCHAR2,50], "F"."PJ_CANTONCODE_CH"[VARCHAR2,12]
6 - "from$_subquery$_009"."HP_HOSREGISTERCODE_VC"[VARCHAR2,20],
"from$_subquery$_009"."HP_PRESCRIPTIONFEE_DEC"[NUMBER,22],
"from$_subquery$_009"."HP_ALLOWEDCOMP_DEC"[NUMBER,22],
"from$_subquery$_009"."HCM_SETTLEMENTDATE_DT"[DATE,7],
"from$_subquery$_009"."HR_PERSONALCODE_VC"[VARCHAR2,30], "from$_subquery$_009"."HR_NAME"[VARCHAR2,50],
"from$_subquery$_009"."HR_SEX"[VARCHAR2,5], "from$_subquery$_009"."HR_INSTITUTIONCODE_CH"[VARCHAR2,14],
"from$_subquery$_009"."HR_COMPENSATIONTYPE_VC"[VARCHAR2,5],
"from$_subquery$_009"."HR_DISEASECODE_VC"[VARCHAR2,10], "from$_subquery$_009"."HR_INHOSDATE_DT"[DATE,7],
"from$_subquery$_009"."HR_OUTHOSSTATUS_VC"[VARCHAR2,100], "from$_subquery$_009"."HR_OUTHOSDATE_DT"[DATE,7],
"from$_subquery$_009"."ME_INSTITUTINNAME_VC"[VARCHAR2,50], "E"."PR_PERSONALCODE_VC"[VARCHAR2,18],
"from$_subquery$_009"."PR_BRITHDAY_VC"[VARCHAR2,50]
7 - "from$_subquery$_007"."HP_HOSREGISTERCODE_VC"[VARCHAR2,20],
"from$_subquery$_007"."HP_PRESCRIPTIONFEE_DEC"[NUMBER,22],
"from$_subquery$_007"."HP_ALLOWEDCOMP_DEC"[NUMBER,22],
"from$_subquery$_007"."HCM_SETTLEMENTDATE_DT"[DATE,7], "C"."HR_PERSONALCODE_VC"[VARCHAR2,30],
"from$_subquery$_007"."HR_NAME"[VARCHAR2,50], "from$_subquery$_007"."HR_SEX"[VARCHAR2,5],
"from$_subquery$_007"."HR_INSTITUTIONCODE_CH"[VARCHAR2,14],
"from$_subquery$_007"."HR_COMPENSATIONTYPE_VC"[VARCHAR2,5],
"from$_subquery$_007"."HR_DISEASECODE_VC"[VARCHAR2,10], "from$_subquery$_007"."HR_INHOSDATE_DT"[DATE,7],
"from$_subquery$_007"."HR_OUTHOSSTATUS_VC"[VARCHAR2,100], "from$_subquery$_007"."HR_OUTHOSDATE_DT"[DATE,7],
"from$_subquery$_007"."ME_INSTITUTINNAME_VC"[VARCHAR2,50], "E"."PR_BRITHDAY_VC"[VARCHAR2,50],
"E"."PR_PERSONALCODE_VC"[VARCHAR2,18]
8 - "from$_subquery$_007"."HP_HOSREGISTERCODE_VC"[VARCHAR2,20],
"from$_subquery$_007"."HP_PRESCRIPTIONFEE_DEC"[NUMBER,22],
"from$_subquery$_007"."HP_ALLOWEDCOMP_DEC"[NUMBER,22],
"from$_subquery$_007"."HCM_SETTLEMENTDATE_DT"[DATE,7], "C"."HR_PERSONALCODE_VC"[VARCHAR2,30],
"from$_subquery$_007"."HR_NAME"[VARCHAR2,50], "from$_subquery$_007"."HR_SEX"[VARCHAR2,5],
"from$_subquery$_007"."HR_INSTITUTIONCODE_CH"[VARCHAR2,14],
"from$_subquery$_007"."HR_COMPENSATIONTYPE_VC"[VARCHAR2,5],
"from$_subquery$_007"."HR_DISEASECODE_VC"[VARCHAR2,10], "from$_subquery$_007"."HR_INHOSDATE_DT"[DATE,7],
"from$_subquery$_007"."HR_OUTHOSSTATUS_VC"[VARCHAR2,100], "from$_subquery$_007"."HR_OUTHOSDATE_DT"[DATE,7],
"from$_subquery$_007"."ME_INSTITUTINNAME_VC"[VARCHAR2,50]
9 - "from$_subquery$_005"."HP_HOSREGISTERCODE_VC"[VARCHAR2,20],
"from$_subquery$_005"."HP_PRESCRIPTIONFEE_DEC"[NUMBER,22],
"from$_subquery$_005"."HP_ALLOWEDCOMP_DEC"[NUMBER,22],
"from$_subquery$_005"."HCM_SETTLEMENTDATE_DT"[DATE,7],
"from$_subquery$_005"."HR_PERSONALCODE_VC"[VARCHAR2,30], "from$_subquery$_005"."HR_NAME"[VARCHAR2,50],
"from$_subquery$_005"."HR_SEX"[VARCHAR2,5], "C"."HR_INSTITUTIONCODE_CH"[VARCHAR2,14],
"from$_subquery$_005"."HR_COMPENSATIONTYPE_VC"[VARCHAR2,5],
"from$_subquery$_005"."HR_DISEASECODE_VC"[VARCHAR2,10], "from$_subquery$_005"."HR_INHOSDATE_DT"[DATE,7],
"from$_subquery$_005"."HR_OUTHOSSTATUS_VC"[VARCHAR2,100], "from$_subquery$_005"."HR_OUTHOSDATE_DT"[DATE,7],
"D"."ME_INSTITUTINNAME_VC"[VARCHAR2,50]
10 - "from$_subquery$_005"."HP_HOSREGISTERCODE_VC"[VARCHAR2,20],
"from$_subquery$_005"."HP_PRESCRIPTIONFEE_DEC"[NUMBER,22],
"from$_subquery$_005"."HP_ALLOWEDCOMP_DEC"[NUMBER,22],
"from$_subquery$_005"."HCM_SETTLEMENTDATE_DT"[DATE,7],
"from$_subquery$_005"."HR_PERSONALCODE_VC"[VARCHAR2,30], "from$_subquery$_005"."HR_NAME"[VARCHAR2,50],
"from$_subquery$_005"."HR_SEX"[VARCHAR2,5], "C"."HR_INSTITUTIONCODE_CH"[VARCHAR2,14],
"from$_subquery$_005"."HR_COMPENSATIONTYPE_VC"[VARCHAR2,5],
"from$_subquery$_005"."HR_DISEASECODE_VC"[VARCHAR2,10], "from$_subquery$_005"."HR_INHOSDATE_DT"[DATE,7],
"from$_subquery$_005"."HR_OUTHOSSTATUS_VC"[VARCHAR2,100], "from$_subquery$_005"."HR_OUTHOSDATE_DT"[DATE,7]
11 - "A"."HP_HOSREGISTERCODE_VC"[VARCHAR2,20], "from$_subquery$_003"."HP_PRESCRIPTIONFEE_DEC"[NUMBER,22],
"from$_subquery$_003"."HP_ALLOWEDCOMP_DEC"[NUMBER,22],
"from$_subquery$_003"."HCM_SETTLEMENTDATE_DT"[DATE,7], "C"."HR_OUTHOSDATE_DT"[DATE,7],
"C"."HR_OUTHOSSTATUS_VC"[VARCHAR2,100], "C"."HR_INHOSDATE_DT"[DATE,7],
"C"."HR_DISEASECODE_VC"[VARCHAR2,10], "C"."HR_COMPENSATIONTYPE_VC"[VARCHAR2,5],
"C"."HR_INSTITUTIONCODE_CH"[VARCHAR2,14], "C"."HR_SEX"[VARCHAR2,5], "C"."HR_NAME"[VARCHAR2,50],
"C"."HR_PERSONALCODE_VC"[VARCHAR2,30]
12 - "A"."HP_HOSREGISTERCODE_VC"[VARCHAR2,20], "from$_subquery$_003"."HP_PRESCRIPTIONFEE_DEC"[NUMBER,22],
"from$_subquery$_003"."HP_ALLOWEDCOMP_DEC"[NUMBER,22], "from$_subquery$_003"."HCM_SETTLEMENTDATE_DT"[DATE,7]
13 - "A"."HP_HOSREGISTERCODE_VC"[VARCHAR2,20], "A"."HP_PRESCRIPTIONFEE_DEC"[NUMBER,22],
"A"."HP_ALLOWEDCOMP_DEC"[NUMBER,22], "B"."HCM_SETTLEMENTDATE_DT"[DATE,7]
14 - "A"."HP_HOSREGISTERCODE_VC"[VARCHAR2,20], "A"."HP_PRESCRIPTIONFEE_DEC"[NUMBER,22],
"A"."HP_ALLOWEDCOMP_DEC"[NUMBER,22]
15 - "B"."DELETEFLAG_CH"[CHARACTER,1], "B"."HCM_SETTLEMENTDATE_DT"[DATE,7]
16 - "B"."HCM_SETTLEMENTDATE_DT"[DATE,7], "B"."DELETEFLAG_CH"[CHARACTER,1]
17 - "C"."DELETEFLAG_CH"[CHARACTER,1], "C"."HR_OUTHOSDATE_DT"[DATE,7],
"C"."HR_OUTHOSSTATUS_VC"[VARCHAR2,100], "C"."HR_INHOSDATE_DT"[DATE,7],
"C"."HR_DISEASECODE_VC"[VARCHAR2,10], "C"."HR_COMPENSATIONTYPE_VC"[VARCHAR2,5],
"C"."HR_INSTITUTIONCODE_CH"[VARCHAR2,14], "C"."HR_SEX"[VARCHAR2,5], "C"."HR_NAME"[VARCHAR2,50],
"C"."HR_PERSONALCODE_VC"[VARCHAR2,30]
18 - "C"."HR_PERSONALCODE_VC"[VARCHAR2,30], "C"."HR_NAME"[VARCHAR2,50], "C"."HR_SEX"[VARCHAR2,5],
"C"."HR_INSTITUTIONCODE_CH"[VARCHAR2,14], "C"."HR_COMPENSATIONTYPE_VC"[VARCHAR2,5],
"C"."HR_DISEASECODE_VC"[VARCHAR2,10], "C"."HR_INHOSDATE_DT"[DATE,7],
"C"."HR_OUTHOSSTATUS_VC"[VARCHAR2,100], "C"."HR_OUTHOSDATE_DT"[DATE,7], "C"."DELETEFLAG_CH"[CHARACTER,1]
19 - "D"."DELETEFLAG_CH"[VARCHAR2,5], "D"."ME_INSTITUTINNAME_VC"[VARCHAR2,50]
20 - "D"."ME_INSTITUTINNAME_VC"[VARCHAR2,50], "D"."DELETEFLAG_CH"[VARCHAR2,5]
21 - "E"."PR_BRITHDAY_VC"[VARCHAR2,50], "E"."PR_PERSONALCODE_VC"[VARCHAR2,18]
22 - "E"."PR_PERSONALCODE_VC"[VARCHAR2,18], "E"."PR_BRITHDAY_VC"[VARCHAR2,50]
23 - "F"."PJ_CANTONCODE_CH"[VARCHAR2,12]
24 - "F"."PJ_CANTONCODE_CH"[VARCHAR2,12]
25 - "M"."DELETEFLAG_CH"[CHARACTER,1], "M"."MD_ICDCODE_VC"[VARCHAR2,10]
26 - "M"."MD_ICDCODE_VC"[VARCHAR2,10], "M"."DELETEFLAG_CH"[CHARACTER,1]
Note
-----
- dynamic sampling used for this statement (level=2)
該sql正常執行時的執行計劃（其他機器oracle資料庫的該sql的執行計劃）
Plan hash value: 1364454912
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 109K(100)| |
| 1 | HASH GROUP BY | | 330K| 86M| 92M| 109K (1)| 00:21:59 |
|* 2 | HASH JOIN | | 330K| 86M| | 90325 (1)| 00:18:04 |
|* 3 | TABLE ACCESS FULL | CFG_MAINTAINDISEASE | 31653 | 556K| | 138 (2)| 00:00:02 |
|* 4 | HASH JOIN | | 328K| 80M| | 90184 (1)| 00:18:03 |
|* 5 | TABLE ACCESS FULL | CFG_MEDICALESTABLISHMENT | 2926 | 108K| | 17 (0)| 00:00:01 |
|* 6 | HASH JOIN OUTER | | 328K| 68M| 61M| 90164 (1)| 00:18:02 |
|* 7 | HASH JOIN OUTER | | 322K| 57M| 50M| 64090 (2)| 00:12:50 |
|* 8 | HASH JOIN | | 316K| 47M| 21M| 45795 (2)| 00:09:10 |
|* 9 | TABLE ACCESS FULL | COMP_HOSREGISTER | 210K| 19M| | 3314 (1)| 00:00:40 |
|* 10 | HASH JOIN | | 310K| 18M| | 40322 (2)| 00:08:04 |
|* 11 | TABLE ACCESS FULL| COMP_HOSCOSTMAIN | 10467 | 316K| | 2208 (2)| 00:00:27 |
|* 12 | TABLE ACCESS FULL| COMP_HOSPRESCRIPTION | 6129K| 181M| | 38070 (2)| 00:07:37 |
| 13 | TABLE ACCESS FULL | JOIN_PERSONALRECORD | 3454K| 98M| | 8851 (1)| 00:01:47 |
| 14 | TABLE ACCESS FULL | JOIN_PERSONAJOIN | 3456K| 105M| | 15795 (1)| 00:03:10 |
-------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$9A5C6B1E
3 - SEL$9A5C6B1E / M@SEL$11
5 - SEL$9A5C6B1E / D@SEL$5
9 - SEL$9A5C6B1E / C@SEL$3
11 - SEL$9A5C6B1E / B@SEL$2
12 - SEL$9A5C6B1E / A@SEL$1
13 - SEL$9A5C6B1E / E@SEL$7
14 - SEL$9A5C6B1E / F@SEL$9
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
DB_VERSION('11.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$9A5C6B1E")
MERGE(@"SEL$72AEFE3E")
OUTLINE(@"SEL$F0958867")
OUTER_JOIN_TO_INNER(@"SEL$13")
OUTLINE(@"SEL$72AEFE3E")
MERGE(@"SEL$11")
MERGE(@"SEL$B97648DD")
OUTLINE(@"SEL$13")
OUTLINE(@"SEL$12")
OUTLINE(@"SEL$11")
OUTLINE(@"SEL$B97648DD")
MERGE(@"SEL$096E5AED")
MERGE(@"SEL$9")
OUTLINE(@"SEL$10")
OUTLINE(@"SEL$096E5AED")
MERGE(@"SEL$15E987C1")
MERGE(@"SEL$7")
OUTLINE(@"SEL$9")
OUTLINE(@"SEL$8")
OUTLINE(@"SEL$15E987C1")
MERGE(@"SEL$5")
MERGE(@"SEL$7237DA6D")
OUTLINE(@"SEL$7")
OUTLINE(@"SEL$6")
OUTLINE(@"SEL$5")
OUTLINE(@"SEL$7237DA6D")
MERGE(@"SEL$3")
MERGE(@"SEL$58A6D7F6")
OUTLINE(@"SEL$4")
OUTLINE(@"SEL$3")
OUTLINE(@"SEL$58A6D7F6")
MERGE(@"SEL$1")
OUTLINE(@"SEL$2")
OUTLINE(@"SEL$1")
FULL(@"SEL$9A5C6B1E" "B"@"SEL$2")
FULL(@"SEL$9A5C6B1E" "A"@"SEL$1")
FULL(@"SEL$9A5C6B1E" "C"@"SEL$3")
FULL(@"SEL$9A5C6B1E" "E"@"SEL$7")
FULL(@"SEL$9A5C6B1E" "F"@"SEL$9")
FULL(@"SEL$9A5C6B1E" "D"@"SEL$5")
FULL(@"SEL$9A5C6B1E" "M"@"SEL$11")
LEADING(@"SEL$9A5C6B1E" "B"@"SEL$2" "A"@"SEL$1" "C"@"SEL$3" "E"@"SEL$7" "F"@"SEL$9" "D"@"SEL$5"
"M"@"SEL$11")
USE_HASH(@"SEL$9A5C6B1E" "A"@"SEL$1")
USE_HASH(@"SEL$9A5C6B1E" "C"@"SEL$3")
USE_HASH(@"SEL$9A5C6B1E" "E"@"SEL$7")
USE_HASH(@"SEL$9A5C6B1E" "F"@"SEL$9")
USE_HASH(@"SEL$9A5C6B1E" "D"@"SEL$5")
USE_HASH(@"SEL$9A5C6B1E" "M"@"SEL$11")
PX_JOIN_FILTER(@"SEL$9A5C6B1E" "E"@"SEL$7")
SWAP_JOIN_INPUTS(@"SEL$9A5C6B1E" "C"@"SEL$3")
SWAP_JOIN_INPUTS(@"SEL$9A5C6B1E" "D"@"SEL$5")
SWAP_JOIN_INPUTS(@"SEL$9A5C6B1E" "M"@"SEL$11")
USE_HASH_AGGREGATION(@"SEL$9A5C6B1E")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("M"."MD_DISEASEID_VC"="C"."HR_DISEASECODE_VC")
3 - filter("M"."DELETEFLAG_CH"='N')
4 - access("C"."HR_INSTITUTIONCODE_CH"="D"."ME_INSTITUTIONCODE_VC")
5 - filter("D"."DELETEFLAG_CH"='N')
6 - access("F"."PJ_PERSONALCODE_VC"="E"."PR_PERSONALCODE_VC")
7 - access("C"."HR_PERSONALCODE_VC"="E"."PR_PERSONALCODE_VC")
8 - access("A"."HP_HOSREGISTERCODE_VC"="C"."HR_HOSREGISTERCODE_VC")
9 - filter("C"."DELETEFLAG_CH"='N')
10 - access("A"."HP_HOSREGISTERCODE_VC"="B"."HCM_HOSREGISTERCODE_VC")
11 - filter((TO_CHAR(INTERNAL_FUNCTION("B"."HCM_SETTLEMENTDATE_DT"),'yyyy-MM-dd')>'2017-03-31' AND
"B"."DELETEFLAG_CH"='N'))
12 - filter("A"."DELETEFLAG_CH"='N')
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "A"."HP_HOSREGISTERCODE_VC"||REPLACE("M"."MD_ICDCODE_VC",'.','')||TO_CHAR(INTERNAL_FUNCTION("C
"."HR_INHOSDATE_DT"),'yyyyMMdd')||TO_CHAR(INTERNAL_FUNCTION("C"."HR_OUTHOSDATE_DT"),'yyyyMMdd')||"C".
"HR_COMPENSATIONTYPE_VC"[51], "B"."HCM_SETTLEMENTDATE_DT"[DATE,7],
"F"."PJ_CANTONCODE_CH"[VARCHAR2,12], "C"."HR_INSTITUTIONCODE_CH"[VARCHAR2,14],
"D"."ME_INSTITUTINNAME_VC"[VARCHAR2,50], "C"."HR_INSTITUTIONCODE_CH"[VARCHAR2,14],
"D"."ME_INSTITUTINNAME_VC"[VARCHAR2,50], "C"."HR_PERSONALCODE_VC"[VARCHAR2,30],
"C"."HR_COMPENSATIONTYPE_VC"[VARCHAR2,5], "C"."HR_NAME"[VARCHAR2,50], "C"."HR_SEX"[VARCHAR2,5],
"E"."PR_BRITHDAY_VC"[VARCHAR2,50], "M"."MD_ICDCODE_VC"[VARCHAR2,10],
"C"."HR_OUTHOSSTATUS_VC"[VARCHAR2,100], "C"."HR_INHOSDATE_DT"[DATE,7],
"C"."HR_OUTHOSDATE_DT"[DATE,7], "C"."HR_INHOSDATE_DT"[DATE,7],
SUM(NVL("A"."HP_ALLOWEDCOMP_DEC",0))[22], SUM(NVL("A"."HP_PRESCRIPTIONFEE_DEC",0))[22]
2 - (#keys=1) "M"."MD_ICDCODE_VC"[VARCHAR2,10], "C"."HR_INSTITUTIONCODE_CH"[VARCHAR2,14],
"D"."ME_INSTITUTINNAME_VC"[VARCHAR2,50], "C"."HR_PERSONALCODE_VC"[VARCHAR2,30],
"E"."PR_BRITHDAY_VC"[VARCHAR2,50], "A"."HP_HOSREGISTERCODE_VC"[VARCHAR2,20],
"A"."HP_PRESCRIPTIONFEE_DEC"[NUMBER,22], "C"."HR_NAME"[VARCHAR2,50], "C"."HR_SEX"[VARCHAR2,5],
"F"."PJ_CANTONCODE_CH"[VARCHAR2,12], "C"."HR_COMPENSATIONTYPE_VC"[VARCHAR2,5],
"C"."HR_INHOSDATE_DT"[DATE,7], "C"."HR_OUTHOSSTATUS_VC"[VARCHAR2,100],
"C"."HR_OUTHOSDATE_DT"[DATE,7], "A"."HP_ALLOWEDCOMP_DEC"[NUMBER,22],
"B"."HCM_SETTLEMENTDATE_DT"[DATE,7]
3 - "M"."MD_DISEASEID_VC"[VARCHAR2,20], "M"."MD_ICDCODE_VC"[VARCHAR2,10]
4 - (#keys=1) "C"."HR_INSTITUTIONCODE_CH"[VARCHAR2,14], "D"."ME_INSTITUTINNAME_VC"[VARCHAR2,50],
"C"."HR_PERSONALCODE_VC"[VARCHAR2,30], "E"."PR_BRITHDAY_VC"[VARCHAR2,50],
"A"."HP_HOSREGISTERCODE_VC"[VARCHAR2,20], "A"."HP_PRESCRIPTIONFEE_DEC"[NUMBER,22],
"C"."HR_NAME"[VARCHAR2,50], "C"."HR_SEX"[VARCHAR2,5], "F"."PJ_CANTONCODE_CH"[VARCHAR2,12],
"C"."HR_COMPENSATIONTYPE_VC"[VARCHAR2,5], "C"."HR_DISEASECODE_VC"[VARCHAR2,10],
"C"."HR_INHOSDATE_DT"[DATE,7], "C"."HR_OUTHOSSTATUS_VC"[VARCHAR2,100],
"C"."HR_OUTHOSDATE_DT"[DATE,7], "A"."HP_ALLOWEDCOMP_DEC"[NUMBER,22],
"B"."HCM_SETTLEMENTDATE_DT"[DATE,7]
5 - "D"."ME_INSTITUTIONCODE_VC"[VARCHAR2,15], "D"."ME_INSTITUTINNAME_VC"[VARCHAR2,50]
6 - (#keys=1) "C"."HR_PERSONALCODE_VC"[VARCHAR2,30], "E"."PR_BRITHDAY_VC"[VARCHAR2,50],
"A"."HP_HOSREGISTERCODE_VC"[VARCHAR2,20], "A"."HP_PRESCRIPTIONFEE_DEC"[NUMBER,22],
"C"."HR_NAME"[VARCHAR2,50], "C"."HR_SEX"[VARCHAR2,5], "C"."HR_INSTITUTIONCODE_CH"[VARCHAR2,14],
"C"."HR_COMPENSATIONTYPE_VC"[VARCHAR2,5], "C"."HR_DISEASECODE_VC"[VARCHAR2,10],
"C"."HR_INHOSDATE_DT"[DATE,7], "C"."HR_OUTHOSSTATUS_VC"[VARCHAR2,100],
"C"."HR_OUTHOSDATE_DT"[DATE,7], "A"."HP_ALLOWEDCOMP_DEC"[NUMBER,22],
"B"."HCM_SETTLEMENTDATE_DT"[DATE,7], "F"."PJ_CANTONCODE_CH"[VARCHAR2,12]
7 - (#keys=1) "C"."HR_PERSONALCODE_VC"[VARCHAR2,30], "E"."PR_PERSONALCODE_VC"[VARCHAR2,18],
"A"."HP_HOSREGISTERCODE_VC"[VARCHAR2,20], "A"."HP_PRESCRIPTIONFEE_DEC"[NUMBER,22],
"C"."HR_NAME"[VARCHAR2,50], "C"."HR_SEX"[VARCHAR2,5], "C"."HR_INSTITUTIONCODE_CH"[VARCHAR2,14],
"C"."HR_COMPENSATIONTYPE_VC"[VARCHAR2,5], "C"."HR_DISEASECODE_VC"[VARCHAR2,10],
"C"."HR_INHOSDATE_DT"[DATE,7], "C"."HR_OUTHOSSTATUS_VC"[VARCHAR2,100],
"C"."HR_OUTHOSDATE_DT"[DATE,7], "A"."HP_ALLOWEDCOMP_DEC"[NUMBER,22],
"B"."HCM_SETTLEMENTDATE_DT"[DATE,7], "E"."PR_BRITHDAY_VC"[VARCHAR2,50]
8 - (#keys=1) "A"."HP_HOSREGISTERCODE_VC"[VARCHAR2,20], "C"."HR_PERSONALCODE_VC"[VARCHAR2,30],
"C"."HR_NAME"[VARCHAR2,50], "C"."HR_SEX"[VARCHAR2,5], "C"."HR_INSTITUTIONCODE_CH"[VARCHAR2,14],
"C"."HR_COMPENSATIONTYPE_VC"[VARCHAR2,5], "C"."HR_DISEASECODE_VC"[VARCHAR2,10],
"C"."HR_INHOSDATE_DT"[DATE,7], "C"."HR_OUTHOSSTATUS_VC"[VARCHAR2,100],
"C"."HR_OUTHOSDATE_DT"[DATE,7], "A"."HP_ALLOWEDCOMP_DEC"[NUMBER,22],
"B"."HCM_SETTLEMENTDATE_DT"[DATE,7], "A"."HP_PRESCRIPTIONFEE_DEC"[NUMBER,22]
9 - "C"."HR_HOSREGISTERCODE_VC"[VARCHAR2,20], "C"."HR_PERSONALCODE_VC"[VARCHAR2,30],
"C"."HR_NAME"[VARCHAR2,50], "C"."HR_SEX"[VARCHAR2,5], "C"."HR_INSTITUTIONCODE_CH"[VARCHAR2,14],
"C"."HR_COMPENSATIONTYPE_VC"[VARCHAR2,5], "C"."HR_DISEASECODE_VC"[VARCHAR2,10],
"C"."HR_INHOSDATE_DT"[DATE,7], "C"."HR_OUTHOSSTATUS_VC"[VARCHAR2,100], "C"."HR_OUTHOSDATE_DT"[DATE,7]
10 - (#keys=1) "A"."HP_HOSREGISTERCODE_VC"[VARCHAR2,20], "B"."HCM_SETTLEMENTDATE_DT"[DATE,7],
"A"."HP_PRESCRIPTIONFEE_DEC"[NUMBER,22], "A"."HP_ALLOWEDCOMP_DEC"[NUMBER,22]
11 - "B"."HCM_HOSREGISTERCODE_VC"[VARCHAR2,20], "B"."HCM_SETTLEMENTDATE_DT"[DATE,7]
12 - "A"."HP_HOSREGISTERCODE_VC"[VARCHAR2,20], "A"."HP_PRESCRIPTIONFEE_DEC"[NUMBER,22],
"A"."HP_ALLOWEDCOMP_DEC"[NUMBER,22]
13 - "E"."PR_PERSONALCODE_VC"[VARCHAR2,18], "E"."PR_BRITHDAY_VC"[VARCHAR2,50]
14 - "F"."PJ_PERSONALCODE_VC"[VARCHAR2,30], "F"."PJ_CANTONCODE_CH"[VARCHAR2,12]
已選擇198行。
該sql語句涉及的物件全部匯出，然後分別匯入其他機器oracle資料庫與出現效能問題的資料庫，發現其他機器資料庫執行非常快而問題資料庫依舊很慢。嘗試
收集方案及表的統計資訊後，再次執行sql語句還是沒有改善，在問題處理的過程中，注意到一個很明顯的問題，就是不管收集統計資訊還是給sql加並行、使
用強制sql走hash，其執行計劃都是原來的巢狀迴圈執行計劃並且出現view字樣。最終定位到資料庫啟動初始化引數_complex_view_merging。
問題sql：
SELECT SUBSTR(F.PJ_CANTONCODE_CH, 0, 6) BMI_CODE,
A.HP_HOSREGISTERCODE_VC || REPLACE(M.MD_ICDCODE_VC, '.', '') || TO_CHAR(C.HR_INHOSDATE_DT, 'yyyyMMdd') || TO_CHAR(C.HR_OUTHOSDATE_DT, 'yyyyMMdd') || C.HR_COMPENSATIONTYPE_VC KEY_1,
A.HP_HOSREGISTERCODE_VC || REPLACE(M.MD_ICDCODE_VC, '.', '') || TO_CHAR(C.HR_INHOSDATE_DT, 'yyyyMMdd') || TO_CHAR(C.HR_OUTHOSDATE_DT, 'yyyyMMdd') || C.HR_COMPENSATIONTYPE_VC HISID,
B.HCM_SETTLEMENTDATE_DT BILLDATE,
C.HR_INSTITUTIONCODE_CH HOSPITAL_ID,
D.ME_INSTITUTINNAME_VC HOSPITAL_NAME,
C.HR_INSTITUTIONCODE_CH HOSPITAL_FEE_ID,
D.ME_INSTITUTINNAME_VC HOSPITAL_FEE_NAME,
C.HR_PERSONALCODE_VC PATIENT_ID,
C.HR_NAME PATIENT_NAME,
C.HR_COMPENSATIONTYPE_VC CLAIM_TYPE,
DECODE(NVL(C.HR_SEX, '-1'), '男', '1', '女', '0', '-1') PATIENT_SEX,
TO_DATE(NVL(E.PR_BRITHDAY_VC,
'1900-01-01'),
'yyyy-MM-dd') PATIENT_BIRTH,
M.MD_ICDCODE_VC ADMISSION_DISEASE_ID,
DECODE(C.HR_OUTHOSSTATUS_VC,
'1',
'治癒',
'2',
'好轉',
'3',
'未愈',
'4',
'死亡',
'9',
'其他',
'其他') DISCHARGE_REASON,
C.HR_INHOSDATE_DT ADMISSION_DATE,
C.HR_OUTHOSDATE_DT DISCHARGE_DATE,
C.HR_INHOSDATE_DT FIRST_DATE,
A.HP_HOSREGISTERCODE_VC || REPLACE(M.MD_ICDCODE_VC, '.', '') || TO_CHAR(C.HR_INHOSDATE_DT, 'yyyyMMdd') || TO_CHAR(C.HR_OUTHOSDATE_DT, 'yyyyMMdd') || C.HR_COMPENSATIONTYPE_VC BILL_NO,
SUM(NVL(A.HP_PRESCRIPTIONFEE_DEC, 0)) TOTAL_AMOUNT,
SUM(NVL(A.HP_ALLOWEDCOMP_DEC, 0)) BMI_CONVERED_AMOUNT,
A.HP_HOSREGISTERCODE_VC || REPLACE(M.MD_ICDCODE_VC, '.', '') ||
TO_CHAR(C.HR_INHOSDATE_DT, 'yyyyMMdd') || TO_CHAR(C.HR_OUTHOSDATE_DT,'yyyyMMdd') || C.HR_COMPENSATIONTYPE_VC TRADENO
FROM COMP_HOSPRESCRIPTION A
LEFT JOIN COMP_HOSCOSTMAIN B
ON A.HP_HOSREGISTERCODE_VC = B.HCM_HOSREGISTERCODE_VC
LEFT JOIN COMP_HOSREGISTER C
ON A.HP_HOSREGISTERCODE_VC = C.HR_HOSREGISTERCODE_VC
LEFT JOIN CFG_MEDICALESTABLISHMENT D
ON C.HR_INSTITUTIONCODE_CH = D.ME_INSTITUTIONCODE_VC
LEFT JOIN JOIN_PERSONALRECORD E
ON C.HR_PERSONALCODE_VC = E.PR_PERSONALCODE_VC
LEFT JOIN JOIN_PERSONAJOIN F
ON F.PJ_PERSONALCODE_VC = E.PR_PERSONALCODE_VC
LEFT JOIN CFG_MAINTAINDISEASE M
ON M.MD_DISEASEID_VC = C.HR_DISEASECODE_VC
WHERE TO_CHAR(B.HCM_SETTLEMENTDATE_DT, 'yyyy-MM-dd') > '2017-03-31'
AND TO_CHAR(B.HCM_SETTLEMENTDATE_DT, 'yyyy-MM-dd') < '2017-05-31'
AND A.DELETEFLAG_CH = 'N'
AND B.DELETEFLAG_CH = 'N'
AND C.DELETEFLAG_CH = 'N'
AND D.DELETEFLAG_CH = 'N'
AND M.DELETEFLAG_CH = 'N'
GROUP BY A.HP_HOSREGISTERCODE_VC || REPLACE(M.MD_ICDCODE_VC, '.', '') ||
TO_CHAR(C.HR_INHOSDATE_DT, 'yyyyMMdd') ||
TO_CHAR(C.HR_OUTHOSDATE_DT, 'yyyyMMdd') || C.HR_COMPENSATIONTYPE_VC,
B.HCM_SETTLEMENTDATE_DT,
F.PJ_CANTONCODE_CH,
C.HR_INSTITUTIONCODE_CH,
D.ME_INSTITUTINNAME_VC,
C.HR_INSTITUTIONCODE_CH,
D.ME_INSTITUTINNAME_VC,
C.HR_PERSONALCODE_VC,
C.HR_COMPENSATIONTYPE_VC,
C.HR_NAME,
C.HR_SEX,
E.PR_BRITHDAY_VC,
M.MD_ICDCODE_VC，C.HR_OUTHOSSTATUS_VC,
C.HR_INHOSDATE_DT,
C.HR_OUTHOSDATE_DT,
C.HR_INHOSDATE_DT;
其執行計劃：
Plan hash value: 1942484934
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 18E(100)| |
| 1 | HASH GROUP BY | | 25G| 6024G| 15E| 18E (0)|999:59:59 |
| 2 | VIEW | | 18E| 15E| | 18E (0)|999:59:59 |
| 3 | NESTED LOOPS | | 18E| 15E| | 18E (0)|999:59:59 |
| 4 | VIEW | | 441P| 15E| | 18E (0)|999:59:59 |
| 5 | NESTED LOOPS OUTER | | 441P| 15E| | 18E (0)|999:59:59 |
| 6 | VIEW | | 441P| 15E| | 112P (1)|999:59:59 |
| 7 | NESTED LOOPS OUTER | | 441P| 15E| | 112P (1)|999:59:59 |
| 8 | VIEW | | 12T| 2601T| | 8133G (1)|999:59:59 |
| 9 | NESTED LOOPS | | 12T| 2613T| | 8133G (1)|999:59:59 |
| 10 | VIEW | | 440G| 77T| | 624G (1)|999:59:59 |
| 11 | NESTED LOOPS | | 440G| 77T| | 624G (1)|999:59:59 |
| 12 | VIEW | | 889M| 43G| | 909M (1)|999:59:59 |
| 13 | NESTED LOOPS | | 889M| 43G| | 909M (1)|999:59:59 |
|* 14 | TABLE ACCESS FULL | COMP_HOSPRESCRIPTION | 1258K| 49M| | 11529 (1)| 00:02:19 |
|* 15 | VIEW | | 707 | 8484 | | 723 (1)| 00:00:09 |
|* 16 | TABLE ACCESS FULL| COMP_HOSCOSTMAIN | 707 | 16968 | | 723 (1)| 00:00:09 |
|* 17 | VIEW | | 495 | 69795 | | 700 (1)| 00:00:09 |
|* 18 | TABLE ACCESS FULL | COMP_HOSREGISTER | 495 | 75735 | | 700 (1)| 00:00:09 |
|* 19 | VIEW | | 29 | 899 | | 17 (0)| 00:00:01 |
|* 20 | TABLE ACCESS FULL | CFG_MEDICALESTABLISHMENT | 29 | 1102 | | 17 (0)| 00:00:01 |
| 21 | VIEW | | 34547 | 1551K| | 8824 (1)| 00:01:46 |
|* 22 | TABLE ACCESS FULL | JOIN_PERSONALRECORD | 34547 | 1012K| | 8824 (1)| 00:01:46 |
| 23 | VIEW | | 1 | 8 | | 15777 (1)| 00:03:10 |
|* 24 | TABLE ACCESS FULL | JOIN_PERSONAJOIN | 1 | 32 | | 15777 (1)| 00:03:10 |
|* 25 | VIEW | | 317 | 3170 | | 137 (1)| 00:00:02 |
|* 26 | TABLE ACCESS FULL | CFG_MAINTAINDISEASE | 317 | 5706 | | 137 (1)| 00:00:02 |
--------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$13
2 - SEL$27C75F45 / from$_subquery$_013@SEL$13
3 - SEL$27C75F45
4 - SEL$10 / from$_subquery$_011@SEL$12
5 - SEL$10
6 - SEL$8 / from$_subquery$_009@SEL$10
7 - SEL$8
8 - SEL$D28406B9 / from$_subquery$_007@SEL$8
9 - SEL$D28406B9
10 - SEL$E875D2C0 / from$_subquery$_005@SEL$6
11 - SEL$E875D2C0
12 - SEL$D186B18B / from$_subquery$_003@SEL$4
13 - SEL$D186B18B
14 - SEL$D186B18B / A@SEL$2
15 - SEL$1 / from$_subquery$_014@SEL$2
16 - SEL$1 / B@SEL$1
17 - SEL$3 / from$_subquery$_015@SEL$4
18 - SEL$3 / C@SEL$3
19 - SEL$5 / from$_subquery$_016@SEL$6
20 - SEL$5 / D@SEL$5
21 - SEL$7 / from$_subquery$_017@SEL$8
22 - SEL$7 / E@SEL$7
23 - SEL$9 / from$_subquery$_018@SEL$10
24 - SEL$9 / F@SEL$9
25 - SEL$11 / from$_subquery$_019@SEL$12
26 - SEL$11 / M@SEL$11
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
OPT_PARAM('_complex_view_merging' 'false')
OPT_PARAM('_optimizer_use_feedback' 'false')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
OUTLINE_LEAF(@"SEL$D186B18B")
OUTER_JOIN_TO_INNER(@"SEL$2" "from$_subquery$_014"@"SEL$2")
OUTLINE_LEAF(@"SEL$3")
OUTLINE_LEAF(@"SEL$E875D2C0")
OUTER_JOIN_TO_INNER(@"SEL$4" "from$_subquery$_015"@"SEL$4")
OUTLINE_LEAF(@"SEL$5")
OUTLINE_LEAF(@"SEL$D28406B9")
OUTER_JOIN_TO_INNER(@"SEL$6" "from$_subquery$_016"@"SEL$6")
OUTLINE_LEAF(@"SEL$7")
OUTLINE_LEAF(@"SEL$8")
OUTLINE_LEAF(@"SEL$9")
OUTLINE_LEAF(@"SEL$10")
OUTLINE_LEAF(@"SEL$11")
OUTLINE_LEAF(@"SEL$27C75F45")
OUTER_JOIN_TO_INNER(@"SEL$12" "from$_subquery$_019"@"SEL$12")
OUTLINE_LEAF(@"SEL$13")
OUTLINE(@"SEL$2")
OUTLINE(@"SEL$4")
OUTLINE(@"SEL$6")
OUTLINE(@"SEL$12")
NO_ACCESS(@"SEL$13" "from$_subquery$_013"@"SEL$13")
USE_HASH_AGGREGATION(@"SEL$13")
NO_ACCESS(@"SEL$27C75F45" "from$_subquery$_011"@"SEL$12")
NO_ACCESS(@"SEL$27C75F45" "from$_subquery$_019"@"SEL$12")
LEADING(@"SEL$27C75F45" "from$_subquery$_011"@"SEL$12" "from$_subquery$_019"@"SEL$12")
USE_NL(@"SEL$27C75F45" "from$_subquery$_019"@"SEL$12")
NO_ACCESS(@"SEL$10" "from$_subquery$_009"@"SEL$10")
NO_ACCESS(@"SEL$10" "from$_subquery$_018"@"SEL$10")
LEADING(@"SEL$10" "from$_subquery$_009"@"SEL$10" "from$_subquery$_018"@"SEL$10")
USE_NL(@"SEL$10" "from$_subquery$_018"@"SEL$10")
FULL(@"SEL$11" "M"@"SEL$11")
NO_ACCESS(@"SEL$8" "from$_subquery$_007"@"SEL$8")
NO_ACCESS(@"SEL$8" "from$_subquery$_017"@"SEL$8")
LEADING(@"SEL$8" "from$_subquery$_007"@"SEL$8" "from$_subquery$_017"@"SEL$8")
USE_NL(@"SEL$8" "from$_subquery$_017"@"SEL$8")
FULL(@"SEL$9" "F"@"SEL$9")
NO_ACCESS(@"SEL$D28406B9" "from$_subquery$_005"@"SEL$6")
NO_ACCESS(@"SEL$D28406B9" "from$_subquery$_016"@"SEL$6")
LEADING(@"SEL$D28406B9" "from$_subquery$_005"@"SEL$6" "from$_subquery$_016"@"SEL$6")
USE_NL(@"SEL$D28406B9" "from$_subquery$_016"@"SEL$6")
FULL(@"SEL$7" "E"@"SEL$7")
NO_ACCESS(@"SEL$E875D2C0" "from$_subquery$_003"@"SEL$4")
NO_ACCESS(@"SEL$E875D2C0" "from$_subquery$_015"@"SEL$4")
LEADING(@"SEL$E875D2C0" "from$_subquery$_003"@"SEL$4" "from$_subquery$_015"@"SEL$4")
USE_NL(@"SEL$E875D2C0" "from$_subquery$_015"@"SEL$4")
FULL(@"SEL$5" "D"@"SEL$5")
FULL(@"SEL$D186B18B" "A"@"SEL$2")
NO_ACCESS(@"SEL$D186B18B" "from$_subquery$_014"@"SEL$2")
LEADING(@"SEL$D186B18B" "A"@"SEL$2" "from$_subquery$_014"@"SEL$2")
USE_NL(@"SEL$D186B18B" "from$_subquery$_014"@"SEL$2")
FULL(@"SEL$3" "C"@"SEL$3")
FULL(@"SEL$1" "B"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
14 - filter("A"."DELETEFLAG_CH"='N')
15 - filter((TO_CHAR(INTERNAL_FUNCTION("B"."HCM_SETTLEMENTDATE_DT"),'yyyy-MM-dd')>'2017-03-31' AND
TO_CHAR(INTERNAL_FUNCTION("B"."HCM_SETTLEMENTDATE_DT"),'yyyy-MM-dd')<'2017-05-31' AND
"B"."DELETEFLAG_CH"='N'))
16 - filter("A"."HP_HOSREGISTERCODE_VC"="B"."HCM_HOSREGISTERCODE_VC")
17 - filter("C"."DELETEFLAG_CH"='N')
18 - filter("A"."HP_HOSREGISTERCODE_VC"="C"."HR_HOSREGISTERCODE_VC")
19 - filter("D"."DELETEFLAG_CH"='N')
20 - filter("C"."HR_INSTITUTIONCODE_CH"="D"."ME_INSTITUTIONCODE_VC")
22 - filter("C"."HR_PERSONALCODE_VC"="E"."PR_PERSONALCODE_VC")
24 - filter("F"."PJ_PERSONALCODE_VC"="E"."PR_PERSONALCODE_VC")
25 - filter("M"."DELETEFLAG_CH"='N')
26 - filter("M"."MD_DISEASEID_VC"="C"."HR_DISEASECODE_VC")
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "A"."HP_HOSREGISTERCODE_VC"||REPLACE("M"."MD_ICDCODE_VC",'.','')||TO_CHAR(INTERNAL_FUNCTION("C"."HR_I
NHOSDATE_DT"),'yyyyMMdd')||TO_CHAR(INTERNAL_FUNCTION("C"."HR_OUTHOSDATE_DT"),'yyyyMMdd')||"C"."HR_COMPENSATI
ONTYPE_VC"[51], "B"."HCM_SETTLEMENTDATE_DT"[DATE,7], "F"."PJ_CANTONCODE_CH"[VARCHAR2,12],
"C"."HR_INSTITUTIONCODE_CH"[VARCHAR2,14], "D"."ME_INSTITUTINNAME_VC"[VARCHAR2,50],
"C"."HR_INSTITUTIONCODE_CH"[VARCHAR2,14], "D"."ME_INSTITUTINNAME_VC"[VARCHAR2,50],
"C"."HR_PERSONALCODE_VC"[VARCHAR2,30], "C"."HR_COMPENSATIONTYPE_VC"[VARCHAR2,5],
"C"."HR_NAME"[VARCHAR2,50], "C"."HR_SEX"[VARCHAR2,5], "E"."PR_BRITHDAY_VC"[VARCHAR2,50],
"M"."MD_ICDCODE_VC"[VARCHAR2,10], "C"."HR_OUTHOSSTATUS_VC"[VARCHAR2,100], "C"."HR_INHOSDATE_DT"[DATE,7],
"C"."HR_OUTHOSDATE_DT"[DATE,7], "C"."HR_INHOSDATE_DT"[DATE,7], SUM(NVL("A"."HP_ALLOWEDCOMP_DEC",0))[22],
SUM(NVL("A"."HP_PRESCRIPTIONFEE_DEC",0))[22]
2 - "A"."HP_HOSREGISTERCODE_VC"[VARCHAR2,20], "A"."HP_PRESCRIPTIONFEE_DEC"[NUMBER,22],
"A"."HP_ALLOWEDCOMP_DEC"[NUMBER,22], "B"."HCM_SETTLEMENTDATE_DT"[DATE,7],
"C"."HR_PERSONALCODE_VC"[VARCHAR2,30], "C"."HR_NAME"[VARCHAR2,50], "C"."HR_SEX"[VARCHAR2,5],
"C"."HR_INSTITUTIONCODE_CH"[VARCHAR2,14], "C"."HR_COMPENSATIONTYPE_VC"[VARCHAR2,5],
"C"."HR_INHOSDATE_DT"[DATE,7], "C"."HR_OUTHOSSTATUS_VC"[VARCHAR2,100], "C"."HR_OUTHOSDATE_DT"[DATE,7],
"D"."ME_INSTITUTINNAME_VC"[VARCHAR2,50], "E"."PR_BRITHDAY_VC"[VARCHAR2,50],
"F"."PJ_CANTONCODE_CH"[VARCHAR2,12], "M"."MD_ICDCODE_VC"[VARCHAR2,10]
3 - "from$_subquery$_011"."HP_HOSREGISTERCODE_VC"[VARCHAR2,20],
"from$_subquery$_011"."HP_PRESCRIPTIONFEE_DEC"[NUMBER,22],
"from$_subquery$_011"."HP_ALLOWEDCOMP_DEC"[NUMBER,22],
"from$_subquery$_011"."HCM_SETTLEMENTDATE_DT"[DATE,7],
"from$_subquery$_011"."HR_PERSONALCODE_VC"[VARCHAR2,30], "from$_subquery$_011"."HR_NAME"[VARCHAR2,50],
"from$_subquery$_011"."HR_SEX"[VARCHAR2,5], "from$_subquery$_011"."HR_INSTITUTIONCODE_CH"[VARCHAR2,14],
"from$_subquery$_011"."HR_COMPENSATIONTYPE_VC"[VARCHAR2,5],
"from$_subquery$_011"."HR_INHOSDATE_DT"[DATE,7], "from$_subquery$_011"."HR_OUTHOSSTATUS_VC"[VARCHAR2,100],
"from$_subquery$_011"."HR_OUTHOSDATE_DT"[DATE,7], "from$_subquery$_011"."ME_INSTITUTINNAME_VC"[VARCHAR2,50],
"from$_subquery$_011"."PR_BRITHDAY_VC"[VARCHAR2,50],
"from$_subquery$_011"."PJ_CANTONCODE_CH"[VARCHAR2,12], "M"."MD_ICDCODE_VC"[VARCHAR2,10]
4 - "from$_subquery$_011"."HP_HOSREGISTERCODE_VC"[VARCHAR2,20],
"from$_subquery$_011"."HP_PRESCRIPTIONFEE_DEC"[NUMBER,22],
"from$_subquery$_011"."HP_ALLOWEDCOMP_DEC"[NUMBER,22],
"from$_subquery$_011"."HCM_SETTLEMENTDATE_DT"[DATE,7],
"from$_subquery$_011"."HR_PERSONALCODE_VC"[VARCHAR2,30], "from$_subquery$_011"."HR_NAME"[VARCHAR2,50],
"from$_subquery$_011"."HR_SEX"[VARCHAR2,5], "from$_subquery$_011"."HR_INSTITUTIONCODE_CH"[VARCHAR2,14],
"from$_subquery$_011"."HR_COMPENSATIONTYPE_VC"[VARCHAR2,5], "C"."HR_DISEASECODE_VC"[VARCHAR2,10],
"from$_subquery$_011"."HR_INHOSDATE_DT"[DATE,7], "from$_subquery$_011"."HR_OUTHOSSTATUS_VC"[VARCHAR2,100],
"from$_subquery$_011"."HR_OUTHOSDATE_DT"[DATE,7], "from$_subquery$_011"."ME_INSTITUTINNAME_VC"[VARCHAR2,50],
"from$_subquery$_011"."PR_BRITHDAY_VC"[VARCHAR2,50], "from$_subquery$_011"."PJ_CANTONCODE_CH"[VARCHAR2,12]
5 - "from$_subquery$_009"."HP_HOSREGISTERCODE_VC"[VARCHAR2,20],
"from$_subquery$_009"."HP_PRESCRIPTIONFEE_DEC"[NUMBER,22],
"from$_subquery$_009"."HP_ALLOWEDCOMP_DEC"[NUMBER,22],
"from$_subquery$_009"."HCM_SETTLEMENTDATE_DT"[DATE,7],
"from$_subquery$_009"."HR_PERSONALCODE_VC"[VARCHAR2,30], "from$_subquery$_009"."HR_NAME"[VARCHAR2,50],
"from$_subquery$_009"."HR_SEX"[VARCHAR2,5], "from$_subquery$_009"."HR_INSTITUTIONCODE_CH"[VARCHAR2,14],
"from$_subquery$_009"."HR_COMPENSATIONTYPE_VC"[VARCHAR2,5],
"from$_subquery$_009"."HR_DISEASECODE_VC"[VARCHAR2,10], "from$_subquery$_009"."HR_INHOSDATE_DT"[DATE,7],
"from$_subquery$_009"."HR_OUTHOSSTATUS_VC"[VARCHAR2,100], "from$_subquery$_009"."HR_OUTHOSDATE_DT"[DATE,7],
"from$_subquery$_009"."ME_INSTITUTINNAME_VC"[VARCHAR2,50],
"from$_subquery$_009"."PR_BRITHDAY_VC"[VARCHAR2,50], "F"."PJ_CANTONCODE_CH"[VARCHAR2,12]
6 - "from$_subquery$_009"."HP_HOSREGISTERCODE_VC"[VARCHAR2,20],
"from$_subquery$_009"."HP_PRESCRIPTIONFEE_DEC"[NUMBER,22],
"from$_subquery$_009"."HP_ALLOWEDCOMP_DEC"[NUMBER,22],
"from$_subquery$_009"."HCM_SETTLEMENTDATE_DT"[DATE,7],
"from$_subquery$_009"."HR_PERSONALCODE_VC"[VARCHAR2,30], "from$_subquery$_009"."HR_NAME"[VARCHAR2,50],
"from$_subquery$_009"."HR_SEX"[VARCHAR2,5], "from$_subquery$_009"."HR_INSTITUTIONCODE_CH"[VARCHAR2,14],
"from$_subquery$_009"."HR_COMPENSATIONTYPE_VC"[VARCHAR2,5],
"from$_subquery$_009"."HR_DISEASECODE_VC"[VARCHAR2,10], "from$_subquery$_009"."HR_INHOSDATE_DT"[DATE,7],
"from$_subquery$_009"."HR_OUTHOSSTATUS_VC"[VARCHAR2,100], "from$_subquery$_009"."HR_OUTHOSDATE_DT"[DATE,7],
"from$_subquery$_009"."ME_INSTITUTINNAME_VC"[VARCHAR2,50], "E"."PR_PERSONALCODE_VC"[VARCHAR2,18],
"from$_subquery$_009"."PR_BRITHDAY_VC"[VARCHAR2,50]
7 - "from$_subquery$_007"."HP_HOSREGISTERCODE_VC"[VARCHAR2,20],
"from$_subquery$_007"."HP_PRESCRIPTIONFEE_DEC"[NUMBER,22],
"from$_subquery$_007"."HP_ALLOWEDCOMP_DEC"[NUMBER,22],
"from$_subquery$_007"."HCM_SETTLEMENTDATE_DT"[DATE,7], "C"."HR_PERSONALCODE_VC"[VARCHAR2,30],
"from$_subquery$_007"."HR_NAME"[VARCHAR2,50], "from$_subquery$_007"."HR_SEX"[VARCHAR2,5],
"from$_subquery$_007"."HR_INSTITUTIONCODE_CH"[VARCHAR2,14],
"from$_subquery$_007"."HR_COMPENSATIONTYPE_VC"[VARCHAR2,5],
"from$_subquery$_007"."HR_DISEASECODE_VC"[VARCHAR2,10], "from$_subquery$_007"."HR_INHOSDATE_DT"[DATE,7],
"from$_subquery$_007"."HR_OUTHOSSTATUS_VC"[VARCHAR2,100], "from$_subquery$_007"."HR_OUTHOSDATE_DT"[DATE,7],
"from$_subquery$_007"."ME_INSTITUTINNAME_VC"[VARCHAR2,50], "E"."PR_BRITHDAY_VC"[VARCHAR2,50],
"E"."PR_PERSONALCODE_VC"[VARCHAR2,18]
8 - "from$_subquery$_007"."HP_HOSREGISTERCODE_VC"[VARCHAR2,20],
"from$_subquery$_007"."HP_PRESCRIPTIONFEE_DEC"[NUMBER,22],
"from$_subquery$_007"."HP_ALLOWEDCOMP_DEC"[NUMBER,22],
"from$_subquery$_007"."HCM_SETTLEMENTDATE_DT"[DATE,7], "C"."HR_PERSONALCODE_VC"[VARCHAR2,30],
"from$_subquery$_007"."HR_NAME"[VARCHAR2,50], "from$_subquery$_007"."HR_SEX"[VARCHAR2,5],
"from$_subquery$_007"."HR_INSTITUTIONCODE_CH"[VARCHAR2,14],
"from$_subquery$_007"."HR_COMPENSATIONTYPE_VC"[VARCHAR2,5],
"from$_subquery$_007"."HR_DISEASECODE_VC"[VARCHAR2,10], "from$_subquery$_007"."HR_INHOSDATE_DT"[DATE,7],
"from$_subquery$_007"."HR_OUTHOSSTATUS_VC"[VARCHAR2,100], "from$_subquery$_007"."HR_OUTHOSDATE_DT"[DATE,7],
"from$_subquery$_007"."ME_INSTITUTINNAME_VC"[VARCHAR2,50]
9 - "from$_subquery$_005"."HP_HOSREGISTERCODE_VC"[VARCHAR2,20],
"from$_subquery$_005"."HP_PRESCRIPTIONFEE_DEC"[NUMBER,22],
"from$_subquery$_005"."HP_ALLOWEDCOMP_DEC"[NUMBER,22],
"from$_subquery$_005"."HCM_SETTLEMENTDATE_DT"[DATE,7],
"from$_subquery$_005"."HR_PERSONALCODE_VC"[VARCHAR2,30], "from$_subquery$_005"."HR_NAME"[VARCHAR2,50],
"from$_subquery$_005"."HR_SEX"[VARCHAR2,5], "C"."HR_INSTITUTIONCODE_CH"[VARCHAR2,14],
"from$_subquery$_005"."HR_COMPENSATIONTYPE_VC"[VARCHAR2,5],
"from$_subquery$_005"."HR_DISEASECODE_VC"[VARCHAR2,10], "from$_subquery$_005"."HR_INHOSDATE_DT"[DATE,7],
"from$_subquery$_005"."HR_OUTHOSSTATUS_VC"[VARCHAR2,100], "from$_subquery$_005"."HR_OUTHOSDATE_DT"[DATE,7],
"D"."ME_INSTITUTINNAME_VC"[VARCHAR2,50]
10 - "from$_subquery$_005"."HP_HOSREGISTERCODE_VC"[VARCHAR2,20],
"from$_subquery$_005"."HP_PRESCRIPTIONFEE_DEC"[NUMBER,22],
"from$_subquery$_005"."HP_ALLOWEDCOMP_DEC"[NUMBER,22],
"from$_subquery$_005"."HCM_SETTLEMENTDATE_DT"[DATE,7],
"from$_subquery$_005"."HR_PERSONALCODE_VC"[VARCHAR2,30], "from$_subquery$_005"."HR_NAME"[VARCHAR2,50],
"from$_subquery$_005"."HR_SEX"[VARCHAR2,5], "C"."HR_INSTITUTIONCODE_CH"[VARCHAR2,14],
"from$_subquery$_005"."HR_COMPENSATIONTYPE_VC"[VARCHAR2,5],
"from$_subquery$_005"."HR_DISEASECODE_VC"[VARCHAR2,10], "from$_subquery$_005"."HR_INHOSDATE_DT"[DATE,7],
"from$_subquery$_005"."HR_OUTHOSSTATUS_VC"[VARCHAR2,100], "from$_subquery$_005"."HR_OUTHOSDATE_DT"[DATE,7]
11 - "A"."HP_HOSREGISTERCODE_VC"[VARCHAR2,20], "from$_subquery$_003"."HP_PRESCRIPTIONFEE_DEC"[NUMBER,22],
"from$_subquery$_003"."HP_ALLOWEDCOMP_DEC"[NUMBER,22],
"from$_subquery$_003"."HCM_SETTLEMENTDATE_DT"[DATE,7], "C"."HR_OUTHOSDATE_DT"[DATE,7],
"C"."HR_OUTHOSSTATUS_VC"[VARCHAR2,100], "C"."HR_INHOSDATE_DT"[DATE,7],
"C"."HR_DISEASECODE_VC"[VARCHAR2,10], "C"."HR_COMPENSATIONTYPE_VC"[VARCHAR2,5],
"C"."HR_INSTITUTIONCODE_CH"[VARCHAR2,14], "C"."HR_SEX"[VARCHAR2,5], "C"."HR_NAME"[VARCHAR2,50],
"C"."HR_PERSONALCODE_VC"[VARCHAR2,30]
12 - "A"."HP_HOSREGISTERCODE_VC"[VARCHAR2,20], "from$_subquery$_003"."HP_PRESCRIPTIONFEE_DEC"[NUMBER,22],
"from$_subquery$_003"."HP_ALLOWEDCOMP_DEC"[NUMBER,22], "from$_subquery$_003"."HCM_SETTLEMENTDATE_DT"[DATE,7]
13 - "A"."HP_HOSREGISTERCODE_VC"[VARCHAR2,20], "A"."HP_PRESCRIPTIONFEE_DEC"[NUMBER,22],
"A"."HP_ALLOWEDCOMP_DEC"[NUMBER,22], "B"."HCM_SETTLEMENTDATE_DT"[DATE,7]
14 - "A"."HP_HOSREGISTERCODE_VC"[VARCHAR2,20], "A"."HP_PRESCRIPTIONFEE_DEC"[NUMBER,22],
"A"."HP_ALLOWEDCOMP_DEC"[NUMBER,22]
15 - "B"."DELETEFLAG_CH"[CHARACTER,1], "B"."HCM_SETTLEMENTDATE_DT"[DATE,7]
16 - "B"."HCM_SETTLEMENTDATE_DT"[DATE,7], "B"."DELETEFLAG_CH"[CHARACTER,1]
17 - "C"."DELETEFLAG_CH"[CHARACTER,1], "C"."HR_OUTHOSDATE_DT"[DATE,7],
"C"."HR_OUTHOSSTATUS_VC"[VARCHAR2,100], "C"."HR_INHOSDATE_DT"[DATE,7],
"C"."HR_DISEASECODE_VC"[VARCHAR2,10], "C"."HR_COMPENSATIONTYPE_VC"[VARCHAR2,5],
"C"."HR_INSTITUTIONCODE_CH"[VARCHAR2,14], "C"."HR_SEX"[VARCHAR2,5], "C"."HR_NAME"[VARCHAR2,50],
"C"."HR_PERSONALCODE_VC"[VARCHAR2,30]
18 - "C"."HR_PERSONALCODE_VC"[VARCHAR2,30], "C"."HR_NAME"[VARCHAR2,50], "C"."HR_SEX"[VARCHAR2,5],
"C"."HR_INSTITUTIONCODE_CH"[VARCHAR2,14], "C"."HR_COMPENSATIONTYPE_VC"[VARCHAR2,5],
"C"."HR_DISEASECODE_VC"[VARCHAR2,10], "C"."HR_INHOSDATE_DT"[DATE,7],
"C"."HR_OUTHOSSTATUS_VC"[VARCHAR2,100], "C"."HR_OUTHOSDATE_DT"[DATE,7], "C"."DELETEFLAG_CH"[CHARACTER,1]
19 - "D"."DELETEFLAG_CH"[VARCHAR2,5], "D"."ME_INSTITUTINNAME_VC"[VARCHAR2,50]
20 - "D"."ME_INSTITUTINNAME_VC"[VARCHAR2,50], "D"."DELETEFLAG_CH"[VARCHAR2,5]
21 - "E"."PR_BRITHDAY_VC"[VARCHAR2,50], "E"."PR_PERSONALCODE_VC"[VARCHAR2,18]
22 - "E"."PR_PERSONALCODE_VC"[VARCHAR2,18], "E"."PR_BRITHDAY_VC"[VARCHAR2,50]
23 - "F"."PJ_CANTONCODE_CH"[VARCHAR2,12]
24 - "F"."PJ_CANTONCODE_CH"[VARCHAR2,12]
25 - "M"."DELETEFLAG_CH"[CHARACTER,1], "M"."MD_ICDCODE_VC"[VARCHAR2,10]
26 - "M"."MD_ICDCODE_VC"[VARCHAR2,10], "M"."DELETEFLAG_CH"[CHARACTER,1]
Note
-----
- dynamic sampling used for this statement (level=2)
Plan hash value: 1364454912
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 109K(100)| |
| 1 | HASH GROUP BY | | 330K| 86M| 92M| 109K (1)| 00:21:59 |
|* 2 | HASH JOIN | | 330K| 86M| | 90325 (1)| 00:18:04 |
|* 3 | TABLE ACCESS FULL | CFG_MAINTAINDISEASE | 31653 | 556K| | 138 (2)| 00:00:02 |
|* 4 | HASH JOIN | | 328K| 80M| | 90184 (1)| 00:18:03 |
|* 5 | TABLE ACCESS FULL | CFG_MEDICALESTABLISHMENT | 2926 | 108K| | 17 (0)| 00:00:01 |
|* 6 | HASH JOIN OUTER | | 328K| 68M| 61M| 90164 (1)| 00:18:02 |
|* 7 | HASH JOIN OUTER | | 322K| 57M| 50M| 64090 (2)| 00:12:50 |
|* 8 | HASH JOIN | | 316K| 47M| 21M| 45795 (2)| 00:09:10 |
|* 9 | TABLE ACCESS FULL | COMP_HOSREGISTER | 210K| 19M| | 3314 (1)| 00:00:40 |
|* 10 | HASH JOIN | | 310K| 18M| | 40322 (2)| 00:08:04 |
|* 11 | TABLE ACCESS FULL| COMP_HOSCOSTMAIN | 10467 | 316K| | 2208 (2)| 00:00:27 |
|* 12 | TABLE ACCESS FULL| COMP_HOSPRESCRIPTION | 6129K| 181M| | 38070 (2)| 00:07:37 |
| 13 | TABLE ACCESS FULL | JOIN_PERSONALRECORD | 3454K| 98M| | 8851 (1)| 00:01:47 |
| 14 | TABLE ACCESS FULL | JOIN_PERSONAJOIN | 3456K| 105M| | 15795 (1)| 00:03:10 |
-------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$9A5C6B1E
3 - SEL$9A5C6B1E / M@SEL$11
5 - SEL$9A5C6B1E / D@SEL$5
9 - SEL$9A5C6B1E / C@SEL$3
11 - SEL$9A5C6B1E / B@SEL$2
12 - SEL$9A5C6B1E / A@SEL$1
13 - SEL$9A5C6B1E / E@SEL$7
14 - SEL$9A5C6B1E / F@SEL$9
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
DB_VERSION('11.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$9A5C6B1E")
MERGE(@"SEL$72AEFE3E")
OUTLINE(@"SEL$F0958867")
OUTER_JOIN_TO_INNER(@"SEL$13")
OUTLINE(@"SEL$72AEFE3E")
MERGE(@"SEL$11")
MERGE(@"SEL$B97648DD")
OUTLINE(@"SEL$13")
OUTLINE(@"SEL$12")
OUTLINE(@"SEL$11")
OUTLINE(@"SEL$B97648DD")
MERGE(@"SEL$096E5AED")
MERGE(@"SEL$9")
OUTLINE(@"SEL$10")
OUTLINE(@"SEL$096E5AED")
MERGE(@"SEL$15E987C1")
MERGE(@"SEL$7")
OUTLINE(@"SEL$9")
OUTLINE(@"SEL$8")
OUTLINE(@"SEL$15E987C1")
MERGE(@"SEL$5")
MERGE(@"SEL$7237DA6D")
OUTLINE(@"SEL$7")
OUTLINE(@"SEL$6")
OUTLINE(@"SEL$5")
OUTLINE(@"SEL$7237DA6D")
MERGE(@"SEL$3")
MERGE(@"SEL$58A6D7F6")
OUTLINE(@"SEL$4")
OUTLINE(@"SEL$3")
OUTLINE(@"SEL$58A6D7F6")
MERGE(@"SEL$1")
OUTLINE(@"SEL$2")
OUTLINE(@"SEL$1")
FULL(@"SEL$9A5C6B1E" "B"@"SEL$2")
FULL(@"SEL$9A5C6B1E" "A"@"SEL$1")
FULL(@"SEL$9A5C6B1E" "C"@"SEL$3")
FULL(@"SEL$9A5C6B1E" "E"@"SEL$7")
FULL(@"SEL$9A5C6B1E" "F"@"SEL$9")
FULL(@"SEL$9A5C6B1E" "D"@"SEL$5")
FULL(@"SEL$9A5C6B1E" "M"@"SEL$11")
LEADING(@"SEL$9A5C6B1E" "B"@"SEL$2" "A"@"SEL$1" "C"@"SEL$3" "E"@"SEL$7" "F"@"SEL$9" "D"@"SEL$5"
"M"@"SEL$11")
USE_HASH(@"SEL$9A5C6B1E" "A"@"SEL$1")
USE_HASH(@"SEL$9A5C6B1E" "C"@"SEL$3")
USE_HASH(@"SEL$9A5C6B1E" "E"@"SEL$7")
USE_HASH(@"SEL$9A5C6B1E" "F"@"SEL$9")
USE_HASH(@"SEL$9A5C6B1E" "D"@"SEL$5")
USE_HASH(@"SEL$9A5C6B1E" "M"@"SEL$11")
PX_JOIN_FILTER(@"SEL$9A5C6B1E" "E"@"SEL$7")
SWAP_JOIN_INPUTS(@"SEL$9A5C6B1E" "C"@"SEL$3")
SWAP_JOIN_INPUTS(@"SEL$9A5C6B1E" "D"@"SEL$5")
SWAP_JOIN_INPUTS(@"SEL$9A5C6B1E" "M"@"SEL$11")
USE_HASH_AGGREGATION(@"SEL$9A5C6B1E")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("M"."MD_DISEASEID_VC"="C"."HR_DISEASECODE_VC")
3 - filter("M"."DELETEFLAG_CH"='N')
4 - access("C"."HR_INSTITUTIONCODE_CH"="D"."ME_INSTITUTIONCODE_VC")
5 - filter("D"."DELETEFLAG_CH"='N')
6 - access("F"."PJ_PERSONALCODE_VC"="E"."PR_PERSONALCODE_VC")
7 - access("C"."HR_PERSONALCODE_VC"="E"."PR_PERSONALCODE_VC")
8 - access("A"."HP_HOSREGISTERCODE_VC"="C"."HR_HOSREGISTERCODE_VC")
9 - filter("C"."DELETEFLAG_CH"='N')
10 - access("A"."HP_HOSREGISTERCODE_VC"="B"."HCM_HOSREGISTERCODE_VC")
11 - filter((TO_CHAR(INTERNAL_FUNCTION("B"."HCM_SETTLEMENTDATE_DT"),'yyyy-MM-dd')>'2017-03-31' AND
"B"."DELETEFLAG_CH"='N'))
12 - filter("A"."DELETEFLAG_CH"='N')
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "A"."HP_HOSREGISTERCODE_VC"||REPLACE("M"."MD_ICDCODE_VC",'.','')||TO_CHAR(INTERNAL_FUNCTION("C
"."HR_INHOSDATE_DT"),'yyyyMMdd')||TO_CHAR(INTERNAL_FUNCTION("C"."HR_OUTHOSDATE_DT"),'yyyyMMdd')||"C".
"HR_COMPENSATIONTYPE_VC"[51], "B"."HCM_SETTLEMENTDATE_DT"[DATE,7],
"F"."PJ_CANTONCODE_CH"[VARCHAR2,12], "C"."HR_INSTITUTIONCODE_CH"[VARCHAR2,14],
"D"."ME_INSTITUTINNAME_VC"[VARCHAR2,50], "C"."HR_INSTITUTIONCODE_CH"[VARCHAR2,14],
"D"."ME_INSTITUTINNAME_VC"[VARCHAR2,50], "C"."HR_PERSONALCODE_VC"[VARCHAR2,30],
"C"."HR_COMPENSATIONTYPE_VC"[VARCHAR2,5], "C"."HR_NAME"[VARCHAR2,50], "C"."HR_SEX"[VARCHAR2,5],
"E"."PR_BRITHDAY_VC"[VARCHAR2,50], "M"."MD_ICDCODE_VC"[VARCHAR2,10],
"C"."HR_OUTHOSSTATUS_VC"[VARCHAR2,100], "C"."HR_INHOSDATE_DT"[DATE,7],
"C"."HR_OUTHOSDATE_DT"[DATE,7], "C"."HR_INHOSDATE_DT"[DATE,7],
SUM(NVL("A"."HP_ALLOWEDCOMP_DEC",0))[22], SUM(NVL("A"."HP_PRESCRIPTIONFEE_DEC",0))[22]
2 - (#keys=1) "M"."MD_ICDCODE_VC"[VARCHAR2,10], "C"."HR_INSTITUTIONCODE_CH"[VARCHAR2,14],
"D"."ME_INSTITUTINNAME_VC"[VARCHAR2,50], "C"."HR_PERSONALCODE_VC"[VARCHAR2,30],
"E"."PR_BRITHDAY_VC"[VARCHAR2,50], "A"."HP_HOSREGISTERCODE_VC"[VARCHAR2,20],
"A"."HP_PRESCRIPTIONFEE_DEC"[NUMBER,22], "C"."HR_NAME"[VARCHAR2,50], "C"."HR_SEX"[VARCHAR2,5],
"F"."PJ_CANTONCODE_CH"[VARCHAR2,12], "C"."HR_COMPENSATIONTYPE_VC"[VARCHAR2,5],
"C"."HR_INHOSDATE_DT"[DATE,7], "C"."HR_OUTHOSSTATUS_VC"[VARCHAR2,100],
"C"."HR_OUTHOSDATE_DT"[DATE,7], "A"."HP_ALLOWEDCOMP_DEC"[NUMBER,22],
"B"."HCM_SETTLEMENTDATE_DT"[DATE,7]
3 - "M"."MD_DISEASEID_VC"[VARCHAR2,20], "M"."MD_ICDCODE_VC"[VARCHAR2,10]
4 - (#keys=1) "C"."HR_INSTITUTIONCODE_CH"[VARCHAR2,14], "D"."ME_INSTITUTINNAME_VC"[VARCHAR2,50],
"C"."HR_PERSONALCODE_VC"[VARCHAR2,30], "E"."PR_BRITHDAY_VC"[VARCHAR2,50],
"A"."HP_HOSREGISTERCODE_VC"[VARCHAR2,20], "A"."HP_PRESCRIPTIONFEE_DEC"[NUMBER,22],
"C"."HR_NAME"[VARCHAR2,50], "C"."HR_SEX"[VARCHAR2,5], "F"."PJ_CANTONCODE_CH"[VARCHAR2,12],
"C"."HR_COMPENSATIONTYPE_VC"[VARCHAR2,5], "C"."HR_DISEASECODE_VC"[VARCHAR2,10],
"C"."HR_INHOSDATE_DT"[DATE,7], "C"."HR_OUTHOSSTATUS_VC"[VARCHAR2,100],
"C"."HR_OUTHOSDATE_DT"[DATE,7], "A"."HP_ALLOWEDCOMP_DEC"[NUMBER,22],
"B"."HCM_SETTLEMENTDATE_DT"[DATE,7]
5 - "D"."ME_INSTITUTIONCODE_VC"[VARCHAR2,15], "D"."ME_INSTITUTINNAME_VC"[VARCHAR2,50]
6 - (#keys=1) "C"."HR_PERSONALCODE_VC"[VARCHAR2,30], "E"."PR_BRITHDAY_VC"[VARCHAR2,50],
"A"."HP_HOSREGISTERCODE_VC"[VARCHAR2,20], "A"."HP_PRESCRIPTIONFEE_DEC"[NUMBER,22],
"C"."HR_NAME"[VARCHAR2,50], "C"."HR_SEX"[VARCHAR2,5], "C"."HR_INSTITUTIONCODE_CH"[VARCHAR2,14],
"C"."HR_COMPENSATIONTYPE_VC"[VARCHAR2,5], "C"."HR_DISEASECODE_VC"[VARCHAR2,10],
"C"."HR_INHOSDATE_DT"[DATE,7], "C"."HR_OUTHOSSTATUS_VC"[VARCHAR2,100],
"C"."HR_OUTHOSDATE_DT"[DATE,7], "A"."HP_ALLOWEDCOMP_DEC"[NUMBER,22],
"B"."HCM_SETTLEMENTDATE_DT"[DATE,7], "F"."PJ_CANTONCODE_CH"[VARCHAR2,12]
7 - (#keys=1) "C"."HR_PERSONALCODE_VC"[VARCHAR2,30], "E"."PR_PERSONALCODE_VC"[VARCHAR2,18],
"A"."HP_HOSREGISTERCODE_VC"[VARCHAR2,20], "A"."HP_PRESCRIPTIONFEE_DEC"[NUMBER,22],
"C"."HR_NAME"[VARCHAR2,50], "C"."HR_SEX"[VARCHAR2,5], "C"."HR_INSTITUTIONCODE_CH"[VARCHAR2,14],
"C"."HR_COMPENSATIONTYPE_VC"[VARCHAR2,5], "C"."HR_DISEASECODE_VC"[VARCHAR2,10],
"C"."HR_INHOSDATE_DT"[DATE,7], "C"."HR_OUTHOSSTATUS_VC"[VARCHAR2,100],
"C"."HR_OUTHOSDATE_DT"[DATE,7], "A"."HP_ALLOWEDCOMP_DEC"[NUMBER,22],
"B"."HCM_SETTLEMENTDATE_DT"[DATE,7], "E"."PR_BRITHDAY_VC"[VARCHAR2,50]
8 - (#keys=1) "A"."HP_HOSREGISTERCODE_VC"[VARCHAR2,20], "C"."HR_PERSONALCODE_VC"[VARCHAR2,30],
"C"."HR_NAME"[VARCHAR2,50], "C"."HR_SEX"[VARCHAR2,5], "C"."HR_INSTITUTIONCODE_CH"[VARCHAR2,14],
"C"."HR_COMPENSATIONTYPE_VC"[VARCHAR2,5], "C"."HR_DISEASECODE_VC"[VARCHAR2,10],
"C"."HR_INHOSDATE_DT"[DATE,7], "C"."HR_OUTHOSSTATUS_VC"[VARCHAR2,100],
"C"."HR_OUTHOSDATE_DT"[DATE,7], "A"."HP_ALLOWEDCOMP_DEC"[NUMBER,22],
"B"."HCM_SETTLEMENTDATE_DT"[DATE,7], "A"."HP_PRESCRIPTIONFEE_DEC"[NUMBER,22]
9 - "C"."HR_HOSREGISTERCODE_VC"[VARCHAR2,20], "C"."HR_PERSONALCODE_VC"[VARCHAR2,30],
"C"."HR_NAME"[VARCHAR2,50], "C"."HR_SEX"[VARCHAR2,5], "C"."HR_INSTITUTIONCODE_CH"[VARCHAR2,14],
"C"."HR_COMPENSATIONTYPE_VC"[VARCHAR2,5], "C"."HR_DISEASECODE_VC"[VARCHAR2,10],
"C"."HR_INHOSDATE_DT"[DATE,7], "C"."HR_OUTHOSSTATUS_VC"[VARCHAR2,100], "C"."HR_OUTHOSDATE_DT"[DATE,7]
10 - (#keys=1) "A"."HP_HOSREGISTERCODE_VC"[VARCHAR2,20], "B"."HCM_SETTLEMENTDATE_DT"[DATE,7],
"A"."HP_PRESCRIPTIONFEE_DEC"[NUMBER,22], "A"."HP_ALLOWEDCOMP_DEC"[NUMBER,22]
11 - "B"."HCM_HOSREGISTERCODE_VC"[VARCHAR2,20], "B"."HCM_SETTLEMENTDATE_DT"[DATE,7]
12 - "A"."HP_HOSREGISTERCODE_VC"[VARCHAR2,20], "A"."HP_PRESCRIPTIONFEE_DEC"[NUMBER,22],
"A"."HP_ALLOWEDCOMP_DEC"[NUMBER,22]
13 - "E"."PR_PERSONALCODE_VC"[VARCHAR2,18], "E"."PR_BRITHDAY_VC"[VARCHAR2,50]
14 - "F"."PJ_PERSONALCODE_VC"[VARCHAR2,30], "F"."PJ_CANTONCODE_CH"[VARCHAR2,12]
已選擇198行。
同一條sql語句不通例項執行下的執行計劃做對比，除了效能問題例項中該sql執行計劃出現view、nestloop巢狀迴圈外、執行時間特別長外，就是
出現效能問題的例項中的sql語句執行計劃裡還出現了： OPT_PARAM('_complex_view_merging' 'false')，於是生成pfile檢視該例項的引數設定
pfile:
orclnew.__db_cache_size=36775657472
orclnew.__java_pool_size=134217728
orclnew.__large_pool_size=134217728
orclnew.__oracle_base='E:\app\Administrator'#ORACLE_BASE set from environment
orclnew.__pga_aggregate_target=13824425984
orclnew.__sga_target=41339060224
orclnew.__shared_io_pool_size=0
orclnew.__shared_pool_size=3892314112
orclnew.__streams_pool_size=134217728
*._complex_view_merging=FALSE
*._optimizer_use_feedback=FALSE
*._simple_view_merging=FALSE
*.audit_file_dest='E:\app\Administrator\admin\orclnew\adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='E:\APP\ADMINISTRATOR\ORADATA\ORCLNEW\CONTROL01.CTL','E:\APP\ADMINISTRATOR\FAST_RECOVERY_AREA\ORCLNEW\CONTROL02.CTL'#Restore Controlfile
*.db_block_size=8192
*.db_domain=''
*.db_name='orclnew'
*.db_recovery_file_dest='E:\app\Administrator\fast_recovery_area'
*.db_recovery_file_dest_size=4322230272
*.deferred_segment_creation=FALSE
*.diagnostic_dest='E:\app\Administrator'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclnewXDB)'
*.log_archive_dest_1='location=D:\orclnew\archivelog'
*.log_archive_format='arch_%r_%t_%s.arc'
*.open_cursors=300
*.pga_aggregate_target=13740539904
*.processes=500
*.remote_login_passwordfile='EXCLUSIVE'
*.session_cached_cursors=3000
*.sga_target=41221619712
*.undo_tablespace='UNDOTBS1'
出現效能問題的例項中的sql語句執行計劃裡還出現了： OPT_PARAM('_complex_view_merging' 'false')，於是生成pfile檢視該例項的引數設定
pfile:
orclnew.__db_cache_size=36775657472
orclnew.__java_pool_size=134217728
orclnew.__large_pool_size=134217728
orclnew.__oracle_base='E:\app\Administrator'#ORACLE_BASE set from environment
orclnew.__pga_aggregate_target=13824425984
orclnew.__sga_target=41339060224
orclnew.__shared_io_pool_size=0
orclnew.__shared_pool_size=3892314112
orclnew.__streams_pool_size=134217728
*._complex_view_merging=FALSE
*._optimizer_use_feedback=FALSE
*._simple_view_merging=FALSE
*.audit_file_dest='E:\app\Administrator\admin\orclnew\adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='E:\APP\ADMINISTRATOR\ORADATA\ORCLNEW\CONTROL01.CTL','E:\APP\ADMINISTRATOR\FAST_RECOVERY_AREA\ORCLNEW\CONTROL02.CTL'#Restore Controlfile
*.db_block_size=8192
*.db_domain=''
*.db_name='orclnew'
*.db_recovery_file_dest='E:\app\Administrator\fast_recovery_area'
*.db_recovery_file_dest_size=4322230272
*.deferred_segment_creation=FALSE
*.diagnostic_dest='E:\app\Administrator'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclnewXDB)'
*.log_archive_dest_1='location=D:\orclnew\archivelog'
*.log_archive_format='arch_%r_%t_%s.arc'
*.open_cursors=300
*.pga_aggregate_target=13740539904
*.processes=500
*.remote_login_passwordfile='EXCLUSIVE'
*.session_cached_cursors=3000
*.sga_target=41221619712
*.undo_tablespace='UNDOTBS1'
於是嘗試修改隱藏引數，執行
alter system set "_complex_view_merging"=TRUE scope=both;
alter system set "_simple_view_merging"=TRUE scope=both;
修改完成重啟資料庫服務後，問題sql執行計劃恢復正常，sql正常執行8s就出結果。
alter system set "_complex_view_merging"=TRUE scope=both;
alter system set "_simple_view_merging"=TRUE scope=both;
修改完成重啟資料庫服務後，問題sql執行計劃恢復正常，sql正常執行8s就出結果。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29357786/viewspace-2139805/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【sql調優之執行計劃】獲取執行計劃SQL
- Oracle調優之看懂Oracle執行計劃Oracle
- 【sql調優之執行計劃】sort operationsSQL
- 【sql調優之執行計劃】estimator iSQL
- 【sql調優之執行計劃】hash joinSQL
- 【sql調優之執行計劃】query transformerSQLORM
- 【sql調優之執行計劃】temp table transformationSQLORM
- 【sql調優之執行計劃】in相關的operationSQL
- 【sql調優之執行計劃】merge sort joinSQL
- Oracle引數-隱藏引數Oracle
- 檢視Oracle隱藏引數的SQLOracleSQL
- 【sql調優之執行計劃】使用hint(五)Hint for parallelSQLParallel
- 【sql調優之執行計劃】使用hint(四)Hints for JoinSQL
- mysql調優之——執行計劃explainMySqlAI
- oracle筆記整理14——效能調優之oracle執行計劃Oracle筆記
- 【sql調優之執行計劃】使用hint(三)Hints for Query TransformationsSQLORM
- 【sql調優之執行計劃】使用hint(二)Hints for Access PathsSQL
- Oracle 隱藏引數使用Oracle
- Oracle+高效能SQL引擎剖析：SQL優化與調優機制詳解-筆記之執行計劃（一）OracleSQL優化筆記
- Oracle+高效能SQL引擎剖析：SQL優化與調優機制詳解-筆記之執行計劃（二）OracleSQL優化筆記
- 隱藏引數查詢sqlSQL
- 【sql調優之執行計劃】使用hint(六) append and noappendSQLAPP
- 【sql調優之執行計劃】merge join cartesian and buffer sortSQL
- Oracle中SQL調優（SQL TUNING）之最權威獲取SQL執行計劃大全OracleSQL
- 檢視oracle隱藏引數Oracle
- Oracle sql執行計劃OracleSQL
- 【sql調優之執行計劃】使用hint(一)Hints for Optimization Approaches and GoalsSQLAPPGo
- 【sql調優之執行計劃】merge semi join and merge anti joinSQL
- 【sql調優之執行計劃】nested loops join and nested loop join outerSQLOOP
- Oracle 各版本引數/隱藏引數 介紹Oracle
- 影響執行計劃之oracle sql baseline與sql profile之互動OracleSQL
- 【優化】Oracle 執行計劃優化Oracle
- .Oracle固定執行計劃之SQL PROFILE概要檔案OracleSQL
- 建立索引調整sql的執行計劃索引SQL
- 【優化】ORACLE執行計劃分析優化Oracle
- oracle隱藏引數的檢視和使用Oracle
- Oracle中檢視已執行sql的執行計劃OracleSQL
- Oracle優化案例-從執行計劃定位SQL問題（三）Oracle優化SQL