--创建 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')
/*
CREATE TABLE his_product_deal_main0801 AS
select * FROM his_product_deal_main m
WHERE M.DEALDATE BETWEEN TO_DATE('20170801','YYYYMMDD') AND TO_DATE('20170802','YYYYMMDD') ;
CREATE TABLE his_commision_split0801 AS
select * FROM his_base_deal_commision_split s
WHERE s.DEALDATE BETWEEN TO_DATE('20170801','YYYYMMDD') AND TO_DATE('20170802','YYYYMMDD') ;
CREATE TABLE product_pa_0801 AS
select * FROM product_deal_main_pa pa
WHERE pa.DEALDATE BETWEEN TO_DATE('20170801','YYYYMMDD') AND TO_DATE('20170802','YYYYMMDD') ; SELECT * FROM his_product_deal_main0801 ;
SELECT * FROM his_commision_split0801 ;
SELECT * FROM product_pa_0801 ;
*/ WITH MAIN1 AS
(SELECT 1 bsid ,
M1.NO,
M1.SELLERNAME,
M1.BUYERNAME,
M1.BUYERBP,
M1.SELLERBP,
DECODE(M1.buyerMKSURE, 0, '没有签约', 1, '已经签约', 2, '预签约') 签约标志,
DECODE(M1.buyerTAXVARITY, -1, '零税', 0, '含税', 1, '不含税') 含税标志,
DECODE(m1.BUYERSPFLAG, 0, '', -1, '业务特殊修改', 1, '特殊修改', 2, 'bridge', 3, '单笔签约', 4, '预估未收', 5, '预估bridge', '') SPFLAG,
M1.BUYERFEEPRICERM AS 应收 ,
M1.BUYERTAXAFTERPRICE 实收,
M1.BUYERTAXPRICE AS 税额
FROM V_PRODUCT_DEAL_MAIN M1
WHERE m1.STATE =9 AND
M1.DEALDATE BETWEEN TO_DATE('','YYYYMMDD') AND TO_DATE('','YYYYMMDD')
UNION ALL
SELECT 2 bsid ,
M1.NO,
M1.SELLERNAME,
M1.BUYERNAME,
M1.BUYERBP,
M1.SELLERBP,
DECODE(M1.sellerMKSURE, 0, '没有签约', 1, '已经签约', 2, '预签约') 签约标志,
DECODE(M1.SELLERTAXVARITY, -1, '零税', 0, '含税', 1, '不含税') 含税标志,
DECODE(m1.sellERSPFLAG, 0, '', -1, '业务特殊修改', 1, '特殊修改', 2, 'bridge', 3, '单笔签约', 4, '预估未收', 5, '预估bridge', '') SPFLAG,
M1.SELLERFEEPRICERM AS 应收,
M1.SELLERTAXAFTERPRICE AS 实收,
M1.SELLERTAXPRICE AS 税额
FROM V_PRODUCT_DEAL_MAIN M1
WHERE m1.STATE =9 AND M1.DEALDATE BETWEEN TO_DATE('','YYYYMMDD') AND TO_DATE('','YYYYMMDD') ),
CSPLIT AS
( SELECT BS.DEALNO,
BS.BSFLAG,
SUM(DECODE(BS.BSFLAG, 1, BS.AMOUNTCNY)) cs_买_实收,
SUM(DECODE(BS.BSFLAG, 1, BS.TAXAFTERPRICE)) cs_买_TAXAFTERPRICE,
SUM(DECODE(BS.BSFLAG, 2, BS.AMOUNTCNY)) cs_卖_实收,
SUM(DECODE(BS.BSFLAG, 2, BS.TAXAFTERPRICE)) cs_卖_TAXAFTERPRICE
FROM HIS_BASE_DEAL_COMMISION_SPLIT BS
WHERE bs.status=0 AND BS.DEALDATE BETWEEN TO_DATE('','YYYYMMDD') AND TO_DATE('','YYYYMMDD')
GROUP BY BS.DEALNO, BS.BSFLAG
ORDER BY BS.DEALNO ),PPA AS ( SELECT 1 bsid ,
M1.NO,
M1.BUYERNAME,
M1.BUYERBP,
DECODE(M1.buyerMKSURE, 0, '没有签约', 1, '已经签约', 2, '预签约') 签约标志,
DECODE(M1.buyerTAXVARITY, -1, '零税', 0, '含税', 1, '不含税') 含税标志,
DECODE(m1.buyerSPFLAG, 0, '', -1, '业务特殊修改', 1, '特殊修改', 2, 'bridge', 3, '单笔签约', 4, '预估未收', 5, '预估bridge', '') SPFLAG, M1.BUYERFEEPRICERM AS 应收 ,
M1.BUYERTAXAFTERPRICE 实收,
M1.BUYERTAXPRICE AS 税额
FROM PRODUCT_DEAL_MAIN_PA M1
WHERE m1.buyermksure=2 AND M1.DEALDATE BETWEEN TO_DATE('','YYYYMMDD') AND TO_DATE('','YYYYMMDD')
UNION
SELECT 2 bsid ,
M1.NO,
M1.SELLERNAME,
M1.SELLERBP,
DECODE(M1.sellerMKSURE, 0, '没有签约', 1, '已经签约', 2, '预签约') 签约标志,
DECODE(M1.SELLERTAXVARITY, -1, '零税', 0, '含税', 1, '不含税') 含税标志,
DECODE(m1.sellerSPFLAG, 0, '', -1, '业务特殊修改', 1, '特殊修改', 2, 'bridge', 3, '单笔签约', 4, '预估未收', 5, '预估bridge', '') SPFLAG,
M1.SELLERFEEPRICERM AS 应收,
M1.SELLERTAXAFTERPRICE AS 实收,
M1.SELLERTAXPRICE AS 税额
FROM PRODUCT_DEAL_MAIN_PA M1
WHERE m1.sellermksure=2 AND M1.DEALDATE BETWEEN TO_DATE('','YYYYMMDD') AND TO_DATE('','YYYYMMDD') )
SELECT /*+ monitor */ MA.NO,
MA.BSID ,
ma.签约标志 AS MA_签约标志,
decode(ma.SPFLAG,NULL,'正常',ma.SPFLAG) MA_SPFLAG,
ma.含税标志 AS MA_含税标志,
ma.应收 MA_应收,
MA.实收 MA_实收,
MA.税额 MA_税额,
DECODE( CS.BSFLAG,1,CS.CS_买_实收,CS.CS_卖_实收) cs_实收,
DECODE( CS.BSFLAG,1,CS_买_TAXAFTERPRICE,CS_卖_TAXAFTERPRICE) cs_含税,
DECODE(PA.含税标志,NULL,'NULL',PA.含税标志) PA_含税标志,
DECODE(PA.应收,NULL,'NULL',PA.应收) PA_应收,
DECODE(PA.实收,NULL,'NULL',PA.实收) PA_实收,
DECODE(PA.税额,NULL,'NULL',PA.税额) PA_税额
FROM MAIN1 MA
LEFT JOIN CSPLIT CS
ON MA.NO = CS.DEALNO
AND MA.BSID = CS.BSFLAG
LEFT JOIN PPA PA
ON MA.NO = PA.NO AND PA.BSID=MA.BSID
-- WHERE cs_实收 IS NULL AND cs_含税 IS NULL
ORDER BY MA.NO
; --a8646vy7mqm4u--
---select dbms_sqltune.report_sql_monitor from dual; DECLARE my_task_name VARCHAR2(1000);
BEGIN
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
SQL_ID => 'a8646vy7mqm4u',
scope => 'COMPREHENSIVE',
time_limit => 60,
task_name => 'tunning_task_a8646vy7mqm4u',
description => 'Task to tune a query on ddw7j6yfnw0vz');
end ;
/ --3.查看任务名
SELECT TASK_NAME FROM DBA_ADVISOR_LOG WHERE OWNER = 'PCISS';
--5.查看sql tunning任务状态
SELECT status FROM USER_ADVISOR_TASKS WHERE task_name = 'tunning_task_a8646vy7mqm4u';
--4.执行sql tuning任务
BEGIN
DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'tunning_task_a8646vy7mqm4u' );
END;
/
--5.查看sql tunning任务状态 --COMPLETED
SELECT status FROM USER_ADVISOR_TASKS WHERE task_name = 'tunning_task_a8646vy7mqm4u'; --6.展示sql tunning结果
SET LONG 10000
SET LONGCHUNKSIZE 1000
SET LINESIZE 100
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('tunning_task_a8646vy7mqm4u') FROM DUAL;
--7.完成后删除sql tunning任务
EXEC DBMS_SQLTUNE.DROP_TUNING_TASK('test_sql_tuning'); --8.其他
--sql tunning任务创建后，也可以修改参数
BEGIN
DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER(
task_name => 'test_sql_tuning',
parameter => 'TIME_LIMIT', value => 300);
END;
/ --查看SQL Tuning Advisor的进展（task执行很久）
col opname for a20
col ADVISOR_NAME for a20
SELECT SID,SERIAL#,USERNAME,OPNAME,ADVISOR_NAME,TARGET_DESC,START_TIME SOFAR, TOTALWORK
FROM V$ADVISOR_PROGRESS
WHERE USERNAME = 'TEST';
----调优计划
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : tunning_task_a8646vy7mqm4u
Tuning Task Owner : PCISS
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status : COMPLETED
Started at : 08/04/2017 13:54:34
Completed at : 08/04/2017 13:54:40 -------------------------------------------------------------------------------
Schema Name: PCISS
SQL ID : a8646vy7mqm4u
SQL Text : /*
CREATE TABLE his_product_deal_main0801 AS
select * FROM his_product_deal_main m
WHERE M.DEALDATE BETWEEN TO_DATE('20170801','YYYYMMDD') AND
TO_DATE('20170802','YYYYMMDD') ;
CREATE TABLE his_commision_split0801 AS
select * FROM his_base_deal_commision_split s
WHERE s.DEALDATE BETWEEN TO_DATE('20170801','YYYYMMDD') AND
TO_DATE('20170802','YYYYMMDD') ;
CREATE TABLE product_pa_0801 AS
select * FROM product_deal_main_pa pa
WHERE pa.DEALDATE BETWEEN TO_DATE('20170801','YYYYMMDD') AND
TO_DATE('20170802','YYYYMMDD') ;
SELECT * FROM his_product_deal_main0801 ;
SELECT * FROM his_commision_split0801 ;
SELECT * FROM product_pa_0801 ;
*/
WITH MAIN1 AS
(SELECT 1 bsid ,
M1.NO,
M1.SELLERNAME,
M1.BUYERNAME,
M1.BUYERBP,
M1.SELLERBP,
DECODE(M1.buyerMKSURE, 0, '没有签约', 1, '已经签约', 2, '预签约')
签约标志,
DECODE(M1.buyerTAXVARITY, -1, '零税', 0, '含税', 1, '不含税')
含税标志,
DECODE(m1.BUYERSPFLAG, 0, '', -1, '业务特殊修改', 1,
'特殊修改', 2, 'bridge', 3, '单笔签约', 4, '预估未收', 5, '预估bridge', '')
SPFLAG,
M1.BUYERFEEPRICERM AS 应收 ,
M1.BUYERTAXAFTERPRICE 实收,
M1.BUYERTAXPRICE AS 税额
FROM V_PRODUCT_DEAL_MAIN M1
WHERE m1.STATE =9 AND
M1.DEALDATE BETWEEN TO_DATE('20170801','YYYYMMDD') AND
TO_DATE('20170802','YYYYMMDD')
UNION ALL
SELECT 2 bsid ,
M1.NO,
M1.SELLERNAME,
M1.BUYERNAME,
M1.BUYERBP,
M1.SELLERBP,
DECODE(M1.sellerMKSURE, 0, '没有签约', 1, '已经签约', 2, '预签约')
签约标志,
DECODE(M1.SELLERTAXVARITY, -1, '零税', 0, '含税', 1, '不含税')
含税标志,
DECODE(m1.sellERSPFLAG, 0, '', -1, '业务特殊修改', 1, '特殊修改',
2, 'bridge', 3, '单笔签约', 4, '预估未收', 5, '预估bridge', '') SPFLAG,
M1.SELLERFEEPRICERM AS 应收,
M1.SELLERTAXAFTERPRICE AS 实收,
M1.SELLERTAXPRICE AS 税额
FROM V_PRODUCT_DEAL_MAIN M1
WHERE m1.STATE =9 AND M1.DEALDATE BETWEEN
TO_DATE('20170801','YYYYMMDD') AND
TO_DATE('20170802','YYYYMMDD')
),
CSPLIT AS
(
SELECT BS.DEALNO,
BS.BSFLAG,
SUM(DECODE(BS.BSFLAG, 1, BS.AMOUNTCNY)) cs_买_实收,
SUM(DECODE(BS.BSFLAG, 1, BS.TAXAFTERPRICE))
cs_买_TAXAFTERPRICE,
SUM(DECODE(BS.BSFLAG, 2, BS.AMOUNTCNY)) cs_卖_实收,
SUM(DECODE(BS.BSFLAG, 2, BS.TAXAFTERPRICE))
cs_卖_TAXAFTERPRICE
FROM HIS_BASE_DEAL_COMMISION_SPLIT BS
WHERE bs.status=0 AND BS.DEALDATE BETWEEN
TO_DATE('20170801','YYYYMMDD') AND
TO_DATE('20170802','YYYYMMDD')
GROUP BY BS.DEALNO, BS.BSFLAG
ORDER BY BS.DEALNO
),PPA AS (
SELECT 1 bsid ,
M1.NO,
M1.BUYERNAME,
M1.BUYERBP,
DECODE(M1.buyerMKSURE, 0, '没有签约', 1, '已经签约', 2, '预签约')
签约标志,
DECODE(M1.buyerTAXVARITY, -1, '零税', 0, '含税', 1, '不含税')
含税标志,
DECODE(m1.buyerSPFLAG, 0, '', -1, '业务特殊修改', 1,
'特殊修改', 2, 'bridge', 3, '单笔签约', 4, '预估未收', 5, '预估bridge', '')
SPFLAG,
M1.BUYERFEEPRICERM AS 应收 ,
M1.BUYERTAXAFTERPRICE 实收,
M1.BUYERTAXPRICE AS 税额
FROM PRODUCT_DEAL_MAIN_PA M1
WHERE m1.buyermksure=2 AND M1.DEALDATE BETWEEN
TO_DATE('20170801','YYYYMMDD') AND
TO_DATE('20170802','YYYYMMDD')
UNION
SELECT 2 bsid ,
M1.NO,
M1.SELLERNAME,
M1.SELLERBP,
DECODE(M1.sellerMKSURE, 0, '没有签约', 1, '已经签约', 2, '预签约')
签约标志,
DECODE(M1.SELLERTAXVARITY, -1, '零税', 0, '含税', 1, '不含税')
含税标志,
DECODE(m1.sellerSPFLAG, 0, '', -1, '业务特殊修改', 1, '特殊修改',
2, 'bridge', 3, '单笔签约', 4, '预估未收', 5, '预估bridge', '') SPFLAG,
M1.SELLERFEEPRICERM AS 应收,
M1.SELLERTAXAFTERPRICE AS 实收,
M1.SELLERTAXPRICE AS 税额
FROM PRODUCT_DEAL_MAIN_PA M1
WHERE m1.sellermksure=2 AND M1.DEALDATE BETWEEN
TO_DATE('20170801','YYYYMMDD') AND
TO_DATE('20170802','YYYYMMDD')
)
SELECT /*+ monitor */ MA.NO,
MA.BSID ,
ma.签约标志 AS MA_签约标志,
decode(ma.SPFLAG,NULL,'正常',ma.SPFLAG) MA_SPFLAG,
ma.含税标志 AS MA_含税标志,
ma.应收 MA_应收,
MA.实收 MA_实收,
MA.税额 MA_税额,
DECODE( CS.BSFLAG,1,CS.CS_买_实收,CS.CS_卖_实收) cs_实收,
DECODE( CS.BSFLAG,1,CS_买_TAXAFTERPRICE,CS_卖_TAXAFTERPRICE)
cs_含税,
DECODE(PA.含税标志,NULL,'NULL',PA.含税标志) PA_含税标志,
DECODE(PA.应收,NULL,'NULL',PA.应收) PA_应收,
DECODE(PA.实收,NULL,'NULL',PA.实收) PA_实收,
DECODE(PA.税额,NULL,'NULL',PA.税额) PA_税额
FROM MAIN1 MA
LEFT JOIN CSPLIT CS
ON MA.NO = CS.DEALNO
AND MA.BSID = CS.BSFLAG
LEFT JOIN PPA PA
ON MA.NO = PA.NO AND PA.BSID=MA.BSID
-- WHERE cs_实收 IS NULL AND cs_含税 IS NULL
ORDER BY MA.NO -------------------------------------------------------------------------------
FINDINGS SECTION (2 findings)
------------------------------------------------------------------------------- 1- Index Finding (see explain plans section below)
--------------------------------------------------
通过创建一个或多个索引可以改进此语句的执行计划。 Recommendation (estimated benefit: 90.53%)
------------------------------------------
- 考虑运行可以改进物理方案设计的访问指导或者创建推荐的索引。
create index PCISS.IDX$$_67460001 on PCISS.PRODUCT_DEAL_MAIN_PA("BUYERMKSUR
E","DEALDATE"); - 考虑运行可以改进物理方案设计的访问指导或者创建推荐的索引。
create index PCISS.IDX$$_67460002 on PCISS.PRODUCT_DEAL_MAIN_PA("SELLERMKSU
RE","DEALDATE"); - 考虑运行可以改进物理方案设计的访问指导或者创建推荐的索引。
create index PCISS.IDX$$_67460003 on PCISS.HIS_BASE_DEAL_COMMISION_SPLIT(TO
_NUMBER("STATUS"),"DEALDATE"); Rationale
---------
创建推荐的索引可以显著地改进此语句的执行计划。但是, 使用典型的 SQL 工作量运行 "访问指导"
可能比单个语句更可取。通过这种方法可以获得全面的索引建议案, 包括计算索引维护的开销和附加的空间消耗。 2- Alternative Plan Finding
---------------------------
通过搜索系统的实时和历史性能数据找到了此语句的某些替代执行计划。 The following table lists these plans ranked by their average elapsed time.
See section "ALTERNATIVE PLANS SECTION" for detailed information on each
plan. id plan hash last seen elapsed (s) origin note
-- ---------- -------------------- ------------ --------------- ----------------
1 2571851218 2017-08-04/13:27:59 0.185 Cursor Cache
2 2840591098 2017-08-04/13:25:14 0.196 Cursor Cache original plan Recommendation
--------------
- 请考虑使用最佳平均用时为计划创建 SQL 计划基线。
execute dbms_sqltune.create_sql_plan_baseline(task_name =>
'tunning_task_a8646vy7mqm4u', owner_name => 'PCISS',
plan_hash_value => 2571851218); -------------------------------------------------------------------------------
ADDITIONAL INFORMATION SECTION
-------------------------------------------------------------------------------
- 优化程序不能合并位于执行计划的行 ID 21 处的视图。. 优化程序不能合并包含 "ORDER BY" 子句的视图, 除非此语句为 "DELETE"
或 "UPDATE", 并且父查询为此语句中的*查询。. -------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
------------------------------------------------------------------------------- 1- Original
-----------
Plan hash value: 2840591098 --------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 920 | 192K| 8188 (1)| 00:01:39 |
| 1 | SORT ORDER BY | | 920 | 192K| 8188 (1)| 00:01:39 |
|* 2 | HASH JOIN RIGHT OUTER | | 920 | 192K| 8187 (1)| 00:01:39 |
| 3 | VIEW | | 2 | 128 | 118 (2)| 00:00:02 |
| 4 | SORT UNIQUE | | 2 | 153 | 118 (51)| 00:00:02 |
| 5 | UNION-ALL | | | | | |
|* 6 | TABLE ACCESS FULL | PRODUCT_DEAL_MAIN_PA | 1 | 77 | 58 (0)| 00:00:01 |
|* 7 | TABLE ACCESS FULL | PRODUCT_DEAL_MAIN_PA | 1 | 76 | 58 (0)| 00:00:01 |
|* 8 | HASH JOIN OUTER | | 920 | 134K| 8068 (1)| 00:01:37 |
| 9 | VIEW | | 920 | 71760 | 198 (0)| 00:00:03 |
| 10 | UNION-ALL | | | | | |
| 11 | VIEW | V_PRODUCT_DEAL_MAIN | 460 | 53820 | 99 (0)| 00:00:02 |
| 12 | UNION-ALL | | | | | |
|* 13 | TABLE ACCESS FULL | PRODUCT_DEAL_MAIN | 1 | 50 | 20 (0)| 00:00:01 |
|* 14 | TABLE ACCESS BY INDEX ROWID| HIS_PRODUCT_DEAL_MAIN | 459 | 23409 | 79 (0)| 00:00:01 |
|* 15 | INDEX RANGE SCAN | PK_MAIN_DEALDATE | 461 | | 4 (0)| 00:00:01 |
| 16 | VIEW | V_PRODUCT_DEAL_MAIN | 460 | 53820 | 99 (0)| 00:00:02 |
| 17 | UNION-ALL | | | | | |
|* 18 | TABLE ACCESS FULL | PRODUCT_DEAL_MAIN | 1 | 50 | 20 (0)| 00:00:01 |
|* 19 | TABLE ACCESS BY INDEX ROWID| HIS_PRODUCT_DEAL_MAIN | 459 | 23409 | 79 (0)| 00:00:01 |
|* 20 | INDEX RANGE SCAN | PK_MAIN_DEALDATE | 461 | | 4 (0)| 00:00:01 |
| 21 | VIEW | | 1327 | 95544 | 7870 (1)| 00:01:35 |
| 22 | SORT GROUP BY | | 1327 | 50426 | 7870 (1)| 00:01:35 |
|* 23 | TABLE ACCESS FULL | HIS_BASE_DEAL_COMMISION_SPLIT | 1327 | 50426 | 7869 (1)| 00:01:35 |
-------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id):
--------------------------------------------------- 2 - access("PA"."BSID"(+)="MA"."BSID" AND "MA"."NO"="PA"."NO"(+))
6 - filter("M1"."DEALDATE">=TO_DATE(' 2017-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"M1"."BUYERMKSURE"=2 AND "M1"."DEALDATE"<=TO_DATE(' 2017-08-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
7 - filter("M1"."DEALDATE">=TO_DATE(' 2017-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"M1"."SELLERMKSURE"=2 AND "M1"."DEALDATE"<=TO_DATE(' 2017-08-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
8 - access("MA"."BSID"=TO_NUMBER("CS"."BSFLAG"(+)) AND "MA"."NO"="CS"."DEALNO"(+))
13 - filter("A"."STATE"=9 AND "A"."DEALDATE">=TO_DATE(' 2017-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
AND "A"."DEALDATE"<=TO_DATE(' 2017-08-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
14 - filter("B"."STATE"=9)
15 - access("B"."DEALDATE">=TO_DATE(' 2017-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"B"."DEALDATE"<=TO_DATE(' 2017-08-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
18 - filter("A"."STATE"=9 AND "A"."DEALDATE">=TO_DATE(' 2017-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
AND "A"."DEALDATE"<=TO_DATE(' 2017-08-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
19 - filter("B"."STATE"=9)
20 - access("B"."DEALDATE">=TO_DATE(' 2017-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"B"."DEALDATE"<=TO_DATE(' 2017-08-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
23 - filter("BS"."DEALDATE">=TO_DATE(' 2017-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
TO_NUMBER("BS"."STATUS")=0 AND "BS"."DEALDATE"<=TO_DATE(' 2017-08-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) 2- Using New Indices
--------------------
Plan hash value: 1939776337 --------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 920 | 192K| 775 (1)| 00:00:10 |
| 1 | SORT ORDER BY | | 920 | 192K| 775 (1)| 00:00:10 |
|* 2 | HASH JOIN RIGHT OUTER | | 920 | 192K| 774 (1)| 00:00:10 |
| 3 | VIEW | | 2 | 128 | 6 (34)| 00:00:01 |
| 4 | SORT UNIQUE | | 2 | 153 | 6 (67)| 00:00:01 |
| 5 | UNION-ALL | | | | | |
| 6 | TABLE ACCESS BY INDEX ROWID | PRODUCT_DEAL_MAIN_PA | 1 | 77 | 2 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | IDX$$_67460001 | 1 | | 1 (0)| 00:00:01 |
| 8 | TABLE ACCESS BY INDEX ROWID | PRODUCT_DEAL_MAIN_PA | 1 | 76 | 2 (0)| 00:00:01 |
|* 9 | INDEX RANGE SCAN | IDX$$_67460002 | 1 | | 1 (0)| 00:00:01 |
|* 10 | HASH JOIN OUTER | | 920 | 134K| 768 (1)| 00:00:10 |
| 11 | VIEW | | 920 | 71760 | 198 (0)| 00:00:03 |
| 12 | UNION-ALL | | | | | |
| 13 | VIEW | V_PRODUCT_DEAL_MAIN | 460 | 53820 | 99 (0)| 00:00:02 |
| 14 | UNION-ALL | | | | | |
|* 15 | TABLE ACCESS FULL | PRODUCT_DEAL_MAIN | 1 | 50 | 20 (0)| 00:00:01 |
|* 16 | TABLE ACCESS BY INDEX ROWID| HIS_PRODUCT_DEAL_MAIN | 459 | 23409 | 79 (0)| 00:00:01 |
|* 17 | INDEX RANGE SCAN | PK_MAIN_DEALDATE | 461 | | 4 (0)| 00:00:01 |
| 18 | VIEW | V_PRODUCT_DEAL_MAIN | 460 | 53820 | 99 (0)| 00:00:02 |
| 19 | UNION-ALL | | | | | |
|* 20 | TABLE ACCESS FULL | PRODUCT_DEAL_MAIN | 1 | 50 | 20 (0)| 00:00:01 |
|* 21 | TABLE ACCESS BY INDEX ROWID| HIS_PRODUCT_DEAL_MAIN | 459 | 23409 | 79 (0)| 00:00:01 |
|* 22 | INDEX RANGE SCAN | PK_MAIN_DEALDATE | 461 | | 4 (0)| 00:00:01 |
| 23 | VIEW | | 1327 | 95544 | 569 (1)| 00:00:07 |
| 24 | SORT GROUP BY | | 1327 | 50426 | 569 (1)| 00:00:07 |
| 25 | TABLE ACCESS BY INDEX ROWID | HIS_BASE_DEAL_COMMISION_SPLIT | 1327 | 50426 | 568 (0)| 00:00:07 |
|* 26 | INDEX RANGE SCAN | IDX$$_67460003 | 1323 | | 7 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id):
--------------------------------------------------- 2 - access("PA"."BSID"(+)="MA"."BSID" AND "MA"."NO"="PA"."NO"(+))
7 - access("M1"."BUYERMKSURE"=2 AND "M1"."DEALDATE">=TO_DATE(' 2017-08-01 00:00:00', 'syyyy-mm-dd
hh24:mi:ss') AND "M1"."DEALDATE"<=TO_DATE(' 2017-08-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
9 - access("M1"."SELLERMKSURE"=2 AND "M1"."DEALDATE">=TO_DATE(' 2017-08-01 00:00:00', 'syyyy-mm-dd
hh24:mi:ss') AND "M1"."DEALDATE"<=TO_DATE(' 2017-08-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
10 - access("MA"."BSID"=TO_NUMBER("CS"."BSFLAG"(+)) AND "MA"."NO"="CS"."DEALNO"(+))
15 - filter("A"."STATE"=9 AND "A"."DEALDATE">=TO_DATE(' 2017-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
AND "A"."DEALDATE"<=TO_DATE(' 2017-08-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
16 - filter("B"."STATE"=9)
17 - access("B"."DEALDATE">=TO_DATE(' 2017-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"B"."DEALDATE"<=TO_DATE(' 2017-08-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
20 - filter("A"."STATE"=9 AND "A"."DEALDATE">=TO_DATE(' 2017-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
AND "A"."DEALDATE"<=TO_DATE(' 2017-08-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
21 - filter("B"."STATE"=9)
22 - access("B"."DEALDATE">=TO_DATE(' 2017-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"B"."DEALDATE"<=TO_DATE(' 2017-08-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
26 - access("HIS_BASE_DEAL_COMMISION_SPLIT".<not feasible>) -------------------------------------------------------------------------------
ALTERNATIVE PLANS SECTION
------------------------------------------------------------------------------- Plan 1
------ Plan Origin :Cursor Cache
Plan Hash Value :2571851218
Executions :2
Elapsed Time :0.185 sec
CPU Time :0.184 sec
Buffer Gets :29579
Disk Reads :28965
Disk Writes :0 Notes:
1. Statistics shown are averaged over multiple executions. --------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 920 | 192K| 8188 (1)| 00:01:39 |
| 1 | SORT ORDER BY | | 920 | 192K| 8188 (1)| 00:01:39 |
|* 2 | HASH JOIN OUTER | | 920 | 192K| 8187 (1)| 00:01:39 |
|* 3 | HASH JOIN OUTER | | 920 | 127K| 317 (1)| 00:00:04 |
| 4 | VIEW | | 920 | 71760 | 198 (0)| 00:00:03 |
| 5 | UNION-ALL | | | | | |
| 6 | VIEW | V_PRODUCT_DEAL_MAIN | 460 | 53820 | 99 (0)| 00:00:02 |
| 7 | UNION-ALL | | | | | |
|* 8 | TABLE ACCESS FULL | PRODUCT_DEAL_MAIN | 1 | 50 | 20 (0)| 00:00:01 |
|* 9 | TABLE ACCESS BY INDEX ROWID| HIS_PRODUCT_DEAL_MAIN | 459 | 23409 | 79 (0)| 00:00:01 |
|* 10 | INDEX RANGE SCAN | PK_MAIN_DEALDATE | 461 | | 4 (0)| 00:00:01 |
| 11 | VIEW | V_PRODUCT_DEAL_MAIN | 460 | 53820 | 99 (0)| 00:00:02 |
| 12 | UNION-ALL | | | | | |
|* 13 | TABLE ACCESS FULL | PRODUCT_DEAL_MAIN | 1 | 50 | 20 (0)| 00:00:01 |
|* 14 | TABLE ACCESS BY INDEX ROWID| HIS_PRODUCT_DEAL_MAIN | 459 | 23409 | 79 (0)| 00:00:01 |
|* 15 | INDEX RANGE SCAN | PK_MAIN_DEALDATE | 461 | | 4 (0)| 00:00:01 |
| 16 | VIEW | | 2 | 128 | 118 (2)| 00:00:02 |
| 17 | SORT UNIQUE | | 2 | 153 | 118 (51)| 00:00:02 |
| 18 | UNION-ALL | | | | | |
|* 19 | TABLE ACCESS FULL | PRODUCT_DEAL_MAIN_PA | 1 | 77 | 58 (0)| 00:00:01 |
|* 20 | TABLE ACCESS FULL | PRODUCT_DEAL_MAIN_PA | 1 | 76 | 58 (0)| 00:00:01 |
| 21 | VIEW | | 1327 | 95544 | 7870 (1)| 00:01:35 |
| 22 | SORT GROUP BY | | 1327 | 50426 | 7870 (1)| 00:01:35 |
|* 23 | TABLE ACCESS FULL | HIS_BASE_DEAL_COMMISION_SPLIT | 1327 | 50426 | 7869 (1)| 00:01:35 |
-------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id):
--------------------------------------------------- 2 - access("MA"."BSID"=TO_NUMBER("CS"."BSFLAG"(+)) AND "MA"."NO"="CS"."DEALNO"(+))
3 - access("PA"."BSID"(+)="MA"."BSID" AND "MA"."NO"="PA"."NO"(+))
8 - filter("A"."STATE"=9 AND "A"."DEALDATE">=TO_DATE(' 2017-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
AND "A"."DEALDATE"<=TO_DATE(' 2017-08-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
9 - filter("B"."STATE"=9)
10 - access("B"."DEALDATE">=TO_DATE(' 2017-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"B"."DEALDATE"<=TO_DATE(' 2017-08-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
13 - filter("A"."STATE"=9 AND "A"."DEALDATE">=TO_DATE(' 2017-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
AND "A"."DEALDATE"<=TO_DATE(' 2017-08-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
14 - filter("B"."STATE"=9)
15 - access("B"."DEALDATE">=TO_DATE(' 2017-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"B"."DEALDATE"<=TO_DATE(' 2017-08-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
19 - filter("M1"."DEALDATE">=TO_DATE(' 2017-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"M1"."BUYERMKSURE"=2 AND "M1"."DEALDATE"<=TO_DATE(' 2017-08-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
20 - filter("M1"."DEALDATE">=TO_DATE(' 2017-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"M1"."SELLERMKSURE"=2 AND "M1"."DEALDATE"<=TO_DATE(' 2017-08-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
23 - filter("BS"."DEALDATE">=TO_DATE(' 2017-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
TO_NUMBER("BS"."STATUS")=0 AND "BS"."DEALDATE"<=TO_DATE(' 2017-08-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) Plan 2
------ Plan Origin :Cursor Cache
Plan Hash Value :2840591098
Executions :1
Elapsed Time :0.196 sec
CPU Time :0.195 sec
Buffer Gets :29585
Disk Reads :28965
Disk Writes :0 Notes:
1. Statistics shown are averaged over multiple executions.
2. The plan matches the original plan. --------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 920 | 192K| 8188 (1)| 00:01:39 |
| 1 | SORT ORDER BY | | 920 | 192K| 8188 (1)| 00:01:39 |
|* 2 | HASH JOIN RIGHT OUTER | | 920 | 192K| 8187 (1)| 00:01:39 |
| 3 | VIEW | | 2 | 128 | 118 (2)| 00:00:02 |
| 4 | SORT UNIQUE | | 2 | 153 | 118 (51)| 00:00:02 |
| 5 | UNION-ALL | | | | | |
|* 6 | TABLE ACCESS FULL | PRODUCT_DEAL_MAIN_PA | 1 | 77 | 58 (0)| 00:00:01 |
|* 7 | TABLE ACCESS FULL | PRODUCT_DEAL_MAIN_PA | 1 | 76 | 58 (0)| 00:00:01 |
|* 8 | HASH JOIN OUTER | | 920 | 134K| 8068 (1)| 00:01:37 |
| 9 | VIEW | | 920 | 71760 | 198 (0)| 00:00:03 |
| 10 | UNION-ALL | | | | | |
| 11 | VIEW | V_PRODUCT_DEAL_MAIN | 460 | 53820 | 99 (0)| 00:00:02 |
| 12 | UNION-ALL | | | | | |
|* 13 | TABLE ACCESS FULL | PRODUCT_DEAL_MAIN | 1 | 50 | 20 (0)| 00:00:01 |
|* 14 | TABLE ACCESS BY INDEX ROWID| HIS_PRODUCT_DEAL_MAIN | 459 | 23409 | 79 (0)| 00:00:01 |
|* 15 | INDEX RANGE SCAN | PK_MAIN_DEALDATE | 461 | | 4 (0)| 00:00:01 |
| 16 | VIEW | V_PRODUCT_DEAL_MAIN | 460 | 53820 | 99 (0)| 00:00:02 |
| 17 | UNION-ALL | | | | | |
|* 18 | TABLE ACCESS FULL | PRODUCT_DEAL_MAIN | 1 | 50 | 20 (0)| 00:00:01 |
|* 19 | TABLE ACCESS BY INDEX ROWID| HIS_PRODUCT_DEAL_MAIN | 459 | 23409 | 79 (0)| 00:00:01 |
|* 20 | INDEX RANGE SCAN | PK_MAIN_DEALDATE | 461 | | 4 (0)| 00:00:01 |
| 21 | VIEW | | 1327 | 95544 | 7870 (1)| 00:01:35 |
| 22 | SORT GROUP BY | | 1327 | 50426 | 7870 (1)| 00:01:35 |
|* 23 | TABLE ACCESS FULL | HIS_BASE_DEAL_COMMISION_SPLIT | 1327 | 50426 | 7869 (1)| 00:01:35 |
-------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id):
--------------------------------------------------- 2 - access("PA"."BSID"(+)="MA"."BSID" AND "MA"."NO"="PA"."NO"(+))
6 - filter("M1"."DEALDATE">=TO_DATE(' 2017-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"M1"."BUYERMKSURE"=2 AND "M1"."DEALDATE"<=TO_DATE(' 2017-08-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
7 - filter("M1"."DEALDATE">=TO_DATE(' 2017-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"M1"."SELLERMKSURE"=2 AND "M1"."DEALDATE"<=TO_DATE(' 2017-08-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
8 - access("MA"."BSID"=TO_NUMBER("CS"."BSFLAG"(+)) AND "MA"."NO"="CS"."DEALNO"(+))
13 - filter("A"."STATE"=9 AND "A"."DEALDATE">=TO_DATE(' 2017-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
AND "A"."DEALDATE"<=TO_DATE(' 2017-08-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
14 - filter("B"."STATE"=9)
15 - access("B"."DEALDATE">=TO_DATE(' 2017-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"B"."DEALDATE"<=TO_DATE(' 2017-08-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
18 - filter("A"."STATE"=9 AND "A"."DEALDATE">=TO_DATE(' 2017-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
AND "A"."DEALDATE"<=TO_DATE(' 2017-08-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
19 - filter("B"."STATE"=9)
20 - access("B"."DEALDATE">=TO_DATE(' 2017-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"B"."DEALDATE"<=TO_DATE(' 2017-08-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
23 - filter("BS"."DEALDATE">=TO_DATE(' 2017-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
TO_NUMBER("BS"."STATUS")=0 AND "BS"."DEALDATE"<=TO_DATE(' 2017-08-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) -------------------------------------------------------------------------------