Deleting a Node From 12cR1 RAC

Deleting a node from a 12cR1 RAC is similar to that of 11gR2. Deletion has three distinct phases, that is removing of the database instance, removing of Oracle database software and finally the clusterware. However as per oracle documentation "you can remove the Oracle RAC database instance from the node before removing the node from the cluster but this step is not required. If you do not remove the instance, then the instance is still configured but never runs. It must also be noted from Oracle documentation that "deleting a node from a cluster does not remove a node's configuration information from the cluster. The residual configuration information does not interfere with the operation of the cluster". For example it is possible to see some information with regard to the deleted node on an OCR dump file and this shouldn't be a cause for concern.
The RAC setup in this case is a 2 node RAC and node named rhel12c2 will be removed from the cluster. The database is a CDB which has a single PDB.

SQL>  select instance_number,instance_name,host_name from gv$instance;

INSTANCE_NUMBER INSTANCE_NAME    HOST_NAME
--------------- ---------------- --------------------
              1 cdb12c1          rhel12c1.domain.net
              2 cdb12c2          rhel12c2.domain.net # node and instance to be removed

SQL> select con_id,dbid,name from gv$pdbs;

    CON_ID       DBID NAME
---------- ---------- ---------
         2 4066687628 PDB$SEED
         3  476277969 PDB12C
         2 4066687628 PDB$SEED
         3  476277969 PDB12C

1. First phase include removing the database instance from the node to be deleted. For this run the DBCA on any node except on the node that has the instance being deleted. In this case DBCA is run from node rhel12c1. Follow the instance management option to remove the instance.

Following message is shown as pdbsvc is created to connect to PDB.

2. At the end of the DBCA run the database instance is removed from the node to be deleted

SQL> select instance_number,instance_name,host_name from gv$instance;

INSTANCE_NUMBER INSTANCE_NAME    HOST_NAME
--------------- ---------------- -------------------
              1 cdb12c1          rhel12c1.domain.net

SQL> select con_id,dbid,name from gv$pdbs;

    CON_ID       DBID NAME
---------- ---------- ---------
         2 4066687628 PDB$SEED
         3  476277969 PDB12C

[oracle@rhel12c1 ~]$ srvctl config database -d cdb12c
Database unique name: cdb12c
Database name: cdb12c
Oracle home: /opt/app/oracle/product/12.1.0/dbhome_1
Oracle user: oracle
Spfile: +DATA/cdb12c/spfilecdb12c.ora
Password file: +DATA/cdb12c/orapwcdb12c
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: cdb12c
Database instances: cdb12c1 # only shows the remaining instance
Disk Groups: DATA,FLASH
Mount point paths:
Services: pdbsvc
Type: RAC
Start concurrency:
Stop concurrency:
Database is administrator managed

3. Check if redo log threads for the deleted instance is removed from the database

SQL> select inst_id,group#,thread# from gv$log;

   INST_ID     GROUP#    THREAD#
---------- ---------- ----------
         1          1          1
         1          2          1

As it only shows redo threads for instance 1 no further action is needed. If DBCA has not removed the redo log threads with alter database disable thread thread# With this step the first phase is completed
4. Second phase is to remove the Oracle database software. In 12c by default the listener runs out of the grid home. However it is possible to setup a listener to run out of the Oracle home (RAC home) as well. If this is the case then stop and disable any listeners running out of the RAC home. In this configuration there's no listeners running out of the RAC home.
5. On the node to be deleted update the node list to include only the node being deleted. Before running the node update command the inventory.xml will have all the nodes under the oracle home after the command is run this will reduce to containing only the node to be deleted. However the inventory.xml in other nodes will still have all the nodes in the cluster under oracle home.

<HOME NAME="OraDB12Home1" LOC="/opt/app/oracle/product/12.1.0/dbhome_1" TYPE="O" IDX="2">
   <NODE_LIST>
      <NODE NAME="rhel12c1"/>
      <NODE NAME="rhel12c2"/>
   </NODE_LIST>
</HOME>


[oracle@rhel12c2 ~]$ /opt/app/oracle/product/12.1.0/dbhome_1/oui/bin/runInstaller.sh -updateNodeList ORACLE_HOME=/opt/app/oracle/product/12.1.0/dbhome_1 "CLUSTER_NODES={rhel12c2}" -local
Starting Oracle Universal Installer...

<HOME NAME="OraDB12Home1" LOC="/opt/app/oracle/product/12.1.0/dbhome_1" TYPE="O" IDX="2">
   <NODE_LIST>
      <NODE NAME="rhel12c2"/>
   </NODE_LIST>
</HOME>

6. Run the deinstall command with local option. Without the -local option this will remove the oracle home of all the nodes!

[oracle@rhel12c2 deinstall]$ ./deinstall -local
Checking for required files and bootstrapping ...
Please wait ...
Location of logs /opt/app/oraInventory/logs/

############ ORACLE DEINSTALL & DECONFIG TOOL START ############
######################### CHECK OPERATION START #########################
## [START] Install check configuration ##
Checking for existence of the Oracle home location /opt/app/oracle/product/12.1.0/dbhome_1
Oracle Home type selected for deinstall is: Oracle Real Application Cluster Database
Oracle Base selected for deinstall is: /opt/app/oracle
Checking for existence of central inventory location /opt/app/oraInventory
Checking for existence of the Oracle Grid Infrastructure home /opt/app/12.1.0/grid
The following nodes are part of this cluster: rhel12c2,rhel12c1
Checking for sufficient temp space availability on node(s) : 'rhel12c2,rhel12c1'
## [END] Install check configuration ##

