dbms_sqltune.report_sql_monitor 自动调优
--创建 dbms_sqltune.create_tuning_task ;
--执行 dbms_sqltune.execute_tuning_task;
--产看创建的task 和 status SELECT TASK_NAME, STATUS FROM DBA_ADVISOR_LOG D LEFT JOIN USER_ADVISOR_TASKS U D.TASK_NAME = U.TASK_NAME WHERE D.TASK_NAME = 'TASK_NAME';
--显示调优结果 SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('test_sql_tuning')
1 /* 2 CREATE TABLE his_product_deal_main0801 AS 3 select * FROM his_product_deal_main m 4 WHERE M.DEALDATE BETWEEN TO_DATE('20170801','YYYYMMDD') AND TO_DATE('20170802','YYYYMMDD') ; 5 CREATE TABLE his_commision_split0801 AS 6 select * FROM his_base_deal_commision_split s 7 WHERE s.DEALDATE BETWEEN TO_DATE('20170801','YYYYMMDD') AND TO_DATE('20170802','YYYYMMDD') ; 8 CREATE TABLE product_pa_0801 AS 9 select * FROM product_deal_main_pa pa 10 WHERE pa.DEALDATE BETWEEN TO_DATE('20170801','YYYYMMDD') AND TO_DATE('20170802','YYYYMMDD') ; 11 12 SELECT * FROM his_product_deal_main0801 ; 13 SELECT * FROM his_commision_split0801 ; 14 SELECT * FROM product_pa_0801 ; 15 */ 16 17 18 WITH MAIN1 AS 19 (SELECT 1 bsid , 20 M1.NO, 21 M1.SELLERNAME, 22 M1.BUYERNAME, 23 M1.BUYERBP, 24 M1.SELLERBP, 25 DECODE(M1.buyerMKSURE, 0, '没有签约', 1, '已经签约', 2, '预签约') 签约标志, 26 DECODE(M1.buyerTAXVARITY, -1, '零税', 0, '含税', 1, '不含税') 含税标志, 27 DECODE(m1.BUYERSPFLAG, 0, '', -1, '业务特殊修改', 1, '特殊修改', 2, 'bridge', 3, '单笔签约', 4, '预估未收', 5, '预估bridge', '') SPFLAG, 28 M1.BUYERFEEPRICERM AS 应收 , 29 M1.BUYERTAXAFTERPRICE 实收, 30 M1.BUYERTAXPRICE AS 税额 31 FROM V_PRODUCT_DEAL_MAIN M1 32 WHERE m1.STATE =9 AND 33 M1.DEALDATE BETWEEN TO_DATE('20170801','YYYYMMDD') AND TO_DATE('20170802','YYYYMMDD') 34 UNION ALL 35 SELECT 2 bsid , 36 M1.NO, 37 M1.SELLERNAME, 38 M1.BUYERNAME, 39 M1.BUYERBP, 40 M1.SELLERBP, 41 DECODE(M1.sellerMKSURE, 0, '没有签约', 1, '已经签约', 2, '预签约') 签约标志, 42 DECODE(M1.SELLERTAXVARITY, -1, '零税', 0, '含税', 1, '不含税') 含税标志, 43 DECODE(m1.sellERSPFLAG, 0, '', -1, '业务特殊修改', 1, '特殊修改', 2, 'bridge', 3, '单笔签约', 4, '预估未收', 5, '预估bridge', '') SPFLAG, 44 M1.SELLERFEEPRICERM AS 应收, 45 M1.SELLERTAXAFTERPRICE AS 实收, 46 M1.SELLERTAXPRICE AS 税额 47 FROM V_PRODUCT_DEAL_MAIN M1 48 WHERE m1.STATE =9 AND M1.DEALDATE BETWEEN TO_DATE('20170801','YYYYMMDD') AND TO_DATE('20170802','YYYYMMDD') 49 50 51 52 ), 53 CSPLIT AS 54 ( 55 56 SELECT BS.DEALNO, 57 BS.BSFLAG, 58 SUM(DECODE(BS.BSFLAG, 1, BS.AMOUNTCNY)) cs_买_实收, 59 SUM(DECODE(BS.BSFLAG, 1, BS.TAXAFTERPRICE)) cs_买_TAXAFTERPRICE, 60 SUM(DECODE(BS.BSFLAG, 2, BS.AMOUNTCNY)) cs_卖_实收, 61 SUM(DECODE(BS.BSFLAG, 2, BS.TAXAFTERPRICE)) cs_卖_TAXAFTERPRICE 62 FROM HIS_BASE_DEAL_COMMISION_SPLIT BS 63 WHERE bs.status=0 AND BS.DEALDATE BETWEEN TO_DATE('20170801','YYYYMMDD') AND TO_DATE('20170802','YYYYMMDD') 64 GROUP BY BS.DEALNO, BS.BSFLAG 65 ORDER BY BS.DEALNO 66 67 ),PPA AS ( 68 69 SELECT 1 bsid , 70 M1.NO, 71 M1.BUYERNAME, 72 M1.BUYERBP, 73 DECODE(M1.buyerMKSURE, 0, '没有签约', 1, '已经签约', 2, '预签约') 签约标志, 74 DECODE(M1.buyerTAXVARITY, -1, '零税', 0, '含税', 1, '不含税') 含税标志, 75 DECODE(m1.buyerSPFLAG, 0, '', -1, '业务特殊修改', 1, '特殊修改', 2, 'bridge', 3, '单笔签约', 4, '预估未收', 5, '预估bridge', '') SPFLAG, 76 77 M1.BUYERFEEPRICERM AS 应收 , 78 M1.BUYERTAXAFTERPRICE 实收, 79 M1.BUYERTAXPRICE AS 税额 80 FROM PRODUCT_DEAL_MAIN_PA M1 81 WHERE m1.buyermksure=2 AND M1.DEALDATE BETWEEN TO_DATE('20170801','YYYYMMDD') AND TO_DATE('20170802','YYYYMMDD') 82 UNION 83 SELECT 2 bsid , 84 M1.NO, 85 M1.SELLERNAME, 86 M1.SELLERBP, 87 DECODE(M1.sellerMKSURE, 0, '没有签约', 1, '已经签约', 2, '预签约') 签约标志, 88 DECODE(M1.SELLERTAXVARITY, -1, '零税', 0, '含税', 1, '不含税') 含税标志, 89 DECODE(m1.sellerSPFLAG, 0, '', -1, '业务特殊修改', 1, '特殊修改', 2, 'bridge', 3, '单笔签约', 4, '预估未收', 5, '预估bridge', '') SPFLAG, 90 M1.SELLERFEEPRICERM AS 应收, 91 M1.SELLERTAXAFTERPRICE AS 实收, 92 M1.SELLERTAXPRICE AS 税额 93 FROM PRODUCT_DEAL_MAIN_PA M1 94 WHERE m1.sellermksure=2 AND M1.DEALDATE BETWEEN TO_DATE('20170801','YYYYMMDD') AND TO_DATE('20170802','YYYYMMDD') 95 96 97 ) 98 SELECT /*+ monitor */ MA.NO, 99 MA.BSID , 100 ma.签约标志 AS MA_签约标志, 101 decode(ma.SPFLAG,NULL,'正常',ma.SPFLAG) MA_SPFLAG, 102 ma.含税标志 AS MA_含税标志, 103 ma.应收 MA_应收, 104 MA.实收 MA_实收, 105 MA.税额 MA_税额, 106 DECODE( CS.BSFLAG,1,CS.CS_买_实收,CS.CS_卖_实收) cs_实收, 107 DECODE( CS.BSFLAG,1,CS_买_TAXAFTERPRICE,CS_卖_TAXAFTERPRICE) cs_含税, 108 DECODE(PA.含税标志,NULL,'NULL',PA.含税标志) PA_含税标志, 109 DECODE(PA.应收,NULL,'NULL',PA.应收) PA_应收, 110 DECODE(PA.实收,NULL,'NULL',PA.实收) PA_实收, 111 DECODE(PA.税额,NULL,'NULL',PA.税额) PA_税额 112 FROM MAIN1 MA 113 LEFT JOIN CSPLIT CS 114 ON MA.NO = CS.DEALNO 115 AND MA.BSID = CS.BSFLAG 116 LEFT JOIN PPA PA 117 ON MA.NO = PA.NO AND PA.BSID=MA.BSID 118 -- WHERE cs_实收 IS NULL AND cs_含税 IS NULL 119 ORDER BY MA.NO 120 ; 121 122 --a8646vy7mqm4u-- 123 ---select dbms_sqltune.report_sql_monitor from dual; 124 125 126 DECLARE 127 128 my_task_name VARCHAR2(1000); 129 BEGIN 130 my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK( 131 SQL_ID => 'a8646vy7mqm4u', 132 scope => 'COMPREHENSIVE', 133 time_limit => 60, 134 task_name => 'tunning_task_a8646vy7mqm4u', 135 description => 'Task to tune a query on ddw7j6yfnw0vz'); 136 end ; 137 / 138 139 --3.查看任务名 140 SELECT TASK_NAME FROM DBA_ADVISOR_LOG WHERE OWNER = 'PCISS'; 141 --5.查看sql tunning任务状态 142 SELECT status FROM USER_ADVISOR_TASKS WHERE task_name = 'tunning_task_a8646vy7mqm4u'; 143 --4.执行sql tuning任务 144 BEGIN 145 DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'tunning_task_a8646vy7mqm4u' ); 146 END; 147 / 148 --5.查看sql tunning任务状态 --COMPLETED 149 SELECT status FROM USER_ADVISOR_TASKS WHERE task_name = 'tunning_task_a8646vy7mqm4u'; 150 151 --6.展示sql tunning结果 152 SET LONG 10000 153 SET LONGCHUNKSIZE 1000 154 SET LINESIZE 100 155 SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('tunning_task_a8646vy7mqm4u') FROM DUAL; 156 --7.完成后删除sql tunning任务 157 EXEC DBMS_SQLTUNE.DROP_TUNING_TASK('test_sql_tuning'); 158 159 160 --8.其他 161 --sql tunning任务创建后，也可以修改参数 162 BEGIN 163 DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER( 164 task_name => 'test_sql_tuning', 165 parameter => 'TIME_LIMIT', value => 300); 166 END; 167 / 168 169 --查看SQL Tuning Advisor的进展（task执行很久） 170 col opname for a20 171 col ADVISOR_NAME for a20 172 SELECT SID,SERIAL#,USERNAME,OPNAME,ADVISOR_NAME,TARGET_DESC,START_TIME SOFAR, TOTALWORK 173 FROM V$ADVISOR_PROGRESS 174 WHERE USERNAME = 'TEST';
----调优计划
1 GENERAL INFORMATION SECTION 2 ------------------------------------------------------------------------------- 3 Tuning Task Name : tunning_task_a8646vy7mqm4u 4 Tuning Task Owner : PCISS 5 Workload Type : Single SQL Statement 6 Scope : COMPREHENSIVE 7 Time Limit(seconds): 60 8 Completion Status : COMPLETED 9 Started at : 08/04/2017 13:54:34 10 Completed at : 08/04/2017 13:54:40 11 12 ------------------------------------------------------------------------------- 13 Schema Name: PCISS 14 SQL ID : a8646vy7mqm4u 15 SQL Text : /* 16 CREATE TABLE his_product_deal_main0801 AS 17 select * FROM his_product_deal_main m 18 WHERE M.DEALDATE BETWEEN TO_DATE('20170801','YYYYMMDD') AND 19 TO_DATE('20170802','YYYYMMDD') ; 20 CREATE TABLE his_commision_split0801 AS 21 select * FROM his_base_deal_commision_split s 22 WHERE s.DEALDATE BETWEEN TO_DATE('20170801','YYYYMMDD') AND 23 TO_DATE('20170802','YYYYMMDD') ; 24 CREATE TABLE product_pa_0801 AS 25 select * FROM product_deal_main_pa pa 26 WHERE pa.DEALDATE BETWEEN TO_DATE('20170801','YYYYMMDD') AND 27 TO_DATE('20170802','YYYYMMDD') ; 28 SELECT * FROM his_product_deal_main0801 ; 29 SELECT * FROM his_commision_split0801 ; 30 SELECT * FROM product_pa_0801 ; 31 */ 32 WITH MAIN1 AS 33 (SELECT 1 bsid , 34 M1.NO, 35 M1.SELLERNAME, 36 M1.BUYERNAME, 37 M1.BUYERBP, 38 M1.SELLERBP, 39 DECODE(M1.buyerMKSURE, 0, '没有签约', 1, '已经签约', 2, '预签约') 40 签约标志, 41 DECODE(M1.buyerTAXVARITY, -1, '零税', 0, '含税', 1, '不含税') 42 含税标志, 43 DECODE(m1.BUYERSPFLAG, 0, '', -1, '业务特殊修改', 1, 44 '特殊修改', 2, 'bridge', 3, '单笔签约', 4, '预估未收', 5, '预估bridge', '') 45 SPFLAG, 46 M1.BUYERFEEPRICERM AS 应收 , 47 M1.BUYERTAXAFTERPRICE 实收, 48 M1.BUYERTAXPRICE AS 税额 49 FROM V_PRODUCT_DEAL_MAIN M1 50 WHERE m1.STATE =9 AND 51 M1.DEALDATE BETWEEN TO_DATE('20170801','YYYYMMDD') AND 52 TO_DATE('20170802','YYYYMMDD') 53 UNION ALL 54 SELECT 2 bsid , 55 M1.NO, 56 M1.SELLERNAME, 57 M1.BUYERNAME, 58 M1.BUYERBP, 59 M1.SELLERBP, 60 DECODE(M1.sellerMKSURE, 0, '没有签约', 1, '已经签约', 2, '预签约') 61 签约标志, 62 DECODE(M1.SELLERTAXVARITY, -1, '零税', 0, '含税', 1, '不含税') 63 含税标志, 64 DECODE(m1.sellERSPFLAG, 0, '', -1, '业务特殊修改', 1, '特殊修改', 65 2, 'bridge', 3, '单笔签约', 4, '预估未收', 5, '预估bridge', '') SPFLAG, 66 M1.SELLERFEEPRICERM AS 应收, 67 M1.SELLERTAXAFTERPRICE AS 实收, 68 M1.SELLERTAXPRICE AS 税额 69 FROM V_PRODUCT_DEAL_MAIN M1 70 WHERE m1.STATE =9 AND M1.DEALDATE BETWEEN 71 TO_DATE('20170801','YYYYMMDD') AND 72 TO_DATE('20170802','YYYYMMDD') 73 ), 74 CSPLIT AS 75 ( 76 SELECT BS.DEALNO, 77 BS.BSFLAG, 78 SUM(DECODE(BS.BSFLAG, 1, BS.AMOUNTCNY)) cs_买_实收, 79 SUM(DECODE(BS.BSFLAG, 1, BS.TAXAFTERPRICE)) 80 cs_买_TAXAFTERPRICE, 81 SUM(DECODE(BS.BSFLAG, 2, BS.AMOUNTCNY)) cs_卖_实收, 82 SUM(DECODE(BS.BSFLAG, 2, BS.TAXAFTERPRICE)) 83 cs_卖_TAXAFTERPRICE 84 FROM HIS_BASE_DEAL_COMMISION_SPLIT BS 85 WHERE bs.status=0 AND BS.DEALDATE BETWEEN 86 TO_DATE('20170801','YYYYMMDD') AND 87 TO_DATE('20170802','YYYYMMDD') 88 GROUP BY BS.DEALNO, BS.BSFLAG 89 ORDER BY BS.DEALNO 90 ),PPA AS ( 91 SELECT 1 bsid , 92 M1.NO, 93 M1.BUYERNAME, 94 M1.BUYERBP, 95 DECODE(M1.buyerMKSURE, 0, '没有签约', 1, '已经签约', 2, '预签约') 96 签约标志, 97 DECODE(M1.buyerTAXVARITY, -1, '零税', 0, '含税', 1, '不含税') 98 含税标志, 99 DECODE(m1.buyerSPFLAG, 0, '', -1, '业务特殊修改', 1, 100 '特殊修改', 2, 'bridge', 3, '单笔签约', 4, '预估未收', 5, '预估bridge', '') 101 SPFLAG, 102 M1.BUYERFEEPRICERM AS 应收 , 103 M1.BUYERTAXAFTERPRICE 实收, 104 M1.BUYERTAXPRICE AS 税额 105 FROM PRODUCT_DEAL_MAIN_PA M1 106 WHERE m1.buyermksure=2 AND M1.DEALDATE BETWEEN 107 TO_DATE('20170801','YYYYMMDD') AND 108 TO_DATE('20170802','YYYYMMDD') 109 UNION 110 SELECT 2 bsid , 111 M1.NO, 112 M1.SELLERNAME, 113 M1.SELLERBP, 114 DECODE(M1.sellerMKSURE, 0, '没有签约', 1, '已经签约', 2, '预签约') 115 签约标志, 116 DECODE(M1.SELLERTAXVARITY, -1, '零税', 0, '含税', 1, '不含税') 117 含税标志, 118 DECODE(m1.sellerSPFLAG, 0, '', -1, '业务特殊修改', 1, '特殊修改', 119 2, 'bridge', 3, '单笔签约', 4, '预估未收', 5, '预估bridge', '') SPFLAG, 120 M1.SELLERFEEPRICERM AS 应收, 121 M1.SELLERTAXAFTERPRICE AS 实收, 122 M1.SELLERTAXPRICE AS 税额 123 FROM PRODUCT_DEAL_MAIN_PA M1 124 WHERE m1.sellermksure=2 AND M1.DEALDATE BETWEEN 125 TO_DATE('20170801','YYYYMMDD') AND 126 TO_DATE('20170802','YYYYMMDD') 127 ) 128 SELECT /*+ monitor */ MA.NO, 129 MA.BSID , 130 ma.签约标志 AS MA_签约标志, 131 decode(ma.SPFLAG,NULL,'正常',ma.SPFLAG) MA_SPFLAG, 132 ma.含税标志 AS MA_含税标志, 133 ma.应收 MA_应收, 134 MA.实收 MA_实收, 135 MA.税额 MA_税额, 136 DECODE( CS.BSFLAG,1,CS.CS_买_实收,CS.CS_卖_实收) cs_实收, 137 DECODE( CS.BSFLAG,1,CS_买_TAXAFTERPRICE,CS_卖_TAXAFTERPRICE) 138 cs_含税, 139 DECODE(PA.含税标志,NULL,'NULL',PA.含税标志) PA_含税标志, 140 DECODE(PA.应收,NULL,'NULL',PA.应收) PA_应收, 141 DECODE(PA.实收,NULL,'NULL',PA.实收) PA_实收, 142 DECODE(PA.税额,NULL,'NULL',PA.税额) PA_税额 143 FROM MAIN1 MA 144 LEFT JOIN CSPLIT CS 145 ON MA.NO = CS.DEALNO 146 AND MA.BSID = CS.BSFLAG 147 LEFT JOIN PPA PA 148 ON MA.NO = PA.NO AND PA.BSID=MA.BSID 149 -- WHERE cs_实收 IS NULL AND cs_含税 IS NULL 150 ORDER BY MA.NO 151 152 ------------------------------------------------------------------------------- 153 FINDINGS SECTION (2 findings) 154 ------------------------------------------------------------------------------- 155 156 1- Index Finding (see explain plans section below) 157 -------------------------------------------------- 158 通过创建一个或多个索引可以改进此语句的执行计划。 159 160 Recommendation (estimated benefit: 90.53%) 161 ------------------------------------------ 162 - 考虑运行可以改进物理方案设计的访问指导或者创建推荐的索引。 163 create index PCISS.IDX$$_67460001 on PCISS.PRODUCT_DEAL_MAIN_PA("BUYERMKSUR 164 E","DEALDATE"); 165 166 - 考虑运行可以改进物理方案设计的访问指导或者创建推荐的索引。 167 create index PCISS.IDX$$_67460002 on PCISS.PRODUCT_DEAL_MAIN_PA("SELLERMKSU 168 RE","DEALDATE"); 169 170 - 考虑运行可以改进物理方案设计的访问指导或者创建推荐的索引。 171 create index PCISS.IDX$$_67460003 on PCISS.HIS_BASE_DEAL_COMMISION_SPLIT(TO 172 _NUMBER("STATUS"),"DEALDATE"); 173 174 Rationale 175 --------- 176 创建推荐的索引可以显著地改进此语句的执行计划。但是, 使用典型的 SQL 工作量运行 "访问指导" 177 可能比单个语句更可取。通过这种方法可以获得全面的索引建议案, 包括计算索引维护的开销和附加的空间消耗。 178 179 2- Alternative Plan Finding 180 --------------------------- 181 通过搜索系统的实时和历史性能数据找到了此语句的某些替代执行计划。 182 183 The following table lists these plans ranked by their average elapsed time. 184 See section "ALTERNATIVE PLANS SECTION" for detailed information on each 185 plan. 186 187 id plan hash last seen elapsed (s) origin note 188 -- ---------- -------------------- ------------ --------------- ---------------- 189 1 2571851218 2017-08-04/13:27:59 0.185 Cursor Cache 190 2 2840591098 2017-08-04/13:25:14 0.196 Cursor Cache original plan 191 192 Recommendation 193 -------------- 194 - 请考虑使用最佳平均用时为计划创建 SQL 计划基线。 195 execute dbms_sqltune.create_sql_plan_baseline(task_name => 196 'tunning_task_a8646vy7mqm4u', owner_name => 'PCISS', 197 plan_hash_value => 2571851218); 198 199 ------------------------------------------------------------------------------- 200 ADDITIONAL INFORMATION SECTION 201 ------------------------------------------------------------------------------- 202 - 优化程序不能合并位于执行计划的行 ID 21 处的视图。. 优化程序不能合并包含 "ORDER BY" 子句的视图, 除非此语句为 "DELETE" 203 或 "UPDATE", 并且父查询为此语句中的顶级查询。. 204 205 ------------------------------------------------------------------------------- 206 EXPLAIN PLANS SECTION 207 ------------------------------------------------------------------------------- 208 209 1- Original 210 ----------- 211 Plan hash value: 2840591098 212 213 -------------------------------------------------------------------------------------------------------------------- 214 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 215 -------------------------------------------------------------------------------------------------------------------- 216 | 0 | SELECT STATEMENT | | 920 | 192K| 8188 (1)| 00:01:39 | 217 | 1 | SORT ORDER BY | | 920 | 192K| 8188 (1)| 00:01:39 | 218 |* 2 | HASH JOIN RIGHT OUTER | | 920 | 192K| 8187 (1)| 00:01:39 | 219 | 3 | VIEW | | 2 | 128 | 118 (2)| 00:00:02 | 220 | 4 | SORT UNIQUE | | 2 | 153 | 118 (51)| 00:00:02 | 221 | 5 | UNION-ALL | | | | | | 222 |* 6 | TABLE ACCESS FULL | PRODUCT_DEAL_MAIN_PA | 1 | 77 | 58 (0)| 00:00:01 | 223 |* 7 | TABLE ACCESS FULL | PRODUCT_DEAL_MAIN_PA | 1 | 76 | 58 (0)| 00:00:01 | 224 |* 8 | HASH JOIN OUTER | | 920 | 134K| 8068 (1)| 00:01:37 | 225 | 9 | VIEW | | 920 | 71760 | 198 (0)| 00:00:03 | 226 | 10 | UNION-ALL | | | | | | 227 | 11 | VIEW | V_PRODUCT_DEAL_MAIN | 460 | 53820 | 99 (0)| 00:00:02 | 228 | 12 | UNION-ALL | | | | | | 229 |* 13 | TABLE ACCESS FULL | PRODUCT_DEAL_MAIN | 1 | 50 | 20 (0)| 00:00:01 | 230 |* 14 | TABLE ACCESS BY INDEX ROWID| HIS_PRODUCT_DEAL_MAIN | 459 | 23409 | 79 (0)| 00:00:01 | 231 |* 15 | INDEX RANGE SCAN | PK_MAIN_DEALDATE | 461 | | 4 (0)| 00:00:01 | 232 | 16 | VIEW | V_PRODUCT_DEAL_MAIN | 460 | 53820 | 99 (0)| 00:00:02 | 233 | 17 | UNION-ALL | | | | | | 234 |* 18 | TABLE ACCESS FULL | PRODUCT_DEAL_MAIN | 1 | 50 | 20 (0)| 00:00:01 | 235 |* 19 | TABLE ACCESS BY INDEX ROWID| HIS_PRODUCT_DEAL_MAIN | 459 | 23409 | 79 (0)| 00:00:01 | 236 |* 20 | INDEX RANGE SCAN | PK_MAIN_DEALDATE | 461 | | 4 (0)| 00:00:01 | 237 | 21 | VIEW | | 1327 | 95544 | 7870 (1)| 00:01:35 | 238 | 22 | SORT GROUP BY | | 1327 | 50426 | 7870 (1)| 00:01:35 | 239 |* 23 | TABLE ACCESS FULL | HIS_BASE_DEAL_COMMISION_SPLIT | 1327 | 50426 | 7869 (1)| 00:01:35 | 240 -------------------------------------------------------------------------------------------------------------------- 241 242 Predicate Information (identified by operation id): 243 --------------------------------------------------- 244 245 2 - access("PA"."BSID"(+)="MA"."BSID" AND "MA"."NO"="PA"."NO"(+)) 246 6 - filter("M1"."DEALDATE">=TO_DATE(' 2017-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND 247 "M1"."BUYERMKSURE"=2 AND "M1"."DEALDATE"<=TO_DATE(' 2017-08-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) 248 7 - filter("M1"."DEALDATE">=TO_DATE(' 2017-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND 249 "M1"."SELLERMKSURE"=2 AND "M1"."DEALDATE"<=TO_DATE(' 2017-08-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) 250 8 - access("MA"."BSID"=TO_NUMBER("CS"."BSFLAG"(+)) AND "MA"."NO"="CS"."DEALNO"(+)) 251 13 - filter("A"."STATE"=9 AND "A"."DEALDATE">=TO_DATE(' 2017-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') 252 AND "A"."DEALDATE"<=TO_DATE(' 2017-08-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) 253 14 - filter("B"."STATE"=9) 254 15 - access("B"."DEALDATE">=TO_DATE(' 2017-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND 255 "B"."DEALDATE"<=TO_DATE(' 2017-08-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) 256 18 - filter("A"."STATE"=9 AND "A"."DEALDATE">=TO_DATE(' 2017-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') 257 AND "A"."DEALDATE"<=TO_DATE(' 2017-08-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) 258 19 - filter("B"."STATE"=9) 259 20 - access("B"."DEALDATE">=TO_DATE(' 2017-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND 260 "B"."DEALDATE"<=TO_DATE(' 2017-08-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) 261 23 - filter("BS"."DEALDATE">=TO_DATE(' 2017-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND 262 TO_NUMBER("BS"."STATUS")=0 AND "BS"."DEALDATE"<=TO_DATE(' 2017-08-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) 263 264 2- Using New Indices 265 -------------------- 266 Plan hash value: 1939776337 267 268 -------------------------------------------------------------------------------------------------------------------- 269 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 270 -------------------------------------------------------------------------------------------------------------------- 271 | 0 | SELECT STATEMENT | | 920 | 192K| 775 (1)| 00:00:10 | 272 | 1 | SORT ORDER BY | | 920 | 192K| 775 (1)| 00:00:10 | 273 |* 2 | HASH JOIN RIGHT OUTER | | 920 | 192K| 774 (1)| 00:00:10 | 274 | 3 | VIEW | | 2 | 128 | 6 (34)| 00:00:01 | 275 | 4 | SORT UNIQUE | | 2 | 153 | 6 (67)| 00:00:01 | 276 | 5 | UNION-ALL | | | | | | 277 | 6 | TABLE ACCESS BY INDEX ROWID | PRODUCT_DEAL_MAIN_PA | 1 | 77 | 2 (0)| 00:00:01 | 278 |* 7 | INDEX RANGE SCAN | IDX$$_67460001 | 1 | | 1 (0)| 00:00:01 | 279 | 8 | TABLE ACCESS BY INDEX ROWID | PRODUCT_DEAL_MAIN_PA | 1 | 76 | 2 (0)| 00:00:01 | 280 |* 9 | INDEX RANGE SCAN | IDX$$_67460002 | 1 | | 1 (0)| 00:00:01 | 281 |* 10 | HASH JOIN OUTER | | 920 | 134K| 768 (1)| 00:00:10 | 282 | 11 | VIEW | | 920 | 71760 | 198 (0)| 00:00:03 | 283 | 12 | UNION-ALL | | | | | | 284 | 13 | VIEW | V_PRODUCT_DEAL_MAIN | 460 | 53820 | 99 (0)| 00:00:02 | 285 | 14 | UNION-ALL | | | | | | 286 |* 15 | TABLE ACCESS FULL | PRODUCT_DEAL_MAIN | 1 | 50 | 20 (0)| 00:00:01 | 287 |* 16 | TABLE ACCESS BY INDEX ROWID| HIS_PRODUCT_DEAL_MAIN | 459 | 23409 | 79 (0)| 00:00:01 | 288 |* 17 | INDEX RANGE SCAN | PK_MAIN_DEALDATE | 461 | | 4 (0)| 00:00:01 | 289 | 18 | VIEW | V_PRODUCT_DEAL_MAIN | 460 | 53820 | 99 (0)| 00:00:02 | 290 | 19 | UNION-ALL | | | | | | 291 |* 20 | TABLE ACCESS FULL | PRODUCT_DEAL_MAIN | 1 | 50 | 20 (0)| 00:00:01 | 292 |* 21 | TABLE ACCESS BY INDEX ROWID| HIS_PRODUCT_DEAL_MAIN | 459 | 23409 | 79 (0)| 00:00:01 | 293 |* 22 | INDEX RANGE SCAN | PK_MAIN_DEALDATE | 461 | | 4 (0)| 00:00:01 | 294 | 23 | VIEW | | 1327 | 95544 | 569 (1)| 00:00:07 | 295 | 24 | SORT GROUP BY | | 1327 | 50426 | 569 (1)| 00:00:07 | 296 | 25 | TABLE ACCESS BY INDEX ROWID | HIS_BASE_DEAL_COMMISION_SPLIT | 1327 | 50426 | 568 (0)| 00:00:07 | 297 |* 26 | INDEX RANGE SCAN | IDX$$_67460003 | 1323 | | 7 (0)| 00:00:01 | 298 -------------------------------------------------------------------------------------------------------------------- 299 300 Predicate Information (identified by operation id): 301 --------------------------------------------------- 302 303 2 - access("PA"."BSID"(+)="MA"."BSID" AND "MA"."NO"="PA"."NO"(+)) 304 7 - access("M1"."BUYERMKSURE"=2 AND "M1"."DEALDATE">=TO_DATE(' 2017-08-01 00:00:00', 'syyyy-mm-dd 305 hh24:mi:ss') AND "M1"."DEALDATE"<=TO_DATE(' 2017-08-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) 306 9 - access("M1"."SELLERMKSURE"=2 AND "M1"."DEALDATE">=TO_DATE(' 2017-08-01 00:00:00', 'syyyy-mm-dd 307 hh24:mi:ss') AND "M1"."DEALDATE"<=TO_DATE(' 2017-08-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) 308 10 - access("MA"."BSID"=TO_NUMBER("CS"."BSFLAG"(+)) AND "MA"."NO"="CS"."DEALNO"(+)) 309 15 - filter("A"."STATE"=9 AND "A"."DEALDATE">=TO_DATE(' 2017-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') 310 AND "A"."DEALDATE"<=TO_DATE(' 2017-08-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) 311 16 - filter("B"."STATE"=9) 312 17 - access("B"."DEALDATE">=TO_DATE(' 2017-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND 313 "B"."DEALDATE"<=TO_DATE(' 2017-08-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) 314 20 - filter("A"."STATE"=9 AND "A"."DEALDATE">=TO_DATE(' 2017-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') 315 AND "A"."DEALDATE"<=TO_DATE(' 2017-08-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) 316 21 - filter("B"."STATE"=9) 317 22 - access("B"."DEALDATE">=TO_DATE(' 2017-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND 318 "B"."DEALDATE"<=TO_DATE(' 2017-08-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) 319 26 - access("HIS_BASE_DEAL_COMMISION_SPLIT".<not feasible>) 320 321 ------------------------------------------------------------------------------- 322 ALTERNATIVE PLANS SECTION 323 ------------------------------------------------------------------------------- 324 325 Plan 1 326 ------ 327 328 Plan Origin :Cursor Cache 329 Plan Hash Value :2571851218 330 Executions :2 331 Elapsed Time :0.185 sec 332 CPU Time :0.184 sec 333 Buffer Gets :29579 334 Disk Reads :28965 335 Disk Writes :0 336 337 Notes: 338 1. Statistics shown are averaged over multiple executions. 339 340 -------------------------------------------------------------------------------------------------------------------- 341 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 342 -------------------------------------------------------------------------------------------------------------------- 343 | 0 | SELECT STATEMENT | | 920 | 192K| 8188 (1)| 00:01:39 | 344 | 1 | SORT ORDER BY | | 920 | 192K| 8188 (1)| 00:01:39 | 345 |* 2 | HASH JOIN OUTER | | 920 | 192K| 8187 (1)| 00:01:39 | 346 |* 3 | HASH JOIN OUTER | | 920 | 127K| 317 (1)| 00:00:04 | 347 | 4 | VIEW | | 920 | 71760 | 198 (0)| 00:00:03 | 348 | 5 | UNION-ALL | | | | | | 349 | 6 | VIEW | V_PRODUCT_DEAL_MAIN | 460 | 53820 | 99 (0)| 00:00:02 | 350 | 7 | UNION-ALL | | | | | | 351 |* 8 | TABLE ACCESS FULL | PRODUCT_DEAL_MAIN | 1 | 50 | 20 (0)| 00:00:01 | 352 |* 9 | TABLE ACCESS BY INDEX ROWID| HIS_PRODUCT_DEAL_MAIN | 459 | 23409 | 79 (0)| 00:00:01 | 353 |* 10 | INDEX RANGE SCAN | PK_MAIN_DEALDATE | 461 | | 4 (0)| 00:00:01 | 354 | 11 | VIEW | V_PRODUCT_DEAL_MAIN | 460 | 53820 | 99 (0)| 00:00:02 | 355 | 12 | UNION-ALL | | | | | | 356 |* 13 | TABLE ACCESS FULL | PRODUCT_DEAL_MAIN | 1 | 50 | 20 (0)| 00:00:01 | 357 |* 14 | TABLE ACCESS BY INDEX ROWID| HIS_PRODUCT_DEAL_MAIN | 459 | 23409 | 79 (0)| 00:00:01 | 358 |* 15 | INDEX RANGE SCAN | PK_MAIN_DEALDATE | 461 | | 4 (0)| 00:00:01 | 359 | 16 | VIEW | | 2 | 128 | 118 (2)| 00:00:02 | 360 | 17 | SORT UNIQUE | | 2 | 153 | 118 (51)| 00:00:02 | 361 | 18 | UNION-ALL | | | | | | 362 |* 19 | TABLE ACCESS FULL | PRODUCT_DEAL_MAIN_PA | 1 | 77 | 58 (0)| 00:00:01 | 363 |* 20 | TABLE ACCESS FULL | PRODUCT_DEAL_MAIN_PA | 1 | 76 | 58 (0)| 00:00:01 | 364 | 21 | VIEW | | 1327 | 95544 | 7870 (1)| 00:01:35 | 365 | 22 | SORT GROUP BY | | 1327 | 50426 | 7870 (1)| 00:01:35 | 366 |* 23 | TABLE ACCESS FULL | HIS_BASE_DEAL_COMMISION_SPLIT | 1327 | 50426 | 7869 (1)| 00:01:35 | 367 -------------------------------------------------------------------------------------------------------------------- 368 369 Predicate Information (identified by operation id): 370 --------------------------------------------------- 371 372 2 - access("MA"."BSID"=TO_NUMBER("CS"."BSFLAG"(+)) AND "MA"."NO"="CS"."DEALNO"(+)) 373 3 - access("PA"."BSID"(+)="MA"."BSID" AND "MA"."NO"="PA"."NO"(+)) 374 8 - filter("A"."STATE"=9 AND "A"."DEALDATE">=TO_DATE(' 2017-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') 375 AND "A"."DEALDATE"<=TO_DATE(' 2017-08-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) 376 9 - filter("B"."STATE"=9) 377 10 - access("B"."DEALDATE">=TO_DATE(' 2017-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND 378 "B"."DEALDATE"<=TO_DATE(' 2017-08-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) 379 13 - filter("A"."STATE"=9 AND "A"."DEALDATE">=TO_DATE(' 2017-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') 380 AND "A"."DEALDATE"<=TO_DATE(' 2017-08-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) 381 14 - filter("B"."STATE"=9) 382 15 - access("B"."DEALDATE">=TO_DATE(' 2017-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND 383 "B"."DEALDATE"<=TO_DATE(' 2017-08-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) 384 19 - filter("M1"."DEALDATE">=TO_DATE(' 2017-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND 385 "M1"."BUYERMKSURE"=2 AND "M1"."DEALDATE"<=TO_DATE(' 2017-08-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) 386 20 - filter("M1"."DEALDATE">=TO_DATE(' 2017-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND 387 "M1"."SELLERMKSURE"=2 AND "M1"."DEALDATE"<=TO_DATE(' 2017-08-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) 388 23 - filter("BS"."DEALDATE">=TO_DATE(' 2017-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND 389 TO_NUMBER("BS"."STATUS")=0 AND "BS"."DEALDATE"<=TO_DATE(' 2017-08-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) 390 391 Plan 2 392 ------ 393 394 Plan Origin :Cursor Cache 395 Plan Hash Value :2840591098 396 Executions :1 397 Elapsed Time :0.196 sec 398 CPU Time :0.195 sec 399 Buffer Gets :29585 400 Disk Reads :28965 401 Disk Writes :0 402 403 Notes: 404 1. Statistics shown are averaged over multiple executions. 405 2. The plan matches the original plan. 406 407 -------------------------------------------------------------------------------------------------------------------- 408 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 409 -------------------------------------------------------------------------------------------------------------------- 410 | 0 | SELECT STATEMENT | | 920 | 192K| 8188 (1)| 00:01:39 | 411 | 1 | SORT ORDER BY | | 920 | 192K| 8188 (1)| 00:01:39 | 412 |* 2 | HASH JOIN RIGHT OUTER | | 920 | 192K| 8187 (1)| 00:01:39 | 413 | 3 | VIEW | | 2 | 128 | 118 (2)| 00:00:02 | 414 | 4 | SORT UNIQUE | | 2 | 153 | 118 (51)| 00:00:02 | 415 | 5 | UNION-ALL | | | | | | 416 |* 6 | TABLE ACCESS FULL | PRODUCT_DEAL_MAIN_PA | 1 | 77 | 58 (0)| 00:00:01 | 417 |* 7 | TABLE ACCESS FULL | PRODUCT_DEAL_MAIN_PA | 1 | 76 | 58 (0)| 00:00:01 | 418 |* 8 | HASH JOIN OUTER | | 920 | 134K| 8068 (1)| 00:01:37 | 419 | 9 | VIEW | | 920 | 71760 | 198 (0)| 00:00:03 | 420 | 10 | UNION-ALL | | | | | | 421 | 11 | VIEW | V_PRODUCT_DEAL_MAIN | 460 | 53820 | 99 (0)| 00:00:02 | 422 | 12 | UNION-ALL | | | | | | 423 |* 13 | TABLE ACCESS FULL | PRODUCT_DEAL_MAIN | 1 | 50 | 20 (0)| 00:00:01 | 424 |* 14 | TABLE ACCESS BY INDEX ROWID| HIS_PRODUCT_DEAL_MAIN | 459 | 23409 | 79 (0)| 00:00:01 | 425 |* 15 | INDEX RANGE SCAN | PK_MAIN_DEALDATE | 461 | | 4 (0)| 00:00:01 | 426 | 16 | VIEW | V_PRODUCT_DEAL_MAIN | 460 | 53820 | 99 (0)| 00:00:02 | 427 | 17 | UNION-ALL | | | | | | 428 |* 18 | TABLE ACCESS FULL | PRODUCT_DEAL_MAIN | 1 | 50 | 20 (0)| 00:00:01 | 429 |* 19 | TABLE ACCESS BY INDEX ROWID| HIS_PRODUCT_DEAL_MAIN | 459 | 23409 | 79 (0)| 00:00:01 | 430 |* 20 | INDEX RANGE SCAN | PK_MAIN_DEALDATE | 461 | | 4 (0)| 00:00:01 | 431 | 21 | VIEW | | 1327 | 95544 | 7870 (1)| 00:01:35 | 432 | 22 | SORT GROUP BY | | 1327 | 50426 | 7870 (1)| 00:01:35 | 433 |* 23 | TABLE ACCESS FULL | HIS_BASE_DEAL_COMMISION_SPLIT | 1327 | 50426 | 7869 (1)| 00:01:35 | 434 -------------------------------------------------------------------------------------------------------------------- 435 436 Predicate Information (identified by operation id): 437 --------------------------------------------------- 438 439 2 - access("PA"."BSID"(+)="MA"."BSID" AND "MA"."NO"="PA"."NO"(+)) 440 6 - filter("M1"."DEALDATE">=TO_DATE(' 2017-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND 441 "M1"."BUYERMKSURE"=2 AND "M1"."DEALDATE"<=TO_DATE(' 2017-08-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) 442 7 - filter("M1"."DEALDATE">=TO_DATE(' 2017-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND 443 "M1"."SELLERMKSURE"=2 AND "M1"."DEALDATE"<=TO_DATE(' 2017-08-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) 444 8 - access("MA"."BSID"=TO_NUMBER("CS"."BSFLAG"(+)) AND "MA"."NO"="CS"."DEALNO"(+)) 445 13 - filter("A"."STATE"=9 AND "A"."DEALDATE">=TO_DATE(' 2017-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') 446 AND "A"."DEALDATE"<=TO_DATE(' 2017-08-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) 447 14 - filter("B"."STATE"=9) 448 15 - access("B"."DEALDATE">=TO_DATE(' 2017-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND 449 "B"."DEALDATE"<=TO_DATE(' 2017-08-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) 450 18 - filter("A"."STATE"=9 AND "A"."DEALDATE">=TO_DATE(' 2017-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') 451 AND "A"."DEALDATE"<=TO_DATE(' 2017-08-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) 452 19 - filter("B"."STATE"=9) 453 20 - access("B"."DEALDATE">=TO_DATE(' 2017-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND 454 "B"."DEALDATE"<=TO_DATE(' 2017-08-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) 455 23 - filter("BS"."DEALDATE">=TO_DATE(' 2017-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND 456 TO_NUMBER("BS"."STATUS")=0 AND "BS"."DEALDATE"<=TO_DATE(' 2017-08-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) 457 458 -------------------------------------------------------------------------------
