OEM Metric “Memory Utilization” Different on 12c and 13c

So, as rollout strategy we created a new OEM13c to decommission a 12c. However during the testes, noticed Memory Utilization metric was a lot different between 12c and 13c. Why?

Happens that the Memory Utilization is calculated differently between 12c and 13c, but also seems 13c is more accurate, as per MOS The Host Memory Utilization Percentage Calculation in Enterprise Manager Cloud Control (Doc ID 1908853.1)

Well, those who are familiar with memory use computations in the operating system might become confused when examining the memory use metric data from Enterprise Manager 12c and 13c Cloud Control. Metrics such as Memory Utilization (%) do not have an equivalent in the OS, but OS data will be used in its derivation.

This is the formula used by Enterprise Manager 12.1.0.3 for Linux Memory Utilization (%), for example:

Memory Utilization (%) = (100.0 * (activeMem) / realMem)
 = 100 * 25046000/99060536
 = 25.28
EM Shows : 25.5

* On this, activeMem is Active Memory (Active), and realMem is Total Memory (MemTotal).

Comparing this with MemFree, which is not valid, might provide an impression that utilization is not being accurately represented.

Also, the “OEM13c value” was already collected in OEM12c, but under metric name “Used Logical Memory”. And basically “Memory Utilization” in 12c uses “activeMem” instead of “realMem-(freeMem+Buffers+Cached)”. As per image below.

OEM12_grep_mem

The formula in place on 13c is exactly the same as used to fix MOS EM 13c: Incorrect Memory Utilization Reported for Linux Hosts in Enterprise Manager 13.1.0.0.0 Cloud Control (Doc ID 2144976.1)

Example:

[root@greporasrv ~]# free
             total       used       free     shared    buffers     cached
Mem:     264087460  257669460    6418000    7657500     461088   11008128
-/+ buffers/cache:  246200244   17887216
Swap:     25165820    3365104   21800716

(100.0 * (realMem-(freeMem+Buffers+Cached)) / realMem)
100*(264087460-(6418000+461088+11008128))/264087460) = 93,22678328

As per OEM13c:

OEM13_grep_mem.jpg

Also, by checking on server using SAR, seems value in OEM 13c is more accurate, indeed:

[root@greporasrv ~]# sar -r
Linux 2.6.39-400.294.4.el6uek.x86_64 (greporasrv) 	08/29/2017 	_x86_64_	(44 CPU)

12:00:01 AM kbmemfree kbmemused  %memused kbbuffers  kbcached  kbcommit   %commit
12:10:01 AM   5377540 258709920     97.96    719080  10775828  83876744     29.00
12:20:01 AM   6131220 257956240     97.68    719504  10721084  82467712     28.51
12:30:01 AM   5623060 258464400     97.87    719700  10720972  83456216     28.85
12:40:01 AM   5606572 258480888     97.88    719836  10779108  83228440     28.77
12:50:01 AM   5783256 258304204     97.81    719860  10848644  82925908     28.67
01:00:01 AM   4151148 259936312     98.43    719888  11589048  84400040     29.18
01:10:01 AM   3717000 260370460     98.59    719904  11534336  84838784     29.33
01:20:01 AM   4282412 259805048     98.38    720164  11480792  84047568     29.06
01:30:01 AM   4473128 259614332     98.31    720184  11483604  83857348     28.99
01:40:01 AM   5113136 258974324     98.06    720256  11528492  83036284     28.71
01:50:01 AM   4971036 259116424     98.12    720284  11587956  82955128     28.68
02:00:01 AM   4026540 260060920     98.48    720344  11663184  86489692     29.90
02:10:01 AM   4312916 259774544     98.37    720380  11678316  83834592     28.98
02:20:01 AM   5058980 259028480     98.08    720408  11624028  82876972     28.65
02:30:01 AM   4609908 259477552     98.25    720556  11541392  83871244     29.00
02:40:01 AM   5020668 259066792     98.10    720592  11574912  82887808     28.66
02:50:01 AM   5175916 258911544     98.04    720748  11619572  82725252     28.60
03:00:01 AM   4701236 259386224     98.22    720780  11687100  83421624     28.84
03:10:01 AM   4757976 259329484     98.20    721204  11648864  83298716     28.80
03:20:01 AM   4485280 259602180     98.30    721248  11719272  83299472     28.80
03:30:01 AM   4267068 259820392     98.38    721264  11794688  83683344     28.93
03:40:01 AM   4080264 260007196     98.45    721404  11856796  83863540     28.99
03:50:01 AM   4864276 259223184     98.16    721676  11975372  82735744     28.60
04:00:01 AM   4427284 259660176     98.32    721696  12056676  83450524     28.85
04:10:01 AM   4868184 259219276     98.16    721736  11863420  82860464     28.65
04:20:01 AM   4711608 259375852     98.22    721760  11877192  83205684     28.77
04:30:01 AM   4452764 259634696     98.31    721928  11945108  83515596     28.87
04:40:01 AM   4800700 259286760     98.18    722072  12015444  82681320     28.58
04:50:01 AM   4796588 259290872     98.18    722212  12075496  82703948     28.59
05:00:01 AM   4320164 259767296     98.36    722372  12164956  83390596     28.83
05:10:01 AM   3350940 260736520     98.73    722488  12120116  84525028     29.22
05:20:01 AM   4200236 259887224     98.41    722628  11965996  83510580     28.87
05:30:01 AM   4028020 260059440     98.47    722640  12019516  83720748     28.94
05:40:01 AM   3929740 260157720     98.51    722720  12069520  83632964     28.91
05:50:01 AM   2719452 261368008     98.97    723460  14408924  83745112     28.95
06:00:01 AM   1530448 262557012     99.42    723644  14943264  84618304     29.25
06:10:01 AM   2925268 261162192     98.89    605748  13363596  84792452     29.31
06:20:02 AM   3235532 260851928     98.77    605916  13811664  83516740     28.87
06:30:01 AM   3265640 260821820     98.76    606072  13848028  83385196     28.83
06:40:01 AM   2102756 261984704     99.20    606232  14745508  83638764     28.92
06:50:01 AM   2386376 261701084     99.10    606644  14821232  83118484     28.74
07:00:01 AM   5343496 258743964     97.98    186908  12019804  84375032     29.17
07:10:01 AM   5073472 259013988     98.08    219044  12597104  83579876     28.90
07:20:01 AM   5380380 258707080     97.96    241300  12600412  83107160     28.73
07:30:01 AM   5063504 259023956     98.08    253984  12653840  83373804     28.82
07:40:01 AM   8241032 255846428     96.88    269960   9772232  83072188     28.72
07:50:01 AM   8549616 255537844     96.76    278472   9853288  82646916     28.57
08:00:01 AM   8185864 255901596     96.90    287296   9938816  83179808     28.76
08:10:01 AM   7797504 256289956     97.05    295856  10029904  83464160     28.86
08:20:01 AM   8813696 255273764     96.66    302620   9930672  82081220     28.38
08:30:01 AM   8574984 255512476     96.75    309156   9880124  82557600     28.54
08:40:01 AM   8010072 256077388     96.97    314804   9912220  83241764     28.78
08:50:01 AM   8791112 255296348     96.67    319568   9980532  81787424     28.28

