Q

Package taking too long to create a file

We have a particular package which creates a file. It is taking a long time to create the file, even though the data in the tables in not very high (around 50000 rows).

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 =
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;
Then there is some more code and then we have:
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_name is 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.

The first thing I'd start with is the SQL statements in your cursor. Are you sure that those statements are tuned properly? You'll have to run each statement through Explain Plan, interpret the results and tune the query accordingly. You'll have to tune each statement in SQL*Plus as if the cursor's query were not in the PL/SQL block. It would also help to see if any wait events are causing significant bottlenecks for these queries.

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.

This was last published in April 2005

Dig Deeper on Oracle database design and architecture

PRO+

Content

Find more PRO+ content and other member only offers, here.

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.

Start the conversation

Send me notifications when other members comment.

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

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close