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 


 

 

 


 

 

 
 

Minimize Datatype Conversions


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:

Datatype conversions take processing time so attempts should be made to minimize the quantity of them in the code.  The Oracle documentation gives the following example to illustrate the effect of datatype conversions.

DECLARE
   n NUMBER;
   c CHAR(5);
BEGIN
   n := n + 15;      -- converted implicitly; slow
   n := n + 15.0;    -- not converted; fast
   c := 25;          -- converted implicitly; slow
   c := TO_CHAR(25); -- converted explicitly; still slow
   c := '25';        -- not converted; fast
END;
/

The datatype_conversions.sql script performs each of these operations multiple times in order measure their relative speeds.

datatype_conversions.sql

SET SERVEROUTPUT ON
DECLARE
  n        NUMBER;
  c        CHAR(5);
  l_loops  NUMBER := 1000000;
  l_start  NUMBER;
BEGIN
  l_start := DBMS_UTILITY.get_time;
  FOR i IN 1 .. l_loops LOOP
    n := n + 15;      -- converted implicitly; slow
  END LOOP;
  DBMS_OUTPUT.put_line('NUMBER Implicit Conversion: ' ||
                       (DBMS_UTILITY.get_time - l_start));


  l_start := DBMS_UTILITY.get_time;
  FOR i IN 1 .. l_loops LOOP
    n := n + 15.0;    -- not converted; fast
  END LOOP;
  DBMS_OUTPUT.put_line('NUMBER No Conversion      : ' ||
                       (DBMS_UTILITY.get_time - l_start));

  l_start := DBMS_UTILITY.get_time;
  FOR i IN 1 .. l_loops LOOP
    c := 25;          -- converted implicitly; slow
  END LOOP;
  DBMS_OUTPUT.put_line('CHAR Implicit Conversion  : ' ||
                       (DBMS_UTILITY.get_time - l_start));

  l_start := DBMS_UTILITY.get_time;
  FOR i IN 1 .. l_loops LOOP
    c := TO_CHAR(25); -- converted explicitly; still slow
  END LOOP;
  DBMS_OUTPUT.put_line('CHAR Explicit Conversion  : ' ||
                       (DBMS_UTILITY.get_time - l_start));

  l_start := DBMS_UTILITY.get_time;
  FOR i IN 1 .. l_loops LOOP
    c := '25';        -- not converted; fast
  END LOOP;
  DBMS_OUTPUT.put_line('CHAR No Conversion        : ' ||
                       (DBMS_UTILITY.get_time - l_start));
END;
/

The output from this script run against several versions of the Oracle database is listed below.

SQL> -- 8i
SQL> @datatype_conversions.sq
NUMBER Implicit Conversion: 311
NUMBER No Conversion      : 195
CHAR Implicit Conversion  : 920
CHAR Explicit Conversion  : 1122
CHAR No Conversion        : 324

PL/SQL procedure successfully completed.

SQL>

SQL> -- 9i
SQL> @datatype_conversions.sql
NUMBER Implicit Conversion: 57
NUMBER No Conversion      : 47
CHAR Implicit Conversion  : 94
CHAR Explicit Conversion  : 117
CHAR No Conversion        : 71

PL/SQL procedure successfully completed.

SQL>

SQL> -- 10g
SQL> @datatype_conversions.sql
NUMBER Implicit Conversion: 8
NUMBER No Conversion      : 11
CHAR Implicit Conversion  : 4
CHAR Explicit Conversion  : 22
CHAR No Conversion        : 23

PL/SQL procedure successfully completed.

These runs all took place on different servers so ignore cross comparisons, but instead look at the results within each test.  As expected, in both Oracle 8i and 9i implicit conversions are slower than not converting the datatypes, but are quicker than explicit conversions.  The Oracle 10g result is rather strange as the implicit conversions are faster than not converting the datatypes, and the explicit conversions are comparable to not converting.  This just goes to show that itís unwise to believe everything you read.

As a result of this test, a bug was raised with Oracle against Oracle 10g Release 1 on all platforms (Bug: 4159053) and at the time of writing the bug was listed as "Fixed->10.2.0".

Despite this specific bug, the tests show that datatype conversions do have an impact on performance.

The next section will disprove the commonly held belief that trigger compilation causes an overhead compare to using stored procedures.

The trigger compilation myth

Prior to Oracle 7.3, trigger code was stored as source and recompiled every time the trigger was executed.  As a result moving code from the trigger body to a packaged procedure could result in substantial performance increases.  Since Oracle 7.3, triggers, like packages, have been stored in a compiled form so there is no longer a performance gain associated with moving code out of the trigger.   Despite this, the myth of trigger compilations and performance remains intact, so the following example is presented solely to disprove it.  The trigger_compilation_setup.sql creates two sets of tables allowing us to test both methods.

trigger_compilation_setup.sql

CREATE TABLE trigger_test1 (
  id           NUMBER(10)    NOT NULL,
  description  VARCHAR2(50)  NOT NULL
);

CREATE SEQUENCE trigger_test1_seq;


