Max PGA Research: Check that a process can allocate a large volume of memory

As mentioned in the Max PGA Research: Introduction post I would like to understand how much memory a single Oracle foreground (user) process can allocate for sorting, hashing or other memory intensive operations.

The first step that I am suggesting is using a simple PL/SQL code check that your OS/DB are configured to allocate a large volumes of memory.

For that purpose I have created a simple PL/SQL code that could “eat” as much memory as we want. The code consist of three parts. The first one is a PL/SQL package that contains the mem_tab structure that allows to store a character strings array. Note that I use a PL/SQL package to make the memory allocation persistence across SQL calls. Alternatively you can use anonymous PL/SQL block. However in that case the memory will be deallocated as the call is finished:

create or replace package pgares_mem_sort
is
type t_mem is record(txt varchar2(4000));
type t_mem_tab is table of t_mem index by binary_integer;
mem_tab t_mem_tab;
end;
/

The second part is a code that based on an input parameter value fills package’s array with values:

declare
c_count number := 1024*&1;

BEGIN

— Memory consumption part
FOR f IN 1..c_count LOOP
pgares_mem_sort.mem_tab(f).txt := substr(lpad(f,1000,’-‘),0,1024);
END LOOP;

END;
/

The third part are the few lines of code that gathers a few important statistics. The SQL script that consumes as much memory as we want is available here.

The following is the execution example. The intention here is to let the session to consume 10GB+ of RAM.

SQL> @Desktop/SQL/mem_killer_c.sql 10240
declare
*
ERROR at line 1:
ORA-04030: out of process memory when trying to allocate 16328 bytes (koh-kghu
sessi,pl/sql vc2)

Elapsed: 00:01:15.65
SQL>

In my default Linux sandbox environment the code has failed. It is useless to hope that an Oracle process will be able to allocate 10G of memory if this simple PL/SQL code fails. We need to put some efforts to configure Linux to allow the larger memory allocation.
The following is the pmap Linux utility’s output executed against the Oracle foreground process. It appears there is a 4GB memory limit by default (thanks to Kevin Closson and Freek D’Hooge for the hints).

root@host:~ # pmap -x 23831 | grep "rw--- zero" | awk '{print $3}' | sort | uniq -c | sort -k1n
1 56
1 60
1 88
3 0
65419 64
root@host:~ # bc <<< 65419*64/1024
4088
root@host:~ #

A single Linux process can allocate 65419 chunks of 64k memory regions by default. This limits total memory that a process can allocated to ~4GB.

It appears that it is relatively simple to increase the limit. You just adjust the vm.max_map_count linux kernel parameter and set _realfree_heap_pagesize_hint init.ora hidden parameter:

root@host:~ # cat /proc/sys/vm/max_map_count
65530
root@host:~ # echo vm.max_map_count = 262144 >> /etc/sysctl.conf
root@host:~ # sysctl -p
...
vm.max_map_count = 262144
root@host:~ # cat /proc/sys/vm/max_map_count
262144

SQL> alter system set “_realfree_heap_pagesize_hint”=262144 scope=spfile;

System altered.

After an instance restart repeat the simple memory allocation test.

SQL> @Desktop/SQL/mem_killer_c.sql 10240
var size = 10240Mb
UGA 0 4095 4095 207
PGA 2 4095 4093 207

PL/SQL procedure successfully completed.

Elapsed: 00:03:27.64
SQL>

The following is pmap utility’s output

root@host:~ # pmap -x 37590 | grep "rw--- zero" | awk '{print $2}' | sort | uniq -c
48510 256
1 49664
root@host:~ # bc <<< 48510*256/1024/1024
11

As you noticed the process has successfully allocated 10+ GB of memory.

Before is finish this blog post I would like to make the following comments:

  • Now we have a simple test to execute within an Oracle Instance to check how much memory an Oracle process could possibly allocate. If the test fails we should focus on tuning you OS before taking the next steps (see Max PGA Research: Introduction post).
  • It looks like as alternative to setting _realfree_heap_pagesize_hint parameter we can set _use_realfree_heap=TRUE parameters. In such case an Oracle process will use 64k regions but would be able to use more than 64k of those.  The vm.max_map_count kernel parameter would need to be set anyway.
  • The other misery that is not clear for me as of now is the fact that Oracle reports that there is just 4095MB of PGA allocated by the process. However the pmap utilities output clearly indicates that 10GB of memory has been allocated. My script uses the following SQL to retrieve the PGA size for the session


select value INTO V_PGAE from v$mystat where statistic#=(select STATISTIC# from v$statname where name = 'session pga memory max');

Advertisements

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 11.2.0.4 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 🙂

Yury

Max PGA Research blog posts:

About Apress Access OR How to get access to an entire Apress library

Recently Apress representatives reached me out and asked what do I think about new product called “Apress Access”. As I have quite several Apress books on my bookshelf, I was interested and agreed to have have a look and share my thoughts.

