comments in line...
----- Original Message -----
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Thursday, January 15, 2004 11:04 AM
> <sarcasm>
> Wow, your buffer cache hit ratio for this query is better than
> 99.999999%! With a BCHR like that, how could you possibly tune this
> query to be better??
> </sarcasm>
>
> Now, seriously, since I know nothing about your application or
> environment, and you do not show execution plan, I can only offer some
> general thoughts.
>
> Table join order should be in the order of smallest to largest number of
> rows being returned, based on the non-join filter predicates, as they
> apply to each table. So, if you have a 1 million row table joining a
> 10,000 row table, but there is no non-join filter predicate on the
> 10,000 row table, and you have a primary key lookup on the 1 million row
> table, then you want the 1 million row table to be the driving table.
> Also, make sure that index access paths are available for your filter
> and join predicates, where possible.
I have never had a problem with join order with the CBO. I have yet to see
oracle pick a bad join order on its own as long as
I analyze the tables.
>
> Hope that helps get you pointed in the right direction,
>
> -Mark
>
>
> Mark J. Bobak
> Oracle DBA
> ProQuest Company
> Ann Arbor, MI
> "Imagination was given to man to compensate him for what he is not, and
> a sense of humor was provided to console him for what he is." --Unknown
>
>
> -----Original Message-----
> Sent: Thursday, January 15, 2004 4:05 AM
> To: Multiple recipients of list ORACLE-L
>
>
> 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 AEH.Set_of_Books_ID = :b1
AND AEL.AE_Header_ID = AEH.AE_Header_ID
>
> 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
>
1. post the explain plan
2. If your in 9i do a 10046 trace, if not run statspack just before running
this query, then just after running it and post
the top 5 wait events. Ill bet your writing a ton to your temp tablespace.
This causes massive slowdowns.
I dont think 'disk' accounts for temp tablespace writes. I could be wrong.
3. are your tables analyzed?
> 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: Bobak, Mark
> 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: Ryan
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).
