SQL & PL/SQL

Hey all,



First time new poster, long time reader.



I have a function that calls a specific query with 2 variables. When I execute the query and hardcode the values, it executes <strong>much </strong>quicker than when the function is called, and the values are passed. I'll give some examples below... here's the full function:



FUNCTION get_golf_gameplay_completed (

p_cabinet_id_in NUMBER,

p_days_back_in NUMBER

)

RETURN ref_cursor

IS

cu_ref_cur ref_cursor;

BEGIN

OPEN cu_ref_cur FOR

SELECT golf_game_data.cabinet_id, golf_game_data.player_num,

TO_CHAR

(global_utility_pkg.cnvrt_gmt_to_local_time_by_cab

(golf_game_data.cabinet_id,

golf_game_data.server_start_time,

golf_game_data.server_start_time

),

'MM/DD/YYYY HH:MI:SS AM'

) cab_local_event_timestamp,

TO_CHAR

(global_utility_pkg.cnvrt_gmt_to_local_time_by_cab

(golf_game_data.cabinet_id,

golf_game_data_end_time.server_end_time,

golf_game_data_end_time.server_end_time

),

'MM/DD/YYYY HH:MI:SS AM'

) cab_local_session_end,

TO_CHAR

(global_utility_pkg.cnvrt_gmt_to_local_time_by_cab

(golf_game_data.cabinet_id,

golf_game_data_end_time.server_end_time,

golf_game_data_end_time.server_end_time

),

'HH:MI:SS AM'

) cab_local_session_end_time,

TO_CHAR

(global_utility_pkg.cnvrt_gmt_to_local_time_by_cab

(golf_game_data.cabinet_id,

golf_game_data_end_time.server_end_time,

golf_game_data_end_time.server_end_time

),

'MM/DD/YYYY'

) cab_local_server_end_date

FROM golf_game_data INNER JOIN golf_game_data_end_time

ON golf_game_data.cabinet_id =

golf_game_data_end_time.cabinet_id

AND golf_game_data.player_num =

golf_game_data_end_time.player_num

AND golf_game_data.session_id =

golf_game_data_end_time.session_id

WHERE golf_game_data.cabinet_id = {color:#ff0000}<strong>p_cabinet_id_in</strong>{color}

AND golf_game_data_end_time.cabinet_id = {color:#ff0000}<strong>p_cabinet_id_in</strong>{color}

AND golf_game_data_end_time.server_end_time IS NOT NULL

AND golf_game_data.server_start_time &gt;= SYSDATE - {color:#ff0000}<strong>p_days_back_in</strong>{color}

ORDER BY golf_game_data_end_time.server_end_time;



RETURN cu_ref_cur;

END get_golf_gameplay_completed;






I've highlighted the variables for readability.



This is how I've been executing the function from the SQL*Plus command line:



select tech_report_package.get_golf_gameplay_completed(122477,10) from dual;




The speed differences between running the raw query versus the function as a whole have been driving me nuts. For the cabinet IDs (the first variable inserted - in the case above, it would be 122477) that have a high amount of activity on the GOLF_GAME_DATA table, the query itself returns results within a few seconds (usually under 10 seconds even for the most bloated data). However, the function, when called with the same IDs, takes minutes - as long as 3 or 4 minutes in some cases.



I've run various tests in a cloned production environment, and we've isolated any possible issues with networking, data contention, and partitioning (the GOLF_GAME_DATA table is not partitioned) - the issue is simply the query speed versus function speed, with no external influences.



What baffles me is the fact that the function is very basic - all it does it executes the query and returns the results, just like the query... the only difference being the cursor that's opened.



So, getting back to square one; my question was: <u>why would any query run much quicker than a function that does nothing but call the same query?</u>



Any thoughts?



Thanks

-Bob