Oracle Database Discussions

Hi experts,
We are running into a problem in which the oracle process quickly goes up to 100% CPU Usage thus slowing the overall DB host and increase response time by a lot. The problem only happens when SSL is configured.

We are using Oracle 10g 10.2.0.1 with CPU Patch Jan 2007 running on Windows Server 2003. Our application uses 10.2.0.1 JDBC thin driver to send SQL queries to the Oracle DB. We have found out that by having our application send one "select" query to retrieve a BLOB from DB makes Oracle.exe process spin up to 100%. Then, even after the end of execution of the SQL query; CPU will remain at 100%. Thus, the more parrallel SQL queries [to fetch BLOB from DB]; the slower will be the DB host and the longer the response time. An interesting observation is that we have not see the Oracle CPU spin when performing "insert" or "update" on BLOB or "select" queries on fields OTHER than BLOB. Afterwards, the only way to bring CPU usage of Oracle.exe back to 0% is to either terminate our application [which terminates the connection between our app and Oracle], or to restart our database. We have not seen any signs of handle or memory leak from either our application or Oracle process.

This behaviour is not observed when SSL is turned off. CPU usage for NON-SSL connection is normal and when the queries end, CPU usage will go back to 0%.

We are not sure if
1. This is a known issue with JDBC SSL and Oracle. If so, any patches?
2. Our configuration of SSL for Oracle is incorrect [see below for SSL config]
3. Our application needs to change to accomodate Oracle SSL.

sqlnet.ora
# sqlnet.ora Network Configuration File: C:\oracle\product\10.2.0\db_2\NETWORK\ADMIN\sqlnet.ora
# Generated by Oracle configuration tools.

# This file is actually generated by netca. But if customers choose to
# install "Software Only", this file wont exist and without the native
# authentication, they will not be able to connect to the database on NT.

SQLNET.AUTHENTICATION_SERVICES= (BEQ, TCPS, NTS)

SSL_VERSION = 0

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

SSL_CLIENT_AUTHENTICATION = TRUE

WALLET_LOCATION =
(SOURCE =
(METHOD = FILE)
(METHOD_DATA =
(DIRECTORY = e:\wallet)
)
)

SSL_CIPHER_SUITES= (SSL_RSA_WITH_AES_256_CBC_SHA)

listener.ora
# listener.ora Network Configuration File: C:\oracle\product\10.2.0\db_2\NETWORK\ADMIN\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = MYDB)
(ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
(SID_NAME = MYDB)
)
(SID_DESC =
(GLOBAL_DBNAME = Oracle8)
(SID_NAME = ORCL)
)
)

SSL_CLIENT_AUTHENTICATION = TRUE

WALLET_LOCATION =
(SOURCE =
(METHOD = FILE)
(METHOD_DATA =
(DIRECTORY = e:\wallet)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCPS)(HOST = MYHOST)(PORT = 2484))
)
)

tnsnames.ora# tnsnames.ora Network Configuration File: C:\oracle\product\10.2.0\db_2\NETWORK\ADMIN\tnsnames.ora
# Generated by Oracle configuration tools.

MYDB=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCPS)(HOST = MYHOST)(PORT = 2484))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = mydb)
)
)