Re: Strangeness

Lee,

I've had similar experiences.

The problem is not PRO*C, but how the program is designed.

Is it by any chance written in C++? I once had the 'privilege' of administering
an the databases for an application written in C++. The software featured and award winning design, literaly. The OOP design was honored in some OOP magazine.

When you consider though that this wonderful OOP design treated every piece of data from the database as atomic, and retrieved them that way, you can begin to see the problem.

The average SQL*Net packet size was 200 bytes, sub optimal to say the least. This is because the app preferred to retrieve it's own information

from the database and do the joins in the software.

In a couple of hours this app could process all of 10k transactions, and generate several million TCP/IP packets in the process.

I suggested they move the app to the database server: this resulted in a 40% decrease in runtime.

We offered to rewrite the whole thing in PL/SQL, but that was a politically
incorrect suggestion.

Jared

Robertson Lee - lerobe <lerobe_at_acxiom.co.uk> Sent by: root_at_fatcity.com
03/08/02 01:28 AM
Please respond to ORACLE-L  

        To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
        cc: 
        Subject:        Strangeness

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: 
  INET: Jared.Still_at_radisys.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).