On my recent project we have a large package holding constants values. From a design point of view this is a very elegant solution because this way the constants are all defined in a centralized way. But the usage of this package lead to enormous performance issues. This post tells why we had these problems and how we solved them.
An example of a constants package could be:
create or replace package my_constants is my_name constant varchar2(30) := 'Andre'; end; /
One of the problems is that package constants cannot be used in Oracle Forms or directly from SQL. So a function was written that dynamically retrieved the constants value from the package. This function can be used in Forms and SQL,
so that problem seemed to be solved. An implementation of a function that retrieves the constants value can be:
function get_my_constant
(p_constant in varchar2
)
return varchar2
is
l_return varchar2(2000);
begin
execute immediate ('begin :1 := '||p_constant||'; end;') using in out l_return;
return l_return;
end get_my_constant;
And now the horror story starts. This function was being used inappropriately. Supposed to be used only in Forms, but used in a lot of packages. The statspack report counted well over 30 million calls to a single value from the package using this function. (Granted the Statspack report covered 28 hours, but still)
Performance of the system was becoming so poor that production problems were beginning to occur. Every call to the function results in a hard parse which is very expensive. The best way, of course, was to remove the function call whenever possible. But the application is so big, that this is not possible in a short timeframe.
The best way was to rewrite the function. Was there a way to make the function perform better? This way we didn’t have to touch the rest of the code. I shortly thought of using deterministic functions. But that is impossible in combination with execute immediate.
The final solution was as follows:
- Lookup the requested constant value in a database table. If the value exists, return this value. This way no dynamic SQL is needed.
- When we can’t find the value in the database table. perform the Dynamic SQL and store it in the table for later use.
The table looks like:
SQL> desc my_constants Name Null? Type ----------------------------------------- -------- ---------------------------- CONST NOT NULL VARCHAR2(61) VALUE VARCHAR2(2000)
I used an private procedure to insert the constant value in the table. This way we could use an autonomous transaction to commit the data that was inserted in the table without affecting the main transaction. The rewritten function looks like:
function get_my_constant
(p_constant in varchar2
)
return varchar2
is
l_return my_constants.value%type;
procedure create_entry (p_con in varchar2
,p_val in varchar2
)
is
pragma autonomous_transaction;
begin
insert into my_constants
(const, value)
values
(p_con, p_val);
commit;
exception
when dup_val_on_index
then null;
end create_entry;
begin
select value
into l_return
from my_constants
where const = p_constant;
return l_return;
exception
when no_data_found
then
execute immediate ('begin :1 := '||p_constant||'; end;') using in out l_return;
create_entry (p_constant, l_return);
return l_return;
end get_my_constant;
The new function “copies†the constant values from the package to our “cache†table. It’s important to delete the contents from the table whenever the constants package is modified.
During the Steve Adams seminar in the Utrecht, the Netherlands, Steve opted to use index organized tables (IOT) instead of “normal†heap tables for lookup tables. The my_constants table will not be a big table, so my colleague Alex and I investigated if there was something to gain by implementing the my_constants table as an IOT instead of a heap table.
Creating an IOT is not very different from creating a normal heap table:
CREATE TABLE CAD_CONSTANTS (CONST VARCHAR2(61) NOT NULL ,VALUE VARCHAR2(2000) ,CONSTRAINT CON_PK PRIMARY KEY (CONST) ) ORGANIZATION INDEX /
Only the organization index clause is extra. This makes sure that the complete table is actually an index. We looked at the number of consistent gets to check if the IOT was more efficient compared to a heap table.
The “normal†heap table results in:
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
391 bytes sent via SQL*Net to client
426 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
The IOT results in:
0 recursive calls
0 db block gets
1 consistent gets
0 physical reads
0 redo size
391 bytes sent via SQL*Net to client
426 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
So I chose the IOT because it’s a little efficient than a heap table when the table holds a small number of rows.
Finally we checked if the new function was faster. We created a test script in which we retrieved the value of 30 constants. This was looped 5000 times. So we did 150000 calls to the function.
We started this script twice in two simultaneous sessions in order to check if the sessions were blocking each other.
The following is a comparison of the two functions. We used tkprof to analyze the trace files.
The old function has the following statistics:
call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 10.24 0 0 0 0 Execute 1 354000.39 394291.20 0 0 0 1 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 354000.39 394301.44 0 0 0 1
The new function has the following statistics:
call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 177500.05 190986.24 0 0 0 1 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 177500.05 190986.24 0 0 0 1
These are big number because the test was done on a 4 CPU machine. I believe you have to divide the number of CPU seconds by 4 (the number of CPU’s) to get the real number of CPU seconds. The new function is around 50% faster than the old one. This is a lot considering that the function is called around 5,575,762 times an hour in production.
On production this change was dramatically. Overall CPU load is now 20 to 30% less, which is enough the keep the system happy for now.
Hi,
Great Discussion!
For existing Projects i would pick the Solution suggested by Andre.
I think it’s Great and Flexible.
Now,my suggestion for New Projects:
just create all Constants as Functions and write a Package Body for each Function
and return the respective Values for Constants.
This has the Advantage that it’s Type Safe because i can specify the Return Type in the Function.
The only Disadvantage here is that one has to write too much code 🙂 but for this we could
write PL-SQL to generate this Package automatically by selecting from a Table where all our
constants are temporarily stored, for instance from table my_constants from Andre.
Oh, one more Disadvantage is that if we have a New Constant then we have to change the Package Spec and Body;
i would have preferred a Solution whereby a Constant Addition would not have necessitated a package Change.
So for example:
create or replace package my_constants
is
–my_name constant varchar2(30) := ‘Andre’;
function my_name return varchar2 ;
function my_salary return number ;
end;
/
create or replace package body my_constants
is
function my_name
return varchar2
is
begin
return ‘Andre’;
end ;
function my_salary
return number
is
begin
return 200000;
end my_salary;
–add more functions for each new constant here
end;
/
select my_constants.MY_NAME from dual
/
What do you think about this Solution?
Regards,
Pasko
Just wanted to say thanks for the article.
Not sure if I’m missing the point … but here is a package that appears to do the job (cut down for legibility)
It uses the string global g_bidon to return a value. The disadvantage is that the value is always a string. But I suppose that functions could be added to cater for other data types.
PACKAGE myConst IS
—
g_bidon VARCHAR2(32767) := NULL;
—
— lang code
—
c_lang_en CONSTANT languages.language_code%TYPE := ‘EN’;
c_lang_fr CONSTANT languages.language_code%TYPE := ‘FR’;
c_lang_es CONSTANT languages.language_code%TYPE := ‘ES’;
—
— Functions
—
FUNCTION getConstant (p_constant_name IN VARCHAR2 DEFAULT NULL)
RETURN VARCHAR2;
—
END myConst;
PACKAGE BODY myConst IS
FUNCTION getConstant (p_constant_name IN VARCHAR2 DEFAULT NULL)
RETURN VARCHAR2 IS
v_return VARCHAR2(2000);
BEGIN
EXECUTE IMMEDIATE ‘BEGIN myConst.g_bidon := myConst.’||p_constant_name||’; END; ‘;
RETURN g_bidon;
END getConstant;
END myConst;
alter table … cache;
@Mr Ed.
Steve Adams mentioned singe table hash clusters, I didnt.
@Harm
I didn’t write the original code. This function would have never existed if it was up to me.
@Wilfred
We have a webapplication with short living sessions. Memory is not possible for us.
Hehe, I wrote the comment above yesterday and it was stuck in my browser until today morning when I clicked submit. Now there are enough suggestions already but point about SQL injection is still valid. 🙂
Thanks for sharing this real life experience. Few comments that might help to improve it further.
Both old and new solutions have another problem that is not mentioned here. It’s very insecure – anyone having access to this function is able to ruin the whole aplication assuming that owner is able to perform DML or DDLs on objects. Since you have big application – you never know where it exposed and who gets access to the function.
With redesign you have introduced another problem. Though less significant than hard parse – it’s many selects of same row or rows in the same block. You can avoid it with PL/SQL tables that would cache values for the session.
Another disadvantage of both solutions is the package itself – anytime you change the constant you are at risk to make a mistake rendering the package status invalid and the whole application unable to read it. In the newer solution this risk is mitigated because chances are that value is cached into the table but there still can be misses.
After all that I would question if you really need package constants anymore. Isn’t having one single lookup table accessed via single function enough? This would solve all problems. If there contention issues arise than you can consider caching values in PL/SQL table (PL/SQL table is store in PGA for each session so no contention). The only reason to keep package can be that these constants are accessed from PL/SQL blocks much more often than via function now.
Why your fonction uses ‘execute_immediate’ ? Why not a simple ‘return my_name;’ ???
@Francois
Paramter could be numeric or string so an additionally pl/sql collection as described would be fine to store the vals all to strings like the Environment in the korn shell.
Youd could even mark the type of the parameter.
Greetings
Karl
We had exactly the same problem. I did not use a database table, but the forementioned PL/SQL table. Especially when using the function in a SQL statement it might be executed hundreds or thousands of times for getting the same constant.
Two things we did:
1) make the function DETERMINISTIC. Oracle 10gR2 “caches” the result and only executes the function once for a SQL statement, even if the constant is required in something like a nested loop.
2) The get_constant function first checks in a package variable (PL/SQL table) if the constant is in that table. Initially the table is just empty. If the constant is in the table, return that value. If it’s not in the table, query it using dynamic SQL and store it in the PL/SQL table before returning the value. This bascially implements a PL/SQL caching table in memory.
This way you don’t run the risk of an outdated database “cache” table. The caching is all done in a package (“session”) variable. We found that the get_constant function is only performing the actual dynamic SQL a couple of times for a session since most of the get_constant calls tend to be for the same set of constants.
PS. You can use this PL/SQL caching table trick for all sorts of functions that you expect to be called multiple times with the same parameters.
It would be even better if you can have some sort of memory structure shared by all sessions to do the in-memory caching. This way all sessions benefit from a single session adding something to the cache. I cannot think of a way to do this in PL/SQL. Perhaps something with Java or an external process. (or would that be too much overhead)
Hi Andre,
Your ‘get_my_constant’ function enables SQL injection. Bad.
Because you use a table to store the values, you could initialize your package constants with the values of this table.
It would not be a big job.
What, Steve Adams didn’t mention single-table hash clusters? Shocking.
If the number of constants is low, a simple set of “if” statements might be faster:
if p_constant = “foo” then
return my_constants.foo;
else
…
end if;
Also, if you used PLSQL arrays, you don’t have to rewrite the constants package. Instead of using a table as a “cache”, you use the PLSQL arrays as a “cache”.
@Karl:
I just have written a document here at the project describing how we could use context with our own namespaces. Yes they would greatly reduce the need for this package. And about the LIO’s. We now have two queries generating more LIO’s than my function call. I love statspack!!!! I will write a post about those two queries very soon. I just have to test my final results on a server on which I can be the DBA (that’s at home).
@francois:
That would be my ideal solution; but I would have to test it. But again I decided not to do so, because it would mean a complete rewrite of a very important package.
Nice tip.
Another kind of approach using a PL/SQL table:
CREATE OR REPLACE PACKAGE Pkg_Constants
AS
TYPE TYP_TAB_NUMBER IS TABLE OF NUMBER INDEX BY VARCHAR2(30) ;
tab_values TYP_TAB_NUMBER ;
FUNCTION Get_Value( PC$ParamName IN VARCHAR2 )
RETURN NUMBER ;
END Pkg_Constants;
/
CREATE OR REPLACE PACKAGE BODY Pkg_Constants
AS
FUNCTION Get_Value( PC$ParamName IN VARCHAR2 )
RETURN NUMBER
IS
BEGIN
RETURN tab_values( PC$ParamName ) ;
END ;
BEGIN
— initialisation phase —
tab_values( ‘param1’ ) := 1.0 ;
tab_values( ‘param5’ ) := 5.0 ;
tab_values( ‘param10’ ) := 10.0 ;
END Pkg_Constants;
/
SQL> select PKG_CONSTANTS.Get_Value(‘param10’) from dual
2 /
PKG_CONSTANTS.GET_VALUE(‘PARAM10’)
———————————-
10
SQL>
Yes Andre,
on a running system the possiblities to change a central package totally are not so good 😉
But on test lab i would like to test the two approaches to implement at the next time the best approach. An Environment package ideally is some kind of component and reusable for other projects.
Another way could be the SYS_CONTEXT function with an own defined namespace for the application but i never used this before.
Greetings
Karl
Hi Karl,
It could be fast what you are saying here. I have thought about implementations like that. I agree that the first approach was not very efficient. I didn’t write it; and will never write something like that.
Your implementation would mean that we have to rewrite the constants package. That’s very possible, but takes more time then only rewriting the function that retreives the constants value. It would also mean more testing because we have to be sure that all constants are correctly implemented in the new pacakge. It would be interesting to test if it’s even faster; I fully agree with that.
Yes my approach caused LIO’s, of course. But the CPU load is 30% less and that’s what we needed right now.
Hi,
the first appraoach seems to be not so effecient using execute immediate …;
Why not build a package with an environment collection like :
type t_env_var is table of binary_integer index by Varchar2(32);
type t_env_val is table of Varchar2(255) index by Binary_integer;
l_highwatermark pls_integer:= 0;
…
with the first collection type you have an array of unique Varnames
you can check the existence of the VarName with Exist collection function.
with the second you have an array of varvals.
i am not sure but this could be a lot cheaper then the first two approachaes.
Reading every time from a table cause high LIO;
Greetings
Karl