Re: PL/SQL Performance

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1998/05/18
Message-ID: <35603d70.5155963@192.86.155.100>#1/1

A copy of this was sent to Lynn Ranen <ranen_at_ix.netcom.com> (if that email address didn't require changing) On Sat, 16 May 1998 21:19:27 +0100, you wrote:

>Hi All --
>
>Iâ€™m still having an uphill battle with performance issues. Last week, I
>posted for help with a â€œlong queryâ€. Since then, I have looked harder at the
>issue and find that overall performance is horrible and completely
>unacceptable. Frankly, I canâ€™t believe that the level of performance I am
>seeing could be considered acceptable by a company that did over $6 billion
>in sales last year.
>
>Therefore, I must assume that something is terribly wrong with the system.
>
>I developed a benchmarking package to time and record every function call in
>the most troublesome procedure. After noticing that most function/procedure
>calls (except those accessing DB tables) took about 0.40 seconds, I became
>suspicious. I decided to devise a â€œfoolproofâ€ test to see what is REALLY
>going on. The variable assignment,
> FOO := 1; where FOO is a NUMBER,
>also takes between 0.40 and 2.47 seconds. Unbelievable!!! Imagine how this
>affects the entire application when itâ€™s stuck inside of a 900 iteration
>loop.
>

I went and found the slowest machine I could to try the same on. A single 50mhz sparc running oracle7.1, 7.2, 7.3, 8.0, and 8.1 with 64m of RAM installed.

I ran:

$ sqlplus tkyte/tkyte

Connected to:
Oracle7 Server Release 7.3.3.0.0 - Production Release With the distributed, replication, parallel query and Spatial Data options PL/SQL Release 2.3.3.0.0 - Production

SQL> set timing on
SQL> declare
  2 foo number;
  3 begin
  4 foo := 1;
  5 end;
  6 /
PL/SQL procedure successfully completed. Elapsed: 00:00:00.06
SQL> /
PL/SQL procedure successfully completed. Elapsed: 00:00:00.01
SQL> /
PL/SQL procedure successfully completed. Elapsed: 00:00:00.01
SQL> /
PL/SQL procedure successfully completed. Elapsed: 00:00:00.01
SQL> /
PL/SQL procedure successfully completed. Elapsed: 00:00:00.01
SQL> /
PL/SQL procedure successfully completed. Elapsed: 00:00:00.01

So, the first time it ran, it took 6/100's of a second (had to parse and compile the code the first time around and all). Each subsequent time took 1/100's of a second.

So I agree with your comment that something with your system is very wrong. You could have saved yourself LOTS of time 'timing' everything if you would have used TKPROF to analyze statistics generated by the database.

Here is a couple of things you need to do:

tell us about your machine. What are you running. Fine, you quadrupled the ram but from what to what? You added a CPU, what kind are you running. Is this NT, Unix, Mainframe??
tell us about your database. Whats your SGA look like. Whats your db_block_buffers set to? Have you tuned the database at all?
tell us about your disks? what are they, how are they attached?
tell us about your environment alltogether? whats the load on the machine? is the database the only thing on it? what does sar, vmstat or perfmon tell you about the machine?

>This is Oracle 7.3.x running on Solaris. Last week we quadrupled the RAM and
>added another CPU. Neither of these had any affect on the times recorded.
>Iâ€™ve timed and tuned EVERY call to the DB. the customer is very unhappy and
>wonâ€™t even use the product (sound familiar?).
>

Not really, how did you tune the queries? do you use "sql_trace=true", "timed_statistics=true" and tkprof? You say you tuned every call to the DB, How?

>Has anyone performed similar benchmark tests? How were youâ€™re times? I am
>thinking to upgrade to Oracle 8 and just forget the whole issue. Will this
>help me? My company just got a $10 million contract and I am REALLY hesitant
>to develop another product on Oracle if this is the performance I can
>expect. Any and all feedback appreciated.
>
>Lynn
>PS: for those who are interested or assisted me with query advice. Time
>results follow.

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA

http://govt.us.oracle.com/ -- downloadable utilities

Opinions are mine and do not necessarily reflect those of Oracle Corporation

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Mon May 18 1998 - 00:00:00 CDT