Re: Process consumes CPU and long time to finished

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: hernawan_at_makro.co.id
>
> 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: ListGuru_at_fatcity.com (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: baflmis_at_bajajauto.co.in

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: ListGuru_at_fatcity.com (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).