当前位置:  数据库>oracle

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

    来源: 互联网  发布时间:2017-06-14

    本文导语: 某系统上午9点到11点的AWR报告中TOP SQL,其中消耗时间最长的花了9770秒,该SQL_ID为36cbabzyq13gy这条SQL语句与SQL_ID为0frcad5600xdu,g1a0qu2b42j83所对应的SQL语句除了文本值不一样外,其它部分是相同的,这里没有使用绑定变量 SQL ordered by El...

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


    
 
 

您可能感兴趣的文章:

  • LINUX下访问oracle时sleeping的线程过多,达到300多个,造成系统拥堵!
  • Oracle 数据库(oracle Database)性能调优技术详解
  • Oracle收购TimesTen 提高数据库软件性能
  • 关于提高Oracle数据库性能的四个错误认识
  • 用Oracle动态性能视图采集查询调优数
  • Oracle性能究极优化 上第1/2页
  • 用PHP连mysql比oracle数据库性能好
  • Oracle性能究极优化 下
  • 保持Oracle数据优良性能的技巧分享
  • 100分寻求最优化的连接oracle的java程序,请给我讲出理由,我是初学者,在做项目时不想让连接oracle影响我的程序性能
  • Oracle数据库应用程序性能优化探究
  • oracle 使用递归的性能提示测试对比
  • 善用Oracle表空间设计提升数据库性能
  • Oracle性能究极优化
  • Oracle SQL性能优化系列学习一
  • Oracle SQL性能优化系列学习三
  • Linux平台下如何监控Oracle数据库的性能
  • Oracle SQL性能优化系列学习二
  • 性能陷阱:Oracle表连接中范围比较
  • 基于Oracle的高性能动态SQL程序开发
  • 浅谈Oracle性能优化可能出现的问题
  • 如何保持Oracle数据库的优良性能
  •  
    本站(WWW.)旨在分享和传播互联网科技相关的资讯和技术,将尽最大努力为读者提供更好的信息聚合和浏览方式。
    本站(WWW.)站内文章除注明原创外,均为转载、整理或搜集自网络。欢迎任何形式的转载,转载请注明出处。












  • 相关文章推荐
  • 浅析基于Oracle案例知识库设计
  • oracle使用sql脚本生成csv文件案例学习
  • Oracle案例:分析10053跟踪文件
  • Linux下Oracle常见安装错误总结及参考案例
  • Oracle PL/SQL入门案例实践
  • oracle—SQL技巧之(一)连续记录查询sql案例测试
  • Oracle案例详细分析:Job任务停止执行
  • Oracle 12c发布简单介绍及官方下载地址
  • 在linux下安装oracle,如何设置让oracle自动启动!也就是让oracle那个服务自动启动,不是手动的
  • oracle 11g最新版官方下载地址
  • 请问su oracle 和su - oracle有什么不同?
  • Oracle 数据库(oracle Database)Select 多表关联查询方式
  • 虚拟机装Oracle R12与Oracle10g
  • Web服务器/前端 iis7站长之家
  • Oracle 数据库开发工具 Oracle SQL Developer
  • 如何设置让Oracle SQL Developer显示的时间包含时分秒
  • Oracle EBS R12 支持 Oracle Database 11g
  • Oracle 10g和Oracle 11g网格技术介绍
  • SCO unix下安装oracle,但没有光盘,请大家推荐一个oracle下载站点(unix版本的)。谢谢!!!!
  • oracle中如何把表中具有相同值列的多行数据合并成一行
  • 请问大家用oracle数据库, 用import oracle.*;下的东西么? 还是用标准库?
  • ORACLE日期相关操作
  • Linux /$ORACLE_HOME $ORACLE_HOME
  • ORACLE数据库常用字段数据类型介绍
  • Linux系统下Oracle的启动与Oracle监听的启动
  • Oracle 12c的九大最新技术特性介绍
  • 请问在solaris下安装ORACLE,用root用户和用oracle用户安装有什么区别么?
  • ORACLE中DBMS_RANDOM随机数生成包
  • 网间Oracle的连接,远程连接Oracle服务器??


  • 站内导航:


    特别声明:169IT网站部分信息来自互联网,如果侵犯您的权利,请及时告知,本站将立即删除!

    ©2012-2021,