MTL_MATERIAL_TRANSACTIONS HAS POOR Performance!
Posted: 21 July 2006 04:31 AM
Newbie
Rank
Total Posts:  3
Joined  2006-07-18

We have developed a custom report that updates a custom table based for what is returned from the following query:  SELECT
          nvl(sum(mt.transaction_quantity),0)
        FROM inv.mtl_transaction_types mtt,
            inv.mtl_material_transactions mt
        WHERE   mt.transaction_type_id = mtt.transaction_type_id
          AND trunc(mt.transaction_date) BETWEEN v_usage_start_date AND v_usage_end_date
         
It is literally taking hours to run.  We have created indexes, change the design of the report, tuned the sql… all with some improvement.  Someone has has suggested, partitioning the table (across multiple devices).  Does anyone have any experience w/ this method?  Or can make a better suggestion?

Thanks.

Profile
 
 
Posted: 07 August 2006 01:28 AM   [ # 1 ]
Newbie
Rank
Total Posts:  6
Joined  2006-08-03

dear
  I have also got a same type of problem.Tried many a ways.no result.
  At last I did one thing i made a customised table and wrote a procedure and scheduled it at every night at 12 which will insert into the table datas and the sysdate in such date that i need only select * from <table> where date between<> and <>  and ....... to run the report
  now it runs within a twinkle of eye.

Profile
 
 
Posted: 07 August 2006 10:16 PM   [ # 2 ]
Sr. Member
Avatar
RankRankRankRank
Total Posts:  149
Joined  2005-04-11

How many records are in your MTL_MATERIAL_TRANSACTIONS table?  How many distinct transaction_type_id’s are there?  What is the range of dates the report is typically selecting from?  Is this typically one day, a week, a month?  You should do some analysis on these columns before you go down the partitioning path.

Regards,
Michael Siebert

 Signature 

Regards,

Michael Siebert

Profile
 
 
Posted: 23 August 2006 08:43 AM   [ # 3 ]
Newbie
Avatar
Rank
Total Posts:  11
Joined  2005-05-06

Just wondeting why do you need inv.mtl_transaction_types table.
You may need to consider removing trunc in your where clause.

Profile