Oracle … as usual

Oracle by Laurent Leturgez

Category Archives: PL/SQL

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 ūüėČ

Advertisements

Oracle 12c: queryable Opatch

One of a cool new feature in Oracle 12c is queryable Opatch. Oracle offers a set of tables and a PL/SQL package to query the Oracle Inventory.

With this feature, you can query the inventory to know if a one off patch has been deployed, which components have been deployed etc.

Tables related to this feature are:

  • OPATCH_XML_INV
  • OPATCH_XINV_TAB
  • OPATCH_INST_JOB
  • OPATCH_INST_PATCH

The OPATCH_XINV_TAB table is in fact external table preprocessed with specific script :

SQL> select dbms_metadata.get_ddl('TABLE','OPATCH_XML_INV','SYS') from dual;

DBMS_METADATA.GET_DDL('TABLE','OPATCH_XML_INV','SYS')
--------------------------------------------------------------------------------

  CREATE TABLE "SYS"."OPATCH_XML_INV"
   (    "XML_INVENTORY" CLOB
   )
   ORGANIZATION EXTERNAL
    ( TYPE ORACLE_LOADER
      DEFAULT DIRECTORY "OPATCH_SCRIPT_DIR"
      ACCESS PARAMETERS
      ( RECORDS DELIMITED BY NEWLINE
      READSIZE 67108864
      preprocessor opatch_script_dir:'qopiprep.bat'
      BADFILE opatch_script_dir:'qopatch_bad.bad'
      LOGFILE opatch_log_dir:'qopatch_log.log'
      FIELDS TERMINATED BY 'UIJSVTBOEIZBEFFQBL'
      MISSING FIELD VALUES ARE NULL
      REJECT ROWS WITH ALL NULL FIELDS
      (
        xml_inventory    CHAR(100000000)
      )
        )
      LOCATION
       ( "OPATCH_SCRIPT_DIR":'qopiprep.bat'
       )
    )
   REJECT LIMIT UNLIMITED
The script is located in the path pointed by oracle directory OPATCH_SCRIPT_DIR.
On my server:
[oracle@oel63 ~]$ find $ORACLE_HOME -name "qopiprep.bat"
/u01/app/oracle/product/12.1.0/dbhome_1/QOpatch/qopiprep.bat
SQL> select * from dba_directories where directory_name='OPATCH_SCRIPT_DIR';

OWNER      DIRECTORY_NAME                 DIRECTORY_PATH                                     ORIGIN_CON_ID
---------- ------------------------------ -------------------------------------------------- -------------
SYS        OPATCH_SCRIPT_DIR              /u01/app/oracle/product/12.1.0/dbhome_1/QOpatch                1
The content of this script is pasted below:
#!/bin/sh
cd $ORACLE_HOME
PATH=/bin:/usr/bin
export PATH
 
$ORACLE_HOME/OPatch/opatch lsinventory -xml  $ORACLE_HOME/QOpatch/xml_file.xml -retry 0 -invPtrLoc $ORACLE_HOME/oraInst.loc >> $ORACLE_HOME/QOpatch/stout.txt
`echo "UIJSVTBOEIZBEFFQBL" >> $ORACLE_HOME/QOpatch/xml_file.xml`
echo `cat $ORACLE_HOME/QOpatch/xml_file.xml`
rm $ORACLE_HOME/QOpatch/xml_file.xml
rm $ORACLE_HOME/QOpatch/stout.txt
This is the base of the feature which is accessed through a PL/SQL package DBMS_QOPATCH.
This package has many functions, for example:
  • IS_PATCH_INSTALLED
  • GET_OPATCH_LIST
  • GET_OPATCH_FILES
For example, if we execute the GET_OPATCH_BUGS (which provides bugs list in a specific patch or for all patches), the result is a XML output:
SQL> select dbms_qopatch.GET_OPATCH_BUGS from dual;

GET_OPATCH_BUGS
------------------------------------------------------------------------------------------------------------------------------------------------------
<bugInfo><bugs xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><bug id="9448798"><UId>FlexibleDataType-957ebac1-ef13-462b-9157-b1e0cb711840</UId
><description>11202 dummy one-off for testing</description></bug></bugs></bugInfo>
This is not very user friendly even with a lightweight output … try to execute¬†DBMS_QOPATCH.GET_OPATCH_LSINVENTORY¬†function, there will be more stuff to analyze ;).
That’s why Oracle delivered a XSLT sheet to transform these outputs, you can get this sheet by executing the DBMS_QOPATCH.GET_OPATCH_XSLT function:
SQL> select xmltransform(DBMS_QOPATCH.GET_OPATCH_LSINVENTORY, DBMS_QOPATCH.GET_OPATCH_XSLT) from dual;

XMLTRANSFORM(DBMS_QOPATCH.GET_OPATCH_LSINVENTORY,DBMS_QOPATCH.GET_OPATCH_XSLT)
----------------------------------------------------------------------------------------------------

Oracle Querayable Patch Interface 1.0
--------------------------------------------------------------------------------
Oracle Home       : /u01/app/oracle/product/12.1.0/dbhome_1
Inventory         : /u01/app/oraInventory
--------------------------------------------------------------------------------Installed Top-level
Products (1):
Oracle Database 12c                                    12.1.0.1.0
Installed Products ( 131)

Oracle Database 12c                                         12.1.0.1.0
Sun JDK                                                     1.6.0.37.0
oracle.swd.oui.core.min                                     12.1.0.1.0
Installer SDK Component                                     12.1.0.1.0
Oracle One-Off Patch Installer                              12.1.0.1.0
 .../...
