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 


 

 

 


 

 

 
 

Bulk Binds in PL/SQL


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:

Procedural PL/SQL code and SQL code are processed by different engines, so switching between the two languages requires context switches that take time and resources.  During small operations this overhead is not noticeable, but it can cause a significant overhead for batch operations.  To counter this issue Oracle provide bulk bind operations which allow PL/SQL collections to be populated and processed in single bind operations.  The whole of chapter three is dedicated to the use of arrays and bulk bind operations.

The performance improvements associated with accessing rows directly using rowids rather than primary key searches will be covered next.

Using rowids when updating

Rowids are Oracle's internal row-level identifiers and are used during index creation to provide the link between index keys and the rows they point to.  Each rowid represents the physical address of a row within the database and as such is the quickest way to access the row.  In processes where data is rapidly selected and updated the use of rowids can improve performance.  The rowid_test.sql script shown below gives an example of this.

rowid_test.sql
CREATE TABLE rowid_test AS
SELECT *
FROM   all_objects;

ALTER TABLE rowid_test ADD (
  CONSTRAINT rowid_test_pk
  PRIMARY KEY (object_id)
);

EXEC DBMS_STATS.gather_table_stats(USER, 'rowid_test', cascade => TRUE);


SET SERVEROUTPUT ON
DECLARE
  TYPE t_id_tab IS TABLE OF rowid_test.object_id%TYPE; 

  l_id_tab     t_id_tab;
  l_rowid      ROWID;
  l_start      NUMBER;
BEGIN
  SELECT object_id
  BULK COLLECT INTO l_id_tab
  FROM   rowid_test; 

  -- Time the original method.
  l_start := DBMS_UTILITY.get_time;

  FOR i IN l_id_tab.first .. l_id_tab.last LOOP
    SELECT rowid
    INTO   l_rowid
    FROM   rowid_test
    WHERE  object_id = l_id_tab(i)
    FOR UPDATE;   

    UPDATE rowid_test
    SET    object_name = object_name
    WHERE  object_id = l_id_tab(i);
  END LOOP; 

  DBMS_OUTPUT.put_line('Primary Key (' || l_id_tab.count || ' rows): ' ||
                       (DBMS_UTILITY.get_time - l_start)); 

  -- Time the original method.
  l_start := DBMS_UTILITY.get_time;

  FOR i IN l_id_tab.first .. l_id_tab.last LOOP
    SELECT rowid
    INTO   l_rowid
    FROM   rowid_test
    WHERE  object_id = l_id_tab(i)
    FOR UPDATE;

    UPDATE rowid_test
    SET    object_name = object_name
    WHERE  rowid = l_rowid;
  END LOOP; 

  DBMS_OUTPUT.put_line('Rowid (' || l_id_tab.count || ' rows)      : ' ||
                       (DBMS_UTILITY.get_time - l_start));
END;
/

DROP TABLE rowid_test;

The script creates a test table as a copy of the all_objects view and then loops through the table twice updating each row at a time.  During the first pass the rows are updated using the primary key value, whilst the rowid is used in the second pass.  The results of the script are displayed below.

SQL> @rowid_test.sql

Table created.

Table altered.

PL/SQL procedure successfully completed.

Primary Key (41189 rows): 1562
Rowid (41189 rows)      : 1366

PL/SQL procedure successfully completed.

Table dropped.

The output from the script demonstrates that accessing rows via the rowid is quicker than using the primary key value.

There are some caveats related to using rowids:

  • Rowids represent physical row locations so they are subject to change.  As a result they should only be used when there is no risk of row movement, like during a SELECT … FOR UPDATE statement.

  • Rowids can be stored, but in my opinion this is a disaster waiting to happen.  If the row is moved the rowid becomes invalid and the link is broken.

  • There is no point adding a database roundtrip to get a rowid, but if you have to retrieve the data, manipulate it and then update it, the rowid is the fasted method.

The next section will show how the order of logical expressions and branching structures can affect the performance of code.

Short-circuit evaluations and ordering logic

When conditional logic contains several expressions separated by the AND or OR operators it is sometimes possible to determine the final result without evaluating all the expressions.  If the first expression in an OR evaluates to TRUE, the result must be TRUE so subsequent expressions do not need to be evaluated.

TRUE OR TRUE  = TRUE
TRUE OR FALSE = TRUE

If the first expression in an AND is FALSE, the result must be FALSE so the subsequent expressions do not need to be evaluated.

FALSE AND TRUE  = FALSE
FALSE AND FALSE = FALSE

PL/SQL is able to take advantage of this behavior and perform short-circuit evaluations allowing it to stop evaluating logical expressions once the final result can be determined.  In these circumstances placing the quickest or least expensive tests first result in quicker performance, as we will now see.

The slow_function.sql script creates an artificially slow function by using the dbms_lock.sleep procedure.  For this to work successfully the user must be granted execute permission on the dbms_lock package.

slow_function.sql

