you mean a dbms_job? execute immediate 'turn trace on'
inside what ever is being called. then check it. or just run it manually. > > From: "Potluri, Venu (CT Appl Suppt)" <[EMAIL PROTECTED]> > Date: 2003/12/29 Mon PM 01:09:29 EST > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > Subject: RE: A performance problem > > The other database in on a different server. > > I looked at the statspack report for the other database, for the time period in > question. > > Top 5 Timed Events > ~~~~~~~~~~~~~~~~~~ % Total > Event Waits Time (s) Ela > Time > -------------------------------------------- ------------ ----------- > ------------------- > db file sequential read 5,802,489 48,722 44.14 > free buffer waits 31,015 26,670 24.16 > db file parallel write 9,817 12,298 11.14 > CPU time 7,020 6.36 > write complete waits 6,301 5,584 5.06 > > We do have increase in amount of data but not enought to account for a 20-hour run. > > I am looking at the statspack report during the times this job previoulsy ran. > > How do I enable 10046 trace for sql executed by a concurrent job? I do have a trace > file for this job but it was obtained by turning trace on in Oracle Apps for this > job and doesn't contain any wait > event information. > > > > -----Original Message----- > [EMAIL PROTECTED] > Sent: Monday, December 29, 2003 12:09 PM > To: Multiple recipients of list ORACLE-L > > > the sqlnet is a network issue. talk to your SAs. is the other database on a > different server? work from there. > > your big one is your read. could mean your SGA is too small. is anything else > running at this time? > > are you sure there is an equivalent amount of work to do? are you sure there isnt > more data involved? > > do you have a previous statspack report to compare it to? > you also need to run a 10046 trace on the queries involved and see what they are > doing. > > maybe the plan changed do to a change in data or you dont have accurate statistics > or a parameter setting changed? > > > > From: "Potluri, Venu (CT Appl Suppt)" <[EMAIL PROTECTED]> > > Date: 2003/12/29 Mon AM 11:44:24 EST > > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > > 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,PRODUCT,LOCATION,CHANNEL,FUTURE,PERIOD_NAME,SUM(BAL) > > BALAMOUNT,SUM(MTD) MTDAMOUNT FROM (SELECT DISTINCT > > ENTITY,ACCOUNT,COST_CENTER,INTERCOMPANY,TRANSACTION_TYPE,PRODUCT,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: [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: <[EMAIL PROTECTED] > 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: Potluri, Venu (CT Appl Suppt) > 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: <[EMAIL PROTECTED] 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).
