Tip#8 Cursor best practice

Cursor best practice

Oracle provides three possible way to loop through the cursor rows and these are,

1. loop with explicit cursor i.e. OPEN, FETCH one row at a time and CLOSE

2. Using cursor FOR-LOOP

3. Bulk collect and then loop through the collection.

Which of the above methods is most efficient for a cursor?

Without a doubt, you should use BULK COLLECT whenever possible to query information from your tables. It will be significantly faster than either a cursor FOR loop or loop with explicit cursor. However, a BULK COLLECT of many rows can consume a large amount of memory. But you can balance memory utilization against performance improvements by using the LIMIT clause with BULK COLLECT. Here's an example:

DECLARE
CURSOR allrows_cur
IS
SELECT *
FROM order_header_scratch;

TYPE order_header_scratch_aat IS TABLE OF order_header_scratch%ROWTYPE
INDEX BY BINARY_INTEGER;

l_order_header_scratch order_header_scratch_aat;
l_row PLS_INTEGER;
BEGIN
OPEN allrows_cur;

LOOP
FETCH allrows_cur
BULK COLLECT INTO l_order_header_scratch LIMIT 100;

-- Remember: BULK COLLECT will NOT raise NO_DATA_FOUND if no rows
-- are queried. Instead, check the contents of the collection to
-- see if you have anything left to process.
EXIT WHEN l_order_header_scratch.COUNT = 0;

-- Process the data, if any.
l_row := l_order_header_scratch.FIRST;

WHILE (l_row IS NOT NULL)
LOOP
-- Some processing ....
process_order_header_scratch (l_order_header_scratch (l_row));

-- get the next row
l_row := l_order_header_scratch.NEXT (l_row);
END LOOP;
END LOOP;

-- Clean up when done: close the cursor and delete everything
-- in the collection.
CLOSE allrows_cur;

l_order_header_scratch.DELETE;
END;

EDIT : I just happen to read an interesting article regarding cursor for-loop and would certainly suggest to read it. LINK .

Also I forgot to mention why bulk collect is faster compare to other, the biggest advantage of BULK COLLECT is: BULKING! obviously :)

In the cursor Oracle must refresh the binds every loop with the new data. Also the Insert is executed each time! With BULK COLLECT Oracle can put all the values into a collection at once. No need to refresh something again and again hence no soft parses, reading of the new values of the binds…

Tip#7 SQL*Plus Command Line History

Thanks to Howard Rogers( Link ) , I got a very useful utility to have command line history for SQL*Plus on Linux. This small utility makes life so much easier for me especially as most of the time I do work on Linux platform. All you have to do is

1. Download a rpm (Link)

2. As root user, Install it -> rpm -ivh rlwrap-0.18-1.i386.rpm

3. Create alias for SQL*Plus in the user profile (Bash -> .bash_profile , Ksh -> .profile )
alias sqlplus='rlwrap sqlplus'

This will make sure that every time you login and run sql*Plus, "rlwrap" captures all SQL*Plus commands which you can just recall with up/down arrow key. Other nice feature is search capability of this utility, you can press Ctrl+R and type in key word to search for in your historical SQLs.

I really find this utility very helpful, hope it helps you too!!!

Tip#6 Compile Invalid Objects

The Oracle database will invalidate objects if a dependent object is changed. In development environment it is sometimes pain as code is deployed very often and objects like packages, procedures and functions keep changing and need to compile all dependent objects.

Earlier most of the time we use a script to recompile invalid objects like PL/SQL packages and package bodies which may need to run it more than once for dependencies. But then as the number of objects grows so as dependencies and hence the number of times the compile script has to run.

So to avoid executing multiple times the compile script, we created following stored procedure which compiles in hierarchical order to take care of dependencies. Since we had a inter schema dependencies as well and wanted each schema owner to compile there own objects we pass a parameter which is a schema owner who will be compiling their own invalid objects in a schema. As of now inter schema dependencies is not table driven but I will implement it soon.

Please note to have generic solution, it needs to be created in a schema with DBA rights e.g. in Dev I created in SYS and then grant execute access to the schema owners.

CREATE OR REPLACE PROCEDURE Compile_Invalid_Objects
(In_Owner IN VARCHAR2)
IS
CURSOR obj_Cur IS
SELECT 'ALTER '
||DECODE(a.Object_Type,'PACKAGE BODY','PACKAGE',
'TYPE BODY','TYPE',
'SYNONYM',DECODE(a.Owner,'PUBLIC','PUBLIC SYNONYM',
'SYNONYM'),
a.Object_Type)
||' '
||DECODE(a.Owner,'PUBLIC',NULL,
a.Owner
||'.')
||a.Object_Name
||DECODE(a.Object_Type,'JAVA CLASS',' RESOLVE',
' COMPILE')
||DECODE(a.Object_Type,'PACKAGE BODY',' BODY',
'TYPE BODY','BODY') Text
FROM sys.dba_Objects a,
(SELECT MAX(LEVEL) dLevel,
Object_Id
FROM sys.Public_Dependency
START WITH Object_Id IN (SELECT Object_Id
FROM sys.dba_Objects
WHERE Status = 'INVALID'
AND Owner = Upper(In_Owner))
CONNECT BY Object_Id = PRIOR Referenced_Object_Id
GROUP BY Object_Id) b
WHERE a.Object_Id = b.Object_Id (+)
AND a.Status = 'INVALID'
AND a.Owner = Upper(In_Owner)
ORDER BY b.dLevel DESC,
a.Object_Name ASC;
BEGIN
FOR obj_rec IN obj_Cur LOOP
BEGIN
EXECUTE IMMEDIATE obj_rec.Text;
EXCEPTION
WHEN OTHERS THEN
util.Error_Handler('compile_invalid_objects'); -- Log any error
END;
END LOOP;

EXCEPTION
WHEN OTHERS THEN
util.Error_Handler('compile_invalid_objects');
END Compile_Invalid_Objects
/*-----------------------------------------------------------------------------

NAME: compile_invalid_objects
DESC: Compile all invalid objects for the given USER
usage : exec compile_invalid_objects ('SCOTT');
-----------------------------------------------------------------------------*/
;
/

Grant access to SCOTT user,

grant execute on sys.compile_invalid_objects to SCOTT;

I also create a public synonym for the SP,

CREATE PUBLIC SYNONYM COMPILE_INVALID_OBJECTS FOR SYS.COMPILE_INVALID_OBJECTS;