Hint not working properly

[indrajit2002]

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

[PAVB]

Query:
Would you mind in posting execution plan?
Statistics:
Would you mind in posting statis gathering command line?
Other:
Would you mind in telling us how many rows are stored in each table?

[indrajit2002]

Thanks for replying.

I will send the explain plan tomorrow,

For statistics we are using analyze table statement.

And the table contains 30 million rows.

[PAVB]

We look forward to see exec plan.

Please don't forget to post "exact" command used to gather stats as well as # of rows on each table.

Also... please let us know which Oracle version are we talking about.

[indrajit2002]

Please find the explain paln below,

PLAN_TABLE_OUTPUT



--------------------------------------------------------------------------------


--------------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost Pstart Pstop


--------------------------------------------------------------------------------
0 SELECT STATEMENT 1 405 25464
1 SORT GROUP BY 1 405 25464
* 2 HASH JOIN 1 405 25458
* 3 HASH JOIN 1 345 25262
* 4 HASH JOIN 1 336 25253
* 5 HASH JOIN 1 284 25057
* 6 TABLE ACCESS FULL DIM_TM_MV 1 52 195
7 PARTITION RANGE ALL 1 9
8 PARTITION LIST ALL 1 12

| 9 | TABLE ACCESS BY LOCAL INDEX ROWID| FACT_DLY_ALGND_SLS | 15M| 3502M| 24632 | 1 | 1
| 10 | BITMAP CONVERSION TO ROWIDS | | | | | | |
0 SELECT STATEMENT 1 405 25464
1 SORT GROUP BY 1 405 25464
* 2 HASH JOIN 1 405 25458
* 3 HASH JOIN 1 345 25262
* 4 HASH JOIN 1 336 25253
* 5 HASH JOIN 1 284 25057
* 6 TABLE ACCESS FULL DIM_TM_MV 1 52 195
7 PARTITION RANGE ALL 1 9
8 PARTITION LIST ALL 1 12
11 BITMAP INDEX FULL SCAN XN1_FACT_DLY_SLS 1 108
* 12 TABLE ACCESS FULL DIM_TM_MV 1 52 195
13 VIEW VW_NSO_1 6 54 8
14 SORT UNIQUE 6 42 8
15 TABLE ACCESS FULL FACT_MLY_PART_TRUNC 6 42 2
16 TABLE ACCESS FULL DIM_TM_MV 15255 893K 195


--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

--------------------------------------------------------------------------------

2 - access("F"."COMP_DT_BE_ID"="COMP_TM"."BE_ID")
3 - access("VW_NSO_1"."YR_MO"=TO_CHAR("F"."PRD_YR_CD")||'_'||LPAD(TO_CHAR("F"."PRD_MO_CD"),2,'0')
4 - access("F"."INVC_DT"="INVC_TM"."DAY_STRT_PRD_OF_TM")
5 - access("F"."TRANSACTION_DATE"="TRANS_TM"."DAY_STRT_PRD_OF_TM")
6 - filter("TRANS_TM"."DAY_STRT_PRD_OF_TM" IS NOT NULL AND "TRANS_TM"."DAY_OID"="TRANS_TM"."BE_ID
12 - filter("INVC_TM"."DAY_STRT_PRD_OF_TM" IS NOT NULL AND "INVC_TM"."DAY_OID"="INVC_TM"."BE_ID")

Note: cpu costing is off





The command for gathering stats is

exec dbms_stats.gather_table_stats(ownname => 'TRANSDATA', tabname => 'FACT_MLY_ALGND_SLS', cascade => true, degree => 4);

The database version is 9.2.0.8

Thanks,
Indrajit

[vnktummala]

would you mind to format the plan properly and attach instead of print?

Thanks,

[indrajit2002]

Hi I have attached the explaain plan screenshot. Please check if you are facing any problem seeing it.

Thanks,
Indrajit

[gopu_g]

Hi I have attached the explaain plan screenshot. Please check if you are facing any problem seeing it.

Thanks,
Indrajit

Where is the attachment?

[indrajit2002]

Sorry for that . I have attached again. Please let me know if you can see it.

Regards,
Indrajit

[PAVB]

As far as I can see explain plan shows three TABLE ACCESS FULL on table DIM_TM_MV - which is exactly what hint is asking for.