OraFAQ Forum: Performance Tuning » Hint is not working properly

Home » RDBMS Server » Performance Tuning » Hint is not working properly (9.2.0.8, HP-UX)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Hint is not working properly [message #465511]

Wed, 14 July 2010 08:15

indrajit2002
Messages: 53
Registered: November 2007
Location: INDIA

Member

I have a query with FULL hint that is behaving in a strange manner. The query fetches around 700000 of data. Sometimes it fetches the data with the hint and sometimes it does not fetch any data with the hint and then I have to remove the hint and have to fetch the data.I am totally confused. Please tell me any remedy out of this. Below is the query,

select  /*+ FULL(COMP_TM) FULL(TRANS_TM) FULL(INVC_TM) */
          CUST_BE_ID     ,
            DISTR_BE_ID    ,
            FG_BE_ID         ,
            KIT_BE_ID        ,
            BG_ID_NO_BE_ID         ,
         ACTL_TERR_BE_ID       ,
            CORE_TERR_BE_ID      ,
        sum(     JNJ_LIST_AMT  ) AS JNJ_LIST_AMT,
            sum(     JNJ_PYMT_AMT            )  AS  JNJ_PYMT_AMT,
            sum(     JNJ_QTY           ) AS JNJ_QTY,
            sum(     JNJ_REB_AMT  ) AS JNJ_REB_AMT,
            sum(     JNJ_SLS_AMT  ) AS JNJ_SLS_AMT,
            sum(     KIT_LIST_AMT   ) AS KIT_LIST_AMT,
            sum(     KIT_QTY           ) AS KIT_QTY,
            sum(     KIT_SLS_AMT   ) AS KIT_SLS_AMT,
            sum(     FG_PYMT_AMT            ) AS FG_PYMT_AMT,
            sum(     FG_QTY           ) AS FG_QTY,
            sum(     FG_REB_AMT   ) AS FG_REB_AMT,
            sum(     FG_SLS_AMT   ) AS FG_SLS_AMT,
            sum(     FG_LIST_AMT   ) AS FG_LIST_AMT,
            to_date('15'||substr(COMP_TM.FISC_MO_CD,8,2)||substr(COMP_TM.FISC_MO_CD,3,4),'DDMMYYYY') AS     TRANS_MO_DATE,
            to_number(substr(COMP_TM.FISC_MO_CD,3,4) ) AS PRD_YR_CD,  
            to_number(substr(COMP_TM.FISC_MO_CD,8,2) ) AS PRD_MO_CD,  
            CONTR_PRD_TIER_NO,
            COMP_TM.FISC_MO_OID AS COMP_MO_BE_ID,
            CLSD_YR_FLG,
            ADJM_TRANS_CD,
            INVC_TM.FISC_MO_OID AS INVC_MO_BE_ID,
            ORD_TYP_CD,
            TRANS_TM.FISC_MO_OID AS TRANS_MO_BE_ID
from 
            FACT_DLY_ALGND_SLS F, DIM_TM_MV TRANS_TM,
            DIM_TM_MV INVC_TM, DIM_TM_MV COMP_TM
-- WHERE (F.PRD_YR_CD, F.PRD_MO_CD)
WHERE to_char(F.PRD_YR_CD)||'_'||lpad(to_char(F.PRD_MO_CD),2,'0')
in (      /* Logic to extract data for all the subpartions where there is a change*/
select 
-- substr(YR_MO,1,4), to_number(substr(YR_MO,6,2))
YR_MO
from  
mdm_dba.FACT_MLY_PART_TRUNC
)
AND F.COMP_DT_BE_ID=COMP_TM.BE_ID
AND F.TRANSACTION_DATE = TRANS_TM.DAY_STRT_PRD_OF_TM
AND TRANS_TM.DAY_OID = TRANS_TM.BE_ID
AND F.INVC_DT = INVC_TM.DAY_STRT_PRD_OF_TM
AND INVC_TM.DAY_OID = INVC_TM.BE_ID
group by 
            CUST_BE_ID     ,
            DISTR_BE_ID    ,
            FG_BE_ID         ,
            KIT_BE_ID        ,
            BG_ID_NO_BE_ID         ,
         ACTL_TERR_BE_ID       ,
            CORE_TERR_BE_ID      ,
            to_date('15'||substr(COMP_TM.FISC_MO_CD,8,2)||substr(COMP_TM.FISC_MO_CD,3,4),'DDMMYYYY'),
            to_number(substr(COMP_TM.FISC_MO_CD,3,4) ),  
            to_number(substr(COMP_TM.FISC_MO_CD,8,2) ),  
            CONTR_PRD_TIER_NO,
            COMP_TM.FISC_MO_OID ,
            CLSD_YR_FLG,
            ADJM_TRANS_CD,
            INVC_TM.FISC_MO_OID ,
            ORD_TYP_CD,
            TRANS_TM.FISC_MO_OID

One more issue is there. The statistics gathering activity of FACT_DLY_ALGND_SLS table takes around 5 hours to complete. It is a range partitioned table with subpartitions. Please help about the possible reasons and way outs.

Regards,
Indrajit

[Updated on: Wed, 14 July 2010 09:47] by Moderator