Java Stored Procedures – Performance Test

Are Java Stored Procedures really faster than standalone Java Programs?

Let’s make the test!

Our test program consists of the following simple steps:

  1. Get a database connection
  2. Drop and create the test table “BASIC_LOB_TABLE”
  3. Insert two rows into “BASIC_LOB_TABLE”
  4. Select and process all the rows from “BASIC_LOB_TABLE”
  5. Read the BLOB and CLOB fields and get their size
  6. Truncate the BLOB and CLOB fields and get their size

1. Source code – equal for standalone and stored procedure! :

/* To create a standalone program, remove the "CREATE OR REPLACE..." line !!! */
CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "TrimLob" AS
import java.sql.Blob;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
 
public class TrimLob
{
    public static void main (String args [])
    throws SQLException
    {
        Connection conn;
 
        /* Are we in the database or outside? */
        if (System.getProperty("oracle.jserver.version") != null)
        {
            /* in the database, use the default connection */
            conn = DriverManager.getConnection("jdbc:default:connection:");
        }
        else
        {
            /* outside, connect to the database */
            DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
            conn = DriverManager.getConnection("jdbc:oracle:thin:@mydb:1521:MYSID", "MY_USER","mypassword");
        }
 
        long t0,t1;
 
        /* Turn auto commit off (Required when using SELECT FOR UPDATE) */
        conn.setAutoCommit(false);
 
        // Create a Statement
        Statement stmt = conn.createStatement ();
 
        // Drop the test table to ensure equal conditions:
        try {
            stmt.execute("drop table basic_lob_table");
        }
        catch (SQLException pE) {
            /* Ignore error "ORA-00942: table or view does not exist", throw all others: */
            if (pE.getErrorCode() != 942) {
                throw(pE);
            }
        }
 
        //Create the test table:
        stmt.execute("create table basic_lob_table (x varchar2 (30), b blob, c clob)");
 
 
        t0 = System.currentTimeMillis();
 
        // Insert two rows in the test table:
        stmt.execute ("insert into basic_lob_table values ('first', " +
            "'010101010101010101010101010101', " +
            „'one.two.three.four.five.six.seven')");
        stmt.execute ("insert into basic_lob_table values ('second', " +
            "'0202020202020202020202020202020202020202', " +
            "'two.three.four.five.six.seven.eight.nine.ten')");
 
        // Retreive LOBs and update contents (trim):
        ResultSet rset = stmt.executeQuery("select * from basic_lob_table for update");
        while (rset.next ())
        {
            // Get the lobs
            Blob blob = (Blob) rset.getObject (2);
            Clob clob = (Clob) rset.getObject (3);
 
            // Truncate the lobs
            blob.truncate (6);
            clob.truncate (6);
        }
        rset.close ();
        conn.commit();
        stmt.close ();
        conn.close ();
 
        // Print runtime:
        t1 = System.currentTimeMillis();
        System.out.println ((int)(t1-t0) + " ms");
    }
}
/

2. PL/SQL Wrapper:

CREATE OR REPLACE PROCEDURE TrimLobSp AS
language JAVA NAME 'TrimLob.main(java.lang.String[])';
/

3. Test:

SET serveroutput ON
CALL dbms_java.set_output(50000);
CALL TrimLobSp();

Runtime comparison – DML only (steps 3 to 6)

Run Standalone JServer faster

1

80 ms

10 ms

8 times

2

94 ms

8 ms

12 times

3

93 ms

8 ms

12 times

4

93 ms

8 ms

12 times

5

93 ms

7 ms

13 times

We did not measure the time to connect to the database, to drop and create the table or anything else. Only the pure DML times (insert, select, update) are measured.

And here is the answer: on our systems Java Stored Procedures are 12 times faster!

Now it’s your turn to measure your systems. Don’t be surprised.

Advertisements

I will speak at the “DOAG 2017 Conference + Exhibition” (“DOAG 2017 Konferenz + Ausstellung”)

I will be on the “DOAG 2017 Conference + Exhibition” in Nürnberg (Nuremberg), Germany from November 21th to 24th.

I will speak at November 21th, at 8:30 am, room “Stockholm”:
Java-Programme in der Oracle-Datenbank? Na klar!
(English: “Java-Programs in the Oracle-Datenbank? Sure!”)

The DOAG is the largest German Oracle user group with over 6000 members.

 

 

PL/SQL: Sleep without using DBMS_LOCK

If you want to wait in your PL/SQL program (“sleep”) you may want to use the procedure “SLEEP” from the package “DBMS_LOCK“. While this function does exactly what you want, your DBA may not grant you access to this package, because it contains some other mighty and dangerous procedures.

But there is help! Here are some valid alternatives:

Option 1: APEX_UTIL.sleep

If APEX is installed you can use the procedure “PAUSE” from the publicly available package APEX_UTIL.

Example – “Wait 5 seconds”:

