Skip to Main Content
  • Questions

  • Behavior of dba_hist_sgastat metrics 'free memory' and 'SQL' for pool 'shared pool' have changed with 12.1.0.2

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Akiva.

Asked: November 15, 2017 - 6:25 pm UTC

Last updated: November 21, 2017 - 1:23 pm UTC

Version: 12.1.0.2

Viewed 1000+ times

You Asked

We are upgrading from 11.2.0.4 to 12.1.0.2 and we are seeing a new behavior for the "free memory" in the shared pool. We are looking at AWR in dba_hist_sgastat. While in 11g it goes up and down, in 12c it is non-decreasing, over several days at least. The 'SQLA' metric is moving in the opposite direction, which supports the theory that 12c ages out cursors lazily, not aggressively. The hit ratio is excellent, consistently.

Is this a bug, and should we flush the shared pool regularly?

and Connor said...

A lot could depend on the memory management option you've gone for (full manual, sga_target, memory_target, etc) but in general for me, I *dont* want my server to have free memory (within reason).

Memory does not last longer if you dont use it. I *want* my memory being used to its full potential.

So unless you are having problems (ora-4031s and/or latching/mutex issues) I would not be worrying about it.

should we flush the shared pool regularly?

No.

Rating

  (1 rating)

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

Comments

aitorit0, November 20, 2017 - 9:52 am UTC

In my case (work with solaris servers) ZFS protocol (used in local disks) try to eat all free memory and release it when database or another process need it.

So I ask it to an Oracle engineers in a SR:

QUESTION: This is our production server for production database. We would like to know what's will happen if we arrive to 0 MB of free MB, and ZFS need to free memory allocated... Can we have performance problems on database?

ANSWER:
Your system will likely never reach 0 MB of free MB, as it will free parts of the ZFS ARC much earlier.
If there is a high demand for RAM in a very short amount of time, however, there might be a memory shortage.
In this situation, your applications/databases will run much slower than usually, affecting your business.

So it is important that you know the expected maximum amount of memory usage by applications and set user_reserve_hint_pct accordingly, taking into account some headroom.
Of course, as per document 1663862.1, it is also important to have enough memory for kernel and ZFS ARC.
That's why user_reserve_hint_pct should normally not be set to more than 80 (Percent).
Connor McDonald
November 21, 2017 - 1:23 pm UTC

If you have allocated "n" gigabytes of RAM to the Oracle instance, then you should assume that it may consume that much.

If you have concerns about *anything* external to that (ZFS, OS, etc) then you would adjust "n" downwards accordingly.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Administration

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