CREATE TABLE trigger_test1_history (
  id               NUMBER(10)    NOT NULL,
  trigger_test_id  NUMBER(10)    NOT NULL,
  description      VARCHAR2(50)  NOT NULL,
  log_timestamp    TIMESTAMP
);

CREATE SEQUENCE trigger_test1_history_seq;

CREATE OR REPLACE TRIGGER trigger_test1_aiur
AFTER INSERT OR UPDATE
ON trigger_test1
FOR EACH ROW
DECLARE
  l_row  trigger_test1_history%ROWTYPE;
BEGIN
  SELECT trigger_test1_history_seq.NEXTVAL
  INTO   l_row.id
  FROM   dual; 

  l_row.trigger_test_id := :new.id;
  l_row.description     := :new.description;
  l_row.log_timestamp   := SYSTIMESTAMP; 

  INSERT INTO trigger_test1_history
  VALUES l_row;
END;
/

CREATE TABLE trigger_test2 (
  id           NUMBER(10)    NOT NULL,
  description  VARCHAR2(50)  NOT NULL
);

CREATE SEQUENCE trigger_test2_seq;

CREATE TABLE trigger_test2_history (
  id               NUMBER(10)    NOT NULL,
  trigger_test_id  NUMBER(10)    NOT NULL,
  description      VARCHAR2(50)  NOT NULL,
  log_timestamp    TIMESTAMP
);

CREATE SEQUENCE trigger_test2_history_seq;

CREATE OR REPLACE PROCEDURE log_trigger_test2_change (
  p_id           IN  trigger_test2.id%TYPE,
  p_description  IN  trigger_test2.description%TYPE)
AS
  l_row  trigger_test2_history%ROWTYPE;
BEGIN
  SELECT trigger_test2_history_seq.NEXTVAL
  INTO   l_row.id
  FROM   dual; 

  l_row.trigger_test_id := p_id;
  l_row.description     := p_description;
  l_row.log_timestamp   := SYSTIMESTAMP;
 
  INSERT INTO trigger_test2_history
  VALUES l_row;
END;
/

CREATE OR REPLACE TRIGGER trigger_test2_aiur
AFTER INSERT OR UPDATE
ON trigger_test2
FOR EACH ROW
CALL log_trigger_test2_change(:new.id, :new.description)
/

With the schema objects in place,  the trigger_compilation_test.sql script can be run to compare the methods.

trigger_compilation_test.sql

SET SERVEROUTPUT ON
DECLARE
  l_loops  NUMBER := 10000;
  l_start  NUMBER;
BEGIN
  EXECUTE IMMEDIATE 'TRUNCATE TABLE trigger_test1';
  EXECUTE IMMEDIATE 'TRUNCATE TABLE trigger_test1_history';
  EXECUTE IMMEDIATE 'TRUNCATE TABLE trigger_test2';
  EXECUTE IMMEDIATE 'TRUNCATE TABLE trigger_test2_history'; 

  -- Time code in trigger.

  l_start := DBMS_UTILITY.get_time;

  FOR i IN 1 .. l_loops LOOP
    INSERT INTO trigger_test1 (id, description)
    VALUES (trigger_test1_seq.NEXTVAL, 'Dummy description');
  END LOOP;

  DBMS_OUTPUT.put_line('Code in trigger: ' ||
                       (DBMS_UTILITY.get_time - l_start)); 

  -- Time code in package.
  l_start := DBMS_UTILITY.get_time;
  FOR i IN 1 .. l_loops LOOP
    INSERT INTO trigger_test2 (id, description)
    VALUES (trigger_test2_seq.NEXTVAL, 'Dummy description');
  END LOOP;

  DBMS_OUTPUT.put_line('Code in package: ' ||
                       (DBMS_UTILITY.get_time - l_start));
END;
/

The results of this script show that the packaged procedure approach is actually slower than leaving the code in the trigger.  This reduction in speed is due to the overhead associated with the procedure call.

SQL> @trigger_compilation_test.sql
Code in trigger: 700
Code in package: 736

PL/SQL procedure successfully completed.

SQL>

Even though it is marginally slower, the packaged procedure approach does have two important advantages:

  • Using the package approach breaks the dependency chain as the package body can be recompiled without invalidating the trigger.

  • The size of the code within a trigger is limited to a maximum of 32K, although the logic is questionable in using large amounts of code in triggers or procedures that are called from triggers. A better approach would be to decouple the processing.

The trigger_compilation_cleanup.sql script can be used to remove the objects created to support this test.

trigger_compilation_cleanup.sql

DROP TRIGGER trigger_test2_aiur;
DROP PROCEDURE log_trigger_test2_change;
DROP SEQUENCE trigger_test2_history_seq;
DROP TABLE trigger_test2_history;
DROP SEQUENCE trigger_test2_seq;
DROP TABLE trigger_test2;

DROP TRIGGER trigger_test1_aiur;
DROP SEQUENCE trigger_test1_history_seq;
DROP TABLE trigger_test1_history;
DROP SEQUENCE trigger_test1_seq;
DROP TABLE trigger_test1;

The next section will investigate ways of making function calls from queries more efficient.

 

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