Hi
The attach below query was execute 4705 times in Pro*c program .
Dose anyone can explain the low parse counter below, comparing to the
execute counter .
I will expect to have the same number for the parse & execute .
Note the query is Fetch single row each time
Thanks .
select PRODUCT.rowid ,PRODUCT.CUSTOM ER_ID ,PRODUCT.PRODUC T_CODE ,
PRODUCT.PRODUCT _ISSUE_NUM ,PRODUCT.PRODUC T_STATUS ,
PRODUCT.RETIREM ENT_STATUS ,PRODUCT.ACTIVI TY_TYPE ,
PRODUCT.ADDITIO NAL_ACTIVITY
,TO_CHAR(PRODUC T.ACTIVITY_DATE ,'YYYYMMDD') ,
TO_CHAR(PRODUCT .ACTIVITY_UPDAT E_DATE,'YYYYMMD D')
,PRODUCT.ADVANC E_SALE_IND
,PRODUCT.PROSPE CT_CODE ,PRODUCT.PROD_C URR_PROS_CODE ,
PRODUCT.PROSPEC T_AGE ,PRODUCT.DOMINA NT_HEADING ,
PRODUCT.DOMINAN T_HEADING_AMT ,TO_CHAR(PRODUC T.LAST_CONT_PRI NT_DATE,
'YYYYMMDD') ,PRODUCT.COMPLA INT_IND
,TO_CHAR(PRODUC T.COMPLAINT_DAT E,
'YYYYMMDD') ,TO_CHAR(PRODUC T.DIVISION_ARRI VE_DATE,'YYYYMM DD') ,
PRODUCT.COMMERC IAL_NAME ,PRODUCT.SRCH_C OMMERCIAL_NAME ,
PRODUCT.DISPLAY _AD_EXIST_IND ,PRODUCT.LOCAL_ FOREIGN_IND ,
PRODUCT.CANVASS _CODE ,PRODUCT.CANVAS S_ISSUE_NUM
,PRODUCT.FIRST_ ASSIGN_ID
,TO_CHAR(PRODUC T.FIRST_ASSIGN_ DATE,'YYYYMMDD' )
,PRODUCT.CURREN T_ASSIGN_ID
,TO_CHAR(PRODUC T.CURRENT_ASSIG N_DATE,'YYYYMMD D') ,
PRODUCT.CONTRAC T_LOAD_PHASE ,PRODUCT.CONTIN UITY_AGE
,PRODUCT.CHANNE L_AGE
,PRODUCT.ASSIST _ASSIGN_ID ,PRODUCT.ASSIST _PERCENTAGE ,
PRODUCT.LENDING _ASSIGN_ID ,PRODUCT.SELL_C ANVASS_CODE ,
PRODUCT.SELL_CA NV_ISS_NUM ,PRODUCT.SELL_A SSIGN_ID ,
PRODUCT.CMSN_CA NVASS_CODE ,PRODUCT.CMSN_C ANV_ISS_NUM ,
PRODUCT.CMSN_AS SIGN_ID ,PRODUCT.QUERY_ STATUS
,PRODUCT.COMPLA INT_ASGN_ID ,
PRODUCT.NIR_PRO DUCT_IND
,TO_CHAR(PRODUC T.START_PUBLISH _DATE,'YYYYMMDD ') ,
PRODUCT.DURATIO N ,TO_CHAR(PRODUC T.ACTUAL_END_PU B_DATE,'YYYYMMD D')
,
PRODUCT.RENEWAL _CONTRACT_IND ,PRODUCT.PRODUC T_NAME ,
PRODUCT.NUM_OF_ BOTS_ITEMS ,PRODUCT.NUM_OF _NISD_ITEMS ,
PRODUCT.NUM_QUE RIED_ITEMS ,PRODUCT.LAST_Y EAR_NISD_AMT ,
PRODUCT.ORIG_AS GN_BOTS_AMT ,PRODUCT.OVER_R ATE_BOTS_AMT ,
PRODUCT.BOTS_AM OUNT ,PRODUCT.NISD_A MOUNT ,PRODUCT.RESULT S_NISD_AMT
,
PRODUCT.TAX_ID_ LETTER ,PRODUCT.TAX_ID _DATE ,PRODUCT.TAX_ID _CODE ,
PRODUCT.E_MAIL_ ADDRESS ,PRODUCT.ASSIST _CANVASS_CODE ,
PRODUCT.ASSIST_ CANV_ISS_NUM ,PRODUCT.CURP_I D
,PRODUCT.HIGH_A MT_COLOR_CODE
,PRODUCT.PREV_H IGH_AMT_COLOR ,PRODUCT.CTCR_B ATCH_PROG_ID ,
TO_CHAR(PRODUCT .CTCR_BATCH_PRO G_DT,'YYYYMMDD' ) ,
PRODUCT.CTCR_ON LINE_USER_ID ,PRODUCT.CTCR_O NLINE_PROG_ID ,
TO_CHAR(PRODUCT .CTCR_ONLINE_PR OG_DT,'YYYYMMDD ') ,
TO_CHAR(PRODUCT .CTCR_SYS_CRE_D T,'YYYYMMDDHH24 MISS') ,
TO_CHAR(PRODUCT .CTCR_SYS_UPD_D T,'YYYYMMDDHH24 MISS') ,
PRODUCT.CTCR_DB I_PROG_ID
,TO_CHAR(PRODUC T.CTCR_DBI_PROG _DT,'YYYYMMDD') ,
TO_CHAR(PRODUCT .CTCR_CNV_DT,'Y YYYMMDD') ,PRODUCT.CTCR_E VENT_ID
into
:b1:i1,:b2:i2,: b3:i3,:b4:i4,:b 5:i5,:b6:i6,:b7 :i7,:b8:i8,:b9: i9,:b10:i10,
:b11:i11,:b12:i 12,:b13:i13,:b1 4:i14,:b15:i15, :b16:i16,:b17:i 17,:b18:i18,
:b11:i11,:b12:i 12,:b13:i13,:b1 4:i14,:b15:i15, :b16:i16,:b17:i 17,:b18:i18,
:b19:i19,:b20:i 20,:b21:i21,:b2 2:i22,:b23:i23, :b24:i24,:b25:i 25,:b26:i26,
:b27:i27,:b28:i 28,:b29:i29,:b3 0:i30,:b31:i31, :b32:i32,:b33:i 33,:b34:i34,
:b35:i35,:b36:i 36,:b37:i37,:b3 8:i38,:b39:i39, :b40:i40,:b41:i 41,:b42:i42,
:b43:i43,:b44:i 44,:b45:i45,:b4 6:i46,:b47:i47, :b48:i48,:b49:i 49,:b50:i50,
:b51:i51,:b52:i 52,:b53:i53,:b5 4:i54,:b55:i55, :b56:i56,:b57:i 57,:b58:i58,
:b59:i59,:b60:i 60,:b61:i61,:b6 2:i62,:b63:i63, :b64:i64,:b65:i 65,:b66:i66,
:b67:i67,:b68:i 68,:b69:i69,:b7 0:i70,:b71:i71, :b72:i72,:b73:i 73,:b74:i74,
:b75:i75,:b76:i 76,:b77:i77,:b7 8:i78,:b79:i79
from
PRODUCT where ((CUSTOMER_ID=: b80 and PRODUCT_CODE=:b 81) and
PRODUCT_ISSUE_N UM=:b82)
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
----------
Parse 1063 0.10 0.17 0 0 0
0
Execute 4705 0.71 1.30 0 0 0
0
Fetch 4705 1.36 2.76 84 14115 0
4705
------- ------ -------- ---------- ---------- ---------- ----------
----------
total 10473 2.17 4.23 84 14115 0
4705
The attach below query was execute 4705 times in Pro*c program .
Dose anyone can explain the low parse counter below, comparing to the
execute counter .
I will expect to have the same number for the parse & execute .
Note the query is Fetch single row each time
Thanks .
select PRODUCT.rowid ,PRODUCT.CUSTOM ER_ID ,PRODUCT.PRODUC T_CODE ,
PRODUCT.PRODUCT _ISSUE_NUM ,PRODUCT.PRODUC T_STATUS ,
PRODUCT.RETIREM ENT_STATUS ,PRODUCT.ACTIVI TY_TYPE ,
PRODUCT.ADDITIO NAL_ACTIVITY
,TO_CHAR(PRODUC T.ACTIVITY_DATE ,'YYYYMMDD') ,
TO_CHAR(PRODUCT .ACTIVITY_UPDAT E_DATE,'YYYYMMD D')
,PRODUCT.ADVANC E_SALE_IND
,PRODUCT.PROSPE CT_CODE ,PRODUCT.PROD_C URR_PROS_CODE ,
PRODUCT.PROSPEC T_AGE ,PRODUCT.DOMINA NT_HEADING ,
PRODUCT.DOMINAN T_HEADING_AMT ,TO_CHAR(PRODUC T.LAST_CONT_PRI NT_DATE,
'YYYYMMDD') ,PRODUCT.COMPLA INT_IND
,TO_CHAR(PRODUC T.COMPLAINT_DAT E,
'YYYYMMDD') ,TO_CHAR(PRODUC T.DIVISION_ARRI VE_DATE,'YYYYMM DD') ,
PRODUCT.COMMERC IAL_NAME ,PRODUCT.SRCH_C OMMERCIAL_NAME ,
PRODUCT.DISPLAY _AD_EXIST_IND ,PRODUCT.LOCAL_ FOREIGN_IND ,
PRODUCT.CANVASS _CODE ,PRODUCT.CANVAS S_ISSUE_NUM
,PRODUCT.FIRST_ ASSIGN_ID
,TO_CHAR(PRODUC T.FIRST_ASSIGN_ DATE,'YYYYMMDD' )
,PRODUCT.CURREN T_ASSIGN_ID
,TO_CHAR(PRODUC T.CURRENT_ASSIG N_DATE,'YYYYMMD D') ,
PRODUCT.CONTRAC T_LOAD_PHASE ,PRODUCT.CONTIN UITY_AGE
,PRODUCT.CHANNE L_AGE
,PRODUCT.ASSIST _ASSIGN_ID ,PRODUCT.ASSIST _PERCENTAGE ,
PRODUCT.LENDING _ASSIGN_ID ,PRODUCT.SELL_C ANVASS_CODE ,
PRODUCT.SELL_CA NV_ISS_NUM ,PRODUCT.SELL_A SSIGN_ID ,
PRODUCT.CMSN_CA NVASS_CODE ,PRODUCT.CMSN_C ANV_ISS_NUM ,
PRODUCT.CMSN_AS SIGN_ID ,PRODUCT.QUERY_ STATUS
,PRODUCT.COMPLA INT_ASGN_ID ,
PRODUCT.NIR_PRO DUCT_IND
,TO_CHAR(PRODUC T.START_PUBLISH _DATE,'YYYYMMDD ') ,
PRODUCT.DURATIO N ,TO_CHAR(PRODUC T.ACTUAL_END_PU B_DATE,'YYYYMMD D')
,
PRODUCT.RENEWAL _CONTRACT_IND ,PRODUCT.PRODUC T_NAME ,
PRODUCT.NUM_OF_ BOTS_ITEMS ,PRODUCT.NUM_OF _NISD_ITEMS ,
PRODUCT.NUM_QUE RIED_ITEMS ,PRODUCT.LAST_Y EAR_NISD_AMT ,
PRODUCT.ORIG_AS GN_BOTS_AMT ,PRODUCT.OVER_R ATE_BOTS_AMT ,
PRODUCT.BOTS_AM OUNT ,PRODUCT.NISD_A MOUNT ,PRODUCT.RESULT S_NISD_AMT
,
PRODUCT.TAX_ID_ LETTER ,PRODUCT.TAX_ID _DATE ,PRODUCT.TAX_ID _CODE ,
PRODUCT.E_MAIL_ ADDRESS ,PRODUCT.ASSIST _CANVASS_CODE ,
PRODUCT.ASSIST_ CANV_ISS_NUM ,PRODUCT.CURP_I D
,PRODUCT.HIGH_A MT_COLOR_CODE
,PRODUCT.PREV_H IGH_AMT_COLOR ,PRODUCT.CTCR_B ATCH_PROG_ID ,
TO_CHAR(PRODUCT .CTCR_BATCH_PRO G_DT,'YYYYMMDD' ) ,
PRODUCT.CTCR_ON LINE_USER_ID ,PRODUCT.CTCR_O NLINE_PROG_ID ,
TO_CHAR(PRODUCT .CTCR_ONLINE_PR OG_DT,'YYYYMMDD ') ,
TO_CHAR(PRODUCT .CTCR_SYS_CRE_D T,'YYYYMMDDHH24 MISS') ,
TO_CHAR(PRODUCT .CTCR_SYS_UPD_D T,'YYYYMMDDHH24 MISS') ,
PRODUCT.CTCR_DB I_PROG_ID
,TO_CHAR(PRODUC T.CTCR_DBI_PROG _DT,'YYYYMMDD') ,
TO_CHAR(PRODUCT .CTCR_CNV_DT,'Y YYYMMDD') ,PRODUCT.CTCR_E VENT_ID
into
:b1:i1,:b2:i2,: b3:i3,:b4:i4,:b 5:i5,:b6:i6,:b7 :i7,:b8:i8,:b9: i9,:b10:i10,
:b11:i11,:b12:i 12,:b13:i13,:b1 4:i14,:b15:i15, :b16:i16,:b17:i 17,:b18:i18,
:b11:i11,:b12:i 12,:b13:i13,:b1 4:i14,:b15:i15, :b16:i16,:b17:i 17,:b18:i18,
:b19:i19,:b20:i 20,:b21:i21,:b2 2:i22,:b23:i23, :b24:i24,:b25:i 25,:b26:i26,
:b27:i27,:b28:i 28,:b29:i29,:b3 0:i30,:b31:i31, :b32:i32,:b33:i 33,:b34:i34,
:b35:i35,:b36:i 36,:b37:i37,:b3 8:i38,:b39:i39, :b40:i40,:b41:i 41,:b42:i42,
:b43:i43,:b44:i 44,:b45:i45,:b4 6:i46,:b47:i47, :b48:i48,:b49:i 49,:b50:i50,
:b51:i51,:b52:i 52,:b53:i53,:b5 4:i54,:b55:i55, :b56:i56,:b57:i 57,:b58:i58,
:b59:i59,:b60:i 60,:b61:i61,:b6 2:i62,:b63:i63, :b64:i64,:b65:i 65,:b66:i66,
:b67:i67,:b68:i 68,:b69:i69,:b7 0:i70,:b71:i71, :b72:i72,:b73:i 73,:b74:i74,
:b75:i75,:b76:i 76,:b77:i77,:b7 8:i78,:b79:i79
from
PRODUCT where ((CUSTOMER_ID=: b80 and PRODUCT_CODE=:b 81) and
PRODUCT_ISSUE_N UM=:b82)
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
----------
Parse 1063 0.10 0.17 0 0 0
0
Execute 4705 0.71 1.30 0 0 0
0
Fetch 4705 1.36 2.76 84 14115 0
4705
------- ------ -------- ---------- ---------- ---------- ----------
----------
total 10473 2.17 4.23 84 14115 0
4705