oracle执行计划px,ORACLE SQL调优之执行计划与隐藏参数_complex_view_merging

最新推荐文章于 2022-04-05 03:30:28 发布
最新推荐文章于 2022-04-05 03:30:28 发布
阅读量446 收藏
点赞数
文章标签： oracle执行计划px

最近，赤峰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')

"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语句不通实例执行下的执行计划做对比，除了性能问题实例中该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'

于是尝试修改隐藏参数，执行

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/，如需转载，请注明出处，否则将追究法律责任。

网络控
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
Oracle SQL执行计划操作（8）——视图与集合相关操作
数据库生态圈（RDB & NoSQL & Bigdata）——专注于关系库优化（Oracle&Mysql&PG&SQL Server ）
11-23 1428
本文详细说明了Oracle SQL语句执行计划中视图与集合相关操作，同时，结合实例对其进行了深入讲解。
ORA-01732: 此视图的数据操纵操作非法 解决方案 oracle10g
热门推荐
dacong的专栏
12-17 4万+
ORA-01732: 此视图的数据操纵操作非法 解决方案， ORA-01732: data manipulation operation not legal on this view Oracle 10g的隐含参数_complex_view_merging引发的性能问题
Oracle11g Hidden Parameters | PERUMAL.ORG
10-08 990
Parameter Name Parameter Value Description _4031_dump_bitvec 67194879 bitvec to specify dumps prior t...
简单视图合并
weixin_30781631的博客
05-20 132
1　启发式查询转换 所有的启发式查询转换都是基于一套优化器内建的规则。在查询转换阶段，转换器会逐个针对这些规则对查询进行检查，确定其是否满足转换规则，一旦满足，转换器就对其进行转换。 1.1　简单视图合并 我们知道，视图（View）的实质就是一条查询语句。在解析阶段，语句中的每个视图都会被展开至一个查询块中。如果未做视图合并，优化器则会单独分析每个视图，并为定义视图的查询语句生成一个视图子计...
oracle 11.2.0.3 隐含参数
Lixora's DB Home
11-09 2147
System Parameters with Non-Default or Modified Values Collected from GV$SYSTEM_PARAMETER2 where isdefault = 'FALSE' OR ismodified != 'FALSE'. "Is Default" = FALSE means the parameter was set in t
srg.rar_Region merging_SRG
09-19
《统计区域合并（Statistical Region Merging, SRG）在图像处理中的应用》 在图像处理和计算机视觉领域，区域合并是一种重要的图像分割技术，它主要用于将图像中的相邻像素组合成更大的连通区域，以便更好地理解和...
图象合并程序.rar_bmp 256_image merging_图象合并
09-14
在IT领域，图象处理是不可或缺的一部分，而“图象合并程序.rar_bmp 256_image merging_图象合并”这个压缩包显然涉及到的是一个专门用于合并256色BMP图像的软件或代码资源。BMP，全称为Bitmap，是一种常见的位图图像...
RegionGrow.rar_matlab region grow_matlab regiongr_region-merging
07-15
区域生长的matlab源码 region grow
ARMA.rar_ARMA_ARMA matlab_matlab ARMA_时间序列 ARMA
最新发布
09-22
时频归并（Time-Frequency Merging）是一种将不同频率域信息结合的技术，通常用于分析非平稳时间序列。在这个过程中，我们可能会用到小波变换或短时傅立叶变换等工具，以便在时间域和频率域之间转换，并观察模型在...
ksm.rar_V2
09-20
标题中的"ksm.rar_V2"表明这是一个关于Linux内核内存合并（Kernel Samepage Merging，简称KSM）功能的更新版本，版本号为2.13.6。描述提到的"Memory merging support for Linux v2.13.6"证实了这一点，这是一项在...
oracle java赋予执行_ORACLE执行详解
weixin_29628635的博客
02-23 271
简介：本文全面详细介绍oracle执行计划的相关的概念，访问数据的存取方法，表之间的连接等内容。并有总结和概述，便于理解与记忆!+++目录---一．相关的概念Rowid的概念RecursiveSql概念Predicate(谓词)DRiving Table(驱动表)Probed Table(被探查表)组合索引(concatenatedindex)可选择性(selectivity)二．oracle...
【Oracle】执行计划详解
As before 如初
03-31 7372
定义 执行计划是一条查询语句在Oracle中的执行过程或访问路径的描述。 组成 目标SQL的正文、sql_id和执行计划对应的plan_hash_value 执行计划主体，主要有内部执行步骤、执行顺序、谓词信息、列信息、Cardinality、Cost等 执行计划的额外补充信息，是否动态采用（dynamic sampling）、是否Cardinality Feedback、是否SQL Profile 模块解读 主体Header ID：序号 Operation： 当前操作的内容
oracle optimizer_index_cost_adj,参数Optimizer_index_cost_adj 对执行计划的影响
weixin_28956763的博客
04-04 208
参数Optimizer_index_cost_adj对执行计划的影响在基于cbo的优化器模式下面，oracle会把sql语句的各种执行计划的成本做比较，取其最小的作为其最优执行计划，同时，在决定是用index scan还是full table scan访问一个表的时候，oracle会把index scan的执行成本转化为fts的成本，然后再根据其成本大小来决定执行计划，这个index scan和f...
oracle函数大全
ywj_316888的专栏
07-28 190
&lt;style type="text/css"&gt; .tt { font-weight: bold; font-size: 16px; color: #F00; } &lt;/style&gt; &lt;style type="text/css"&gt; &lt;!-- .t2 { color: #00F; font-weight: bold; font-size: 24px; } ...
Oracle 并行 QC PX
jnrjian的博客
04-05 1736
概念 串行执行： 串行执行时候,sql语句由一个server process处理(既只运行在一个cpu上),所以sql语句所能使用的资源受cpu限制,除去异步i/o外server process 处理cpu操作时，不可访问disk。 并行处理： 就是多个slave process一起处理同一个sql语句 概念 串行执行：串行执行时候,sql语句由一个server process处理(既只运行在一个cpu上),所以sql语句所能使用的资源受cpu限制,除去异步i/o外server process处..
oracle执行计划px,sql 并行与什么有关，执行计划显示 PX COORDINATOR ，表的并行度都为1...
weixin_42514921的博客
04-04 859
-----------------------------------------------------------------------------------------------------------------------------| Id | Operation | Name ...
oracle 并行执行计划,Oracle 对表设置并行度对执行计划的影响
weixin_42353186的博客
04-05 290
Oracle数据库版本11.2.0.4，最近发现有一条SQL很慢，执行一次要20多s,GG_FUNCTION_LOCATION这张表很大，发现这张表有并行度，修改并行度之后，执行计划变了走索引了，执行时间为2s。说明一个问题，当表有并行度的时候，SQL倾向于走索引。有并行度的执行计划：---------------------------------------------------------...
【SQL 优化器技术系列】简单 view merging
余璜的技术博客
07-14 253
Oracle 2005 年出了一个 30 多页的小册子，《Query Optimization in Oracle Database10g Release 2》，介绍了常见的优化器技术。 我是做 SQL 执行的，优化部分只了解皮毛，从没有系统学习过。本系列逐个学习和介绍，自我提升，也帮助他人。 第一篇，改写 最简单的改写是 view merging。 create view MY_VIEW as SELECT * FROM factory WHERE CITY = 'Wuhan'; select *.
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值