RE: A performance problem

Venu,

You can work out the trace file name for Conc jobs. The OS process for a CM job is stored in the ORACLE_PROCESS_ID in FND_CONCURRENT_REQUESTS for that particular REQUEST_ID. You can then use this process number to generate the trace file in udump (normally
$ORACLE_HOME/admin/<DBSID>/udump/*<Os_proc>*.trc in the case of a UNIX based 11i DB server). Although this would have been just a SQL_TRACE (10046 Level 1), you can *still* run a tkprof on it to determine which SQL consumed the most time....

Hth,
John Kanagaraj
DB Soft Inc
Phone: 408-970-7002 (W)

Disappointment is inevitable, but Discouragement is optional!

• The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers **

>-----Original Message-----
>From: Potluri, Venu (CT Appl Suppt) [mailto:venu_potluri_at_ml.com]
>Sent: Monday, December 29, 2003 10:15 AM
>To: Multiple recipients of list ORACLE-L
>Subject: RE: A performance problem
>
>
>John,
>
>I can run this in our development environment and trace the
>job. But, the data is quite a bit larger in production. I
>can't really take on a refresh/clone now and the prodcution
>database is over 600GB
>in size. We do have trace for the job which was available
>because the program definition for this custom feed job has
>trace enabled in Apps. That trace file doesn't have any wait
>event information.
>This job does use db link. We know that for sure. I advised
>the developer who wrote this custom feed job to tune it but
>that is never a satisfactory answer for them.
>
>
>Venu Potluri
>
>-----Original Message-----
>John Kanagaraj
>Sent: Monday, December 29, 2003 12:35 PM
>To: Multiple recipients of list ORACLE-L
>
>
>Venu,
>
>Trying to solve the performance issue with a *single* job with
>Statspack is
>like searching for a needle in a haystack, especially in an Oracle Apps
>environment. You will need to trace the program *as it runs*,
>and if you
>cannot do that right now, see if you can clone the database to
>a test system
>and rerun it again. Btw, was this concurrent job an Oracle
>standard job or
>was it a custom program? Any recent changes or patches to the
>environment?
>Note that you *can* set trace (albeit just the plain vanilla
>level 1) on a
>Concurrent job in 11i... As for the DB Link, can you determine if this
>indeed does use a Dblink or it is from somewhere else... [See
>the problem
>with Statspack?!]
>
>John Kanagaraj
>DB Soft Inc
>Phone: 408-970-7002 (W)
>
>Grace - Getting something we do NOT deserve
>Mercy - NOT getting something we DO deserve
>Click on 'http://www.needhim.org' for Grace and Mercy that is freely
>available!
>
>** The opinions and facts contained in this message are
>entirely mine and do
>not reflect those of my employer or customers **
>
>>-----Original Message-----
>>From: Potluri, Venu (CT Appl Suppt) [mailto:venu_potluri_at_ml.com]
>>Sent: Monday, December 29, 2003 8:44 AM
>>To: Multiple recipients of list ORACLE-L
>>Subject: A performance problem
>>
>>
>>I have a performance issue in our 11.5.5 Oracle Apps
>>production environment (Oracle 8.1.7.4). A concurrent job that
>>feeds into another production envrironment (Oracle 9.2) and
>>runs less than an hour
>>typically suddenly took almost 20 hours to finish. The users
>>are as expected up in arms calling my head on a platter. I
>>looked at the statspack report for the database this job ran on.
>>
>>The Top5 Wait events were:
>>
>>Top 5 Wait Events
>>~~~~~~~~~~~~~~~~~
>>
>>Wait Event Waits
>>Time (cs) % Total Wt Time
>>---------------------------------------------------------------
>>--------------------------------------------
>>db file sequential read 15,978,336
>> 5,809,277 57.28
>>SQL*Net message from dblink 3,868
>>1,960,168 19.33
>>db file scattered read 2,460,279
>> 943,252 9.30
>>control file sequential read 907,148
>> 300,572 2.96
>>pipe put 2,033
>> 208,850 2.06
>>
>-------------------------------------------------------------
>>-> cs - centisecond - 100th of a second
>>-> ms - millisecond - 1000th of a second
>>-> ordered by wait time desc, waits desc (idle events last)
>>
>>
>> Avg
>>
>> Total Wait wait Waits
>>Event Waits Timeouts
>>Time (cs) (ms) /txn
>>---------------------------- ------------ ----------
>>----------- ------ ---------------------------------
>>db file sequential read 15,978,336 0
>> 5,809,277 4 970.3
>>SQL*Net message from dblink 3,868 0
>>1,960,168 5068 0.2
>>db file scattered read 2,460,279 0
>> 943,252 4 149.4
>>control file sequential read 907,148 0
>> 300,572 3 55.1
>>pipe put 2,033 2,032
>> 208,850 1027 0.1
>>
>>
>>
>>Breakdown of Wait time
>>
>>Event Time Percentage Avg.
>>Wait Per Execute Per User Call Per Transaction
>>db file sequential read 5809277 60.16%
>>0.36 0.68 8.22 8762.11
>>SQL*Net message from dblink 1960168 20.30% 506.77
>> 0.23 2.77 2956.51
>>db file scattered read 943252 9.77%
>>0.38 0.11 1.34 1422.70
>>control file sequential read 300572 3.11% 0.33
>> 0.04 0.43 453.35
>>pipe put 208850 2.16% 102.73
>> 0.02 0.30 315.01
>>
>>Here are the top SQL statements ordered by physical reads per
>>execute: (these two happen to belong to this long running job)
>>Statement Executes Physical Reads
>>Reads/Execute Hashs Value % of Total
>>INSERT INTO ML_MGMT_MCS_FEED SELECT /*+ ORDERED INDEX(MGNAL
>>ML_MGMT_DIST_NAT_AC_LKUP_X1) USE_MERGE(BAL)
>>*/SUBSTR(GLCC.SEGMENT3,1,6) CENTER,SUBSTR(MGNAL.GL11PROD_ACCOUNT,1,5)
>>ACCT,SUBSTR(GLCC.SEGMENT2,1,10)
>>NEW10,SUBSTR(GLCC.SEGMENT6,1,6)
>>PRODUCT,SUBSTR(GLCC.SEGMENT5,1,4)
>>TRANSTYPE,NVL(SUBSTR(MGNAL.GL11PROD_ACCOUNT,1,5
>> 13 9737644
>>749049.54 1419451399 30.18
>>SELECT DISTINCT
>>ENTITY,ACCOUNT,COST_CENTER,INTERCOMPANY,TRANSACTION_TYPE,PRODUC
>>T,LOCATION,CHANNEL,FUTURE,PERIOD_NAME,SUM(BAL)
>>BALAMOUNT,SUM(MTD) MTDAMOUNT FROM (SELECT DISTINCT
>>ENTITY,ACCOUNT,COST_CENTER,INTERCOMPANY,TRANSACTION_TYPE,PRODUC
>>T,LOCATION,CHANNEL,FUTURE,PERIOD_NAME,0
>>BAL,(ABS(NVL(MTD_TRANSACTION_DR_AMOUNT
>> 30 5839191
>>194639.70 2733501134 48.27
>>
>>I am not sure on how to interpret the SQL*Net message from
>>dblink wait event. Obviously we have a db link on this
>>database pointing to another production database into which
>>the data is being fed.
>>Does this wait event indicate a network issue more so than a
>>database issue? What else jumps out here? Thanks.
>>
>>
>>
>>Venu Potluri
>>Oracle Financials DBA
>>
>>
>>
>>--
>>Please see the official ORACLE-L FAQ: http://www.orafaq.net
>>--
>>Author: Potluri, Venu (CT Appl Suppt)
>> INET: venu_potluri_at_ml.com
>>
>>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: John Kanagaraj
> INET: john.kanagaraj_at_hds.com
>
>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: Potluri, Venu (CT Appl Suppt)
> INET: venu_potluri_at_ml.com
>
>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: John Kanagaraj
  INET: john.kanagaraj_at_hds.com

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).