How to use Bulk Collect with Dynamic SQL or Can I use execute immediate with Bulk Collect? With this blog post I am trying to answer this question with very simple example. Yes we can use Bulk Collect with Dynamic SQLs and can improves performance by minimizing the number of context switches between the PL/SQL and SQL engines.
I have a "EMP" table with "11246872" records.
Lets run a sample code for Dynamic SQL in EMP table without using bulk-collect.
Above code is nice and simple, which we want to rewrite so that we can use performance benefits of bulk collect with dynamic sql. Lets modify above code of dynamic sql with bulk collect and execute it to check the performance.
We have saved a lot of context switching between the PL/SQL and SQL engines. As we can see, this simple change has given us more than 85% performance benefit. Always use Bulk Collect in your code, wherever you are planning to have simple cursor loop fetch.
Related Posts:
- Avoiding unnecessary function calls to optimize SQL statements
- Bulk Collect with Limit Clause and %NOTFOUND
- PLSQL Tuning: Bind Variables and execute immediate
- DBMS_PROFILER: How to analyze pl/sql performance
- Append String to CLOB in Optimized way
- Why do I prefer COALESCE over NVL
- Query optimization tips for Oracle
- Ensure quality and performance in plsql code using Oracle Compiler
- How to Get Execution Plan and Statistics of SQL Query
I have a "EMP" table with "11246872" records.
SQL> select count(*) from emp;
COUNT(*)
----------
11246872
Lets run a sample code for Dynamic SQL in EMP table without using bulk-collect.
SQL> declare
2 l_sql varchar2(4000);
3 l_row emp%rowtype;
4 c sys_refcursor;
5 begin
6 l_sql := 'select * from emp';
7 open c for l_sql;
8
9 loop
10 fetch c into l_row;
11 exit when c%notfound;
12 -- some operation
13 end loop;
14 close c;
15 end;
16 /
PL/SQL procedure successfully completed.
Elapsed: 00:03:12.84
Above code is nice and simple, which we want to rewrite so that we can use performance benefits of bulk collect with dynamic sql. Lets modify above code of dynamic sql with bulk collect and execute it to check the performance.
SQL> declare
2 l_sql varchar2(4000);
3 type t_tab is table of emp%rowtype index by binary_integer;
4 l_tab t_tab;
5 c sys_refcursor;
6 begin
7 l_sql := 'select * from emp';
8 open c for l_sql;
9 loop
10 fetch c bulk collect into l_tab limit 1000;
11
12 for i in 1..l_tab.count
13 loop
14 null;
15 -- some operation
16 end loop;
17 exit when c%notfound;
18 end loop;
19 close c;
20 end;
21 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:28.45
We have saved a lot of context switching between the PL/SQL and SQL engines. As we can see, this simple change has given us more than 85% performance benefit. Always use Bulk Collect in your code, wherever you are planning to have simple cursor loop fetch.
Related Posts:
- Avoiding unnecessary function calls to optimize SQL statements
- Bulk Collect with Limit Clause and %NOTFOUND
- PLSQL Tuning: Bind Variables and execute immediate
- DBMS_PROFILER: How to analyze pl/sql performance
- Append String to CLOB in Optimized way
- Why do I prefer COALESCE over NVL
- Query optimization tips for Oracle
- Ensure quality and performance in plsql code using Oracle Compiler
- How to Get Execution Plan and Statistics of SQL Query
This is the proper use for bulk collection with one small issue. The limit you set is 1000 and should actually be lowered to 100 since limits larger could impact performance in a negative way even though it will still be better than no bulk collection.
ReplyDeleteIt would say it depends on your rowsize, I have even done it using limit 10000 with better performance
DeleteHey Nimish, here's one problem I've encountered: Let's say that you are asked to bulk collect different rows everytime, not just the entire table. How do you declare a dynamic bulk collection object to bulk collect into from the cursor? Is there a way to define the bulk collection object after the cursor has been executed so that the bulk collection object has the same columns as the cursor?
ReplyDeleteYou may need to define a Record Type at database level with desired fields and then create a Table type of that record in your code
DeleteWell, the problem is how can I create those objects on the fly if I don't know the desired fields until the moment of execution. While in execution, I can only declare them in DECLARE section and then use them inside my execution block right? OR are you referring to perhaps creating a record collection type at database level using EXECUTE IMMEDIATE during execution and using that?
DeleteHi , In the first block you are fetching whole table but in the second block you are limiting to 1000 . I know that bulk collectbrings lot of performance change ,if you remove that limit then it brings more clarity .
ReplyDeleteusing bulk collect with no limit clause is a bad example of coding. It may eatup your memory
DeleteHi Nimish. Using c%notfound as EXIT condition is a bad idea there could result in loss of records to be processed. Use of EXIT WHEN l_tab.COUNT = 0 is a better and more safe way to do it. Best Regards Torben S. Nielsen
ReplyDeleteHi Nitish, I have a doubt..can you please clarify it?
ReplyDeleteIf I am storing an output of SQL query in any variable..let's say in this case it is l_sql variable...but my query is using a table for which the schema is not fixed and if I didn't return any result in one schema it should search for other schema and atleast when the output is there for that query it should return the whole result.
Please let me know the approach if you have..
Thanks.!!
Can we create EXECUTE IMMEDIATE ' Create Or replace type type_name is table number';
ReplyDeleteCan we create type
ReplyDeleteEXECUTE IMMEDIATE ' Create Or replace type type_name is table Of number';