In nutshell: “Apress Access” is an subscription that you buy for a year. You get a full access to most Apress books for that year. The only exceptions are those titles that are too old to be formatted for Apress’ reader and books that have not been fully published. For those of us who buy/read 4-6 Apress technical books, it could be a good option to explore. You can find more details over here.

Practical_Oracle_Database_Appliance

My personal thoughts and observations are below.

Advantages:

  • There is one huge advantage of this model. Instead of buying one or several books you get an access to an entire Apress library. And there are many good books in the Apress library. In addition to access to books relative to your immediate interest area, you can explore other technical areas for the same price. That means you can learn more 🙂
  • The books are available via a web-based Apress reader. It works on both workstations and portable devices. The reader has all basic functionality. You may learn more from the this Apress video.

Disadvantages:

  • As of now, to read books you need internet access. This may not be too convenient if you would like to read books while you are traveling (e.g. via air)
  • Apress Access uses its own reader, so you cannot use some of the devices you are using to read a book at the moment. For example, you will not be able to use your Kindle reader to view these files.
  • In my opinion, the Apress Access reader may not be that convenient to use on small devices like a smart phone. However, who really reads books on a smart phone? 🙂 I should mention that the reader works well on a tablet.

To summarize: I think some of us may find the Apress Access subscription model a very attractive option that allows access to a very good library of the technical books. I am sure that there will be others who are used to other ways of reading books and therefore this option will not be that convenient for them. There will be people who will use both traditional and the subscription models at the same time.  In any case I am glad to see that Apress team are innovative and think about providing more options for accessing great technical content.

I would be interested what do you think about this option. Please feel free to leave a comment.

Yury

Joining GOOGLE to Make this world even better place

My “Desire to Grow and Make this world even better place” blog post didn’t answer the main question many of you may have. This post does.

I am proud to announce that I have started to work at Google today. This company have a mission to make the world a better place. It have changed IT landscape and the way people use information and interact with computers. Right from the beginning of my career I have admired Google and could just dream about today. Such products like Google Maps and GMail made a significant positive impact how I personally have used information. Now I have got an opportunity to be part of it.

Image

I think that I just received a credit. There is a long and exiting journey in front of me now. Time will show if I will be up to the level of the great company I just joined.

Q&A: Let me answer some questions you may have

Q: Does that mean Yury moves to US?

A: Yes. It does. However it will not happen immediately. There is a big personal project in front of me and my family now. We are planning to complete it in few months time. I hope to minimize an impact from the relocation on my family as much as possible. I appreciate any useful hints and advice from your side my friends 🙂

Q: What Yury going  to do at Google ?

A: I am still to find about of information I can release. One thing is for sure. I will leverage my 17 years Oracle Database Administrator experience 🙂

I am very exited about the change!

Yury

Desire to Grow and Make this world even better place

I worked at Pythian for last 4 years. I know that some of you will ask what happened and why did I left. This post gives some answers.

In short: I have got and accepted an offer that allows me to make the world even better place and grow even more professionally.

Pythian is a fantastic company with a great mission to deliver the best data management services in the industry. There are great and friendly people working for the company. I will miss the spirit and the team a lot. That is for sure. I have witnessed a very rapid grow during last 4 years. I am very grateful and proud that Pythian gave me an opportunity to be part of the team and grow professionally along with the company. I have learned a lot. I made more friends than ever before. I had an opportunity to fail and succeed. I would never get to the point where I am today without Pythian. If I would have an opportunity get back in time and change something, I would change nothing. It was/is an experience that I suggest to leave through to anyone.

Working at Pythian I learned that grow is a good thing. Pythian have good ambitions to grow. So am I 🙂 I have received a good offer and decided to use it. I believe that my new job will allow me to grow professionally even more rapidly, continue to contribute to Oracle community even more then before and make this world a better place to leave in. I do not want to give all the details away about my new employment now, as I just about to start it tomorrow. However, I hope to release all the details in few days’ time.

WP_20140120_13_21_24_Pro

FAQ: I think that a few of you will have more questions after reading it. Let me cover some of possible questions:

Q: Is it “Oracle” or “Enkitec”?

A: Nope 🙂 Those are great companies. However, I am not joining any of those.

Q: Many good people left Pythian. What is happening?

A: I thinks nothing too serious happening. Pythian is a great company and doing well. It just called Life! Some good folks joining good companies. Some decides to leave. Some re-join previous companies they worked some time ago. Without a change world would not progress. As for example if I would not leave TietoEnator Alise (the company I grew up with and worked for 10 years) I would not join Pythian. There are many gifted people working for the company, many will join it as opportunities arisen. I am sure it has a great future.

Q: What is going your relationship with Pythian crew?

A: Well, I am missing them already. I hope to stay a good friend of Pythian. I respect people working there very much. I am sure that our paths will cross many times in the future. I am planning to be part of Oracle Conferences in the future. If you want to have a good time on a conference then look for Pythian team and join events they organize. There is a good chance that you will meet me there too 🙂

Q: Are you moving to USA?

A: Not yet. But it looks like I will. As of now the ETA is 6 months.

All right folks! It is time for me to get ready for the next step in my career. See you around and stay tuned for more news from my side 🙂

Yury