乱用Oracle Hint造成性能问题案例一

某系统上午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_TMPDX_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_PAYIDX_LV_URBAN_TO NONUNIQUE 2 #### 2,223 18,979,800 17 708 1,574,170 YES NO 530,075 12-08-2014
PAY_TMP_BUSIDX_LV_URBAN_TO NONUNIQUE 3 #### 223 22,474,402 393 25,642 5,718,207 YES NO 284,917 12-08-2014
PAY_TMP_CENTERIDX_LV_URBAN_TO NONUNIQUE 2 #### 660,682 21,796,771 1 17 11,256,787 YES NO 407,563 12-08-2014
PAY_TMP_FAMIDX_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_INDIIDX_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_NOIDX_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_SOMEIDX_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_IDIDX_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 NULLPK_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