Oracle存储过程的执行计划
我有一个问题,如何在一个存储过程的执行计划创建,如果它有一个IF ELSE语句,其中IF或执行ELSE块上的IN参数的值基于并且块中的查询从相同的表中获取相同的值,但在where子句中具有不同的列。Oracle存储过程的执行计划
据我所知,每个存储过程只有一个执行计划。如果Java应用程序使用普通JDBC调用存储过程,则在第一次调用该过程时创建执行计划,并使用块(IF)执行以创建执行计划。
当执行ELSE块,它使用相同的执行计划并且因此执行将是缓慢的下一次。
我问这个问题,因为我呼吁类似的存储过程和执行的时间是执行基于什么样的价值我传递和块很大的不同。
如果是这种情况,可以采取哪些措施来提高性能。应该写一个独立的程序而不是使用IF ELSE。
PFB存储的过程代码:
PACKAGE COP00134_006_1 AS
TYPE t_cur_type IS REF CURSOR;
PROCEDURE GetConsignmentDetails(
sPInputList IN VARCHAR2,
sPInputType IN VARCHAR2,
cPCon OUT t_cur_type,
cPStatus OUT t_cur_type,
sPError OUT VARCHAR2);
END COP00134_006_1;
实现:
PACKAGE BODY COP00134_006_1 AS
PROCEDURE GetConsignmentDetails(
sPInputList IN VARCHAR2,
sPInputType IN VARCHAR2,
cPCon OUT t_cur_type,
cPStatus OUT t_cur_type,
sPError OUT VARCHAR2)
IS
BEGIN
sPError := '0';
IF sPInputType = 'CON' THEN
OPEN cPCon FOR
SELECT
con_id
,con_legacy_id
,con_create_td
,con_pickup_lt
,con_deliv_due_lt
,CON_DELIV_END_LT
,SRP_ID_AREA_DEST
,CON_CLNT_REF_TX
,BUL_CSYS_ID_ORIG
,BUL_ID_ORIG
,BUL_NM_ORIG
,BUL_NM_DEST
,cpn_oa_town_nm_r
,cpn_oa_town_nm_d
,cou_iso_id_orig
,Cou_Nm_Orig
,cou_iso_id_dest
,Cou_Nm_Dest
,PCE_QT
,DUPLICATES
,COS_SIGN_NM FROM (
SELECT /*+ cardinality (b 3) */
co.con_id
,co.con_legacy_id
,co.con_create_td
,co.con_pickup_lt
,co.con_deliv_due_lt
,co.CON_DELIV_END_LT
,co.SRP_ID_AREA_DEST
,co.CON_CLNT_REF_TX
,co.BUL_CSYS_ID_ORIG
,blOrig.BUL_ID as BUL_ID_ORIG
,blOrig.BUL_NM as BUL_NM_ORIG
,blDest.BUL_NM as BUL_NM_DEST
,cnr.cpn_oa_town_nm as cpn_oa_town_nm_r
,cnd.cpn_oa_town_nm as cpn_oa_town_nm_d
,co.cou_iso_id_orig
,cuOrig.Cou_nm as Cou_Nm_Orig
,co.cou_iso_id_dest
,cuDest.Cou_nm as Cou_Nm_Dest
,(select count(*) from CORPCV01 PC WHERE PC.CON_ID = CO.CON_ID) PCE_QT
,CASE WHEN COUNT(*) OVER(PARTITION BY CON_LEGACY_ID) > 1 THEN 'TRUE' ELSE 'FALSE' END AS DUPLICATES
,cs.COS_SIGN_NM, ROW_NUMBER() OVER(PARTITION BY CS.CON_ID ORDER BY COS_EVENT_TD) SEQ_NR
FROM corcov01 co
INNER JOIN corcnv01 cnr
ON cnr.con_id = co.con_id
AND cnr.cpn_type_cd = 'R'
LEFT OUTER JOIN corcnv01 cnd
ON cnd.con_id = co.con_id
AND cnd.cpn_type_cd = 'D'
INNER JOIN ncrcuv01 cuOrig
ON cuOrig.COU_ISO_ID = co.COU_ISO_ID_ORIG
AND TRUNC(CURRENT_DATE) BETWEEN cuOrig.COU_EFFECT_DT AND cuOrig.COU_EFFECT_TO_DT
INNER JOIN ncrcuv01 cuDest
ON cuDest.COU_ISO_ID = co.COU_ISO_ID_DEST
AND TRUNC(CURRENT_DATE) BETWEEN cuDest.COU_EFFECT_DT AND cuDest.COU_EFFECT_TO_DT
LEFT OUTER JOIN ncrblv01 blOrig
ON blOrig.BUL_CSYS_ID = co.BUL_CSYS_ID_ORIG
AND TRUNC(CURRENT_DATE) BETWEEN blOrig.BUL_EFFECT_DT AND blOrig.BUL_EFFECT_TO_DT
LEFT OUTER JOIN ncrblv01 blDest
ON blDest.BUL_CSYS_ID = co.BUL_CSYS_ID_DEST
AND TRUNC(CURRENT_DATE) BETWEEN blDest.BUL_EFFECT_DT AND blDest.BUL_EFFECT_TO_DT
LEFT OUTER JOIN corcsv01 cs
ON co.con_id = cs.con_id
AND cs.cos_sign_nm is not null
and cs.cos_delete_in = 'N'
WHERE co.con_Legacy_Id IN (select dataItem from TABLE(ZYADMIN.ZYP90008.GetListFromCSV(sPInputList)) b)
)WHERE SEQ_NR = 1;
OPEN cPStatus FOR
SELECT csv.CON_ID AS CON_ID_COS
,csv.XSF_ID
,csv.XSS_ID
,csv.XSG_ID
,csv.XSD_ID
,ndv.XSX_ID
,ndv.xsd_customer_ds
,BUL_CSYS_ID_OCC
,nlv.BUL_NM as BUL_NM_OCC
,csv.COS_EVENT_LT
,ndv.XSD_SEVERITY_CD
,qb.QLA_DS
FROM corcsv01 csv
INNER JOIN corcov01 co
ON csv.con_id = co.con_id
INNER JOIN ncrsdv01 ndv
ON ndv.XSF_ID=csv.XSF_ID
AND ndv.XSS_ID=csv.XSS_ID
AND ndv.XSG_ID=csv.XSG_ID
AND ndv.XSD_ID=csv.XSD_ID
AND TRUNC(CURRENT_DATE) BETWEEN ndv.XSD_EFFECT_DT AND ndv.XSD_EFFECT_TO_DT
INNER JOIN ncrksv01 ks
ON ndv.XSX_ID= ks.XSX_ID
AND TRUNC(CURRENT_DATE) BETWEEN ks.SKT_EFFECT_DT AND ks.SKT_EFFECT_TO_DT
AND ks.SCA_ID = 'WEB'
LEFT OUTER JOIN ncrqav01 qa
ON qa.XSX_ID = ndv.XSX_ID
AND qa.APP_ID = 'EXCO'
AND TRUNC(CURRENT_DATE) BETWEEN QAS_EFFECT_DT AND QAS_EFFECT_TO_DT
LEFT OUTER JOIN ncrqbv01 qb
ON qa.QLA_ID = qb.QLA_ID
AND qa.APP_ID = qb.APP_ID
AND TRUNC(CURRENT_DATE) BETWEEN QLA_EFFECT_DT AND QLA_EFFECT_TO_DT
INNER JOIN ncrblv01 nlv
ON nlv.BUL_CSYS_ID=csv.BUL_CSYS_ID_OCC
AND TRUNC(CURRENT_DATE)BETWEEN nlv.BUL_EFFECT_DT AND nlv.BUL_EFFECT_TO_DT
WHERE co.con_Legacy_Id IN (select dataItem from TABLE(ZYADMIN.ZYP90008.GetListFromCSV(sPInputList)) b);
ELSE
OPEN cPCon FOR
SELECT
con_id
,con_legacy_id
,con_create_td
,con_pickup_lt
,con_deliv_due_lt
,CON_DELIV_END_LT
,SRP_ID_AREA_DEST
,CON_CLNT_REF_TX
,BUL_CSYS_ID_ORIG
,BUL_ID_ORIG
,BUL_NM_ORIG
,BUL_NM_DEST
,cpn_oa_town_nm_r
,cpn_oa_town_nm_d
,cou_iso_id_orig
,Cou_Nm_Orig
,cou_iso_id_dest
,Cou_Nm_Dest
,PCE_QT
,DUPLICATES
,COS_SIGN_NM FROM (
SELECT /*+ cardinality (b 3) */
co.con_id
,co.con_legacy_id
,co.con_create_td
,co.con_pickup_lt
,co.con_deliv_due_lt
,co.CON_DELIV_END_LT
,co.SRP_ID_AREA_DEST
,co.CON_CLNT_REF_TX
,co.BUL_CSYS_ID_ORIG
,blOrig.BUL_ID as BUL_ID_ORIG
,blOrig.BUL_NM as BUL_NM_ORIG
,blDest.BUL_NM as BUL_NM_DEST
,cnr.cpn_oa_town_nm as cpn_oa_town_nm_r
,cnd.cpn_oa_town_nm as cpn_oa_town_nm_d
,co.cou_iso_id_orig
,cuOrig.Cou_nm as Cou_Nm_Orig
,co.cou_iso_id_dest
,cuDest.Cou_nm as Cou_Nm_Dest
,(select count(*) from CORPCV01 PC WHERE PC.CON_ID = CO.CON_ID) PCE_QT
,'FALSE' AS DUPLICATES
,cs.COS_SIGN_NM, ROW_NUMBER() OVER(PARTITION BY CS.CON_ID ORDER BY COS_EVENT_TD) SEQ_NR
FROM corcov01 co
INNER JOIN corcnv01 cnr
ON cnr.con_id = co.con_id
AND cnr.cpn_type_cd = 'R'
LEFT OUTER JOIN corcnv01 cnd
ON cnd.con_id = co.con_id
AND cnd.cpn_type_cd = 'D'
INNER JOIN ncrcuv01 cuOrig
ON cuOrig.COU_ISO_ID = co.COU_ISO_ID_ORIG
AND TRUNC(CURRENT_DATE) BETWEEN cuOrig.COU_EFFECT_DT AND cuOrig.COU_EFFECT_TO_DT
INNER JOIN ncrcuv01 cuDest
ON cuDest.COU_ISO_ID = co.COU_ISO_ID_DEST
AND TRUNC(CURRENT_DATE) BETWEEN cuDest.COU_EFFECT_DT AND cuDest.COU_EFFECT_TO_DT
LEFT OUTER JOIN ncrblv01 blOrig
ON blOrig.BUL_CSYS_ID = co.BUL_CSYS_ID_ORIG
AND TRUNC(CURRENT_DATE) BETWEEN blOrig.BUL_EFFECT_DT AND blOrig.BUL_EFFECT_TO_DT
LEFT OUTER JOIN ncrblv01 blDest
ON blDest.BUL_CSYS_ID = co.BUL_CSYS_ID_DEST
AND TRUNC(CURRENT_DATE) BETWEEN blDest.BUL_EFFECT_DT AND blDest.BUL_EFFECT_TO_DT
LEFT OUTER JOIN corcsv01 cs
ON co.con_id = cs.con_id
AND cs.cos_sign_nm is not null
and cs.cos_delete_in = 'N'
WHERE co.CON_CLNT_REF_CR IN (select dataItem from TABLE(ZYADMIN.ZYP90008.GetListFromCSV(sPInputList)) b)
)WHERE SEQ_NR = 1;
OPEN cPStatus FOR
SELECT csv.CON_ID AS CON_ID_COS
,csv.XSF_ID
,csv.XSS_ID
,csv.XSG_ID
,csv.XSD_ID
,ndv.XSX_ID
,ndv.xsd_customer_ds
,BUL_CSYS_ID_OCC
,nlv.BUL_NM as BUL_NM_OCC
,csv.COS_EVENT_LT
,ndv.XSD_SEVERITY_CD
,qb.QLA_DS
FROM corcsv01 csv
INNER JOIN corcov01 co
ON csv.con_id = co.con_id
INNER JOIN ncrsdv01 ndv
ON ndv.XSF_ID=csv.XSF_ID
AND ndv.XSS_ID=csv.XSS_ID
AND ndv.XSG_ID=csv.XSG_ID
AND ndv.XSD_ID=csv.XSD_ID
AND TRUNC(CURRENT_DATE) BETWEEN ndv.XSD_EFFECT_DT AND ndv.XSD_EFFECT_TO_DT
INNER JOIN ncrksv01 ks
ON ndv.XSX_ID= ks.XSX_ID
AND TRUNC(CURRENT_DATE) BETWEEN ks.SKT_EFFECT_DT AND ks.SKT_EFFECT_TO_DT
AND ks.SCA_ID = 'WEB'
LEFT OUTER JOIN ncrqav01 qa
ON qa.XSX_ID = ndv.XSX_ID
AND qa.APP_ID = 'EXCO'
AND TRUNC(CURRENT_DATE) BETWEEN QAS_EFFECT_DT AND QAS_EFFECT_TO_DT
LEFT OUTER JOIN ncrqbv01 qb
ON qa.QLA_ID = qb.QLA_ID
AND qa.APP_ID = qb.APP_ID
AND TRUNC(CURRENT_DATE) BETWEEN QLA_EFFECT_DT AND QLA_EFFECT_TO_DT
INNER JOIN ncrblv01 nlv
ON nlv.BUL_CSYS_ID=csv.BUL_CSYS_ID_OCC
AND TRUNC(CURRENT_DATE)BETWEEN nlv.BUL_EFFECT_DT AND nlv.BUL_EFFECT_TO_DT
WHERE co.CON_CLNT_REF_CR IN (select dataItem from TABLE(ZYADMIN.ZYP90008.GetListFromCSV(sPInputList)) b);
END IF;
EXCEPTION
WHEN OTHERS THEN
sPError := '1' ||' '||SQLCODE||' '||SUBSTR(SQLERRM, 1, 200);
END;
END COP00134_006_1;
问题是在ELSE部分执行的查询。 正如评论中所述,我单独运行了相同的查询,并且在不到一秒的时间内执行了相同的查询,而当相同的数据传递到过程时,它需要一分多钟。我不能运行探查,因为我没有访问数据库,并要求DBA为同样会采取一个多星期的过程中,我需要尽快
甲骨文建立执行计划的查询不存储过程解决这个问题。因此,存储过程中的每个查询都将拥有自己的执行计划。你说这约yoour查询:
“在where子句不同的列”
不同的列是指不同的访问路径:这可能转化为不同的使用指标,不同的磁盘读取活动,可能不同的数据量。因此,取决于IF ... ELSE的哪个分支,体验不同的性能并不令人惊讶。分开的过程不会改变底层查询的性能。
要检查差异,请为每个单独的查询运行EXPLAIN PLAN。 Find out more。您需要将这些语句删除到SQL工作表(或者用于SQL的任何工具)中。
如果您认为问题出在存储过程,那么您应该调查PL/SQL分析器所处的时间。 Find out more。如果它尚未安装,则可能需要安装DBA才能安装它。
“时相同的查询执行内部存储过程具有相同的价值观和相同的数据正在采取60+秒”
你一直谈论经过的时间。你如何衡量这些?你在测量什么?时间到了哪里?当实际的罪魁祸首是一个糟糕的JDBC配置的错误网络连接时,这不会是数据库首次因性能较差而受到指责。
AFAI,是的,如果您在通过jdbc从数据库检索数据时遇到性能问题,应该这样做。 –
SQL查询具有执行计划,而不是存储过程(或针对该问题的任何PL/SQL代码块)。所以执行计划将被视为您的存储过程中的每个查询。你不会像你描述的那样经历这个问题。 – sstan
@cihanseven - 不确定你的意思。如果已经正确配置,JDBC能够提供良好的性能。 – APC