执行下面的语句来接受SQL 概要文件
SQL> execute dbms_sqltune.accept_sql_profile(task_name =>'my_sql_tuning_task_2014080804', replace => TRUE,force_match => TRUE);
PL/SQL procedure successfully completed.
再来测试该语句
SQL> select * from v_zzzd_ylbx_ylfymxcx where aac002='430703198202280017'; AAC001 AAC002 AAB301 AKF008 AKF010 AKF011 AKF012 AKF013 AKF014 ------- -------------------- ------- ----------- -------- ------- -------- -------- ------- 44499 430703198202280017 430701 4307000305 18000304 购药 19 19 0 44499 430703198202280017 430701 4307030186 14200513 购药 34 34 0 44499 430703198202280017 430701 4307000070 11535710 购药 7 7 0 44499 430703198202280017 430701 4307000211 13157523 购药 10 10 0 44499 430703198202280017 430701 4307000178 10504509 购药 37.2 37.2 0 44499 430703198202280017 430701 4307000025 14186783 购药 6.5 6.5 0 44499 430703198202280017 430701 4307000211 18855092 购药 51 51 0 44499 430703198202280017 430701 4307000025 23298689 购药 32 32 0 44499 430703198202280017 430701 4307000305 17251025 购药 20 20 0 44499 430703198202280017 430701 4307000211 11246538 购药 10.5 10.5 0 44499 430703198202280017 430701 4307000011 20015343 门诊 20 20 0 44499 430703198202280017 430701 4307000135 13248044 购药 103.2 103.2 0 44499 430703198202280017 430701 4307000070 17745955 购药 20 20 0 44499 430703198202280017 430701 4307000011 23548511 门诊 94.2 94.2 0 44499 430703198202280017 430701 4307000305 18000319 购药 16 16 0 44499 430703198202280017 430701 4307000025 20291585 购药 374 374 0 44499 430703198202280017 430701 4307000075 11425923 购药 11.8 11.8 0 44499 430703198202280017 430701 4307000089 23298593 购药 170.8 170.8 0 44499 430703198202280017 430701 4307000110 11548588 购药 28.5 28.5 0 44499 430703198202280017 430701 4307000011 18454938 门诊 105.8 105.8 0 44499 430703198202280017 430701 4307000075 11757756 购药 282.7 282.7 0 44499 430703198202280017 430701 4307000025 10545113 购药 340.8 340.8 0 44499 430703198202280017 430701 4307000285 17325032 购药 67.5 67.5 0 44499 430703198202280017 430701 4307000070 17341126 购药 87 87 0 44499 430703198202280017 430701 4307000211 17655418 购药 20 20 0 44499 430703198202280017 430701 4307000011 19042114 门诊 127.2 127.2 0 44499 430703198202280017 430701 4307000211 18070864 购药 6 6 0 44499 430703198202280017 430701 4307000011 23547574 门诊 36 36 0 28 rows selected. Elapsed: 00:00:00.01 SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced')); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------- SQL_ID 1n2t3u0q0gmhz, child number 0 ------------------------------------- select * from v_zzzd_ylbx_ylfymxcx where aac002='430703198202280017' Plan hash value: 484693682 ----------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 25 (100)| | | 1 | HASH GROUP BY | | 251 | 53965 | 25 (4)| 00:00:01 | |* 2 | TABLE ACCESS BY INDEX ROWID | MT_PAY_RECORD_FIN | 1 | 44 | 1 (0)| 00:00:01 | | 3 | NESTED LOOPS | | 251 | 53965 | 24 (0)| 00:00:01 | | 4 | NESTED LOOPS | | 28 | 4788 | 19 (6)| 00:00:01 | | 5 | NESTED LOOPS | | 28 | 4284 | 18 (6)| 00:00:01 | | 6 | NESTED LOOPS | | 28 | 4116 | 17 (6)| 00:00:01 | | 7 | NESTED LOOPS | | 28 | 4060 | 16 (7)| 00:00:01 | | 8 | NESTED LOOPS | | 28 | 3108 | 10 (0)| 00:00:01 | |* 9 | HASH JOIN | | 28 | 2968 | 9 (0)| 00:00:01 | | 10 | TABLE ACCESS FULL | BS_BIZTYPE | 96 | 1728 | 3 (0)| 00:00:01 | | 11 | INLIST ITERATOR | | | | | | | 12 | TABLE ACCESS BY INDEX ROWID| MT_BIZ_FIN | 79 | 6952 | 6 (0)| 00:00:01 | |* 13 | INDEX RANGE SCAN | IDX$$_429C0001 | 27 | | 1 (0)| 00:00:01 | |* 14 | INDEX UNIQUE SCAN | PK_BS_CORP | 1 | 5 | 1 (0)| 00:00:01 | | 15 | TABLE ACCESS BY INDEX ROWID | BS_HOSPITAL | 1 | 34 | 1 (0)| 00:00:01 | |* 16 | INDEX UNIQUE SCAN | PK_BS_HOSPITAL | 1 | | 1 (0)| 00:00:01 | |* 17 | INDEX UNIQUE SCAN | PK_BS_HOSP_LEVEL | 1 | 2 | 1 (0)| 00:00:01 | |* 18 | INDEX UNIQUE SCAN | PK_BS_INSURED | 1 | 6 | 1 (0)| 00:00:01 | |* 19 | INDEX RANGE SCAN | INX_BS_DISEASE_01 | 1 | 18 | 1 (0)| 00:00:01 | |* 20 | INDEX RANGE SCAN | IDX$$_429C0002 | 1 | | 1 (0)| 00:00:01 | ----------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$F5BB74E1 2 - SEL$F5BB74E1 / B@SEL$2 10 - SEL$F5BB74E1 / F@SEL$2 12 - SEL$F5BB74E1 / A@SEL$2 13 - SEL$F5BB74E1 / A@SEL$2 14 - SEL$F5BB74E1 / G@SEL$2 15 - SEL$F5BB74E1 / D@SEL$2 16 - SEL$F5BB74E1 / D@SEL$2 17 - SEL$F5BB74E1 / E@SEL$2 18 - SEL$F5BB74E1 / H@SEL$2 19 - SEL$F5BB74E1 / C@SEL$2 20 - SEL$F5BB74E1 / B@SEL$2 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$F5BB74E1") MERGE(@"SEL$2") OUTLINE(@"SEL$1") OUTLINE(@"SEL$2") FULL(@"SEL$F5BB74E1" "F"@"SEL$2") INDEX_RS_ASC(@"SEL$F5BB74E1" "A"@"SEL$2" ("MT_BIZ_FIN"."IDCARD" "MT_BIZ_FIN"."VALID_FLAG" "MT_BIZ_FIN"."PERS_TYPE" "MT_BIZ_FIN"."BIZ_TYPE")) NUM_INDEX_KEYS(@"SEL$F5BB74E1" "A"@"SEL$2" "IDX$$_429C0001" 3) INDEX(@"SEL$F5BB74E1" "G"@"SEL$2" ("BS_CORP"."CORP_ID")) INDEX_RS_ASC(@"SEL$F5BB74E1" "D"@"SEL$2" ("BS_HOSPITAL"."HOSPITAL_ID")) INDEX(@"SEL$F5BB74E1" "E"@"SEL$2" ("BS_HOSP_LEVEL"."HOSP_LEVEL")) INDEX(@"SEL$F5BB74E1" "H"@"SEL$2" ("BS_INSURED"."INDI_ID")) INDEX(@"SEL$F5BB74E1" "C"@"SEL$2" ("BS_DISEASE"."CENTER_ID" "BS_DISEASE"."ICD")) INDEX(@"SEL$F5BB74E1" "B"@"SEL$2" ("MT_PAY_RECORD_FIN"."HOSPITAL_ID" "MT_PAY_RECORD_FIN"."SERIAL_NO")) LEADING(@"SEL$F5BB74E1" "F"@"SEL$2" "A"@"SEL$2" "G"@"SEL$2" "D"@"SEL$2" "E"@"SEL$2" "H"@"SEL$2" "C"@"SEL$2" "B"@"SEL$2") USE_HASH(@"SEL$F5BB74E1" "A"@"SEL$2") USE_NL(@"SEL$F5BB74E1" "G"@"SEL$2") USE_NL(@"SEL$F5BB74E1" "D"@"SEL$2") USE_NL(@"SEL$F5BB74E1" "E"@"SEL$2") USE_NL(@"SEL$F5BB74E1" "H"@"SEL$2") USE_NL(@"SEL$F5BB74E1" "C"@"SEL$2") USE_NL(@"SEL$F5BB74E1" "B"@"SEL$2") END_OUTLINE_DATA */ Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("B"."VALID_FLAG"='1') 9 - access("A"."BIZ_TYPE"="F"."BIZ_TYPE" AND "A"."CENTER_ID"="F"."CENTER_ID") 13 - access("A"."IDCARD"='430703198202280017' AND "A"."VALID_FLAG"='1' AND (("A"."PERS_TYPE"='1' OR "A"."PERS_TYPE"='2'))) filter(("A"."BIZ_TYPE"='10' OR "A"."BIZ_TYPE"='11' OR "A"."BIZ_TYPE"='12' OR "A"."BIZ_TYPE"='13' OR "A"."BIZ_TYPE"='16' OR "A"."BIZ_TYPE"='17')) 14 - access("A"."CORP_ID"="G"."CORP_ID") 16 - access("A"."HOSPITAL_ID"="D"."HOSPITAL_ID") 17 - access("D"."HOSP_LEVEL"="E"."HOSP_LEVEL") 18 - access("H"."INDI_ID"="A"."INDI_ID") 19 - access("A"."CENTER_ID"="C"."CENTER_ID" AND "A"."FIN_DISEASE"="C"."ICD") 20 - access("A"."HOSPITAL_ID"="B"."HOSPITAL_ID" AND "A"."SERIAL_NO"="B"."SERIAL_NO") Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - "A"."INDI_ID"[NUMBER,22], "A"."IDCARD"[VARCHAR2,25], "A"."CENTER_ID"[VARCHAR2,10], "A"."NAME"[VARCHAR2,20], "A"."HOSPITAL_ID"[VARCHAR2,20], "D"."HOSPITAL_NAME"[VARCHAR2,70], "A"."SERIAL_NO"[VARCHAR2,16], "F"."BIZ_NAME"[VARCHAR2,20], "A"."FIN_DATE"[DATE,7], "A"."IN_DAYS"[NUMBER,22], SUM("B"."REAL_PAY")[22], SUM(CASE "B"."FUND_ID" WHEN '003' THEN "B"."REAL_PAY" ELSE 0 END )[22], SUM(CASE "B"."FUND_ID" WHEN '001' THEN "B"."REAL_PAY" ELSE 0 END )[22], SUM(CASE WHEN (("B"."FUND_ID"='999' OR "B"."FUND_ID"='003') AND ("B"."POLICY_ITEM_CODE"='S00' OR "B"."POLICY_ITEM_CODE"='S01' OR "B"."POLICY_ITEM_CODE"='C001' OR "B"."POLICY_ITEM_CODE"='C004''C006')) THEN "B"."REAL_PAY" ELSE 0 END )[22], SUM(CASE "B"."FUND_ID" WHEN '003' THEN "B"."REAL_PAY" WHEN '999' THEN "B"."REAL_PAY" ELSE 0 END )[22], SUM(CASE "B"."FUND_ID" WHEN '001' THEN "B"."REAL_PAY" WHEN '201' THEN "B"."REAL_PAY" WHEN '301' THEN "B"."REAL_PAY" ELSE 0 END )[22] 2 - "B"."POLICY_ITEM_CODE"[VARCHAR2,20], "B"."FUND_ID"[VARCHAR2,3], "B"."REAL_PAY"[NUMBER,22] 3 - "A"."CENTER_ID"[VARCHAR2,10], "F"."BIZ_NAME"[VARCHAR2,20], "A"."HOSPITAL_ID"[VARCHAR2,20], "A"."SERIAL_NO"[VARCHAR2,16], "A"."INDI_ID"[NUMBER,22], "A"."NAME"[VARCHAR2,20], "A"."IDCARD"[VARCHAR2,25], "A"."IN_DAYS"[NUMBER,22], "A"."FIN_DATE"[DATE,7], "D"."HOSPITAL_NAME"[VARCHAR2,70], "B".ROWID[ROWID,10] 4 - "A"."CENTER_ID"[VARCHAR2,10], "F"."BIZ_NAME"[VARCHAR2,20], "A"."HOSPITAL_ID"[VARCHAR2,20], "A"."SERIAL_NO"[VARCHAR2,16], "A"."INDI_ID"[NUMBER,22], "A"."NAME"[VARCHAR2,20], "A"."IDCARD"[VARCHAR2,25], "A"."IN_DAYS"[NUMBER,22], "A"."FIN_DATE"[DATE,7], "D"."HOSPITAL_NAME"[VARCHAR2,70] 5 - "A"."CENTER_ID"[VARCHAR2,10], "F"."BIZ_NAME"[VARCHAR2,20], "A"."HOSPITAL_ID"[VARCHAR2,20], "A"."SERIAL_NO"[VARCHAR2,16], "A"."INDI_ID"[NUMBER,22], "A"."NAME"[VARCHAR2,20], "A"."IDCARD"[VARCHAR2,25], "A"."IN_DAYS"[NUMBER,22], "A"."FIN_DISEASE"[VARCHAR2,20], "A"."FIN_DATE"[DATE,7], "D"."HOSPITAL_NAME"[VARCHAR2,70] 6 - "A"."CENTER_ID"[VARCHAR2,10], "F"."BIZ_NAME"[VARCHAR2,20], "A"."HOSPITAL_ID"[VARCHAR2,20], "A"."SERIAL_NO"[VARCHAR2,16], "A"."INDI_ID"[NUMBER,22], "A"."NAME"[VARCHAR2,20], "A"."IDCARD"[VARCHAR2,25], "A"."IN_DAYS"[NUMBER,22], "A"."FIN_DISEASE"[VARCHAR2,20], "A"."FIN_DATE"[DATE,7], "D"."HOSPITAL_NAME"[VARCHAR2,70] 7 - "A"."CENTER_ID"[VARCHAR2,10], "F"."BIZ_NAME"[VARCHAR2,20], "A"."HOSPITAL_ID"[VARCHAR2,20], "A"."SERIAL_NO"[VARCHAR2,16], "A"."INDI_ID"[NUMBER,22], "A"."NAME"[VARCHAR2,20], "A"."IDCARD"[VARCHAR2,25], "A"."IN_DAYS"[NUMBER,22], "A"."FIN_DISEASE"[VARCHAR2,20], "A"."FIN_DATE"[DATE,7], "D"."HOSPITAL_NAME"[VARCHAR2,70], "D"."HOSP_LEVEL"[CHARACTER,1] 8 - "A"."CENTER_ID"[VARCHAR2,10], "F"."BIZ_NAME"[VARCHAR2,20], "A"."HOSPITAL_ID"[VARCHAR2,20], "A"."SERIAL_NO"[VARCHAR2,16], "A"."INDI_ID"[NUMBER,22], "A"."NAME"[VARCHAR2,20], "A"."IDCARD"[VARCHAR2,25], "A"."IN_DAYS"[NUMBER,22], "A"."FIN_DISEASE"[VARCHAR2,20], "A"."FIN_DATE"[DATE,7] 9 - (#keys=2) "A"."CENTER_ID"[VARCHAR2,10], "F"."BIZ_NAME"[VARCHAR2,20], "A"."HOSPITAL_ID"[VARCHAR2,20], "A"."SERIAL_NO"[VARCHAR2,16], "A"."INDI_ID"[NUMBER,22], "A"."NAME"[VARCHAR2,20], "A"."IDCARD"[VARCHAR2,25], "A"."CORP_ID"[NUMBER,22], "A"."IN_DAYS"[NUMBER,22], "A"."FIN_DISEASE"[VARCHAR2,20], "A"."FIN_DATE"[DATE,7] 10 - "F"."CENTER_ID"[VARCHAR2,10], "F"."BIZ_TYPE"[CHARACTER,2], "F"."BIZ_NAME"[VARCHAR2,20] 11 - "A"."HOSPITAL_ID"[VARCHAR2,20], "A"."SERIAL_NO"[VARCHAR2,16], "A"."BIZ_TYPE"[VARCHAR2,2], "A"."INDI_ID"[NUMBER,22], "A"."NAME"[VARCHAR2,20], "A"."IDCARD"[VARCHAR2,25], "A"."CORP_ID"[NUMBER,22], "A"."IN_DAYS"[NUMBER,22], "A"."FIN_DISEASE"[VARCHAR2,20], "A"."FIN_DATE"[DATE,7], "A"."CENTER_ID"[VARCHAR2,10] 12 - "A"."HOSPITAL_ID"[VARCHAR2,20], "A"."SERIAL_NO"[VARCHAR2,16], "A"."BIZ_TYPE"[VARCHAR2,2], "A"."INDI_ID"[NUMBER,22], "A"."NAME"[VARCHAR2,20], "A"."PERS_TYPE"[VARCHAR2,3], "A"."IDCARD"[VARCHAR2,25], "A"."CORP_ID"[NUMBER,22], "A"."IN_DAYS"[NUMBER,22], "A"."FIN_DISEASE"[VARCHAR2,20], "A"."FIN_DATE"[DATE,7], "A"."CENTER_ID"[VARCHAR2,10] 13 - "A".ROWID[ROWID,10], "A"."IDCARD"[VARCHAR2,25], "A"."PERS_TYPE"[VARCHAR2,3], "A"."BIZ_TYPE"[VARCHAR2,2] 15 - "D"."HOSPITAL_NAME"[VARCHAR2,70], "D"."HOSP_LEVEL"[CHARACTER,1] 16 - "D".ROWID[ROWID,10] 20 - "B".ROWID[ROWID,10] Note ----- - SQL profile "SYS_SQLPROF_0151ed60f3d28000" used for this statement 163 rows selected. |
从SQL profile "SYS_SQLPROF_0151ed60f3d28000" used for this statement 这个信息就是知道已经使用了SQL概要文件
现在语句执行只要0.1毫秒
