Monitor/sample values from gv$SesStat in history: monitor transaction count per session in history
In current version of Oracle database there’s no way to breakdown historic statistic values to sessions if session has already terminated. Some times this information is strongly needed to identify bottlenecks. Actual situation was: – massive number of transactions which caused massive small write I/O reaching limits of physical disks – to identify root cause identification fo processes/sessions was needed – consideration can only be done in history with time gap of some hours, problematic sessions have been terminated for long time Solution: Create sampling process with minimal resource impact to CPU and I/O with result storage in database table. Preparation Create two tables, one for result storage and one for abortion of sampling process: CREATE TABLE SessMon_Semaphore (Name VARCHAR2(30)); COMMENT ON TABLE SessMon_Semaphore IS 'Control abortion of sampling process'; COMMENT ON COLUMN SessMon_Semaphore.Name IS 'Record identifier to store record lock on it&