Network Configuration check config START
Network de-configuration trace file location: /opt/app/oraInventory/logs/netdc_check2014-02-27_11-08-10-AM.log
Network Configuration check config END
Database Check Configuration START
Database de-configuration trace file location: /opt/app/oraInventory/logs/databasedc_check2014-02-27_11-08-16-AM.log
Use comma as separator when specifying list of values as input

Specify the list of database names that are configured locally on this node for this Oracle home. Local configurations of the discovered databases will be removed []:
Database Check Configuration END
Oracle Configuration Manager check START
OCM check log file location : /opt/app/oraInventory/logs//ocm_check9786.log
Oracle Configuration Manager check END

######################### CHECK OPERATION END #########################
####################### CHECK OPERATION SUMMARY #######################
Oracle Grid Infrastructure Home is: /opt/app/12.1.0/grid
The cluster node(s) on which the Oracle home deinstallation will be performed are:rhel12c2,rhel12c1
Since -local option has been specified, the Oracle home will be deinstalled only on the local node, 'rhel12c2'.
Oracle Home selected for deinstall is: /opt/app/oracle/product/12.1.0/dbhome_1
Inventory Location where the Oracle home registered is: /opt/app/oraInventory
Checking the config status for CCR
rhel12c2 : Oracle Home exists with CCR directory, but CCR is not configured
rhel12c1 : Oracle Home exists and CCR is configured
CCR check is finished
Do you want to continue (y - yes, n - no)? [n]: y
A log of this session will be written to: '/opt/app/oraInventory/logs/deinstall_deconfig2014-02-27_11-07-43-AM.out'
Any error messages from this session will be written to: '/opt/app/oraInventory/logs/deinstall_deconfig2014-02-27_11-07-43-AM.err'
######################## CLEAN OPERATION START ########################
Database de-configuration trace file location: /opt/app/oraInventory/logs/databasedc_clean2014-02-27_11-08-52-AM.log

Network Configuration clean config START
Network de-configuration trace file location: /opt/app/oraInventory/logs/netdc_clean2014-02-27_11-08-52-AM.log
Network Configuration clean config END
Oracle Configuration Manager clean START
OCM clean log file location : /opt/app/oraInventory/logs//ocm_clean9786.log
Oracle Configuration Manager clean END
Setting the force flag to false
Setting the force flag to cleanup the Oracle Base
Oracle Universal Installer clean START

Detach Oracle home '/opt/app/oracle/product/12.1.0/dbhome_1' from the central inventory on the local node : Done
Delete directory '/opt/app/oracle/product/12.1.0/dbhome_1' on the local node : Done
The Oracle Base directory '/opt/app/oracle' will not be removed on local node. The directory is in use by Oracle Home '/opt/app/12.1.0/grid'.
Oracle Universal Installer cleanup was successful.
Oracle Universal Installer clean END

## [START] Oracle install clean ##
Clean install operation removing temporary directory '/tmp/deinstall2014-02-27_11-06-59AM' on node 'rhel12c2'
Clean install operation removing temporary directory '/tmp/deinstall2014-02-27_11-06-59AM' on node 'rhel12c1'
## [END] Oracle install clean ##

######################### CLEAN OPERATION END #########################
####################### CLEAN OPERATION SUMMARY #######################
Cleaning the config for CCR
Cleaning the CCR configuration by executing its binaries
As CCR is not configured, so skipping the cleaning of CCR configuration
CCR clean is finished
Successfully detached Oracle home '/opt/app/oracle/product/12.1.0/dbhome_1' from the central inventory on the local node.
Successfully deleted directory '/opt/app/oracle/product/12.1.0/dbhome_1' on the local node.
Oracle Universal Installer cleanup was successful.

Oracle deinstall tool successfully cleaned up temporary directories.
#######################################################################
############# ORACLE DEINSTALL & DECONFIG TOOL END #############

7. After the deinstall completed run the node update command on any remaining node. This will update the node list by removing the deleted oracle home from the node list. The inventory.xml output before and after command has executed is shown. The command shown is for non shred oracle homes. For shared homes folow oracle documentation.

<HOME NAME="OraDB12Home1" LOC="/opt/app/oracle/product/12.1.0/dbhome_1" TYPE="O" IDX="2">
   <NODE_LIST>
      <NODE NAME="rhel12c1"/>
      <NODE NAME="rhel12c2"/>
   </NODE_LIST>
</HOME>

[oracle@rhel12c1 ~]$ /opt/app/oracle/product/12.1.0/dbhome_1/oui/bin/runInstaller.sh -updateNodeList ORACLE_HOME=/opt/app/oracle/product/12.1.0/dbhome_1 "CLUSTER_NODES={rhel12c1}" LOCAL_NODE=rhel12c1
Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB.   Actual 5119 MB    Passed
The inventory pointer is located at /etc/oraInst.loc
'UpdateNodeList' was successful.

<HOME NAME="OraDB12Home1" LOC="/opt/app/oracle/product/12.1.0/dbhome_1" TYPE="O" IDX="2">
   <NODE_LIST>
      <NODE NAME="rhel12c1"/>
   </NODE_LIST>
</HOME>

This conclude the second phase. Final phase is to remove the clusterware.



8. Check if the node to be deleted is active and unpinned. If the node is pinned then unpin it with crsctl unpin command. Following could be run as either grid user or root

[grid@rhel12c2 ~]$ olsnodes -t -s
rhel12c1        Active  Unpinned
rhel12c2        Active  Unpinned

9. On the node to be deleted run the node update command to update the node list for the grid home such that it will include only the node being deleted. The inventory.xml output before and after the command has been executed is shown below.

<HOME NAME="OraGI12Home1" LOC="/opt/app/12.1.0/grid" TYPE="O" IDX="1" CRS="true">
   <NODE_LIST>
      <NODE NAME="rhel12c1"/>
      <NODE NAME="rhel12c2"/>
   </NODE_LIST>
