RE: Anyone implented compiled PL/SQL?
- From: "Ellis R. Miller" <sartre1@xxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 28 Jul 2004 11:44:44 -0600
Looks like you performed a valid and respectable amount of testing. Based on
the white papers I have read your results demonstrating that NCOMP doesn't
provide a significant performance improvement for that particular
application seems completely reasonable.
Yes, there was significant improvement: in the end, almost 50,000 records
were being processed in under 2 hours as opposed to the 3,000 that were
originally taking up to 12 hours. The bulk of the performance improvement
was due to extensive rewriting of the application PL/SQL packages and
procedures identified, leveraging tracing and BMS_PROFILER. I found there
was around a 15% to 20% performance improvement in the nightly batch
processing attributable to Oracle 9i NCOMP. Please note, their SLA required
them to load up to 50,000 records a night completing before 8am the next
morning as it was a hybrid application...intensive OLTP during the day,
batch processing at night. Keep in mind, I did not test or natively compile
or test any of the PL/SQL outside of the packages used for the nightly batch
processing.
McKesson never officially approved my testing (nor paid me my last check).
Thus, most of my testing was on the sly and not in the type of stable,
controlled environment in which I could scientifically test i.e. not
necessarily change more than one variable at a time . Nevertheless, I was
able to use new baselines for purposes of isolating the performance benefits
of NCOMP.
By the way, good job in terms of the benchmarking. Other than my notes,
which could incriminate me, there is littel value I can add to your
benchmarking...even the white paper I sent you along with other links
details or validates what you have described: like Viagra, NCOMP isn't
necessarily appropriate for all Oracle applications. Someday, however, if
some nutcase implements NCOMP globally we can all learn a valuable lesson
regarding the additional administration required for what otherwise would be
a no-lose proposition, that is, NCOMP, in general, when ALL PL/SQL is
natively compiled, can only improve application performance.
Personally, I would like to use a tool like LoadRunner in a FDA approved
test environment someday. However, those tools do cut into the doughnut
budget and would often make Mary Jane feel bad about her lack of a high
school education...despite her more than 20 years of relational database
experience:)
Ellis
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Anthony Molinaro
Sent: Wednesday, July 28, 2004 10:02 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: Anyone implented compiled PL/SQL?
Ellis,
> Even using Oracle collections such as nested tables, arrays, etc. a
benchmark in which several stored procedures are called and=20
> generally completes in seconds, anyway, isn't going to be very
representative.
Agreed. The benchmarks I ran consisted of 500 concurrent sessons hitting
the db:
150 sessions from python (through dcoracle2)
150 sessions from java (through oci drivers)
200 sessions from sqlplus (through shell scripts)
All sessions performing tons of dml on 12 tables.
All sessions reading clobs into temp clobs, doing string manipulations
and loading them back in.
Tables sizes ranged from 1000 rows to 5 million (no partitioning)
While the benchmark was not huge, I thought there was enough work there
to make it a valid test.
> I have a couple references to white papers/case studies in which are
very realistic and explicit in explaining the performance=20
> benefits of NCOMP. Otherwise, there is no downside, in my humble
opinion, and Oracle 10G makes NCOMP mindless to implement.=20
I think I've read them all. I'm more interested in what you did
specifically because I haven't seen much improvement firsthand
(everything looks good in whitepapers). Not to say I haven't seen any,
just not a dramatic improvement.
> I have used Oracle 9i NCOMP on Solaris 9 as well as Oracle 10G NCOMP
on Fedora (Linux). It provides anywhere from a 30 to 40%=20
> improvement in application response time depending on the intensity of
the application stored procedures, etc.
Was this improvement from something you read or did? You mentioned a
McKession app, so, I'm assuming you did tons of
(non-sql) calculations? Financials?
I was really excited about NCOMP but after the tests, it seemed
interpreted pl/sql is optimized to be only slightly slower
(for the specific things I was testing). It's fine if it's on your Ultra
5. I'm just curious what you did to observe
*any* significant improvement.
> Fascinates me to this day how there are those who perceive "advanced"
functionality as risky but the flagrant abuse of things such > as the
use of bind variables, packages, Veritas Quick I/O, etc. as a safe,
standard industry practice. Thus, I wouldn't go > =20
> charging into testing NCOMP at my local IT department as it may incite
a riot or a series of heated debates about who has the most=20
> seniority and why Johnny wasn't told about Bob wantin' time off for
his hip operation...cause we go testin' this NCOMP while we=20
> got some of our top talent off at the Mayo and that's how things start
to change (the "C" word)...
=20
Huh? Where'd that come from?
- ant
-----Original Message-----
From: Ellis R. Miller [mailto:sartre1@xxxxxxxxxxx]=20
Sent: Wednesday, July 28, 2004 12:44 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: Anyone implented compiled PL/SQL?
In terms of the benchmarking I have done, personally, it was faster
(sometimes significantly) but I don't count my Ultra 5 as a true test
environment:) I have tested on one or two commercial applications but
not necessarily...I do a lot of testing on my own.
With regard to the Oracle white papers I have perused, a half dozen or
so, and the "Uncle Larry" site I referenced in the prior email the
performance improvement was dramatic. However, these were commercial
applications running on enterprise servers being tested for scalability.
In other words, all things being equal one person kicking off one batch
process to test loading 50 records is probably not going to record a
dramatic performance improvement. Even using Oracle collections such as
nested tables, arrays, etc. a benchmark in which several stored
procedures are called and generally completes in seconds, anyway, isn't
going to be very representative. On the other hand, take a McKesson
application that was taking 10 hours to process 3,000 records having an
elaborate rule-based engine comprised of hundreds of packages, stored
procedures, and functions and there was a noticeable improvement.
I have a couple references to white papers/case studies in which are
very realistic and explicit in explaining the performance benefits of
NCOMP. Otherwise, there is no downside, in my humble opinion, and Oracle
10G makes NCOMP mindless to implement.
Ellis
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Anthony Molinaro
Sent: Wednesday, July 28, 2004 9:15 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: Anyone implented compiled PL/SQL?
Ellis,
That's interesting that you mentioned the 30 - 40% improvement. I
benchmarked ncomp for 10 days (albeit, using procs and functions that
contain mostly sql with some object type manipulations ) and saw almost
no performance improvement.
Also, adding to what you mentioned about mixing interpreted and ncomp'ed
procs, I ran into several errors when using UDTs that were interpreted
and referenced by a proc/func that was ncomp'd.
What are you doing in your code that provides such a huge performance
gain? Is it mostly pl/sql with very little sql references?=3D20
The benchmark I performed was on 9.2.0.1 and 9.2.0.4 on redhat AS 3.0
The procs I used contained mostly:
- sql
- tons of string parsing
- associative arrays
Regards,
- ant
-----Original Message-----
From: Ellis R. Miller [mailto:sartre1@xxxxxxxxxxx]=3D20
Sent: Wednesday, July 28, 2004 12:16 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: Anyone implented compiled PL/SQL?
Mark,
I have used Oracle 9i NCOMP on Solaris 9 as well as Oracle 10G NCOMP on
Fedora (Linux). It provides anywhere from a 30 to 40% improvement in
application response time depending on the intensity of the application
stored procedures, etc. Nevertheless, it is always faster. However,
there are some caveats regarding native compilation of only some stored
procedures, functions, etc. that are natively compiled by other stored
procedures, for example, that are not. In general, don't do it, that is,
either group those that are natively compiled, logically, or natively
compile ALL applicaton PL/SQL.
There used to be a study, "C, Java, and PL/SQL: A language platform
suitability study" comparing Java, Java (NCOMP), PL/SQL, PL/SQL (NCOMP),
and C relative performance. The latter being the fastest, of course, but
PL/SQL
(NCOMP) being a "close" second and significantly faster than PL/SQL.
However, the link is currently dead.
On a final note, the Oracle 10G NCOMP is much easier to configure: do
not require an external c compiler, such as gcc, or make utility. In
essence, only have to set one parameter. Here are some really basic
notes on NCOMP in Oracle 10G:
Which Oracle parameters are relevant to native compilation?
---------------------------------------------------------------
1. plsql_native_library_dir -- create directory to hold shared libraries
2. plsql_native_library_subdir_count -- optional 3. plsql_code_type --
set at the database level or session
plsql_native_library_dir parameter
----------------------------------
This parameter specifies the location of the directory where the OS copy
of shared libraries (
DLLs) are kept.
When a module is natively compiled, the shared libary is created in this
location and then copied into the database dictionary table
(ncomp_dll$). Although the master copy of the shared library resides in
the database, the shared objects are also materialized in the file
system so that they can be dynamically loaded into the Oracle's address
space.
Note 1: Users (DBAs) must never delete the shared libraries
manually from the
plsql_native_library_dir when the system is up and running as
these DLLs may be mapped to Oracle
processes. The only time it is safe to delete the OS copy of a
shared library is when the system
is down.
Note 2: In RAC configuration, this parameter must be set in each
instance. The instances are not
required to have a shared file system. On each instance the
plsql_native_library_dir can be set
to point to an instance local directory. Alternately, if the RAC
configuration supports a shared
(cluster) file system, you can use a common directory (on the
shared file
system) for all
instances.
Note 3: You must create this directory. Oracle will not
automatically create this directory for
you.
Note 4: Oracle Corp does not support NFS mounted directories for
plsql_native_library_dir either
in a single instance or a RAC case. This is because NFS causes
some unpredictable timing errors
while writing or deleting files.
Use the following syntax to set this parameter:
--------------------------------------------------
For native compilation mode:
SQL>alter session set plsql_code_type=3D3D'NATIVE'
or
SQL>alter system set plsql_code_type=3D3D'NATIVE'
For interpreted mode:
SQL>alter session set plsql_code_type=3D3D'INTERPRETED'
or
SQL>alter system set plsql_code_type=3D3D'INTERPRETED'
The shared libraries generated by native compilation are stored in the
database as BLOBs in the ncomp_dll$ dictionary table.
SQL> connect / as sysdba;
SQL> describe ncomp_dll$;
Name Null? Type
------------ -------------- --------
OBJ# NOT NULL NUMBER
VERSION NUMBER
DLL BLOB
DLLNAME RAW(1024)
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Bobak, Mark
Sent: Wednesday, July 28, 2004 8:48 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: Anyone implented compiled PL/SQL?
Hi,
As part of our move to 9i, we're looking at various features, and I'm
wondering about compiled PL/SQL. Does anyone have any experience with
it? Any major issues or problems? Is there any gold at the end of the
rainbow? (Does it actually provide noticeable performance improvement?)
Thanks,
-Mark
PS Oracle 9.2.0.5 (64-bit) on Solaris 8.
--
Mark J. Bobak
Oracle DBA
ProQuest Company
Ann Arbor, MI
"On two occasions, I have been asked [by members of Parliament], "Pray,
Mr. Babbage, if you put into the machine wrong figures, will the right
answers come out?' I am not able to rightly apprehend the kind of
confusion of ideas that could provoke such a question."
-- Charles Babbage (1791-1871)
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put
'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put
'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put
'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put
'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