CREATE OR REPLACE FUNCTION slow_function (p_number  IN  NUMBER)
  RETURN BOOLEAN AS
BEGIN
  -- Mimic a slow function.
  DBMS_LOCK.sleep(1);
  RETURN TRUE;
END;
/
SHOW ERRORS

The slow_function_test.sql script uses this function to test the short-circuit behavior by comparing AND and OR statements in different orders.

slow_function_test.sql

SET SERVEROUTPUT ON
DECLARE
  l_loops  NUMBER := 10;
  l_start  NUMBER;
BEGIN
  -- Time normal AND.
  l_start := DBMS_UTILITY.get_time;

  FOR i IN 1 .. l_loops LOOP
    IF slow_function(i) AND FALSE THEN
      -- Do nothing.
      NULL;
    END IF;
  END LOOP;

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


  -- Time short-circuit AND.
  l_start := DBMS_UTILITY.get_time;


  FOR i IN 1 .. l_loops LOOP
    IF FALSE AND slow_function(i) THEN
      -- Do nothing.
      NULL;
    END IF;
  END LOOP;

  DBMS_OUTPUT.put_line('Short circuit AND: ' ||
                       (DBMS_UTILITY.get_time - l_start));

  -- Time normal OR.
  l_start := DBMS_UTILITY.get_time;

  FOR i IN 1 .. l_loops LOOP
    IF slow_function(i) OR TRUE THEN
      -- Do nothing.
      NULL;
    END IF;
  END LOOP;

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

  -- Time short-circuit OR.
  l_start := DBMS_UTILITY.get_time;

  FOR i IN 1 .. l_loops LOOP
    IF TRUE OR slow_function(i) THEN
      -- Do nothing.
      NULL;
    END IF;
  END LOOP;

  DBMS_OUTPUT.put_line('Short circuit OR : ' ||
                       (DBMS_UTILITY.get_time - l_start));
END;
/

The output from the script shows that the short-circuit evaluations are significantly quicker because the PL/SQL engine doesn’t have to call the slow function.

SQL> @slow_function_test.sql

Normal AND       : 1025
Short circuit AND: 0
Normal OR        : 1025
Short circuit OR : 0

PL/SQL procedure successfully completed.

It makes sense to order long ELSIF and CASE structures such that the most frequently chosen branch is the first option in the list to be evaluated, as shown by the branch_order.sql script.

branch_order.sql

SET SERVEROUTPUT ON
DECLARE
  l_loops  NUMBER := 1000000;
  l_value  VARCHAR2(1) := 'A';
  l_start  NUMBER;
BEGIN
  -- Time ELSIF first.
  l_start := DBMS_UTILITY.get_time;

  FOR i IN 1 .. l_loops LOOP
    IF l_value = 'A' THEN
      NULL;
    ELSIF l_value = 'B' THEN
      NULL;
    ELSIF l_value = 'C' THEN
      NULL;
    ELSIF l_value = 'D' THEN
      NULL;
    ELSIF l_value = 'E' THEN
      NULL;
    END IF;
  END LOOP;

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

  -- Time ELSIF last.
  l_start := DBMS_UTILITY.get_time;

  FOR i IN 1 .. l_loops LOOP
    IF l_value = 'B' THEN
      NULL;
    ELSIF l_value = 'C' THEN
      NULL;
    ELSIF l_value = 'D' THEN
      NULL;
    ELSIF l_value = 'E' THEN
      NULL;
    ELSIF l_value = 'A' THEN
      NULL;
    END IF;
  END LOOP;

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

  -- Time CASE first.
  l_start := DBMS_UTILITY.get_time;

  FOR i IN 1 .. l_loops LOOP
    CASE l_value
      WHEN 'A' THEN
        NULL;
      WHEN 'B' THEN
        NULL;
      WHEN 'C' THEN
        NULL;
      WHEN 'D' THEN
        NULL;
      WHEN 'E' THEN
        NULL;
    END CASE;
  END LOOP;

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

  -- Time CASE first.
  l_start := DBMS_UTILITY.get_time;

  FOR i IN 1 .. l_loops LOOP
    CASE l_value
      WHEN 'B' THEN
        NULL;
      WHEN 'C' THEN
        NULL;
      WHEN 'D' THEN
        NULL;
      WHEN 'E' THEN
        NULL;
      WHEN 'A' THEN
        NULL;
    END CASE;
  END LOOP;

  DBMS_OUTPUT.put_line('CASE last  : ' ||
                       (DBMS_UTILITY.get_time - l_start));
END;
/

The results of this script clearly show that the order of the choices in both the ELSIF and CASE statements can produce a measurable difference in speed.

SQL> @branch_order.sql

ELSIF first: 22
ELSIF last : 51
CASE first : 22
CASE last  : 79

PL/SQL procedure successfully completed.

With this in mind, all code should be revised to place quickly evaluated expressions towards the start of Boolean expressions and to order branching code such that the most frequent taken branches are evaluated first.

The next section will compare the performance of implicit and explicit cursors.

 

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