某系统上午9点到11点的AWR报告中TOP SQL,其中消耗时间最长的花了9770秒,该SQL_ID为36cbabzyq13gy
这条SQL语句与SQL_ID为0frcad5600xdu,g1a0qu2b42j83所对应的SQL语句除了文本值不一样外,其它部分是相同的,这里没有使用绑定变量
SQL ordered by Elapsed Time Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code. % Total DB Time is the Elapsed Time of the SQL statement divided into the Total Database Time multiplied by 100 Elapsed Time (s) CPU Time (s) Executions Elap per Exec (s) % Total DB Time SQL Id SQL Module SQL Text 9,770 6,418 1 9769.94 16.02 36cbabzyq13gy w3wp.exe SELECT /*+ index(lt, PK_LV_U... 5,831 3,851 2 2915.28 9.56 0frcad5600xdu w3wp.exe SELECT /*+ index(lt, PK_LV_U... 2,495 1,643 1 2495.48 4.09 g1a0qu2b42j83 w3wp.exe SELECT /*+ index(lt, PK_LV_U... 2,348 1,482 62 37.86 3.85 a7dkwg8uhrwkj JDBC Thin Client select * from ( select a.hosp... 772 341 22 35.10 1.27 2vpny9ut5dcm6 JDBC Thin Client select t.pay_type as pay_... 670 438 29 23.10 1.10 acj1640jvr3u5 JDBC Thin Client select t.biz_flag, t1.name, t1... 618 317 60 10.30 1.01 ggrctzgtcg14s JDBC Thin Client select t.pay_type as pay_... 617 39 2 308.52 1.01 c5m1092x9vg2y JDBC Thin Client select w.hospital_id, t.hospit... 605 398 1 604.51 0.99 3yy1wbuvsxm93 w3wp.exe SELECT /*+ index(lt, PK_LV_U... 381 55 1 380.52 0.62 6q1xuznmvsu5d w3wp.exe SELECT t_center.center_name, ...
从awrsqrpt报告中可以看到,该SQL的逻辑读为3亿多次
Stat Name Statement Total Per Execution % Snap Total Elapsed Time (ms) 9,769,937 9,769,936.85 16.02 CPU Time (ms) 6,417,920 6,417,920.27 20.69 Executions 1 Buffer Gets 361,831,845 361,831,845.00 15.35 Disk Reads 23,989 23, 989.00 0.05 Parse Calls 1 1.00 0.00 Rows 0 0.00 User I/O Wait Time (ms) 27,723 Cluster Wait Time (ms) 0 Application Wait Time (ms) 0 Concurrency Wait Time (ms) 460 Invalidations 0 Version Count 2 Sharable Mem(KB) 275
SQL_ID为36cbabzyq13gy的SQL语句如下:
SELECT /*+ index(lt,PK_LV_URBAN_TOPAY_TMP) */ bi.indi_id, bi.name, pt.pers_name, bs.sex_name, lt.pay_money, bi.idcard, bi.birthday, bf.headed_name, lt.fac_pay_date, lbb.audit_man, tab_hosp.hospital_name as hospital_name, to_char(lbb.make_bill_tm, 'yyyy-mm-dd') as make_bill_tm, bf.telephone, nvl((decode(lt.intensive_disability_flag, 1, decode(lt.lowflag, 1, '重症伤残,', '重症伤残'), '') || decode(lt.lowflag, 1, decode(lt.nothing_flag, 1, '低保,', '低保'), '') || decode(lt.nothing_flag, 1, '三无', '')), '标准') as subsidykide FROM lv_urban_topay_tmp lt, bs_insured bi, bs_sex bs, bs_person_type pt, bs_pres_insur bpi, bs_family bf, lv_busi_bill lbb, lv_busi_record lbr, lv_busi_assign lba, (select bh.hospital_name, bph.indi_id from bs_pers_hosp bph, bs_hospital bh where bph.hospital_id = bh.hospital_id and bph.first_flag = 1 and bph.end_year = '2015') tab_hosp WHERE nvl(lt.busi_asg_no, 0) <> 0 AND nvl(lt.busi_asg_no, 0) not in (-999, -998, -997, -981, -980) AND lt.fac_pay_date is not null AND bi.indi_id = lt.indi_id AND bs.sex = bi.sex AND bi.indi_id = tab_hosp.indi_id(+) AND lbr.busi_reco_no = lba.busi_reco_no AND lbr.busi_bill_sn = lbb.busi_bill_sn AND lt.center_id = lbb.center_id AND lt.busi_asg_no = lba.busi_asg_no AND lt.indi_id = bi.indi_id AND pt.pers_type = bi.pers_type AND bpi.indi_id = bi.indi_id AND lt.center_id = pt.center_id AND bf.family_id = bi.family_id AND bf.family_sta = 1 AND bi.indi_sta = 1 AND bpi.indi_join_sta = 1 AND bf.center_id = lt.center_id AND bf.corp_id = lt.corp_id AND lt.policy_item_code like '%INDI_TOPAY' AND lt.corp_id = '19159' AND bpi.insr_detail_code = 21 AND lt.center_id = '430726' AND lt.curr_year = '2015' AND lt.fac_pay_date >= to_date('2014-12-01 00:00:00', 'yyyy-MM-dd hh24:mi:ss') AND lt.fac_pay_date < = to_date('2015-01-05 23:59:59', 'yyyy-MM-dd hh24:mi:ss') and exists (select 'X' FROM lv_busi_bill lbb, lv_busi_record lbr, lv_busi_assign lba, lv_urban_topay_tmp lutt WHERE lbr.busi_reco_no = lba.busi_reco_no AND lbr.busi_bill_sn = lbb.busi_bill_sn AND lbb.center_id = '430726' AND lutt.corp_id = '19159' AND lutt.center_id = lbb.center_id AND lutt.busi_asg_no = lba.busi_asg_no and lba.busi_asg_no = lt.busi_asg_no and lutt.indi_id = bi.indi_id) order by lt.fac_pay_date, bi.indi_id, bi.name
通过执地xplan脚本来获得SQL_ID为36cbabzyq13gy的执行计划,其执行计划如下
----------------------------------------------------------------------------------------------------------------------------------- | Id | Order | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | ----------------------------------------------------------------------------------------------------------------------------------- | 0 | 45 | SELECT STATEMENT | | | | 20484 (100)| | | 1 | 44 | SORT ORDER BY | | 1 | 290 | 20484 (3)| 00:04:06 | | 2 | 43 | NESTED LOOPS | | 1 | 290 | 20483 (3)| 00:04:06 | | 3 | 40 | NESTED LOOPS | | 1 | 265 | 20482 (3)| 00:04:06 | | 4 | 37 | NESTED LOOPS | | 1 | 254 | 20481 (3)| 00:04:06 | | 5 | 34 | NESTED LOOPS | | 1 | 242 | 20480 (3)| 00:04:06 | | 6 | 32 | NESTED LOOPS | | 1 | 230 | 20479 (3)| 00:04:06 | | 7 | 29 | HASH JOIN | | 168 | 27720 | 17063 (3)| 00:03:25 | | 8 | 13 | VIEW | VW_SQ_1 | 168 | 2016 | 2441 (1)| 00:00:30 | | 9 | 12 | HASH UNIQUE | | 168 | 9408 | | | | 10 | 11 | NESTED LOOPS | | 168 | 9408 | 2441 (1)| 00:00:30 | | 11 | 8 | NESTED LOOPS | | 1718 | 75592 | 2097 (1)| 00:00:26 | | 12 | 5 | NESTED LOOPS | | 1758 | 58014 | 1745 (1)| 00:00:21 | | 13 | 2 | TABLE ACCESS BY INDEX ROWID| LV_URBAN_TOPAY_TMP | 1742 | 36582 | 1397 (1)| 00:00:17 | | 14 | 1 | INDEX RANGE SCAN | IDX_LV_URBAN_TOPAY_TMP_CORP_ID | 18770 | | 14 (0)| 00:00:01 | | 15 | 4 | TABLE ACCESS BY INDEX ROWID| LV_BUSI_ASSIGN | 1 | 12 | 1 (0)| 00:00:01 | | 16 | 3 | INDEX UNIQUE SCAN | PK_LV_BUSI_ASSIGN | 1 | | 1 (0)| 00:00:01 | | 17 | 7 | TABLE ACCESS BY INDEX ROWID | LV_BUSI_RECORD | 1 | 11 | 1 (0)| 00:00:01 | | 18 | 6 | INDEX UNIQUE SCAN | PK_LV_BUSI_RECORD | 1 | | 1 (0)| 00:00:01 | | 19 | 10 | TABLE ACCESS BY INDEX ROWID | LV_BUSI_BILL | 1 | 12 | 1 (0)| 00:00:01 | | 20 | 9 | INDEX UNIQUE SCAN | PK_LV_BUSI_BILL | 1 | | 1 (0)| 00:00:01 | | 21 | 28 | NESTED LOOPS OUTER | | 123 | 18819 | 14622 (3)| 00:02:56 | | 22 | 21 | HASH JOIN | | 123 | 13776 | 14375 (3)| 00:02:53 | | 23 | 15 | TABLE ACCESS BY INDEX ROWID | BS_FAMILY | 102 | 3264 | 93 (0)| 00:00:02 | | 24 | 14 | INDEX RANGE SCAN | IDX_BS_FAMILY_CORP_ID | 1203 | | 1 (0)| 00:00:01 | | 25 | 20 | HASH JOIN | | 1081K| 82M| 14272 (3)| 00:02:52 | | 26 | 16 | TABLE ACCESS FULL | BS_PERSON_TYPE | 11 | 198 | 3 (0)| 00:00:01 | | 27 | 19 | HASH JOIN | | 1080K| 63M| 14258 (3)| 00:02:52 | | 28 | 17 | TABLE ACCESS FULL | BS_SEX | 4 | 24 | 3 (0)| 00:00:01 | | 29 | 18 | TABLE ACCESS FULL | BS_INSURED | 1080K| 57M| 14244 (3)| 00:02:51 | | 30 | 27 | VIEW PUSHED PREDICATE | | 1 | 41 | 2 (0)| 00:00:01 | | 31 | 26 | NESTED LOOPS | | 1 | 57 | 2 (0)| 00:00:01 | | 32 | 23 | TABLE ACCESS BY INDEX ROWID | BS_PERS_HOSP | 1 | 25 | 1 (0)| 00:00:01 | | 33 | 22 | INDEX RANGE SCAN | PK_BS_PERS_HOSP | 2 | | 1 (0)| 00:00:01 | | 34 | 25 | TABLE ACCESS BY INDEX ROWID | BS_HOSPITAL | 1 | 32 | 1 (0)| 00:00:01 | | 35 | 24 | INDEX UNIQUE SCAN | PK_BS_HOSPITAL | 1 | | 1 (0)| 00:00:01 | | 36 | 31 | TABLE ACCESS BY INDEX ROWID | LV_URBAN_TOPAY_TMP | 1 | 65 | 3416 (4)| 00:00:41 | | 37 | 30 | INDEX FULL SCAN | PK_LV_URBAN_TOPAY_TMP | 1 | | 3416 (4)| 00:00:41 | | 38 | 33 | INDEX UNIQUE SCAN | INDEX_BS_PRES_INSUR_UNIQUE | 1 | 12 | 1 (0)| 00:00:01 | | 39 | 36 | TABLE ACCESS BY INDEX ROWID | LV_BUSI_ASSIGN | 1 | 12 | 1 (0)| 00:00:01 | | 40 | 35 | INDEX UNIQUE SCAN | PK_LV_BUSI_ASSIGN | 1 | | 1 (0)| 00:00:01 | | 41 | 39 | TABLE ACCESS BY INDEX ROWID | LV_BUSI_RECORD | 1 | 11 | 1 (0)| 00:00:01 | | 42 | 38 | INDEX UNIQUE SCAN | PK_LV_BUSI_RECORD | 1 | | 1 (0)| 00:00:01 | | 43 | 42 | TABLE ACCESS BY INDEX ROWID | LV_BUSI_BILL | 1 | 25 | 1 (0)| 00:00:01 | | 44 | 41 | INDEX UNIQUE SCAN | PK_LV_BUSI_BILL | 1 | | 1 (0)| 00:00:01 | -----------------------------------------------------------------------------------------------------------------------------------
*********** Table Level *********** Table Number Empty Average Chain Average Global User Sample Date Name of Rows Blocks Blocks Space Count Row Len Stats Stats Size MM-DD-YYYY --------------- -------------- -------- ------------ ------- -------- ------- ------ ------ -------------- ---------- LV_URBAN_TOPAY_ 22,991,252 580,702 8,018 904 5 175 YES NO 5,747,813 12-08-2014 TMP Column Column Distinct Number Number Global User Sample Date Name Details Values Density Buckets Nulls Stats Stats Size MM-DD-YYYY ------------------------- ------------------------ ------------ ------- ------- ---------- ------ ------ -------------- ---------- URBAN_TOPAY_SN NUMBER(12,0) NOT NULL 17,882,394 0 254 0 YES NO 5,747,813 12-08-2014 CORP_ID NUMBER(12,0) NOT NULL 848 0 254 0 YES NO 5,935 12-08-2014 INDI_ID NUMBER(12,0) NOT NULL 1,934,475 0 254 0 YES NO 5,747,813 12-08-2014 NAME VARCHAR2(20) 380,042 0 254 0 YES NO 580,870 12-08-2014 IDCARD VARCHAR2(20) 857,905 0 1 4,611,866 YES NO 464,507 12-08-2014 PERS_TYPE NUMBER(2,0) 4 0 4 0 YES NO 5,935 12-08-2014 POLICY_ITEM_CODE VARCHAR2(50) NOT NULL 9 0 9 0 YES NO 5,935 12-08-2014 POLICY_ITEM_NAME VARCHAR2(50) 9 0 9 0 YES NO 5,935 12-08-2014 INTENSIVE_DISABILITY_FLAG NUMBER(1,0) 2 1 1 0 YES NO 5,935 12-08-2014 VETERAN_BENEFIT_FLAG NUMBER(1,0) 1 1 1 0 YES NO 5,935 12-08-2014 STIPEND_FLAG NUMBER(1,0) 1 1 1 0 YES NO 5,935 12-08-2014 LOANS_FLAG NUMBER(1,0) 2 1 1 0 YES NO 5,935 12-08-2014 REGISTERED_NUMBER VARCHAR2(20) 0 0 0 ########## YES NO 12-08-2014 PAY_INFO_NO NUMBER(12,0) 6,086,462 0 1 0 YES NO 5,747,813 12-08-2014 MONEY_NO NUMBER(12,0) 17,398,621 0 1 0 YES NO 5,747,813 12-08-2014 INDIPAYSER NUMBER(12,0) 22,991,252 0 1 0 YES NO 580,870 12-08-2014 CALC_PRD VARCHAR2(6) 28 0 28 0 YES NO 5,935 12-08-2014 SRC_TYPE NUMBER(2,0) 4 0 4 0 YES NO 5,935 12-08-2014 MONEY_ID NUMBER(3,0) 8 0 8 0 YES NO 5,935 12-08-2014 PAY_MONEY NUMBER(12,2) 16 0 16 0 YES NO 5,935 12-08-2014 DO_FLAG NUMBER(1,0) 1 0 1 0 YES NO 5,935 12-08-2014 CENTER_ID VARCHAR2(10) 10 0 10 0 YES NO 5,935 12-08-2014 LOWFLAG NUMBER(1,0) 2 1 1 0 YES NO 5,935 12-08-2014 NOTHING_FLAG NUMBER(1,0) 2 1 1 0 YES NO 5,935 12-08-2014 FAMILY_ID NUMBER(12,0) 660,682 0 1 714,868 YES NO 562,833 12-08-2014 URBAN_TYPE NUMBER(2,0) 4 0 4 0 YES NO 5,935 12-08-2014 URBAN_TYPE_NAME VARCHAR2(50) 4 0 1 0 YES NO 5,935 12-08-2014 BUSI_ASG_NO NUMBER(12,0) 2,223 0 254 3,836,517 YES NO 4,967 12-08-2014 FAC_PAY_DATE DATE 907 0 254 3,836,517 YES NO 4,967 12-08-2014 CURR_YEAR VARCHAR2(4) NOT NULL 8 0 8 0 YES NO 5,935 12-08-2014 MOD_TIMESTAMP TIMESTAMP(6)(11) 0 0 0 ########## YES NO 12-08-2014 IS_PRINT NUMBER(1,0) 1 1 1 0 YES NO 5,935 12-08-2014 CURR_YEAR_BEG_PRD VARCHAR2(6) 8 0 1 0 YES NO 5,935 12-08-2014 CURR_YEAR_END_PRD VARCHAR2(6) 8 0 1 0 YES NO 5,935 12-08-2014 B Average Average Index Tree Leaf Distinct Number Leaf Blocks Data Blocks Cluster Global User Sample Date Name Unique Level Blks Keys of Rows Per Key Per Key Factor Stats Stats Size MM-DD-YYYY --------------- --------- ----- ---- -------------- -------------- ----------- ----------- ------------ ------ ------ -------------- ---------- PK_LV_URBAN_TOP UNIQUE 2 #### 21,623,824 21,623,824 1 1 2,737,193 YES NO 137,855 12-08-2014 AY_TMP DX_LV_URBAN_TOP NONUNIQUE 2 #### 6,086,462 22,613,945 1 1 10,946,874 YES NO 429,108 12-08-2014 AY_TMP_PAY IDX_LV_URBAN_TO NONUNIQUE 2 #### 2,223 18,979,800 17 708 1,574,170 YES NO 530,075 12-08-2014 PAY_TMP_BUS IDX_LV_URBAN_TO NONUNIQUE 3 #### 223 22,474,402 393 25,642 5,718,207 YES NO 284,917 12-08-2014 PAY_TMP_CENTER IDX_LV_URBAN_TO NONUNIQUE 2 #### 660,682 21,796,771 1 17 11,256,787 YES NO 407,563 12-08-2014 PAY_TMP_FAM IDX_LV_URBAN_TO NONUNIQUE 2 #### 1,934,475 21,768,753 1 7 15,125,646 YES NO 381,994 12-08-2014 PAY_TMP_INDI IDX_LV_URBAN_TO NONUNIQUE 2 #### 17,398,621 21,880,953 1 1 11,499,589 YES NO 423,560 12-08-2014 PAY_TMP_NO IDX_LV_URBAN_TO NONUNIQUE 2 #### 22,485,115 22,485,115 1 1 2,820,116 YES NO 121,303 12-08-2014 PAY_TMP_SOME IDX_LV_URBAN_TO NONUNIQUE 3 #### 996 22,727,163 80 8,493 8,459,953 YES NO 311,063 12-08-2014 PAY_TMP_CORP_ID IDX_LV_URBAN_TO NONUNIQUE 2 #### 8 23,228,508 7,131 ####### 828,346 YES NO 465,750 12-08-2014 PAY_TMP_YEAR Index Column Col Column Name Name Pos Details --------------- ------------------------- ---- ------------------------ DX_LV_URBAN_TOP PAY_INFO_NO 1 NUMBER(12,0) AY_TMP_PAY PAY_INFO_NO 1 NUMBER(12,0) IDX_LV_URBAN_TO BUSI_ASG_NO 1 NUMBER(12,0) PAY_TMP_BUS BUSI_ASG_NO 1 NUMBER(12,0) IDX_LV_URBAN_TO CENTER_ID 1 VARCHAR2(10) PAY_TMP_CENTER CENTER_ID 1 VARCHAR2(10) CURR_YEAR 2 VARCHAR2(4) NOT NULL CURR_YEAR 2 VARCHAR2(4) NOT NULL PERS_TYPE 3 NUMBER(2,0) PERS_TYPE 3 NUMBER(2,0) IDX_LV_URBAN_TO CORP_ID 1 NUMBER(12,0) NOT NULL PAY_TMP_CORP_ID CORP_ID 1 NUMBER(12,0) NOT NULL CURR_YEAR 2 VARCHAR2(4) NOT NULL CURR_YEAR 2 VARCHAR2(4) NOT NULL PERS_TYPE 3 NUMBER(2,0) PERS_TYPE 3 NUMBER(2,0) IDX_LV_URBAN_TO FAMILY_ID 1 NUMBER(12,0) PAY_TMP_FAM FAMILY_ID 1 NUMBER(12,0) IDX_LV_URBAN_TO INDI_ID 1 NUMBER(12,0) NOT NULL PAY_TMP_INDI INDI_ID 1 NUMBER(12,0) NOT NULL IDX_LV_URBAN_TO MONEY_NO 1 NUMBER(12,0) PAY_TMP_NO MONEY_NO 1 NUMBER(12,0) IDX_LV_URBAN_TO URBAN_TOPAY_SN 1 NUMBER(12,0) NOT NULL PAY_TMP_SOME URBAN_TOPAY_SN 1 NUMBER(12,0) NOT NULL INDI_ID 2 NUMBER(12,0) NOT NULL INDI_ID 2 NUMBER(12,0) NOT NULL POLICY_ITEM_CODE 3 VARCHAR2(50) NOT NULL POLICY_ITEM_CODE 3 VARCHAR2(50) NOT NULL CORP_ID 4 NUMBER(12,0) NOT NULL CORP_ID 4 NUMBER(12,0) NOT NULL BUSI_ASG_NO 5 NUMBER(12,0) BUSI_ASG_NO 5 NUMBER(12,0) CURR_YEAR 6 VARCHAR2(4) NOT NULL CURR_YEAR 6 VARCHAR2(4) NOT NULL IDX_LV_URBAN_TO CURR_YEAR 1 VARCHAR2(4) NOT NULL PAY_TMP_YEAR CURR_YEAR 1 VARCHAR2(4) NOT NULL PK_LV_URBAN_TOP URBAN_TOPAY_SN 1 NUMBER(12,0) NOT NULLAY_TMP INDI_ID 2 NUMBER(12,0) NOT NULL POLICY_ITEM_CODE 3 VARCHAR2(50) NOT NULL CORP_ID 4 NUMBER(12,0) NOT NULL CURR_YEAR 5 VARCHAR2(4) NOT NULL
从上面的显示结果可以看到执行计划的第一步执行的是对索引IDX_LV_URBAN_TOPAY_TMP_CORP_ID执行索引范围扫描并没有使用Hint所指定的PK_LV_URBAN_TOPAY_TMP,而且从上面的显示的索引信息部分可以看到索引PK_LV_URBAN_TOPAY_TMP是由列URBAN_TOPAY_SN, INDI_ID, POLICY_ITEM_CODE, CORP_ID, CURR_YEAR组成的复合索引而查询条件没有URBAN_TOPAY_SN字段使用不了这个索引从上面的执行计划可以看到这一结果。上面执行计划中步骤28与步骤29执行哈希连接,都是全表扫描,其中步骤Id=29是对BS_INSURED执行100多万条记录执行全表扫并且将它们的结果与表BS_PERSON_TYPE执行哈希连接最后与BS_FAMILY执行哈希连接返回记录123条数据,其成本是14375,其中对表BS_INSURED执行全表扫描的成本就是14244。而执行计划执行的第一步就是访问LV_URBAN_TOPAY_TMP,而where条件中有 bi.indi_id = lt.indi_id AND bs.sex = bi.sex and pt.pers_type = bi.pers_type,且这条连接条件都存在索引,那么就不应该那对BS_INSURED,BS_SEX,BS_PERSON_TYPE,BS_FAMILY 这四个表之间进行哈希连接后再与这四个表(LV_URBAN_TOPAY_TMP,LV_BUSI_ASSIGN,LV_BUSI_RECORD,LV_BUSI_BILL)连接后的结果集之间执行哈希连接。而是应该
在这四个表(LV_URBAN_TOPAY_TMP,LV_BUSI_ASSIGN,LV_BUSI_RECORD,LV_BUSI_BILL)连接后与BS_INSURED执行嵌套循环连接,因为有where条件bi.indi_id = lt.indi_id 而且indi_id在表BS_INSURED中是主键,从上面的索引信息中可以看到IDX_LV_URBAN_TOPAY_TMP_CORP_ID索引的distinct key是996,而where条件能使用CBO选择使用该索引从执行计划的Order列为1的步骤可知首先执行的就是对索引IDX_LV_URBAN_TOPAY_TMP_CORP_ID的索引范围扫描。当LV_URBAN_TOPAY_TMP与BS_INSURED执行完嵌套循环连接后因为有where条件bs.sex = bi.sex and pt.pers_type = bi.pers_type所以对BS_SEX,BS_PERSON_TYPE,BS_FAMILY表都应该执行嵌套循环连接。
这里优化步骤是首先删除原来SQL语句中所使用的Hint”/*+ index(lt,PK_LV_URBAN_TOPAY_TMP) */”后并执行SQL语句:
SQL>SELECT bi.indi_id, bi.name, pt.pers_name, bs.sex_name, lt.pay_money, bi.idcard, bi.birthday, bf.headed_name, lt.fac_pay_date, lbb.audit_man, tab_hosp.hospital_name as hospital_name, to_char(lbb.make_bill_tm, 'yyyy-mm-dd') as make_bill_tm, bf.telephone, nvl((decode(lt.intensive_disability_flag, 1, decode(lt.lowflag, 1, '重症伤残,', '重症伤残'), '') || decode(lt.lowflag, 1, decode(lt.nothing_flag, 1, '低保,', '低保'), '') || decode(lt.nothing_flag, 1, '三无', '')), '标准') as subsidykide FROM lv_urban_topay_tmp lt, bs_insured bi, bs_sex bs, bs_person_type pt, bs_pres_insur bpi, bs_family bf, lv_busi_bill lbb, lv_busi_record lbr, lv_busi_assign lba, (select bh.hospital_name, bph.indi_id from bs_pers_hosp bph, bs_hospital bh where bph.hospital_id = bh.hospital_id and bph.first_flag = 1 and bph.end_year = '2015') tab_hosp WHERE nvl(lt.busi_asg_no, 0) <> 0 AND nvl(lt.busi_asg_no, 0) not in (-999, -998, -997, -981, -980) AND lt.fac_pay_date is not null AND bi.indi_id = lt.indi_id AND bs.sex = bi.sex AND bi.indi_id = tab_hosp.indi_id(+) AND lbr.busi_reco_no = lba.busi_reco_no AND lbr.busi_bill_sn = lbb.busi_bill_sn AND lt.center_id = lbb.center_id AND lt.busi_asg_no = lba.busi_asg_no AND lt.indi_id = bi.indi_id AND pt.pers_type = bi.pers_type AND bpi.indi_id = bi.indi_id AND lt.center_id = pt.center_id AND bf.family_id = bi.family_id AND bf.family_sta = 1 AND bi.indi_sta = 1 AND bpi.indi_join_sta = 1 AND bf.center_id = lt.center_id AND bf.corp_id = lt.corp_id AND lt.policy_item_code like '%INDI_TOPAY' AND lt.corp_id = '19159' AND bpi.insr_detail_code = 21 AND lt.center_id = '430726' AND lt.curr_year = '2015' AND lt.fac_pay_date >= to_date('2014-12-01 00:00:00', 'yyyy-MM-dd hh24:mi:ss') AND lt.fac_pay_date < = to_date('2015-01-05 23:59:59', 'yyyy-MM-dd hh24:mi:ss') and exists (select 'X' FROM lv_busi_bill lbb, lv_busi_record lbr, lv_busi_assign lba, lv_urban_topay_tmp lutt WHERE lbr.busi_reco_no = lba.busi_reco_no AND lbr.busi_bill_sn = lbb.busi_bill_sn AND lbb.center_id = '430726' AND lutt.corp_id = '19159' AND lutt.center_id = lbb.center_id AND lutt.busi_asg_no = lba.busi_asg_no and lba.busi_asg_no = lt.busi_asg_no and lutt.indi_id = bi.indi_id) order by lt.fac_pay_date, bi.indi_id, bi.name ; ....省略输出结果 2304 rows selected. Elapsed: 00:00:08.83
一共返回了2304条记录,使用时间是8.83秒,其执行计划如下,现在消除了对BS_INSURED的全表扫描,选择最优的执行计划
-------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 278 | 88 (2)| 00:00:02 | | 1 | SORT ORDER BY | | 1 | 278 | 88 (2)| 00:00:02 | | 2 | NESTED LOOPS | | 1 | 278 | 82 (0)| 00:00:01 | | 3 | NESTED LOOPS OUTER | | 1 | 272 | 81 (0)| 00:00:01 | | 4 | NESTED LOOPS | | 1 | 231 | 79 (0)| 00:00:01 | | 5 | NESTED LOOPS | | 1 | 219 | 78 (0)| 00:00:01 | | 6 | NESTED LOOPS | | 1 | 201 | 77 (0)| 00:00:01 | | 7 | NESTED LOOPS | | 1 | 169 | 76 (0)| 00:00:01 | | 8 | NESTED LOOPS | | 1 | 113 | 75 (0)| 00:00:01 | | 9 | NESTED LOOPS | | 1 | 88 | 74 (0)| 00:00:01 | | 10 | NESTED LOOPS | | 1 | 77 | 73 (0)| 00:00:01 | |* 11 | TABLE ACCESS BY INDEX ROWID | LV_URBAN_TOPAY_TMP | 1 | 65 | 72 (0)| 00:00:01 | |* 12 | INDEX RANGE SCAN | IDX_LV_URBAN_TOPAY_TMP_CORP_ID | 954 | | 1 (0)| 00:00:01 | | 13 | TABLE ACCESS BY INDEX ROWID | LV_BUSI_ASSIGN | 1 | 12 | 1 (0)| 00:00:01 | |* 14 | INDEX UNIQUE SCAN | PK_LV_BUSI_ASSIGN | 1 | | 1 (0)| 00:00:01 | | 15 | TABLE ACCESS BY INDEX ROWID | LV_BUSI_RECORD | 1 | 11 | 1 (0)| 00:00:01 | |* 16 | INDEX UNIQUE SCAN | PK_LV_BUSI_RECORD | 1 | | 1 (0)| 00:00:01 | |* 17 | TABLE ACCESS BY INDEX ROWID | LV_BUSI_BILL | 1 | 25 | 1 (0)| 00:00:01 | |* 18 | INDEX UNIQUE SCAN | PK_LV_BUSI_BILL | 1 | | 1 (0)| 00:00:01 | |* 19 | TABLE ACCESS BY INDEX ROWID | BS_INSURED | 1 | 56 | 1 (0)| 00:00:01 | |* 20 | INDEX UNIQUE SCAN | PK_BS_INSURED | 1 | | 1 (0)| 00:00:01 | | 21 | NESTED LOOPS | | 1 | 56 | 5 (0)| 00:00:01 | | 22 | NESTED LOOPS | | 1 | 35 | 3 (0)| 00:00:01 | | 23 | NESTED LOOPS | | 1 | 23 | 2 (0)| 00:00:01 | | 24 | TABLE ACCESS BY INDEX ROWID| LV_BUSI_ASSIGN | 1 | 12 | 1 (0)| 00:00:01 | |* 25 | INDEX UNIQUE SCAN | PK_LV_BUSI_ASSIGN | 1 | | 1 (0)| 00:00:01 | | 26 | TABLE ACCESS BY INDEX ROWID| LV_BUSI_RECORD | 1679K| 17M| 1 (0)| 00:00:01 | |* 27 | INDEX UNIQUE SCAN | PK_LV_BUSI_RECORD | 1 | | 1 (0)| 00:00:01 | |* 28 | TABLE ACCESS BY INDEX ROWID | LV_BUSI_BILL | 83405 | 977K| 1 (0)| 00:00:01 | |* 29 | INDEX UNIQUE SCAN | PK_LV_BUSI_BILL | 1 | | 1 (0)| 00:00:01 | |* 30 | TABLE ACCESS BY INDEX ROWID | LV_URBAN_TOPAY_TMP | 1 | 21 | 2 (0)| 00:00:01 | |* 31 | INDEX RANGE SCAN | IDX_LV_URBAN_TOPAY_TMP_INDI | 12 | | 1 (0)| 00:00:01 | |* 32 | TABLE ACCESS BY INDEX ROWID | BS_FAMILY | 1 | 32 | 1 (0)| 00:00:01 | |* 33 | INDEX UNIQUE SCAN | PK_BS_FAMILY | 1 | | 1 (0)| 00:00:01 | | 34 | TABLE ACCESS BY INDEX ROWID | BS_PERSON_TYPE | 1 | 18 | 1 (0)| 00:00:01 | |* 35 | INDEX UNIQUE SCAN | PK_BS_PERSON_TYPE | 1 | | 1 (0)| 00:00:01 | |* 36 | INDEX UNIQUE SCAN | INDEX_BS_PRES_INSUR_UNIQUE | 1 | 12 | 1 (0)| 00:00:01 | | 37 | VIEW PUSHED PREDICATE | | 1 | 41 | 2 (0)| 00:00:01 | | 38 | NESTED LOOPS | | 1 | 57 | 2 (0)| 00:00:01 | |* 39 | TABLE ACCESS BY INDEX ROWID | BS_PERS_HOSP | 1 | 25 | 1 (0)| 00:00:01 | |* 40 | INDEX RANGE SCAN | PK_BS_PERS_HOSP | 2 | | 1 (0)| 00:00:01 | | 41 | TABLE ACCESS BY INDEX ROWID | BS_HOSPITAL | 1 | 32 | 1 (0)| 00:00:01 | |* 42 | INDEX UNIQUE SCAN | PK_BS_HOSPITAL | 1 | | 1 (0)| 00:00:01 | | 43 | TABLE ACCESS BY INDEX ROWID | BS_SEX | 1 | 6 | 1 (0)| 00:00:01 | |* 44 | INDEX UNIQUE SCAN | PK_BS_SEX | 1 | | 1 (0)| 00:00:01 | --------------------------------------------------------------------------------------------------------------------------
从上面的执行计划可以看到,当去掉Hint”/*+ index(lt, PK_LV_URBAN_TOPAY_TMP) */”后,CBO选择的执行计划果然是先访问IDX_LV_URBAN_TOPAY_TMP_CORP_ID
来访问表LV_URBAN_TOPAY_TMP,然后因为“lbr.busi_reco_no = lba.busi_reco_no AND lbr.busi_bill_sn = lbb.busi_bill_sn AND lt.center_id = lbb.center_id
AND lt.busi_asg_no = lba.busi_asg_no ”,表LV_BUSI_ASSIGN,LV_BUSI_RECORD,LV_BUSI_BILL与LV_URBAN_TOPAY_TMP连接的列都是各个表的主键,所以会分别与这三个执行嵌套循环连接。且存在where条件bi.indi_id = lt.indi_id ,而indi_id是表BS_INSURED表的主键所以也执行嵌套循环连接。而又因为与表BS_INSURED进行表连接的列也存在相关索引因此,依次类推整个执行计划都是执行嵌套循环连接。而且从下面的执行计划信息中可以看到,整个语句的逻辑读只有96762,而原来的逻辑读为3亿多次。这可是数据量级的减少。
----------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Order | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------- | 1 | 44 | SORT ORDER BY | | 1 | 1 | 2304 |00:00:00.39 | 96762 | 407K| 407K| 361K (0)| | 2 | 43 | NESTED LOOPS | | 1 | 1 | 2304 |00:00:00.39 | 96762 | | | | | 3 | 40 | NESTED LOOPS OUTER | | 1 | 1 | 2304 |00:00:00.37 | 94456 | | | | | 4 | 33 | NESTED LOOPS | | 1 | 1 | 2304 |00:00:00.30 | 80900 | | | | | 5 | 31 | NESTED LOOPS | | 1 | 1 | 2304 |00:00:00.29 | 76290 | | | | | 6 | 28 | NESTED LOOPS | | 1 | 1 | 2304 |00:00:00.27 | 73984 | | | | | 7 | 25 | NESTED LOOPS | | 1 | 1 | 2304 |00:00:00.25 | 67070 | | | | | 8 | 11 | NESTED LOOPS | | 1 | 1 | 2304 |00:00:00.09 | 21695 | | | | | 9 | 8 | NESTED LOOPS | | 1 | 1 | 2304 |00:00:00.07 | 14781 | | | | | 10 | 5 | NESTED LOOPS | | 1 | 1 | 2304 |00:00:00.05 | 7867 | | | | |* 11 | 2 | TABLE ACCESS BY INDEX ROWID | LV_URBAN_TOPAY_TMP | 1 | 1 | 2304 |00:00:00.02 | 953 | | | | |* 12 | 1 | INDEX RANGE SCAN | IDX_LV_URBAN_TOPAY_TMP_CORP_ID | 1 | 954 | 6986 |00:00:00.01 | 33 | | | | | 13 | 4 | TABLE ACCESS BY INDEX ROWID | LV_BUSI_ASSIGN | 2304 | 1 | 2304 |00:00:00.02 | 6914 | | | | |* 14 | 3 | INDEX UNIQUE SCAN | PK_LV_BUSI_ASSIGN | 2304 | 1 | 2304 |00:00:00.01 | 4610 | | | | | 15 | 7 | TABLE ACCESS BY INDEX ROWID | LV_BUSI_RECORD | 2304 | 1 | 2304 |00:00:00.02 | 6914 | | | | |* 16 | 6 | INDEX UNIQUE SCAN | PK_LV_BUSI_RECORD | 2304 | 1 | 2304 |00:00:00.01 | 4610 | | | | |* 17 | 10 | TABLE ACCESS BY INDEX ROWID | LV_BUSI_BILL | 2304 | 1 | 2304 |00:00:00.02 | 6914 | | | | |* 18 | 9 | INDEX UNIQUE SCAN | PK_LV_BUSI_BILL | 2304 | 1 | 2304 |00:00:00.01 | 4610 | | | | |* 19 | 24 | TABLE ACCESS BY INDEX ROWID | BS_INSURED | 2304 | 1 | 2304 |00:00:00.15 | 45375 | | | | |* 20 | 23 | INDEX UNIQUE SCAN | PK_BS_INSURED | 2304 | 1 | 2304 |00:00:00.13 | 43071 | | | | | 21 | 22 | NESTED LOOPS | | 2304 | 1 | 2304 |00:00:00.11 | 38461 | | | | | 22 | 19 | NESTED LOOPS | | 2304 | 1 | 2304 |00:00:00.06 | 27648 | | | | | 23 | 16 | NESTED LOOPS | | 2304 | 1 | 2304 |00:00:00.04 | 18432 | | | | | 24 | 13 | TABLE ACCESS BY INDEX ROWID| LV_BUSI_ASSIGN | 2304 | 1 | 2304 |00:00:00.02 | 9216 | | | | |* 25 | 12 | INDEX UNIQUE SCAN | PK_LV_BUSI_ASSIGN | 2304 | 1 | 2304 |00:00:00.01 | 6912 | | | | | 26 | 15 | TABLE ACCESS BY INDEX ROWID| LV_BUSI_RECORD | 2304 | 1679K| 2304 |00:00:00.02 | 9216 | | | | |* 27 | 14 | INDEX UNIQUE SCAN | PK_LV_BUSI_RECORD | 2304 | 1 | 2304 |00:00:00.01 | 6912 | | | | |* 28 | 18 | TABLE ACCESS BY INDEX ROWID | LV_BUSI_BILL | 2304 | 83405 | 2304 |00:00:00.02 | 9216 | | | | |* 29 | 17 | INDEX UNIQUE SCAN | PK_LV_BUSI_BILL | 2304 | 1 | 2304 |00:00:00.01 | 6912 | | | | |* 30 | 21 | TABLE ACCESS BY INDEX ROWID | LV_URBAN_TOPAY_TMP | 2304 | 1 | 2304 |00:00:00.04 | 10813 | | | | |* 31 | 20 | INDEX RANGE SCAN | IDX_LV_URBAN_TOPAY_TMP_INDI | 2304 | 12 | 7564 |00:00:00.02 | 6920 | | | | |* 32 | 27 | TABLE ACCESS BY INDEX ROWID | BS_FAMILY | 2304 | 1 | 2304 |00:00:00.02 | 6914 | | | | |* 33 | 26 | INDEX UNIQUE SCAN | PK_BS_FAMILY | 2304 | 1 | 2304 |00:00:00.01 | 4610 | | | | | 34 | 30 | TABLE ACCESS BY INDEX ROWID | BS_PERSON_TYPE | 2304 | 1 | 2304 |00:00:00.01 | 2306 | | | | |* 35 | 29 | INDEX UNIQUE SCAN | PK_BS_PERSON_TYPE | 2304 | 1 | 2304 |00:00:00.01 | 2 | | | | |* 36 | 32 | INDEX UNIQUE SCAN | INDEX_BS_PRES_INSUR_UNIQUE | 2304 | 1 | 2304 |00:00:00.01 | 4610 | | | | | 37 | 39 | VIEW PUSHED PREDICATE | | 2304 | 1 | 2297 |00:00:00.06 | 13556 | | | | | 38 | 38 | NESTED LOOPS | | 2304 | 1 | 2297 |00:00:00.05 | 13556 | | | | |* 39 | 35 | TABLE ACCESS BY INDEX ROWID | BS_PERS_HOSP | 2304 | 1 | 2297 |00:00:00.04 | 8960 | | | | |* 40 | 34 | INDEX RANGE SCAN | PK_BS_PERS_HOSP | 2304 | 2 | 4327 |00:00:00.02 | 4633 | | | | | 41 | 37 | TABLE ACCESS BY INDEX ROWID | BS_HOSPITAL | 2297 | 1 | 2297 |00:00:00.01 | 4596 | | | | |* 42 | 36 | INDEX UNIQUE SCAN | PK_BS_HOSPITAL | 2297 | 1 | 2297 |00:00:00.01 | 2299 | | | | | 43 | 42 | TABLE ACCESS BY INDEX ROWID | BS_SEX | 2304 | 1 | 2304 |00:00:00.01 | 2306 | | | | |* 44 | 41 | INDEX UNIQUE SCAN | PK_BS_SEX | 2304 | 1 | 2304 |00:00:00.01 | 2 | | | | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): ----------------------------------------------------------- 11 - filter(("LT"."FAC_PAY_DATE">=TO_DATE(' 2014-12-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "LT"."CENTER_ID"='430726' AND "LT"."POLICY_ITEM_CODE" LIKE '%INDI_TOPAY' AND "LT"."FAC_PAY_DATE" IS NOT NULL AND "LT"."BUSI_ASG_NO" IS NOT NULL AND NVL("LT"."BUSI_ASG_NO",0)<>0 AND NVL("LT"."BUSI_ASG_NO",0)<>(-980) AND NVL("LT"."BUSI_ASG_NO",0)<>(-981) AND NVL("LT"."BUSI_ASG_NO",0)<>(-997) AND NVL("LT"."BUSI_ASG_NO",0)<>(-998) AND NVL("LT"."BUSI_ASG_NO",0)<>(-999) AND "LT"."FAC_PAY_DATE"< =TO_DATE(' 2015-01-05 23:59:59', 'syyyy-mm-dd hh24:mi:ss'))) 12 - access("LT"."CORP_ID"=19159 AND "LT"."CURR_YEAR"='2015') 14 - access("LT"."BUSI_ASG_NO"="LBA"."BUSI_ASG_NO") 16 - access("LBR"."BUSI_RECO_NO"="LBA"."BUSI_RECO_NO") 17 - filter("LBB"."CENTER_ID"='430726') 18 - access("LBR"."BUSI_BILL_SN"="LBB"."BUSI_BILL_SN") 19 - filter(("BI"."FAMILY_ID" IS NOT NULL AND "BI"."INDI_STA"=1)) 20 - access("BI"."INDI_ID"="LT"."INDI_ID") filter( IS NOT NULL) 25 - access("LBA"."BUSI_ASG_NO"=:B1) 27 - access("LBR"."BUSI_RECO_NO"="LBA"."BUSI_RECO_NO") 28 - filter("LBB"."CENTER_ID"='430726') 29 - access("LBR"."BUSI_BILL_SN"="LBB"."BUSI_BILL_SN") 30 - filter(("LUTT"."BUSI_ASG_NO"=:B1 AND "LUTT"."CORP_ID"=19159 AND "LUTT"."CENTER_ID"='430726')) 31 - access("LUTT"."INDI_ID"=:B1) 32 - filter(("BF"."CORP_ID"=19159 AND "BF"."CENTER_ID"='430726' AND "BF"."FAMILY_STA"=1)) 33 - access("BF"."FAMILY_ID"="BI"."FAMILY_ID") 35 - access("PT"."PERS_TYPE"="BI"."PERS_TYPE" AND "PT"."CENTER_ID"='430726') 36 - access("BPI"."INDI_ID"="BI"."INDI_ID" AND "BPI"."INSR_DETAIL_CODE"=21 AND "BPI"."INDI_JOIN_STA"=1) 39 - filter("BPH"."END_YEAR"='2015') 40 - access("BPH"."INDI_ID"="BI"."INDI_ID" AND "BPH"."FIRST_FLAG"=1) filter("BPH"."FIRST_FLAG"=1) 42 - access("BPH"."HOSPITAL_ID"="BH"."HOSPITAL_ID") 44 - access("BS"."SEX"=TO_NUMBER("BI"."SEX"))
现在由于没有办法修改代码,所以选择使用SQL Profile来固定该SQL的执行计划。生成SQL Profile有两种方法,这里使用手工生成SQL Profile。使用coe_xfr_sql_profile.sql脚本先对原SQL语句生成SQL Profile文件。
SQL> @/oracle/sqlt/utl/coe_xfr_sql_profile.sql Parameter 1: SQL_ID (required) Enter value for 1: 36cbabzyq13gy PLAN_HASH_VALUE AVG_ET_SECS --------------- ----------- 1849931106 6280.149 Parameter 2: PLAN_HASH_VALUE (required) Enter value for 2: 1849931106 Values passed to coe_xfr_sql_profile: ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ SQL_ID : "36cbabzyq13gy" PLAN_HASH_VALUE: "1849931106" SQL>BEGIN 2 IF :sql_text IS NULL THEN 3 RAISE_APPLICATION_ERROR(-20100, 'SQL_TEXT for SQL_ID &&sql_id. was not found in memory (gv$sqltext_with_newlines) or AWR (dba_hist_sqltext).'); 4 END IF; 5 END; 6 / SQL>SET TERM OFF; SQL>BEGIN 2 IF :other_xml IS NULL THEN 3 RAISE_APPLICATION_ERROR(-20101, 'PLAN for SQL_ID &&sql_id. and PHV &&plan_hash_value. was not found in memory (gv$sql_plan) or AWR (dba_hist_sql_plan).'); 4 END IF; 5 END; 6 / SQL>SET TERM OFF; Execute coe_xfr_sql_profile_36cbabzyq13gy_1849931106.sql on TARGET system in order to create a custom SQL Profile with plan 1849931106 linked to adjusted sql_text.
对去掉Hint后的SQL生成SQL Profile
SQL>@E:\scripts\ch\coe_xfr_sql_profile.sql Parameter 1: SQL_ID (required) 输入 1 的值: 46fc6316z15mh PLAN_HASH_VALUE AVG_ET_SECS --------------- ----------- 3748535674 1.24 Parameter 2: PLAN_HASH_VALUE (required) 输入 2 的值: 3748535674 Values passed to coe_xfr_sql_profile: ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ SQL_ID : "46fc6316z15mh" PLAN_HASH_VALUE: "3748535674" SQL>BEGIN 2 IF :sql_text IS NULL THEN 3 RAISE_APPLICATION_ERROR(-20100, 'SQL_TEXT for SQL_ID &&sql_id. was not found in memory (gv$sqltext_with_newlines) or AWR (dba_hist_sqltext).'); 4 END IF; 5 END; 6 / SQL>SET TERM OFF; SQL>BEGIN 2 IF :other_xml IS NULL THEN 3 RAISE_APPLICATION_ERROR(-20101, 'PLAN for SQL_ID &&sql_id. and PHV &&pl an_hash_value. was not found in memory (gv$sql_plan) or AWR (dba_hist_sql_plan). '); 4 END IF; 5 END; 6 / SQL>SET TERM OFF; Execute coe_xfr_sql_profile_46fc6316z15mh_3748535674.sql on TARGET system in order to create a custom SQL Profile with plan 3748535674 linked to adjusted sql_text.
原SQL的SQL Profile文件coe_xfr_sql_profile_36cbabzyq13gy_1849931106.sql中有如下记录:
h := SYS.SQLPROF_ATTR( q'[BEGIN_OUTLINE_DATA]', q'[IGNORE_OPTIM_EMBEDDED_HINTS]', q'[OPTIMIZER_FEATURES_ENABLE('10.2.0.4')]', q'[OPT_PARAM('optimizer_index_cost_adj' 20)]', q'[OPT_PARAM('optimizer_index_caching' 90)]', q'[ALL_ROWS]', q'[OUTLINE_LEAF(@"SEL$3")]', q'[OUTLINE_LEAF(@"SEL$639F1A6F")]', q'[PUSH_PRED(@"SEL$1" "TAB_HOSP"@"SEL$1" 12)]', q'[OUTLINE_LEAF(@"SEL$1")]', q'[OUTLINE(@"SEL$3")]', q'[OUTLINE(@"SEL$2")]', q'[OUTLINE(@"SEL$1")]', q'[INDEX_RS_ASC(@"SEL$1" "LT"@"SEL$1" ("LV_URBAN_TOPAY_TMP"."CORP_ID" "LV_URBAN_TOPAY_TMP"."CURR_YEAR" "LV_URBAN_TOPAY_TMP"."PERS_TYPE"))]', q'[INDEX_RS_ASC(@"SEL$1" "LBA"@"SEL$1" ("LV_BUSI_ASSIGN"."BUSI_ASG_NO"))]', q'[INDEX_RS_ASC(@"SEL$1" "LBR"@"SEL$1" ("LV_BUSI_RECORD"."BUSI_RECO_NO"))]', q'[INDEX_RS_ASC(@"SEL$1" "LBB"@"SEL$1" ("LV_BUSI_BILL"."BUSI_BILL_SN"))]', q'[INDEX_RS_ASC(@"SEL$1" "BI"@"SEL$1" ("BS_INSURED"."INDI_ID"))]', q'[INDEX_RS_ASC(@"SEL$1" "BF"@"SEL$1" ("BS_FAMILY"."FAMILY_ID"))]', q'[INDEX_RS_ASC(@"SEL$1" "PT"@"SEL$1" ("BS_PERSON_TYPE"."PERS_TYPE" "BS_PERSON_TYPE"."CENTER_ID"))]', q'[INDEX(@"SEL$1" "BPI"@"SEL$1" ("BS_PRES_INSUR"."INDI_ID" "BS_PRES_INSUR"."INSR_DETAIL_CODE" "BS_PRES_INSUR"."INDI_JOIN_STA"))]', q'[NO_ACCESS(@"SEL$1" "TAB_HOSP"@"SEL$1")]', q'[INDEX_RS_ASC(@"SEL$1" "BS"@"SEL$1" ("BS_SEX"."SEX"))]', q'[LEADING(@"SEL$1" "LT"@"SEL$1" "LBA"@"SEL$1" "LBR"@"SEL$1" "LBB"@"SEL$1" "BI"@"SEL$1" "BF"@"SEL$1" "PT"@"SEL$1" "BPI"@"SEL$1" "TAB_HOSP"@"SEL$1" "BS"@"SEL$1")]', q'[USE_NL(@"SEL$1" "LBA"@"SEL$1")]', q'[USE_NL(@"SEL$1" "LBR"@"SEL$1")]', q'[USE_NL(@"SEL$1" "LBB"@"SEL$1")]', q'[USE_NL(@"SEL$1" "BI"@"SEL$1")]', q'[USE_NL(@"SEL$1" "BF"@"SEL$1")]', q'[USE_NL(@"SEL$1" "PT"@"SEL$1")]', q'[USE_NL(@"SEL$1" "BPI"@"SEL$1")]', q'[USE_NL(@"SEL$1" "TAB_HOSP"@"SEL$1")]', q'[USE_NL(@"SEL$1" "BS"@"SEL$1")]', q'[INDEX_RS_ASC(@"SEL$639F1A6F" "BPH"@"SEL$2" ("BS_PERS_HOSP"."INDI_ID" "BS_PERS_HOSP"."HOSPITAL_ID" "BS_PERS_HOSP"."FIRST_FLAG" "BS_PERS_HOSP"."BEG_YEAR" "BS_PERS_HOSP"."BIZ_TYPE"))]', q'[INDEX_RS_ASC(@"SEL$639F1A6F" "BH"@"SEL$2" ("BS_HOSPITAL"."HOSPITAL_ID"))]', q'[LEADING(@"SEL$639F1A6F" "BPH"@"SEL$2" "BH"@"SEL$2")]', q'[USE_NL(@"SEL$639F1A6F" "BH"@"SEL$2")]', q'[INDEX_RS_ASC(@"SEL$3" "LBA"@"SEL$3" ("LV_BUSI_ASSIGN"."BUSI_ASG_NO"))]', q'[INDEX_RS_ASC(@"SEL$3" "LBR"@"SEL$3" ("LV_BUSI_RECORD"."BUSI_RECO_NO"))]', q'[INDEX_RS_ASC(@"SEL$3" "LBB"@"SEL$3" ("LV_BUSI_BILL"."BUSI_BILL_SN"))]', q'[INDEX_RS_ASC(@"SEL$3" "LUTT"@"SEL$3" ("LV_URBAN_TOPAY_TMP"."INDI_ID"))]', q'[LEADING(@"SEL$3" "LBA"@"SEL$3" "LBR"@"SEL$3" "LBB"@"SEL$3" "LUTT"@"SEL$3")]', q'[USE_NL(@"SEL$3" "LBR"@"SEL$3")]', q'[USE_NL(@"SEL$3" "LBB"@"SEL$3")]', q'[USE_NL(@"SEL$3" "LUTT"@"SEL$3")]', q'[END_OUTLINE_DATA]');
用去掉Hint后SQL语句所对应的SQL Profile文件coe_xfr_sql_profile_46fc6316z15mh_3748535674.sql中的如下记录替换coe_xfr_sql_profile_36cbabzyq13gy_1849931106.sql文件中上述内容:
h := SYS.SQLPROF_ATTR( q'[BEGIN_OUTLINE_DATA]', q'[IGNORE_OPTIM_EMBEDDED_HINTS]', q'[OPTIMIZER_FEATURES_ENABLE('10.2.0.4')]', q'[OPT_PARAM('optimizer_index_cost_adj' 20)]', q'[OPT_PARAM('optimizer_index_caching' 90)]', q'[ALL_ROWS]', q'[OUTLINE_LEAF(@"SEL$3")]', q'[OUTLINE_LEAF(@"SEL$639F1A6F")]', q'[PUSH_PRED(@"SEL$1" "TAB_HOSP"@"SEL$1" 12)]', q'[OUTLINE_LEAF(@"SEL$1")]', q'[OUTLINE(@"SEL$3")]', q'[OUTLINE(@"SEL$2")]', q'[OUTLINE(@"SEL$1")]', q'[INDEX_RS_ASC(@"SEL$1" "LT"@"SEL$1" ("LV_URBAN_TOPAY_TMP"."CORP_ID" "LV_URBAN_TOPAY_TMP"."CURR_YEAR" "LV_URBAN_TOPAY_TMP"."PERS_TYPE"))]', q'[INDEX_RS_ASC(@"SEL$1" "LBA"@"SEL$1" ("LV_BUSI_ASSIGN"."BUSI_ASG_NO"))]', q'[INDEX_RS_ASC(@"SEL$1" "LBR"@"SEL$1" ("LV_BUSI_RECORD"."BUSI_RECO_NO"))]', q'[INDEX_RS_ASC(@"SEL$1" "LBB"@"SEL$1" ("LV_BUSI_BILL"."BUSI_BILL_SN"))]', q'[INDEX_RS_ASC(@"SEL$1" "BI"@"SEL$1" ("BS_INSURED"."INDI_ID"))]', q'[INDEX_RS_ASC(@"SEL$1" "BF"@"SEL$1" ("BS_FAMILY"."FAMILY_ID"))]', q'[INDEX_RS_ASC(@"SEL$1" "PT"@"SEL$1" ("BS_PERSON_TYPE"."PERS_TYPE" "BS_PERSON_TYPE"."CENTER_ID"))]', q'[INDEX(@"SEL$1" "BPI"@"SEL$1" ("BS_PRES_INSUR"."INDI_ID" "BS_PRES_INSUR"."INSR_DETAIL_CODE" "BS_PRES_INSUR"."INDI_JOIN_STA"))]', q'[NO_ACCESS(@"SEL$1" "TAB_HOSP"@"SEL$1")]', q'[INDEX_RS_ASC(@"SEL$1" "BS"@"SEL$1" ("BS_SEX"."SEX"))]', q'[LEADING(@"SEL$1" "LT"@"SEL$1" "LBA"@"SEL$1" "LBR"@"SEL$1" "LBB"@"SEL$1" "BI"@"SEL$1" "BF"@"SEL$1" "PT"@"SEL$1" "BPI"@"SEL$1" "TAB_HOSP"@"SEL$1" "BS"@"SEL$1")]', q'[USE_NL(@"SEL$1" "LBA"@"SEL$1")]', q'[USE_NL(@"SEL$1" "LBR"@"SEL$1")]', q'[USE_NL(@"SEL$1" "LBB"@"SEL$1")]', q'[USE_NL(@"SEL$1" "BI"@"SEL$1")]', q'[USE_NL(@"SEL$1" "BF"@"SEL$1")]', q'[USE_NL(@"SEL$1" "PT"@"SEL$1")]', q'[USE_NL(@"SEL$1" "BPI"@"SEL$1")]', q'[USE_NL(@"SEL$1" "TAB_HOSP"@"SEL$1")]', q'[USE_NL(@"SEL$1" "BS"@"SEL$1")]', q'[INDEX_RS_ASC(@"SEL$639F1A6F" "BPH"@"SEL$2" ("BS_PERS_HOSP"."INDI_ID" "BS_PERS_HOSP"."HOSPITAL_ID" "BS_PERS_HOSP"."FIRST_FLAG" "BS_PERS_HOSP"."BEG_YEAR" "BS_PERS_HOSP"."BIZ_TYPE"))]', q'[INDEX_RS_ASC(@"SEL$639F1A6F" "BH"@"SEL$2" ("BS_HOSPITAL"."HOSPITAL_ID"))]', q'[LEADING(@"SEL$639F1A6F" "BPH"@"SEL$2" "BH"@"SEL$2")]', q'[USE_NL(@"SEL$639F1A6F" "BH"@"SEL$2")]', q'[INDEX_RS_ASC(@"SEL$3" "LBA"@"SEL$3" ("LV_BUSI_ASSIGN"."BUSI_ASG_NO"))]', q'[INDEX_RS_ASC(@"SEL$3" "LBR"@"SEL$3" ("LV_BUSI_RECORD"."BUSI_RECO_NO"))]', q'[INDEX_RS_ASC(@"SEL$3" "LBB"@"SEL$3" ("LV_BUSI_BILL"."BUSI_BILL_SN"))]', q'[INDEX_RS_ASC(@"SEL$3" "LUTT"@"SEL$3" ("LV_URBAN_TOPAY_TMP"."INDI_ID"))]', q'[LEADING(@"SEL$3" "LBA"@"SEL$3" "LBR"@"SEL$3" "LBB"@"SEL$3" "LUTT"@"SEL$3")]', q'[USE_NL(@"SEL$3" "LBR"@"SEL$3")]', q'[USE_NL(@"SEL$3" "LBB"@"SEL$3")]', q'[USE_NL(@"SEL$3" "LUTT"@"SEL$3")]', q'[END_OUTLINE_DATA]');
替换后保存coe_xfr_sql_profile_36cbabzyq13gy_1849931106.sql文件,并执行该文件:
SQL>@C:\Users\Administrator\coe_xfr_sql_profile_36cbabzyq13gy_1849931106.sql SQL>REM SQL>REM $Header: 215187.1 coe_xfr_sql_profile_36cbabzyq13gy_1849931106.sql 11.4. 3.5 2015/01/05 carlos.sierra $ SQL>REM SQL>REM Copyright (c) 2000-2011, Oracle Corporation. All rights reserved. SQL>REM SQL>REM AUTHOR SQL>REM carlos.sierra@oracle.com SQL>REM SQL>REM SCRIPT SQL>REM coe_xfr_sql_profile_36cbabzyq13gy_1849931106.sql SQL>REM SQL>REM DESCRIPTION SQL>REM This script is generated by coe_xfr_sql_profile.sql SQL>REM It contains the SQL*Plus commands to create a custom SQL>REM SQL Profile for SQL_ID 36cbabzyq13gy based on plan hash SQL>REM value 1849931106. SQL>REM The custom SQL Profile to be created by this script SQL>REM will affect plans for SQL commands with signature SQL>REM matching the one for SQL Text below. SQL>REM Review SQL Text and adjust accordingly. SQL>REM SQL>REM PARAMETERS SQL>REM None. SQL>REM SQL>REM EXAMPLE SQL>REM SQL> START coe_xfr_sql_profile_36cbabzyq13gy_1849931106.sql; SQL>REM SQL>REM NOTES SQL>REM 1. Should be run as SYSTEM or SYSDBA. SQL>REM 2. User must have CREATE ANY SQL PROFILE privilege. SQL>REM 3. SOURCE and TARGET systems can be the same or similar. SQL>REM 4. To drop this custom SQL Profile after it has been created: SQL>REM EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE('coe_36cbabzyq13gy_1849931106'); SQL>REM 5. Be aware that using DBMS_SQLTUNE requires a license SQL>REM for the Oracle Tuning Pack. SQL>REM SQL>WHENEVER SQLERROR EXIT SQL.SQLCODE; SQL>REM SQL>VAR signature NUMBER; SQL>REM SQL>DECLARE 2 sql_txt CLOB; 3 h SYS.SQLPROF_ATTR; 4 BEGIN 5 sql_txt := q'[ 6 SELECT /*+ index(lt, 7 PK_LV_URBAN_TOPAY_TMP) 8 */ bi.indi_id, 9 bi.name, 10 pt.pers_name, 11 bs.sex_name, 12 lt.pay_money, 13 bi.idcard, 14 bi.birthday, 15 bf.headed_name, 16 lt.fac_pay_date, 17 lbb.audit_man, 18 tab_hosp.hospital_name as hospital_name, 19 to_char(lbb.make_bill_tm, 20 'yyyy-mm-dd') 21 as make_bill_tm, 22 bf.telephone, 23 nvl((decode(lt.intensive_disability_flag, 24 1, 25 decode(lt.lowflag, 26 1, 27 '重症伤残, 28 ', 29 '重症伤残') 30 , 31 '') 32 || decode(lt.lowflag, 33 1, 34 decode(lt.nothing_flag, 35 1, 36 '低保, 37 ', 38 '低保') 39 , 40 '') 41 || decode(lt.nothing_flag, 42 1, 43 '三无', 44 '') 45 ) 46 , 47 '标准') 48 as subsidykide FROM lv_urban_topay_tmp lt, 49 bs_insured bi, 50 bs_sex bs, 51 bs_person_type pt, 52 bs_pres_insur bpi, 53 bs_family bf, 54 lv_busi_bill lbb, 55 lv_busi_record lbr, 56 lv_busi_assign lba, 57 (select bh.hospital_name, 58 bph.indi_id from bs_pers_hosp bph, 59 bs_hospital bh where bph.hospital_id = bh.hospital_id and bph .first_flag = 1 and bph.end_year = '2015') 60 tab_hosp WHERE nvl(lt.busi_asg_no, 61 0) 62 <> 0 AND nvl(lt.busi_asg_no, 63 0) 64 not in (-999, 65 -998, 66 -997, 67 -981, 68 -980) 69 AND lt.fac_pay_date is not null AND bi.indi_id = lt.indi_id AND bs.sex = bi.sex AND bi.indi_id = tab_hosp.indi_id(+) 70 AND lbr.busi_reco_no = lba.busi_reco_no AND lbr.busi_bill_s n = lbb.busi_bill_sn AND lt.center_id = lbb.center_id AND lt.busi_asg_no = lba.busi_asg_no AND lt.indi_id = bi.ind i_id AND pt.pers_type = bi.pers_type AND bpi.indi_id = b i.indi_id AND lt.center_id = pt.center_id AND bf.family_ id = bi.family_id AND bf.family_sta = 1 AND bi.indi_sta = 1 AND bpi.indi_join_sta = 1 AND bf.center_id = lt.center _id AND bf.corp_id = lt.corp_id AND lt.policy_item_cod e like '%INDI_TOPAY' 71 AND lt.corp_id='19159' AND bpi.insr_detail_code=21 AND lt.center_id= '430726' AND lt.curr_year= '2015' AND lt.fac_pay_date >= to_date('2014-12-01 00:00:00', 72 'yyyy-MM-dd hh24:mi:ss') 73 AND lt.fac_pay_date < = to_date('2015-01-05 23:59:59', 74 'yyyy-MM-dd hh24:mi:ss') 75 and exists (select 'X' FROM lv_busi_bill lbb, 76 lv_busi_record lbr, 77 lv_busi_assign lba, 78 lv_urban_topay_tmp lutt WHERE lbr.busi_reco_no = lba.busi_reco_no AND lbr.busi_bill_sn = lbb.busi_bill_sn AND lbb.cen ter_id= '430726' AND lutt.corp_id='19159' AND lutt.cent er_id = lbb.center_id AND lutt.busi_asg_no = lba.busi_asg_no and lba.busi_asg_no = lt.busi_asg_no and lutt.indi_id = bi.indi_id) 79 order by lt.fac_pay_date, 80 bi.indi_id, 81 bi.name 82 ]'; 83 h := SYS.SQLPROF_ATTR( 84 q'[BEGIN_OUTLINE_DATA]', 85 q'[IGNORE_OPTIM_EMBEDDED_HINTS]', 86 q'[OPTIMIZER_FEATURES_ENABLE('10.2.0.4')]', 87 q'[OPT_PARAM('optimizer_index_cost_adj' 20)]', 88 q'[OPT_PARAM('optimizer_index_caching' 90)]', 89 q'[ALL_ROWS]', 90 q'[OUTLINE_LEAF(@"SEL$3")]', 91 q'[OUTLINE_LEAF(@"SEL$639F1A6F")]', 92 q'[PUSH_PRED(@"SEL$1" "TAB_HOSP"@"SEL$1" 12)]', 93 q'[OUTLINE_LEAF(@"SEL$1")]', 94 q'[OUTLINE(@"SEL$3")]', 95 q'[OUTLINE(@"SEL$2")]', 96 q'[OUTLINE(@"SEL$1")]', 97 q'[INDEX_RS_ASC(@"SEL$1" "LT"@"SEL$1" ("LV_URBAN_TOPAY_TMP"."CORP_ID" "LV_U RBAN_TOPAY_TMP"."CURR_YEAR" "LV_URBAN_TOPAY_TMP"."PERS_TYPE"))]', 98 q'[INDEX_RS_ASC(@"SEL$1" "LBA"@"SEL$1" ("LV_BUSI_ASSIGN"."BUSI_ASG_NO"))]', 99 q'[INDEX_RS_ASC(@"SEL$1" "LBR"@"SEL$1" ("LV_BUSI_RECORD"."BUSI_RECO_NO"))]' , 100 q'[INDEX_RS_ASC(@"SEL$1" "LBB"@"SEL$1" ("LV_BUSI_BILL"."BUSI_BILL_SN"))]', 101 q'[INDEX_RS_ASC(@"SEL$1" "BI"@"SEL$1" ("BS_INSURED"."INDI_ID"))]', 102 q'[INDEX_RS_ASC(@"SEL$1" "BF"@"SEL$1" ("BS_FAMILY"."FAMILY_ID"))]', 103 q'[INDEX_RS_ASC(@"SEL$1" "PT"@"SEL$1" ("BS_PERSON_TYPE"."PERS_TYPE" "BS_PER SON_TYPE"."CENTER_ID"))]', 104 q'[INDEX(@"SEL$1" "BPI"@"SEL$1" ("BS_PRES_INSUR"."INDI_ID" "BS_PRES_INSUR". "INSR_DETAIL_CODE" "BS_PRES_INSUR"."INDI_JOIN_STA"))]', 105 q'[NO_ACCESS(@"SEL$1" "TAB_HOSP"@"SEL$1")]', 106 q'[INDEX_RS_ASC(@"SEL$1" "BS"@"SEL$1" ("BS_SEX"."SEX"))]', 107 q'[LEADING(@"SEL$1" "LT"@"SEL$1" "LBA"@"SEL$1" "LBR"@"SEL$1" "LBB"@"SEL$1" "BI"@"SEL$1" "BF"@"SEL$1" "PT"@"SEL$1" "BPI"@"SEL$1" "TAB_HOSP"@"SEL$1" "BS"@"SE L$1")]', 108 q'[USE_NL(@"SEL$1" "LBA"@"SEL$1")]', 109 q'[USE_NL(@"SEL$1" "LBR"@"SEL$1")]', 110 q'[USE_NL(@"SEL$1" "LBB"@"SEL$1")]', 111 q'[USE_NL(@"SEL$1" "BI"@"SEL$1")]', 112 q'[USE_NL(@"SEL$1" "BF"@"SEL$1")]', 113 q'[USE_NL(@"SEL$1" "PT"@"SEL$1")]', 114 q'[USE_NL(@"SEL$1" "BPI"@"SEL$1")]', 115 q'[USE_NL(@"SEL$1" "TAB_HOSP"@"SEL$1")]', 116 q'[USE_NL(@"SEL$1" "BS"@"SEL$1")]', 117 q'[INDEX_RS_ASC(@"SEL$639F1A6F" "BPH"@"SEL$2" ("BS_PERS_HOSP"."INDI_ID" "BS _PERS_HOSP"."HOSPITAL_ID" "BS_PERS_HOSP"."FIRST_FLAG" "BS_PERS_HOSP"."BEG_YEAR" "BS_PERS_HOSP"."BIZ_TYPE"))]', 118 q'[INDEX_RS_ASC(@"SEL$639F1A6F" "BH"@"SEL$2" ("BS_HOSPITAL"."HOSPITAL_ID")) ]', 119 q'[LEADING(@"SEL$639F1A6F" "BPH"@"SEL$2" "BH"@"SEL$2")]', 120 q'[USE_NL(@"SEL$639F1A6F" "BH"@"SEL$2")]', 121 q'[INDEX_RS_ASC(@"SEL$3" "LBA"@"SEL$3" ("LV_BUSI_ASSIGN"."BUSI_ASG_NO"))]', 122 q'[INDEX_RS_ASC(@"SEL$3" "LBR"@"SEL$3" ("LV_BUSI_RECORD"."BUSI_RECO_NO"))]' , 123 q'[INDEX_RS_ASC(@"SEL$3" "LBB"@"SEL$3" ("LV_BUSI_BILL"."BUSI_BILL_SN"))]', 124 q'[INDEX_RS_ASC(@"SEL$3" "LUTT"@"SEL$3" ("LV_URBAN_TOPAY_TMP"."INDI_ID"))]' , 125 q'[LEADING(@"SEL$3" "LBA"@"SEL$3" "LBR"@"SEL$3" "LBB"@"SEL$3" "LUTT"@"SEL$3 ")]', 126 q'[USE_NL(@"SEL$3" "LBR"@"SEL$3")]', 127 q'[USE_NL(@"SEL$3" "LBB"@"SEL$3")]', 128 q'[USE_NL(@"SEL$3" "LUTT"@"SEL$3")]', 129 q'[END_OUTLINE_DATA]'); 130 :signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt); 131 DBMS_SQLTUNE.IMPORT_SQL_PROFILE ( 132 sql_text => sql_txt, 133 profile => h, 134 name => 'coe_36cbabzyq13gy_1849931106', 135 description => 'coe 36cbabzyq13gy 1849931106 '||:signature||'', 136 category => 'DEFAULT', 137 validate => TRUE, 138 replace => TRUE, 139 force_match => TRUE /* TRUE:FORCE (match even when different literals in SQ L). FALSE:EXACT (similar to CURSOR_SHARING) */ ); 140 END; 141 / PL/SQL 过程已成功完成。 SQL>WHENEVER SQLERROR CONTINUE SQL>SET ECHO OFF; SIGNATURE --------------------- 718970022444771957 ... manual custom SQL Profile has been created COE_XFR_SQL_PROFILE_36cbabzyq13gy_1849931106 completed
现在我们再来执行原SQL语句来检测SQL Profile是否生效:
SQL>SELECT /*+ index(lt,PK_LV_URBAN_TOPAY_TMP) */ bi.indi_id, bi.name, pt.pers_name, bs.sex_name, lt.pay_money, bi.idcard, bi.birthday, bf.headed_name, lt.fac_pay_date, lbb.audit_man, tab_hosp.hospital_name as hospital_name, to_char(lbb.make_bill_tm, 'yyyy-mm-dd') as make_bill_tm, bf.telephone, nvl((decode(lt.intensive_disability_flag, 1, decode(lt.lowflag, 1, '重症伤残,', '重症伤残'), '') || decode(lt.lowflag, 1, decode(lt.nothing_flag, 1, '低保,', '低保'), '') || decode(lt.nothing_flag, 1, '三无', '')), '标准') as subsidykide FROM lv_urban_topay_tmp lt, bs_insured bi, bs_sex bs, bs_person_type pt, bs_pres_insur bpi, bs_family bf, lv_busi_bill lbb, lv_busi_record lbr, lv_busi_assign lba, (select bh.hospital_name, bph.indi_id from bs_pers_hosp bph, bs_hospital bh where bph.hospital_id = bh.hospital_id and bph.first_flag = 1 and bph.end_year = '2015') tab_hosp WHERE nvl(lt.busi_asg_no, 0) <> 0 AND nvl(lt.busi_asg_no, 0) not in (-999, -998, -997, -981, -980) AND lt.fac_pay_date is not null AND bi.indi_id = lt.indi_id AND bs.sex = bi.sex AND bi.indi_id = tab_hosp.indi_id(+) AND lbr.busi_reco_no = lba.busi_reco_no AND lbr.busi_bill_sn = lbb.busi_bill_sn AND lt.center_id = lbb.center_id AND lt.busi_asg_no = lba.busi_asg_no AND lt.indi_id = bi.indi_id AND pt.pers_type = bi.pers_type AND bpi.indi_id = bi.indi_id AND lt.center_id = pt.center_id AND bf.family_id = bi.family_id AND bf.family_sta = 1 AND bi.indi_sta = 1 AND bpi.indi_join_sta = 1 AND bf.center_id = lt.center_id AND bf.corp_id = lt.corp_id AND lt.policy_item_code like '%INDI_TOPAY' AND lt.corp_id = '19159' AND bpi.insr_detail_code = 21 AND lt.center_id = '430726' AND lt.curr_year = '2015' AND lt.fac_pay_date >= to_date('2014-12-01 00:00:00', 'yyyy-MM-dd hh24:mi:ss') AND lt.fac_pay_date < = to_date('2015-01-05 23:59:59', 'yyyy-MM-dd hh24:mi:ss') and exists (select 'X' FROM lv_busi_bill lbb, lv_busi_record lbr, lv_busi_assign lba, lv_urban_topay_tmp lutt WHERE lbr.busi_reco_no = lba.busi_reco_no AND lbr.busi_bill_sn = lbb.busi_bill_sn AND lbb.center_id = '430726' AND lutt.corp_id = '19159' AND lutt.center_id = lbb.center_id AND lutt.busi_asg_no = lba.busi_asg_no and lba.busi_asg_no = lt.busi_asg_no and lutt.indi_id = bi.indi_id) order by lt.fac_pay_date, bi.indi_id, bi.name ...省略输入结果 2304 rows selected. Elapsed: 00:00:08.08
SQL> select * from table(dbms_xplan.display_cursor('3z8rmv9d64xyx',0,'advanced')); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ SQL_ID 3z8rmv9d64xyx, child number 0 ------------------------------------- SELECT /*+ index(lt,PK_LV_URBAN_TOPAY_TMP) */ bi.indi_id, bi.name, pt.pers_name, bs.sex_name, lt.pay_money, bi.idcard, bi.birthday, bf.headed_name, lt.fac_pay_date, lbb.audit_man, tab_hosp.hospital_name as hospital_name, to_char(lbb.make_bill_tm, 'yyyy-mm-dd') as make_bill_tm, bf.telephone, nvl((decode(lt.intensive_disability_flag, 1, decode(lt.lowflag, 1, '重症伤残,', '重症伤残'), '') || decode(lt.lowflag, 1, decode(lt.nothing_flag, 1, '低保,', '低保'), '') || decode(lt.nothing_flag, 1, '三无', '')), '标准') as subsidykide FROM lv_urban_topay_tmp lt, bs_insured bi, bs_sex bs, bs_person_type pt, bs_pres_insur bpi, bs_family bf, lv_busi_bill lbb, lv_busi_record lbr, lv_busi_assign lba, (select bh.hospital_name, bph.indi_id from bs_pers_hosp bph, bs_hospital bh where bph.hospital_id = bh.hospital_id and bph.first_flag = 1 Plan hash value: 3748535674 -------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 88 (100)| | | 1 | SORT ORDER BY | | 1 | 278 | 88 (2)| 00:00:02 | | 2 | NESTED LOOPS | | 1 | 278 | 82 (0)| 00:00:01 | | 3 | NESTED LOOPS OUTER | | 1 | 272 | 81 (0)| 00:00:01 | | 4 | NESTED LOOPS | | 1 | 231 | 79 (0)| 00:00:01 | | 5 | NESTED LOOPS | | 1 | 219 | 78 (0)| 00:00:01 | | 6 | NESTED LOOPS | | 1 | 201 | 77 (0)| 00:00:01 | | 7 | NESTED LOOPS | | 1 | 169 | 76 (0)| 00:00:01 | | 8 | NESTED LOOPS | | 1 | 113 | 75 (0)| 00:00:01 | | 9 | NESTED LOOPS | | 1 | 88 | 74 (0)| 00:00:01 | | 10 | NESTED LOOPS | | 1 | 77 | 73 (0)| 00:00:01 | |* 11 | TABLE ACCESS BY INDEX ROWID | LV_URBAN_TOPAY_TMP | 1 | 65 | 72 (0)| 00:00:01 | |* 12 | INDEX RANGE SCAN | IDX_LV_URBAN_TOPAY_TMP_CORP_ID | 954 | | 1 (0)| 00:00:01 | | 13 | TABLE ACCESS BY INDEX ROWID | LV_BUSI_ASSIGN | 1 | 12 | 1 (0)| 00:00:01 | |* 14 | INDEX UNIQUE SCAN | PK_LV_BUSI_ASSIGN | 1 | | 1 (0)| 00:00:01 | | 15 | TABLE ACCESS BY INDEX ROWID | LV_BUSI_RECORD | 1 | 11 | 1 (0)| 00:00:01 | |* 16 | INDEX UNIQUE SCAN | PK_LV_BUSI_RECORD | 1 | | 1 (0)| 00:00:01 | |* 17 | TABLE ACCESS BY INDEX ROWID | LV_BUSI_BILL | 1 | 25 | 1 (0)| 00:00:01 | |* 18 | INDEX UNIQUE SCAN | PK_LV_BUSI_BILL | 1 | | 1 (0)| 00:00:01 | |* 19 | TABLE ACCESS BY INDEX ROWID | BS_INSURED | 1 | 56 | 1 (0)| 00:00:01 | |* 20 | INDEX UNIQUE SCAN | PK_BS_INSURED | 1 | | 1 (0)| 00:00:01 | | 21 | NESTED LOOPS | | 1 | 56 | 5 (0)| 00:00:01 | | 22 | NESTED LOOPS | | 1 | 35 | 3 (0)| 00:00:01 | | 23 | NESTED LOOPS | | 1 | 23 | 2 (0)| 00:00:01 | | 24 | TABLE ACCESS BY INDEX ROWID| LV_BUSI_ASSIGN | 1 | 12 | 1 (0)| 00:00:01 | |* 25 | INDEX UNIQUE SCAN | PK_LV_BUSI_ASSIGN | 1 | | 1 (0)| 00:00:01 | | 26 | TABLE ACCESS BY INDEX ROWID| LV_BUSI_RECORD | 1 | 11 | 1 (0)| 00:00:01 | |* 27 | INDEX UNIQUE SCAN | PK_LV_BUSI_RECORD | 1 | | 1 (0)| 00:00:01 | |* 28 | TABLE ACCESS BY INDEX ROWID | LV_BUSI_BILL | 1 | 12 | 1 (0)| 00:00:01 | |* 29 | INDEX UNIQUE SCAN | PK_LV_BUSI_BILL | 1 | | 1 (0)| 00:00:01 | |* 30 | TABLE ACCESS BY INDEX ROWID | LV_URBAN_TOPAY_TMP | 1 | 21 | 2 (0)| 00:00:01 | |* 31 | INDEX RANGE SCAN | IDX_LV_URBAN_TOPAY_TMP_INDI | 12 | | 1 (0)| 00:00:01 | |* 32 | TABLE ACCESS BY INDEX ROWID | BS_FAMILY | 1 | 32 | 1 (0)| 00:00:01 | |* 33 | INDEX UNIQUE SCAN | PK_BS_FAMILY | 1 | | 1 (0)| 00:00:01 | | 34 | TABLE ACCESS BY INDEX ROWID | BS_PERSON_TYPE | 1 | 18 | 1 (0)| 00:00:01 | |* 35 | INDEX UNIQUE SCAN | PK_BS_PERSON_TYPE | 1 | | 1 (0)| 00:00:01 | |* 36 | INDEX UNIQUE SCAN | INDEX_BS_PRES_INSUR_UNIQUE | 1 | 12 | 1 (0)| 00:00:01 | | 37 | VIEW PUSHED PREDICATE | | 1 | 41 | 2 (0)| 00:00:01 | | 38 | NESTED LOOPS | | 1 | 57 | 2 (0)| 00:00:01 | |* 39 | TABLE ACCESS BY INDEX ROWID | BS_PERS_HOSP | 1 | 25 | 1 (0)| 00:00:01 | |* 40 | INDEX RANGE SCAN | PK_BS_PERS_HOSP | 2 | | 1 (0)| 00:00:01 | | 41 | TABLE ACCESS BY INDEX ROWID | BS_HOSPITAL | 1 | 32 | 1 (0)| 00:00:01 | |* 42 | INDEX UNIQUE SCAN | PK_BS_HOSPITAL | 1 | | 1 (0)| 00:00:01 | | 43 | TABLE ACCESS BY INDEX ROWID | BS_SEX | 1 | 6 | 1 (0)| 00:00:01 | |* 44 | INDEX UNIQUE SCAN | PK_BS_SEX | 1 | | 1 (0)| 00:00:01 | -------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 11 - SEL$1 / LT@SEL$1 12 - SEL$1 / LT@SEL$1 13 - SEL$1 / LBA@SEL$1 14 - SEL$1 / LBA@SEL$1 15 - SEL$1 / LBR@SEL$1 16 - SEL$1 / LBR@SEL$1 17 - SEL$1 / LBB@SEL$1 18 - SEL$1 / LBB@SEL$1 19 - SEL$1 / BI@SEL$1 20 - SEL$1 / BI@SEL$1 21 - SEL$3 24 - SEL$3 / LBA@SEL$3 25 - SEL$3 / LBA@SEL$3 26 - SEL$3 / LBR@SEL$3 27 - SEL$3 / LBR@SEL$3 28 - SEL$3 / LBB@SEL$3 29 - SEL$3 / LBB@SEL$3 30 - SEL$3 / LUTT@SEL$3 31 - SEL$3 / LUTT@SEL$3 32 - SEL$1 / BF@SEL$1 33 - SEL$1 / BF@SEL$1 34 - SEL$1 / PT@SEL$1 35 - SEL$1 / PT@SEL$1 36 - SEL$1 / BPI@SEL$1 37 - SEL$639F1A6F / TAB_HOSP@SEL$1 38 - SEL$639F1A6F 39 - SEL$639F1A6F / BPH@SEL$2 40 - SEL$639F1A6F / BPH@SEL$2 41 - SEL$639F1A6F / BH@SEL$2 42 - SEL$639F1A6F / BH@SEL$2 43 - SEL$1 / BS@SEL$1 44 - SEL$1 / BS@SEL$1 Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('10.2.0.4') OPT_PARAM('optimizer_index_cost_adj' 20) OPT_PARAM('optimizer_index_caching' 90) ALL_ROWS OUTLINE_LEAF(@"SEL$3") OUTLINE_LEAF(@"SEL$639F1A6F") PUSH_PRED(@"SEL$1" "TAB_HOSP"@"SEL$1" 12) OUTLINE_LEAF(@"SEL$1") OUTLINE(@"SEL$3") OUTLINE(@"SEL$2") OUTLINE(@"SEL$1") INDEX_RS_ASC(@"SEL$1" "LT"@"SEL$1" ("LV_URBAN_TOPAY_TMP"."CORP_ID" "LV_URBAN_TOPAY_TMP"."CURR_YEAR" "LV_URBAN_TOPAY_TMP"."PERS_TYPE")) INDEX_RS_ASC(@"SEL$1" "LBA"@"SEL$1" ("LV_BUSI_ASSIGN"."BUSI_ASG_NO")) INDEX_RS_ASC(@"SEL$1" "LBR"@"SEL$1" ("LV_BUSI_RECORD"."BUSI_RECO_NO")) INDEX_RS_ASC(@"SEL$1" "LBB"@"SEL$1" ("LV_BUSI_BILL"."BUSI_BILL_SN")) INDEX_RS_ASC(@"SEL$1" "BI"@"SEL$1" ("BS_INSURED"."INDI_ID")) INDEX_RS_ASC(@"SEL$1" "BF"@"SEL$1" ("BS_FAMILY"."FAMILY_ID")) INDEX_RS_ASC(@"SEL$1" "PT"@"SEL$1" ("BS_PERSON_TYPE"."PERS_TYPE" "BS_PERSON_TYPE"."CENTER_ID")) INDEX(@"SEL$1" "BPI"@"SEL$1" ("BS_PRES_INSUR"."INDI_ID" "BS_PRES_INSUR"."INSR_DETAIL_CODE" "BS_PRES_INSUR"."INDI_JOIN_STA")) NO_ACCESS(@"SEL$1" "TAB_HOSP"@"SEL$1") INDEX_RS_ASC(@"SEL$1" "BS"@"SEL$1" ("BS_SEX"."SEX")) LEADING(@"SEL$1" "LT"@"SEL$1" "LBA"@"SEL$1" "LBR"@"SEL$1" "LBB"@"SEL$1" "BI"@"SEL$1" "BF"@"SEL$1" "PT"@"SEL$1" "BPI"@"SEL$1" "TAB_HOSP"@"SEL$1" "BS"@"SEL$1") USE_NL(@"SEL$1" "LBA"@"SEL$1") USE_NL(@"SEL$1" "LBR"@"SEL$1") USE_NL(@"SEL$1" "LBB"@"SEL$1") USE_NL(@"SEL$1" "BI"@"SEL$1") USE_NL(@"SEL$1" "BF"@"SEL$1") USE_NL(@"SEL$1" "PT"@"SEL$1") USE_NL(@"SEL$1" "BPI"@"SEL$1") USE_NL(@"SEL$1" "TAB_HOSP"@"SEL$1") USE_NL(@"SEL$1" "BS"@"SEL$1") INDEX_RS_ASC(@"SEL$639F1A6F" "BPH"@"SEL$2" ("BS_PERS_HOSP"."INDI_ID" "BS_PERS_HOSP"."HOSPITAL_ID" "BS_PERS_HOSP"."FIRST_FLAG" "BS_PERS_HOSP"."BEG_YEAR" "BS_PERS_HOSP"."BIZ_TYPE")) INDEX_RS_ASC(@"SEL$639F1A6F" "BH"@"SEL$2" ("BS_HOSPITAL"."HOSPITAL_ID")) LEADING(@"SEL$639F1A6F" "BPH"@"SEL$2" "BH"@"SEL$2") USE_NL(@"SEL$639F1A6F" "BH"@"SEL$2") INDEX_RS_ASC(@"SEL$3" "LBA"@"SEL$3" ("LV_BUSI_ASSIGN"."BUSI_ASG_NO")) INDEX_RS_ASC(@"SEL$3" "LBR"@"SEL$3" ("LV_BUSI_RECORD"."BUSI_RECO_NO")) INDEX_RS_ASC(@"SEL$3" "LBB"@"SEL$3" ("LV_BUSI_BILL"."BUSI_BILL_SN")) INDEX_RS_ASC(@"SEL$3" "LUTT"@"SEL$3" ("LV_URBAN_TOPAY_TMP"."INDI_ID")) LEADING(@"SEL$3" "LBA"@"SEL$3" "LBR"@"SEL$3" "LBB"@"SEL$3" "LUTT"@"SEL$3") USE_NL(@"SEL$3" "LBR"@"SEL$3") USE_NL(@"SEL$3" "LBB"@"SEL$3") USE_NL(@"SEL$3" "LUTT"@"SEL$3") END_OUTLINE_DATA */ Predicate Information (identified by operation id): --------------------------------------------------- 11 - filter(("LT"."FAC_PAY_DATE">=TO_DATE(' 2014-12-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "LT"."CENTER_ID"='430726' AND "LT"."POLICY_ITEM_CODE" LIKE '%INDI_TOPAY' AND "LT"."FAC_PAY_DATE" IS NOT NULL AND "LT"."BUSI_ASG_NO" IS NOT NULL AND NVL("LT"."BUSI_ASG_NO",0)<>0 AND NVL("LT"."BUSI_ASG_NO",0)<>(-980) AND NVL("LT"."BUSI_ASG_NO",0)<>(-981) AND NVL("LT"."BUSI_ASG_NO",0)<>(-997) AND NVL("LT"."BUSI_ASG_NO",0)<>(-998) AND NVL("LT"."BUSI_ASG_NO",0)<>(-999) AND "LT"."FAC_PAY_DATE"< =TO_DATE(' 2015-01-05 23:59:59', 'syyyy-mm-dd hh24:mi:ss'))) 12 - access("LT"."CORP_ID"=19159 AND "LT"."CURR_YEAR"='2015') 14 - access("LT"."BUSI_ASG_NO"="LBA"."BUSI_ASG_NO") 16 - access("LBR"."BUSI_RECO_NO"="LBA"."BUSI_RECO_NO") 17 - filter("LBB"."CENTER_ID"='430726') 18 - access("LBR"."BUSI_BILL_SN"="LBB"."BUSI_BILL_SN") 19 - filter(("BI"."FAMILY_ID" IS NOT NULL AND "BI"."INDI_STA"=1)) 20 - access("BI"."INDI_ID"="LT"."INDI_ID") filter( IS NOT NULL) 25 - access("LBA"."BUSI_ASG_NO"=:B1) 27 - access("LBR"."BUSI_RECO_NO"="LBA"."BUSI_RECO_NO") 28 - filter("LBB"."CENTER_ID"='430726') 29 - access("LBR"."BUSI_BILL_SN"="LBB"."BUSI_BILL_SN") 30 - filter(("LUTT"."BUSI_ASG_NO"=:B1 AND "LUTT"."CORP_ID"=19159 AND "LUTT"."CENTER_ID"='430726')) 31 - access("LUTT"."INDI_ID"=:B1) 32 - filter(("BF"."CORP_ID"=19159 AND "BF"."CENTER_ID"='430726' AND "BF"."FAMILY_STA"=1)) 33 - access("BF"."FAMILY_ID"="BI"."FAMILY_ID") 35 - access("PT"."PERS_TYPE"="BI"."PERS_TYPE" AND "PT"."CENTER_ID"='430726') 36 - access("BPI"."INDI_ID"="BI"."INDI_ID" AND "BPI"."INSR_DETAIL_CODE"=21 AND "BPI"."INDI_JOIN_STA"=1) 39 - filter("BPH"."END_YEAR"='2015') 40 - access("BPH"."INDI_ID"="BI"."INDI_ID" AND "BPH"."FIRST_FLAG"=1) filter("BPH"."FIRST_FLAG"=1) 42 - access("BPH"."HOSPITAL_ID"="BH"."HOSPITAL_ID") 44 - access("BS"."SEX"=TO_NUMBER("BI"."SEX")) Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - (#keys=3) "LT"."FAC_PAY_DATE"[DATE,7], "BI"."INDI_ID"[NUMBER,22], "BI"."NAME"[VARCHAR2,20], "BF"."TELEPHONE"[VARCHAR2,20], TO_CHAR(INTERNAL_FUNCTION("LBB"."MAKE_BILL_TM"),'yyyy-mm-dd')[10], "PT"."PERS_NAME"[VARCHAR2,20], "BS"."SEX_NAME"[VARCHAR2,5], "LT"."PAY_MONEY"[NUMBER,22], "BI"."IDCARD"[VARCHAR2,21], "BI"."BIRTHDAY"[DATE,7], "BF"."HEADED_NAME"[VARCHAR2,50], NVL(DECODE("LT"."INTENSIVE_DISABILITY_FLAG",1,DECODE("LT"."LOWFLAG",1,'重症伤残,','重症伤残'),'')||DECODE("LT"."LOWFLAG",1 ,DECODE("LT"."NOTHING_FLAG",1,'低保,','低保'),'')||DECODE("LT"."NOTHING_FLAG",1,'三无',''),'标准')[18], "LBB"."AUDIT_MAN"[VARCHAR2,50], "TAB_HOSP"."HOSPITAL_NAME"[VARCHAR2,70] 2 - "LT"."INTENSIVE_DISABILITY_FLAG"[NUMBER,22], "LT"."PAY_MONEY"[NUMBER,22], "LT"."LOWFLAG"[NUMBER,22], "LT"."NOTHING_FLAG"[NUMBER,22], "LT"."FAC_PAY_DATE"[DATE,7], "LBB"."MAKE_BILL_TM"[DATE,7], "LBB"."AUDIT_MAN"[VARCHAR2,50], "BI"."INDI_ID"[NUMBER,22], "BI"."NAME"[VARCHAR2,20], "BI"."BIRTHDAY"[DATE,7], "BI"."IDCARD"[VARCHAR2,21], "BF"."HEADED_NAME"[VARCHAR2,50], "BF"."TELEPHONE"[VARCHAR2,20], "PT"."PERS_NAME"[VARCHAR2,20], "TAB_HOSP"."HOSPITAL_NAME"[VARCHAR2,70], "BS"."SEX_NAME"[VARCHAR2,5] 3 - "LT"."INTENSIVE_DISABILITY_FLAG"[NUMBER,22], "LT"."PAY_MONEY"[NUMBER,22], "LT"."LOWFLAG"[NUMBER,22], "LT"."NOTHING_FLAG"[NUMBER,22], "LT"."FAC_PAY_DATE"[DATE,7], "LBB"."MAKE_BILL_TM"[DATE,7], "LBB"."AUDIT_MAN"[VARCHAR2,50], "BI"."INDI_ID"[NUMBER,22], "BI"."NAME"[VARCHAR2,20], "BI"."SEX"[CHARACTER,1], "BI"."BIRTHDAY"[DATE,7], "BI"."IDCARD"[VARCHAR2,21], "BF"."HEADED_NAME"[VARCHAR2,50], "BF"."TELEPHONE"[VARCHAR2,20], "PT"."PERS_NAME"[VARCHAR2,20], "TAB_HOSP"."HOSPITAL_NAME"[VARCHAR2,70] 4 - "LT"."INTENSIVE_DISABILITY_FLAG"[NUMBER,22], "LT"."PAY_MONEY"[NUMBER,22], "LT"."LOWFLAG"[NUMBER,22], "LT"."NOTHING_FLAG"[NUMBER,22], "LT"."FAC_PAY_DATE"[DATE,7], "LBB"."MAKE_BILL_TM"[DATE,7], "LBB"."AUDIT_MAN"[VARCHAR2,50], "BI"."INDI_ID"[NUMBER,22], "BI"."NAME"[VARCHAR2,20], "BI"."SEX"[CHARACTER,1], "BI"."BIRTHDAY"[DATE,7], "BI"."IDCARD"[VARCHAR2,21], "BF"."HEADED_NAME"[VARCHAR2,50], "BF"."TELEPHONE"[VARCHAR2,20], "PT"."PERS_NAME"[VARCHAR2,20] 5 - "LT"."INTENSIVE_DISABILITY_FLAG"[NUMBER,22], "LT"."PAY_MONEY"[NUMBER,22], "LT"."LOWFLAG"[NUMBER,22], "LT"."NOTHING_FLAG"[NUMBER,22], "LT"."FAC_PAY_DATE"[DATE,7], "LBB"."MAKE_BILL_TM"[DATE,7], "LBB"."AUDIT_MAN"[VARCHAR2,50], "BI"."INDI_ID"[NUMBER,22], "BI"."NAME"[VARCHAR2,20], "BI"."SEX"[CHARACTER,1], "BI"."BIRTHDAY"[DATE,7], "BI"."IDCARD"[VARCHAR2,21], "BF"."HEADED_NAME"[VARCHAR2,50], "BF"."TELEPHONE"[VARCHAR2,20], "PT"."PERS_NAME"[VARCHAR2,20] 6 - "LT"."INTENSIVE_DISABILITY_FLAG"[NUMBER,22], "LT"."PAY_MONEY"[NUMBER,22], "LT"."LOWFLAG"[NUMBER,22], "LT"."NOTHING_FLAG"[NUMBER,22], "LT"."FAC_PAY_DATE"[DATE,7], "LBB"."MAKE_BILL_TM"[DATE,7], "LBB"."AUDIT_MAN"[VARCHAR2,50], "BI"."INDI_ID"[NUMBER,22], "BI"."PERS_TYPE"[NUMBER,22], "BI"."NAME"[VARCHAR2,20], "BI"."SEX"[CHARACTER,1], "BI"."BIRTHDAY"[DATE,7], "BI"."IDCARD"[VARCHAR2,21], "BF"."HEADED_NAME"[VARCHAR2,50], "BF"."TELEPHONE"[VARCHAR2,20] 7 - "LT"."INTENSIVE_DISABILITY_FLAG"[NUMBER,22], "LT"."PAY_MONEY"[NUMBER,22], "LT"."LOWFLAG"[NUMBER,22], "LT"."NOTHING_FLAG"[NUMBER,22], "LT"."FAC_PAY_DATE"[DATE,7], "LBB"."MAKE_BILL_TM"[DATE,7], "LBB"."AUDIT_MAN"[VARCHAR2,50], "BI"."INDI_ID"[NUMBER,22], "BI"."PERS_TYPE"[NUMBER,22], "BI"."NAME"[VARCHAR2,20], "BI"."SEX"[CHARACTER,1], "BI"."BIRTHDAY"[DATE,7], "BI"."IDCARD"[VARCHAR2,21], "BI"."FAMILY_ID"[NUMBER,22] 8 - "LT"."INDI_ID"[NUMBER,22], "LT"."INTENSIVE_DISABILITY_FLAG"[NUMBER,22], "LT"."PAY_MONEY"[NUMBER,22], "LT"."LOWFLAG"[NUMBER,22], "LT"."NOTHING_FLAG"[NUMBER,22], "LT"."BUSI_ASG_NO"[NUMBER,22], "LT"."FAC_PAY_DATE"[DATE,7], "LBB"."MAKE_BILL_TM"[DATE,7], "LBB"."AUDIT_MAN"[VARCHAR2,50] 9 - "LT"."INDI_ID"[NUMBER,22], "LT"."INTENSIVE_DISABILITY_FLAG"[NUMBER,22], "LT"."PAY_MONEY"[NUMBER,22], "LT"."LOWFLAG"[NUMBER,22], "LT"."NOTHING_FLAG"[NUMBER,22], "LT"."BUSI_ASG_NO"[NUMBER,22], "LT"."FAC_PAY_DATE"[DATE,7], "LBR"."BUSI_BILL_SN"[NUMBER,22] 10 - "LT"."INDI_ID"[NUMBER,22], "LT"."INTENSIVE_DISABILITY_FLAG"[NUMBER,22], "LT"."PAY_MONEY"[NUMBER,22], "LT"."LOWFLAG"[NUMBER,22], "LT"."NOTHING_FLAG"[NUMBER,22], "LT"."BUSI_ASG_NO"[NUMBER,22], "LT"."FAC_PAY_DATE"[DATE,7], "LBA"."BUSI_RECO_NO"[NUMBER,22] 11 - "LT"."INDI_ID"[NUMBER,22], "LT"."INTENSIVE_DISABILITY_FLAG"[NUMBER,22], "LT"."PAY_MONEY"[NUMBER,22], "LT"."LOWFLAG"[NUMBER,22], "LT"."NOTHING_FLAG"[NUMBER,22], "LT"."BUSI_ASG_NO"[NUMBER,22], "LT"."FAC_PAY_DATE"[DATE,7] 12 - "SYS_ALIAS_1".ROWID[ROWID,10] 13 - "LBA"."BUSI_RECO_NO"[NUMBER,22] 14 - "LBA".ROWID[ROWID,10] 15 - "LBR"."BUSI_BILL_SN"[NUMBER,22] 16 - "LBR".ROWID[ROWID,10] 17 - "LBB"."MAKE_BILL_TM"[DATE,7], "LBB"."AUDIT_MAN"[VARCHAR2,50] 18 - "LBB".ROWID[ROWID,10] 19 - "BI"."INDI_ID"[NUMBER,22], "BI"."PERS_TYPE"[NUMBER,22], "BI"."NAME"[VARCHAR2,20], "BI"."SEX"[CHARACTER,1], "BI"."BIRTHDAY"[DATE,7], "BI"."IDCARD"[VARCHAR2,21], "BI"."FAMILY_ID"[NUMBER,22] 20 - "SYS_ALIAS_2".ROWID[ROWID,10], "BI"."INDI_ID"[NUMBER,22] 23 - "LBR"."BUSI_BILL_SN"[NUMBER,22] 24 - "LBA"."BUSI_RECO_NO"[NUMBER,22] 25 - "LBA".ROWID[ROWID,10] 26 - "LBR"."BUSI_BILL_SN"[NUMBER,22] 27 - "LBR".ROWID[ROWID,10] 29 - "LBB".ROWID[ROWID,10] 31 - "LUTT".ROWID[ROWID,10] 32 - "BF"."HEADED_NAME"[VARCHAR2,50], "BF"."TELEPHONE"[VARCHAR2,20] 33 - "BF".ROWID[ROWID,10] 34 - "PT"."PERS_NAME"[VARCHAR2,20] 35 - "PT".ROWID[ROWID,10] 37 - "TAB_HOSP"."HOSPITAL_NAME"[VARCHAR2,70] 38 - "BH"."HOSPITAL_NAME"[VARCHAR2,70] 39 - "BPH"."HOSPITAL_ID"[VARCHAR2,20] 40 - "BPH".ROWID[ROWID,10], "BPH"."HOSPITAL_ID"[VARCHAR2,20] 41 - "BH"."HOSPITAL_NAME"[VARCHAR2,70] 42 - "BH".ROWID[ROWID,10] 43 - "BS"."SEX_NAME"[VARCHAR2,5] 44 - "BS".ROWID[ROWID,10] Note ----- - SQL profile "coe_36cbabzyq13gy_1849931106" used for this statement 276 rows selected.
从Note部分的SQL profile "coe_36cbabzyq13gy_1849931106" used for this statement可知SQL Profile对该SQL生效了,而且从执行计划中可知现在与去掉Hint“/*+ index(lt,PK_LV_URBAN_TOPAY_TMP) */”之后的SQL执行计划一样的。至此,对该SQL的优化也就完成。
这个SQL语句执行缓慢的原因就是因为使用了Hint"/*+ index(lt,PK_LV_URBAN_TOPAY_TMP) */"后,使CBO选择了错误的执行计划而造成的。