Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Bolat.

Asked: September 06, 2018 - 4:22 am UTC

Last updated: September 08, 2018 - 10:25 am UTC

Version: 12.2.0.1

Viewed 10K+ times! This question is

You Asked

Hi Oracle professionals,
Please advice us and give right direction.

We have database on 12c on OS Red Hat Enterprise Linux Server release 6.10 with memory settings:
SGA: 4928M
PGA: 1250M

OS memory: 8GB
CPU q-ty: 2
Huge Pages disabled.

It worked OK, with above settings before enabling Data Guard.

2) DG was enabled - then we got of messages like:

2018-08-28T09:02:50.604204+05:00
WARNING: Heavy swapping observed on system in last 5 mins.
Heavy swapping can lead to timeouts, poor performance, and instance eviction.
2018-08-28T09:02:52.420519+05:00
Errors in file /u01/app/oracle/diag/rdbms/pp_sm/PP_SM/trace/PP_SM_dbrm_32047.trc (incident=89163):
ORA-00700: soft internal error, arguments: [kskvmstatact: excessive swapping observed], [], [], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/pp_sm/PP_SM/incident/incdir_89163/PP_SM_dbrm_32047_i89163.trc
2018-08-28T09:03:47.866712+05:00
Dumping diagnostic data in directory=[cdmp_20180828090347], requested by (instance=1, osid=32047 (DBRM)), summary=[incident=89163].


Details in incident:
----- Beginning of Customized Incident Dump(s) -----
==========================================
INSTANCE-WIDE SWAP ALERT SUMMARY
------------------------------------------
WARNING: Heavy swapping observed on the system.
Heavy swapping can lead to timeouts, poor performance, and instance eviction.
To reduce swapping, reduce the memory usage of this database instance and/or
other applications running on this server.

total physical memory: 8001 MB
bytes swapped in/out in last 5 mins: 1003028 KB
cpu utilization: 98%

SGA_TARGET: 4928 MB

PGA_AGGREGATE_LIMIT: 0 MB
maximum PGA allocated since startup: 5554 MB
total PGA currently allocated: 4797 MB
PGA_AGGREGATE_TARGET: 1250 MB

Beware that PGA usage (5554 MB) exceeds PGA_AGGREGATE_TARGET (1250 MB).
Decrease PGA usage by lowering PGA_AGGREGATE_TARGET and PGA_AGGREGATE_LIMIT.

Reduce the memory usage of this database instance by lowering SGA_TARGET,
PGA_AGGREGATE_TARGET, and/or PGA_AGGREGATE_LIMIT.


*****************************************************************
----We added extra capacity as:
OS: extra 4 GB memory added (Total = 12 GB)
CPU a-TY: extra 2 CPU added (Total = 4 CPU)

also in DB level:
SGA: 8GB (was extended)
PGA: 1700M (was extended)
Huge Pages still disabled.

Then after DB was restarted.
1 day after, we continued getting above errors.

[TOC00000]
Jump to table of contents
Dump continued from file: /u01/app/oracle/diag/rdbms/pp_sm/PP_SM/trace/PP_SM_dbrm_3857.trc
[TOC00001]
ORA-00700: soft internal error, arguments: [kskvmstatact: excessive swapping observed], [], [], [], [], [], [], [], [], [], [], []

[TOC00001-END]
[TOC00002]
========= Dump for incident 94092 (ORA 700 [kskvmstatact: excessive swapping observed]) ========
[TOC00003]
----- Beginning of Customized Incident Dump(s) -----
==========================================
INSTANCE-WIDE SWAP ALERT SUMMARY
------------------------------------------
WARNING: Heavy swapping observed on the system.
Heavy swapping can lead to timeouts, poor performance, and instance eviction.
To reduce swapping, reduce the memory usage of this database instance and/or
other applications running on this server.

total physical memory: 12041 MB
bytes swapped in/out in last 5 mins: 771796 KB
cpu utilization: 91%

SGA_TARGET: 8192 MB

PGA_AGGREGATE_LIMIT: 0 MB
maximum PGA allocated since startup: 4446 MB
total PGA currently allocated: 4086 MB
PGA_AGGREGATE_TARGET: 1700 MB

Beware that PGA usage (4446 MB) exceeds PGA_AGGREGATE_TARGET (1700 MB).
Decrease PGA usage by lowering PGA_AGGREGATE_TARGET and PGA_AGGREGATE_LIMIT.

Reduce the memory usage of this database instance by lowering SGA_TARGET,
PGA_AGGREGATE_TARGET, and/or PGA_AGGREGATE_LIMIT.

*****************************************************************

So, I have questions - critical for us:
What could be the source of Heavy Swapping problem?
1. Enabling Data Guard? Its processes eat all memory? Before enabling DG, database worked OK without Huge Page enabling.
2. Disabled Huge Pages - caused low performance?
3. I have read somewhere that if OS Total Memory => 8GB - huge pages must be enabled.
Is it true? If there are any ORA DOCS saying that huge page enabling is MUST HAVE for data guard feature?

Also we tried to return back the state - Database without DG.
Even after disabling all enabled DG parameters (LOG_ARCHIVE_CONFIG, LOG_ARCHIVE_DEST_2,
LOG_ARCHIVE_DEST_STATE_2, FAL_CLIENT, FAL_SERVER, STANDBY_FILE_MANAGEMENT) & restarting DB
- we were getting the above errors - heavy swapping and low performance. We do not use DG broker.
I came to conclusion that after disabling DG parameters - Data Guard is not disabled,
because we continued getting the errors.
4.Is it possible to disable Data Guard (in states if DG Broker is not used), for it became standalone server, cutting from Standby DB server fully?

With regards!

and Connor said...

The rule we used for the message is total swap in + swap out percentage is over a certain threshold (2%).

But looking at your figures (I'll focus on the last config):

SGA_TARGET: 8192 MB
maximum PGA allocated since startup: 4446 MB

That's already 12.5G on a 12G machine, so I'm not surprised you are seeing alerts about memory pressure. And since in the 8G config, you had a max PGA of 5G it seems quite likely that you have processes that would be demanding even more memory if it was available to them.

So I'd put DataGuard aside for the time being, and focus on identifying *which* processes are consuming all that memory.

select
  st.sid,
  s.name, 
  st.value
from v$statname s, v$sesstat st
where st.statistic# = s.statistic#
and s.name = 'session pga memory max';


is a simple place to start. Get the SID's that are the troublemakers and then see what sessions they map to. If they are your application, then you've got some code examination to be do. If they are Oracle sessions, then you can present a case to Support and get some analysis done.

On the topic of huge pages, if you have

a) a large SGA, and
b) lots of sessions

then there are considerable benefits to be had. A couple of blog posts on the topic here

https://kevinclosson.net/2009/07/25/little-things-doth-crabby-make-%E2%80%93-part-ix-sometimes-you-have-to-really-really-want-your-hugepages/

https://kevinclosson.net/2009/07/28/quantifying-hugepages-memory-savings-with-oracle-database-11g/

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, September 06, 2018 - 10:07 am UTC

ok, thanks for reply.
What if the problem do not exist now.
We had to migrate data from problem DB to another correct working DB for not extending downtime for customers.

But now we are making root cause analysis - for finding what caused the low performance.

As far as I understood you SQL query shows picture happening in the database for now? Is it true?
If there is query showing historical data?

With regards!




Connor McDonald
September 08, 2018 - 10:25 am UTC

AWR reports can show you the same information for a previous time period.

https://asktom.oracle.com/pls/apex/asktom.search?tag=awr-report-analysis

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database