"A big problem is SQL Plan Managementis only available on enterprise."
Well, you can use sql_patch:
https://blogs.oracle.com/optimizer/entry/how_can_i_hint_a and you can
use stored outlines.
On 26.11.2015 22:15, Juan Carlos Reyes Pacheco wrote:
Thank you for your advice :) ,
it's only an idea, for some specific situations when there is no other solution, not generic.
A big problem is SQL Plan Managementis only avaible on enterprise.
Anyway I can't skip the error you can't execute inserts inside a query.
and I have to execute a job or a loop before the select.
2015-11-26 11:40 GMT-04:00 Juan Carlos Reyes Pacheco <jcdrpllist@xxxxxxxxx <mailto:jcdrpllist@xxxxxxxxx>>:
Hello I got a practical idea to optimize creating a cache
functions for intensive processes, that calls several times a
function, in a short period.
But without the complexity of having to validate and guarantee the
data used to g et the data, changed.
1. The problem is the control of the modifications of values in
tables and packages, specific user variables, etc. that could
change the value a procedure returns.
2. But if we take a short period of time, and we include critical
variables like user and specific login information like year, that
could affect the result;
we can create a generic solution.
original function
We have function with a complex query
schema.function_name(value)
cmoplex query
return return_value
end;
Optimizing function
rename schema.function_name(value)
to schema.function_name_cached(value)
create global temporary table schema%function_name
username
value1
return1
timestamp
new:
schema.function_name(value)
begin
select from schema%function_name where username=user and
value1=value and timestamp>sysdate-(5*60/*period*/);
when no_data_found then
return_value = schema.function_name_cached(value);
delete from schema%function_name where value
insert into schema%function_name
end;
return return_value
end;
So the idea is for every specific user a value will be stored in a
temporary table, when they call the function and it will be reused
only if it was get the last 5 minutes.
So unless this is a very dinamic query that requires to validate
modifications before that time, it's ok :)