</HOME>

[grid@rhel12c2 ~]$ /opt/app/12.1.0/grid/oui/bin/runInstaller -updateNodeList ORACLE_HOME=/opt/app/12.1.0/grid "CLUSTER_NODES={rhel12c2}" CRS=TRUE -silent -local
Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB.   Actual 5119 MB    Passed
The inventory pointer is located at /etc/oraInst.loc
'UpdateNodeList' was successful.

<HOME NAME="OraGI12Home1" LOC="/opt/app/12.1.0/grid" TYPE="O" IDX="1" CRS="true">
   <NODE_LIST>
      <NODE NAME="rhel12c2"/>
   </NODE_LIST>
</HOME>

10. If the GI home is non shared run the deinstall with -local option. If -local option is omitted this will remove GI from all nodes.

[grid@rhel12c2 ~]$ /opt/app/12.1.0/grid/deinstall/deinstall -local
Checking for required files and bootstrapping ...
Please wait ...
Location of logs /tmp/deinstall2014-02-27_04-47-10PM/logs/

############ ORACLE DEINSTALL & DECONFIG TOOL START ############

######################### CHECK OPERATION START #########################
## [START] Install check configuration ##
Checking for existence of the Oracle home location /opt/app/12.1.0/grid
Oracle Home type selected for deinstall is: Oracle Grid Infrastructure for a Cluster
Oracle Base selected for deinstall is: /opt/app/oracle
Checking for existence of central inventory location /opt/app/oraInventory
Checking for existence of the Oracle Grid Infrastructure home /opt/app/12.1.0/grid
The following nodes are part of this cluster: rhel12c2,rhel12c1
Checking for sufficient temp space availability on node(s) : 'rhel12c2,rhel12c1'
## [END] Install check configuration ##

Traces log file: /tmp/deinstall2014-02-27_04-47-10PM/logs//crsdc_2014-02-27_04-48-27PM.log
Network Configuration check config START
Network de-configuration trace file location: /tmp/deinstall2014-02-27_04-47-10PM/logs/netdc_check2014-02-27_04-48-29-PM.log
Network Configuration check config END
Asm Check Configuration START
ASM de-configuration trace file location: /tmp/deinstall2014-02-27_04-47-10PM/logs/asmcadc_check2014-02-27_04-48-29-PM.log
Database Check Configuration START
Database de-configuration trace file location: /tmp/deinstall2014-02-27_04-47-10PM/logs/databasedc_check2014-02-27_04-48-29-PM.log
Database Check Configuration END

######################### CHECK OPERATION END #########################
####################### CHECK OPERATION SUMMARY #######################
Oracle Grid Infrastructure Home is: /opt/app/12.1.0/grid
The cluster node(s) on which the Oracle home deinstallation will be performed are:rhel12c2,rhel12c1
Since -local option has been specified, the Oracle home will be deinstalled only on the local node, 'rhel12c2'.
Oracle Home selected for deinstall is: /opt/app/12.1.0/grid
Inventory Location where the Oracle home registered is: /opt/app/oraInventory
Option -local will not modify any ASM configuration.
Do you want to continue (y - yes, n - no)? [n]: y
A log of this session will be written to: '/tmp/deinstall2014-02-27_04-47-10PM/logs/deinstall_deconfig2014-02-27_04-48-01-PM.out'
Any error messages from this session will be written to: '/tmp/deinstall2014-02-27_04-47-10PM/logs/deinstall_deconfig2014-02-27_04-48-01-PM.err'

######################## CLEAN OPERATION START ########################
Database de-configuration trace file location: /tmp/deinstall2014-02-27_04-47-10PM/logs/databasedc_clean2014-02-27_04-48-33-PM.log
ASM de-configuration trace file location: /tmp/deinstall2014-02-27_04-47-10PM/logs/asmcadc_clean2014-02-27_04-48-33-PM.log
ASM Clean Configuration END
Network Configuration clean config START
Network de-configuration trace file location: /tmp/deinstall2014-02-27_04-47-10PM/logs/netdc_clean2014-02-27_04-48-34-PM.log
Network Configuration clean config END

---------------------------------------->
The deconfig command below can be executed in parallel on all the remote nodes. Execute the command on  the local node after the execution completes on all the remote nodes.

Run the following command as the root user or the administrator on node "rhel12c2".
/tmp/deinstall2014-02-27_04-47-10PM/perl/bin/perl -I/tmp/deinstall2014-02-27_04-47-10PM/perl/lib -I/tmp/deinstall2014-02-27_04-47-10PM/crs/install /tmp/deinstall2014-02-27_04-47-10PM/crs/install/rootcrs.pl -force  -deconfig -paramfile "/tmp/deinstall2014-02-27_04-47-10PM/response/deinstall_OraGI12Home1.rsp"
Press Enter after you finish running the above commands

<----------------------------------------

[root@rhel12c2 ~]# /tmp/deinstall2014-02-27_04-47-10PM/perl/bin/perl -I/tmp/deinstall2014-02-27_04-47-10PM/perl/lib -I/tmp/deinstall2014-02-27_04-47-10PM/crs/install /tmp/deinstall2014-02-27_04-47-10PM/crs/install/rootcrs.pl -force  -deconfig -paramfile "/tmp/deinstall2014-02-27_04-47-10PM/response/deinstall_OraGI12Home1.rsp"
Using configuration parameter file: /tmp/deinstall2014-02-27_04-47-10PM/response/deinstall_OraGI12Home1.rsp
Network 1 exists
Subnet IPv4: 192.168.0.0/255.255.255.0/eth0, static
Subnet IPv6:
VIP exists: network number 1, hosting node rhel12c1
VIP Name: rhel12c1-vip
VIP IPv4 Address: 192.168.0.89
VIP IPv6 Address:
VIP exists: network number 1, hosting node rhel12c2
VIP Name: rhel12c2-vip
VIP IPv4 Address: 192.168.0.90
VIP IPv6 Address:
ONS exists: Local port 6100, remote port 6200, EM port 2016

CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'rhel12c2'
CRS-2673: Attempting to stop 'ora.crsd' on 'rhel12c2'
CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on 'rhel12c2'
CRS-2673: Attempting to stop 'ora.FLASH.VOLUME1.advm' on 'rhel12c2'
CRS-2673: Attempting to stop 'ora.CLUSTERDG.dg' on 'rhel12c2'
CRS-2677: Stop of 'ora.FLASH.VOLUME1.advm' on 'rhel12c2' succeeded
CRS-2673: Attempting to stop 'ora.FLASH.dg' on 'rhel12c2'
CRS-2677: Stop of 'ora.FLASH.dg' on 'rhel12c2' succeeded
CRS-2677: Stop of 'ora.CLUSTERDG.dg' on 'rhel12c2' succeeded
CRS-2673: Attempting to stop 'ora.DATA.dg' on 'rhel12c2'
CRS-2677: Stop of 'ora.DATA.dg' on 'rhel12c2' succeeded
CRS-2673: Attempting to stop 'ora.asm' on 'rhel12c2'
CRS-2677: Stop of 'ora.asm' on 'rhel12c2' succeeded
CRS-2673: Attempting to stop 'ora.net1.network' on 'rhel12c2'
CRS-2677: Stop of 'ora.net1.network' on 'rhel12c2' succeeded
CRS-2792: Shutdown of Cluster Ready Services-managed resources on 'rhel12c2' has completed
CRS-2677: Stop of 'ora.crsd' on 'rhel12c2' succeeded
CRS-2673: Attempting to stop 'ora.drivers.acfs' on 'rhel12c2'
CRS-2673: Attempting to stop 'ora.evmd' on 'rhel12c2'
CRS-2673: Attempting to stop 'ora.storage' on 'rhel12c2'
CRS-2673: Attempting to stop 'ora.mdnsd' on 'rhel12c2'
CRS-2673: Attempting to stop 'ora.gpnpd' on 'rhel12c2'
CRS-2677: Stop of 'ora.drivers.acfs' on 'rhel12c2' succeeded
CRS-2677: Stop of 'ora.storage' on 'rhel12c2' succeeded
CRS-2677: Stop of 'ora.mdnsd' on 'rhel12c2' succeeded
CRS-2677: Stop of 'ora.gpnpd' on 'rhel12c2' succeeded
CRS-2677: Stop of 'ora.evmd' on 'rhel12c2' succeeded
CRS-2673: Attempting to stop 'ora.ctssd' on 'rhel12c2'
CRS-2673: Attempting to stop 'ora.asm' on 'rhel12c2'
CRS-2677: Stop of 'ora.ctssd' on 'rhel12c2' succeeded
CRS-2677: Stop of 'ora.asm' on 'rhel12c2' succeeded
CRS-2673: Attempting to stop 'ora.cluster_interconnect.haip' on 'rhel12c2'
CRS-2677: Stop of 'ora.cluster_interconnect.haip' on 'rhel12c2' succeeded
CRS-2673: Attempting to stop 'ora.cssd' on 'rhel12c2'
CRS-2677: Stop of 'ora.cssd' on 'rhel12c2' succeeded
CRS-2673: Attempting to stop 'ora.gipcd' on 'rhel12c2'
CRS-2677: Stop of 'ora.gipcd' on 'rhel12c2' succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'rhel12c2' has completed
CRS-4133: Oracle High Availability Services has been stopped.

2014/02/27 16:53:43 CLSRSC-336: Successfully deconfigured Oracle clusterware stack on this node

Failed to delete the directory '/opt/app/oracle/product/12.1.0'. The directory is in use.
Failed to delete the directory '/opt/app/oracle/diag/rdbms/cdb12c/cdb12c2/log/test'. The directory is in use.

Removal of some of the directories failed but this had no impact on the removing of the node from the cluster. These directories could be manually cleaned up afterwards.
11. From any remaining node run the following command with reaming nodes as the node list. The inventory.xml output is given before and after the command is run

<HOME NAME="OraGI12Home1" LOC="/opt/app/12.1.0/grid" TYPE="O" IDX="1" CRS="true">
   <NODE_LIST>
      <NODE NAME="rhel12c1"/>
      <NODE NAME="rhel12c2"/>
   </NODE_LIST>
</HOME>

[grid@rhel12c1 bin]$ ./runInstaller -updateNodeList ORACLE_HOME=/opt/app/12.1.0/grid "CLUSTER_NODES={rhel12c1}" CRS=TRUE -silent
Starting Oracle Universal Installer...
Checking swap space: must be greater than 500 MB.   Actual 5119 MB    Passed
The inventory pointer is located at /etc/oraInst.loc
'UpdateNodeList' was successful.

<HOME NAME="OraGI12Home1" LOC="/opt/app/12.1.0/grid" TYPE="O" IDX="1" CRS="true">
   <NODE_LIST>
      <NODE NAME="rhel12c1"/>
   </NODE_LIST>
</HOME>

12. From the node remaining in the cluster run the node deletion command as root

[root@rhel12c1 bin]# crsctl delete node -n rhel12c2
CRS-4661: Node rhel12c2 successfully deleted.

13. Finally use the cluster verification utility to check the node deletion has completed successfully.

[grid@rhel12c1 bin]$ cluvfy stage -post nodedel -n rhel12c2

Performing post-checks for node removal

Checking CRS integrity...
CRS integrity check passed
Clusterware version consistency passed.
Node removal check passed
Post-check for node removal was successful.

This conclude the deletion of node from 12cR1 RAC.

Related Post
Deleting a Node From 11gR2 RAC
Deleting a 11gR1 RAC Node

Oracle vs Standard JDBC Batching For Updates

Oracle provided two flavors of batching with the JDBC drivers. One was called standard which followed the JBDC 2.0 specification and the other flavor was an Oracle specific implementation (called Oracle Batching) which is independent of JDBC 2.0. It is not possible to mix both these modes in the same application. However with the latest implementation of the JDBC driver (12c) Oracle update batching is depreciated. Evolution of the documentation over the years on this is given below.
From 10.2 JDBC developers guide Oracle update batching is a more efficient model because the driver knows ahead of time how many operations will be batched. In this sense, the Oracle model is more static and predictable. With the standard model, the driver has no way of knowing in advance how many operations will be batched. In this sense, the standard model is more dynamic in nature.
From 11.2 JDBC developers guide Oracle recommends that you use JDBC standard features when possible. This recommendation applies to update batching as well. Oracle update batching is retained primarily for backwards compatibility.
From 12.1 JDBC developers guide Starting from Oracle Database 12c Release 1 (12.1), Oracle update batching is deprecated. Oracle recommends that you use standard JDBC batching instead of Oracle update batching.
As the Oracle mode batching is depreciated the comparison only holds for lower versions of the JDBC drivers. The test related to this post was carried out using 11.2 JDBC driver. The java code used for the test is given at the end of the post. Two tests were carried out. First involving updating number of rows in a table with standard, standard implementation of oracle batching and oracle batching. Standard batching test only execute the update batch once. The standard implementation mimics what the Oracle standard does by executing the update batch at fixed intervals (500 and 1000 updates at a time). Finally Oracle batching will use the values of 500 and 1000 for batch values. This test is to check if there's any considerable difference in the timing of the updates. Result table is given below.

# Updates	Standared Batching	Standared Batching - 500	Standared Batching - 1000	Oracle Batching - 500	Oracle Batchnig - 1000
100	0.35	0.35	0.36	0.36	0.36
200	0.62	0.62	0.65	0.63	0.63
500	1.43	1.41	1.47	1.43	1.45
1000	2.76	2.73	2.88	2.81	2.81
2000	5.72	5.38	5.64	5.54	5.68
5000	13.55	13.43	13.99	13.67	13.79
10000	27.82	26.58	28.05	27.31	27.58
25000	80.01	75.43	78.65	76.42	76.93
50000	165.24	158.99	164.32	159.13	159.04
75000	243.25	242.92	248.73	242.17	242.79
100000	330.92	330.84	333.34	327.65	331.22

There's not much difference in terms of time it takes to complete all the updates whether it is via standard batching or oracle batching. Scatter plot shown below.

The next test involved updating a column with a blob (size 8KB). The results table is given below.

# Updates	Standared Batching	Standared Batching - 500	Standared Batching - 1000	Oracle Batching - 500	Oracle Batchnig - 1000
100	1.47	1.53	1.50	1.68	1.76
200	2.79	2.96	2.91	3.14	3.19
500	6.88	7.37	7.14	7.68	7.87
1000	13.60	14.70	14.20	15.30	15.50
2000	27.31	29.25	30.13	30.07	30.41
5000	68.30	74.10	74.89	77.23	74.82
10000	143.31	151.05	143.14	151.68	152.43
25000	348.83	351.34	365.54	384.77	394.99
50000	701.67	727.28	707.79	857.79	859.15

There's not much difference in the update time between the different standard batching tests.

However there's some advantage when using standard batching compared to oracle standard as the number of updates are 25,000 or over. But the increase number of batching size requires more memory and this situation may be uncommon. For lower values again there's not much difference in terms of time saved.

These tests have shown that batching mode (Oracle or standard) does not have a real influence on the time taken for the updates. As mentioned earlier as of 12c the Oracle standard batching is depreciated and if these results hold true for 12c driver as well then there shouldn't be any performance degradation.

Test java code used

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;
import oracle.jdbc.pool.OracleDataSource;

/**
 *
 * @author Asanga
 */
public class Update {
    
