Oracle Commit size larger then available Ram, but v$memory_target_advice says OK

0

Running Oracle 18c on a Windows 2012R VM (VMWare). (I don't have access to any of the VM Admin tools.)

The VM has 5 vCPU, and 12GB Ram (this was supposedly the recommendation from the VM Team to reduce CPU contention...) However I'm noticing the committed memory (used by Oracle) is significantly higher than the available Ram to the machine.

Snippet from Task Manager Performance Tab showing 18.9GB of 36GB Committed

Snippet from Task Manager Details tab showing the breakdown by oracle process



v$memory_target_advice is not hinting at any improvements by increasing the memory target...

enter image description here

Questions

  1. Does this mean that a large part of Oracle is in pagefile?
  2. Being that 3GB is still available, does this mean that if Oracle needed (or if Windows decided that Oracle should be allowed to access) extra ram, it would be able to? (Meaning no need to change anything?)

dangel

Posted 2019-09-20T18:04:33.900

Reputation: 121

Do this: Open Task Manager, click Details, right-click any column, choose Select columns, check Commit size, OK, click on Commit size to sort. What are the processes using the biggest amount? Add perhaps a screenshot. – harrymc – 2019-09-20T19:46:54.670

@harrymc, is that different than the screen shot in the post? – dangel – 2019-09-21T00:59:46.157

Sorry. Yes, this is useful. – harrymc – 2019-09-21T06:43:11.860

No worries :-D. I edited the post to help the images and text stand out better – dangel – 2019-09-21T06:47:13.837

Answers

0

Oracle used to have a bug in its optimizer that caused it to badly calculate the memory required for sorting data. This bad calculation caused it to allocate an enormous amount of memory, in many cases more than the available amount. This is not to say that it ever used this memory, so it was allocated in the page-file but never used.

However, this bug was claimed to have been fixed in version 12. One possibility is that it is still present.

Another possibility is that Oracle has a memory leak. You may test this by immediately after a reboot verifying the committed memory after Oracle starts and after doing the SELECT.

If your tests point to a bug in Oracle's memory allocation, you should report it.

harrymc

Posted 2019-09-20T18:04:33.900

Reputation: 306 093

Asked: 2019-09-20T18:04:33.900

Viewed: 60 times

Active: 2019-09-21T07:08:18.817