Transcript PL/SQL Bulk Collect performance tests

PL/SQL Bulk Collections
in Oracle 9i and 10g
Kent Crotty
Burleson Consulting
October 13, 2006
AGENDA
•
•
•
•
Performance gains with Bulk Processing
Array processing with BULK COLLECT and
FORALL
Oracle 10g FORALL improvements.
Error handling.
Top Books on PL/SQL
Dr. Tim Hall Oracle ACE
And Oracle’s ACE of the Year
John Garmany
A fantastic beginner book on PL/SQL
Bulk Processing
• Supercharge your PL/SQL code with BULK COLLECT
and FORALL
• Working at a table-level instead of the row-level
• Simple and easy to use
PL/SQL Code
• Consists of two types of statements
Procedural (declare, begin, if, while, for …)
SQL (select, insert, update, delete)
• Oracle has two engines to process that information
PL/SQL Engine
SQL Engine
• A Content Switch occurs each time the PL/SQL engine needs to
execute a SQL statement
• Switches are fast but large loops can cause performance delays
Context Switches
Oracle Server
Session PL/SQL
Block
PL/SQL
Block
PL/SQL Engine
PL/SQL
Block
SQL
Procedural Statement
Executor
Data
SQL Statement
Executor
SQL Engine
PL/SQL Code
Consider this procedure code
CREATE OR REPLACE PROCEDURE update_price (
product_type_in IN product.product_type%TYPE,
multiplier_in
IN number(2,2) )
IS
CURSOR products_cur IS
SELECT product_id, product_price
FROM products WHERE product_type = product_type_in;
BEGIN
FOR prod_rec IN products_cur LOOP
UPDATE products
SET product_price = product_price * multiplier_in
WHERE product_id = prod_rec.product_id;
END LOOP;
END update_price;
PL/SQL Code
FOR prod_rec IN products_cur LOOP
UPDATE products
SET product_price = product_price * multiplier_in
WHERE product_id = prod_rec.product_id;
END LOOP;
• For each iteration of this loop, there is going to be a conventional
bind and a context switch!
• Overhead for these statements can be large
• But there is a solution – Bulk Collections
Bulk Collection Categories
• SELECT or FETCH statements
BULK COLLECT INTO
• Out-Bind binding
RETURNING clause
• In-Bind binding
FORALL – INSERT, UPDATE, DELETE
SELECT / FETCH statements
Data may be Bulk Collected/Fetched into:
Table.column%TYPE
Record of arrays
Table%ROWTYPE
Cursor%ROWTYPE
Array of records
Nested tables
Products Table
SQL> create table products (
2 product_id number,
3 product_name varchar2(15),
4 effective_date date );
Table created.
SQL> begin -- inserting 100000 records into the products table
1 for i in 1 .. 100000 loop
2 insert into products values (i, 'PROD'||to_char(i),sysdate-1);
3 end loop;
4 end;
5 /
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
BULK COLLECT clause
•
•
•
•
Used in a SELECT statement
Binds the result set of the query to a
collection
Much less communication between the
PL/SQL and SQL engines
All variables in the INTO clause must be a
collection
BULK COLLECT
SET SERVEROUTPUT ON
DECLARE
TYPE prod_tab IS TABLE OF products%ROWTYPE;
products_tab
prod_tab := prod_tab();
start_time
number;
end_time
number;
BEGIN
start_time := DBMS_UTILITY.get_time;
FOR prod_rec in (SELECT * FROM products
WHERE effective_date BETWEEN sysdate - 2 AND TRUNC(sysdate))
LOOP
products_tab.extend;
products_tab(products_tab.last) := prod_rec;
END LOOP;
end_time := DBMS_UTILITY.get_time;
DBMS_OUTPUT.PUT_LINE(‘Conventional (‘||products_tab.count||’):
’||to_char(end_time-start_time));
Start_time := DBMS_UTILITY.get_time;
SELECT *
BULK COLLECT INTO products_tab
FROM products
WHERE effective_date BETWEEN sysdate - 2 AND TRUNC(sysdate);
end_time := DBMS_UTILITY.get_time;
DBMS_OUTPUT.PUT_LINE(‘Bulk Collect (‘||products_tab.count||’):
’||to_char(end_time-start_time));
END;
BULK COLLECT
SQL> /
Conventional (100000): 40
Bulk Collect (100000): 27
PL/SQL procedure successfully completed.
Bulk Collect is quite a bit faster!
BULK COLLECT – Explicit Cursor- Fetch
DECLARE
TYPE prod_tab IS TABLE OF products%ROWTYPE;
products_tab
prod_tab := prod_tab();
start_time
number;
end_time
number;
CURSOR products_data IS SELECT * FROM products;
BEGIN
start_time := DBMS_UTILITY.get_time;
OPEN products_data;
LOOP
products_tab.extend;
FETCH products_data INTO products_tab(products_tab.last);
IF products_data%NOTFOUND THEN
products_tab.delete(products_tab.last);
EXIT;
END IF;
END LOOP;
CLOSE products_data;
end_time := DBMS_UTILITY.get_time;
DBMS_OUTPUT.PUT_LINE(‘Conventional (‘||products_tab.count||’):
’||to_char(end_time-start_time));
Start_time := DBMS_UTILITY.get_time;
OPEN products_data;
FETCH products_data BULK COLLECT INTO products_tab;
CLOSE products_data;
end_time := DBMS_UTILITY.get_time;
DBMS_OUTPUT.PUT_LINE(‘Bulk Collect (‘||products_tab.count||’):
’||to_char(end_time-start_time));
END;
BULK COLLECT
SQL> /
Conventional (100000): 117
Bulk Collect (100000): 14
PL/SQL procedure successfully completed.
Bulk Collect is significantly faster – over 8 times!
BULK COLLECT - LIMIT
• Collections are arrays held in memory – massive
collections can eat up all the memory
• By using the LIMIT clause, we can now process the
result set in chunks
• Explicit cursors must be used with the LIMIT clause
BULK COLLECT – Explicit Cursor- LIMIT
DECLARE
TYPE prod_tab IS TABLE OF products%ROWTYPE;
products_tab
prod_tab := prod_tab();
start_time number;
end_time
number;
CURSOR products_data IS SELECT * FROM products;
BEGIN
Start_time := DBMS_UTILITY.get_time;
OPEN products_data;
LOOP
FETCH products_data BULK COLLECT INTO products_tab LIMIT 10000;
EXIT WHEN products_data%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Processed '||to_char(products_tab.count)||' rows');
END LOOP;
CLOSE products_data;
end_time := DBMS_UTILITY.get_time;
DBMS_OUTPUT.PUT_LINE('Bulk Collect: ‘||to_char(end_time-start_time));
end;
Result Set is limited to only 10000 rows – more memory efficient!
Will the processing be slower because of the LIMIT?
BULK COLLECT – Explicit Cursor- LIMIT
SQL> /
Processed 10000 rows
Processed 10000 rows
Processed 10000 rows
Processed 10000 rows
Processed 10000 rows
Processed 10000 rows
Processed 10000 rows
Processed 10000 rows
Processed 10000 rows
Processed 10000 rows
Bulk Collect: 15
PL/SQL procedure successfully completed.
Yes, but only slightly. Still over 8 times better than conventional!
BULK COLLECT - RETURNING
• The RETURNING clause can be used to return
specific columns after a DML statement
• This is referred to as OUT-BINDING
DECLARE
TYPE prod_tab IS TABLE OF products.product_id%TYPE;
products_tab
prod_tab := prod_tab();
BEGIN
DELETE FROM products
WHERE product_id > 20000
RETURNING product_id BULK COLLECT INTO products_tab;
DBMS_OUTPUT.PUT_LINE('Deleted Product Ids: '||
products_tab.count||' rows');
end;
SQL> /
Deleted Product Ids: 80000 rows
PL/SQL procedure successfully completed.
BULK COLLECT Use Considerations
•
•
Use the LIMIT clause to manage
memory requirements
NO_DATA_FOUND will not be raised
if no records are returned – check
contents to make sure records are
retrieved
BULK COLLECT - FORALL
• We have seen BULK COLLECT with the SELECT
statements
• For the INSERT, UPDATE and DELETE
statements there is the FORALL statement
• This is referred to as IN-BINDING
BULK COLLECT – INSERT - FORALL
DECLARE
TYPE prod_tab IS TABLE OF products%ROWTYPE;
products_tab
prod_tab := prod_tab();
start_time number; end_time
number;
BEGIN
-- Populate a collection - 100000 rows
SELECT * BULK COLLECT INTO products_tab FROM products;
EXECUTE IMMEDIATE 'TRUNCATE TABLE products';
Start_time := DBMS_UTILITY.get_time;
FOR i in products_tab.first .. products_tab.last LOOP
INSERT INTO products (product_id, product_name, effective_date)
VALUES (products_tab(i).product_id, products_tab(i).product_name,
products_tab(i).effective_date);
END LOOP;
end_time := DBMS_UTILITY.get_time;
DBMS_OUTPUT.PUT_LINE(‘Conventional Insert: ’||to_char(end_time-start_time));
EXECUTE IMMEDIATE 'TRUNCATE TABLE products';
Start_time := DBMS_UTILITY.get_time;
FORALL i in products_tab.first .. products_tab.last
INSERT INTO products VALUES products_tab(i);
end_time := DBMS_UTILITY.get_time;
DBMS_OUTPUT.PUT_LINE(‘Bulk Insert: ’||to_char(end_time-start_time));
COMMIT;
END;
BULK COLLECT – INSERT - FORALL
SQL> /
Conventional Insert: 686
Bulk Insert: 22
PL/SQL procedure successfully completed.
The Bulk Operation is considerably faster!
BULK COLLECT – UPDATE - FORALL
DECLARE
TYPE prod_id_tab is TABLE OF products.product_id%TYPE;
TYPE prod_tab IS TABLE OF products%ROWTYPE;
products_id_tab prod_id_tab := prod_id_tab();
products_tab
prod_tab := prod_tab();
start_time number;
end_time
number;
BEGIN
-- Populate a collection - 10000 rows
FOR i in 1 .. 10000 LOOP
products_id_tab.extend;
products_id_tab(products_id_tab.last) := i+10;
products_tab.extend;
products_tab(products_tab.last).product_id := i;
END LOOP;
Start_time := DBMS_UTILITY.get_time;
FOR i in products_tab.first .. products_tab.last LOOP
UPDATE products SET ROW = products_tab(i) -- ROW available in 9.2
WHERE product_id = products_tab(i).product_id;
END LOOP;
end_time := DBMS_UTILITY.get_time;
DBMS_OUTPUT.PUT_LINE('Conventional Update: '||to_char(end_time-start_time));
Start_time := DBMS_UTILITY.get_time;
FORALL i in products_tab.first .. products_tab.last
UPDATE products SET ROW = products_tab(i) – ROW available in 9.2
WHERE product_id = products_id_tab(i);
end_time := DBMS_UTILITY.get_time;
DBMS_OUTPUT.PUT_LINE('Bulk Update: '||to_char(end_time-start_time));
END;
BULK COLLECT – UPDATE - FORALL
SQL> /
Conventional Update: 301
Bulk Update: 116
PL/SQL procedure successfully completed.
The Bulk Operation is again considerably faster!
FORALL Use
•
•
•
•
Only a single DML statement is
allowed per FORALL
In 9i, the binding array must be
sequentially filled
Use SAVE EXCEPTIONS to continue
past errors
SQL%BULK_ROWCOUNT returns the
number of affected rows
The Finer Points
• Use bulk bind techniques for recurring SQL
statements in a PL/SQL loop.
• Bulk bind rules:
•
•
•
•
Can be used with any type of collection
Collection subscripts cannot be expressions
Collections should be densely filled
If error, statement is rolled back. Prior
successful DML statements are not rolled back.
• Bulk Collects
• Can be used with implicit or explicit cursors
• Collection is always filled sequentially starting
with 1
New in 10g – FORALL Improvements
•
•
•
FORALL driving array no longer needs to
be processed in sequential order
The INDICES OF clause is used to
reference the row numbers defined in
another array
The VALUES OF clause is used to
reference the values defined in another
array
New in 10g – FORALL - INDICES
DECLARE
TYPE prod_id_tab is TABLE OF BOOLEAN INDEX BY PLS_INTEGER;
TYPE prod_tab IS TABLE OF products%ROWTYPE;
products_id_tab prod_id_tab := prod_id_tab();
products_tab prod_tab := prod_tab();
BEGIN
products_tab(10).effective_date := sysdate;
products_tab(100).effective_date := sysdate + 10;
products_tab(1000).effective_date := sysdate + 100;
products_id_tab(10) := TRUE;
products_id_tab(100) := TRUE;
products_id_tab(1000) := TRUE;
FORALL i IN INDICES OF products_id_tab
UPDATE products SET ROW = products_tab(i)
WHERE product_id = products_id_tab(i);
END;
New in 10g – FORALL - VALUES
DECLARE
TYPE prod_id_tab is TABLE OF BOOLEAN INDEX BY PLS_INTEGER;
TYPE prod_tab IS TABLE OF products%ROWTYPE;
products_id_tab prod_id_tab := prod_id_tab();
products_tab prod_tab := prod_tab();
BEGIN
products_tab(10).effective_date := sysdate;
products_tab(100).effective_date := sysdate + 10;
products_tab(1000).effective_date := sysdate + 100;
products_id_tab(100) := 10;
products_id_tab(200) := 100;
products_id_tab(300) := 1000;
FORALL i IN VALUES OF products_id_tab
UPDATE products SET ROW = products_tab(i)
WHERE product_id = products_id_tab(i);
END;
FORALL – Error Handling
• No more FULL rollback in case of an
EXCEPTION
• SAVE EXCEPTIONS clause
• SQL%BULK_EXCEPTIONS – Collection of
records
• SQL%BULK_EXCEPTIONS(i).ERROR_INDEX –
stores iteration “i” when exception is raised
• SQL%BULK_EXCEPTIONS(i).ERROR_CODE –
stores the Oracle error code
• SQL%BULK_EXCEPTIONS.count – returns the
count of the exceptions
Context Switches – Conventional Binds
Oracle Server
Session PL/SQL
Block
PL/SQL
Block
PL/SQL Engine
PL/SQL
Block
SQL
Procedural Statement
Executor
Data
SQL Statement
Executor
SQL Engine
Context Switches – Bulk Binds
Oracle Server
Session PL/SQL
Block
PL/SQL
Block
PL/SQL Engine
PL/SQL
Block
SQL
Procedural Statement
Executor
Data
SQL Statement
Executor
SQL Engine