How to optimize the use of a pipelined, weakly typed ref cursor

Question

I am having problems with the procedure; when starting for "large" sets (800+ parents, 1300 + children), it is very slow (30-60 seconds).

The main idea is to select all the parent records (and their corresponding child elements) that match certain search criteria, as well as 3 additional pieces of information that will need to be calculated.

My approach to the problem was

• to create your own record type with additional fields for calculated values.
• A reference to this type of record can then be passed to each function controlled by the main processing function.
• When a value is calculated for each parent record, bind it to the record.

Each procedure GET_PARENT_RECORDS and GET_CHILD_RECORDS is called once for each search, and each computational function is executed N times (where N is the number of parent and / or child records).

---

Question 1 : Is this the right approach? (weakly typed cursors, pipelined functions). If not, how can I approach this problem, assuming that I can redo it?

Question 2 : prohibition of full rewriting, is there something obvious that can be improved in the provided code?

Question 3 : Or something else may be wrong, because I notice that the same slow request returned after 20 seconds when I started the procedure several times?

---

Package definition

 create or replace PACKAGE THIS_PKG AS Type parentCursor IS REF CURSOR; Type childCursor IS REF CURSOR; Type ParentRecordType IS RECORD ( other_columns, Extra_column_A, Extra_column_B, Extra_column_C, Row_num); --associative array TYPE ParentArray IS TABLE OF ParentRecordType; FUNCTION processParents( p IN THIS_PKG. parentCursor ) RETURN ParentArray PIPELINED ; FUNCTION countSomething(some params…) RETURN INT; FUNCTION checkCondX (SomeParent IN ParentRecordType) RETURN VARCHAR2; FUNCTION checkCondY (SomeParent IN ParentRecordType) RETURN VARCHAR2; PROCEDURE GET_PARENT_RECORDS( other_parameters, Parents OUT THIS_PKG.parentCursor); PROCEDURE GET_CHILD_RECORDS( other_parameters, Children OUT THIS_PKG.childCursor); END THIS_PKG; 

Packing body

 -- omitted FUNCTION processParents( p IN THIS_PKG.parentCursor ) RETURN ParentArray PIPELINED IS out_rec ParentArray; someParent ParentRecordType; BEGIN LOOP FETCH p BULK COLLECT INTO out_rec LIMIT 100; FOR i IN 1 .. out_rec.COUNT LOOP out_rec(i).extra_column_A := countSomething (out_rec(i).field1, out_rec(i).field2); out_rec(i).extra_column_B := checkCondX(out_rec(i)); out_rec(i).extra_column_C := checkCondY(out_rec(i)); pipe row(out_rec(i)); END LOOP; EXIT WHEN p%NOTFOUND; END LOOP; RETURN; END processParents; PROCEDURE GET_PARENT_RECORDS( some_columns, Parents OUT THIS_PKG. parentCursor) IS BEGIN OPEN Parents FOR SELECT * FROM TABLE(processParents (CURSOR( SELECT * FROM ( --some select statement with quite a few where clause --to simulate dynamic search (from pre-canned search options) ) ))) abc WHERE abc.extra_column_C like '%xyz%' --(xyz is a user given value) ; END GET_PARENT_RECORDS; 

---

Update Some research took place yesterday and came across the SQL Batch Task optimizer (from Toad). I plugged in the package and this is what I got.

Batch Optimizer Results

Comprehensive request

Problem request

Answer 1

What happens in the string processing section? A lot of time can be spent on these functions countSomething, checkCondX / Y. Are they also an SQL call? At first, I tested the performance of a table function without additional predicates. Maybe it's better to just create a query that does all of this in SQL, rather than in functions - if you can do this, it will be much faster than calling a function for each row.

  out_rec(i).extra_column_A := countSomething (out_rec(i).field1, out_rec(i).field2); out_rec(i).extra_column_B := checkCondX(out_rec(i)); out_rec(i).extra_column_C := checkCondY(out_rec(i)); 

Also, the plan you proposed is explained by the fact that the optimizer believes that only one row is returned from all tables (power 1). If this is not the case, then the query plan will not be optimal. You may need to collect statistics, use dynamic sampling, or power hints in a table function.

Finally, check out DBMS_SQLTUNE.REPORT_SQL_MONITOR for a detailed report of your sql. If the request is not dynamically identified, because you need to monitor the need to add / * + MONITOR * / hint. This gives more detailed information, for example, the number of rows returned, the number of instances and other interesting tidbits not available in terms of explanation.

 SELECT /*+ MONITOR */ FROM slow_query; -- then run sqltune to get a report SELECT * FROM TABLE(DBMS_SQLTUNE.REPORT_SQL_MONITOR()); 

Answer 2

The search batch SQL optimizer (from Toad) or any other tool will not be able to help you take into account that they do not understand what you are doing inside the functions. The problem is "FETCH p BULK COLLECT INTO out_rec LIMIT 100;". The quality of the request, which is passed to p, actually determines the final execution plan and execution time. Conveyorization is not a cause of slowness. When you run your procedure several times, Oracle uses cached data. My best advice: use Java instead of PL / SQL for this specific purpose, it will be easier to understand.