Oracle 12数据库32GB专用Linux服务器memory_target/memory_max_target配置建议咨询

Alright, let's break this down for your 32GB dedicated Linux server running Oracle 12c. I’ve spent plenty of time tuning these settings for similar dedicated environments, so here’s what I recommend:

First, a quick recap: memory_max_target is the hard upper limit for all Oracle memory (SGA + PGA combined), while memory_target is the initial dynamic allocation Oracle can adjust between SGA and PGA as workload demands change.

For a 32GB dedicated server:

• memory_max_target: Set this to 28G. We reserve 4GB for the Linux OS to handle kernel processes, file system cache, and essential background utilities—this prevents the OS from swapping under normal load, which is critical for Oracle performance.
• memory_target: Start with 26G. Leaving a 2GB buffer between this and memory_max_target gives Oracle room to handle sudden memory spikes (like large batch jobs or unexpected query loads) without hitting the hard limit immediately.

These tips will help you get the most out of your memory allocation and avoid common pitfalls:

• Lock Oracle memory to prevent swapping: Edit /etc/security/limits.conf to set memory locking limits for the oracle user. This ensures Oracle’s allocated memory stays in physical RAM instead of being swapped to disk. Add these lines:

  oracle soft memlock 29360128
  oracle hard memlock 29360128
  

  (That’s 28GB converted to kilobytes, matching our memory_max_target.) Restart the oracle user session and verify with ulimit -l.
• Tune OS memory settings: Disable any unnecessary Linux services to free up OS memory. Check available memory with free -h—you should see at least 3-4GB free for the OS after Oracle starts. Configure swap to ~4GB as a safety net (even if you don’t expect to use it, it’s better than crashing under extreme load).
• Monitor and adjust dynamically: Use Oracle’s v$memory_dynamic_components view to track how memory is split between SGA and PGA. If you notice consistent PGA shortages (e.g., frequent sort-related errors), you can bump memory_target up to 28G dynamically with:

  ALTER SYSTEM SET memory_target = 28G SCOPE=BOTH;
  

  Just remember memory_max_target can’t be increased without a database restart.
• Avoid conflicting parameters: If you’ve previously set sga_target or pga_aggregate_target, note that memory_target takes precedence in dynamic memory management. It’s best to let Oracle handle the split unless you have a specific workload (like a data warehouse that needs more PGA for sorting/aggregation) where manual tuning makes sense.
• Test after changes: After adjusting settings, run your typical workloads and check performance metrics (like query response times, wait events) to ensure the memory allocation is working as expected.

内容的提问来源于stack exchange，提问作者Basil A