Home » SQL & PL/SQL » SQL & PL/SQL » Slow Query (11.1.0.7 on AIX 5.3)
Slow Query [message #440339] Fri, 22 January 2010 11:26 Go to next message
dkranes
Messages: 25
Registered: February 2008
Location: Upstate New York
Junior Member
Hello, I have a query that runs very slow. I am running Oracle 11.1.0.7 on AIX 5.3. Any ideas on how to improve performance?

Here is the query and plan is attached:

SELECT /*+ ORDERED INDEX(dw dw_store_status_x1) INDEX(h av_transaction_header_x0) INDEX(d av_discount_detail_x0) INDEX(tl av_transaction_line_x0) */
	h.store_no, w.ORG_CHN_NAME, h.transaction_date,
	SUM((d.pos_discount_amount * tl.db_cr_none * tl.voiding_reversal_flag * -1) * COALESCE(v.exchange_rate, 1)),
	tl.line_object,
	w.DFLT_CRNCY_CODE,
	h.transaction_category,
	tl.line_action,
	d.pos_discount_type,
	w.currency_id
FROM phu_all_stores_currency_322 w
	INNER JOIN dw_store_status dw ON (w.ORG_CHN_NUM = dw.store_no)
	INNER JOIN av_transaction_header h ON (dw.store_no = h.store_no AND dw.sales_date = h.transaction_date)
	INNER JOIN av_discount_detail d ON (h.av_transaction_id = d.av_transaction_id AND d.applied_flag = 1)
	INNER JOIN av_transaction_line tl ON (d.av_transaction_id = tl.av_transaction_id AND d.line_id = tl.line_id)
	LEFT JOIN currency_conversion v ON (w.currency_id = v.currency_id
			AND v.currency_conversion_type_id = 1
			AND v.effective_date_from <= h.transaction_date
			AND (v.effective_date_to >= h.transaction_date OR v.effective_date_to IS NULL)
			AND null IS NOT NULL)
WHERE dw.sales_date >= TO_DATE('2010-01-17', 'YYYY-MM-DD HH24:MI:SS')
AND dw.sales_date <= TO_DATE('2010-01-17', 'YYYY-MM-DD HH24:MI:SS')
AND dw.subledger_copied_flag = 0
AND dw.store_status > 0 
AND h.date_reject_id = 0 AND h.sa_rejection_flag = 0 AND h.transaction_void_flag IN (0,8) 
AND tl.line_void_flag = 0 AND tl.db_cr_none <> 0
GROUP BY
	h.store_no, w.ORG_CHN_NAME, h.transaction_date,
	tl.line_object, w.DFLT_CRNCY_CODE, h.transaction_category, tl.line_action, d.pos_discount_type, w.currency_id;



here are the indexes:


          Table                       Index                          Column
          Name                         Name                           Name
------------------------- ------------------------------ ------------------------------
AV_DISCOUNT_DETAIL        AV_DISCOUNT_DETAIL_X0          APPLIED_BY_LINE_ID
                                                         AV_TRANSACTION_ID
                                                         LINE_ID
                                                         TRANSACTION_DATE

                          AV_DISCOUNT_DETAIL_X99         AV_TRANSACTION_ID



AV_TRANSACTION_HEADER     AV_TRANSACTION_HEADER_X0       AV_TRANSACTION_ID
                                                         EDIT_TIMESTAMP
                                                         REGISTER_NO
                                                         STORE_NO
                                                         TRANSACTION_DATE
                                                         TRANSACTION_NO
                                                         TRANSACTION_SERIES

                          AV_TRANSACTION_HEADER_X1       AV_TRANSACTION_ID
                                                         TRANSACTION_DATE

                          AV_TRANSACTION_HEADER_X99      AV_TRANSACTION_ID



AV_TRANSACTION_LINE       AV_TRANSACTION_LINE_X0         AV_TRANSACTION_ID
                                                         LINE_ID
                                                         TRANSACTION_DATE

                          AV_TRANSACTION_LINE_X2         REFERENCE_NO
                                                         TRANSACTION_DATE

                          AV_TRANSACTION_LINE_X99        AV_TRANSACTION_ID



CURRENCY_CONVERSION       CURRENCY_CONVERSION_X0         CURRENCY_CONVERSION_ID

                          CURRENCY_CONVERSION_X1         CURRENCY_CONVERSION_TYPE_ID
                                                         CURRENCY_ID
                                                         EFFECTIVE_DATE_FROM



DW_STORE_STATUS           DW_STORE_STATUS_X0             SALES_DATE
                                                         STORE_NO

                          DW_STORE_STATUS_X1             SALES_DATE
                                                         STORE_NO



  • Attachment: plan.txt
    (Size: 5.43KB, Downloaded 99 times)
Re: Slow Query [message #440340 is a reply to message #440339] Fri, 22 January 2010 11:52 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
Thanks for a formatted & reasonably detailed post.

>       LEFT JOIN currency_conversion v ON (w.currency_id = v.currency_id
>                       AND v.currency_conversion_type_id = 1
>                       AND v.effective_date_from <= h.transaction_date
>                       AND (v.effective_date_to >= h.transaction_date OR v.effective_date_to IS NULL)
>                       AND null IS NOT NULL)
>


since "AND null IS NOT NULL" will NEVER be true, why are these lines included?

I could be mistaken, but "dw_store_status dw" (TABLE) provides no data to SELECT clause.
Therefore it should not be included in the INNER JOIN clause,
but subordinated into WHERE clause.

[Updated on: Fri, 22 January 2010 11:53]

Report message to a moderator

Re: Slow Query [message #440357 is a reply to message #440340] Fri, 22 January 2010 15:31 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Could you post the Exlpain Plan please, or perhaps the TKPROF output of a trace - that would be even better.

See this manual for more details on these tools.

But if you want to try for a silver bullet, I reckon you need an index on AV_TRANSACTION_HEADER(TRANSACTION_DATE) or even better - on AV_TRANSACTION_HEADER(STORE_NO, TRANSACTION_DATE).

Ross Leishman
Re: Slow Query [message #440359 is a reply to message #440357] Fri, 22 January 2010 15:35 Go to previous message
dkranes
Messages: 25
Registered: February 2008
Location: Upstate New York
Junior Member
Ross, it is attached to the original post as plan.txt. Thank you.

David
Previous Topic: how to write INLINE SUM to this query
Next Topic: Command to add constraints
Goto Forum:
  


Current Time: Thu Sep 29 05:36:12 CDT 2016

Total time taken to generate the page: 0.19037 seconds