Oracle … as usual

Oracle by Laurent Leturgez

Category Archives: Development

Executing a SQL Statement with bind variables on Oracle with Python

In the previous blog post, I detailed how to execute sql statement on an Oracle database with Python. (See here for more details: https://laurent-leturgez.com/2018/08/30/executing-a-sql-statement-on-oracle-with-python/)

As you have seen, I used two kind of data structure to get the results: numpy array, and pandas array and for each, it’s possible to get the results in these structures and filter it directly in the Python script. The problem is that it will cause some issues regarding the dataset size in memory, network roundtrips etc.

So, as usual it’s always better to use Oracle capacity to filter data, joins them (in case of tables that resides in the Oracle database). And if you want to reduce parsing inside Oracle, you will probably want to use bind variables inside your SQL Statements … so how do we do this with python?

That will mostly depend on your result set data structure (numpy or pandas).

Just before we start the details, I consider that you are now able to get a Connection from an Oracle Database using cx_Oracle package (if not … see: https://laurent-leturgez.com/2018/07/31/connecting-python-to-an-oracle-database/).

Using numpy

For this purpose, we will have to declare an associative array of bind variables.

For example, the code below will declare, in an associative array “p” the bind variable named “param” and its value “log_archive_dest_1”  :


p = {'param': "log_archive_dest_1"}

Of course, in your statement declaration, you have to name your bind variable the same as declared in your associative array (in our case, param). If you have many bind variables in the same statement, let’s say p1 and p2, you will have to declare your associative array as follows:

p = {'p1': "log_archive_dest_1", 'p2': "log_archive_dest_2"}

Once declared, you just have to execute the statement and associate the array as parameters:


from __future__ import print_function

import cx_Oracle
import os
import numpy as np
import pandas as pd


oh="D:/tools/Oracle/instantclient_12_2_x8664"
os.environ["ORACLE_HOME"]=oh
os.environ["PATH"]=oh+os.pathsep+os.environ["PATH"]
os.environ["NLS_LANG"]="AMERICAN_AMERICA.AL32UTF8"

.../...

def executeStmt(conn, stmt, parameters):
    if conn is not None and isinstance (conn, cx_Oracle.Connection):
        cur = conn.cursor()
        if parameters is None:
            cur.execute (stmt)
        else:
            cur.execute(stmt,parameters)
    return cur

.../...

if __name__ == '__main__':
    c=cx_Oracle.Connection
    stmt="select name,value from v$parameter where name = :param"
    try:
        c=connectToOracle("192.168.99.2:1521/orcl","sys","oracle",mode=cx_Oracle.SYSDBA)
        p = {'param': "log_archive_dest_1"}
        
        print (">>>>>>    NUMPY STYLE")
        curs=executeStmt(c,stmt,p)
        if curs.rowcount!=0:
            curs.scroll(value=0)
        r = curs.fetchall()
        print("type(r) = ",type(r))
        n = np.array (r)
        print("type(n) =",type(n))
        print("n=", n)

        curs.close()
    except cx_Oracle.DatabaseError as ex:
        err, =ex.args
        print("Error code    = ",err.code)
        print("Error Message = ",err.message)
        os._exit(1)
    c.close()

Please notice that I converted the list return by fetchall call to a numpy array. If you prefer to use list, you can work directly on it without converting it to a numpy array.

This piece of code will produce the results below:

>>>>>>    NUMPY STYLE
type(r) =  <type 'list'>
type(n) = <type 'numpy.ndarray'>
n= [['log_archive_dest_1' None]]

Using Pandas

Using pandas dataframe is a bit different and, as mentioned in the previous post, I prefer to use pandas dataframes when dealing with a rdbms (like Oracle). Why? because of its capabilities (filtering, joining etc.)

Ok, but now, how do we deal with SQL Statement and bind variable with python and pandas dataframes ?

The principle is nearly the same as sql statement without bind variables.

You have to call read_sql function, and pass the same associative array as parameters … and that’s it 🙂 … See below


from __future__ import print_function

import cx_Oracle
import os
import numpy as np
import pandas as pd


oh="D:/tools/Oracle/instantclient_12_2_x8664"
os.environ["ORACLE_HOME"]=oh
os.environ["PATH"]=oh+os.pathsep+os.environ["PATH"]
os.environ["NLS_LANG"]="AMERICAN_AMERICA.AL32UTF8"

.../...

if __name__ == '__main__':
    c=cx_Oracle.Connection
    stmt="select name,value from v$parameter where name = :param"
    try:
        c=connectToOracle("192.168.99.2:1521/orcl","sys","oracle",mode=cx_Oracle.SYSDBA)
        p = {'param': "log_archive_dest_1"}

        print(">>>>>>    PANDAS STYLE")
        dataframe=pd.read_sql(stmt,con=c,params=p)
        print("type(dataframe)",type(dataframe))
        print(dataframe)

        # Panda conversion to numpy is always possible.
        # n=dataframe.values
        curs.close()
    except cx_Oracle.DatabaseError as ex:
        err, =ex.args
        print("Error code    = ",err.code)
        print("Error Message = ",err.message)
        os._exit(1)
    c.close()

That will produce the same kind of results:

>>>>>>    PANDAS STYLE
type(dataframe) <class 'pandas.core.frame.DataFrame'>
                 NAME VALUE
0  log_archive_dest_1  None

On the database side

On the database side, you will find this kind of statement on the shared pool:

SQL> select sql_id,sql_fulltext,executions
  2  from v$sql
  3  where sql_text like 'select name,value from v%';

SQL_ID        SQL_FULLTEXT                                                                     EXECUTIONS
------------- -------------------------------------------------------------------------------- ----------
4zqq6y8cdbqmu select name,value from v$parameter where name = :param                                   17

You can see the bind variable name can be found in the statement and not an anonymous :1 or :B1 😉

In a next blogpost, I will show how to call a PLSQL stored procedure / function / package from Python.

 

Source code is available on my gitlab: https://gitlab.com/lolo115/python/tree/master/oracle

That’s it for today 😉

Advertisements

Executing a SQL Statement on Oracle with Python

In the first blogpost of this series dedicated to Oracle and Python, I described how to connect a Python script to an Oracle Database (see. connecting python to an oracle database). In this second post, I will describe how to query an Oracle database and gets some results by using most popular Python libraries for this stuff: numpy and pandas.

So, first thing to do, you have to install pandas and numpy libraries in your work environnement (virtualenv, miniconda etc.). And of course, cx_Oracle has to be installed too 😉

In my case, libraries are deployed in my virtualenv hosted in PyCharm project:

In the program I write, in order to load packages and configure basics, I will use the header below:

from __future__ import print_function

import cx_Oracle
import os
import numpy as np
import pandas as pd


oh="D:/tools/Oracle/instantclient_12_2_x8664"
os.environ["ORACLE_HOME"]=oh
os.environ["PATH"]=oh+os.pathsep+os.environ["PATH"]
os.environ["NLS_LANG"]="AMERICAN_AMERICA.AL32UTF8"

First step, write a function to connect the database

So, first thing to write is a function that will return a cx_Oracle connection object (or reuse the function used in the first blogpost 😉 ).

This function will user parameters like username, password, datasource name (or url) and optionaly the mode used (SYSDBA for example).

def connectToOracle(url, username, password, mode=None):
    if mode is not None:
        connection = cx_Oracle.Connection (user=username, password=password, dsn=url, mode=mode)
    else:
        connection = cx_Oracle.Connection (user=username, password=password, dsn=url)
    return connection

 

Get a cursor and execute a statement

Once we have a cx_Oracle connection object, we can create a cursor by executing the cursor() function and then execute a statement.

To do this, I wrote a function with two parameters: the connection object and the statement text, and this returns the cursor that has been executed in the function:


def executeStmt(conn, stmt):
    if conn is not None and isinstance (conn, cx_Oracle.Connection):
        cur = conn.cursor()
        cur.execute(stmt)
    return cur

Once the cursor will be executed, we will be able to fetch one, many or all rows, and to describe it to get more metadata.

 

Describe executed cursor to get some metadata

By describing the cursor, we are able to get some information of its structure (column name and types, number precision and scale, nullable column etc.).

To do that, we use a read only attribute of the cursor (named description). In this attribute, there are 7 items that can be read.

These items are the ones below:

  • Item 1: Column names
  • Item 2: Column types (types are cx_Oracle data types)
  • Item 3: Column displayed sizes
  • Item 4: Column internal sizes
  • Item 5: Column precision
  • Item 6: Column scale
  • Item 7: Nullable column

This function code is the one below:


def describeCursor(cur):
    if cur is not None and isinstance (cur, cx_Oracle.Cursor):
        colnames = [row[0] for row in cur.description]
        coltypes = [row[1] for row in cur.description]
        coldisplay_sz = [row[2] for row in cur.description]
        colinternal_sz = [row[3] for row in cur.description]
        colprecision = [row[4] for row in cur.description]
        colscale = [row[5] for row in cur.description]
        colnullok = [row[6] for row in cur.description]
    print("Column names     : ",colnames)
    print("Column types     : ",coltypes)
    print("Display Size     : ",coldisplay_sz)
    print("Internal Size    : ",colinternal_sz)
    print("Column precision : ",colprecision)
    print("Column Scale     : ",colscale)
    print("Null OK?         : ",colnullok)

It can be used like this:


if __name__ == '__main__':
    c=cx_Oracle.Connection
    stmt="select name,value from v$parameter where name like 'log_archive%' and name not like 'log_archive_dest%' "
    try:
        c=connectToOracle("192.168.99.2:1521/orcl","sys","oracle",mode=cx_Oracle.SYSDBA)
        curs=executeStmt(c,stmt)
        describeCursor(curs)
...
        curs.close()
    except cx_Oracle.DatabaseError as ex:
        err, =ex.args
        print("Error code    = ",err.code)
        print("Error Message = ",err.message)
        os._exit(1)
    c.close()

And will produce this kind of result:


Column names     :  ['NAME', 'VALUE']
Column types     :  [<type 'cx_Oracle.STRING'>, <type 'cx_Oracle.STRING'>]
Display Size     :  [80, 4000]
Internal Size    :  [80, 4000]
Column precision :  [None, None]
Column Scale     :  [None, None]
Null OK?         :  [1, 1]

 

Get results of the cursor execution

Once the cursor executed, you can fetch your cursor row by row, you can fetch a set of n rows, or fetch all the rows.

Fetching the cursor

To do that, you have to use these function (from cursor)

  • fetchone() will return a list of one tuple
  • fetchmany(n) will return  a list of n tuples
  • fetchall() will return a list of n tuples (n is equal to the number of rows in the cursor)

for example, this piece of code:





<pre>if __name__ == '__main__':
    c=cx_Oracle.Connection
    stmt="select name,value from v$parameter where name like 'log_archive%' and name not like 'log_archive_dest%' "
    try:
        c=connectToOracle("192.168.99.2:1521/orcl","sys","oracle",mode=cx_Oracle.SYSDBA)
        curs=executeStmt(c,stmt)
        r=curs.fetchmany(2)
        print("R=",r)
        curs.close()
    except cx_Oracle.DatabaseError as ex:
        err, =ex.args
        print("Error code    = ",err.code)
        print("Error Message = ",err.message)
        os._exit(1)
    c.close()</pre>

will produce this result:

R= [('log_archive_start', 'FALSE'), ('log_archive_duplex_dest', None)]

 

Displaying the cursor results

Once the cursor has been executed and the rows fetched, you can use numpy or pandas to work on the data.

Numpy

Numpy is a library which is primarly used for scientific computing. So, it’s not the best if your resultset is made of string datatypes.

A better way to proceed is:

  1. fetch your cursor
  2. convert the result into a numpy array. And decribe the types of your array.

For example:


if __name__ == '__main__':
    c=cx_Oracle.Connection
    stmt="select SIZE_FOR_ESTIMATE,SIZE_FACTOR,ESTD_PHYSICAL_READS from v$db_cache_advice"
    try:
        c=connectToOracle("192.168.99.2:1521/orcl","sys","oracle",mode=cx_Oracle.SYSDBA)
        curs=executeStmt(c,stmt)
        # Fetch all rows from the cursor
        r=curs.fetchall()
        # convert the r variable into a numpy array and describe it
        n=np.array(r,dtype=[('SIZE_FOR_ESTIMATE','float64'),('SIZE_FACTOR','float64'),('ESTD_PHYSICAL_READS','float64')])
        print("ndim=",n.ndim)
        print("n=",n)
        curs.close()
    except cx_Oracle.DatabaseError as ex:
        err, =ex.args
        print("Error code    = ",err.code)
        print("Error Message = ",err.message)
        os._exit(1)
    c.close()

This piece of code will produce the result above:

n= [(  96., 0.0896, 9690.) ( 192., 0.1791, 9690.) ( 288., 0.2687, 9690.)
 ( 384., 0.3582, 9690.) ( 480., 0.4478, 9690.) ( 576., 0.5373, 9690.)
 ( 672., 0.6269, 9690.) ( 768., 0.7164, 9690.) ( 864., 0.806 , 9690.)
 ( 960., 0.8955, 9690.) (1056., 0.9851, 9690.) (1072., 1.    , 9690.)
 (1152., 1.0746, 9690.) (1248., 1.1642, 9690.) (1344., 1.2537, 9690.)
 (1440., 1.3433, 9690.) (1536., 1.4328, 9690.) (1632., 1.5224, 9690.)
 (1728., 1.6119, 9690.) (1824., 1.7015, 9690.) (1920., 1.791 , 9690.)]

Note: If you want to get more information about type conversion into numpy data type see: 

 

A better way to proceed, specially if you want to execute some joins between two datasets is to use Pandas.

Pandas

Pandas is a python library which provide easy to use data structures. It uses a central data structure named dataframe with which you can execute filters, joins etc.

When you decide to create pandas dataframes from data coming from Oracle (or another database, CSV file, JSON etc.), you have to use pandas function written specifically for this purpose. And in the case of an Oracle database, you have a function read_sql() that can be executed with a sql statement given in parameter. It will create a pandas dataframe and then you will be able to proceed this structure in your python script.

One advantage of pandas dataframes is in its data types because they are converted directly depending on the types returned by the cursor, and you don’y need to create a cursor, execute it and then fetch the rows … everything is automatic.

But let’s have a look to a piece of code (I will publish the complete script to show we don’t need explicit cursors etc.):


from __future__ import print_function

import cx_Oracle
import os
import pandas as pd


oh="D:/tools/Oracle/instantclient_12_2_x8664"
os.environ["ORACLE_HOME"]=oh
os.environ["PATH"]=oh+os.pathsep+os.environ["PATH"]
os.environ["NLS_LANG"]="AMERICAN_AMERICA.AL32UTF8"


def connectToOracle(url, username, password, mode=None):
    if mode is not None:
        connection = cx_Oracle.Connection (user=username, password=password, dsn=url, mode=mode)
    else:
        connection = cx_Oracle.Connection (user=username, password=password, dsn=url)
    return connection

# main
if __name__ == '__main__':
    c=cx_Oracle.Connection

    stmt="select SIZE_FOR_ESTIMATE,SIZE_FACTOR,ESTD_PHYSICAL_READS from v$db_cache_advice"
    try:
        c=connectToOracle("192.168.99.2:1521/orcl","sys","oracle",mode=cx_Oracle.SYSDBA)
        dataframe=pd.read_sql(stmt,con=c)
        print("------\nDF : \n ",dataframe)
        print("------\nDF Data types : \n",dataframe.dtypes)
        print("------\nDF Filtered   : \n",dataframe[dataframe['SIZE_FOR_ESTIMATE']>1440])
    except cx_Oracle.DatabaseError as ex:
        err, =ex.args
        print("Error code    = ",err.code)
        print("Error Message = ",err.message)
        os._exit(1)
    c.close()

We just have to instantiate a cx_Oracle connection, and then use pandas read_sql function to get a pandas dataframe and then process it.

In the previous basic example, I printed it, its datatypes and then run a filter which produces this kind of results:

------
DF : 
      SIZE_FOR_ESTIMATE  SIZE_FACTOR  ESTD_PHYSICAL_READS
0                  96       0.0896                 9766
1                 192       0.1791                 9766
2                 288       0.2687                 9766
3                 384       0.3582                 9766
4                 480       0.4478                 9766
5                 576       0.5373                 9766
6                 672       0.6269                 9766
7                 768       0.7164                 9766
8                 864       0.8060                 9766
9                 960       0.8955                 9766
10               1056       0.9851                 9766
11               1072       1.0000                 9766
12               1152       1.0746                 9766
13               1248       1.1642                 9766
14               1344       1.2537                 9766
15               1440       1.3433                 9766
16               1536       1.4328                 9766
17               1632       1.5224                 9766
18               1728       1.6119                 9766
19               1824       1.7015                 9766
20               1920       1.7910                 9766
------
DF Data types : 
 SIZE_FOR_ESTIMATE        int64
SIZE_FACTOR            float64
ESTD_PHYSICAL_READS      int64
dtype: object
------
DF Filtered   : 
     SIZE_FOR_ESTIMATE  SIZE_FACTOR  ESTD_PHYSICAL_READS
16               1536       1.4328                 9766
17               1632       1.5224                 9766
18               1728       1.6119                 9766
19               1824       1.7015                 9766
20               1920       1.7910                 9766

More information on pandas dataframes and their capacities is available at the following url: https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.html

In a next blogpost (probably shorter than this one), I will explain on how to use bind variables in statement.

Source code is fully available on my gitlab: https://gitlab.com/lolo115/python/tree/master/oracle

 

That’s it for today !!! 😉

 

 

Connecting Python to an Oracle Database

I decided to write a new series of blogposts related to python programming language and its interactions with Oracle.

Python is a powerful scripting language with a lot of packages that can be installed for various stuff (scientific computing, dataviz, machine learning, deep learning etc.).

In my case, I used python for many things and specially for running my sql tuning scripts on an Oracle database, and then present the results with beautiful graphs.

This first blogpost will present how I use python on my laptop and how to install the required package to connect an Oracle Database.

 

1. Work Environment

I used python on my MS Windows laptop, and I used two versions of python depending on what I have to do or packages I will have to use.

So I installed two distributions of python :

You are free to use the version of Python you want to use and/or bundled with the package installer of your choice.

Next, I use a very well known python IDE name PyCharm (https://www.jetbrains.com/pycharm/download). PyCharm is released with 2 versions : a professional one (not free) and a free distribution (named community edition). The community edition is enough for my needs.

In PyCharm, you can run what is called a virtual-environment which is a kind of clone of your python installation where you will be able to deploy the package you want.

That can be assimilated to a “project” because in PyCharm, when you create a new project, you have to configure an interpreter, and pyCharm will automatically create a new virtual environment (default) based on the interpreter you chose :

Once the project has been created, we have to install the package required to connect to an Oracle Database : cx_Oracle.

In your pyCharm project, you have to open the project settings (in the “File” Menu) and then click on the “Project Interpreter” sub menu. The packages installed in your virtual environment appear.

Now, it’s easy to add a package by clicking on the “+” icon and search for cx_oracle package, and click on Install Package to install it. (For compatibility matrix, visit https://oracle.github.io/python-cx_Oracle/)

Don’t forget to install an Oracle Client (Instant client or full client) and configure your windows environment with ORACLE_HOME and PATH environment variables. In the example below, I decided to set the environment variable in the script (because I have many clients installed on my laptop) :


from __future__ import print_function

import cx_Oracle
import os

oh="D:/tools/Oracle/instantclient_12_2_x8664"
os.environ["ORACLE_HOME"]=oh
os.environ["PATH"]=oh+os.pathsep+os.environ["PATH"]

print("Running tests for cx_Oracle version", cx_Oracle.version,"built at", cx_Oracle.buildtime)
print("File:", cx_Oracle.__file__)
print("Client Version:", ".".join(str(i) for i in cx_Oracle.clientversion()))

If this piece of code runs fine … you have correctly configured your environnent.

 

2. Connect to an Oracle database

Once your development environment configured, connecting a python script to an Oracle Database is really easy.

You just have to execute Connection constructor from cx_Oracle and then you will get a connection object. Of course, you have to catch the correct exception if an error occured during Connection call:


from __future__ import print_function

import cx_Oracle
import os

oh="D:/tools/Oracle/instantclient_12_2_x8664"
os.environ["ORACLE_HOME"]=oh
os.environ["PATH"]=oh+os.pathsep+os.environ["PATH"]
os.environ["NLS_LANG"]="AMERICAN_AMERICA.AL32UTF8"

def printConnectionAttr(connection):
    if connection is not None and isinstance(connection,cx_Oracle.Connection):
        print("Data Source Name  = ",connection.dsn)
        a="true" if connection.autocommit==1 else "False"
        print("Autocommit         = ",a)
        print("Session Edition    = ",connection.edition)
        print("Encoding           = ",connection.encoding)
        print("National Encoding  = ",connection.nencoding)
        print("Logical Tx Id      = ",connection.ltxid)
        print("Server version     = ",connection.version)

def connectToOracle(url, username, password, mode=None):
    if mode is not None:
        connection = cx_Oracle.Connection (user=username, password=password, dsn=url, mode=mode)
    else:
        connection = cx_Oracle.Connection (user=username, password=password, dsn=url)
    
    return connection


# main
if __name__ == '__main__':
    c=cx_Oracle.Connection
    try:
        c=connectToOracle("192.168.99.2:1521/orcl","sys","oracle",mode=cx_Oracle.SYSDBA)
    except cx_Oracle.DatabaseError as ex:
        err, =ex.args
        print("Error code    = ",err.code)
        print("Error Message = ",err.message)
        os._exit(1)

    printConnectionAttr(c)
    c.close()

 

This piece of code will connect to an Oracle Database and will print the connection details:

 

Data Source Name  =  192.168.99.2:1521/orcl
Autocommit         =  False
Session Edition    =  None
Encoding           =  UTF-8
National Encoding  =  UTF-8
Logical Tx Id      =  
Server version     =  12.1.0.2.0

Source code is fully available on my gitlab: https://gitlab.com/lolo115/python/tree/master/oracle

Ok, so that’s it for this first part … in a next blog post, I will present how to execute a basic statement with Python and Cx_Oracle

 

Insert data in Oracle with Java … Batched mode.

Problem / Context

Today, I worked on a performance issue at my customer. The context is a Java app which did a processing and at the end of this processing, the application inserts tons of data on an Oracle database.

The customers was complaining about slow performance on the insertion phase and asked me to have a look on it.

Database was IO Bound, and many sessions were doing some one-row inserts on various tables (INSERT INTO … VALUES (:1, :2, …) ). These one-row insert were off course processed many times, and oracle session’s stats was reporting a high number of executions.

On the Java side

As an Oracle DBA or Developer, we all know that, with PLSQL, it’s easy to write a FORALL loop that will bulk insert the data, and then reduce execution time. To do that, you need to have a cursor which is read in bulkl (BULK COLLECT) and each part is then inserted in the database. This reduces the number of operations, loops etc.


DECLARE
CURSOR s_cur IS
SELECT *
FROM servers;

TYPE fetch_array IS TABLE OF s_cur%ROWTYPE;
s_array fetch_array;
BEGIN
OPEN s_cur;
LOOP
FETCH s_cur BULK COLLECT INTO s_array LIMIT 1000;

FORALL i IN 1..s_array.COUNT
INSERT INTO servers2 VALUES s_array(i);

EXIT WHEN s_cur%NOTFOUND;
END LOOP;
CLOSE s_cur;
COMMIT;
END;
/

Source: http://psoug.org/snippet/FORALL-Insert_499.htm

In Java, the problem can be different because the data processed by your app are not necesseraly coming from a database.

Usually, statements are prepared, variables are attached and the statement is executed. When it’s done in a loop, it looks something like that:


try{
         DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver());
         Connection c = DriverManager.getConnection ("jdbc:oracle:thin:@192.168.99.8:1521:orcl","laurent","laurent");
         PreparedStatement stmt = c.prepareStatement("insert into emp(employee_id,first_name,last_name,salary) values (?,?,?,?)");
         start=System.currentTimeMillis();

         for (int i=0 ; i < 50000 ; i++) {
                  stmt.setInt(1,i);
                  stmt.setString(2,generateRandomChars("01234567890ABCDEF",16));
                  stmt.setString(3,generateRandomChars("01234567890ABCDEF",16));
                  stmt.setInt(4,r.nextInt(10000+1)+5000);
                  stmt.executeUpdate();
         }
         stop=System.currentTimeMillis();
         System.out.println("time = "+(stop-start));
         stmt.close();
         c.close();
   }
   catch(Exception e){
      System.err.println(e);
      e.printStackTrace();
   }

The problem is the statement is sent to the database 50000 times, and then increase number of operation between the database and the application. As a result, execution time is high (this demo has been executed on a VM on my laptop):

[oracle@oel6 JAVA]$ time java -cp $CLASSPATH:ojdbc7.jar:. TestNoBatchedInsert
time = 75430

real    1m16.514s
user    0m5.279s
sys     0m1.080s

As for PLSQL, there is a technique to write a beautiful and powerful java code that will process inserts by batchs, and then reduce the number of loops, calls between database and application etc.

To do that, instead of using executeStatement method X times, we can use the addBatch method that will create a “batch” which will be executed on the database when PreparedStatement’s method “executeBatch” will be called.

If you’re familiar with FORALL in PLSQL, you probably know that, the more the batchsize is big, the more memory you will use. In Java, it will be the same and the result will be a bigger heap size, that’s why it’s recommended to define a batch size and each time your batch will reach a max size, the batch will be executed. This can be done like this:

try{
        DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver());
        Connection c = DriverManager.getConnection ("jdbc:oracle:thin:@192.168.99.8:1521:orcl","laurent","laurent");
        PreparedStatement stmt = c.prepareStatement("insert into emp(employee_id,first_name,last_name,salary) values (?,?,?,?)");
        start=System.currentTimeMillis();

        int batchSize=5000;
        int count=0;
        for (int i=0 ; i < 50000 ; i++) {
                stmt.setInt(1,i);
                stmt.setString(2,generateRandomChars("01234567890ABCDEF",16));
                stmt.setString(3,generateRandomChars("01234567890ABCDEF",16));
                stmt.setInt(4,r.nextInt(10000+1)+5000);
                stmt.addBatch();

                if (++count % batchSize == 0) {
                        stmt.executeBatch();
                }
        }

        stop=System.currentTimeMillis();
        System.out.println("time = "+(stop-start));
        stmt.close();
        c.close();
}
catch(Exception e){
  System.err.println(e);
  e.printStackTrace();
}

Now, if I run this program, wall time is not really the same scale:

[oracle@oel6 JAVA]$ time java -cp $CLASSPATH:ojdbc7.jar:. TestBatchedInsert
time = 598

real    0m1.964s
user    0m3.393s
sys     0m0.269s

On the database side

AWR Reports show many things:

  • First,  if we run the java sample codes as they are described, the no batch mode will commit the database after each execute update. As a result, log file switch event will increase:

Non batched inserts:

Top 10 Foreground Events by Total Wait Time
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                                           Total Wait       Wait   % DB Wait
Event                                Waits Time (sec)    Avg(ms)   time Class
------------------------------ ----------- ---------- ---------- ------ --------
log file sync                       49,999         64       1.28   86.8 Commit
DB CPU                                            9.3              12.6
db file sequential read                 47          0       0.97     .1 User I/O
SQL*Net message to client           50,012          0       0.00     .1 Network
log file switch completion               1          0      27.60     .0 Configur
undo segment extension                   2          0       5.16     .0 Configur
direct path write                        3          0       1.08     .0 User I/O
direct path sync                         1          0       3.15     .0 User I/O
Disk file operations I/O                19          0       0.04     .0 User I/O
control file sequential read           194          0       0.00     .0 System I

Batched inserts:

Top 10 Foreground Events by Total Wait Time
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                                           Total Wait       Wait   % DB Wait
Event                                Waits Time (sec)    Avg(ms)   time Class
------------------------------ ----------- ---------- ---------- ------ --------
DB CPU                                             .3              72.9
SQL*Net more data from client          268          0       0.17   10.1 Network
log file sync                           13          0       3.29    9.5 Commit
db file sequential read                 49          0       0.37    4.0 User I/O
Disk file operations I/O                17          0       0.03     .1 User I/O
control file sequential read           134          0       0.00     .1 System I
SQL*Net more data to client             10          0       0.02     .1 Network
SQL*Net message to client               22          0       0.00     .0 Network
log file single write                    0          0                .0 System I
asynch descriptor resize                 0          0                .0 Other
  • Then, due to batched process, number of buffer gets is reduced when batched inserts are used

Non batched inserts:

Segments by Logical Reads                   DB/Inst: ORCL/orcl  Snaps: 200-201
-> Total Logical Reads:         212,288
-> Captured Segments account for   28.4% of Total
-> When ** MISSING ** occurs, some of the object attributes may not be available

                     Tablespace
Owner                   Name
-------------------- ----------
                     Subobject  Obj.                             Logical
Object Name            Name     Type        Obj#   Dataobj#        Reads  %Total
-------------------- ---------- ----- ---------- ---------- ------------ -------
LAURENT              USERS
EMP                             TABLE      93857      93860       52,576   24.77

Batched inserts:
Segments by Logical Reads                   DB/Inst: ORCL/orcl  Snaps: 201-202
-> Total Logical Reads:          11,792
-> Captured Segments account for   83.0% of Total
-> When ** MISSING ** occurs, some of the object attributes may not be available

                     Tablespace
Owner                   Name
-------------------- ----------
                     Subobject  Obj.                             Logical
Object Name            Name     Type        Obj#   Dataobj#        Reads  %Total
-------------------- ---------- ----- ---------- ---------- ------------ -------
LAURENT              USERS
EMP                             TABLE      93857      93860        3,504   29.72
  • Same for buffer gets. Execution number is less for batched inserts (number of execution depends on your batch size)

Non batched inserts:

     Buffer                 Gets              Elapsed
      Gets   Executions   per Exec   %Total   Time (s)  %CPU   %IO    SQL Id
----------- ----------- ------------ ------ ---------- ----- ----- -------------
    152,587      50,000          3.1   71.9        2.1  11.3     0 7c9ws5uvx9nyc
Module: JDBC Thin Client
insert into emp(employee_id,first_name,last_name,salary) values (:1 ,:2 ,:3 ,:4
)

Batched inserts:

     Buffer                 Gets              Elapsed
      Gets   Executions   per Exec   %Total   Time (s)  %CPU   %IO    SQL Id
----------- ----------- ------------ ------ ---------- ----- ----- -------------
      3,669          10        366.9   31.1        0.1    57     0 7c9ws5uvx9nyc
Module: JDBC Thin Client
insert into emp(employee_id,first_name,last_name,salary) values (:1 ,:2 ,:3 ,:4)

 

When developer have to insert tons on data from a java app, it’s better when they used the best way to do it …. batched inserts have to be used in that case, it will help the database to be more powerful.

That’s it for today 😉

Profiling Java Application with Systemtap

A couple of days ago, I read this tweet from Tanel Poder :

I’m not a JVM internals geek but I was sure there was a way to do the job without restarting the JVM, and I found some cool stuff with Systemtap.

To do this, you have to install two packages on your linux distribution: systemtap and systemtap-runtime-java (and configure correctly your user environment):

[root@spark ~]# yum install systemtap systemtap-runtime-java

Please note that I used a CentOS 7.4 distribution.

Then, and for the demo, I wrote a very small piece of Java that do these steps:

  1. Prints the JVM PID
  2. Wait for a key to be pressed. During this time, you will have to execute the systemtap script I will described later.
  3. Execute a loop ten times, each loop with print a message and wait one second, and this last step is executed in a method name “loop_and_wait”.

Here’s the sample code:


package com.premiseo;

import java.lang.*;
import java.io.BufferedReader;
import java.io.InputStreamReader;
import java.io.IOException;

class Example {
   public static void loop_and_wait(int n) throws InterruptedException{
           System.out.println("Waiting "+n+"ms... Tick");
           Thread.sleep(n);
        }

   public static void main(String[] args) {

      System.out.println("PID = "+java.lang.management.ManagementFactory.getRuntimeMXBean().getName().split("@")[0]);
      System.out.println("Press any key when ready ...");

      try {
        BufferedReader in = new BufferedReader(new InputStreamReader(System.in));
        String next = in.readLine();
      }
      catch (IOException ioe) {
        ioe.printStackTrace();
      }

      try {
        for (int i=0;i<10;i++) {
           loop_and_wait(1000);
        }
      }
      catch (InterruptedException ie) {
        ie.printStackTrace();
      }
   }
}

Then, compile and execute … very basic I said 😉

[spark@spark java]$ javac -cp $CLASSPATH:. com/premiseo/Example.java
[spark@spark java]$ java -cp $CLASSPATH:. com.premiseo.Example
PID = 9928
Press any key when ready ...

Waiting 1000ms... Tick
Waiting 1000ms... Tick
Waiting 1000ms... Tick
Waiting 1000ms... Tick
Waiting 1000ms... Tick
Waiting 1000ms... Tick
Waiting 1000ms... Tick
Waiting 1000ms... Tick
Waiting 1000ms... Tick
Waiting 1000ms... Tick

Now, to answer to Tanel, I used a short systemtap script that will profile the program and specially the loop_and_wait method. I will count the number of times the loop_and_wait method has been called, and I will account the time spent in this method execution.

To do that, I had to write two probes related to:

  • the full name of the class, including the package name: com.premiseo.Example
  • the class name where the method is defined: Example
  • the method name I want to profile: loop_and_wait

The first one will be executed when the program will start to execute the targeted method (java(“com.premiseo.Example”).class(“Example”).method(“loop_and_wait”)), the second one will be executed when the method will return (java(“com.premiseo.Example”).class(“Example”).method(“loop_and_wait”).return)

The related systemtap script is given below:

#!/usr/bin/env stap

global counter,timespent,t

probe begin {
  printf("Press Ctrl+C to stop profiling\n")
  counter=0
  timespent=0
}

probe java("com.premiseo.Example").class("Example").method("loop_and_wait")
{
  counter++
  t=gettimeofday_ms()
}

probe java("com.premiseo.Example").class("Example").method("loop_and_wait").return
{
  timespent+=gettimeofday_ms()-t
}

probe end {
   printf("Number of calls for loop_and_wait method: %ld \n",    counter)
   printf("Time Spent in method loop_and_wait: %ld msecs \n", timespent)
}

Execution of this systemtap script gave the following result (click the image for full size):

Is it dynamic? Yes, no need to restart the running JVM process you want to target. If you want to target a specific JVM process id, you can use the stap’s “-x” option, add the modify your probe definition like this:

probe java("com.premiseo.Example").class("Example").method("loop_and_wait")
{
  if (pid() == target())
    counter++
    t=gettimeofday_ms()
}

There’s a limitation, you cannot use wilcards in the java probe definition (java(“com.premiseo.Example”).class(“Example”).method(“loop*”) … for example). That would have been useful to profile a set of methods in the same class … but not possible currently.

If you want to read more about this kind of stuff, please read the following websites:

And … that’s all for today !! 😉