Database network statistics (SQL*NET)

Job details

Parameters

Job Summary

• Purpose: The purpose of this job is to monitor and report on the network SQL*NET statistics for Oracle database instances.
• Why: This job is crucial because it helps in analyzing the network load and performance by capturing various metrics such as bytes sent/received and SQL*Net roundtrips. This data can help in diagnosing performance issues, planning network capacity, and ensuring that the database network communication is optimal.
• Manual checking: You can check this manually in the database by issuing the following SQL commands:

select * from dbw_sqlnet;

select * from dbw_sqlnet_name;

select * from dbw_network_traffic_hist;

select * from dbw_sqlnet_histr;

Job Components

• Object: sqlnet_stat
• Type of Check: Performance Category
• Company: dbwatch.com
• Default Schedule: Runs every ten minutes every hour around the clock (7, 17, 27, etc. minutes past every hour).

Dependencies

Tables and Views

create table dbw_sqlnet(STATISTIC# NUMBER,value number,h_date date)

create table dbw_sqlnet_name(STATISTIC# NUMBER, NAME VARCHAR2(64))

create table dbw_network_traffic_hist(HISTORY_DATE DATE, NAME VARCHAR2(64), VALUE NUMBER)

create table dbw_sqlnet_histr (HISTORY_DATE date, EXECUTION_COUNTER number, STATISTIC# number, value number, diff_value number, period number)

create or replace view dbv_sqlnet_bytes_histr(name, diff_value, h_date) as select t1.name, round(((t2.diff_value/1048576)/t2.period)*60, 2), t2.history_date from dbw_sqlnet_name t1, dbw_sqlnet_histr t2 where t1.statistic# = t2.statistic# and t2.period > 1 and t1.name like 'bytes%'

create or replace view dbv_sqlnet_roundtrips_histr(name, diff_value, h_date) as select t1.name, round((t2.diff_value/t2.period)*60), t2.history_date from dbw_sqlnet_name t1, dbw_sqlnet_histr t2 where t1.statistic# = t2.statistic# and t2.period > 1 and t1.name like 'SQL%'

Procedure Assumptions

• Description: The ‘sqlnet_stat’ procedure tracks SQL*Net related statistics and updates various tables based on dynamic statistics from Oracle performance views.
• Potential Issues: Requires adequate permissions on dynamic views like V_$SYSSTAT.
• Exception Handling: Updates task values with error information, logs to DBW_ERRORS table on exception.

Execution and Reporting

• Report Prepared: SQL*Net statistics are accumulated and presented through dbWatch Reports, illustrating data transfer rates across SQL*Net and roundtrips.
• Charts: Reports include categorical charts visualizing trends over time based on collected network statistics.

This job setup ensures continuous monitoring and historical data capturing, facilitating detailed analysis of network performance for Oracle databases managed by dbWatch Control Center.