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

 
 Home
 E-mail Us
 Oracle Articles
New 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 


 

 

 


 

 

 
 

Declarations, Blocks, Functions and Procedures in Loops


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 declaration of types, variables and cursors within loops is inefficient as all memory management is performed once for each iteration of the loop.  If these declarations are placed outside the loop the associated memory management is only performed once for the entire loop.  The declare_in_loop.sql script compares an internal and external variable declaration.

declare_in_loop.sql

SET SERVEROUTPUT ON
DECLARE
  l_loops   NUMBER := 1000000;
  l_start   NUMBER;
  l_number  NUMBER;
BEGIN
  -- Time internal declaration.
  l_start := DBMS_UTILITY.get_time;

  FOR i IN 1 .. l_loops LOOP
    DECLARE
      l_number  NUMBER;
    BEGIN
      l_number := i;
    END;
  END LOOP;

  DBMS_OUTPUT.put_line('Internal declaration: ' ||
                       (DBMS_UTILITY.get_time - l_start));

  -- Time external declaration.
  l_start := DBMS_UTILITY.get_time;

  FOR i IN 1 .. l_loops LOOP
    BEGIN
      l_number := i;
    END;
  END LOOP;

  DBMS_OUTPUT.put_line('External declaration: ' ||
                       (DBMS_UTILITY.get_time - l_start));
END;
/

The output from this script shows that the effect of even a single declaration is measurable.

SQL> @declare_in_loop.sql
Internal declaration: 24
External declaration: 19

PL/SQL procedure successfully completed.

Whilst discussing this issue with a colleague I was asked if placing an anonymous block with no declarations inside a loop was a performance problem. The block_in_loop.sql script is a variation on the previous script which provides an answer this question.

block_in_loop.sql

SET SERVEROUTPUT ON
DECLARE
  l_loops   NUMBER := 1000000;
  l_start   NUMBER;
  l_number  NUMBER;
BEGIN
  -- Time block.
  l_start := DBMS_UTILITY.get_time;

  FOR i IN 1 .. l_loops LOOP
    BEGIN
      l_number := i;
    EXCEPTION
      WHEN OTHERS THEN
        NULL;
    END;
  END LOOP;

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

  -- Time no block.
  l_start := DBMS_UTILITY.get_time;

  FOR i IN 1 .. l_loops LOOP
    l_number := i;
  END LOOP;

  DBMS_OUTPUT.put_line('No block: ' ||
                       (DBMS_UTILITY.get_time - l_start));
END;
/

The output from this script shows that there is no perceivable impact created by placing an anonymous block within a loop provided it contains no declarations.

SQL> @block_in_loop.sql
Block   : 20
No block: 20

PL/SQL procedure successfully completed.

Based on this information declarations within loops are removed from the code and the result is that procedures and functions are being called within loops that internally contain variable declarations.  Does this have an impact? The function_in_loop.sql script provides the answer to this question by comparing a function call to a regular assignment.

function_in_loop.sql

CREATE OR REPLACE FUNCTION overhead_function(p_number  IN  NUMBER)
  RETURN NUMBER AS
  l_number  NUMBER;
BEGIN
  l_number := p_number;
  RETURN l_number;
END;
/

SET SERVEROUTPUT ON
DECLARE
  l_loops   NUMBER := 1000000;
  l_start   NUMBER;
  l_number  NUMBER;
BEGIN
  -- Time function call.
  l_start := DBMS_UTILITY.get_time;

  FOR i IN 1 .. l_loops LOOP
    l_number := overhead_function(p_number => i);
  END LOOP;

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

  -- Time no function call.
  l_start := DBMS_UTILITY.get_time;

  FOR i IN 1 .. l_loops LOOP
    l_number := i;
  END LOOP;

  DBMS_OUTPUT.put_line('No Procedure: ' ||
                       (DBMS_UTILITY.get_time - l_start));
END;
/

DROP FUNCTION overhead_function;

The results of this script show that the impact of a function call is even greater than a declaration within the loop.

SQL> @function_in_loop.sql

Function created.

Function   : 189
No Function: 20

PL/SQL procedure successfully completed.

Function dropped.

The reason for the increased impact is that there is an overhead associated with each procedure or function call, regardless of the complexity of the call specification.  This overhead can be shown more clearly by the procedure_in_loop.sql script.

procedure_in_loop.sql

CREATE OR REPLACE PROCEDURE overhead_procedure AS
BEGIN
  NULL;
END;
/

SET SERVEROUTPUT ON
DECLARE
  l_loops   NUMBER := 1000000;
  l_start   NUMBER;
BEGIN
  -- Time block.
  l_start := DBMS_UTILITY.get_time;

  FOR i IN 1 .. l_loops LOOP
    overhead_procedure;
  END LOOP;

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

  -- Time no block.
  l_start := DBMS_UTILITY.get_time;

  FOR i IN 1 .. l_loops LOOP
    NULL;
  END LOOP;

  DBMS_OUTPUT.put_line('No Procedure: ' ||
                       (DBMS_UTILITY.get_time - l_start));
