How to monitor OracleDB ACTIVE/INACTIVE Sessions on WebLogic?

WebLogic contains lots of inbox JDBC MBeans to monitor DB transactions. The most important ones are;

• ActiveConnectionsCurrentCount @ JDBCDataSourceRuntimeMBean
• ActiveConnectionsAverageCount @ JDBCDataSourceRuntimeMBean
• WaitingForConnectionCurrentCount @ JDBCDataSourceRuntimeMBean
• ConnectionDelayTime @ JDBCDataSourceRuntimeMBean
• FailedReserveRequestCount @ JDBCDataSourceRuntimeMBean
• LeakedConnectionCount @ JDBCDataSourceRuntimeMBean

These are available on Data Sources dashboard on WLSDM console.

If you are good at Database Administration or want to work close with DBA; Here is a recipe to reflect your DB knowledge to WLSDM dashboards and understand what’s going on at your main DB that WebLogic connects over JDBC driver.

Case: Monitoring ACTIVE/INACTIVE Oracle Database Sessions on WebLogic?

I have prepared a short screencast tutorial about monitoring DB sessions on WebLogic. It can be extended by adding machine, inst_id fields from gv$session and gv$sqlarea.

select  
    status, count(*) 
from  
    gv$session 
group by status  
order by 1

You can use below SQL on Oracle Database for your reference to understand what is going on at your Oracle database then reflect and visualize it on WLSDM dashboards.

select s.inst_id,'alter system kill session'||''''||sid||','||serial#||''''||',@'||s.inst_id||''''||';',q.SQL_ID,q.HASH_VALUE,lower(s.username) ora_user,s.machine,s.sid, s.serial#,q.sql_text, s.event#, s.event,  
q.executions, q.cluster_wait_time, q.plsql_exec_time, q.java_exec_time, q.rows_processed, q.cpu_time, q.elapsed_time,s.osuser  
from gv$session s, gv$sqlarea q  
where s.status='ACTIVE'  
and  
q.hash_value = s.sql_hash_value  
and q.address = s.sql_address  
and s.type!='BACKGROUND'

Watch this short screencast tutorial then understand how to monitor Oracle DB sessions, understand how WebLogic datasource MBeans behave under load? Very useful and educational screencast to understand WebLogic and Oracle Database behavior while working together.

Sreencast GIF File URL: Simply download and share with your team members.
http://blog.wlsdm.com/content/images/2017/06/oracledatabasesessionmonitoringweblogicwlsdmdevops_mbean.gif

Approximately: 40MB GIF File (High Quality Screencast)

Watch Screencast Tutorial and WebLogic/OracleDB Interactions: