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
===================================================================

ORA-07445: exception encountered: core dump (from view execution)

ORA-07445: exception encountered: core dump
=====================================
Note: ORA-07445 please note that internal error all are not same depends on argument it vary.

this was one of the internal error we most cases, the best practice we follow was contact oracle support. and upload the file to them(even in starting trace file and alertlog verfiy and find the cause)

in my case cause was one our app team member created view and executed from toad.(he did his testing in production, so better you suggest them to test in test and qa)

solution:-
   1. tune the sql and try to gather stats on objects.

  2. incase if still first solution does not work follow below procedure set parameter to avoid.

  alter session set "_simple_view_merging"=false;


in my case contacted the app team member applied suggestion number 1.

ADD/Remove UNDO tablespace in oracle database

ADD/Remove UNDO tablespace in oracle database:-
=====================================

 every database having default undo tablespace, if you want to change it do follow below procedure.

1. first create new tablespace with new(as you want to create)
Example:-
CREATE UNDO TABLESPACE <NEW_NAME> DATAFILE '<datafile_location/name.dbf>' SIZE 1000M AUTOEXTEND OFF ONLINE RETENTION NOGUARANTEE
BLOCKSIZE 8K
FLASHBACK ON;

ALTER SYSTEM SET UNDO_TABLESPACE = <NEW_NAME>;
ALTER SYSTEM SET UNDO_RETENTION = <SET as per NEED>;

Once new one created, if you want drop old one follow steps below.
check any session already using old one  with below sql

SQL> select tablespace_name, owner, segment_name, status from dba_rollback_segs where TABLESPACE_NAME='UNDOTBS1' and status='ONLINE';

you will get SID&Serial# if any in use, kill them if have or else you can proceed to drop old one.

To kill
SQL> alter system kill session 'SID,SERIAL#';

To drop
SQL> drop tablespace undotbs1 including contents and datafiles;



please help to share your suggestions,  or  share your experience on same kind of issue in comment session.
  your sharing or suggestions will helps me to improve in my future posts.

Thanks for the visit!!!

ADD/Remove TEMP tablespace in oracle database

ADD/Remove TEMP tablespace in oracle database:-
=====================================

  To add new Temp tablespace and change it as default do follow below procedure.

1. First create New temptablespace and change it to default temp.

CREATE TEMPORARY TABLESPACE <NEW_TEMP_NAME> TEMPFILE '<teampfile location.dbf' SIZE 1000M AUTOEXTEND OFF
TABLESPACE GROUP ''
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE <NEW_TEMP_NAME>;


Remove the old temptable space , before remove you must check any session already in use or not.

to check use below query to get live session(SID&serial#)  those who use old temp.

SELECT b.tablespace,b.segfile#,b.segblk#,b.blocks,a.sid,a.serial#,
a.username,a.osuser, a.status
FROM v$session a,v$sort_usage b
WHERE a.saddr = b.session_addr;

THEN kill session if any session in use.

alter system kill session 'SID_NUMBER, SERIAL#';

we can drop old temporary tablespace.
DROP TABLESPACE TEMP including contents and datafiles;


Be careful while doing this in production.

Thanks for your visit my blog, if you have any suggestion or question please comment below. 

SQL write Query with Tuning tips

Query Performance Tuning
====================

 In this post i will give few tips while writing sql query, how we can write better query with less cost
 If you follow below steps keep in mind while writing query you can tune up your sql while writning
 time. Below are some tips which can be used as guideline.

  Make sure am not expert, But what i learn have been post here to help some one. 
  If you know more points or you want to add on more please post in comment session it help to me and also others to learn. 
  Even though i can miss some silly points or known point as well. 
  so please help to remind them !!!

1. DISTINCT:-  Do not use, If the objective can be achieved otherwise. DISTINCT incurs an extra sort operation and therefore slows your queries down.  
2. Indexed columns:- Do not modify indexed columns with functions like RTRIM, TO_CHAR, UPPER, TRUNC, As this will prevent the optimizer from identifying the index. If possible perform the modification on the constant side of the condition. If the indexed column is usually accessed through a function, consider creating a function based index. (many cases people use and functions and complain my query not using index).
3. UNION:- Do not use UNION, if the objective can be achieved through an UNION ALL. UNION incurs an extra sort operation which can be avoided.
4. EXISTS :- use if selective predicate in parent query .
5. IN : use if selective predicate is in the sub query.
6. joins from one complex view to another not recommended.
7. Do not use HINTS (as oracle new version using CBO(cost based optimizer).
8. statistics for the objects used in the query are up to date or not, need to check.
9. Read explain plan and try  tune your query.
10. Commented lines are not good in between query lines.
11. Instead of using NULL some cases DECODE better to use.

still have more point will add on later .

Thanks for your visit, please help to post in comment session if you have more points or any correction from my statements. it help to me &others!!!

B-tree vs Bitmap index

B-tree vs bitmap index
=================

  In this post i would like talk about index that to mainly b-tree vs bit man index.
  Both are used to play key role in performance of query.

lets some comparison of both:-

B-tree:-

• it is one Default index, Like create index on one or columns using tree like structure.
• Cardinality is High, means less duplicate
• don`t confuse some people call it function based index as in.

syntax example:-

CREATE INDEX <index_name>
ON <table_name>(function(column_name));

Bitmap:-

• Carnality is Low, means more duplicate.
• bitmap index create name with bimap
• key word, Bitmap only can use in oracle Enterprise edition, standard edition we can not use this feature.

syntax example:- CREATE BITMAP INDEX 
<index_name>
ON <table_name> (column_name);


still we can add on more points, but i want to hear from your side also.
so please post your opinion about index types in comment session.