Re: Simple Idea to cache return values from procedures on Oracle Standard edition

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_at_gmail.com <mailto:jcdrpllist_at_gmail.com>>:
>
> 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 :)
>
>

-- 





--
http://www.freelists.org/webpage/oracle-l