Ask the Expert

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, 
tt_emp_3)
SELECT src_tab.src_id,
        src_tab.scr_disp,
        src_tab.scr_date,
        src_tab.scr_emp_1,
        src_tab.scr_emp_2,
        src_tab.scr_emp_3
FROM
    (SELECT row_number() over( 
        ORDER BY SUBSTR(fn_cil_sort_format(SUBSTR(src_cil, 1, 8)), 1,
4),
                SUBSTR(src_cil, 4, 8)) AS src_id,
        scr_disp,
        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
        FROM source_table
        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;
Thanks.

    Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: