hi you write query in order of largest,larger,large table fashion and input parameter column may be indexed
tlw hernawan wrote: > Hi all, > > I have process in Oracle apps 11.5.8 which need > very lot CPUs and long time to complete. > for about 17,000 invoices it takes 28 hours !! > > I have open TAR since month ago, and still get no solution. > maybe here someone can share any idea ....? > im using 11.5.8, sparc. DB 9i rel2 > > here is from the tkprof : > > SELECT sum(nvl(entered_cr,0) - nvl(entered_dr,0)) , > sum(nvl(accounted_cr,0) - nvl(accounted_dr,0)) > FROM AP_AE_Lines AEL, > AP_AE_Headers AEH, > AP_Invoice_Payments AIP > WHERE AIP.Invoice_ID = :b2 > AND AEL.Source_ID = AIP.Invoice_Payment_ID > AND AEL.Source_Table = 'AP_INVOICE_PAYMENTS' > AND AEL.AE_Line_type_code = 'LIABILITY' > AND AEL.AE_Header_ID = AEH.AE_Header_ID > AND AEH.Set_of_Books_ID = :b1 > > call count cpu elapsed disk query current > rows > ------- ------ -------- ---------- ---------- ---------- ---------- > ---------- > Parse 1 0.00 0.00 0 0 0 > 0 > Execute 1539 0.23 0.31 0 0 0 > 0 > Fetch 1539 16474.95 21810.67 24 46864854 0 > 1538 > ------- ------ -------- ---------- ---------- ---------- ---------- > ---------- > total 3079 16475.18 21810.99 24 46864854 0 > 1538 > > Misses in library cache during parse: 0 > Optimizer goal: CHOOSE > Parsing user id: 24 (recursive depth: 1) > > tq > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: hernawan > INET: [EMAIL PROTECTED] > > Fat City Network Services -- 858-538-5051 http://www.fatcity.com > San Diego, California -- Mailing list and web hosting services > --------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: MIS DEPT -BAFL- INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
