Re: Oracle Efficiency Question.

hmmm...

I did some tests a little while ago that measured the differences between PL/SQL and proC. The results aren't scientific (by any stretch of the imagination) but I did try to measure purely the sql (or Oracle DB interaction) and not actual time to process coding statements.

The system under test was a sun 220R and Oracle 8.1.7

I used array processing for my tests and array size were set to 1,10,100,1000 and 10,000. I processed a million rows for each test.

I found that on Average:
For Selects were much faster (60%) in PL/SQL than proC Updates proC was about 25% faster than PL/SQL. Insert proC was about 50% faster than PL/SQL Deletes were about even.

Also for array sizes of 1 were inefficient in PL/SQL particularly in the insert case. I've never cheked to see if it was a coding error (but I don't think so). Array processing in PL/SQl is still a bit buggy (you have to do some strange things to get them to work).

On the whole I would suggest you use proC (unless it is pure SQL). Normal C code runs about 64 times faster than PL/SQl code.

As for your other question your parsed sql statements will be kept in the shared SQL pool (which is set by the shared_pool_size in the init.ora). The statement that hasn't been processed for the longest length of time is removed first from the Library cache. So check your library cache reload/pin ratio. If the percentage is large (I use above 1%) increase your shared pool size.

Cheers
Blair

"DCCoolBreeze" <aa_at_bb.net> wrote in message news:<9po3dp$5n3$1_at_bob.news.rcn.net>...
> Lets assume that I have a proC application that performs some oracle
> operations. The execuable is located on the same server as the database so
> there are no network transport issues. The question is will I get better
> performance if I convert the oracle parts of the application to PL/SQL? The
> application consists of SELECT, INSERT, DELETE and UPDATE and, of course,
> consists of condition statements.
>
> This is my feelings. I will experience better performance for the following
> reasons:
>
> 1. PL/SQL is compiled and the ProC is not so I will save the compile
> time
> 2. PL/SQL works in the oracle address space whereas my application
> works out of
> it's address space so Oracle will have to move results from its
> address space to
> the application's address space.
> 3. The application consists of objects and therefore it contains
> additional instructions
> connected to objects whereas PL/SQL does not have this additional
> overhead.
>
> First, am I on track and if so, will I save a significant amount of time
> converting to PL/SQL or will the savings be negligable. Now I know that the
> answer to this question is a function of the hardware and the amount of
> transaction processing I am performing so I will state that I am running on
> a 24 processor system and the application processes about 1 billion records
> a day.
>
> Oh yes, one other question. The application performs the same oracle
> transaction using bind variables, so Oracle will continue to use the saved
> statement. How long does Oracle hold a compiled transaction statement???
Received on Tue Oct 09 2001 - 22:56:50 CEST