NOCOPY

Oracle Database Tips by Donald Burleson

Releases of PL/SQL before 8.1 provided three parameter passing modes namely : IN, OUT and IN OUT. PL/SQL passes IN parameter by reference, IN OUT parameters by copy-in & copy-out mechanism and OUT parameters by copy-out mechanism.

The copy-in (for IN OUT) and copy-out (for IN OUT & OUT) imposes significant CPU and memory overhead particularly when the parameters involved are large data structures like nested tables, varrays, records etc.

The NOCOPY feature allows you to pass parameters to a procedure or function by reference avoiding CPU and memory overheads.

The following tables illustrate the performance improvement achieved by using the NOCOPY modifier:

Note: These figures are representative of the gains that might be had by using the NOCOPY mechanism. Actual timings may vary depending on the machine type and load etc.

Parameter Passed ----> Nested table (Employee nested Table)

Task	WITHOUT NOCOPY	WITH NOCOPY
Time Units	( Seconds )	( Seconds )
Incrementing Salary	13	1

Parameter Passed ----> User Defined Record (All Departments Rec)

Task	WITHOUT NOCOPY	WITH NOCOPY
Time Units	( Seconds )	( Seconds )|
Changing location of Department	8	0

Using the NOCOPY feature is as simple as:

PROCEDURE increment_salary_withnocopy(emp_tab IN OUT NOCOPY nt_all_employees,

                                        pcnt    IN            NUMBER);

No other changes are required to your code, just specify the NOCOPY clause after the IN OUT or OUT declarations in your package header, package body or procedure specification.

Use DBMS Packages

This tip goes more to coding efficiency, assume that oracle has already created a function or procedure to do most system related activities such as random numbers (DBMS_RANDOM) and LOB manipulations (DBMS_LOB) as well as other activities. When you need a system function related function or procedure the Oracle9i Supplied PL/SQL and Types reference Manual should be your first stop.  The DBMS packages that PL/SQL programmers should be familiar with are shown in the following table.

DBMS_SQL	Used to implement dynamic SQL, replaced by EXECUTE IMMEDIATE
DBMS_TRANSACTION	Used to alter transaction specific variables
DBMS_SYSTEM	Used to set tracing and other system activities
DBMS_PROFILER	Used to create profiles of PL/SQL objects
DBMS_DEBUG	Used to instrument PL/SQL programs for debugging
DBMS_APPLICATION_INFO	Used to place comments in V$SESSION to track PL/SQL usage
DBMS_ALERT	Used to generate alerts to other sessions
DBMS_PIPE	Used to send messages to other sessions and processes
DBMS_LOCK	Used to generate user defined locks, has useful SLEEP function
DBMS_LOB	Used to handle LOB data types
DBMS_METADATA	Used to recreate any database object
DBMS_MVIEW	Used to facilitate materialized view handline
DBMS_OLAP	Used to facilitate summary handling
DBMS_RLS	Used to generate virtual private databases, row level security
DBMS_ROWID	Used to manipulate ROWID values
DBMS_SESSION	used to manipulate session variables
DBMS_TRACE	Used to trace program executions when programs compiled with DEBUG
UTL_FILE	Used to generate IO to and from files from PL/SQL
UTL_HTTM	Used to retrieve HTML
UTL_SMTP	Used to incorporate mail into PL/SQL
UTL_TCP	Facilitates TCP communitations from PL/SQL
UTL_REF	Facilitates use of REF values in UDTs
UTL_RAW	Facilitates the use of RAW values
UTL_URL	Provides escaping of URL codes for PL/SQL processing
ANYDATA	provides data transformation for Oracle

EXECUTE IMMEDIATE Verses DBMS_SQL

Using EXECUTE IMMEDIATE rather than DBMS_SQL reduces code complexity and improves efficiency, for example, look at Figure 25.

create or replace PROCEDURE get_count (
    tab_name  IN VARCHAR2,
rows OUT NUMBER
)  AS
  cur     INTEGER;
  ret     INTEGER;
 com_string   VARCHAR2(100);
 row_count      NUMBER;
BEGIN
 com_string :=
  'SELECT /* DBA_UTIL.get_count */ count(1) row_count FROM '||tab_name;
 cur := DBMS_SQL.OPEN_CURSOR;
 DBMS_SQL.PARSE(cur,com_string,dbms_sql.v7);
 DBMS_SQL.DEFINE_COLUMN(cur, 1, row_count);
 ret := DBMS_SQL.EXECUTE(cur);
 ret := DBMS_SQL.FETCH_ROWS(cur);
 DBMS_SQL.COLUMN_VALUE(cur, 1, row_count);
 DBMS_SQL.CLOSE_CURSOR(cur);
 DBMS_OUTPUT.PUT_LINE('Count='||TO_CHAR(row_count));
 rows:=row_count;
EXCEPTION
WHEN others THEN
null;
END;

Figure 25: Procedure using DBMS_SQL

Using EXECUTE IMMEDIATE the procedure would be altered to Figure 26.

CREATE OR REPLACE PROCEDURE get_count2 (
    tab_name  IN VARCHAR2,
    rows OUT NUMBER
)  AS
 com_string   VARCHAR2(100);
 row_count number;
BEGIN
 com_string :=
  'SELECT count(1) FROM '||tab_name;
 EXECUTE IMMEDIATE com_string INTO row_count;
 rows:=row_count;
 DBMS_OUTPUT.PUT_LINE('Count='||TO_CHAR(row_count));
 EXCEPTION
  WHEN others THEN
  NULL;
END;

Figure 26: Use of EXECUTE IMMEDIATE

As you can see the code complexity is greatly reduced. For this limited example performance differences weren't detectable, however in a LOOP situation they should be substantial. By placing a LOOP to execute the code inside the two procedures above 1000 times the first procedure required 15:19:03 to complete the 1000 loops, the second completed the 1000 loops in a time of 14:56.03.

Alter system parameters to enhance performance

For parameters that control PGA and memory areas, sometimes increasing a sort or hash area can make a dramatic difference in code execution time.