Home » RDBMS Server » Performance Tuning » Optimizing Dynamic SQL Usage (G12)
Optimizing Dynamic SQL Usage [message #630466] Mon, 29 December 2014 00:13 Go to next message
FDAVIDOV
Messages: 20
Registered: December 2014
Junior Member
Hello everyone,

I'm not sure the following is possible at all, but...

I have a mechanism that enables (through a simple GUI) to define computation paradigms and make use of them dynamically. For instance (trivial but simple example), you could define a mechanism that calculates the average between two numbers as:

BEGIN 
    RETURN ( p_a + p_b ) / 2; 
END; 

This code is then used to create a small anonymous block like:

DECLARE 
  temp_func( p_a NUMBER , p_b NUMBER) 
  RETURN NUMBER AS 
BEGIN 
  RETURN (p_a + p_b)/2 ; 
END ; 
BEGIN 
  :PAR0 := Temp_func(:PAR1,:PAR2) ; 
END ;


This code will then be injected into an "EXECUTE IMMEDIATE" statement returning the average.

The problem is that this code could be executed very many times, wasting a lot of resources (parsing, etc.).

The reason for doing this and not creating stored functions is that this approach warrants (as much as possible) that there would be minimal or none side effects resulting from new code chunks.

My question would then be:

Is there any possibility to "compile and store" (into a DB table) the compiled code of the anonymous block shown above, so that whenever needed the code would just be extracted and executed? (saving in this way a lost of resources).

Thanks in advance for your feedback,
Fernando.


Edited by Lalit : Formatted the code and added code tags

[Updated on: Mon, 29 December 2014 02:02] by Moderator

Report message to a moderator

Re: Optimizing Dynamic SQL Usage [message #630469 is a reply to message #630466] Mon, 29 December 2014 01:15 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

No.

Re: Optimizing Dynamic SQL Usage [message #630470 is a reply to message #630466] Mon, 29 December 2014 02:00 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Welcome to the forum!

Please read and follow the OraFAQ Forum Guide and How to use [code] tags, to enable us to help you.
Re: Optimizing Dynamic SQL Usage [message #630486 is a reply to message #630469] Mon, 29 December 2014 04:33 Go to previous messageGo to next message
FDAVIDOV
Messages: 20
Registered: December 2014
Junior Member
Thank you for the quick response. I was afraid that "NO" would be the answer, but had to check it.
Re: Optimizing Dynamic SQL Usage [message #630647 is a reply to message #630486] Thu, 01 January 2015 12:57 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
Quote:
The reason for doing this and not creating stored functions is that this approach warrants (as much as possible) that there would be minimal or none side effects resulting from new code chunks


This makes no sense at all. How do you define a "SIDE EFFECT"? No matter how you slice or dice it, if you are saving code to reference later, then it does not matter if the code was precompiled or not. I do not see how your scheme of executing dynamic SQL changes anything. What am I missing?

Kevin
Re: Optimizing Dynamic SQL Usage [message #630665 is a reply to message #630647] Fri, 02 January 2015 05:33 Go to previous messageGo to next message
FDAVIDOV
Messages: 20
Registered: December 2014
Junior Member
Kevin,

Thanks for your comments. I'll try to explain the context and my approach.

The system I'm working on is required to enable us (or the user!) to dynamically create new functionalities, even during normal operation. These functionalities are (I would venture to say ALWAYS) related to complex calculations (e.g. multi-tiered discounts, complex loan interest calculations, etc.).

Taking into account that we want to exploit PACKAGING of functionalities as much as possible (basic SW Engineering practice), one would be tempted to create a package to host all those "CUSTOM" functions.

Unfortunatelly, if one function within the package contains syntactic errors, the while package would become INVALIDATED (and hence the "SIDE EFFECTS").

Keeping the custome code in a table and "compiling" it on-the-fly will warrant that, in case of errors, only that particular piece of code would not function as needed.

Hope this is clear and makes some sense.

All the best,

Fernando.
Re: Optimizing Dynamic SQL Usage [message #630674 is a reply to message #630665] Fri, 02 January 2015 08:56 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
That is a good explanation, thanks.

So if you are keeping the code in a table and reusing it then lots of people do that. Is this your design? Maybe you could provide teh table desribe for the table that keeps the code. I presume the user names their code snippet so they can find it later, and that you check the code snippet for syntax for them as part of the process?

Kevin

[Updated on: Fri, 02 January 2015 08:58]

Report message to a moderator

Re: Optimizing Dynamic SQL Usage [message #630677 is a reply to message #630665] Fri, 02 January 2015 10:45 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You could then use stand alone functions and not package functions, this will allow you to verify that the functions at least compile.

Re: Optimizing Dynamic SQL Usage [message #630699 is a reply to message #630677] Sat, 03 January 2015 03:47 Go to previous messageGo to next message
FDAVIDOV
Messages: 20
Registered: December 2014
Junior Member
Michel,

Indeed, that was on of the options I considered, but eventually dismissed for two reasons:

a) Stored procedures/functions OUTSIDE packages are not in line with "encapsulation" and "information hiding" principles and best practices,
b) When you define a FUNCTION you are always exposed to unintentionally override other function (imagine a situation in which you have hundreds or even thousands of such functions...).

I still think that the best solution would be (if it was supported of course) to store the "compiled version of the anonymous block" into a table, retrieve it, and just execute it without the need of recompiling it time and time again.

Hope this will be supported in the future since, the way I see it, will dramatically boost the flexibility of the product.

All the best,
Fernando.
Re: Optimizing Dynamic SQL Usage [message #630701 is a reply to message #630699] Sat, 03 January 2015 03:58 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

a) Indeed but this is a better choice than storing code inside a table and use dynamic compilation (one must choose the lesser of two evils Smile )
b) You can always choose a unique name for your function that you will store in your table instead of the code, for instance something like:
SQL> select 'MYAPP_'||sys_guid() from dual;
'MYAPP_'||SYS_GUID()
--------------------------------------
MYAPP_CC59A9D52B3A4CC7BD881C76AB40AD9E


Your best solution does not exist so you have to choose something "less better". Smile

Re: Optimizing Dynamic SQL Usage [message #630709 is a reply to message #630701] Sat, 03 January 2015 06:44 Go to previous messageGo to next message
FDAVIDOV
Messages: 20
Registered: December 2014
Junior Member
Michel,

a) I hope you would agree with me that "evils" are polifacetic things (what appears to be the worst evil to one, may not be the worst for somebody else),
b) Your suggestion of using allocated names to functions is worth some analysis (you could build such a name using a sequence, and storing in a table a description of the function along with the resulting name and "unique search keys").

And yet... best would be to have Oracle support what I was asking for.

Now, a question for you: If what I would like to do was supported, wouldn't you being using it as well?

All the best,

Fernando.
Re: Optimizing Dynamic SQL Usage [message #630710 is a reply to message #630709] Sat, 03 January 2015 08:11 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Yes, if this feature existed it should be worth to use it but I'd bet that Oracle, uf you ask them, will tell you that this feature already exists but the "compiled" code is stored in our tables and what you have to do is to give us the name and code and we handle it for you (we take care of the code and who is able to use it), and this feature is... CREATE FUNCTION. Smile

Re: Optimizing Dynamic SQL Usage [message #630711 is a reply to message #630710] Sat, 03 January 2015 08:33 Go to previous messageGo to next message
FDAVIDOV
Messages: 20
Registered: December 2014
Junior Member
Well, I would not bet because you would most likely win Laughing
Re: Optimizing Dynamic SQL Usage [message #630712 is a reply to message #630710] Sat, 03 January 2015 08:35 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
This kind of solution is in fact not allowed in many shops due to its high likelihood of abuse by injection. You are letting people create any code they want. A smart person can abuse this easily.

So no, it is not a given that such a "preferred" solution would be used.

In any event, since you have total control of how your code would be named, stored, compiled, documented, managed, located, and executed, you can provide a solution as was described without any problem of name collisions so your complaints are unfounded.

Good luck. Kevin.

create table code_user
(
    username varchar2(30) not null
  , create_date date not null
)
/
 
alter table code_user
   add constraint code_user_pk primary key (username)
/
 
create table code_snippet
(
    unique_name varchar2(30) not null
  , plsql_snippet varchar2(4000) not null
  , username varchar2(30) not null
  , user_desc varchar2(4000) not null
  , call_spec varchar2(4000) not null
)
/
 
alter table code_snippet
   add constraint code_snippet_pk primary key (unique_name)
   add constraint code_snippet_fk1 foreign key (username) references code_user
/

create sequence myseq;
 
insert into code_user values ('FDAVIDOV',sysdate);
commit;
 
declare
   snippet_name_v varchar2(30);
begin
   snippet_name_v := 'ms'||myseq.nextval;
   insert into code_snippet values
   (
       snippet_name_v
     , 'create or replace function '||snippet_name_v||' (p_a in NUMBER, p_b in NUMBER) return number is'
       ||' '||'BEGIN'
       ||' '||'  RETURN (p_a + p_b)/2 ;'
       ||' '||'END ;'
     , 'FDAVIDOV'
     , 'my first piece of code'
     , ':PAR0 := '||snippet_name_v||' (:PAR1,:PAR2);'
   )
   ;
   for r1 in (
               select *
               from code_snippet
               where unique_name = snippet_name_v
             ) loop
      begin
         execute immediate r1.plsql_snippet;
      exception when others then
         delete
         from code_snippet
         where unique_name = r1.unique_name
         ;
         raise;
      end;
   end loop;
end;
/

select * from code_user;
select * from code_snippet;
desc ms2

variable par0 number;
exec :par0 := ms2(1,2)
print par0
Re: Optimizing Dynamic SQL Usage [message #630713 is a reply to message #630665] Sat, 03 January 2015 09:27 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
FDAVIDOV wrote on Fri, 02 January 2015 11:33
Kevin,

Thanks for your comments. I'll try to explain the context and my approach.

The system I'm working on is required to enable us (or the user!) to dynamically create new functionalities, even during normal operation. These functionalities are (I would venture to say ALWAYS) related to complex calculations (e.g. multi-tiered discounts, complex loan interest calculations, etc.).

Taking into account that we want to exploit PACKAGING of functionalities as much as possible (basic SW Engineering practice), one would be tempted to create a package to host all those "CUSTOM" functions.

Unfortunatelly, if one function within the package contains syntactic errors, the while package would become INVALIDATED (and hence the "SIDE EFFECTS").

Keeping the custome code in a table and "compiling" it on-the-fly will warrant that, in case of errors, only that particular piece of code would not function as needed.

Hope this is clear and makes some sense.

All the best,

Fernando.
To get the effect you want, use Editions. Your default edition would publish the current, working, version of your custom functions package. Whenever you (or anyone else) needs to add a function to your custom functions package, create a new edition and edit the package there. Once it compiles OK, set that edition as the database default edition to publish the new version of the package to everyone. Easy!

[Updated on: Sat, 03 January 2015 09:31]

Report message to a moderator

Re: Optimizing Dynamic SQL Usage [message #630717 is a reply to message #630712] Sat, 03 January 2015 10:30 Go to previous message
FDAVIDOV
Messages: 20
Registered: December 2014
Junior Member
Kevin,

One small but important correction: I don't have complete control on what is being injected into the system through this functionality. The only thing I do is to make sure that the code within the anonymous block does not contain any data alteration commands (e.g. INSERT, UPDATE, DELETE). True, any person with the aim of sabotaging the system will always find the way to do it.

Thanks for your comments.

Fernando.
Previous Topic: LNS wait on SENDREQ+ARCH wait on SENDREQ on ASYNC dataguard at primary database
Next Topic: How To Check Performace Tuning
Goto Forum:
  


Current Time: Thu Mar 28 09:24:36 CDT 2024