I would like to create a series of custom PL/SQL Packages/Functions/Procedu
While I appreciate that Oracle have used a few tricks for their own native builtins (NVL, GREATEST, etc..) that aren't available to general PL/SQL programmers my exagerated full table scan tests against a large table (500,000+ rows) show some pretty massive timing differences.
Test 1 completes in approximately 8 seconds, whereas Test 2 completes in 140+ seconds.
My understanding is that the SYS.STANDARD package defines the PL/SQL environment and is therefore ultimately that which is being called by the unqualified builtins, therefore I can only assume that the time difference is being in part introduced by object security verification (i.e. can the connected user execute the code they are trying to?) as well as the numerous Oracle only pragmas found in stdspec.sql and stdbody.sql (under ORACLE_HOME\RDBMS\ADMIN).
My question is therefore this, if I want to have custom PL/SQL Packages/Functions/Procedu
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
The closest you will be able to get to oracle builtin performance for a function is to use native compilation.
To mimic SYS.STANDARD and SYS.DBMS_STANDARD open-security you will need to create public synonyms for your functions and grant execute to public.
Note, 10g and 11g have different setups for native compilation (11g is much easier)
10g
http://download.oracle.com
11g
http://download.oracle.com
Thanks for the comments.
schwertner - Yes, I would expect to use a WHERE clause, indexes and tune the query in a real life scenario however my full table scan example is there purely to exaggerate the execution time difference (with a fast query you wouldn't see the difference whereas with a purposely horrendous example it is quite apparent).
sdstuber - I've previously tried native compilation and it does indeed makes a small difference, but unfortunately we are talking about shaving off a few seconds rather than getting close to the built in speed. I've found that using dbms_shared_pool.keep on the package in question improves over and above native compilation (again by a few seconds), but was hoping to get as close to the Oracle Builtin as possible. Understand your public synonym / grant on the package comment, unfortunately this still incurrs the delay.
security and performance are two different goals
the public synonym and grant are only to address the "available to everyone" problem
for performance, the best you can do is native compilation.
you can use other steps, like 11g result cache to help use the function and eliminate extra calls.
but for single-run speed, the best is native compilation.
of course, its possible you shouldn't be using a function at all. Are you trying to solve a specific performance issue? if so, what is it? If this is an "in-general" question, then compile natively and maximize the amount of caching you can do to reduce the loading and executions.
and, of course, all tuning suggestions above from schwertner apply to the work "inside" the function. If the function's internals aren't efficient, you nothing you do on the outside will help much.
I have no inside knowledge of ORACLE software but some things to keep in mind
because someone told me or I did see things that suggested a certain behaviour.
a. there is a performance penalty in switching from sql-engine to pl/sql-engine (for performing the function) and back.
Doing the test like
CREATE OR REPLACE PROCEDURE perform_test AS
v_date date;
BEGIN
FOR 1 .. 1000000 LOOP
v_date := to_date('01-JAN-2009');
END LOOP;
END;
/
--Test 2
CREATE OR REPLACE PROCEDURE perform_test AS
v_date date;
BEGIN
FOR 1 .. 1000000 LOOP
v_date := sys.standard.to_date('01-J
END LOOP;
END;
/
I expect a smaller difference
b. De sql-engine from 10g after is smart
A lookup with the arguments is just executed when arguments change.
So probably the to_date('01-JAN-2009') is evaluated just once
calling a package function even with the same arguments can give different result in subsequent
calls : it will be probably evaluated every time
search for the deterministic clause : maybe that will help
"b. De sql-engine from 10g after is smart"
this is true, 10g and above are "smarter" than previous versions but there are still costs.
On an 11.1.0.7 db I tested the scripts above on a test table of about 80000 rows and got approximately 6x recursive cpu.
I haven't determined yet where the recursive cpu is being consumed. Most of the other statistics were approximately the same.
Thanks for the comments, very useful.
The underlying performance issue I am investigating / trying to solve is that I am diectly wrapping some of the Oracle specific builtins within custom package/functions/procedur
The code (function extracted from custom package body) is of the form:
FUNCTION QCToDate(pkText VARCHAR2) RETURN DATE IS
BEGIN
RETURN TO_DATE(pkText);
END QCToDate;
All of our code (packages, functions, procedures, triggers, views, etc..) will then make use of the QCToDate function (which is overloaded within our package to facilitate 1, 2 or 3 parameters as per Oracle's builtin).
This wrapping may seem a little odd, but the aim is to make the code more database independent and I am therefore keen to ensure that the method used is as efficient as possible.
My tests showed that calling QCToDate or SYS.STANDARD.TO_DATE were about the same timewise (a long way off the non qualified TO_DATE) so put the difference down to an object security overhead.
I'll take another look at the native compilation and the 11g Results cache you mentioned sdstuber and run some more tests.
The effect you are worried about has a very simple explanation.
When you invoke
sys.standard.to_date('01-J
working not as user SYS (I gues this is the case)
the server does so called 'context switch'.
This means that it begins to work under SYS domain
of rights, priveleges and set of subroutins.
This takes time.
When the task is done a backward 'conetext switch'
to the user you use is done.
This also takes time.
The conclusion is not to use special package
calls for common available for every user
functions and procedures.
In your example there is no specialneed to
use the SYS package.
Business Accounts
Answer for Membership
by: schwertnerPosted on 2009-10-20 at 06:34:05ID: 25613898
The main thing in performance tuning is>
1. To analzye the JOIN conditions in the WHERE clause
2. To cretate appropriate indexes over the affected columns
3. To invoke the procedure for computing the statistics.
All this will allow the Optimiyer to build good and fast
execution plan.