Home » RDBMS Server » Performance Tuning » Hint is not working properly (9.2.0.8, HP-UX)
Hint is not working properly [message #465511] |
Wed, 14 July 2010 08:15 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 Report message to a moderator
|
|
|
Re: Hint is not working properly [message #465568 is a reply to message #465511] |
Wed, 14 July 2010 16:33 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
coleing
Messages: 213 Registered: February 2008
|
Senior Member |
|
|
Explain plan would be good.
As you are obviously in datawarehouse territory, check for any query re-writes or mview usage.
Check for degree of parallelism, There are several bugs out there meaning you can get inconsistent results during parallel execution.
are your "tables" really "views"?
|
|
|
|
Re: Hint is not working properly [message #465627 is a reply to message #465596] |
Thu, 15 July 2010 00:56 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
indrajit2002
Messages: 53 Registered: November 2007 Location: INDIA
|
Member |
|
|
Please find the explain plan with the hint,
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
[Updated on: Thu, 15 July 2010 01:01] by Moderator Report message to a moderator
|
|
|
|
|
|
|
|
Re: Hint is not working properly [message #465688 is a reply to message #465684] |
Thu, 15 July 2010 04:37 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Please have a read of the orafaq forum guide, especially the bit about formatting posts and using code tags. This is what the plans looks like if you use them, which is a lot more readable:
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 | | | | | | |
| 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")
|
|
|
|
Re: Hint is not working properly [message #465699 is a reply to message #465691] |
Thu, 15 July 2010 06:08 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
indrajit2002
Messages: 53 Registered: November 2007 Location: INDIA
|
Member |
|
|
Thanks for the idea. But do you have any clue of why is it behaving whimsically? Means sometimes the full hint working and sometimes not working. Is it because of some CBO issue? How to identify that? Also to gather statistics of the FACT_DLY_ALGND_SLS table shall i use estimate percent=>30? since it is taking 5 hours to gather stats of this 30 million rows table. Please help
Thanks,
Indrajit
|
|
|
Re: Hint is not working properly [message #465722 is a reply to message #465511] |
Thu, 15 July 2010 07:23 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Roachcoach
Messages: 1576 Registered: May 2010 Location: UK
|
Senior Member |
|
|
indrajit2002 wrote on Wed, 14 July 2010 14:15I 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.
The same query shouldn't give inconsistent results if run across the same, static tables.
Are you sure the hint is the issue?
Or do you mean something else by not fetching the data?
[Updated on: Thu, 15 July 2010 07:23] Report message to a moderator
|
|
|
Re: Hint is not working properly [message #465755 is a reply to message #465722] |
Thu, 15 July 2010 09:18 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
indrajit2002
Messages: 53 Registered: November 2007 Location: INDIA
|
Member |
|
|
The hint is working sometimes and fetching the data. Sometimes it is not working and still data is getting fetched.Vice versa is also happening means without hint data is being fetched one time and another time when we are executing without hint not a single data is fetching after executing for 15 hours. That is why i am not able to understand what is going on.
|
|
|
Re: Hint is not working properly [message #467046 is a reply to message #465755] |
Thu, 22 July 2010 02:29 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
indrajit2002
Messages: 53 Registered: November 2007 Location: INDIA
|
Member |
|
|
I found a new thing. In qua database when I am running this query with hint it is fetching the data in 1 hour. But in prod database when I executed the query without hint it is taking hours and hours.
|
|
|
Goto Forum:
Current Time: Sun Sep 08 06:44:25 CDT 2024
|