Hi, we have a database here that has a few nested tables and there is a monthly process that the users run that reference (update, select, insert, delete) from Oracle Nested Tables. This monthly process takes an hour to run 10 out of 12 months. For the other 2 months, there's about twice as much data that it needs to process and it takes about 4 hours to run. We did a sql trace on this and then ran it through TKPROF and the tkprof shows that it is spending a LOT of time doing recursive call when compared to non-recursive calls. The recursive calls appear to be Oracle handling Nested Table maintenance. Does anyone have any suggestions on how we can reduce the amount of time Oracle spends in recursive calls when it is working with Nested Tables? Thanks for your input.

Here's the top few SQL statements from the trace followed by the Totals. The SORT=EXEELA and SYS=YES option was used:

********************************************************************************

CALL rn_create_min_entries(:1)


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 7854.07 8700.50 42489 7399256 69914346 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 7854.07 8700.50 42489 7399256 69914346 0

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 122 (PVENDOR) (recursive depth: 1)
********************************************************************************

BEGIN :rc := SS_JOB.submit( :0); END;


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 5387.83 5745.05 2914 5811632 427751 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 5387.84 5745.05 2914 5811632 427751 1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 182 (LPRETULA)
********************************************************************************

INSERT INTO "PVENDOR"."NESTED_PROCESS_AGREEMENTS"("NESTED_TABLE_ID",
"SYS_NC_ROWINFO$")
VALUES
(:1,:2)


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2507 0.53 0.64 0 0 0 0
Execute 2507 701.12 823.92 14461 394950 1600217 4595331
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5014 701.65 824.56 14461 394950 1600217 4595331

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 3)
********************************************************************************

DELETE FROM "PVENDOR"."NESTED_PROCESS_OUTPUT"
WHERE
"NESTED_TABLE_ID" = :1


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2507 0.50 0.32 0 0 0 0
Execute 2507 675.55 793.65 0 102552 45827284 9008887
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5014 676.05 793.97 0 102552 45827284 9008887

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 3)

Rows Row Source Operation
------- ---------------------------------------------------
1 DELETE NESTED_PROCESS_OUTPUT
1 INDEX RANGE SCAN (object id 17910)

********************************************************************************

SELECT RN_AGREEMENT_T(AGREEMENT_NB,SEQ_NB,SELECTION_TYPE,LTYPE,STATUS,
EFFECTIVE_DT,FILE_NB,BA_PAYOR_ID,BA_PAYOR_ADDR_ID,GRS_RENTAL,NET_RENTAL,
RENTAL_NEXT_DUE_DT,RENTAL_PAYMENT_FREQUENCY,RENTAL_PAYMENT_FREQ_UOM,
RENTAL_RATE,RENTAL_TYPE_CD,RENTAL_DESC,BA_PROVINCE_CD,MINERAL_INTEREST_PCT,
DOCUMENT_TYPE_CD,PRODUCING_STATUS_FG,LESSOR_LEASE_NB,
FLUID_PROD_SUBSTANCE_CD,AREA_VALUE,AREA_UOM,MR_AREA_NB,REPORTING_UNIT,
AGREEMENT_CMT)
FROM
TABLE(CAST(:b1 AS RN_AGREEMENTS)) A WHERE A.AGREEMENT_NB = :b2 AND
A.SEQ_NB = :b3


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 3 0 0
Execute 7898 580.61 616.95 0 0 0 0
Fetch 7898 71.64 76.03 0 0 0 7898
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 15797 652.25 692.98 0 3 0 7898

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 122 (PVENDOR) (recursive depth: 2)

Rows Row Source Operation
------- ---------------------------------------------------
7898 COLLECTION ITERATOR PICKLER FETCH

error during parse of EXPLAIN PLAN statement
ORA-22905: cannot access rows from a non-nested table item

parse error offset: 508
********************************************************************************

DELETE FROM "PVENDOR"."NESTED_PROCESS_AGREEMENTS"
WHERE
"NESTED_TABLE_ID" = :1


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2507 0.42 0.83 0 0 0 0
Execute 2507 382.76 456.72 0 62597 19193904 4595331
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5014 383.18 457.55 0 62597 19193904 4595331

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 3)

Rows Row Source Operation
------- ---------------------------------------------------
1 DELETE NESTED_PROCESS_AGREEMENTS
1834 INDEX RANGE SCAN (object id 35825)




Here's the Totals from the end of the tkprof file... Notice how much time is spent on recursive statements!!!

********************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 91 0.82 1.09 54 955 6 0
Execute 84 5387.90 5745.16 2914 5812220 427751 1
Fetch 2084 7.20 9.47 2180 9849 196 32965
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2259 5395.92 5755.72 5148 5823024 427953 32966

Misses in library cache during parse: 38


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 97483 26.30 31.78 55 2036 13 0
Execute 1686694 11158.13 12541.48 80778 13002299 139542209 27573302
Fetch 1658491 138.90 179.79 4511 1802589 286484 1593546
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3442668 11323.33 12753.05 85344 14806924 139828706 29166848

Misses in library cache during parse: 3950

67673 user SQL statements in session.
29901 internal SQL statements in session.
97574 SQL statements in session.
3926 statements EXPLAINed in this session.
********************************************************************************
Trace file: qbli_ora_3339.trc
Trace file compatibility: 8.00.04
Sort options: exeela
1 session in tracefile.
67673 user SQL statements in trace file.
29901 internal SQL statements in trace file.
97574 SQL statements in trace file.
4021 unique SQL statements in trace file.
3926 SQL statements EXPLAINed using schema:
SYSTEM.prof$plan_table
Default table was used.
Table was created.
Table was dropped.
3935416 lines in trace file.



Any suggestions on the enormous amount of time spent in the recursive calls? Thanks.