OraFAQ Forum: Performance Tuning » Can any one help me to tune this query.

Home » RDBMS Server » Performance Tuning » Can any one help me to tune this query.

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

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;

Report message to a moderator

Re: Can any one help me to tune this query. [message #396121 is a reply to message #396115]

Sat, 04 April 2009 01:25

Michel Cadot
Messages: 68664
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

1/ No one can help with what you posted
2/ Why do you use and want to use hints?
3/ If you cannot change the query, how could you change hints?

Regards
Michel

Report message to a moderator

Re: Can any one help me to tune this query. [message #396129 is a reply to message #396121]

Sat, 04 April 2009 01:48

vipinsonkar2000
Messages: 38
Registered: May 2008

Member

I can change the hint only and save this hint in separate table, our production job takes hint from this table and append it to above query and run it in production. So by this mean i can only change the hint.
What other information do you need to tune this query?

Report message to a moderator

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: 68664
Registered: March 2007
Location: Nanterre, France, http://...

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?

Report message to a moderator

Re: Can any one help me to tune this query. [message #396304 is a reply to message #396115]

Mon, 06 April 2009 01:50

vipinsonkar2000
Messages: 38
Registered: May 2008

Member

Hi All,

Michel Cadot, we can only apply hints on main query(SELECT) not to its sub query.

BlackSwan, if we will not include these tables (xtra.xtra_card xtra_card & product.sku sku) in from clause then how could able to apply join.

Thanks and Regards

Report message to a moderator

Re: Can any one help me to tune this query. [message #396307 is a reply to message #396115]

Mon, 06 April 2009 01:55

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>BlackSwan, if we will not include these tables (xtra.xtra_card xtra_card & product.sku sku) in from clause then how could able to apply join.
Moot point as you claim only can change hints.

In reality they can be subordinated into WHERE clause to obtain desired filters.

Report message to a moderator

Re: Can any one help me to tune this query. [message #396313 is a reply to message #396304]

Mon, 06 April 2009 01:57

Michel Cadot
Messages: 68664
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

we can only apply hints on main query(SELECT) not to its sub query.

Who is we? Do you mean you at your place or anybody.
Don't you notice you have a hint on your subquery?

Quote:

if we will not include these tables (xtra.xtra_card xtra_card & product.sku sku) in from clause then how could able to apply join.

With EXISTS for instance.

Did you try to follow my advice?

Quote:

But first try removing all hints and keeping your statistics up to date, 99% of the times it is sufficient.

Regards
Michel

Report message to a moderator

Re: Can any one help me to tune this query. [message #396340 is a reply to message #396115]

Mon, 06 April 2009 03:48

vipinsonkar2000
Messages: 38
Registered: May 2008

Member

Hi Michel,

We means production support members. Yes we have the hints in sub query but that hints we can't change.

Yes I am following your advice and requested DBA to analyze tables used in query.

Thanks and Regards

Report message to a moderator

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?

Report message to a moderator

Previous Topic:	PCTFREE and PCTUSED
Next Topic:	List partition and Index partitioning (merged)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Jun 26 11:43:18 CDT 2024