Ask the Expert

Performance problem when running a query

I am facing a performance problem when running one query. Even after indexing the query, it takes around one hour to execute. Table INVOICE_LINE has 7181561 rows and it is partitioned by range. Remaining tables in join are small tables. Could you please help me tune the below query?
SELECT  count(1)
  FROM  datap.ITEM_MASTER,
	datap.DB_MASTER,
	datap.FISCAL_WEEK,
	datap.CUSTOMER_MASTER  CUSTOMER_MASTER_SOLD,
	datap.FISCAL_WEEK  DUE_WEEK,
	datap.INVOICE_LINE
 WHERE  INVOICE_LINE.ITEM_ID = ITEM_MASTER.ITEM_ID  
   AND  INVOICE_LINE.FISCAL_WEEK = FISCAL_WEEK.FISCAL_WEEK  
   AND  INVOICE_LINE.DB_CODE = DB_MASTER.DB_CODE  
   AND  CUSTOMER_MASTER_SOLD.CUST_ID=INVOICE_LINE.CUST_SOLD_ID  
   AND  INVOICE_LINE.DUE_DATE >= DUE_WEEK.FISCAL_DT_START 
   and  INVOICE_LINE.DUE_DATE < DUE_WEEK.FISCAL_DT_END  ;

Explain Plan statistic:

OPERATION                      OPTIONS         OBJECT_NAME
------------------------------ --------------- --------------------
SELECT STATEMENT
SORT                           AGGREGATE
NESTED LOOPS
NESTED LOOPS
HASH JOIN
INDEX                          FAST FULL SCAN  PK_ITEM_MASTER
HASH JOIN
MERGE JOIN                     CARTESIAN
TABLE ACCESS                   FULL            FISCAL_WEEK
BUFFER                         SORT
INDEX                          FULL SCAN       PK_DB_MASTER
PARTITION RANGE                ALL
TABLE ACCESS                   FULL            INVOICE_LINE
INDEX                          UNIQUE SCAN     PK_FISCAL_WEEK
INDEX                          UNIQUE SCAN     PK_CUSTOMER_MASTER

    Requires Free Membership to View

I see a couple of possible problems that you should address:

1) The full scan on INVOICE_LINE is ignoring the partitioning (note the line in the explain plan which says PARTITION RANGE ALL). This indicates that your partition bounds/partition indexing is not adequate to place all similar rows into a single partition. My question here is, what are your partition bounds? Do you use fiscal_week, due_date or what as your partition bounds? If you've set up partition bounds that don't have some logical data divisions, then the optimizer won't be able to "prune" the unneeded partitions. By having to full scan the seven million plus INVOICE_LINE table, you've pretty well assured that your performance is going to stink.

2) What's up with the CARTESTIAN join? Do you really want that? You may need to check your join columns in the where clause to make sure that you are joining the tables correctly and on the proper columns. Anytime I see CARTESIAN in a plan, I want to know why it's there and if there's not an actual reason it's needed, I get rid of it.

Finally, you didn't mention what indexes you created that you expected to help. If you created indexes that aren't being used OR aren't being used properly (i.e. they actually help improve performance), then you need to look at dropping those indexes and creating ones that work.

Try looking into both these areas and see if you can't make progress from there.

This was first published in January 2004

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: