Looking for some extra help in cutting down execution times on SQL’s:
Below are my steps in DB package - they are simple but step2 and step3 have large execution times.
SELECT <primary_key> BULK COLLECT INTO < some_array>
ORDER BY <input_parameter>
**The execution time is fast in milliseconds as there are indexes on where/order_by columns**
**PARENT_TABLE has nearly 1 B rows**
This is a dynamic SQL with combination of 10 input parameters to the DB package.
SELECT <50 parent_columns> BULK COLLECT INTO < some_array>
WHERE <primary_key from step1>
** The execution time takes up to 35 seconds – very slow**
** I tried selecting all 50 columns in Step1 itself but it was taking a long time as it would order all the 50 columns – execution time up to 40 seconds**
I get about 3000 ID’s from step 1. What is the best way to pass 3000 ID’s in Step 2 SQL? Any optimizers that I could use?
I could only think of:
1. Gather input from Step 1 into Oracle array via bulk collect – using for loop, go through Oracle array and issue individual SQL statements and gather results.
2. Gather input from Step 1 into Oracle array via bulk collect - cast Oracle array as table and use IN clause, like:
SELECT <50 columns> BULK COLLECT INTO <some_array>
WHERE primary_key in
(SELECT column_value FROM THE
(SELECT CAST(oracle_array() as oracle_array) FROM DUAL))
WHERE <1200 ID’s from step2>
WHERE <800 ID’s from step2>
** The execution time takes up to 20 seconds – very slow**
**The where column has index**
**CHILD1_TABLE has 80 M rows and CHILD2_TABLE has 140 M rows**
From output in step 2, there is indicator on parent table that tells me how many ID’s have child records.
For example, from 3000 ID’s, I can filter them down to 1200 ID’s and 800 ID’s respectively and only pass those to step 3.
Again in Step 3, I tried to use the same approach as Step2_1 and Step2_2 – got same delayed execution times.
DBA did all the tracing, tkprof’ing etc.. We found that only I/O had big numbers.
I know the above description is little abstract but I tried to put the design approach out there. Please let me know if I need to take some different approach/design.