Thursday, October 7, 2010

Shared Pool Fragmentation


  • Shared Pool Fragmentation:
    Every time a SQL or PL/SQL statement needs to be executed the parse representation is loaded in the library cache requiring a specific amount of free contiguous space. The first resource where the database scans is the free memory available in the shared pool. Once the free memory is exhausted, the database looks for reusing an already allocated piece not in use. If a chunk with the exact size is not available, the scan continues looking for space based on the following criteria:
- The chunk size is larger than the required size
- The space is contiguous
- The chunk is available (not in use)
Then that chunk is split and the remaining free space is added to the appropriate free space list. When the database is operating in this way for a certain period of time the shared pool structure will be fragmented.
When the shared pool is suffering fragmentation ORA-04031 errors (when the database cannot find a contiguous piece of free memory) may occur. Also as a concequence , the allocation of a piece of free space takes more time an the performance may be affected (the "chunk allocation" is protected by a single latch called "shared pool latch" which is held during the whole operation). However, ORA-4031 errors don't always affect the performance of the database.
If the SHARED_POOL_SIZE is large enough, most ORA-04031 errors are a result of dynamic sql fragmenting the shared pool  This can be caused by:
o Not sharing SQL
o Making unnecessary parse calls (soft)
o Setting session_cached_cursors too high
o Not using bind variables
To reduce fragmentation you will need to address one or more of the causes described before. In general to reduce fragmentation you must analyze how the application is using the shared pool and maximize the use of sharable cursors.
The following views will help you to identify non-sharable versions of SQL/PLSQL text in the shared pool:
    • V$SQLAREA View
This view keeps information of every SQL statement and PL/SQL block executed in the database. The following SQL can show you statements with literal values or candidates to include bind variables:
SELECT substr(sql_text,1,40) "SQL",
count(*) ,
sum(executions) "TotExecs"
FROM v$sqlarea
WHERE executions < 5
GROUP BY substr(sql_text,1,40)
HAVING count(*) > 30
ORDER BY 2;
Note: The number "30" in the having section of the statement can be adjusted as needed to get more detailed information.
    • X$KSMLRU View
There is a fixed table called x$ksmlru that tracks allocations in the shared pool that cause other objects in the shared pool to be aged out. This fixed table can be used to identify what is causing the large allocation.
If many objects are being periodically flushed from the shared pool then this will cause response time problems and will likely cause library cache latch contention problems when the objects are reloaded into the shared pool.
One unusual thing about the x$ksmlru fixed table is that the contents of the fixed table are erased whenever someone selects from the fixed table. This is done since the fixed table stores only the largest allocations that have occurred. The values are reset after being selected so that subsequent large allocations can be noted even if they were not quite as large as others that occurred previously. Because of this resetting, the output of selecting from this table should be carefully kept since it cannot be retrieved back after the query is issued.
To monitor this fixed table just run the following:
SELECT * FROM X$KSMLRU WHERE ksmlrsiz > 0;
This view can only be queried by connected as the SYS.

    • X$KSMSP View (Similar to Heapdump Information)
Using this view you will be able to find out how the free space is currently allocated, which will be helpful to undrestand the level of fragmentation of the shared pool. As it was described before, the first place to find a chunck big enough for the cursor allocation is the free list. The following SQL shows the chunks available in the free list:
select '0 (<140)' BUCKET, KSMCHCLS, KSMCHIDX, 10*trunc(KSMCHSIZ/10) "From",
count(*) "Count" , max(KSMCHSIZ) "Biggest",
trunc(avg(KSMCHSIZ)) "AvgSize", trunc(sum(KSMCHSIZ)) "Total"
from x$ksmsp
where KSMCHSIZ<140
and KSMCHCLS='free'
group by KSMCHCLS, KSMCHIDX, 10*trunc(KSMCHSIZ/10)
UNION ALL
select '1 (140-267)' BUCKET, KSMCHCLS, KSMCHIDX,20*trunc(KSMCHSIZ/20) ,
count(*) , max(KSMCHSIZ) ,
trunc(avg(KSMCHSIZ)) "AvgSize", trunc(sum(KSMCHSIZ)) "Total"
from x$ksmsp
where KSMCHSIZ between 140 and 267
and KSMCHCLS='free'
group by KSMCHCLS, KSMCHIDX, 20*trunc(KSMCHSIZ/20)
UNION ALL
select '2 (268-523)' BUCKET, KSMCHCLS, KSMCHIDX, 50*trunc(KSMCHSIZ/50) ,
count(*) , max(KSMCHSIZ) ,
trunc(avg(KSMCHSIZ)) "AvgSize", trunc(sum(KSMCHSIZ)) "Total"
from x$ksmsp
where KSMCHSIZ between 268 and 523
and KSMCHCLS='free'
group by KSMCHCLS, KSMCHIDX, 50*trunc(KSMCHSIZ/50)
UNION ALL
select '3-5 (524-4107)' BUCKET, KSMCHCLS, KSMCHIDX, 500*trunc(KSMCHSIZ/500) ,
count(*) , max(KSMCHSIZ) ,
trunc(avg(KSMCHSIZ)) "AvgSize", trunc(sum(KSMCHSIZ)) "Total"
from x$ksmsp
where KSMCHSIZ between 524 and 4107
and KSMCHCLS='free'
group by KSMCHCLS, KSMCHIDX, 500*trunc(KSMCHSIZ/500)
UNION ALL
select '6+ (4108+)' BUCKET, KSMCHCLS, KSMCHIDX, 1000*trunc(KSMCHSIZ/1000) ,
count(*) , max(KSMCHSIZ) ,
trunc(avg(KSMCHSIZ)) "AvgSize", trunc(sum(KSMCHSIZ)) "Total"
from x$ksmsp
where KSMCHSIZ >= 4108
and KSMCHCLS='free'
group by KSMCHCLS, KSMCHIDX, 1000*trunc(KSMCHSIZ/1000);
Note: The information available in this view is the same that is generated as part of a HEAPDUMP level 2.
Also be aware that running this query too often is likely to cause other memory issues in the shared pool.
There is also a port specific bug filed on HP and 10g where running queries on x$ksmsp will hang the database.
If the result of the above query shows that must of the space available is on the top part of the list (meaning available only in very small chuncks). It is very likely that the error is due to a heavy fragmentation.

You can also use this view as follows to review overall memory usage in the SGA

SQL> SELECT KSMCHCLS CLASS, COUNT(KSMCHCLS) NUM, SUM(KSMCHSIZ) SIZ,
To_char( ((SUM(KSMCHSIZ)/COUNT(KSMCHCLS)/1024)),'999,999.00')||'k' "AVG SIZE"
FROM X$KSMSP GROUP BY KSMCHCLS;

CLASS NUM SIZ AVG SIZE
-------- ---------- ---------- ------------
R-free 12 8059200 655.86k <<= Reserved List
R-freea 24 960 .04k <<= Reserved List
free 331 151736448 447.67k <<= Free Memory
freeabl 4768 7514504 1.54k <<= Memory for user / system processing
perm 2 30765848 15,022.39k <<= Memory allocated to the system
recr 3577 3248864 .89k <<= Memory for user / system processing

a) if free memory (SIZ) is low (less than 5mb or so) you may need to increase the shared_pool_size and shared_pool_reserved_size.
b) if perm continually grows then it is possible you are seeing system memory leak.
c) if freeabl and recr are always huge, this indicates that you have lots of cursor info stored that is not releasing.
d) if free is huge but you are still getting 4031 errors, (you can correlate that with the reloads and invalids causing fragmentation)

2 comments: