Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

Oracle NOCOPY Hint Tips


Oracle Tips by Burleson Consulting

 

The following Tip is from the outstanding book "Oracle PL/SQL Tuning: Expert Secrets for High Performance Programming" by Dr. Tim Hall, Oracle ACE of the year, 2006:

The NOCOPY hint tells the PL/SQL compiler to pass OUT and IN OUT parameters by reference, rather than by value.

Also see these notes on using nocopy in PL/SQL

When parameters are passed by value, the contents of the OUT and IN OUT parameters are copied to temporary variables, which are then used by the subprogram being called.  On successful completion of the subprogram the values are copied back to the actual parameters, but unhandled exceptions result in the original parameter values being left unchanged.  The process of copying large parameters, such as records, collections, and objects requires both time and memory which affects performance.

With the NOCOPY hint the parameters are passed by reference and on successful completion the outcome is the same, but unhandled exceptions may leave the parameters in an altered state, so programs must handle errors or cope with the suspect values.

The nocopy.sql script compares the performance of both methods by passing a populated collection as a parameter.

nocopy.sql

SET SERVEROUTPUT ON
DECLARE
   TYPE     t_tab IS TABLE OF VARCHAR2(32767);
   l_tab    t_tab := t_tab();
   l_start  NUMBER;

   PROCEDURE in_out (p_tab  IN OUT  t_tab) IS
   BEGIN
     NULL;
   END;  

   PROCEDURE in_out_nocopy (p_tab  IN OUT NOCOPY  t_tab) IS
   BEGIN
     NULL;
   END;
BEGIN
   l_tab.extend;
   l_tab(1) := '1234567890123456789012345678901234567890';
   l_tab.extend(999999, 1);  -- Copy element 1 into 2..1000000


   -- Time normal IN OUT
   l_start := DBMS_UTILITY.get_time;

   in_out(l_tab);

   DBMS_OUTPUT.put_line('IN OUT       : ' ||
                        (DBMS_UTILITY.get_time - l_start));

   -- Time IN OUT NOCOPY
   l_start := DBMS_UTILITY.get_time;

   in_out_nocopy(l_tab);  -- pass IN OUT NOCOPY parameter

   DBMS_OUTPUT.put_line('IN OUT NOCOPY: ' ||
                        (DBMS_UTILITY.get_time - l_start));
END;
/

The output of the script clearly demonstrates the performance improvements possible when using the NOCOPY hint.

SQL> @nocopy.sql

IN OUT       : 122
IN OUT NOCOPY: 0

PL/SQL procedure successfully completed.

SQL>

The use of the NOCOPY hint does come with some drawbacks, as it can result in parameter aliasing when global variables are passed as parameters to subprograms.  A parameter alias occurs when two variable names point to the same memory location.  The update_global.sql script shows how aliasing can occur if a global variable is accessed directly from a procedure when it has also been passed as a NOCOPY parameter.

update_global.sql

SET SERVEROUTPUT ON
DECLARE
  l_global  NUMBER := 10; 

  PROCEDURE update_global (p_number  IN OUT NOCOPY  NUMBER) IS
  BEGIN
    DBMS_OUTPUT.put_line('Started update_global');
    DBMS_OUTPUT.put_line('p_number=' || p_number);
    p_number := 20;
    DBMS_OUTPUT.put_line('p_number := 20 : l_global=' || l_global);
    l_global := 30;
    DBMS_OUTPUT.put_line('l_global := 30 : l_global=' || l_global);
    DBMS_OUTPUT.put_line('Finished update_global');
  END;
BEGIN
   DBMS_OUTPUT.put_line('Start: l_global=' || l_global);

   update_global(l_global);

   DBMS_OUTPUT.put_line('End: l_global=' || l_global);
END;
/

The results from this script are displayed below.

SQL> @update_global.sql
Start: l_global=10
Started update_global
p_number=10
p_number := 20 : l_global=20
l_global := 30 : l_global=30
Finished update_global
End: l_global=30

PL/SQL procedure successfully completed.

This shows that the value of the global variable is being set using both methods.  It isnít a problem if this is the intention of the programmer, but it can lead to problems if the programmer believes they have assigned values to two separate variables. Aliasing can also occur when the same actual parameter is passed multiple times to a procedure or function, as shown by the multiple_params.sql script.

multiple_params.sql

SET SERVEROUTPUT ON
DECLARE
  l_global  NUMBER := 10;

  PROCEDURE multiple_params (p_in             IN             NUMBER,
                             p_in_out         IN OUT         NUMBER,
                             p_in_out_nocopy  IN OUT NOCOPY  NUMBER) IS
  BEGIN
    DBMS_OUTPUT.put_line('Started multiple_params');
    DBMS_OUTPUT.put_line('p_in=' || p_in);
    p_in_out := 20;
    DBMS_OUTPUT.put_line('p_in_out := 20 : p_in=' || p_in);
    p_in_out_nocopy := 30;
    DBMS_OUTPUT.put_line('p_in_out_nocopy := 30 : p_in=' || p_in);
    DBMS_OUTPUT.put_line('Finished multiple_params');
  END multiple_params;
BEGIN
   DBMS_OUTPUT.put_line('Start: l_global=' || l_global);

   multiple_params(l_global, l_global, l_global);

   DBMS_OUTPUT.put_line('End: l_global=' || l_global);
END;
/

The result of this script is shown below.

SQL> @multiple_params.sql
Start: l_global=10
Started multiple_params
p_in=10
p_in_out := 20 : p_in=10
p_in_out_nocopy := 30 : p_in=30
Finished multiple_params
End: l_global=20

PL/SQL procedure successfully completed.

Here, the global variable l_global is initialized with the value 10 and is passed into the multiple_params procedure three times.  Setting the value of the p_in_out parameter has no affect on the p_in parameter, as p_in_out is passed by value.  Setting the p_in_out_nocopy parameter changes the value of p_in immediately as p_in_out_nocopy is passed by reference.  Once the procedure completes the value of the p_in_out parameter, it is copied back from the temporary variable, setting the final value of l_global to 20, overwriting the previous value of 30.  As a result, an unsuspecting programmer may believe the final value should be 30.

The fact that NOCOPY is a hint not a directive means that the compiler can ignore it and pass parameters by value without producing errors.  To quote the "PL/SQL User's Guide and Reference 10g Release 1" from Oracle, the situations where the NOCOPY hint will be ignored include:

  • The actual parameter is an element of an associative array. This restriction does not apply if the parameter is an entire associative array.

  • The actual parameter is constrained, such as by scale or NOT NULL. This restriction does not apply to size-constrained character strings. This restriction does not extend to constrained elements or attributes of composite types.

  • The actual and formal parameters are records, one or both records were declared using %ROWTYPE or %TYPE, and constraints on corresponding fields in the records differ.

  • The actual and formal parameters are records, the actual parameter was declared (implicitly) as the index of a cursor FOR loop, and constraints on corresponding fields in the records differ.

  • Passing the actual parameter requires an implicit datatype conversion.

The subprogram is called through a database link or as an external procedure.

Next, the performance gains associated with using native integer types over internal oracle numeric types will be reviewed.

 
Get the Complete
Oracle SQL Tuning Information 

The landmark book "Advanced Oracle SQL Tuning  The Definitive Reference"  is filled with valuable information on Oracle SQL Tuning. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.

 


 

 
��  
 
 

 
 
 
 
oracle dba poster
 

 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2014

All rights reserved by Burleson

Oracle © is the registered trademark of Oracle Corporation.