OEM: The number of hanging transactions are hang_trans is %

Hi all!
So, today is quickie one, just to make the links. Seems this message from OEM is not clear enough for some people, specially regarding non-specialists in Oracle: This means something is in lock in your database!

If this is the case, contact a DBA.

If you ARE a DBA, you may want to read this post about easy locating and solving locks: Solving Simple Locks Through @lock2s and @killlocker.

Also, if the session if from DBLink, is always useful to read this: Lock by DBLink – How to locate the remote session?

There is also some additional/specific material about some issues and bugs in this regard here: Tag: LOCK.

I hope it helps!
Cheers!

Oracle TPS: Evaluating Transaction per Second

Sometimes this information has some ‘myth atmosphere’… Maybe because of that Oracle doesn’t have this information very clear and it’s not the most useful metric.
But for comparison to another systems and also to performance/’throughput’ with different infrastructure/database configuration, it can be useful.

It can be seen by AWR on “Report Summary” section, on “Load Profile”, “Transactions” item:

awr_tps

But if you want to calculate it through SQL query?
And if you want to have a historic from this metric?

I found a reference for this calculation here, using v$sysstat.
It’s the only reference I found, and it on 10g documentation… It refers this metric as:

Number of Transactions = (DeltaCommits+DeltaRollbacks)/Time

It also refers as DeltaCommits and DeltaRollbacks, respectively, “user commits” and user “rollbacks”.

Where it goes a possible SQL to do that:

WITH hist_snaps
AS (SELECT instance_number,
snap_id,
round(begin_interval_time,'MI') datetime,
(  begin_interval_time + 0 - LAG (begin_interval_time + 0)
OVER (PARTITION BY dbid, instance_number ORDER BY snap_id)) * 86400 diff_time
FROM dba_hist_snapshot), hist_stats
AS (SELECT dbid,
instance_number,
snap_id,
stat_name,
VALUE - LAG (VALUE) OVER (PARTITION BY dbid,instance_number,stat_name ORDER BY snap_id)
delta_value
FROM dba_hist_sysstat
WHERE stat_name IN ('user commits', 'user rollbacks'))
SELECT datetime,
ROUND (SUM (delta_value) / 3600, 2) "Transactions/s"
FROM hist_snaps sn, hist_stats st
WHERE     st.instance_number = sn.instance_number
AND st.snap_id = sn.snap_id
AND diff_time IS NOT NULL
GROUP BY datetime
ORDER BY 1 desc;

I like to use PL/SQL Developer to see this kind of data. And it regards us to make very good charts very quickly. I try it in a small database here, just for example:

7days_tps

Jedi Master Jonathan Lewis wrote a good post about Transactions and this kind of AWR metric here.

See ya!
Matheus.