END;
/

DROP PROCEDURE overhead_procedure;

The procedure used by this script accepts no parameters and does no work, so any delay is purely associated with the overhead of calling the procedure.  The results displayed below clearly show that delay.

SQL> @procedure_in_loop.sql

Procedure created.

Procedure   : 128
No Procedure: 3

PL/SQL procedure successfully completed.

Procedure dropped.

One conclusion that may be drawn from all these examples is that the use of declarations, procedures, and functions within loops is erroneous.  Further, it may be concluded that to use procedures and functions at all is wrong as they incur an unnecessary overhead.  In actual fact, the results from all of these examples must be taken in context.  In all cases results of 1,000,000 loops were compared, so the overheads experienced are actually very small.  In addition, the benefits of using procedures and functions for modular programming need no explanation.

The next section will look at the pitfalls in trying to “reinvent the wheel” in relation to Oracle built-in string functions.

Duplication of built-in string functions

Oracle provides many built in string functions for use in SQL and PL/SQL including:

ASCII, CHR, CONCAT, INITCAP, INSTR, LENGTH, LOWER, LPAD, LTRIM, NLS_INITCAP, NLS_LOWER, NLSSORT, NLS_UPPER, REGEXP_INSTR, REGEXP_LIKE, REGEXP_REPLACE, REGEXP_SUBSTR, REPLACE, RPAD, RTRIM, SOUNDEX, SUBSTR, TRANSLATE, TREAT, TRIM, UPPER

In many cases these functions are implemented using low-level code that is more efficient than normal PL/SQL.  Care should be taken not to reinvent the wheel as this wastes time and may result in poor performance.  The regular_expression.sql script shows how built-in functions can simply code and improve performance.

regular_experssion.sql

CREATE OR REPLACE FUNCTION good_credit_card (p_credit_card  IN  VARCHAR2)
  RETURN BOOLEAN AS
  l_number        NUMBER;
  ex_bad_card     EXCEPTION;
BEGIN
  l_number := TO_NUMBER(SUBSTR(p_credit_card, 1, 4));
  l_number := TO_NUMBER(SUBSTR(p_credit_card, 6, 4));
  l_number := TO_NUMBER(SUBSTR(p_credit_card, 11, 4));
  l_number := TO_NUMBER(SUBSTR(p_credit_card, 16, 4));
  IF SUBSTR(p_credit_card, 5, 1)  != ' '
  OR SUBSTR(p_credit_card, 10, 1) != ' '
  OR SUBSTR(p_credit_card, 15, 1) != ' ' THEN
    RAISE ex_bad_card;
  END IF;
  RETURN TRUE;
EXCEPTION
  WHEN OTHERS THEN
    RETURN FALSE;
END;
/

SET SERVEROUTPUT ON
DECLARE
  l_loops         NUMBER := 100000;
  l_start         NUMBER;
  l_credit_card   VARCHAR2(19)  := '1234 1234 1234 1234';
BEGIN
  -- Time manual check.
  l_start := DBMS_UTILITY.get_time;

  FOR i IN 1 .. l_loops LOOP
    IF NOT good_credit_card(p_credit_card => l_credit_card) THEN
      DBMS_OUTPUT.put_line('Bad Credit Card: ' || l_credit_card);
    END IF;
  END LOOP;

  DBMS_OUTPUT.put_line('Manual check      : ' ||
                       (DBMS_UTILITY.get_time - l_start));

  -- Time regular expression.
  l_start := DBMS_UTILITY.get_time;

  FOR i IN 1 .. l_loops LOOP
    IF NOT REGEXP_LIKE(l_credit_card, '[0-9]{4} [0-9]{4} [0-9]{4} [0-9]{4}') THEN
      DBMS_OUTPUT.put_line('Bad Credit Card: ' || l_credit_card);
    END IF;
  END LOOP;

  DBMS_OUTPUT.put_line('Regular expression: ' ||
                       (DBMS_UTILITY.get_time - l_start));
END;
/

DROP FUNCTION good_credit_card;

The script creates a function which validates the format of a credit card number.  It then compares the speed of this function to the same format check performed by a regular expression.  The results from the script are displayed below.

SQL> @regular_expression.sql

Function created.

Manual check      : 186
Regular expression: 1

PL/SQL procedure successfully completed.

Function dropped.

Not only has time been wasted writing the validation code, but system performance has been reduced in the process.

It is important to read the new features manual for each new database version to get an idea of which new built-in functions may be of use.

The next section will look at the improvements in efficiency that can be made by reducing the number of datatype conversions in PL/SQL code.

 

This is an excerpt from the bestselling book "Oracle PL/SQL Tuning: Expert Secrets for High Performance Programming" by Dr. Tim Hall, Oracle ACE of the year, 2006.

You can buy the book for only $23.95 (30%-off) when you buy directly from the publisher, and you also get instant access to the code depot of PL/SQL tuning scripts:


 

 
��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
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 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational