Category Archives: Other Teach posts

Max PGA Research: Introduction

I am starting a set of blog posts focused on a Oracle database PGA related questions. In particular I would like to understand what is the maximum size of memory a single Oracle process could possibly allocate for sorting, hashing and other memory intensive operations. Today it is not rare to see Oracle systems running on servers with a lot of memory. Many Oracle systems have 64, 128, 512 GB or even several Terabytes of RAM today. In some cases it is beneficial of a single process could leverage a lot of memory. The question what I would like to find an answer to is: How much memory a single Oracle foreground/user’s process could possibly allocate?

I would like to make my research as practical as possible. Therefore I will use set of practical experiments that I hope will help us to make a fact base conclusions. As of now I think about the following steps:

  • Check that a process can allocate a large volume of memory.
    • Create a simple code that uses a much memory as we want, e.g. 10G.
    • Before we go into different advanced (non default) OS and Oracle parameters tuning we should make sure that a process could allocate memory using as simple test as possible.
    • If this test works, we switch to the next phase. If it doesn’t then we need to spent some time on the OS configuration.
  • Create a good test code that would allow to test the memory allocation for an Oracle session/process
    • In particular I am interested in memory allocated for sorting/hashing operations
  • Make an overview of the diagnostic tools that are available to diagnose memory allocation of an Oracle database process
    • As an example I would mention V$SQL_WORKAREA, V$SQL_PLAN, V$SESSTAT views
    • A good test case that we will create at the previous step will help us to see how to read the output from the different memory diagnostic tools and conclude if the output makes sense
  • Work through the configuration settings that have an impact on the maximum size of memory a single Oracle process could allocate.
    • As an example of such parameters I would mention: pga_aggregate_target, _pga_max_size, _smm_max_size, _realfree_heap_pagesize_hint, _use_realfree_heap and others

A few additional points I would like to mention before I finish this post:

  • I will focus on Oracle database version running on the 64 bit Linux. In particular my sandbox runs 6.5 of Oracle Enterprise Linux.
  • I am not an expert in this field. I know the basics but can miss important stuff easily. Please feel free to correct me if I got it wrong. The only reason I have started to publish this set of blogs it to learn. Please feel free to learn together with me and contribute to the research.
  • There are many more knowledgeable people on the subject in the Oracle community and they helped me already giving some hints via Social Media. Just to mention a few:
  • I hope that all together we will find the right answers to the PGA related questions and those posts will be useful for me, you and others 🙂


Max PGA Research blog posts: