某系统上午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选择了错误的执行计划而造成的。
