one sql runs very slow ,but run normal in rules mode
os: Linux Release: 2.6.18-164.el5xen
db :10.2.0.4
1.pls check the attached AWR report.
we found the top sql in " SQL ordered by Elapsed Time" run very slow,but it run normal which use rule optimzer.
=======
sql id:4hc0qp734k8am
sql text:
select DISTINCT DC_GET_TERNAME_F(J.PLN_TER_CODE_FROM) OUT_TerCD , DC_GET_TERNAME_F(J.PLN_TER_CODE_TO) IN_TerCD , DC_SECOND_VSL_BY_CNTID_F(A.CNT_ID) VSL_NAME , DC_SECOND_VYG_BY_CNTID_F(A.CNT_ID) VOYAGE , To_Char(A.CNT_INPORTTM, 'YYYY-MM-DD HH24:MI') INPortTM , A.CNT_CNTRNO CNT_NO , A.Cnt_Csizecd CSIZE , decode(A.CNT_CTP_CTYPECD, 'GP', DECODE(A.CNT_CHEIGHTCD, '5', 'HC', 'GP'), A.CNT_CTP_CTYPECD) CNT_CTP_CTYPECD , A.CNT_CWEIGHT , DC_GET_TERNAME_F(A.CNT_CTERMCD) CNT_CTERMCD , DC_SECOND_VSL_BY_CNTID_F(A.CNT_ID) SECOND_VSL , DC_SECOND_VYG_BY_CNTID_F(A.CNT_ID) SECOND_VYG , DC_Q_Scheme_TM_F(A.CNT_ID, 'FG') PortFG , DC_SECOND_VYG_STARTTM_CNT_F(A.CNT_ID) VYG_STARTTM , A.CNT_ID CNTID , A.CNT_DNGCD DNGCD , A.CNT_EFFG EFFG , A.CNT_POT_DISPORT DISPort , A.CNT_COWNER COwner , DC_Q_Scheme_CheckCD_F(A.CNT_ID) CheckCD , DC_Q_Scheme_PlanNO_F(A.CNT_ID) PlanNO , DC_SECOND_TER_BY_CNTID_F(CNT_ID) GPLNTERNAME , to_char(A.CNT_ST_TEMPNUM) TEMPNUM , DC_IS_WegCNT_F(A.CNT_CWEIGHT) WegCNT , DC_Status_CNT_F(A.CNT_ID) THROUGHCUSTOM , A.CNT_POT_LDPORT LDPORTB , DC_GET_BILLNO_BY_CNTID_F(A.CNT_ID) BILLNO , DC_Q_Scheme_TM_F(A.CNT_ID, 'DP') KXTM , J.PLN_ID PLNID , I.PLC_PSTATUS OP