|
| Paging on Solaris | 8 April 1999 |
| We have poor performance due to memory pressure running multiple instances under Solaris. We have plenty of physical memory and are only using a quarter of it for all the SGAs together. When multiple instances are active, the page out and page in rates go up, and performance goes bad. When only one instance is up, performance is great. Do Oracle instances hold on to memory (besides the SGA) and not release it? Are there specific things within Solaris that should be checked when running multiple instances? | ||
 |
I trust you are aware that Solaris does all file I/O through the paging subsystem,
so most of the page ins and outs are normally just I/O in disguise.
The real measure of memory pressure is the scan rate - reported under the sr heading by vmstat .
Nevertheless, the rest of your info is consistent with this indeed being a virtual memory problem. Because of the Solaris virtual memory architecture it degrades VERY badly under memory pressure, whereas traditional VM systems degrade much more gracefully. On the other hand its does give you somewhat better performance until you actually hit the wall. There are only two ways to address this problem. One is to buy more memory. The other is to reduce memory usage. Check out serial_reuse for significant memory savings. Other than that, converting your datafiles to raw will help. |
|
| PGA size on Solaris | 19 May 1999 |
| I have noticed that the ps command seems to include both the SGA and PGA for each server process. Is the ps command including the SGA memory in the user server processes? If I sum up the results from the ps command, is that a true picture of memory allocation, or is the single SGA memory size repeated in the user processes because they are all accessing it? | ||
|
Yes, ps includes the SGA. To get a better idea of memory use try:
/usr/proc/bin/pmap /proc/* |
|
| Variable SGA size | 6 June 1999 |
| Why is the variable size shown when I type SHOW SGA different than the shared_pool_size I set in the init.ora? | ||
| Other than the shared pool, the variable area also contains the memory arrays behind V$PROCESS, V$SESSION, V$TRANSACTION, V$LOCK and so on, plus a good number of other things. Some are fixed in size, but many are sensitive to the setting of various init.ora parameters. On some platforms each structure starts on a memory protection boundary, so some extra memory is needed for padding. Nevertheless, this does not affect the size of the shared pool, because Oracle calculates what it needs here on instance startup, and then adds the value of the shared_pool_size parameter before allocating the variable area of the SGA. |
|
| Hash join memory | 22 June 1999 |
| Where does the hash_area_size memory get allocated? Is it in the PGA like the sort_area_size? | ||
| The hash area and sort area are both allocated in the 'cursor work heap' when necessary, in small chunks up to the size limit. Cursor work heaps are allocated in the UGA, which is a subheap of the PGA in dedicated connections, or of the SGA for multiplexed connections (MTS, XA). |
|
| Redo buffers in V$SGA | 6 July 1999 |
| When I query V$SGA in one of our 8.0.5 databases, I notice that the value for "Redo Buffers" does not agree with log_buffer as set in the init.ora (and reflected in V$PARAMETER). V$SGA shows 81920; V$PARAMETER shows 8192, and V$SGASTAT shows 65536. The values yielded by V$SGA and V$SGASTAT don't even agree with each other. What's going on here? | ||
|
There is a minimum size for the log buffer in Oracle8, and if the parameter is set to something less, then it is silently enlarged.
The minimum is 4 times the largest possible db_block_size on the platform - in your case 4 * 16K.
On platforms that support memory protection there are also guard pages on each side of the log buffer. These are one memory protection unit in size - 8K each in your case. Oracle turns off permission on the guard pages with an mprotect call to set the permission to PROT_NONE, so that Oracle software bugs or hacker tricks like corrupting the stack will not be able to corrupt the log buffer. |
|
| Runtime buffers | 20 July 1999 |
| I am confused about the runtime buffers in private SQL areas and database buffer cache. Will both of them contain data blocks/data from the tables, or does only the database buffer cache contain data blocks? If so what do the run time buffers contain? | ||
| In general, blocks are read into the buffer cache (db_block_buffers) and the runtime buffers in session memory (also called the cursor work heap) are used to buffer intermediate row sources during query execution. In particular this is where the sort area goes. However, parallel queries and other operations that perform direct I/O do also have block images buffered in the cursor work heap. But this is the exception, rather than the rule. |
|
| Sort memory | 21 September 1999 |
| When is the sort_area_size chunk of memory allocated? Is it allocated as soon as a user process connects to Oracle, or is it when a user process first performs a sort? | ||
| The sort_area_size parameter is the maximum size to which a sort area can grow, but it is not allocated all at once. It grows in chunks of just a few K each, as the sort proceeds. The memory is reduced to the retained size when the sort is finished and while the sorted rows are being fetched. Thereafter it is freed entirely. Unless you use parallel query heavily, it is unlikely that your large sort area is a significant cause of memory pressure. But given that each session can use two sort areas concurrently, your worst case is worse than you might imagine. |
|
| Installed memory under HP-UX | 24 October 1999 |
| How can I find out how much memory is installed on a system running HP-UX 10.20? | ||
|
There are many possible ways, but here is one that you can use reliably in a script (assuming you have the required permissions).
echo physmem/D | adb /stand/vmunix /dev/kmem |
|
| Large pool | 25 October 1999 |
| What is the large pool? Is it the portion of the SGA that is used for pinning large objects in memory? | ||
|
It is a separate pool of memory in addition to the shared pool
designed to separate certain large memory demands from the shared pool, which is typically used for small chunks.
The large chunks concerned are those used for the UGA of MTS and XA connections,
I/O buffers for backup slaves and message buffers for parallel query.
Objects are placed in the large pool according to their usage, not their size. This differs from the reserved portion of the shared pool, in that object are located there instead of in the body of the shared pool merely on the basis of the chunk size. My book has a fuller explanation of the shared pool, reserved pool and large pool. |
|
| log_buffer in V$SGASTAT | 9 November 1999 |
| There are two entries for log_buffer in V$SGASTAT. One is the same as the parameter setting. Any idea where the other one comes from? | ||
| The log buffer consists of blocks and for each block a header is needed in the variable area of the shared pool. The second entry represents those headers. However, this will not be seen on all platforms, because what is shown in V$SGASTAT, and what gets bundled into miscellaneous is quite variable. I don't understand it myself (yet). |
|
| Java pool | 9 November 1999 |
| Do you know how to measure utilization of the Java pool under 8.1.5? | ||
| The Java pool is almost invisible at 8.1.5, except for the java_pool_size parameter itself. It is not shown in V$SGA or V$SGASTAT, nor do I know of any X$ objects for it. If you are not using Java, you can reduce it to its minimum (1M under 8.1.5, or 32K under 8.1.6), to save 19+M of shared memory. If you get Java related ORA-4031 errors, flush the shared pool and try again. If the error persists, increase the Java pool. Not very satisfactory, but that's all we can do for now. |
|
| ORA-4030 on AIX | 12 December 1999 |
| I am getting ORA-4030 when I execute a particular SQL statement. If I check ulimit -a it shows that my data segment size is "unlimited". That means I get the AIX maximum which I think is 256M. I cannot imagine that I am using that much memory. Is there some other limit on the size of the PGA? How can I resolve this problem? | ||
| Do you have the maxmem executable on that platform? If so, try it and see if you are hitting the maximum data segment size. If not, you are probably short of swap space, because there is no other limit on the size of the PGA. This is discussed towards the end of my book. If it is not a swap issue, then you will have to tune down the parameters that control PGA memory allocation. |
|
| Process heap size | 4 January 2000 |
| Your book says, "... Oracle does not attempt to reduce the size of the process data heap segment and release that virtual memory back to the operating system. ... Oracle relies on the operating system to page out any unused virtual pages if necessary." However, I have seen both the UGA and PGA shrink dynamically on 8.0.5 on SNI. | ||
| To clarify the point ... yes, the UGA and PGA do shrink, but the data heap in which they reside does not. Oracle just uses malloc() and free(). So, Oracle does not call sbrk() with a negative increment to reduce the heap when the PGA shrinks. It just calls free() which returns the free memory to the circular malloc buffer in the heap, but does not shrink the heap. |
|
| RSS (resident set size) | 7 January 2000 |
| In one of my databases, whenever a new session gets established the shadow process starts initially with memory size of 18 MB (based on the RSS column in ps). The size goes up to 50 MB if the session does any sorting or querying. Also all the background processes are larger than 25 MB. I took a PGA heapdump, but it did not show up that amount of allocated memory. Do you think it is some UNIX configuration parameter, or it is an Oracle problem? | ||
| The RSS (resident set size) shown by ps is not a valid indication of PGA size. It includes the SGA (db_block_buffers & shared_pool_size) and is sensitive to memory pressure on the system as well as to PGA size. |
|
| SAP upgrade | 15 February 2000 |
| What size should the SGA be for a SAP upgrade? The plan is to increase the SGA from approximately 350M to 1000M by increasing db_block_buffers to speed the upgrade process. Is this worthwhile, and what percentage increase in throughput could we expect? | ||
|
I'm sorry, but it is not that simple.
There is no right SGA size for all SAP upgrades.
What I can say is that a large db_block_buffers value seldom hurts.
But you may not get any performance gain.
I have recently been involved with a similar project for another client. We got big gains from doing the index creation in parallel and unrecoverable. There were also significant problems with contention for access to some hot blocks in the cache, particularly number ranges. |
|
| PRAGMA SERIALLY_REUSABLE | 6 March 2000 |
| Do you have any information about PRAGMA SERIALLY_REUSABLE? | ||
|
It is equivalent to serial_reuse.
The parameter affects the location of frame memory for SQL, and the pragma affects it for PL/SQL.
Frame memory is used during the execution phase of an SQL statement or PL/SQL program unit to track the state of the execution.
This is called "ephemeral frame" in cursor dumps.
There is also something called the "persistent frame" which I guess just tracks the state of the bind and define information.
The frame size depends on the SQL statement or PL/SQL code, but is the same for any use of that code.
Because frame memory is not sharable, it would normally be allocated in the UGA (for select statements) or CGA (otherwise).
Serial reuse places these frames into the SGA.
The idea is to save memory when large user populations execute shared code.
Because the frame memory is in the SGA, it can be reused for a later execution of the code by another user,
or flushed subject to memory pressure.
Thus the UGAs and PGAs do not grow to accommodate frame memory (which would otherwise account for much of their memory usage).
I do not know how SGA frame memory chunks are managed. There is probably a segmented array in the library object; similar to those used for child cursors and so on. However, I've not seen it in any dumps. There would also need to be a pointer in the lock and pin state objects. Because we would already be holding a library cache latch while manipulating these structures, no further concurrency control would be needed. Thus although the library cache latch might be held for longer, there is probably a net saving in CPU under serial reuse, barring latch contention. I have not however done any tests to check this. |
|
| Memory sizing | 28 March 2000 |
| When sizing the SGA, sort_area_size and hash_area_size parameters, how much memory do you usually leave for the operating system? Is there such a thing as a rule of thumb for this? I understand that NT boxes usually require at least 64M for the operating system. Also I have heard that some people leave 40% of a server's physical memory for the operating system on Unix platforms. | ||
| It depends strongly on whether direct I/O (or raw) is in use. If so, the operating system does not need memory for file system buffering of Oracle data, so more memory can be given to Oracle. Otherwise, the SGA should be limited to about 40% of physical memory. How much can be given to Oracle processes depends on the virtual memory architecture and kernel size. On NT, it is best to limit Oracle processes to only 20% of physical memory. On Solaris, 30% is OK; and on HP-UX 40% is normally OK. Of course circumstances differ, and such rules-of-thumb are no substitute for appropriate tuning. |
| Copyright © Ixora Pty Ltd |
|