Hi,
I have a stored procedure with dynamic SQL, something like this:
CREATE OR REPLACE PROCEDURE MYSCHEMA.GET_DATA (cols_for_select IN varchar2, where_condition IN varchar2, query_no IN varchar2, result OUT SYS_REFCURSOR) IS
begin
CASE query_no
WHEN '1' then
OPEN result FOR
cols_for_select || ' from (SELECT col1,col2.... -- I select all possible columns here
) where ' || where_condition;
WHEN '2' then
OPEN result FOR
cols_for_select || ' from (SELECT col1,col2.... -- I select all possible columns here
) where ' || where_condition;
-- ...etc...
end CASE;
exception when others then
OPEN result FOR
'SELECT ' || '''' || SQLERRM || '''' || ' as error from dual';
end;
/
When I run all of these queries with a WHERE condition BETWEEN two dates my queries runs fast, but I If run them for a single date (e.g. "WHERE mydate=TO_DATE('01.01.2019','mm.dd.yyyy')") all queries run slow.
A bit strange, I would expect queries to be slow when data is being searched between two dates...
After some examination I found out that Oracle allways chooses different execution plan for queries running on a single date, so I started to search for a soluton to force a plan which is faster.
However, whatever I could found is only how to force execution plan for particular SQL. I tested that and It worked, but that's no use for me as I don't know what date is going to be choosed by user.
Also, I would rather like to be able to force execution plan for entire stored procedure when executed and not just a single query, because I have a lot of queries inside Select Case statement - and all query results are from same tables, just some slight differencies in output.
So, Is there anything I can do to force execution plan for stored procedure ?
P.S.: All tables that are used in my queries are optimised (indexed - that includes date column too).
Thanks in advance.