SET SERVEROUTPUT ON ;
BEGIN
    DBMS_OUTPUT.PUT_LINE('Start ' || to_char(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));
    APEX_UTIL.PAUSE(5);
    DBMS_OUTPUT.PUT_LINE('End   ' || to_char(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));
END;
/

Option 2: java.lang.Thread.sleep

An other option is the use of the method “sleep” from the Java class “Thread”, which you can easily use through providing a simple PL/SQL wrapper procedure:

CREATE OR REPLACE PROCEDURE SLEEP (P_MILLI_SECONDS IN NUMBER) 
AS LANGUAGE JAVA NAME 'java.lang.Thread.sleep(long)';

Note: Please remember, that “Thread.sleep” uses milli seconds!

Example – “Wait 5 seconds”:

SET SERVEROUTPUT ON ;
BEGIN
    DBMS_OUTPUT.PUT_LINE('Start ' || to_char(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));
    SLEEP(5 * 1000);
    DBMS_OUTPUT.PUT_LINE('End   ' || to_char(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));
END;
/

 

I will speak at the “DOAG 2015 Conference + Exhibition” (“DOAG 2015 Konferenz + Ausstellung”)

I will be on the “DOAG 2015 Conference + Exhibition” in Nürnberg (Nuremberg), Germany from November 17th to 19th.

I will speak at November 18th, at 10 am, room “Konferenzraum EG”:
Was Sie bei modernen Datenbank-Systemen anders machen müssen
(English: “What you need to do differently in modern database systems”)

The DOAG is the largest German Oracle user group with over 6000 members.

DOAG 2015 Konferenz + Ausstellung

DOAG 2015 Conference + Exhibition

SQL-Developer: How to prevent the “Java Heap Space” error

When you are working with a lot of worksheets and reports or try to open large SQL files, you may run into “Java Heap Space” errors.

To fix this problem you need to increase the Xmx (maximum heap size).

Here is how:

  1. Close all open SQL-Developer instances.
  2. Find the “sqldeveloper.conf” file
    1. Windows:
      1. goto the directory where the SQL-Developer program is installed.
      2. goto the subdirectory “sqldeveloper\bin”
    2. Mac OS X:
      1. goto your applications folder (“/Applications”)
      2. open the SQLDeveloper-App via context menu (“right mouse click”)
      3. goto the subdirectory “Contents/Resources/sqldeveloper/sqldeveloper/bin”
  3. Open the file “sqldeveloper.conf” with the text editor of your choise.
  4. Add a new line with the following text to the file:
    AddVMOption -Xmx1024M
  5. Save the file.

Oracle Advanced Queuing Series: 1. Create Message Type, AQ Table and Queue

Welcome to the first part of the series “Oracle Advanced Queuing”.

We start with the basic elements of Oracle Advanced Queuing:
the message type, the queue table and finally the queue.

  1. Create a Type for your messages:
    CREATE TYPE my_queue_typ  AS OBJECT(message_text VARCHAR2(4000));
  2. Create an AQ Table based on your type:
    EXEC DBMS_AQADM.CREATE_QUEUE_TABLE('my_queue_table', 'my_queue_type');
  3. Create a Message Queue with your table:
    EXEC DBMS_AQADM.CREATE_QUEUE('my_queue', 'my_queue_table');
  4. Start the queue:
    EXEC DBMS_AQADM.START_QUEUE('my_queue');

Missing statements in SQL Monitoring

Since Oracle 11g, the database offers real-time SQL Monitoring, a great feature for insights of currently and previously running queries.
In most cases this works great without effort, but sometimes your statements won’t show.
This can be caused by statements with very large execution plans or statements with short execution times.

Monitor statements with very large execution plans

To include statements with very large execution plans in SQL Monitoring, we need to increase two thresholds. The following values served me well, but your millage may vary:

ALTER SESSION SET "_SQLMON_MAX_PLAN"=4020;
ALTER SESSION SET "_SQLMON_MAX_PLANLINES"=4000;

Remember to execute these commands in the same session and before the statements you want to monitor!

Monitor short running statements

To include short running statements into SQL Monitoring, we can use the MONITOR hint.

example:
SELECT /*+ MONITOR*/ * FROM DUAL;

Remeber that this hint is only valid when the parameter CONTROL_MANAGEMENT_PACK_ACCESS is set to DIAGNOSTIC+TUNING.

Execlude statements from SQL Monitoring

To exclude statements from SQL Monitoring, we can use the NO_MONITOR hint.

example:
SELECT /*+ NO_MONITOR*/ count(*) FROM DBA_OBJECTS CROSS JOIN DBA_OBJECTS;

Access SQL Monitoring

The SQL Monitoring informations can be accessed through

  • the tables V$SQL_MONITOR and V$SQL_PLAN_MONITOR
    (RAC:  GV$SQL_MONITOR and GV$SQL_PLAN_MONITOR)
  • a PL/SQL procedure:
    variable l_rep CLOB;
    exec :l_rep := DBMS_SQLTUNE.REPORT_SQL_MONITOR();
    print :l_rep
  • or with Oracle Enterprise Manager