I've mentioned one of the four queries/cursor below. This is the style in which the coding is done :
CURSOR j_order_activity IS SELECT A.activity_number, A.activity_category, A.activity_type, A.engr_id_1, A.queue_name, A.charge_ind, A.activity_status, A.completion_abort_date, A.required_by_date, p.progress_date, p.current_status, p.progress_recr, p.delay_code FROM pstn_order_activity_o_1 A, pstn_order_progress_o_1 p WHERE A.job_number = wo_job_number AND A.order_number = w_order_number AND A.SUB_ZONE = w_sub_zone AND A.data_date = w_data_date AND A.activity_number = p.activity_number(+) AND A.job_number =Then there is some more code and then we have:
p.job_number(+) AND A.order_number = p.order_number(+) AND
A.SUB_ZONE = p.SUB_ZONE(+) AND A.data_date = p.data_date(+); joacr j_order_activity%ROWTYPE;
TYPE VC1 IS REF CURSOR; v_cursor1 VC1; v_sql_stmt1 VARCHAR2(10000); v_sql_stmt1 := 'SELECT A.activity_number, A.activity_category, A.activity_type, A.engr_id_1, A.queue_name, A.charge_ind, A.activity_status, A.completion_abort_date, A.required_by_date, p.progress_date, p.current_status, p.progress_recr, p.delay_code FROM pstn_order_activity_o_'||v_table_name||' A,
pstn_order_progress_o_'||v_table_name||' p WHERE A.job_number
= :bv_order_number AND A.order_number = :bv_order_number AND
A.SUB_ZONE = :bv_sub_zone AND A.data_date = :bv_data_date AND A.activity_number
= p.activity_number(+) AND A.job_number = p.job_number(+) AND
A.order_number = p.order_number(+) AND A.SUB_ZONE = p.SUB_ZONE(+) AND
A.data_date = p.data_date(+)';
v_table_nameis assigned some value earlier itself
OPEN v_cursor1 FOR v_sql_stmt1 USING w_order_number,w_order_number,v_sub_zone,v_run_date; LOOP FETCH v_cursor1 INTO joacr; EXIT WHEN V_CURSOR1%NOTFOUND;Some processing goes in here and based on certain conditions, another cursor is opened and closed after some processing.
-- wo_activity_number := joacr.activity_number; -- wo_activity_category := joacr.activity_category; -- wo_activity_type := joacr.activity_type; END LOOP; CLOSE v_cursor1;Can the code be made more easy? All the tables are indexed properly and stats have been gathered. We're not able to explain why the performance is slow. As shown above, four cursors are being used in the package and they are all nested. Can there be a better substitution to the above code (like using other collection variables or BULK collects or GTTs)? Could they improve performance.
We're using Oracle version 9i (9.0.2) on HP UX Titanium.
One of the statements in your question caused me to pause: "Some processing goes in here and based on certain conditions, another cursor is opened and closed after some processing." This really does not give me too much to go on here. What does the other cursor look like? Is it possible to move this cursor outside the loop and process it in another way? Unfortunately, I do not have enough information to make that determination.
Try tuning your PL/SQL queries outside of the PL/SQL block and see if that helps your performance. That is the first place to start when tuning PL/SQL code.
Dig Deeper on Oracle database design and architecture
Related Q&A from Brian Peasland
Oracle expert Brian Peasland answers one reader's question about common pitfalls when connecting Oracle to outside programs. Continue Reading
One reader asks expert Brian Peasland a question about datafile sizes with the Oracle RMAN duplicate 10g command. Continue Reading
Managing parent table-child table relations in Oracle SQL environments is key to efficient programming. Continue Reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.