解释计划成本与执行时间 [英] Explain Plan Cost vs Execution Time
问题描述
之前，我发现执行计划中的成本"可以很好地指示相对执行时间.为什么这种情况不同?我认为执行计划具有相关性吗?我具体可以尝试提高v_test性能的什么?
Before, I have found the "Cost" in the execution plan to be a good indicator of relative execution time. Why is this case different? Am I a fool for thinking the execution plan has relevance? What specifically can I try to improve v_test performance?
谢谢.
使用Oracle 10g，我在下面定义了一个简单的查询视图
Using Oracle 10g I have a simple query view defined below
create or replace view v_test as
select distinct u.bo_id as bo_id, upper(trim(d.dept_id)) as dept_id
from
cust_bo_users u
join cust_bo_roles r on u.role_name=r.role_name
join cust_dept_roll_up_tbl d on
(r.region is null or trim(r.region)=trim(d.chrgback_reg)) and
(r.prod_id is null or trim(r.prod_id)=trim(d.prod_id)) and
(r.div_id is null or trim(r.div_id)=trim(d.div_id )) and
(r.clus_id is null or trim(r.clus_id )=trim( d.clus_id)) and
(r.prod_ln_id is null or trim(r.prod_ln_id)=trim(d.prod_ln_id)) and
(r.dept_id is null or trim(r.dept_id)=trim(d.dept_id))
定义为替换以下视图
create or replace view v_bo_secured_detail
select distinct Q.BO_ID, Q.DEPT_ID
from (select U.BO_ID BO_ID, UPPER(trim(D.DEPT_ID)) DEPT_ID
from CUST_BO_USERS U, CUST_BO_ROLES R, CUST_DEPT_ROLL_UP_TBL D
where U.ROLE_NAME = R.ROLE_NAME and
R.ROLE_LEVEL = 'REGION' and
trim(R.REGION) = UPPER(trim(D.CHRGBACK_REG))
union all
select U.BO_ID BO_ID, UPPER(trim(D.DEPT_ID)) DEPT_ID
from CUST_BO_USERS U, CUST_BO_ROLES R, CUST_DEPT_ROLL_UP_TBL D
where U.ROLE_NAME = R.ROLE_NAME and
R.ROLE_LEVEL = 'RG_PROD' and
trim(R.REGION) = UPPER(trim(D.CHRGBACK_REG)) and
trim(R.PROD_ID) = UPPER(trim(D.PROD_ID))
union all
select U.BO_ID BO_ID, UPPER(trim(D.DEPT_ID)) DEPT_ID
from CUST_BO_USERS U, CUST_BO_ROLES R, CUST_DEPT_ROLL_UP_TBL D
where U.ROLE_NAME = R.ROLE_NAME and
R.ROLE_LEVEL = 'PROD' and
trim(R.PROD_ID) = UPPER(trim(D.PROD_ID))
union all
select U.BO_ID BO_ID, UPPER(trim(D.DEPT_ID)) DEPT_ID
from CUST_BO_USERS U, CUST_BO_ROLES R, CUST_DEPT_ROLL_UP_TBL D
where U.ROLE_NAME = R.ROLE_NAME and
R.ROLE_LEVEL = 'DIV' and
trim(R.DIV_ID) = UPPER(trim(D.DIV_ID))
union all
select U.BO_ID BO_ID, UPPER(trim(D.DEPT_ID)) DEPT_ID
from CUST_BO_USERS U, CUST_BO_ROLES R, CUST_DEPT_ROLL_UP_TBL D
where U.ROLE_NAME = R.ROLE_NAME and
R.ROLE_LEVEL = 'RG_DIV' and
trim(R.REGION) = UPPER(trim(D.CHRGBACK_REG)) and
trim(R.DIV_ID) = UPPER(trim(D.DIV_ID))
union all
select U.BO_ID BO_ID, UPPER(trim(D.DEPT_ID)) DEPT_ID
from CUST_BO_USERS U, CUST_BO_ROLES R, CUST_DEPT_ROLL_UP_TBL D
where U.ROLE_NAME = R.ROLE_NAME and
R.ROLE_LEVEL = 'CLUS' and
trim(R.CLUS_ID) = UPPER(trim(D.CLUS_ID))
union all
select U.BO_ID BO_ID, UPPER(trim(D.DEPT_ID)) DEPT_ID
from CUST_BO_USERS U, CUST_BO_ROLES R, CUST_DEPT_ROLL_UP_TBL D
where U.ROLE_NAME = R.ROLE_NAME and
R.ROLE_LEVEL = 'RG_CLUS' and
trim(R.REGION) = UPPER(trim(D.CHRGBACK_REG)) and
trim(R.CLUS_ID) = UPPER(trim(D.CLUS_ID))
union all
select U.BO_ID BO_ID, UPPER(trim(D.DEPT_ID)) DEPT_ID
from CUST_BO_USERS U, CUST_BO_ROLES R, CUST_DEPT_ROLL_UP_TBL D
where U.ROLE_NAME = R.ROLE_NAME and
R.ROLE_LEVEL = 'PROD_LN' and
trim(R.PROD_LN_ID) = UPPER(trim(D.PROD_LN_ID))
union all
select U.BO_ID BO_ID, UPPER(trim(R.DEPT_ID)) DEPT_ID
from CUST_BO_USERS U, CUST_BO_ROLES R
where U.ROLE_NAME = R.ROLE_NAME and
R.ROLE_LEVEL = 'DEPT') Q
旨在消除对ROLE_LEVEL列的依赖.
with the goal of removing the dependency on the ROLE_LEVEL column.
简单起见，v_test的执行计划明显低于v_bo_secured_detail
The execution plan for v_test is significantly lower than v_bo_secured_detail for simple
select * from <view> where bo_id='value'
查询.并且在实际查询中使用时会大大降低
queries. And is significantly lower when used in a real world query
select CT_REPORT.RPT_KEY,
CT_REPORT_ENTRY.RPE_KEY,
CT_REPORT_ENTRY.CUSTOM16,
Exp_Sub_Type.value,
min(CT_REPORT_PAYMENT_CONF.PAY_DATE),
CT_REPORT.PAID_DATE
from CT_REPORT,
<VIEW> SD,
CT_REPORT_ENTRY,
CT_LIST_ITEM_LANG Exp_Sub_Type,
CT_REPORT_PAYMENT_CONF,
CT_STATUS_LANG Payment_Status
where (CT_REPORT_ENTRY.RPT_KEY = CT_REPORT.RPT_KEY) and
(Payment_Status.STAT_KEY = CT_REPORT.PAY_KEY) and
(Exp_Sub_Type.LI_KEY = CT_REPORT_ENTRY.CUSTOM9 and Exp_Sub_Type.LANG_CODE = 'en') and
(CT_REPORT.RPT_KEY = CT_REPORT_PAYMENT_CONF.RPT_KEY) and
(SD.BO_ID = 'JZHU9') and
(SD.DEPT_ID = UPPER(CT_REPORT_ENTRY.CUSTOM5)) and
(Payment_Status.name = 'Payment Confirmed' and (Payment_Status.LANG_CODE = 'en') and
CT_REPORT.PAID_DATE > to_date('01/01/2008', 'mm/dd/yyyy') and Exp_Sub_Type.value != 'Korea')
group by CT_REPORT.RPT_KEY,
CT_REPORT_ENTRY.RPE_KEY,
CT_REPORT_ENTRY.CUSTOM16,
Exp_Sub_Type.value,
CT_REPORT.PAID_DATE
执行时间完全不同. v_test视图需要15个小时，而v_bo_secured_detail则需要几秒钟.
The execution times are WILDLY different. The v_test view taking 15 hours, and the v_bo_secured_detail taking a few seconds.
谢谢所有回答
这是我要记住的一个.表达式的理论和数学与基于硬件的执行的现实相遇的地方.哎呀.
This is one to remember for me. The places where the theory and mathematics of the expressions meets the reality of hardware based execution. Ouch.
推荐答案
As the Oracle documentation says, the cost is the estimated cost relative to a particular execution plan. When you tweak the query, the particular execution plan that costs are calculated relative to can change. Sometimes dramatically.
v_test性能的问题在于，除了执行嵌套循环外，Oracle无法想到执行它的方法，对于每个cust_bo_roles，扫描所有cust_dept_roll_up_tbl以找到匹配项.如果表的大小为n和m，则需要花费n * m的时间，这对于大型表来说很慢.相比之下，设置v_bo_secured_detail使其成为一系列查询，每个查询都可以通过其他某种机制来完成. (Oracle可能会使用一个数字，包括使用索引，动态建立哈希表或对数据集进行排序并将它们合并.这些操作都是O(n * log(n))或更好.)快速查询很快.
The problem with v_test's performance is that Oracle can think of no way to execute it other than performing a nested loop, for each cust_bo_roles, scan all of cust_dept_roll_up_tbl to find a match. If the table are of size n and m, this takes n*m time, which is slow for large tables. By contrast v_bo_secured_detail is set up so that it is a series of queries, each of which can be done through some other mechanism. (Oracle has a number it may use, including using an index, building a hash on the fly, or sorting the datasets and merging them. These operations are all O(n*log(n)) or better.) A small series of fast queries is fast.
痛苦的是，如果您希望此查询快速进行，则需要像上一个查询一样将其分解.
As painful as it is, if you want this query to be fast then you need to break it out like the previous query did.
这篇关于解释计划成本与执行时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
