This month I'll focus on a couple of PL/SQL performance tips that will include how to utilize collections to radically increase row processing and a simple but useful coding tip on the benefits and pitfalls of some easy PL/SQL cursor coding techniques.
Cursor Coding Tips
Let’s start with a couple of simple techniques that most of us use to save coding. Part 1 (click here to review Part 1) of this series discussed using %ROWTYPE to get the column names and attributes easily into your program. I called it runtime inheritance. When the program hits this, it does a SELECT statement to retrieve these names and attributes. Well, you can select * into this rowtype, as I indicated.
Tip 1: If your table has lots of columns and you only need a few, don’t code the SELECT * but code the specific columns. Toad makes it very easy to name the cursor variable or the table name and it will do its ‘insites’ where a popup will appear and let you pick the column names. IF you do the SELECT * and only use a few of the columns, you are incurring a ton of network traffic for just a little bit of information. The network is the slow part anymore.
Bulk Binding and Row Processing
PL/SQL passes all SQL to the SQL engine for processing. This causes a context switch where PL/SQL will wait for the result set back from the SQL engine for each SQL statement processed.
Passing any kind of variable to SQL is called binding. The variables in the SQL statement are called bind variables. Passing an entire collection to a SQL statement in one step is called bulk binding. Bulk binding is very quick because the SQL is processed with very few context switches. The larger the collection, the more efficient bulk binding becomes.
This section will discuss various types of bulk binding such as:
Bulk collect works with
The number of elements returned to a collection can be controlled using the LIMITS syntax.
Bulk Collect works equally well with all the collection types.
Bulk Collect initializes and extends the Associative Array and the Nested Table collections automatically. Bulk Collect cannot automatically extend a VARRAY collection, however, this unit will illustrate the LIMIT command and a loop can easily be setup to load and extend a VARRAY as well.
For empinfo_rec IN ‘select * from empinfo’
***your code here***
The above code is used to do what I call ‘row at a time’ processing. This is an implicit cursor loop. PL/SQL creates the cursor, opens, reads, and closes the cursor for you. This code is fine when you are only looping through maybe a hundred rows or less. Oracle10 converted this to a cursor using bulk binding! So, technically, you don’t have to fix this code, Oracle fixed it for you.
Tip 2: I would use the bulk collect method below anytime I’m accessing more than just a handful of rows.
TYPE EMP_INFO_AA_TYPE IS TABLE OF
INDEX BY BINARY_INTEGER;
TYPE EMP_INFO_NT_TYPE IS TABLE OF USER0.EMP_INFO%ROWTYPE;
EMPNO_NT_TABLE EMP_INFO_NT_TYPE := EMP_INFO_NT_TYPE();
DBMS_OUTPUT.PUT_LINE('User0 Timer Starts');
SELECT * FROM user0.emp_info
BULK COLLECT into EMPNO_AA_Table;
Notice in the above code the key work ‘BULK COLLECT into EMPNO_AA_Table’ syntax. This will execute the SQL statement and populate all the returned rows into the array in one database action. All other syntax remains the same.
Bulk Collect Timing Example
The illustration on the left is not using bulk collect. In this example, using the bulk collect syntax on the right to load these 15000 rows was six times faster.
TYPE Emp_TYPE IS TABLE OF EMP.ENAME%TYPE;
CURSOR C_EMP IS
SELECT ENAME FROM EMP;
FETCH C_EMP BULK COLLECT INTO EMP_TABLE;
DBMS_OUTPUT.PUT_LINE(EMP_TABLE.COUNT || 'Rows Fetched');
FOR i in EMP_TABLE.FIRST .. EMP_TABLE.LAST
You can use Bulk Collect works equally well with a FETCH command. This method gives you a little better control over the cursor perhaps. Maybe you are converting other code to use this method and maybe this will allow for fewer changes to your code.
TYPE Emp_ENAME IS TABLE OF EMP.ENAME%TYPE INDEX BY BINARY_INTEGER;
BULK COLLECT INTO EMPNO_TABLE
EXIT WHEN EMPNO_TABLE.COUNT = 0;
-- Do some additional PL/SQL processing perhaps
DBMS_OUTPUT.PUT_LINE(C_EMP%ROWCOUNT || ' Rows Fetched so far');
This bulk collect illustrates the use of the LIMIT clause. This loop will bring back 1000 rows at a time. The <collection>.COUNT variable will contain the number of rows returned and when this variable is 0, then all the rows will have been returned. If you just check the cursor variable %NOTFOUND to exit, you might miss some rows.
Tip3: Even a limit of 100 rows will produce better performance than just returning a single row at a time.
Why is this important? Say you have a longer row size and you are building your collection off of a %ROWTYPE type syntax. You could make your PL/SQL routine larger than your assigned program global area will allow…causing your Oracle session to incur swapping or paging…an operating system memory management technique that will greatly slow your code execution.
Program Global Area Sizing
This SQL statement shows the total memory allocated and used by all PGA’s on a particular Oracle database. \
Collections, like cursors, are created and maintained in the user’s Program Global Area, or PGA. Each user gets one of these memory structures upon successful logon. The size of the collection is the element type * the number of elements.
Make sure your ‘PGA inuse’ is not larger than your ‘PGA allocated’ while your code is executing. You should check for this while coding and testing your code, not in production.
As always, you can ask me for the scripts and sample code.
Next in the series is using bulk binding to put rows back to the database super fast!
I hope you find these tips useful in your day to day use of the Oracle RDBMS.
Good day it may save processing time. It however makes for less maintainable and les readable code 2 o'clock in the morning when failure occurs, as line for line processing makes the insert debugging code easy. Coding this way is like C++ allowing you to code almost a complete program on one line. I was called to look at problems often in my life the code that was done in the slightly longer way maybe slower is fixed much easier than "NIFTY" fast code.. It is like "goto" programming it is bad programming it is done when the program is not designed but happens as the programmer types it. My first COBOL assignment on course worked perfect but 50 marks was deducted for I single go to it took 10 lines of extra code. On the saving of runtime todays hardware and operating systems and ORACLE it is no longer a motivation for sorry for the word SHITTY programing. Those programmers that had solid training will steer clear of these programming methods!
BULK COLLECT reduces context switches and increases performance. But nothing is free (except maybe deep down in quantum physics ;-) ) - the coin that you pay for this increase in performance is memory.
BULK COLLECT requires additional memory (PGA). The bigger the bulk collect, the less context switches, the more PGA is required.
Obviously there is a balance between performance improvements with decreased context switching and performance degredation due to increase in memory.
So main point - USE THE LIMIT CLAUSE. This allows you to turn the context switch vs memory knob up and down to tune performance.
Last but not least, for most optimized memory usage, use
commands whenever possible to release not used memory immediately.
Hope this helps,
My understanding is that the FOR LOOP is turned into a BULK COLLECT LIMIT 100 behind the scenes, so its performance should be very similar to an explicit BULK COLLECT (and this is what I've found in my testing - I've been unable to reproduced the 6x difference shown in the example above).
this is true. this directly depend of Oracle version