User:Arvindkumar Subramanian

== AUTOMATED & CUSTOMIZED DMS DATABASE,SYSTEM AND NETWORK ALERTS==

Table of Contents

1.Introduction.

2.Top DML Activity.

3.Top SELECT Statements.

4.Session Counts.

5.Active and Inactive Sessions.

6.Locked Objects.

7.Invalid Objects.

8.Disk Space Usage.

9. DMS DB Tablespace Size.

10.Memory Target Usage & Allocation.

11.System Statistics.

12.Network Wait Events.

1.Introduction

The purpose of the document is to provide the description about the automated report alerts on CLAIMS DMS Production Database.

2. Top DML Activity

The report shows about the Top 20 DML queries used in DMS product database for last two hours. To retrieve the queries is based on the elapsed_time column.

USERNAME  -	 Name of the DB user.

SQL_ID 	-	SQL identifier of the SQL statement that the session was executing at the time of sampling.

SQL_FULLTEXT  	-	Full text for the SQL statement.

ELAPSED_SECONDS       -	Elapsed time (in microseconds) used by this cursor for parsing, executing, and fetching

CPU_TIME	-	CPU time (in microseconds) used by this cursor for parsing, executing, and fetching.

DISK_READS		-	Number of disk reads for this child cursor.

BUFFER_GETS		-	Number of buffer gets for this child cursor.

3. Top SELECT statement

The report shows about the Top 20 SELECT queries used in DMS product database for last two hours. To retrieve the queries is based on the elapsed_time column.

USERNAME		-	 Name of the DB user.

SQL_ID	-	SQL identifier of the SQL statement that the session was executing at the time of sampling.

SQL_FULLTEXT  	-	Full text for the SQL statement.

ELAPSED_SECONDS       -	Elapsed time (in microseconds) used by this cursor for parsing, executing, and fetching.

CPU_TIME	-	CPU time (in microseconds) used by this cursor for parsing, executing, and fetching.

DISK_READS		-	Number of disk reads for this child cursor.

BUFFER_GETS		-	Number of buffer gets for this child cursor.

4. Session Count

The report shows about the number of sessions handled by Oracle users in the past two hours.

USERNAME		–	Oracle username[SHI_DMS_LIVE,BPMSUSER].

SESSION_COUNT	–	Number of sessions opened by oracle users.

5. Active and Inactive Sessions

This report shows the Active and Inactive sessions found in the Database with status, idle time,sql text,oracle and OS users in last two hours.

USERNAME		-	Oracle user.

SQL_TEXT		-	Full text for the SQL statement.

SID			-	Session Identifier.

SERIAL#		-	Session serial number used to uniquely identify a session's objects.

OSUSER		-	Operating system client user name.

MACHINE		-	Operating system machine name.

PROGRAM		-	Operating system program name.

STATUS		      	-	Status of the session[Active,Inactive,Killed,Cached,Sniped].

TIME			-	Time of logon.

IDLE                               -		Represents the elapsed time in seconds since the session has become Active or Inactive.

6.Locked Objects

This reports about the locked objects in detail occurred in the Production Database for the past two hours.

SID			-	Session Identifier.

SERIAL#		-	Session serial number used to uniquely identify a session's objects.

USERNAME		-	Displays Oracle users.

OS_USER_NAME	-	Displays the OS User Names.

TERMINAL		-	Operating system terminal name.

OBJECT_ID		-	Dictionary object number of the object.

OBJECT_NAME		-	Name of the Object.

7.INVALID OBJECT

The report shows the Invalid Objects found in the Database in last two hours.

OBJECT_NAME		-	Name of the Object.

OBJECT_TYPE		-	Type of the Object such as Tables, Index,etc.,

STATUS			-	Status about the Object(valid, invalid, or N/A).

8.DISK SPACE USAGE

Reports the file system disk space Growth information in Operating System.

FILE SYSTEM		-	Displays the File system allocated in OS.

1M-BLOCKS		-	Total space allocate for the particular file system.

USED			-	Displays the Used size for the file system in MB.

AVAILABLE		-	Available space of the file system in MB.

USE%			-	Used spaces is mentioned in percentage.

MOUNTED ON		-	Mount space for the file system.

9.DMS DB TABLESPACES SIZE

The following columns report for the DMS Database tablespace growth information details.

TABLESPACE		-	Name of the Tablespace.

USED MB		-	Used space in the Tablespace.

FREE MB		-	Available Free space in the Tablespaces.

TOTAL MB		-	Total space allocated to the Tablespaces.

PCT FREE		-	Reserved percentage of space in ORACLE data blocks.

10.MEMORY_TARGET USAGE & ALLOCATION

Displays information about the dynamic SGA components. All sizes are expressed in MB

COMPONENT_NAME		-	Name of the Component.

CURRENT_SIZE			-	Current using size of the Components.

MIN_SIZE   -	Minimum size allocate for the component since Instance startup.

MAX_SIZE   -	Maximum size allocate for the component since instance startup.

USER_SPAECIFIED_SIZE	-	Value of the user parameter for the component.

OPER_COUNT			-	Number of operations since instance startup.

LAST_OPER_TYPE	-	Last completed operation for the component like as STATIC,INITIALIZING, DISABLED, GROW, SHRINK,SHRINK_CANCEL.

LAST_OPER_MODE 	-	Mode for the last completed operation like as Manual, deferred, Immediate.

LAST_OPER_TIME		-	Start time of the last completed operation.

GRANULE_SIZE			-	Information for Granularity of the GROW or SHRINK operation.

11.SYSTEM STATISTICS

Reports on various system loads, including CPU activity,Kernel,memory,paging, device load, network, swapping & System calls for last two hours with 10 minutes interval.Following informations are gather from this session


 * Collective CPU usage.
 * Individual CPU statistics.
 * Memory used and available.
 * Swap space used and available.
 * Overall I/O activities of the system.
 * Individual device I/O activities.
 * Context switch statistics.
 * Run queue and load average data.
 * Network statistics.
 * Report sar data from a specific time.
 * Individual device I/O activities.
 * Context switch statistics.
 * Run queue and load average data.
 * Network statistics.
 * Report sar data from a specific time.
 * Network statistics.
 * Report sar data from a specific time.
 * Report sar data from a specific time.
 * Report sar data from a specific time.

12.NETWORK WAIT EVENTS

The output for the reports usually shows about network wait events for last two hours with one minute Interval.

END_TIME		-	End time of the interval.

WAIT_CLASS		-	Name of the wait class.

SAMPLE_COUNT	-	Time waited during the interval.