| Can any one help me to tune this query. [message #396115] |
Sat, 04 April 2009 01:10  |
vipinsonkar2000
Messages: 38
Registered: May 2008
|
Member |
|
|
This query is taking too much. I need to tune this only with the help HINTS. I don't have permission to rewrite/change the SQL.
SELECT /*+ FULL(POS_TXN_DTL_CUR) */ COUNT(POS_TXN_DTL_CUR.VISIT_NBR) a,POS_TXN_DTL_CUR.XTRA_CARD_NBR
FROM COMMON.V_POS_TXN_DTL_CUR POS_TXN_DTL_CUR, XTRA.XTRA_CARD XTRA_CARD, PRODUCT.SKU SKU
WHERE ( ( POS_TXN_DTL_CUR.DATE_DT BETWEEN TO_DATE('01/31/2009','MM/DD/YYYY')
AND TO_DATE('01/01/2010','MM/DD/YYYY') AND SKU.SKU_NBR IN ( 444706,444717,444718,444719,444720,444723,444724 )
AND POS_TXN_DTL_CUR.XTRA_CARD_NBR NOT IN ( SELECT /*+ ALL_ROWS */ LT2.LOOKUP_VAL FROM PVANTAGE.LT2 LT2 ) )
AND POS_TXN_DTL_CUR.XTRA_CARD_NBR = XTRA_CARD.XTRA_CARD_NBR AND SKU.SKU_NBR = POS_TXN_DTL_CUR.SKU_NBR )
and btch_cntl_nbr in (Select btch_cntl_nbr from campaign_batch_control where cmpgn_id = 6700
and dv_id = 1962 and proc_end_dt IS NULL AND defer_day_cnt IS NULL
AND NVL(hold_ind,'N') ='N') GROUP BY POS_TXN_DTL_CUR.XTRA_CARD_NBR;
|
|
|
|
|
|
|
|
| Re: Can any one help me to tune this query. [message #396135 is a reply to message #396129] |
Sat, 04 April 2009 02:38  |
 |
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
I'm curious to know how the program knows to which subquery in the query it has to apply which hint.
| Quote: | What other information do you need to tune this query?
|
Read forum and sticky topic and other posts in this forum, and it becomes obvious what is needed.
But first try removing all hints and keeping your statistics up to date, 99% of the times it is sufficient.
Regards
Michel
[Updated on: Sat, 04 April 2009 02:38] Report message to a moderator |
|
|
|
| Re: Can any one help me to tune this query. [message #396167 is a reply to message #396115] |
Sat, 04 April 2009 09:20 |
 |
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
Senior Member |
|
|
SELECT /*+ FULL(POS_TXN_DTL_CUR) */ Count(pos_txn_dtl_cur.visit_nbr) a,
pos_txn_dtl_cur.xtra_card_nbr
FROM common.v_pos_txn_dtl_cur pos_txn_dtl_cur,
xtra.xtra_card xtra_card,
product.sku sku
WHERE ((pos_txn_dtl_cur.date_dt BETWEEN To_date('01/31/2009','MM/DD/YYYY') AND To_date('01/01/2010','MM/DD/YYYY')
AND sku.sku_nbr IN (444706,444717,444718,444719,
444720,444723,444724)
AND pos_txn_dtl_cur.xtra_card_nbr NOT IN (SELECT /*+ ALL_ROWS */ lt2.lookup_val
FROM pvantage.lt2 lt2))
AND pos_txn_dtl_cur.xtra_card_nbr = xtra_card.xtra_card_nbr
AND sku.sku_nbr = pos_txn_dtl_cur.sku_nbr)
AND btch_cntl_nbr IN (SELECT btch_cntl_nbr
FROM campaign_batch_control
WHERE cmpgn_id = 6700
AND dv_id = 1962
AND proc_end_dt IS NULL
AND defer_day_cnt IS NULL
AND Nvl(hold_ind,'N') = 'N')
GROUP BY pos_txn_dtl_cur.xtra_card_nbr;
Why have xtra.xtra_card xtra_card & product.sku sku as part of FROM clause when they contribute no data to the SELECT clause?
>I can change the hint only and save this hint in separate table, our production job takes hint from this table
How does application know which hint goes with which SELECT?
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Can any one help me to tune this query. [message #397168 is a reply to message #396340] |
Thu, 09 April 2009 05:07 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Take the hint off the query and show use the explain plan that is produced.
Tell us what indexes you've got on the tables that involve columns in the WHERE clause.
Tell us how many rows are in the tables, and how many meet the various conditions in the Where clause.
Can you create new indexes?
Can you create stored outlines?
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