    public static void main(String[] args) {
        try {
            OracleDataSource dataSource = new OracleDataSource();
               dataSource.setURL("jdbc:oracle:thin:@192.168.0.66:1521:ent11g2");
               dataSource.setUser("asanga");
               dataSource.setPassword("asa");               
               
//               String SQL = "update x set b = ?, c = ? where a = ?"; // update blob
               String SQL = "update x set b = ? where a = ?";
               
               Connection con = dataSource.getConnection();
               con.setAutoCommit(false);
               
               long t1 = System.nanoTime();
               
//               byte[] lob = new byte[8*1024];
//               lob[10] =10;
//               lob[8000]=20;
               
               PreparedStatement pr = con.prepareStatement(SQL);
//               ((OraclePreparedStatement)pr).setExecuteBatch(1000);
               for(int i =1; i <= 75000; i++){
                   
                   StringBuilder b = new StringBuilder("khf").append(i);
                   
                   pr.setString(1, b.toString());
//                   pr.setBytes(2,lob );
                   pr.setInt(2, i);
                   
                   pr.addBatch();
                   
                   if((i > 0) && (i%1000 == 0)){
                       int[] ret =pr.executeBatch();
                       System.out.println(ret.length);
                   }
//                   pr.executeUpdate();
               }
               
               int[] ret = pr.executeBatch();
//               ((OraclePreparedStatement)pr).sendBatch();
               con.commit();
               long t2 = System.nanoTime();
               
               System.out.println("returned "+ret.length+" time : "+(t2-t1));
//               System.out.println("time : "+(t2-t1));
               
               pr.close();
               con.close();
               
        } catch (SQLException ex) {
            Logger.getLogger(Update.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
}

Session Cached Cursors and JDBC CallableStatement

Similar to earlier post that compared the various preparedstatments scenarios this post examine the use of callablestatements and the effects of session cached cursors (SCC) parameter. Case 1 and Case 2 are same as with the preparedstatements.

Case 1	Vs		Case 2
Create CallableStatement For Loop bind values execute End Loop Close CallableStatement			For Loop Create CallableStatement bind values execute Close CallableStatement End Loop

However the execution of client side code via JDBC results in two calls, one from the JDBC calling the PL/SQL and other the SQL within the PL/SQL. First up is the case 1 with SCC=50 where the callablestatment is executed 10000 times. Output format is same as earlier.The java code and the pl/sql scripts used for the test is given at the end of the post.

start got connection
1 select rownum,sql_text,cursor_type,USER_NAME,sid from v$open   OPEN                             ASANGA 19
2 select /*+ connect_by_filtering */ privilege#,level from sys   SESSION CURSOR CACHED            SYS    19
3 insert into sys.aud$( sessionid,entryid,statement,ntimestamp   OPEN-RECURSIVE                   ASANGA 19
4 select value$ from props$ where name = 'GLOBAL_DB_NAME'        DICTIONARY LOOKUP CURSOR CACHED  SYS    19
5 select SYS_CONTEXT('USERENV', 'SERVER_HOST'), SYS_CONTEXT('U   DICTIONARY LOOKUP CURSOR CACHED  SYS    19
6 select decode(failover_method, NULL, 0 , 'BASIC', 1, 'PRECON   DICTIONARY LOOKUP CURSOR CACHED  SYS    19
7 select privilege# from sysauth$ where (grantee#=:1 or grante   DICTIONARY LOOKUP CURSOR CACHED  SYS    19

Parse_Total Hard_Parse Ses_Cur_Cache_Hit Ses_Cur_Cache_Count CPU
        9         0         3                 7               3

end got connection

start execute
1 SELECT B FROM X WHERE A = :B1                                 PL/SQL CURSOR CACHED              ASANGA 19
2 begin :1  := cursorfunc(:2 ); end;                            DICTIONARY LOOKUP CURSOR CACHED   ASANGA 19
3 select rownum,sql_text,cursor_type,USER_NAME,sid from v$open  DICTIONARY LOOKUP CURSOR CACHED   ASANGA 19
4 select * from (SELECT name,  value FROM v$sesstat,  v$statna  DICTIONARY LOOKUP CURSOR CACHED   ASANGA 19
5 select /*+ connect_by_filtering */ privilege#,level from sys  SESSION CURSOR CACHED             SYS    19
6 insert into sys.aud$( sessionid,entryid,statement,ntimestamp  OPEN-RECURSIVE                    ASANGA 19
7 select value$ from props$ where name = 'GLOBAL_DB_NAME'       DICTIONARY LOOKUP CURSOR CACHED   SYS    19
8 select SYS_CONTEXT('USERENV', 'SERVER_HOST'), SYS_CONTEXT('U  DICTIONARY LOOKUP CURSOR CACHED   SYS    19
9 select decode(failover_method, NULL, 0 , 'BASIC', 1, 'PRECON  DICTIONARY LOOKUP CURSOR CACHED   SYS    19
10 select privilege# from sysauth$ where (grantee#=:1 or grante DICTIONARY LOOKUP CURSOR CACHED   SYS    19

Parse_Total Hard_Parse Ses_Cur_Cache_Hit Ses_Cur_Cache_Count CPU
        11         0         10004                 10        587
end execute

At the time of the connection get the connection (session) has done 9 parses in total and already had 3 session cache hits and 7 cursors cached and spent 3 centi-seconds of CPU. After the execute of the callablestatement the output shows that two additional (soft) parses occurred and 10001 session cache hits. Since the callablestatement is never closed as such it's never cached and the 10001 cache hits occurred due to the SQL called inside the PL/SQL function (1 in second output above). when the PL/SQL called for the first time the SQL inside it will be missing from the session cache. At the completion of the PL/SQL this cursor is implicitly closed (even though java side callablestatement is never closed) resulting it being added to the session cache and being found on subsequent executions from the same connection. This result in 9999 session cache hits. The other two are statistics gathering SQLs that were executed second time around.
Tkprof formatted output shows the following for this case.

begin :1  := cursorfunc(:2 ); end;


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute  10000      2.89       3.23          0          6          0       10000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    10001      2.89       3.23          0          6          0       10000

SELECT B FROM X WHERE A = :B1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute  10000      0.28       0.39          0          0          0           0
Fetch    10000      0.66       0.75          0      60000          0       10000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    20001      0.95       1.14          0      60000          0       10000

Both SQL and callablestatement were parsed once and executed multiple times. The CPU usage for this case is 584 centi-seconds.
Next is the case 2 with SCC=50, output given below.

start got connection

1 select rownum,sql_text,cursor_type,USER_NAME,sid from v$open  OPEN                             ASANGA  19
2 select /*+ connect_by_filtering */ privilege#,level from sys  SESSION CURSOR CACHED            SYS     19
3 insert into sys.aud$( sessionid,entryid,statement,ntimestamp  OPEN-RECURSIVE                   ASANGA  19
4 select value$ from props$ where name = 'GLOBAL_DB_NAME'       DICTIONARY LOOKUP CURSOR CACHED  SYS     19
5 select SYS_CONTEXT('USERENV', 'SERVER_HOST'), SYS_CONTEXT('U  DICTIONARY LOOKUP CURSOR CACHED  SYS     19
6 select decode(failover_method, NULL, 0 , 'BASIC', 1, 'PRECON  DICTIONARY LOOKUP CURSOR CACHED  SYS     19
7 select privilege# from sysauth$ where (grantee#=:1 or grante  DICTIONARY LOOKUP CURSOR CACHED  SYS     19

Parse_Total Hard_Parse Ses_Cur_Cache_Hit Ses_Cur_Cache_Count CPU
        9         0         3                 7               2

end got connection

start execute
1 SELECT B FROM X WHERE A = :B1                                 PL/SQL CURSOR CACHED             ASANGA  19
2 begin :1  := cursorfunc(:2 ); end;                            SESSION CURSOR CACHED            ASANGA  19
3 select rownum,sql_text,cursor_type,USER_NAME,sid from v$open  DICTIONARY LOOKUP CURSOR CACHED  ASANGA  19
4 select * from (SELECT name,  value FROM v$sesstat,  v$statna  DICTIONARY LOOKUP CURSOR CACHED  ASANGA  19
5 select /*+ connect_by_filtering */ privilege#,level from sys  SESSION CURSOR CACHED            SYS     19
6 insert into sys.aud$( sessionid,entryid,statement,ntimestamp  OPEN-RECURSIVE                   ASANGA  19
7 select value$ from props$ where name = 'GLOBAL_DB_NAME'       DICTIONARY LOOKUP CURSOR CACHED  SYS     19
8 select SYS_CONTEXT('USERENV', 'SERVER_HOST'), SYS_CONTEXT('U  DICTIONARY LOOKUP CURSOR CACHED  SYS     19
9 select decode(failover_method, NULL, 0 , 'BASIC', 1, 'PRECON  DICTIONARY LOOKUP CURSOR CACHED  SYS     19
10 select privilege# from sysauth$ where (grantee#=:1 or grante DICTIONARY LOOKUP CURSOR CACHED  SYS     19

Parse_Total Hard_Parse Ses_Cur_Cache_Hit Ses_Cur_Cache_Count CPU
        11         0         20003                 10        721

end execute

The case 2 test result in two additional soft parses after the callablestatement is executed. But the total session cache hit is 20000. Since the callable statement is repeatedly created with each iteration this results in 9999 session cache hits plus the 9999 from the SQL inside the PL/SQL function which is 19998 together with the two cache hits for the statistics gathering sql makes it 20000 session cache hits. Total CPU used is 719 centi-seconds, higher compared to case 1. Tkprof formatted output shows the following, trace file incorrectly list 10000 parses for callablestatement (same as in the case of preparedstatements)

begin :1  := cursorfunc(:2 ); end;

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse    10000      0.23       0.30          0          0          0           0
Execute  10000      3.08       3.15          0          6          0       10000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    20000      3.31       3.45          0          6          0       10000

SELECT B FROM X WHERE A = :B1

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute  10000      0.38       0.39          0          0          0           0
Fetch    10000      0.63       0.72          0      60000          0       10000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    20001      1.02       1.12          0      60000          0       10000

In the second phase the same two test cases carried out but with session cached cursors parameter set to 0. Below is the output from case 1 with SCC=0

start got connection

1 select rownum,sql_text,cursor_type,USER_NAME,sid from v$open OPEN           ASANGA 151
2 insert into sys.aud$( sessionid,entryid,statement,ntimestamp OPEN-RECURSIVE ASANGA 151

Parse_Total Hard_Parse Ses_Cur_Cache_Hit Ses_Cur_Cache_Count CPU
        12         0         0                 0               6

end got connection

start execute
1 select rownum,sql_text,cursor_type,USER_NAME,sid from v$open OPEN           ASANGA 151
2 insert into sys.aud$( sessionid,entryid,statement,ntimestamp OPEN-RECURSIVE ASANGA 151

Parse_Total Hard_Parse Ses_Cur_Cache_Hit Ses_Cur_Cache_Count CPU
        10015         0         0                 0          717
end execute

As there's no session cached cursors the execution of callablestatement resulted in 10003 soft parses. This count is made up of 10000 soft parses on the SQL inside the PL/SQL, one soft parse on the callablestatement and the two statistics gathering statements. In this case repeated use of the open callablestatement does not result in additional parses. The tkprof output confirm is given below

begin :1  := cursorfunc(:2 ); end;


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute  10000      4.16       4.32          0          6          0       10000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    10001      4.16       4.32          0          6          0       10000

SELECT B FROM X WHERE A = :B1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse    10000      0.24       0.31          0          0          0           0
Execute  10000      0.32       0.46          0          0          0           0
Fetch    10000      0.59       0.73          0      60000          0       10000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    30000      1.17       1.50          0      60000          0       10000

Output of the case 2 is given below.

start got connection

1 select rownum,sql_text,cursor_type,USER_NAME,sid from v$open OPEN           ASANGA 139
2 insert into sys.aud$( sessionid,entryid,statement,ntimestamp OPEN-RECURSIVE ASANGA 139

Parse_Total Hard_Parse Ses_Cur_Cache_Hit Ses_Cur_Cache_Count CPU
        12         0         0                 0               5

end got connection

start execute
1 select rownum,sql_text,cursor_type,USER_NAME,sid from v$open OPEN           ASANGA 139
2 insert into sys.aud$( sessionid,entryid,statement,ntimestamp OPEN-RECURSIVE ASANGA 139

Parse_Total Hard_Parse Ses_Cur_Cache_Hit Ses_Cur_Cache_Count CPU
        20014         0         0                 0          945

end execute

Since callablestatement is closed after each iteration, this results in soft parses each time its called. Therefore in this case the total number of soft parses are 20002. This count is made up of 10000 soft parses for callablestatement, 10000 for the SQL inside PL/SQL and the two statistics gathering SQLs. The tkprof output also list the soft parses

begin :1  := cursorfunc(:2 ); end;


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse    10000      0.37       0.41          0          0          0           0
Execute  10000      4.45       4.48          0          6          0       10000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    20000      4.83       4.90          0          6          0       10000

SELECT B FROM X WHERE A = :B1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse    10000      0.30       0.32          0          0          0           0
Execute  10000      0.40       0.50          0          0          0           0
Fetch    10000      0.69       0.75          0      60000          0       10000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    30000      1.40       1.58          0      60000          0       10000

As with the preparedstatment, it is possible to reduce some of the soft parses when SCC=0 by enabling cursor caching on the connection pool or connection. The output of these tests are not shown but the parse count and CPU is included in the summary table.

Test	Parse count	CPU (centi-seconds)
SCC=50,Case=1,MaxStm=0	2	584
SCC=50,Case=2,MaxStm=0	2	719
SCC=0,Case=1,MaxStm=0	10003	711
SCC=0,Case=2,MaxStm=0	20002	940
SCC=0,Case=1,MaxStm=10	10001	724
SCC=0,Case=2,MaxStm=10	10001	731

CPU usage graph for each case.

These tests have shown setting SCC is helpful in reducing the parse count and the CPU usage associated with parsing. When SCC is not set or cannot be set, similar behavior could be achieved with by setting cursor caching on connection pool.

Related Post
Session Cached Cursors and JDBC PreparedStatement

Database test case Code

create table x (a number, b varchar2(10));

begin
  for i in 1 .. 10
  loop
  insert into x values (i, 'abc'||i);
  end loop;
end;
/

create or replace function cursorfunc(inval in number) 
return varchar2 as
  retval varchar2(10);
begin
  select b into retval from x where a = inval;
  return retval;
end;
/

Java Code

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;

import oracle.ucp.jdbc.PoolDataSource;
import oracle.ucp.jdbc.PoolDataSourceFactory;

/**
 *
 * @author Asanga
 */
public class PLSQLCursorTest {

    static String SQL1 = "begin ? := cursorfunc(?); end;";

    static String SQL2 = "select * from (SELECT name,  value FROM v$sesstat,  v$statname WHERE v$sesstat.statistic#=v$statname.statistic# "
            + "AND (v$statname.name in ('parse count (total)','parse count (hard)','session cursor cache hits','session cursor cache count','CPU used by this session'))"
            + " AND v$sesstat.sid= sys_context('USERENV','SID')"
            + " )pivot (sum(value) for name in ('parse count (total)' as Parse_Total,'parse count (hard)' Hard_Parse,'session cursor cache hits' as SES_CUR_CACHE_HITS,'session cursor cache count' as SES_CUR_CACHE_COUNT, 'CPU used by this session' as CPU))";
    static String SQL3 = "select rownum,sql_text,cursor_type,USER_NAME,sid from v$open_cursor where sid=sys_context('USERENV','SID')";


    public static void main(String[] args) throws Exception {
        try {

            PoolDataSource ds = PoolDataSourceFactory.getPoolDataSource();
            ds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
            ds.setConnectionPoolName("TTPool");
            ds.setURL("jdbc:oracle:thin:@192.168.0.85:1521/dbsrv1");
            ds.setUser("asanga");
            ds.setPassword("asa");
//            ds.setMaxStatements(10);

            Connection con = ds.getConnection();
            CallableStatement pr = null;

            System.out.println("start got connection");
            stat(con);
            System.out.println("end got connection");

            System.out.println("start execute");

/****************************** Start CASE 1 ************************************/
//            pr = con.prepareCall(SQL1);
//            pr.registerOutParameter(1, java.sql.Types.VARCHAR);
//            for (int i = 0; i < 10000; i++) {
//                pr.setInt(2,(i%10)+1 );
//                pr.execute();
//                pr.getString(1);
//            }
//
//            pr.close();
/****************************** End CASE 1 ************************************/

/****************************** Start CASE 2 ************************************/
            for (int i = 0; i < 10000; i++) {
                pr = con.prepareCall(SQL1);
                pr.registerOutParameter(1, java.sql.Types.VARCHAR);
                pr.setInt(2, (i%10)+1);
                pr.execute();
                pr.getString(1);
                pr.close();
            }
            pr.close();
/****************************** End CASE 2 ************************************/

            stat(con);
            System.out.println("end execute");
            con.close();

        } catch (SQLException ex) {
            Logger.getLogger(PLSQLCursorTest.class.getName()).log(Level.SEVERE, null, ex);
        }


    }

    public static void stat(Connection con) throws SQLException {

        PreparedStatement pr = null;
        ResultSet rs = null;

        pr = con.prepareStatement(SQL3);
        rs = pr.executeQuery();
        while (rs.next()) {
            System.out.println(rs.getString(1) + " " + rs.getString(2) + " " + rs.getString(3) + " " + rs.getString(4) + " " + rs.getString(5));
        }

        rs.close();
        pr.close();

        System.out.println("Parse_Total Hard_Parse Ses_Cur_Cache_Hit Ses_Cur_Cache_Count CPU");
        pr = con.prepareStatement(SQL2);
        rs = pr.executeQuery();

        while (rs.next()) {

            System.out.println("\t" + rs.getString(1) + "\t " + rs.getInt(2) + "\t " + rs.getString(3) + "\t\t " + rs.getInt(4)+ "\t       " + rs.getInt(5));
        }

        rs.close();
        pr.close();

    }
}

Session Cached Cursors and JDBC PreparedStatement