Re: RE: A performance problem

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)" <venu_potluri_at_ml.com>
> Date: 2003/12/29 Mon PM 01:09:29 EST
> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> 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-----
> ryan_oracle_at_cox.net
> 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)" <venu_potluri_at_ml.com>
> > Date: 2003/12/29 Mon AM 11:44:24 EST
> > To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> > 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: 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: <ryan_oracle_at_cox.net
> INET: ryan_oracle_at_cox.net
>
> 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: <ryan_oracle_at_cox.net
  INET: ryan_oracle_at_cox.net

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