Execution time increases with packaged code
I'm running 10g Release 2 on a W2K server. I've created a package that moves data from one schema to another.
Within the stored procs are insert statments that move the data.
If I copy the data to the target schema and then run the insert statements, they execute much faster than moving the data between
schemas using the packaged code.
Any insight into why execution time is greatly increased by using the packaged code?
Here's an example of the code:
INSERT INTO target_table(tt_id, tt_disp, tt_date, tt_emp_1, tt_emp_2,
(SELECT row_number() over(
ORDER BY SUBSTR(fn_cil_sort_format(SUBSTR(src_cil, 1, 8)), 1,
SUBSTR(src_cil, 4, 8)) AS src_id,
fn_date_format(date_time) AS scr_date,
v_convert AS scr_emp_1,
v_convert AS scr_emp_2,
v_convert AS scr_emp_3
ORDER BY SUBSTR(fn_sort_format(SUBSTR(src_cil, 1, 8)), 1, 4),
SUBSTR(src_cil, 4, 8)) src_tab
WHERE scr_disp IS NOT NULL;
Assuming that by "copy the data to the target schema and then run the insert statements" you mean that you manually move data to work tables, then run the same insert statements that you use in your PL/SQL code, you could be seeing delays due to context switches between the SQL and PL/SQL engines. Try tracing both methods. If the SQL executions (and their elapsed times) look similar, then that's probably the difference, but I'd guess that there are differences in the SQL and/or their execution plans. Traces (using DBMS_MONITOR with waits=true) will give you the details of how time is being spent.
This was first published in April 2006