|
| SQL aging | 1 April 1999 |
| I am trying to understand when and which shared SQL in the library cache is subject to aging out. Can a cursor be aged out of memory while it is open? Why, or why not? | ||
 |
Used shared pool memory chunks can be 'recreatable', 'freeable' or 'permanent'.
Only recreatable chunks are candidates for flushing from the pool.
The library cache handles for open cursors cannot be aged out, because they are freeable not recreatable.
However, the other parts of a cursor, such as the execution plan, are recreatable and can be aged out.
There are two categories of recreateable chunks - those that are pinned and those that are not. Only unpinned chunks can be flushed. However, open cursors are not pinned unless they are also being executed, so open cursors can be flushed out. When flushing, chunks are flushed in LRU order, but only until a large enough chunk has been freed. The library cache frees all chunks that have not been marked for keeping with DBMS_SHARED_POOL.KEEP. So open cursors are subject to aging out, unless kept. When a session with an open instantiation of a shared cursor wants to execute that cursor it must acquire a library cache pin on its library cache handle for the cursor. The act of establishing the pin implicitly loads the chunks for the cursor if they have been aged out. It is these reloads that the 'reloads' statistic in V$LIBRARYCACHE is counting. |
|
| Flushing the shared pool | 21 April 1999 |
| Is there any benefit in flushing the shared pool several times in succession? | ||
| No, flushing only scans unpinned recreatable chunks in the top level SGA heap. If one of those chunks happens to be a subheap, it will be flushed entirely. There is no other way in which subordinate chunks can be flushed, and thus no possible benefit from repeated flushing. |
|
| Shared pool memory reuse | 9 June 1999 |
| Do I understand correctly that once part of shared pool memory has ever been used for the library cache then this memory will never become available for any other part of shared pool but the library cache? | ||
| Memory, once allocated to a subheap, is normally only available within that subheap, until the entire subheap has been freed. However, the library cache is not just one subhead, but many. The component subheaps of the library cache are freed independent of one another, and so the amount of memory allocated to the library cache overall can and does shrink under changes in memory pressure. |
|
| Free memory | 23 June 1999 |
|
I am calculating free memory in the shared pool as
select bytes from v$sgastat where name = 'free memory'; Please let me know if this is right. | ||
| What you have is correct. However, the 'free memory' statistic does slightly overstate the free memory. I think it includes some of the padding for alignment purposes as being free. I suppose it is technically free, but it is certainly not usable. |
|
| Reserved stopper | 28 July 1999 |
| I was looking at V$SGASTAT. My question is about the rather significant component 'reserved stopper' (size of 1800040 bytes). | ||
| There is a 20 or 24 byte (depending on version) stopper chunk at each end of the reserved part of the shared pool. What you are seeing is the entire reserved pool, plus the two stopper chunks. The stopper chunks prevent otherwise contiguous free space from the main part of the shared pool and the KSM stack respectively from being coalesced with free space in the reserved pool during a flush. |
|
| Shared pool too large | 30 September 1999 |
| How do I know if the shared pool is much larger than required? Using the hit ratios I can only tell if the shared pool is too small. | ||
|
For a start you can use the APT script shared_pool_spare_free.sql.
Beyond that, you can take a heap dump and have a look at what's there.
The syntax is
alter session set events 'immediate trace name heapdump level 2'; |
|
|
Ok, would you advise to make the shared pool smaller for a database with these values? SHARED_POO SPARE_FREE WASTAGE ---------- ---------- ------- 100000000 32457144 32% |
| Yes, if this instance has already reached its normal workload, then you can reduce the shared pool by at least the "wasted" amount, without impact. However, I would recommend that you go further to reduce the amount of unprofitable material cached in the shared pool. That is, in this case I would halve the shared pool as a first action. |
|
| Shared pool fragmentation | 2 October 1999 |
| How you can tell if your shared pool is fragmented? | ||
| The true indication of fragmentation is the lengths of the shared pool free lists. The APT script shared_pool_free_lists.sql looks at X$KSMSP to get that information. However, the acceptable length depends on the degree of concurrent access. You can have a badly fragmented shared pool but low concurrency and virtually no performance impact. But you can have a moderately fragmented shared pool with high concurrency and terrible latch contention. So, I would suggest that you look at the latch sleep rates on the shared pool and library cache latches, rather than at the lengths of the free lists, to assess whether you have a fragmentation problem. |
|
| Shared pool too big | 20 October 1999 |
| We have recently migrated to Oracle8i, but it seems the performance is quite slow. The machine is large in terms of disks, CPU, and memory. Also, we have moved to IPC since we have batch jobs running on the database, and no users connected over the network. Could you tell me what I should diagnose on the database and the Solaris 2.6 server to find the bottlenecks? A utlestat report is attached. | ||
|
This is a classic case of too large a shared pool causing extreme latch contention on the shared pool latch.
Note the following statistics from your report.txt .
CPU used by this session 11639.58 (seconds) latch free 25353.58 (seconds)That is, you are spending twice as long waiting for latches than doing useful work, and much of your CPU time would be consumed while spinning. Note also where those latch sleeps are ... shared pool 4292776 (sleeps) library cache 252949 (sleeps)That is, 94% of these sleeps are on the shared pool latch. The rest are secondary sleeps on the library cache latches. The problem is that shared pool latch is being held too long while searching the free lists, because the free lists are too long. The free lists are too long, because the shared pool is too big. You need to make appropriate use of DBMS_SHARED_POOL.KEEP to mark valuable objects for keeping, and reduce the size of your shared pool dramatically. |
|
| Could it be that there is not enough space in the shared pool? |
| No, the ideal situation is to have a small shared pool in which all the important reusable objects are marked for keeping and in which other objects are recycled quickly. People often attempt to increase the shared pool under these circumstances rather than reducing it. Normally, it appears to have worked for a while, because it takes longer for the LRU lists to begin to be flushed. But once that happens, you immediately get longer free lists and worse contention for the shared pool latch than would previously have been the case. |
|
| Shared pool size | 24 October 1999 |
| I am trying to size my shared pool by following the procedure recommended in several Oracle books. The idea is to temporarily allocate a very large shared pool, run the application, and then add up the sharable memory in V$DB_OBJECT_CACHE and V$SQLAREA and so on, and then reduce the shared pool to the calculated size. My problem is that the calculation shows that my large shared pool is 125% full! | ||
| This method of estimating an appropriate shared pool size is only marginally valid. Library cache handles can be kept in cache long after the sharable memory components have been aged out. You are counting memory that no longer exists. Also, depending on the SQL you are using, you may be counting some memory twice, because cursors are also visible in V$DB_OBJECT_CACHE. |
|
| 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. |
|
| Shared pool usage | 2 November 1999 |
| Is there a way to see how full the shared pool is at any one time? | ||
| The available memory in the shared pool at any point in time is the sum of the free memory statistic in V$SGASTAT, plus the sum of the sizes of the unpinned recreatable chunks of the shared pool LRU lists. You can get the length of these LRU lists from X$KGHLU, but the only way that I know of to get the sizes is with a heapdump at level 2. |
|
| ORA-6502 and the shared pool | 17 November 1999 |
| We have a Forms application that calls a procedure in a package. Most of the time there are no problems, but sometimes it returns 'Numeric or value error'. My first thought of course was a PL/SQL variable being overloaded. When the operation is immediately retried, the same problem occurs. When the shared pool is flushed first, it works fine. I think this is not a problem with a PL/SQL variable, but with the shared pool. | ||
|
Intermittent 6502 errors normally indicate a problem with the initialization of bind variables, or package variables.
However, there is a well-known bug (516664 fixed in 7.3.3.4)
in which shared pool memory pressure or manually flushing the shared pool can cause this error.
There are also other candidate bugs such as 535798 fixed in 7.3.4.
To diagnose the situation further, you will need to send Oracle Support an errorstack.
To do this, set the following event
event = "6502 trace name errorstack level 10" and then restart the instance. When the error strikes a trace file will be produced in user_dump_dest. Send it to Support, and they should be able to help you. |
|
| Shared pool size again | 18 November 1999 |
| You suggested that I reduce my shared pool size to prevent contention for the shared pool and library cache latches. However, it seems that the shared pool size may actually need to be bigger, not smaller. Presently my SGA is 1.8G, and we have good performance for 4 or 5 days while there is still free memory in V$SGASTAT. Performance only goes bad when that free memory is used up. If you can suggest something, please let me know. | ||
| What you are saying is consistent with my original diagnosis. When Oracle eventually starts to age chunks out of the shared pool, the shared pool free lists get very long, and that is what causes the latch contention. The length of time for which the shared pool latch is held is often proportional to the length of one of the shared pool free lists, and a library cache latch is often held while waiting for the shared pool latch, which causes secondary contention for the library cache latches. The solution is to make appropriate use of DBMS_SHARED_POOL.KEEP and then reduce the shared_pool_size . |
|
| Shared pool fragmentation | 12 December 1999 |
| I'm still unclear how to determine if the shared pool is fragmented. I've downloaded several of your scripts and executed them, but I am unclear on how to interpret the results. We are currently running 7.3.4.2. | ||
| If you have large number of free chunks on any of the free lists, then your shared pool is fragmented. Large means somewhere between 2000 and 5000 chunks on any one freelist. The severity of the fragmentation is characterized both by the free list length, and also by the degree to which it is improved by a flush. Really bad fragmentation does not get much better even when you flush the shared pool. |
|
| SGA heapdump | 12 December 1999 |
|
How do I interpret the output from
alter session set events 'immediate trace name heapdump level 2'; in terms of establishing how much unused space there is? | ||
| Look for the large "permanent" chunk. Alongside is says how much is actually allocated. The rest is spare free space. Then look at the bottom of the free lists section. It will give you a total of the free chunks. Then look at the bottom of the unpinned recreatable section, for the total available for immediate reuse. The actual number is slightly larger, because of the way that the dictionary cache lru mechanism works. |
|
| shared pool latch again | 11 January 2000 |
| Can contention for the shared pool latch be due to the bug that results in long library cache hash chains? | ||
| There have been some bugs in this regard in some earlier versions of Oracle. However, from 7.3 onwards the hash value is computed on the entire text of the statement, and the hash table expands dynamically to ensure that the average hash chain length remains below 2. That is, the size of the shared pool has no effect on the length of the library cache hash chains. Also, the length of the library cache hash chains can have no negative impact on contention for the shared pool latch, because the latch levels (5 & 7 respectively) dictate that the shared pool latch is never held while attempting to take a library cache latch. (There is one possible positive impact, namely that contention for the library cache latches may limit contention for the shared pool latch by introducing an earlier bottleneck). |
|
| Latch contention in 8.1.5 | 20 January 2000 |
We've recently upgraded from 7.3.4 to 8.1.5, and we've started to see much more latch contention than before.
It appears that most of the problems are with the library cache and shared pool latches.
We also have some known nasty queries that need to be fixed that are affecting the cache buffers chains latches as well.
By halving the size of the shared pool and being more aggressive in keeping program objects in the shared pool,
the performance degradation has improved significantly.
Is this the right (and only) approach to tuning the shared pool?
Why is 8i behavior so drastically different (and less forgiving) than previous versions?
Finally, what is the transaction allocation latch and is it a significant part of the problem here?
Thanks in advance for any insight you can provide.
A utlestat report is also attached.
LATCH TYPE IMPACT SLEEP RATE WAITS_HOLDING_LATCH LEVEL# ------------------------------ ----------- ---------- ------------------- ---------- library cache 6701580 0.32% 3280652 5 shared pool 648923 1.01% 156976 7 cache buffers chains 374455 0.00% 57207 1 transaction allocation 252575 0.08% 10173 8 cache buffers lru chain 20480 0.01% 431821 3 multiblock read objects 14718 0.01% 48837 3 row cache objects 5705 0.01% 30671 4 session allocation 3233 0.01% 4498 5 latch wait list 2482 0.21% 876 9 | ||
|
Well done on reducing the shared_pool_size.
Most people go the other way.
There is lots more that can (and sometimes must) be done about shared pool and library cache tuning,
but keeping and getting the size right are very important.
A lot of my consulting assignments involve solving big problems in this area.
There are a few bugs in 8.1.5 that can make it significantly worse in this area than 7.3.4 was.
Contact Oracle support to see if you are hitting any of them.
It is also possible that you may have increased the number of users or transaction rates,
or changed some significant parameters inadvertently.
The transaction allocation latch protects the transaction state object free list (see page 44 in my book). I am not aware of any relationship between this latch and shared pool and library cache issues. However, the activity against this latch is extraordinary and warrants investigation. |
|
| Keeping packages | 1 February 2000 |
| With Oracle8, is it still necessary to execute or compile a package to get it loaded into memory before keeping it with DBMS_SHARED_POOL.KEEP? | ||
| From release 7.3, marking an object for keeping loads it if necessary. |
|
| ORA-4031 | 19 February 2000 |
| I am getting ORA-4031, "unable to allocate 208 bytes of shared memory ...", consistently when running a particular function. It does not even help to flush the shared pool beforehand. V$SGASTAT shows that I have plenty of free memory available, and your shared_pool_free_lists.sql script shows that it is not fragmented. But when I run this function, it fails to get a tiny bit of memory, and afterwards there is still plenty of free memory available. Any ideas what is going on? | ||
|
The SQL statement obviously needs a lot of memory.
It is possible to have a lot (but not enough) free memory available beforehand, then to use it all up, fail with ORA-4031,
release the memory, and have even more free memory available afterwards.
Part of the reason for this is that in a desperate attempt to find enough memory, lots of other stuff is flushed out.
To prove that the error is not spurious, you need to take a shared pool heapdump as the error occurs.
To do so, set the following event in your init.ora file.
event = "4031 trace name heapdump forever, level 2"An interesting aside is that it is sometimes possible to fail to get say 208 bytes even when there is a somewhat bigger chunk available. This is because the chunk normally needs to be allocated in a subheap, and so that subheap needs to allocate another extent. The extent needs an extent header, and is subject to a minimum extent size. So the free chunk needed in the parent heap is larger than reflected by the error message. Subheaps are nested to a possible depth of 4, and so it is possible to require a free chunk in the top level heap (the shared pool itself) as much as 226 bytes larger than the error message reports. |
|
|
I took your advice and got a heapdump of the shared pool.
I can see the type, size, and class of the memory chunks (most are freeable, sql area), but where do I go from here?
What can I learn from this pile of data?
Also, do you have any suggestions for good resources to learn more about heaps, subheaps, and other stuff like this? For Unix someone recommended The Design of the UNIX Operating System by Bach, but of course I haven't had the time to read it yet. |
|
The point of taking the heapdump was to convince yourself that the shared pool was genuinely out of memory when you got the error,
and that it would be necessary to make the shared pool bigger.
To get that much information out of the heapdump is easy.
Just look down near the end where the free chunks and unpinned recreatable chunks are listed.
There will be no free chunks of the size you required (with due allowance for headers and minimum extent sizes)
and there should be very little if anything on the lists of recurrent and transient unpinned recreatable chunks.
To understand the rest of the heapdump is a little more challenging, because you would need to know what all the abbreviations stand for.
My book covers the general structure of the heaps and subheaps,
but does not attempt to enumerate all the stuff that you might find in them, and the significance of those findings.
For that you will have to wait for my book called The Definitive Guide to Oracle Internals
due to published sometime after my retirement in 2025!
The Maurice Bach book is good, but a little dated now. There are numerous more recent offerings in the area of Unix internals that you could get to supplement Bach, and if you're serious, the source code of the open source Unix versions can also be helpful. Unfortunately, there is nothing equivalent on the Oracle side of things. |
|
| X$KSMLRU | 1 March 2000 |
| Where can I find information about the X$ tables? The one I need a description of is X$KSMLRU. | ||
| There is a note on MetaLink called "Library Cache and Shared Pool Tuning" that contains a description. However, it is not exactly as that note describes, because only the largest chunk allocation is guaranteed to be captured, as my book explains. The other 9 rows should be taken with a pinch of salt. I actually tried to log a bug about it, and got an email back from the responsible developer. He outlined his algorithm in pseudo-code, and explained that it works exactly as he intended. That is, he felt that the note was in error in describing his X$, rather than vice versa. |
|
| Reserved pool | 2 March 2000 |
| How can I find out what objects are currently in the shared pool reserved area? | ||
| You can do this using the address. X$KSMSPR lists all the chunks in the reserved pool, and KSMCHPTR is the address of the chunk. X$KGLOB lists all the library cache objects, and KGLHDADR is the relevant address here. However, you need to allow an offset of 28 bytes (may be version dependent) from the address of the chunk header to that of the library cache object. |
|
| Shared pool sizing | 4 March 2000 |
I was reading about the shared pool in your book, and ran the scripts on one of my databases.
Here are the results.
SQL> @shared_pool_lru_stats
RECURRENT TRANSIENT FLUSHED PINS AND ORA-4031 LAST ERROR
CHUNKS CHUNKS CHUNKS RELEASES ERRORS SIZE
--------- --------- --------- --------- --------- ----------
15904 70647 218289 7185470 0 0
SQL> @shared_pool_free_lists
BUCKET FREE_SPACE FREE_CHUNKS AVERAGE_SIZE BIGGEST
--------- ---------- ----------- ------------ ---------
0 380560 7355 51 72
1 340752 3851 88 96
4 848 1 848 848
5 1320 1 1320 1320
6 2104 1 2104 2104
9 7039280 303 23231 32744
10 6695504 147 45547 65504
11 7245200 77 94093 129440
12 6686200 39 171441 249800
13 4871960 13 374766 505488
14 1437616 2 718808 906456
SQL> @shared_pool_spare_free
SHARED_POOL_SIZE SPARE_FREE WASTAGE
---------------- ---------- -------
251658240 0 0%
SQL> @reserved_pool_hwm
RESERVED_SIZE HIGH_WATER_MARK USAGE
------------- --------------- -------
25165920 24944240 99%
According to your book, the shared pool is oversized
because the number of transient chunks is greater than 3 times the number of recurrent chunks.
But if you consider that the ratio of flushed chunks to other LRU operations (pins and releases?) is 1:32,
then that should mean that the shared pool is too small.
I get a conflicting result.
I am not sure I understand.
Also as per the shared_pool_spare_free.sql script, the wastage is zero and no spare memory is left.
Does that support the idea that my shared pool is too small?
Could you please explain what I am missing?
| ||
|
You are misunderstanding the statement that,
"if the ratio of chunk flushes to other LRU operations is more than 1 in 20, then the shared pool is probably too small."
Your ratio of 1 in 32 is less than 1 in 20, not more than it.
You may see this more clearly if you express the numbers in decimal as 0.03 and 0.05 respectively.
The key indicator is the length of free lists 0 and 1. These suggest that your shared pool is indeed too large. Of course, this is only really a problem if your concurrency is high enough to induce contention for the shared pool latch. So, if you are seeing any contention for that latch, then I would recommend a reduction in the shared pool size, otherwise, it is not really an issue. Again, be careful to ensure that you have suitable KEEPing procedures in place before reducing the shared pool. |
|
| shared pool latch contention | 16 March 2000 |
|
One of the reasons that shared pool latch contention can become so severe where applications fail to properly use bind variables
appears to be the length of the lists of free memory chunks which must be scanned when making space for the new object in the shared pool.
As these free lists are organized by size categories, it occurs to me that perhaps the problem could be mitigated
if the new objects could be pseudo-randomly coerced into different size categories to balance the load.
This could be accomplished by injecting strategically sized comments into the SQL before sending it to Oracle.
Am I completely off my rocker on this?
Of course Oracle is claiming to remedy this with the new cursor_sharing initialization parameter in 8.1.6. Have you tried this out yet? | ||
|
The use or otherwise of bind variables is logically a different problem to that of shared pool free space fragmentation.
The 8.1.6 cursor_sharing parameter is doing a wonderful job for one of my clients whose application does not use bind variables.
It is still important however to get the shared pool size right to prevent fragmentation.
I believe that 8.2 will address this problem by rounding up all memory allocations to one of a set of fixed sizes. This may waste a lot of shared pool memory, but will kill the fragmentation issue entirely. Controlling (or randomizing) the sizes of SQL statements is not feasible at present, because most of the chucks that affect the problem presently are not actually from the SQL area. |
|
| I thought that not using bind variables would have a relationship to shared pool fragmentation in that since each statement is new, the heap manager would be called upon much more frequently than otherwise. |
| Yes, it certainly increases the work, but if the pool is small then the fragmentation does not get too severe. I suppose it would be best to say that they are interrelated, although logically distinct issues. |
|
| Shared pool reserved size | 22 March 2000 |
Running your reserved_pool_hwm.sql script, I get the following
RESERVED_SIZE HIGH_WATER_MARK USAGE
------------- --------------- -------
25000072 24467280 98%
But, when I query V$SHARED_POOL_RESERVED the total free space is 21331652.
From the first query it seems that we are running out of space in the reserved pool,
but the second one shows that there is still plenty of free space.
Could you please tell me what I am missing here?
| ||
| V$SHARED_POOL_RESERVED shows you how full the reserved pool is at the exact time that you happen to be looking. Our script tells you how close the reserved pool got to being full at some point in time earlier in the life of the instance - that is, it show the highwater mark. A high highwater mark is not of concern, because for no apparent reason Oracle appears to sometimes grab a big chunk of reserved memory and then release it. When this happens, the chunk grabbed is almost the whole of the reserved pool regardless of its size. |
| Copyright © Ixora Pty Ltd |
|