Or with my example (bugs corrected by a specific or all OneOff patches) :
SQL> select xmltransform(DBMS_QOPATCH.GET_OPATCH_BUGS, DBMS_QOPATCH.GET_OPATCH_XSLT) from dual;

XMLTRANSFORM(DBMS_QOPATCH.GET_OPATCH_BUGS,DBMS_QOPATCH.GET_OPATCH_XSLT)
----------------------------------------------------------------------------------------------------

  Bugs fixed:
         9448798

Of course, you can write your own XSLT sheet, if you want more information. For example, I want the patch Id and the description of this one:

SQL> select xmltransform(dbms_qopatch.GET_OPATCH_BUGS,
  2  '<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  3  <xsl:template match="bug">
  4  <xsl:text>Patch Id= </xsl:text><xsl:value-of select="@id"/>
  5  <xsl:text>      Description:  </xsl:text> <xsl:value-of select="description"/>
  6  </xsl:template>
  7  </xsl:stylesheet>')
  8  from dual
  9  /

XMLTRANSFORM(DBMS_QOPATCH.GET_OPATCH_BUGS,'<XSL:STYLESHEETVERSION="1.0"XMLNS:XSL="HTTP://WWW.W3.ORG/
----------------------------------------------------------------------------------------------------
Patch Id= 9448798      Description:  11202 dummy one-off for testing

Managing database audit Trail in Oracle 11gR2

Due to American Security laws, Oracle has changed its audit management rules in Oracle 11g.

Now, AUDIT_TRAIL is configured bydefault to ‘DB’. Many operations are now audited by default and stored in the AUD$ table (which is stored by default in the SYSTEM tablespace).

Audited operations are available in the DBA_PRIV_AUDIT_OPTS view, and if you have a look in it, you will see that “CREATE SESSION” operations are audited.

So, if you create your database with ‘dbca’, if you update your database with ‘dbua’, you leave the default configuration, and your SYSTEM Tablespace datafile is in autoextend mode … you will probably have a huge system datafile soon.

To manage the audit trail, you have different solutions:

– The first one is to disable audit trail: set AUDIT_TRAIL to NONE into your spfile file, shutdown and restart your database.

– The second one is to move your AUD$ to another tablespace :

SQL> select table_name,tablespace_name from dba_tables where table_name='AUD$';

TABLE_NAME      TABLESPACE_NAME
--------------- ---------------
AUD$            SYSTEM
SQL> exec DBMS_AUDIT_MGMT.set_audit_trail_location(audit_trail_type=>DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, audit_trail_location_value => 'AUD_TS');

PL/SQL procedure successfully completed.
SQL> select table_name,tablespace_name from dba_tables where table_name='AUD$';

TABLE_NAME      TABLESPACE_NAME
--------------- ---------------
AUD$            AUD_TS

– The third one is to manage the properties of the audit trail with the DBMS_AUDIT_MGMT PL/SQL package

If you want to change it, you have to use DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY procedure to configure properties of the audit trail:

SQL> begin
  2  DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY(audit_trail_type=>DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
  3                                           audit_trail_property=>DBMS_AUDIT_MGMT.DB_DELETE_BATCH_SIZE,
  4                                           audit_trail_property_value=>1000);
  5  end;
  6  /

Then, you have to initialize the cleanup, and to define the oldest record to leave in the table (Here 30 days):

SQL> begin
  2  DBMS_AUDIT_MGMT.INIT_CLEANUP(audit_trail_type=> DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
  3                               default_cleanup_interval=>720);
  4
  5  DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(audit_trail_type  => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
  6                                             last_archive_time => SYSTIMESTAMP-30);
  7  end;
  8  /

Next, we create a job. This job will be executed every 168 hours (7 days).

SQL> BEGIN
  2  DBMS_AUDIT_MGMT.CREATE_PURGE_JOB(audit_trail_type=> DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
  3                                   audit_trail_purge_interval => 168,
  4                                   audit_trail_purge_name=> 'PURGE_AUD_JOB',
  5                                   use_last_arch_timestamp => TRUE);
  6  END;
  7  /
Note : if USE_LAST_ARCH_TIMESTAMP is set to TRUE, the job will purge only the oldest entries that match to the corresponding retention parameter. You can have a look to the DBA_SCHEDULER_JOBS to have more details about this new job.

Here I gave you an example to purge the DB audit structure (AUD$ and FGA_AUD$ tables), if you have a look to the Oracle documentation, you can use the same procedure to purge OS Audit File, XML File.

Generate a random date in Oracle

This morning, I had to generate a random date using DBMS_RANDOM package. As the documentation mentions this package can only generate alphanumeric and numeric value, I have to search a little bit.

So the trick for generating a random date is to choose two dates between the random process will choose a date.

Those dates will be translated to Julian format. As the Julian format is the number of days since January 1, 4712 BC, you can use the numeric random generation procedure of DBMS_RANDOM package.

Let’s see how we can generate a random date between 2009 Jan, 1st and 2009 Dec, 31st :

SQL> create table t (d date);
Table created.

SQL> select to_char(to_date('2009/01/01','YYYY/MM/DD'),'J') "20090101",
2 to_char(to_date('2009/12/31','YYYY/MM/DD'),'J') "20091231"
3 from dual;

2009010 2009123
------- -------
2454833 2455197

SQL> begin
2 for i in 1..5 loop
3 insert into t values(to_date(trunc(dbms_random.value(2454833,2455197)),'J'));
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed.

SQL> select * from t;
D
----------------------
2009-NOV-30 12AM:00:00
2009-MAY-31 12AM:00:00
2009-SEP-01 12AM:00:00
2009-DEC-28 12AM:00:00
2009-MAY-03 12AM:00:00