Tuning one SQL that is generated by report tool
来源：互联网 发布：网络教育专升本难吗 编辑：程序博客网 时间：2024/06/07 01:14
Recently, I encoutered one long running SQL which needs 7 minutes to complete.
The sql looks like:
select sum(nvl(T1589240.ACTL_GIV_AMT , 0)) as c1,
T1590424.ACN as c2, T1590424.NAME as c3,
… …
T1589324.PRMTN_SKID as c23
from OPT_ACCT_FDIM T1590424,
OPT_BUS_UNIT_FDIM T1588960,
OPT_CAL_MASTR_DIM T1589257 ,
OPT_PRMTN_FDIM T1589324,
OPT_BASLN_FCT T1589240
where ( T1588960.BUS = T1589240.BUS
and T1589240.WK_SKID = T1589257.CAL_MASTR_SKID
and T1589240.BUS = T1590424.BUS
and T1589240.BUS = T1589324.BUS
and T1589240.ACCT_SKID = T1590424.ACCT_SKID
and T1588960.BUN = :b_h and T1589240.PRMTN_SKID = T1589324.PRMTN_SKID
and T1589257.FYA = :b_f and T1590424.ACN = :b_c
and case when T1589324.CORP_PRMTN_TYPE_CODE = :b_ta then 'XXXX' else T1589324.CORP_PRMTN_TYPE_CODE end = 'YYYY' and T1590424.ACN is not null
and T1589324.PRMTN_LONG_NAME in
(select distinct T1589324.PRMTN_LONG_NAME as c1
from OPT_ACCT_FDIM T1590424 ,
OPT_BUS_UNIT_FDIM T1588960,
OPT_CAL_MASTR_DIM T1589257 ,
OPT_PRMTN_FDIM T1589324,
OPT_PRMTN_PROD_FLTR_LKP T1589598
where ( T1588960.BUS = T1589598.BUS
and T1589257.CAL_MASTR_SKID = T1589598.DATE_SKID
and T1589598.ACCT_PRMTN_SKID = T1590424.ACCT_SKID
and T1589324.PRMTN_SKID = T1589598.PRMTN_SKID
and T1589324.BUS = T1589598.BUS and T1588960.BUN = :b_h
and T1589257.FYA = :b_f and T1589598.BUS = T1590424.BUS and T1590424.ACN = :b_c
and case when T1589324.CORP_PRMTN_TYPE_CODE = :b_ta then 'XXXX' else T1589324.CORP_PRMTN_TYPE_CODE end = 'YYYY'
)
)
)
group by T1589324.PRMTN_SKID, T1589324.PRMTN_ID, T1589324.PRMTN_NAME, T1589324.SHPMT_END_DATE, T1589324.SHPMT_START_DATE, T1589324.PRMTN_STTUS_CODE, T1589324.APPRV_STTUS_CODE, T1589324.CMMNT_DESC, T1589324.PGM_START_DATE, T1589324.PGM_END_DATE, T1589324.CREAT_DATE, T1589324.APPRV_BY_DESC, T1589324.AUTO_UPDT_GTIN_IND, T1589324.PRMTN_STOP_DATE, T1589324.ACTVY_DETL_POP, T1589324.CNBLN_WK_CNT, T1589324.PRMTN_AVG_POP,…… T1590424.NAME, T1590424.CHANL_TYPE_DESC, T1590424.ACN
order by c23, c2
Please see BAD PLAN in the bottom.
From the execution plan, I don’t think we can get any useful information from it. It’s neither good nor bad.
Then I tested to convert IN operator to logically-equal EXISTS operator.
After rewriting, the SQL ran to end within 40s. And I got the plan. Please see GOOD PLAN in the bottom.
In the good plan, I noticed that CBO just parallized the execution in subquery and used normal table joins to join the outer tables with the result of the subquery.
In my opinion, with the evolution of Oracle, IN and EXISTS have less and less difference ,if there is any. We don’t have to worry about the big and small tables rules to determine to use IN or EXISTS. In most of cases, CBO translates both of them to normal table joins , such as HASH JOIN or NESTED LOOP.
But in some rare cases, especially in extremely complex SQLs, CBO can’t deal with them approriately and needs developer or DBA’s help to use IN or EXISTS explicitly.
It seemed that we were done. However, the reality was not.
The big headache was that the SQL was generated by reporting tool and couldn’t be modified and we didn’t know how to configurate the tool to switch between IN and EXISTS.
And no hint is allowed here because of the regulations.
I knew I have to materialize the subquery to get a better execution plan without hint and rewriting ,at least the rewring that can’t be implemented by tool automatically.
Here I rememebered the trick that ROWNUM keyword forced the materialization of subquery. And I thought reporting tool should give the users the capability to decide how many rows they want to see, which meant ROWNUM>=1 could be achieved by the tool.
This was my motive to test to add ROWNUM>=1 in the IN subquery and the result showed I was correct. The SQL now only needs 10s to complete.
At last, I have to say, if it’s possible, I would rewrite it with EXITST operator rather than materialize the subqery explicitly. Because in the former approach, we don’t ask CBO to do anything special. We leave CBO on its own choice. This is the more scalable way.
Leon
BAD PLAN:
-------------------------------------------------------------------- PLAN_TABLE_OUTPUT PLAN_TABLE_OUTPUT GOOD PlAN; -------------------------------------------------------- PLAN_TABLE_OUTPUT PLAN_TABLE_OUTPUT
| Id | Operation |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT |
| 1 | PX COORDINATOR |
| 2 | PX SEND QC (ORDER) |
| 3 | SORT GROUP BY |
| 4 | PX RECEIVE |
| 5 | PX SEND RANGE |
| 6 | SORT GROUP BY |
| 7 | VIEW |
| 8 | HASH UNIQUE |
| 9 | PX RECEIVE |
| 10 | PX SEND HASH |
| 11 | NESTED LOOPS |
| 12 | NESTED LOOPS |
| 13 | NESTED LOOPS |
| 14 | NESTED LOOPS |
--------------------------------------------------------------------
| 15 | NESTED LOOPS |
|* 16 | HASH JOIN |
| 17 | BUFFER SORT |
| 18 | PX RECEIVE |
| 19 | PX SEND BROADCAST |
| 20 | NESTED LOOPS |
| 21 | NESTED LOOPS |
| 22 | NESTED LOOPS |
| 23 | NESTED LOOPS |
| 24 | NESTED LOOPS |
|* 25 | INDEX RANGE SCAN |
|* 26 | TABLE ACCESS BY INDEX ROWID |
|* 27 | INDEX UNIQUE SCAN |
| 28 | PARTITION LIST INLIST |
|* 29 | TABLE ACCESS BY LOCAL INDEX ROWID|
|* 30 | INDEX RANGE SCAN |
| 31 | PARTITION LIST INLIST |
|* 32 | TABLE ACCESS BY LOCAL INDEX ROWID |
| 33 | BITMAP CONVERSION TO ROWIDS |
|* 34 | BITMAP INDEX SINGLE VALUE |
|* 35 | INDEX UNIQUE SCAN |
--------------------------------------------------------------------
|* 36 | TABLE ACCESS BY GLOBAL INDEX ROWID |
| 37 | PX BLOCK ITERATOR |
| 38 | TABLE ACCESS FULL |
|* 39 | TABLE ACCESS BY GLOBAL INDEX ROWID |
|* 40 | INDEX UNIQUE SCAN |
|* 41 | TABLE ACCESS BY GLOBAL INDEX ROWID |
|* 42 | INDEX UNIQUE SCAN |
|* 43 | TABLE ACCESS BY INDEX ROWID |
|* 44 | INDEX UNIQUE SCAN |
|* 45 | INDEX UNIQUE SCAN |
|* 46 | TABLE ACCESS BY INDEX ROWID |
--------------------------------------------------------------------
| Id | Operation |
--------------------------------------------------------
| 0 | SELECT STATEMENT |
| 1 | SORT GROUP BY |
|* 2 | FILTER |
| 3 | NESTED LOOPS |
| 4 | NESTED LOOPS |
| 5 | NESTED LOOPS |
| 6 | NESTED LOOPS |
| 7 | NESTED LOOPS |
|* 8 | INDEX RANGE SCAN |
| 9 | PARTITION LIST INLIST |
|* 10 | TABLE ACCESS BY LOCAL INDEX ROWID |
|* 11 | INDEX RANGE SCAN |
| 12 | PARTITION LIST INLIST |
|* 13 | TABLE ACCESS BY LOCAL INDEX ROWID |
| 14 | BITMAP CONVERSION TO ROWIDS |
--------------------------------------------------------
|* 15 | BITMAP INDEX SINGLE VALUE |
|* 16 | TABLE ACCESS BY GLOBAL INDEX ROWID |
|* 17 | INDEX UNIQUE SCAN |
|* 18 | INDEX UNIQUE SCAN |
|* 19 | TABLE ACCESS BY INDEX ROWID |
| 20 | PX COORDINATOR |
| 21 | PX SEND QC (RANDOM) |
| 22 | NESTED LOOPS |
| 23 | NESTED LOOPS |
| 24 | NESTED LOOPS |
|* 25 | HASH JOIN |
| 26 | BUFFER SORT |
| 27 | PX RECEIVE |
| 28 | PX SEND BROADCAST |
| 29 | PARTITION LIST INLIST |
|* 30 | TABLE ACCESS BY LOCAL INDEX ROWID|
|* 31 | INDEX RANGE SCAN |
| 32 | NESTED LOOPS |
| 33 | BUFFER SORT |
| 34 | PX RECEIVE |
| 35 | PX SEND BROADCAST |
--------------------------------------------------------
|* 36 | INDEX RANGE SCAN |
| 37 | PX BLOCK ITERATOR |
|* 38 | TABLE ACCESS FULL |
|* 39 | TABLE ACCESS BY GLOBAL INDEX ROWID |
|* 40 | INDEX UNIQUE SCAN |
|* 41 | INDEX UNIQUE SCAN |
|* 42 | TABLE ACCESS BY INDEX ROWID |
--------------------------------------------------------
- Tuning one SQL that is generated by report tool
- One sql performance tuning
- instances) and similar IP handles (that may additionally report that one particular person is filling out the prospect kind vari
- instances) and similar IP handles (that may additionally report that one particular person is filling out the prospect kind vari
- 【原创】Modularize Romized Native Code Table Generated By JCC Tool
- MySQL SQL Tuning:深入理解Order By
- 053-5 You run the SQL Tuning Advisor (STA) to tune a SQL statement that is part of a fixed SQL plan
- "report is being generated " 但就是不显示结果的原因
- Caused by: java.sql.SQLException: Generated keys not requested
- An attempt was made to set a report parameter that is not defined in this report
- Errors Generated by Make
- Lesson one hundred thirty nine Is that you,John?
- 71.Which arithmetic operations can be performed on a column by using a SQL function that is built in
- Oracle SQL Tuning Pocket Reference by Mark Gurry
- How to move the databases that are used by SharePoint Portal Server 2003 to a computer that is running SQL Server
- SQL Tuning
- SQL Tuning
- SQL Tuning
- 超级实用且不花哨的js代码大全
- placement new释疑
- 新年好！愿新年新收获！
- 通过VBS代码调用Excel中的宏
- DataSet和List 泛型之间互相转换 (转载, 作者写的很好)
- Tuning one SQL that is generated by report tool
- Java 移位操作
- Windows纸牌链接库Cards.dll的用法
- root用户登录Fedora 12
- 堆栈
- 26.Visitor（行为型模式）
- Oracle Date类型字段使用Hibernate插入后没有时分秒的解决
- Eclipse 启动参数&运行命令行参数 大全
- 斯坦福大学-公开课-编程方法学
