PL/SQL performance tuning…

pts.
Tags:
Oracle 10g
Performance/Tuning
SQL
I need to move data between to schemas. I have a created packaged code to accomplish this. The problem is the execution time. When running the insert statements from the source schema to insert data into the target schema, it takes considerably longer to complete the statement than if I copied the tables from the source schema into the target schema and executed the same statement in the target schema. Any insight as to why this might be? Thanks in advance Here is a sample of one of the insert statements: 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;

Answer Wiki

Thanks. We'll let you know when a new response is added.

This is a bit of a “shot in the dark” but it is easy to check…

One possible explanation might be an I/O bottleneck when changing schemas but not within a schema. For example, if target_table exists and is on the same physical disk as source_table, most likely I/O bandwidth would limit performance. However, if you copied the source_table into the schema of the target table *and* it happened to be located on a different physical disk from the target_table, insert performance would be noticably better.

Discuss This Question: 3  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

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
  • Jelewis
    Additionally, all of the data resides on the same disk drive.
    0 pointsBadges:
    report
  • JennyMack
    You might want to check out this Fast Guide to PL/SQL. It outlines several methods of performance tuning, and links to several resources on the subject.
    4,280 pointsBadges:
    report
  • Jelewis
    [...] Address: http://itknowledgeexchange.techtarget.com/itanswers/plsql-performance-tuning/ (0) Comments Read [...]
    0 pointsBadges:
    report

Forgot Password

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

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

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

Thanks! We'll email you when relevant content is added and updated.

Following