I'm getting many old messages

-----Original Message-----
Sent: Thursday, March 14, 2002 4:59 PM
To: Multiple recipients of list ORACLE-L

Oracle 8.0.5.0.0
Tru64 4.0f

We have a process running here and without going into the detail of it we have a Pro C program that is taking ages to run updates and selects (2 hours to do 10000 records). The program was changed to PL/SQL and we suddenly were seeing 5 million records processed in 1 hour.

Is PL/SQL that much faster than Pro C. Can somone more in the know give me some hints ??

TIA Lee

> -----Original Message-----
> From: Lawlor Michael - mlawlo
> Sent: 05 March 2002 17:17
> To: Robertson Lee - lerobe; Fremaux Ian - ifrema; Khiroya Asit - akhiro
> Cc: Richardson Phil - pricha; Mathew Varghese - vmathe; Peters Roy -
> ropete
> Subject: RE: Roy's extract job
>
>
> Dunno, was hoping it might spark an idea.
>
> From the dark recesses of my mind, do I recall that if you have TWO_TASK,
> you always connect through the listener, even from the same box. Is it
> possible that it could have a bottleneck of some sort? Maybe that's
> impossible - I don't know
>
> Mick
>
> > -----Original Message-----
> > From: Robertson Lee - lerobe
> > Sent: Tuesday, March 05, 2002 5:17 PM
> > To: Lawlor Michael - mlawlo; Fremaux Ian - ifrema; Khiroya
> > Asit - akhiro
> > Cc: Richardson Phil - pricha; Mathew Varghese - vmathe;
> > Peters Roy - ropete
> > Subject: RE: Roy's extract job
> >
> > erm.... how ??
> >
> > -----Original Message-----
> > From: Lawlor Michael - mlawlo
> > Sent: 05 March 2002 17:11
> > To: Fremaux Ian - ifrema; Robertson Lee - lerobe; Khiroya
> > Asit - akhiro
> > Cc: Richardson Phil - pricha; Mathew Varghese - vmathe;
> > Peters Roy - ropete
> > Subject: RE: Roy's extract job
> >
> >
> > Something to do with the Oracle listener?
> >
> > M
> >
> > > -----Original Message-----
> > > From: Fremaux Ian - ifrema
> > > Sent: Tuesday, March 05, 2002 5:04 PM
> > > To: Robertson Lee - lerobe; Khiroya Asit - akhiro
> > > Cc: Richardson Phil - pricha; Mathew Varghese - vmathe;
> > > Peters Roy - ropete; Lawlor Michael - mlawlo
> > > Subject: RE: Roy's extract job
> > >
> > > Chaps,
> > >
> > > We have some information that may be of interest. We had this
> > > problem (discussed below) last week where a Pro-C program was
> > > running very slowly but when Lee monitored the database
> > > response time (SELECTs & UPDATEs) it was performing very
> > > quickly. We converted the code to PL-SQL and ran it yesterday
> > > and it flew along and processed 5 million records in 1 hour.
> > > This morning we tried the Pro-C version of the program again
> > > and after two hours it had processed less than 10K records.
> > > So we executed the PL-SQL again this afternoon and it has
> > > almost completed the 5 million rows in about 2 hours again.
> > >
> > > Any thoughts on the implications of this?
> > >
> > > Regards, Ian.
> > >
> > > -----Original Message-----
> > > From: Fremaux Ian - ifrema
> > > Sent: Friday, March 01, 2002 12:04 PM
> > > To: Robertson Lee - lerobe; Khiroya Asit - akhiro
> > > Cc: Richardson Phil - pricha; Mathew Varghese - vmathe;
> > > Peters Roy - ropete
> > > Subject: RE: Roy's extract job
> > >
> > > Yesterday it was executing the same code but without the
> > > UPDATE statement. I'd agree that the performance would be
> > > affected by having to do the UPDATEs but from the figures you
> > > observed it was executing the UPDATE statement over 1000
> > > times per second. It is performing a commit every 10K records
> > > although I don't know how long this is taking, but from what
> > > I can see in Toad the rate of increase in the number of times
> > > the UPDATE is executing indicates that the COMMIT is probably
> > > not the problem.
> > >
> > > -----Original Message-----
> > > From: Robertson Lee - lerobe
> > > Sent: Friday, March 01, 2002 11:57 AM
> > > To: Fremaux Ian - ifrema; Khiroya Asit - akhiro
> > > Cc: Richardson Phil - pricha; Mathew Varghese - vmathe;
> > > Peters Roy - ropete
> > > Subject: RE: Roy's extract job
> > >
> > > Erm...couldn't see the wood for the trees time. Why are
> you
> > > doing single updates per record. Were you doing this in
> > > batches yesterday ?
> > >
> > > If this is the case and unless I am mistaken, then a
> severe
> > > degradation in performance is the sort of thing I would
> expect
> > >
> > >
> > >
> > > -----Original Message-----
> > > From: Fremaux Ian - ifrema
> > > Sent: 01 March 2002 10:00
> > > To: Khiroya Asit - akhiro
> > > Cc: Robertson Lee - lerobe; Richardson Phil -
> pricha;
> > > Mathew Varghese - vmathe; Peters Roy - ropete
> > > Subject: RE: Roy's extract job
> > >
> > > Hi Asit,
> > >
> > > This program is still running like a dog,
> yesterday 1million
> > > row processed per hour and today only 20K. I
> suspect it may
> > > be a problem with the ProC program, the program
> has been
> > > modified since yesterday to perform a single
> update for each
> > > record processed, the statement for which is
> shown below. The
> > > table being updated has a primary index on
> mobile number but
> > > from the processing rates we are getting I
> suspect it may not
> > > be using it, can you find out for us please. The
> variable
> > > used to store the mobile number is an element in
> a character
> > > array and the mobile number column is a
> varChar2, will Oracle
> > > use this or does the variable need to be
> converted in the
> > > where clause?
> > >
> > > SELECT
> > > MDA_Extract set
> > >
> nickname=:b0,voice_directory=:b1,electronic_directory=:b2,www_
> > >
> directory=:b3,Mdq_other_1=:b4,partial_address=:b5,degender_fla
> > > g=:b6 where mobile_number=:b7
> > >
> > >
> > > -----Original Message-----
> > > From: Richardson Phil - pricha
> > > Sent: Friday, March 01, 2002 9:50 AM
> > > To: Fremaux Ian - ifrema
> > > Cc: Khiroya Asit - akhiro; Robertson Lee -
> lerobe
> > > Subject: RE: Roy's extract job
> > >
> > > OK, prioirity upped..
> > >
> > > -----Original Message-----
> > > From: Fremaux Ian - ifrema
> > > Sent: 01 March 2002 09:39
> > > To: Richardson Phil - pricha
> > > Cc: Khiroya Asit - akhiro; Robertson Lee -
> lerobe
> > > Subject: RE: Roy's extract job
> > >
> > > Hi Phil,
> > >
> > > We believe it is PID 229961.
> > >
> > > Ian.
> > > -----Original Message-----
> > > From: Richardson Phil - pricha
> > > Sent: Friday, March 01, 2002 9:39 AM
> > > To: Fremaux Ian - ifrema
> > > Cc: Khiroya Asit - akhiro; Robertson Lee -
> lerobe
> > > Subject: RE: Roy's extract job
> > >
> > > Asit/Lee
> > >
> > > Could you please check the Oracle connections on
> CLyde &
> > > pinpoint the PID for Roy's job....
> > >
> > > THanks
> > >
> > > Phil
> > >
> > > -----Original Message-----
> > > From: Fremaux Ian - ifrema
> > > Sent: 01 March 2002 09:32
> > > To: Richardson Phil - pricha
> > > Subject: RE: Roy's extract job
> > >
> > > Hi Phil,
> > >
> > > So far this doesn't appear to have made any
> difference. Roy's
> > > job will have opened a connection to Oracle
> which I believe
> > > appears as a separate job, is it worth nicing
> the Oracle
> > job as well?
> > >
> > > Regards, Ian.
> > >
> > > -----Original Message-----
> > > From: Richardson Phil - pricha
> > > Sent: Friday, March 01, 2002 9:25 AM
> > > To: Fremaux Ian - ifrema; Peters Roy - ropete
> > > Cc: Boddy Bill - bboddy
> > > Subject: Roy's extract job
> > >
> > > Gents,
> > >
> > > I have upped the priority on Roy's extract -
> process 229958
> > >
> > > The box is running very lightly at the moment,
> so I've given
> > > this job the maximum priority possible - Bill is
> checking out
> > > some BMC graphs from overnight as we speak.
> > >
> > > Phil
> > >
> > >
> > >
> > > Phil Richardson
> > > ACXIOM Limited
> > > email : phil.richardson_at_acxiom.com
> > > tel: 0191 5257433
> > >
> > >
> > >

The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please re-send this communication to the sender and delete the original message or any copy of it from your computer system.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Robertson Lee - lerobe
  INET: lerobe_at_acxiom.co.uk

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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.com
-- 
Author: Khedr, Waleed
  INET: Waleed.Khedr_at_FMR.COM

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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).