ORA 700 [kskvmstatact: excessive swapping observed]

ORA 700 [kskvmstatact: excessive swapping observed]
========================================


One of my database down suddenly.
Reason:  ORA-700 SWAP error, as the dump it's taking more that 10 minutes pmon killed the process and the database was down.

this database running with less memory .

Solution
alter system set "_kse_die_timeout"=XX or 0 if we want to disable the pmon killer during dumping .


note:
The error is documented in: 
- Note:1919850.1 - 12c: Warning Message About Heavy Swapping Observation and ORA-700 [kskvmstatact: excessive swapping observed] 

Because your swap memory has low values of swap in / swap out, this warning message can be safely ignored. 

Given both the alert log message and ORA-700 are expected behavior for 12c, no action is required. 

An enhancement request (ER) was created to change the threshold in future releases in 
Bug 19495842 - CHANGE THRESHOLD IN THE SWAP WARNING IN THE ALERT_<SID>.LOG 


=================
1 - Increase the timeout value to _kse_die_timeout to 360000 to increase the timeout to 1 hour or 180000 if you desired 30 minutes . That will stop PMON killing the process and db shutdown.

2 - Set "_kse_die_timeout"=0 (it is a dynamic parameter so can be set with alter system) but in this case If a process does get stuck spending ages dumping diagnostic data it would not be automatically killed after XX mins, this means that all the resources for the process still allocated and could cause other issues related to locking or hanging issue.
==================================

ORA-04030: out of process memory when trying to allocate 123416 bytes

ORA-04030: out of process memory when trying to allocate 123416 bytes
====================================================

we revive alert from database due to less memory. user run query consuming more.


It impacts other process to fail. This error causing lack of memory in pga and makes other process to fail.

You can also monitor the PGA usage and high consumption of the pga process with below queries during the error time.

=================================================

spool memory_results.txt append

set pages 999
select to_char(sysdate,'DD-MON-YY HH:MI:SS') from dual;


PROMPT
PROMPT PROCESS CONSUMING MORE PGA:
PROMPT -------------------------

set line 200
col logon for a10
col spid for a10
col username for a10
col osuer for a10
col machine for a10
col program for a15
col module for a10
SELECT DECODE(TRUNC(SYSDATE - LOGON_TIME), 0, NULL, TRUNC(SYSDATE - LOGON_TIME) || ' Days' || ' + ') ||
TO_CHAR(TO_DATE(TRUNC(MOD(SYSDATE-LOGON_TIME,1) * 86400), 'SSSSS'), 'HH24:MI:SS') LOGON,
SID, v$session.SERIAL#, v$process.SPID , ROUND(v$process.pga_used_mem/(1024*1024), 2) PGA_MB_USED,
v$session.USERNAME, STATUS, OSUSER, MACHINE, v$session.PROGRAM, MODULE
FROM v$session, v$process
WHERE v$session.paddr = v$process.addr
--and status = 'ACTIVE'
--and v$session.sid = 97
--and v$session.username = 'SYSTEM'
--and v$process.spid = 24301
ORDER BY pga_used_mem DESC;

PROMPT
PROMPT List largest process:
PROMPT -------------------------

col user for a20
SELECT pid, spid, substr(username,1,20) "USER", program, pga_used_mem, pga_alloc_mem, pga_freeable_mem, pga_max_mem
FROM v$process
WHERE pga_alloc_mem = (SELECT max(pga_alloc_mem)
FROM v$process
WHERE program NOT LIKE '%LGWR%');

PROMPT
PROMPT Summation of PGA based on v$process:
PROMPT -------------------------

SELECT SUM(pga_alloc_mem)/1024/1024 AS "Mbytes allocated", SUM(PGA_USED_MEM)/1024/1024 AS "Mbytes used" FROM v$process;

PROMPT
PROMPT Summation of PGA memory based on V$SESSTAT:
PROMPT -------------------------

SELECT SUM(value)/1024/1024 AS Mbytes FROM v$sesstat s, v$statname n WHERE n.STATISTIC# = s.STATISTIC# AND n.name = 'session pga memory';

PROMPT
PROMPT PGASTATS
PROMPT -------------------------

SELECT SUBSTR(name,1,30), value, unit FROM v$pgastat;

PROMPT
PROMPT List all processes including PGA size from V$PROCESS.
PROMPT -------------------------

SET LINESIZE 120
SET PAGESIZE 120
COLUMN spid HEADING 'OSpid' FORMAT a8
COLUMN pid HEADING 'Orapid' FORMAT 999999
COLUMN sid HEADING 'Sess id' FORMAT 99999
COLUMN serial# HEADING 'Serial#' FORMAT 999999
COLUMN status HEADING 'Status' FORMAT a8
COLUMN pga_alloc_mem HEADING 'PGA alloc' FORMAT 99,999,999,999
COLUMN pga_used_mem HEADING 'PGA used' FORMAT 99,999,999,999
COLUMN username HEADING 'oracleuser' FORMAT a12
COLUMN osuser HEADING 'OS user' FORMAT a12
COLUMN program HEADING 'Program' FORMAT a20

SELECT p.spid,
p.pid,
s.sid,
s.serial#,
s.status,
p.pga_alloc_mem,
p.pga_used_mem,
s.username,
s.osuser,
s.program
FROM v$process p, v$session s
WHERE s.paddr( + ) = p.addr
AND p.background is null /* Remove if need to monitor background processes */
ORDER BY p.pga_alloc_mem DESC;



PROMPT
PROMPT Summation of PGA and SGA
PROMPT ----------


SELECT SUM(bytes)/1024/1024 AS Mbytes
FROM (SELECT value AS bytes
FROM v$sga
UNION ALL
SELECT value AS bytes
FROM v$sesstat s, v$statname n
WHERE n.STATISTIC# = s.STATISTIC#
AND n.name = 'session pga memory');



PROMPT
PROMPT PGA and SGA Memory hourly
PROMPT -----------------


SELECT dhs.instance_number, trunc(dhs.end_interval_time,'mi') Time,
pga.mem pga, sga.mem sga,(pga.mem+sga.mem) Total_MB
FROM
(SELECT snap_id,instance_number,round(sum(value)/1024/1024,4) mem
FROM DBA_HIST_PGASTAT
WHERE name = 'total PGA allocated'
GROUP BY snap_id,instance_number) pga,
(SELECT snap_id,instance_number,round(sum(bytes)/1024/1024,4) mem
FROM DBA_HIST_SGASTAT
GROUP BY snap_id,instance_number) sga,
dba_hist_snapshot dhs
WHERE dhs.snap_id=sga.snap_id
and dhs.instance_number=sga.instance_number
and dhs.snap_id=pga.snap_id
and dhs.instance_number=pga.instance_number
ORDER BY dhs.snap_id desc, dhs.instance_number

spool off
===================================================================