Q

Execution time increases with packaged code

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?

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.
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

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close