| 2014-03-26 09:16:15 |
siva |
description |
I have connected Oracle db with Application. For each and every run, no.of inactive sessions are increased and that means the old sessions are not reused and new three connection s are created for every insert/update operation. Only after shut downing Application server everything reset and comes to normal.
If it continues, whether it raise any problem?. Any idea for why this happened and Anyway to rectify the issue?
This is basic config for JDBC connection for Oracle
Class.forName(driverClass);
BoneCPConfig config = new BoneCPConfig();
config.setJdbcUrl("jdbc:oracle:thin:@localhost:1521:orcl");
config.setUsername(username);
config.setPassword(password);
config.setPartitionCount(1);
config.setMinConnectionsPerPartition(2);
config.setMaxConnectionsPerPartition(4);
config.setAcquireIncrement(3);
config.setLazyInit(true);
config.setDefaultAutoCommit(true);
config.setLogStatementsEnabled(true);
poolManager = new BoneCP(config);
Query
select * from v$session where username='ORACLE_DB';
"SADDR" "SID" "SERIAL#" "AUDSID" "PADDR" "USER#" "USERNAME" "COMMAND" "OWNERID" "TADDR" "LOCKWAIT" "STATUS" "SERVER" "SCHEMA#" "SCHEMANAME" "OSUSER" "PROCESS" "MACHINE" "PORT" "TERMINAL" "PROGRAM" "TYPE" "SQL_ADDRESS" "SQL_HASH_VALUE" "SQL_ID" "SQL_CHILD_NUMBER" "SQL_EXEC_START" "SQL_EXEC_ID" "PREV_SQL_ADDR" "PREV_HASH_VALUE" "PREV_SQL_ID" "PREV_CHILD_NUMBER" "PREV_EXEC_START" "PREV_EXEC_ID" "PLSQL_ENTRY_OBJECT_ID" "PLSQL_ENTRY_SUBPROGRAM_ID" "PLSQL_OBJECT_ID" "PLSQL_SUBPROGRAM_ID" "MODULE" "MODULE_HASH" "ACTION" "ACTION_HASH" "CLIENT_INFO" "FIXED_TABLE_SEQUENCE" "ROW_WAIT_OBJ#" "ROW_WAIT_FILE#" "ROW_WAIT_BLOCK#" "ROW_WAIT_ROW#" "TOP_LEVEL_CALL#" "LOGON_TIME" "LAST_CALL_ET" "PDML_ENABLED" "FAILOVER_TYPE" "FAILOVER_METHOD" "FAILED_OVER" "RESOURCE_CONSUMER_GROUP" "PDML_STATUS" "PDDL_STATUS" "PQ_STATUS" "CURRENT_QUEUE_DURATION" "CLIENT_IDENTIFIER" "BLOCKING_SESSION_STATUS" "BLOCKING_INSTANCE" "BLOCKING_SESSION" "FINAL_BLOCKING_SESSION_STATUS" "FINAL_BLOCKING_INSTANCE" "FINAL_BLOCKING_SESSION" "SEQ#" "EVENT#" "EVENT" "P1TEXT" "P1" "P1RAW" "P2TEXT" "P2" "P2RAW" "P3TEXT" "P3" "P3RAW" "WAIT_CLASS_ID" "WAIT_CLASS#" "WAIT_CLASS" "WAIT_TIME" "SECONDS_IN_WAIT" "STATE" "WAIT_TIME_MICRO" "TIME_REMAINING_MICRO" "TIME_SINCE_LAST_WAIT_MICRO" "SERVICE_NAME" "SQL_TRACE" "SQL_TRACE_WAITS" "SQL_TRACE_BINDS" "SQL_TRACE_PLAN_STATS" "SESSION_EDITION_ID" "CREATOR_ADDR" "CREATOR_SERIAL#" "ECID"
000007FF657E9E78 32 17519 8268263 000007FF654EC488 574 "ORACLE_DB" 0 2147483644 "" "" "INACTIVE" "DEDICATED" 574 "ORACLE_DB" "Administrator" "1234" "TESTPC-PC" 52779 "unknown" "JDBC Thin Client" "USER" 00 0 "" "" "" "" 000007FF4B548518 1374653582 "cs6jdgt8yz34f" 0 24-03-14 16789303 "" "" "" "" "JDBC Thin Client" 2546894660 "" 0 "" 5403085 -1 0 0 0 5 24-03-14 3739 "NO" "NONE" "NONE" "NO" "" "DISABLED" "ENABLED" "ENABLED" 0 "" "NO HOLDER" "" "" "NO HOLDER" "" "" 2045 345 "SQL*Net message from client" "driver id" 1952673792 0000000074637000 "#bytes" 1 0000000000000001 "" 0 00 2723168908 6 "Idle" 0 3739 "WAITING" 3738938101 -1 0 "SYS$USERS" "DISABLED" "FALSE" "FALSE" "FIRST EXEC" 100 000007FF654EC488 233 ""
000007FF657E7008 33 25842 8264735 000007FF654E61E8 574 "ORACLE_DB" 0 2147483644 "" "" "INACTIVE" "DEDICATED" 574 "ORACLE_DB" "Administrator" "1316" "WIN-E2J14SLOCDF" 60229 "unknown" "SQL Developer" "USER" 00 0 "" "" "" "" 000007FF6453EDE8 2969983240 "cd7vnh2shcq88" 0 24-03-14 16777239 "" "" "" "" "SQL Developer" 1012150930 "" 0 "" 5400289 -1 0 0 0 94 24-03-14 5553 "NO" "NONE" "NONE" "NO" "" "DISABLED" "ENABLED" "ENABLED" 0 "" "NO HOLDER" "" "" "NO HOLDER" "" "" 169 345 "SQL*Net message from client" "driver id" 1952673792 0000000074637000 "#bytes" 1 0000000000000001 "" 0 00 2723168908 6 "Idle" 0 5553 "WAITING" 5552781156 -1 0 "SYS$USERS" "DISABLED" "FALSE" "FALSE" "FIRST EXEC" 100 000007FF654E61E8 197 ""
000007FF657E4198 34 36782 8268131 000007FF654E3098 574 "ORACLE_DB" 0 2147483644 "" "" "INACTIVE" "DEDICATED" 574 "ORACLE_DB" "Administrator" "1234" "TESTPC-PC" 52675 "unknown" "JDBC Thin Client" "USER" 00 0 "" 0 "" "" 00 0 "" 0 "" "" "" "" "" "" "JDBC Thin Client" 2546894660 "" 0 "" 5402600 -1 0 0 0 115 24-03-14 4354 "NO" "NONE" "NONE" "NO" "" "DISABLED" "ENABLED" "ENABLED" 0 "" "NO HOLDER" "" "" "NO HOLDER" "" "" 6 345 "SQL*Net message from client" "driver id" 1952673792 0000000074637000 "#bytes" 1 0000000000000001 "" 0 00 2723168908 6 "Idle" 0 4353 "WAITING" 4353237602 -1 0 "SYS$USERS" "DISABLED" "FALSE" "FALSE" "FIRST EXEC" 100 000007FF654E3098 77 ""
000007FF657CFC88 41 44470 8268198 000007FF654EA3A8 574 "ORACLE_DB" 0 2147483644 "" "" "INACTIVE" "DEDICATED" 574 "ORACLE_DB" "Administrator" "1234" "TESTPC-PC" 52708 "unknown" "JDBC Thin Client" "USER" 00 0 "" "" "" "" 000007FF4B548518 1374653582 "cs6jdgt8yz34f" 0 24-03-14 16788295 "" "" "" "" "JDBC Thin Client" 2546894660 "" 0 "" 5402793 -1 0 0 0 5 24-03-14 4050 "NO" "NONE" "NONE" "NO" "" "DISABLED" "ENABLED" "ENABLED" 0 "" "NO HOLDER" "" "" "NO HOLDER" "" "" 2023 345 "SQL*Net message from client" "driver id" 1952673792 0000000074637000 "#bytes" 1 0000000000000001 "" 0 00 2723168908 6 "Idle" 0 4049 "WAITING" 4049292378 -1 0 "SYS$USERS" "DISABLED" "FALSE" "FALSE" "FIRST EXEC" 100 000007FF654EA3A8 76 "" |
I have connected Oracle db with Application. For each and every run, no.of inactive sessions are increased and that means the old sessions are not reused and new three connection s are created for every insert/update operation. Only after shut downing Application server everything reset and comes to normal.
If it continues, whether it raise any problem?. Any idea for why this happened and Anyway to rectify the issue?
This is basic config for JDBC connection for Oracle
-----------------------------------------------------
Class.forName(driverClass);
BoneCPConfig config = new BoneCPConfig();
config.setJdbcUrl("jdbc:oracle:thin:@localhost:1521:orcl");
config.setUsername(username);
config.setPassword(password);
config.setPartitionCount(1);
config.setMinConnectionsPerPartition(2);
config.setMaxConnectionsPerPartition(4);
config.setAcquireIncrement(3);
config.setLazyInit(true);
config.setDefaultAutoCommit(true);
config.setLogStatementsEnabled(true);
poolManager = new BoneCP(config);
Query
-------
select * from v$session where username='ORACLE_DB';
Results
-------
"SADDR" "SID" "SERIAL#" "AUDSID" "PADDR" "USER#" "USERNAME" "COMMAND" "OWNERID" "TADDR" "LOCKWAIT" "STATUS" "SERVER" "SCHEMA#" "SCHEMANAME" "OSUSER" "PROCESS" "MACHINE" "PORT" "TERMINAL" "PROGRAM" "TYPE" "SQL_ADDRESS" "SQL_HASH_VALUE" "SQL_ID" "SQL_CHILD_NUMBER" "SQL_EXEC_START" "SQL_EXEC_ID" "PREV_SQL_ADDR" "PREV_HASH_VALUE" "PREV_SQL_ID" "PREV_CHILD_NUMBER" "PREV_EXEC_START" "PREV_EXEC_ID" "PLSQL_ENTRY_OBJECT_ID" "PLSQL_ENTRY_SUBPROGRAM_ID" "PLSQL_OBJECT_ID" "PLSQL_SUBPROGRAM_ID" "MODULE" "MODULE_HASH" "ACTION" "ACTION_HASH" "CLIENT_INFO" "FIXED_TABLE_SEQUENCE" "ROW_WAIT_OBJ#" "ROW_WAIT_FILE#" "ROW_WAIT_BLOCK#" "ROW_WAIT_ROW#" "TOP_LEVEL_CALL#" "LOGON_TIME" "LAST_CALL_ET" "PDML_ENABLED" "FAILOVER_TYPE" "FAILOVER_METHOD" "FAILED_OVER" "RESOURCE_CONSUMER_GROUP" "PDML_STATUS" "PDDL_STATUS" "PQ_STATUS" "CURRENT_QUEUE_DURATION" "CLIENT_IDENTIFIER" "BLOCKING_SESSION_STATUS" "BLOCKING_INSTANCE" "BLOCKING_SESSION" "FINAL_BLOCKING_SESSION_STATUS" "FINAL_BLOCKING_INSTANCE" "FINAL_BLOCKING_SESSION" "SEQ#" "EVENT#" "EVENT" "P1TEXT" "P1" "P1RAW" "P2TEXT" "P2" "P2RAW" "P3TEXT" "P3" "P3RAW" "WAIT_CLASS_ID" "WAIT_CLASS#" "WAIT_CLASS" "WAIT_TIME" "SECONDS_IN_WAIT" "STATE" "WAIT_TIME_MICRO" "TIME_REMAINING_MICRO" "TIME_SINCE_LAST_WAIT_MICRO" "SERVICE_NAME" "SQL_TRACE" "SQL_TRACE_WAITS" "SQL_TRACE_BINDS" "SQL_TRACE_PLAN_STATS" "SESSION_EDITION_ID" "CREATOR_ADDR" "CREATOR_SERIAL#" "ECID"
000007FF657E9E78 32 17519 8268263 000007FF654EC488 574 "ORACLE_DB" 0 2147483644 "" "" "INACTIVE" "DEDICATED" 574 "ORACLE_DB" "Administrator" "1234" "TESTPC-PC" 52779 "unknown" "JDBC Thin Client" "USER" 00 0 "" "" "" "" 000007FF4B548518 1374653582 "cs6jdgt8yz34f" 0 24-03-14 16789303 "" "" "" "" "JDBC Thin Client" 2546894660 "" 0 "" 5403085 -1 0 0 0 5 24-03-14 3739 "NO" "NONE" "NONE" "NO" "" "DISABLED" "ENABLED" "ENABLED" 0 "" "NO HOLDER" "" "" "NO HOLDER" "" "" 2045 345 "SQL*Net message from client" "driver id" 1952673792 0000000074637000 "#bytes" 1 0000000000000001 "" 0 00 2723168908 6 "Idle" 0 3739 "WAITING" 3738938101 -1 0 "SYS$USERS" "DISABLED" "FALSE" "FALSE" "FIRST EXEC" 100 000007FF654EC488 233 ""
000007FF657E7008 33 25842 8264735 000007FF654E61E8 574 "ORACLE_DB" 0 2147483644 "" "" "INACTIVE" "DEDICATED" 574 "ORACLE_DB" "Administrator" "1316" "WIN-E2J14SLOCDF" 60229 "unknown" "SQL Developer" "USER" 00 0 "" "" "" "" 000007FF6453EDE8 2969983240 "cd7vnh2shcq88" 0 24-03-14 16777239 "" "" "" "" "SQL Developer" 1012150930 "" 0 "" 5400289 -1 0 0 0 94 24-03-14 5553 "NO" "NONE" "NONE" "NO" "" "DISABLED" "ENABLED" "ENABLED" 0 "" "NO HOLDER" "" "" "NO HOLDER" "" "" 169 345 "SQL*Net message from client" "driver id" 1952673792 0000000074637000 "#bytes" 1 0000000000000001 "" 0 00 2723168908 6 "Idle" 0 5553 "WAITING" 5552781156 -1 0 "SYS$USERS" "DISABLED" "FALSE" "FALSE" "FIRST EXEC" 100 000007FF654E61E8 197 ""
000007FF657E4198 34 36782 8268131 000007FF654E3098 574 "ORACLE_DB" 0 2147483644 "" "" "INACTIVE" "DEDICATED" 574 "ORACLE_DB" "Administrator" "1234" "TESTPC-PC" 52675 "unknown" "JDBC Thin Client" "USER" 00 0 "" 0 "" "" 00 0 "" 0 "" "" "" "" "" "" "JDBC Thin Client" 2546894660 "" 0 "" 5402600 -1 0 0 0 115 24-03-14 4354 "NO" "NONE" "NONE" "NO" "" "DISABLED" "ENABLED" "ENABLED" 0 "" "NO HOLDER" "" "" "NO HOLDER" "" "" 6 345 "SQL*Net message from client" "driver id" 1952673792 0000000074637000 "#bytes" 1 0000000000000001 "" 0 00 2723168908 6 "Idle" 0 4353 "WAITING" 4353237602 -1 0 "SYS$USERS" "DISABLED" "FALSE" "FALSE" "FIRST EXEC" 100 000007FF654E3098 77 ""
000007FF657CFC88 41 44470 8268198 000007FF654EA3A8 574 "ORACLE_DB" 0 2147483644 "" "" "INACTIVE" "DEDICATED" 574 "ORACLE_DB" "Administrator" "1234" "TESTPC-PC" 52708 "unknown" "JDBC Thin Client" "USER" 00 0 "" "" "" "" 000007FF4B548518 1374653582 "cs6jdgt8yz34f" 0 24-03-14 16788295 "" "" "" "" "JDBC Thin Client" 2546894660 "" 0 "" 5402793 -1 0 0 0 5 24-03-14 4050 "NO" "NONE" "NONE" "NO" "" "DISABLED" "ENABLED" "ENABLED" 0 "" "NO HOLDER" "" "" "NO HOLDER" "" "" 2023 345 "SQL*Net message from client" "driver id" 1952673792 0000000074637000 "#bytes" 1 0000000000000001 "" 0 00 2723168908 6 "Idle" 0 4049 "WAITING" 4049292378 -1 0 "SYS$USERS" "DISABLED" "FALSE" "FALSE" "FIRST EXEC" 100 000007FF654EA3A8 76 "" |
|
