Update on 07/06/11: Please also watch this video tutorial from Tanel Poder after you read this article
Nowadays, I am working on performance tuning guide of 10gR2 and I found it is worthy to write about shared pool management after reading the metalink note Note:396940.1 Troubleshooting and Diagnosing ORA-4031 Error.
Note: This blog entry is my summary of the references at the bottom and to be honest I wrote this long entry for myself for not to get lost between those references again 🙂
Shared pool is used to cache different types of data such as textual and executable forms of PL/SQL blocks and SQL statements, dictionary cache data, and other data. If you use shared pool effectively you can reduce resource consumption in at least four ways
- Parse overhead is avoided if the SQL statement is already in the shared pool. This saves CPU resources on the host and elapsed time for the end user.
- Latching resource usage is significantly reduced, which results in greater scalability.
- Shared pool memory requirements are reduced, because all applications use the same pool of SQL statements and dictionary resources.
- I/O resources are saved, because dictionary elements that are in the shared pool do not require disk access.
Main components of shared pool are library cache (executable forms of SQL cursors, PL/SQL programs, and Java classes.) and the dictionary cache (usernames, segment information, profile data, tablespace information, and sequence numbers. ).The library cache stores the executable (parsed or compiled) form of recently referenced SQL and PL/SQL code. The dictionary cache stores data referenced from the data dictionary. This caches are managed by LRU algorithm to “age out” memory structures that have not been reused over time. Allocation of memory from the shared pool is performed in chunks. This allows large objects (over 5k) to be loaded into the cache without requiring a single contiguous area, hence reducing the possibility of running out of enough contiguous memory due to fragmentation. Starting with 9i The Shared Pool divide its shared memory areas into subpools. Each subpool will have Free List Buckets (containing pointers to memory chunks within the subpool ) and , memory structure entries, and LRU list. This architecture is designed to to increase the throughput of shared pool in that now each subpool is protected by a Pool child latch. This means there is no longer contention in the Shared Pool for a single latch as in earlier versions.
Infrequently, Java, PL/SQL, or SQL cursors may make allocations out of the shared pool that are larger than 5k then Oracle must search for and free enough memory to satisfy this request. This operation could conceivably hold the latch resource for detectable periods of time, causing minor disruption to other concurrent attempts at memory allocation. To allow these allocations to occur most efficiently, Oracle segregates a small amount of the shared pool. This memory is used if the shared pool does not have enough space. The segregated area of the shared pool is called the reserved pool which is also divided into subpools. Smaller objects will not fragment the reserved list, helping to ensure the reserved list will have large contiguous chunks of memory. Once the memory allocated from the reserved list is freed, it returns to the reserved list.
By using automatic shared memory management (ASMM) option available with 10G, which is activated by setting SGA_TARGET parameter with a value greater than 0, shared pool size is started to be managed by Oracle, under the limits of SGA_TARGET and other SGA components.
After these explanations lets start to explain how to manage shared pool with ASMM.
1- Using Shared Pool Effectively
avoid hard parsing by
- using bind variables instead of literal values in your queries The script below can be used to find sqls which use literals
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 ;
- Standardizing naming conventions for bind variables and spacing conventions for SQL statements and PL/SQL blocks.
- Because they are stored, Consider using stored procedures whenever possible
- Avoiding users from change the optimization approach and goal for their individual sessions.
- Reducing the number of entries in the dictionary cache by explicitly qualifying the segment owner, rather than using public synonyms or try to connect to the database through a single user ID, rather than individual user IDs because Reducing the number of distinct userIDs also reduces the load on the dictionary cache.SELECT employee_id FROM hr.employees WHERE department_id = :dept_id;
- Using PL/SQL packages when your system has thousands of users, each with individual user sign-on and public synonyms because a package is executed as the owner, rather than the caller, which reduces the dictionary cache load considerably.
- Avoid performing DDLs in peak hours because ddl operations invalidate the dependent SQLs and cause hard parsing when the statement called again.
- Cache the sequence numbers by using CACHE keyword of CREATE/ALTER SEQUENCE clause to reduce the frequency of dictionary cache locks,
- Try to avoid closing of rapidly executed cursors
- Check for hash values which maps different literals. The query below should return no rows otherwise there is possibility for a bug
SELECT hash_value, count(*) FROM v$sqlarea GROUP BY hash_value HAVING count(*) > 5 ;
3- Identify which sqls are using lots of shared memory
SELECT substr(sql_text,1,20) "Stmt", count(*), sum(sharable_mem) "Mem", sum(users_opening) "Open", sum(executions) "Exec" FROM v$sql GROUP BY substr(sql_text,1,20) HAVING sum(sharable_mem) > 1426063 -–%10 of Shared Pool Size ; ---Dynamic version SELECT substr(sql_text,1,20) "Stmt", count(*), sum(sharable_mem)/1024/1024 "Mem", sum(users_opening) "Open", sum(executions) "Exec" FROM v$sql GROUP BY substr(sql_text,1,20) HAVING sum(sharable_mem) > (select current_size*0.1 from v$sga_dynamic_components where component='shared pool');--10 percent
Stmt | COUNT(*) | Mem | Open | Exec |
/* OracleOEM */ SEL | 18 | 1445971 | 2 | 54 |
This should show if there are similar literal statements, or multiple versions of a statements which account for a large portion of the memory in the shared pool.
4- Identify which allocations causing shared pool to be aged out
spool ageout.txt SELECT *FROM x$ksmlru WHERE ksmlrnum>0; spool off
This select returns no more than 10 rows and then erases the contents of the X$KSMLRU table so be sure to SPOOL the output. The X$KSMLRU table shows which memory allocations have caused the MOST memory chunks to be thrown out of the shared pool since it was last queried. This is sometimes useful to help identify sessions or statements which are continually causing space to be requested.
5- Why are there multiple child cursors.
V$SQL_SHARED_CURSOR explains why a particular child cursor is not shared with existing child cursors. Each column identifies a specific reason why the cursor cannot be shared.
SELECT SA.SQL_TEXT,SA.VERSION_COUNT,SS.* FROM V$SQLAREA SA,V$SQL_SHARED_CURSOR SS WHERE SA.ADDRESS=SS.ADDRESS AND SA.VERSION_COUNT > 5 ORDER BY SA.VERSION_COUNT
6- Monitor Shared Pool sizing operations
You can see the shrinking and growing operations from V$SGA_RESIZE_OPS dynamic view and you can guess why there is need for this operations by focusing the sql at the sizing operation times.
column initial_size format 999999999999999 column target_size format 999999999999999 column final_size format 999999999999999 select to_char(end_time, 'dd-Mon-yyyy hh24:mi') end, oper_type, initial_size, target_size, final_size from V$SGA_RESIZE_OPS where component='shared pool' order by end;
END | OPER_TYPE | INITIAL_SIZE | TARGET_SIZE | FINAL_SIZE |
12-Sep-2007 19:05 | STATIC | 0 | 134217728 | 134217728 |
12-Sep-2007 22:01 | SHRINK | 134217728 | 130023424 | 130023424 |
13-Sep-2007 11:35 | SHRINK | 130023424 | 125829120 | 125829120 |
13-Sep-2007 11:36 | SHRINK | 125829120 | 121634816 | 121634816 |
13-Sep-2007 22:08 | GROW | 121634816 | 125829120 | 125829120 |
13-Sep-2007 22:09 | GROW | 125829120 | 130023424 | 130023424 |
13-Sep-2007 22:10 | GROW | 130023424 | 134217728 | 134217728 |
13-Sep-2007 22:12 | GROW | 134217728 | 138412032 | 138412032 |
14-Sep-2007 09:49 | GROW | 138412032 | 142606336 | 142606336 |
14-Sep-2007 16:13 | GROW | 142606336 | 146800640 | 146800640 |
7- Minimum Size of Shared Pool
Current size of the shared pool;
column bytes format 999999999999999 select bytes,bytes/1024/1024/1024 size_gb from v$sgainfo where name='Shared Pool Size';
You can use the sizing advices from the view v$shared_pool_advice. This view displays information about estimated parse time in the shared pool for different pool sizes and the sizes range from %10 to %200 of current shared pool size. This can give you idea for sizing SGA and obliquely shared pool by the help of ASMM.
select * from V$SHARED_POOL_ADVICE;
Suggested minimum shared pool size;
column cr_shared_pool_size format 99999999 column sum_obj_size format 99999999 column sum_sql_size format 99999999 column sum_user_size format 99999999 column min_shared_pool format 9999999 select cr_shared_pool_size, sum_obj_size, sum_sql_size, sum_user_size, (sum_obj_size + sum_sql_size+sum_user_size)* 1.3 min_shared_pool from (select sum(sharable_mem) sum_obj_size from v$db_object_cache where type<> 'CURSOR'), (select sum(sharable_mem) sum_sql_size from v$sqlarea), (select sum(250*users_opening) sum_user_size from v$sqlarea), (select to_Number(b.ksppstvl) cr_shared_pool_size from x$ksppi a, x$ksppcv b, x$ksppsv c where a.indx = b.indx and a.indx = c.indx and a.ksppinm ='__shared_pool_size' );
CR_SHARED_POOL_SIZE | SUM_OBJ_SIZE | SUM_SQL_SIZE | SUM_USER_SIZE | MIN_SHARED_POOL |
146800640 | 9520659 | 25660770 | 11750 | 45751132,7 |
You should set the suggested minimum shared pool size to avoid shrinking operation of ASMM
SQL>alter system set shared_pool_size=73M; System altered.
8- How much free memory in SGA is available for shared pool and how to interpret the free memory
First of all find the free memory in shared pool. If you have free memory you should relax but if you don’t have go to the step below
SELECT * FROM V$SGASTAT WHERE NAME = 'free memory' AND POOL = 'shared pool';
The X$KSMSP view shows the breakdown of memory in the SGA. You can run this query to build trend information on memory usage in the SGA. Remember, the ‘free’ class in this query is not specific to the Shared Pool, but is across the SGA. Dont use the script below when db is under load. Check out Jonathan Lewis’s experiences on this view from here
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 Watch for trends using these guidelines:
Watch for trends using these guidelines:
a) if ‘free’ memory is low (less than 5mb or so) you may need to increase the shared_pool_size and shared_pool_reserved_size. You should expect ‘free’ memory to increase and decrease over time. Seeing trends where ‘free’ memory decreases consistently is not necessarily a problem, but seeing consistent spikes up and down could be a problem.
b) if ‘freeable’ or ‘perm’ memory continually grows then it is possible you are seeing a memory bug.
c) if ‘freeabl’ and ‘recr’ memory classes are always huge, this indicates that you have a lot of cursor info stored that is not releasing.
d) if ‘free’ memory is huge but you are still getting 4031 errors, the problem is likely reloads and invalids in the library cache causing fragmentation.
!!!!!!!!!! Note says that this query can hang database on HP platforms
To see the free memory chunks detailed use the script below
select KSMCHIDX "SubPool", 'sga heap('||KSMCHIDX||',0)'sga_heap,ksmchcom ChunkComment, decode(round(ksmchsiz/1000),0,'0-1K', 1,'1-2K', 2,'2-3K',3,'3-4K', 4,'4-5K',5,'5-6k',6,'6-7k',7,'7-8k',8, '8-9k', 9,'9-10k','> 10K') "size", count(*),ksmchcls Status, sum(ksmchsiz) Bytes from x$ksmsp where KSMCHCOM = 'free memory' group by ksmchidx, ksmchcls, 'sga heap('||KSMCHIDX||',0)',ksmchcom, ksmchcls,decode(round(ksmchsiz/1000),0,'0-1K', 1,'1-2K', 2,'2-3K', 3,'3-4K',4,'4-5K',5,'5-6k',6, '6-7k',7,'7-8k',8,'8-9k', 9,'9-10k','> 10K') order by 1;
SubPool | SGA_HEAP | CHUNKCOMMENT | size | COUNT(*) | STATUS | BYTES |
1 | sga heap(1,0) | free memory | > 10K | 34 | R-free | 7238192 |
1 | sga heap(1,0) | free memory | 3-4K | 2 | free | 6284 |
1 | sga heap(1,0) | free memory | > 10K | 241 | free | 35707400 |
1 | sga heap(1,0) | free memory | 8-9k | 1 | free | 7712 |
1 | sga heap(1,0) | free memory | 2-3K | 4 | free | 6752 |
1 | sga heap(1,0) | free memory | 0-1K | 2090 | free | 133288 |
1 | sga heap(1,0) | free memory | 9-10k | 21 | free | 188676 |
1 | sga heap(1,0) | free memory | 1-2K | 30 | free | 25868 |
If you see lack of large chunks it is possible that you can face with ORA-04031 in near future.
9- Is library_cache or dictionary_cache utilization satisfactory ?
The statistics below is based since the start of the instance. You should take interval statistics to interpret these values for performance issues .
- Library Cache Stats
SELECT NAMESPACE, PINS, PINHITS, RELOADS, INVALIDATIONS FROM V$LIBRARYCACHE ORDER BY NAMESPACE; SQL> SELECT SUM(PINHITS)/SUM(PINS) FROM V$LIBRARYCACHE; SUM(PINHITS)/SUM(PINS) ---------------------- .964764429
NAMESPACE | PINS | PINHITS | RELOADS | INVALIDATIONS |
BODY | 72782 | 72582 | 49 | 0 |
CLUSTER | 1175 | 1161 | 3 | 0 |
INDEX | 2800 | 2023 | 42 | 0 |
JAVA DATA | 0 | 0 | 0 | 0 |
JAVA RESOURCE | 0 | 0 | 0 | 0 |
JAVA SOURCE | 0 | 0 | 0 | 0 |
OBJECT | 0 | 0 | 0 | 0 |
PIPE | 0 | 0 | 0 | 0 |
SQL AREA | 563349 | 541678 | 2069 | 342 |
TABLE/PROCEDURE | 175850 | 165318 | 2005 | 0 |
TRIGGER | 6923 | 6802 | 34 | 0 |
High invalidations indicates that there is parsing problem with the namespace and high reloads indicates that there is a sizing problem which causes aging out.
- Library cache hit ratio;
SQL>SELECT SUM(PINHITS)/SUM(PINS) FROM V$LIBRARYCACHE; SUM(PINHITS)/SUM(PINS)---------------------- 95558088
low hit ratio is an indication of a sizing or caching problem
- Dictionary cache stats
SELECT PARAMETER , SUM(GETS) , SUM(GETMISSES) , 100*SUM(GETS – GETMISSES) / SUM(GETS) PCT_SUCC_GETS , SUM(MODIFICATIONS) UPDATES FROM V$ROWCACHE WHERE GETS > 0 GROUP BY PARAMETER;
PARAMETER | SUM(GETS) | SUM(GETMISSES) | PCT_SUCC_GETS | UPDATES |
dc_constraints | 99 | 35 | 64,6464646 | 99 |
dc_tablespaces | 90104 | 14 | 99,9844624 | 0 |
dc_tablespace_quotas | 13 | 3 | 76,9230769 | 0 |
dc_awr_control | 1351 | 2 | 99,8519615 | 121 |
dc_object_grants | 867 | 174 | 79,9307958 | 0 |
dc_histogram_data | 52053 | 6181 | 88,1255643 | 3047 |
dc_rollback_segments | 55098 | 92 | 99,8330248 | 263 |
dc_sequences | 100 | 27 | 73 | 100 |
dc_usernames | 6632 | 33 | 99,5024125 | 0 |
dc_segments | 23404 | 2466 | 89,4633396 | 331 |
dc_objects | 37434 | 3776 | 89,9129134 | 358 |
dc_histogram_defs | 65987 | 16796 | 74,5465016 | 3280 |
dc_table_scns | 8 | 8 | 0 | 0 |
dc_users | 171638 | 105 | 99,9388247 | 0 |
outstanding_alerts | 1674 | 58 | 96,5352449 | 66 |
dc_files | 80 | 10 | 87,5 | 0 |
dc_object_ids | 134005 | 2646 | 98,0254468 | 123 |
dc_global_oids | 52337 | 185 | 99,6465216 | 0 |
dc_profiles | 1962 | 4 | 99,7961264 | 0 |
High updates with low pct_succ_gets can be a clue of performance problems when accessing that dictionary object. For frequently accessed dictionary caches, the ratio of total GETMISSES
to total GETS
should be less than 10% or 15%, depending on the application. If this ratio is higher and every previous control is OK then you should consider to increase the shared pool size
- Dictionary cache hit ratio;
SQL>SELECT (SUM(GETS – GETMISSES – FIXED)) / SUM(GETS) "ROW CACHE" FROM V$ROWCACHE; ROW CACHE --------------------, 9516921886454345524
Low hit ratio is an indication of a sizing problem.
10- Are there any objects candidate for library cache pinning ?
Having objects pinned will reduce fragmentation and changes of encountering the ORA-04031 error. Objects causing a large number of other objects been flushed out from the shared pool are candidates to be pinned into the shared pool using dbms_shared_pool.keep procedure. You can check the x$ksmlru fixed table to see the candidates. This table keeps track of the objects and the corresponding number of objects flushed out of the shared pool to allocate space for the load. These objects are stored and flushed out based on the Least Recently Used (LRU) algorithm. Because this is a fixed table, once you query the table, Oracle will automatically reset the table so first insert the contents to temporary table like below,
CREATE TABLE LRU_TMP AS SELECT * FROM X$KSMLRU; INSERT INTO LRU_TMP SELECT * FROM X$KSMLRU;
Use the LRU_TMP table for analysis. You can use a query below to see more information on candidate code in the library cache.
SELECT USERNAME, KSMLRCOM, KSMLRHON, KSMLRNUM, KSMLRSIZ, SQL_TEXT FROM V$SQLAREA A, LRU_TMP K, V$SESSION S WHERE KSMLRSIZ > 3000 AND A.ADDRESS=S.SQL_ADDRESS AND A.HASH_VALUE = S.SQL_HASH_VALUE AND SADDR=KSMLRSES;
You can see the candidates to pin from the query below
COL STORED_OBJECT FORMAT A40; COL SQ_EXECUTIONS FORMAT 999,999; SELECT /*+ ORDERED USE_HASH(D) USE_HASH(C) */ O.KGLNAOWN||'.'||O.KGLNAOBJ STORED_OBJECT, SUM(C.KGLHDEXC) SQL_EXECUTIONS FROM SYS.X$KGLOB O, SYS.X$KGLRD D, SYS.X$KGLCURSOR C WHERE O.INST_ID = USERENV('INSTANCE') AND D.INST_ID = USERENV('INSTANCE') AND C.INST_ID = USERENV('INSTANCE') AND O.KGLOBTYP IN (7, 8, 9, 11, 12) AND D.KGLHDCDR = O.KGLHDADR AND C.KGLHDPAR = D.KGLRDHDL GROUP BY O.KGLNAOWN, O.KGLNAOBJ HAVING SUM(C.KGLHDEXC) > 0 ORDER BY 2 DESC;
You should pin objects you find immediatelly after the each restart of instance. You can pin the object by DBMS_SHARED_POOL package like below
EXECUTE DBMS_SHARED_POOL.KEEP(OWNER.TRIGGER, ‘R’) |
11- Is my Reserved Area sized properly?
An ORA-04031 error referencing a large failed requests indicates the Reserved Area is too fragmented.
col free_space for 999,999,999,999 head "TOTAL FREE" col avg_free_size for 999,999,999,999 head "AVERAGE|CHUNK SIZE" col free_count for 999,999,999,999 head "COUNT" col request_misses for 999,999,999,999 head "REQUEST|MISSES" col request_failures for 999,999,999,999 head "REQUEST|FAILURES" col max_free_size for 999,999,999,999 head "LARGEST CHUNK" select free_space, avg_free_size, free_count, max_free_size, request_misses, request_failures from v$shared_pool_reserved;
TOTAL FREE | AVERAGE CHUNK SIZE |
COUNT | LARGEST CHUNK | REQUEST MISSES |
REQUEST FAILURES |
7,238,192 | 212,888 | 34 | 212,888 | 0 | 0 |
The reserved pool is small when:
REQUEST_FAILURES > 0 (and increasing)
The DBA should Increase shared_pool_reserved_size and shared_pool_size together.
It is possible that too much memory has been allocated to the reserved list.
If:
REQUEST_MISS = 0 or not increasing
FREE_MEMORY = > 50% of shared_pool_reserved_size minimum
The DBA should Decrease shared_pool_reserved_size
You should also use hidden and unsupported parameter “_shared_pool_reserved_pct” to control reserved pool. This parameter controls the allocated percentage of shared pool for reserved pool. By default it is %5 of the shared pool and if you use ASMM for memory management you can set this value higher like 10 to allocate reserved pool dynamically. When you set the parameter you will see the shared_pool_reserved_size parameter will be adjusted to the new setting.
The parameter can not be modified when instance is started. You can use the query below to see the current value
select a.ksppinm "Parameter", b.ksppstvl "Session Value", c.ksppstvl "Instance Value" from sys.x$ksppi a, sys.x$ksppcv b, sys.x$ksppsv c where a.indx = b.indx and a.indx = c.indx and a.ksppinm = '_shared_pool_reserved_pct';
Parameter | Session Value | Instance Value |
_shared_pool_reserved_pct | 10 | 10 |
12-Is there any fragmentation in shared pool?
The primary problem that occurs is that free memory in the shared pool becomes fragmented into small pieces over time. Any attempt to allocate a large piece of memory in the shared pool will cause large amount of objects in the library cache to be flushed out and may result in an ORA-04031 out of shared memory error. But how to understand the fragmentation ?
- Occurrence of ORA-04031 error. Before this error signalled, memory is freed from unnecessary objects and merged. This error only occurs when there is still not a large enough contiguous piece of free memory after this cleaning process. There may be very large amounts of total free memory in the shared pool, but just not enough contiguous memory.
- Using X$KSMLRU internal fixed table. We told about this view before about its usage for tracking age out operations, it also can be used to identify what is causing the large allocations. KSMLRSIZ column of this table shows the amount of contiguous memory being allocated. Values over around 5K start to be a problem, values over 10K are a serious problem, and values over 20K are very serious problems. Anything less then 5K should not be a problem. Again be careful to save spool the result when you query this table
spool ageout.txt select * from x$ksmlru where ksmlrsiz > 5000; spool off
After finding the result you should do the followings to correct fragmentation
- Keep object by pinning them as we discussed above
- Use bind variables as we discussed before
- Eliminate large anonymous PL/SQL block. Large anonymous PL/SQL blocks should be turned into small anonymous PL/SQL blocks that call packaged functions. The packages should be ‘kept’ in memory. To view candidates
select sql_text from v$sqlarea
where command_type=47 — command type for anonymous block
and length(sql_text) > 500;
Fallacies about solving shared pool fragmentation
- Free memory in shared pool prevents fragmentation. This is not true because Free memory is more properly thought of as ‘wasted memory’. You would rather see this value be low than very high. In fact, a high value of free memory is sometimes a symptom that a lot of objects have been aged out of the shared pool and therefore the system is experiencing fragmentation problems.
- Flushing shared pool frequently solves fragmentation and improves performance. This is also incorrect because Executing this statement causes a big spike in performance and does nothing to improve fragmentation. You lost your cached cursors when you flush and they will hard parsed next time with high CPU consumption.
13- Using related database parameters
- CURSOR_SHARING: Setting this parameter to smilar can solve your hard parse problems caused by using literals but can have side effects mostly on DSS environments and systems which uses stored outlines.
- CURSOR_SPACE_FOR_TIME: This parameter specifies whether a cursor can be deallocated from the library cache to make room for a new SQL statement. CURSOR_SPACE_FOR_TIME has the following values meanings:
- If CURSOR_SPACE_FOR_TIME is set to false (the default), then a cursor can be deallocated from the library cache regardless of whether application cursors associated with its SQL statement are open. In this case, Oracle must verify that the cursor containing the SQL statement is in the library cache.
- If CURSOR_SPACE_FOR_TIME is set to true, then a cursor can be deallocated only when all application cursors associated with its statement are closed. In this case, Oracle need not verify that a cursor is in the cache, because it cannot be deallocated while an application cursor associated with it is open.
You must be sure that the shared pool is large enough for the work load otherwise performance will be badly affected and ORA-4031 eventually signalled.
- OPEN_CURSORS: This parameter sets the upper bound for the number of cursor that a session can have open and if you size it correctly, cached cursors can be stay opened and won’t have to be closed to let new cursor open
- PROCESSES / SESSIONS: You can review the high water mark for Sessions and Processes in the V$RESOURCE_LIMIT view. If the hard-coded values for these parameters are much higher than the high water mark information, consider decreasing the parameter settings to free up some memory in the Shared Pool for other uses.
- SESSION_CACHED_CURSORS: When a cursor is closed, Oracle divorces all association between the session and the library cache state. If no other session has the same cursor opened, the library cache object and its heaps are unpinned and available for an LRU operation. The parameter SESSION_CACHED_CURSORS controls the number of cursors “soft” closed, much like the cached PL/SQL cursors. Oracle checks the library cache to determine whether more than three parse requests have been issued on a given statement. If so, then Oracle assumes that the session cursor associated with the statement should be cached and moves the cursor into the session cursor cache. Subsequent requests to parse that SQL statement by the same session then find the cursor in the session cursor cache.To determine whether the session cursor cache is sufficiently large for your instance, you can examine the session statistic
session
cursor
cache
hits
in theV$SYSSTAT
view. This statistic counts the number of times a parse call found a cursor in the session cursor cache. If this statistic is a relatively low percentage of the total parse call count for the session, then consider settingSESSION_CACHED_CURSORS
to a larger value. Steve Adams also wrote usefully queries to find the usage and the maximum cacheable cursors.session_cursor_cache.sql
This was a long article and if you see anything wrong or suspicious please feel free to comment for correction
Update 07/06/11 : Please also check out sgastatx.sql by Tanel poder
All of the queries are tested on Oracle 10.2.0.3 for Windows
Code Depot of The Queries (All scripts are taken from metalink notes and official documentation)
References :
Oracle® Database Performance Tuning Guide 10g Release 2 (10.2)
Metalink Notes
Note:396940.1 Troubleshooting and Diagnosing ORA-4031 Error
Note:146599.1 Diagnosing and Resolving Error ORA-04031
Note:61623.1 Resolving Shared Pool Fragmentation In Oracle7
Note:62143.1 Understanding and Tuning the Shared Pool
Note:1012047.6 How To Pin Objects in Your Shared Pool
Note:274496.1 ora-7445 and ora-4031 in 9.2.0.5 and 10g if SESSION_CACHED_CURSORS is used
http://www.ixora.com Oracle Advanced Performance Tuning Scripts
Mailing list threads from Oracle-l
http://www.freelists.org/archives/oracle-l/08-2007/msg00975.html
Update 09/12/2011
Some extra useful metalink note
Interpreting the automatically generated ORA-4031 diagnostic trace. [ID 809560.1]
Hi sir,
I have only one word to say,Excellent!I guess I wont have the requirement to read manual again for shared pool.I realy liked your approach for trainings.I am also in trainings but much much junior to you both in experience and knowledge but I am trying to learn database.If there would be some doubts than may I ask them on this blog?
I am going to add your blog on my website and bloglines account.
With best regards,
Aman….
Comment by Aman Sharma — October 15, 2007 @ 3:55 pm
You can ask whenever you want Aman. Thank you for you comment
Comment by coskan — October 15, 2007 @ 5:53 pm
Hi sir,
Thanks alot for your reply sir.Just one thing,it is Aman :-).
best regards,
Aman….
Comment by Aman.... — October 19, 2007 @ 2:45 am
Hi Coskan,
this is a very good post. I like it very much!
What I would like to add here is:
– there is additional shared memory needed in the SHARED POOL if you are using ASM storage. It can be calculated by using a formula and teh amount of shared memory needed is determined by the amount of ASM storage you use as well as the redundancy level of the ASM diskgroups. For more info you can refer to my post on this
=> http://sysdba.wordpress.com/2006/03/30/how-to-calculate-the-minimum-size-of-the-shared-pool-in-oracle-10g/
– in 11g we have a new feature called SQL-Relsut-Cache which can be used to cache results of queries as well as PL/SQL functions in order to share it amoun different sessions. This result cache utilizes SHARED POOL memory!
For more see my post
=> http://sysdba.wordpress.com/2007/09/11/my-top-10-oracle-11g-new-features-part-4-the-sql-query-result-cache/
– also I would like to point out that it is strongly recommended to configure a LARGE_POOL under certain conditions, such as parallel executions, use of shared servers and use of RMAN for tape backups (pls refer to my post about this
=> http://sysdba.wordpress.com/2007/05/08/how-to-calculate-the-minimum-size-of-large_pool_size-for-io-slaves-rman-and-memory-utilization/.
Otherwise you can also cause fragmentation in the SHARED POOL if no LARGE_POOL is available at appropriate size and configured for use.
Cheers,
Lutz
=;-)
Comment by Lutz Hartmann — October 31, 2007 @ 3:16 pm
Thank you for these valuable comments Lutz now the topic become better
Comment by coskan — October 31, 2007 @ 5:55 pm
Super /excellent / max-explosion of share pool i can say.
Comment by Prashanth — February 8, 2008 @ 4:58 am
Please can also do this for database buffer please.
Comment by Prashanth — February 8, 2008 @ 4:59 am
Tom Kyte discourage the explicit caching of Objects into memory.
”
Followup October 27, 2003 – 6pm US/Eastern:
you normally should not ever need to use this, it only comes into play when you full scan a table
that exceeds the short table threshold — causing us to cache it differently.
it is rare to do that.
you would know you are doing it.
i’ve used it once in my experience.
”
Comment by Polarski Bernard — March 19, 2008 @ 8:05 am
Hi,
Nice article…Even I was planning to write one 😉
I would like to emphasize the importance of subpools also in diagnosing shared problems. You can refer to following link for details
http://askoracledba.blogspot.com/2008/03/shared-sub-pools.html
And shared_pool_reserved_min_alloc parameter
http://askoracledba.blogspot.com/2008/04/tweaking-sharedpoolreservedminalloc-and.html
Comment by Piyush — April 15, 2008 @ 4:04 am
Hi Coskan,
I have moved to wordpress. So I am posting the new links
Information about shared sub pools
Comment by Amit — May 12, 2008 @ 6:16 am
yrmzehu vkngc kjenhsw zunem qjkacbz glfocuat kwjpygdz
Comment by nxwdgrbcp opmsalzd — July 10, 2008 @ 4:51 am
Coskan
Thanks for providing wonderfull information about Shared pool.
Comment by Tej — August 8, 2008 @ 1:15 pm
hi…
Its nice information…can u add me in ur list… so that i can get these type of information posted by u….
Comment by chetna — August 11, 2008 @ 10:53 am
I have been working on Oracle over 10 years. Your web site gave me a very impression. I still can learn something and update my knowledge from here. Also you looks like an artist and make this web so beautiful.
Comment by Ray Wang — October 23, 2008 @ 4:13 pm
thank you very much for your comments they are very encouraging to keep what I am doing.
To be honest I have lost my concentration to blogging after changing , country that I live , language I speak, marital status from single to married in the same year, but today I started writing again. I hope this time there wont be a long break period like this.
Comment by coskan — October 23, 2008 @ 5:22 pm
Thanks Coskan,
excellent work!
Alle the best for the new year, Klaus
Comment by Klaus — January 5, 2009 @ 1:03 pm
hi sir,
it’s excellent work.
can u give me answer for one question?
my question is
How we can find it that open cursor is improper even though
shared pool size is configured properly?
Pls give answer for that question
Comment by Mihir — March 12, 2009 @ 2:55 pm
Hi Mihir,
What do you mean by “open cursor is improper ” is that the number of open cursors ?
IF you can make this clear and give the version yu work on maybe I can give an answer to your question
Comment by coskan — March 13, 2009 @ 8:59 am
Sir,
I really feel the passion of oracle you have ,excellent would be the small word,
Think its best to fix the problems of shared pool.
Query:
My prod DB is facing with Ora-07445 error,to my knoweldge or research it searching for a CHILD CURSORS which is not been in shared pool,can you please advice me,if needed i will share my alert & user trace file as well
Thanks & Regards,
viswa08
Comment by viswa08 — April 20, 2009 @ 10:59 am
Hi Viswa,
Thank you for you comment but the right place for 7445 errors is not here It is metalink support , as you see it on error definition 😦
ORA-07445:
exception encountered: core dump [string] [string] [string] [string] [string] [string]
Cause: An OS exception occurred which should result in the creation of a core file. This is an internal error.
Action: Contact your customer support representative.
Comment by coskan — April 20, 2009 @ 4:26 pm
[…] reason that makes this book unique. I already wrote my own compilation about shared pool in a blog post but this chapter thought me many more things like lathes and mutexes in shared pool , In memory undo […]
Pingback by Oracle Performance Firefighting by Craig Shallahamer « Coskan’s Approach to Oracle — September 10, 2009 @ 9:39 pm
Nice write up!
Comment by Robert Joost — May 29, 2010 @ 7:22 am
[…] 2. https://coskan.wordpress.com/2007/09/14/what-i-learned-about-shared-pool-management/ […]
Pingback by ORA-04031 « Anand's Blog — July 9, 2010 @ 11:40 pm
Merhaba, Well done
Comment by alex — January 27, 2011 @ 5:01 pm
BOSS THIS IS GREATE I ‘RELLLY FAN OF U
AJAY
Comment by A.K SOLANKI — April 26, 2011 @ 4:19 pm
Excellent post!!!
Had a question. You have used v$sql to “Identify which sqls are using lots of shared memory”
The sum(sharable_mem) from v$sql and v$sqlarea for a sqlid is different. Which sharable_mem should be considered for the actual space utilized by a sql_id in shared_pool? Also what is the difference between v$sql and v$sqlarea?
Thanks!!!!
Comment by Vinu — April 13, 2012 @ 3:34 pm
Instead of me repeating what is written best is you check from the links
http://docs.oracle.com/cd/E11882_01/server.112/e25513/dynviews_3064.htm
http://docs.oracle.com/cd/E11882_01/server.112/e25513/dynviews_3043.htm
If it is still not clear we can discuss
Comment by coskan — April 13, 2012 @ 4:23 pm
Thanks a lot, I really appreciate you answering my question.
Sorry, I am still not able to understand why sum(sharable_mem) in v$sql does not match sharable_mem in v$sqlarea for a sql_id
This is what I see for one sql_id
– sum(sharable_mem)/1024/1024 from v$sql – 3438.83
– sharable_mem/1024/1024 from v$sqlarea – 29.23
Can you please let me know why sum(sharable_mem) in v$sql does not match sharable_mem in v$sqlarea?
In the above example is it correct that sql_id is using 3438 MB of my shared_pool?
Comment by Vinu — April 13, 2012 @ 7:06 pm
Lets see what is wrong
What is the version of your database and what is the output of sql below (please put the sql_id)
select ‘v$sqlarea’ view_name,sharable_mem,version_count,loaded_versions from v$sqlarea where sql_id=”
union
select ‘v$sql’ view_name,sum(sharable_mem),null,count(sql_id) from v$sql where sql_id=”
Comment by coskan — April 15, 2012 @ 9:10 am
Thanks, my database version is 11.2.0.2. Following is the output of the query. After the weekend the sharable_mem for the sqlid in v$sql has dropped from 3.4GB to 76MB.
select ‘v$sqlarea’ view_name,sharable_mem,version_count,loaded_versions from v$sqlarea where sql_id=’ft2sb2zuzg87z’
2 union
3 select ‘v$sql’ view_name,sum(sharable_mem),null,count(sql_id) from v$sql where sql_id=’ft2sb2zuzg87z’;
VIEW_NAME SHARABLE_MEM VERSION_COUNT LOADED_VERSIONS
—————————— ———— ————- —————
v$sql 76,540,112 96
v$sqlarea 7,005,432 96 96
Comment by Vinu — April 16, 2012 @ 2:51 pm
thats also what I observe on 11.2.0.2, looks like it is a bug on 11.2.0.3 this problem disappears. I can see on 11.2.0.2 same sql has got x number of childs but v$sqlarea claims it is less then x number. If you have issues I suggest open an SR with ORacle
Comment by coskan — April 16, 2012 @ 7:13 pm
Thanks a lot!!! I will submit a SR, will let you know once I have Oracle’s update.
Comment by Vinu — April 16, 2012 @ 9:45 pm
Vinu,
Did you ever hear back from oracle about this?
Comment by krish — April 9, 2018 @ 6:06 am
[…] Shared Pool Oracle na web: pinning objects in shared pool – Oracle— Question: What is the Oracle shared_pool_size parameter and what is contained in this shared pool? Answer: the Shared Pool is a RAM area within the RAM heap that is created at … http://www.dba-oracle.com/concepts/shared_pool_concepts.htm – ver What I learned about shared pool— could some one please tell me the steps needed to pin packages in the shared pool . thanks dbaforums.org/oracle/index.php?showtopic=3854 – ver DBMS SHARED POOL – Oracle FAQ 14-09-2007 · Update on 07/06/11: Please also watch this video tutorial from Tanel Poder after you read this article. Nowadays, I am working on performance tuning guide of 10gR2 … coskan.wordpress.com/2007/09/14/what-i-learned-about-shared-pool-management – ver […]
Pingback by Shared Pool Oracle – ForoGeek — April 24, 2012 @ 6:13 am
Excellent post
Comment by Jotheesh — June 12, 2012 @ 4:21 pm
Halo,
I have a question. Our production was upgraded from oracle 10g to 11g. Since then there has been concurrency issue in the form of Library cache mutex x waits and shared pool contention which slows down the DB performance. This has been there for almost 4 months. We have tried the basic trouble shooting methods to resolve mutex waits and shared pool contention but still the problem exists. We have also raised an SR in metalink. Would be great if you could help us with your suggestions. Appreciate your time.
Comment by Jenny Thomas — June 16, 2012 @ 5:32 am
[…] very good explanation: https://coskan.wordpress.com/2007/09/14/what-i-learned-about-shared-pool-management/ Share this:TwitterFacebookGefällt mir:Gefällt mirSei der Erste dem dies […]
Pingback by What I learned about shared pool management « Logbook — August 24, 2012 @ 12:48 pm
[…] You can found a very good study about shared pool management in Coskan’s post. […]
Pingback by Real-Time SGA component monitoring « bdt's oracle blog — December 11, 2012 @ 8:58 pm
sir,very good post,i just want to know that can i increase shared pool and yesterday increased size of shared pool 900m but in enterprise manager after restarting database shows 0 why so? and reserverd pool is 76336332
SQL> SELECT NAME, BYTES
2 FROM v$sgainfo;
NAME BYTES
——————————– ———-
Fixed SGA Size 2038696
Redo Buffers 14737408
Buffer Cache Size 1.0133E+10
Shared Pool Size 536870912
Large Pool Size 16777216
Java Pool Size 33554432
Streams Pool Size 0
Granule Size 16777216
Maximum SGA Size 1.0737E+10
Startup overhead in Shared Pool 218103808
Free SGA Memory Available 0
———————————————————
POOL NAME BYTES
———— ————————– ———-
shared pool free memory 60458000
Comment by shilpa168shilpa — December 12, 2012 @ 6:59 am
Hi thanks for your comments,
I don’t know why EM is showing that way but depending on the questions I may do some guesswork
Questions
1- version of DB and EM
2- do you use memory_target memory_max_size
3- did you state spfile
4- output of select current_size/1024/1024 from v$sga_dynamic_components where component like ‘shared%’
Comment by coskan — December 12, 2012 @ 7:50 am
Hi Coskan
I run the query for 11- Is my Reserved Area sized properly?
Output I got is as below:
AVERAGE REQUEST REQUEST
TOTAL FREE CHUNK SIZE COUNT LARGEST CHUNK MISSES FAILURES
—————- —————- —————- —————- —————- —————-
90,291,240 95,144 72 1,851,208 0 23
1 row selected.
Running
SQL> show parameter reserved
NAME TYPE VALUE
———————————— ———– ——————————
shared_pool_reserved_size big integer 80M
Is the output from the SQL correct? The TOTAL FREE is > than shared_pool_reserved_size, I would though the value should be <= to shared_pool_reserved_size?
Comment by newbie01oracle — February 26, 2013 @ 3:28 am
Excellent information. I was able to find some areas for improvement. For the high invalidations and reloads which I see I am trying to fix them. Your queries help us figure out the issues it would be great if we could have the solutions to fix those issues like which particular parameter needs to be changed. The 11th point to check the reserved pool sizehad a query to check for the particular value and if it is >0 increase the pool size. That point helped me fix it quickly. I am in a situation where the queries cannot be tuned as it is a package but have to try improving the complete daily load by tuning the database as much as I can.
NAMESPACE PINS PINHITS RELOADS INVALIDATIONS
ACCOUNT_STATUS 0 0 0 0
APP CONTEXT 221 220 0 0
BODY 219874 219612 15 0
CLUSTER 5880 5865 0 0
DBINSTANCE 0 0 0 0
DBLINK 0 0 0 0
DIRECTORY 33 25 0 0
EDITION 6591 6589 0 0
INDEX 6694 4938 263 0
JAVA DATA 577 571 0 0
JAVA SOURCE 2 0 0 0
OBJECT ID 0 0 0 0
QUEUE 1252527 1252479 6 0
RULE 50 23 0 0
RULESET 32833 32779 0 0
SCHEMA 0 0 0 0
SQL AREA 3539783 3114381 4734 12720
SQL AREA BUILD 0 0 0 0
SQL AREA STATS 143176 3738 0 0
SUBSCRIPTION 27 10 0 0
TABLE/PROCEDURE 2263882 2235227 8742 0
TEMPORARY INDEX 522 0 248 0
TEMPORARY TABLE 1387 0 1170 0
TRIGGER 30381 30324 0 0
USER AGENT 1 0 0 0
XDB CONFIG 1 0 0 0
XML SCHEMA 18 9 0 0
Comment by oracledba_1 — March 24, 2013 @ 3:46 am
Hi Coskan,
If running the query below returns something, are we supposed to do anything about it or just keep it for informatIon?
spool ageout.txt
select * from x$ksmlru where ksmlrsiz > 5000;
spool off
Can we link this to v$sqltext / v$session to get more information about the SQL?
Any feedback much appreciated, thanks in advance.
Comment by newbie01oracle — June 21, 2013 @ 1:46 am
Hi Coskan,
Excellent document and very useful information in a nut shell.
I’ve a query, Is it possible to join v$sqlarea and v$db_object_cache?
Basically my requirement is to gather the information that when a package/procedure/function is last executed which I put in KEEP shared pool.
I’m getting the package name and number of executions in v$db_object_cache, but not getting information about last executed, so can I use last_load_date of v$sqlarea for the same purpose (is v$sqlarea records the plsql package entries also?)?
Thanks
Amit P
Comment by Amit P — August 28, 2013 @ 10:37 am
Are you talking about DRAM memory or storage when you say shared memory?
Comment by Mrittika — September 30, 2013 @ 2:30 am
[…] Source: https://coskan.wordpress.com/2007/09/14/what-i-learned-about-shared-pool-management/ […]
Pingback by Live Streaming Κάλυψη Συνεδρείων — December 16, 2013 @ 7:33 pm
[…] https://coskan.wordpress.com/2007/09/14/what-i-learned-about-shared-pool-management/ […]
Pingback by mkjay — May 14, 2014 @ 8:56 pm
Easy to understand,good material.
Oracle Training in Chennai
Comment by rekha — July 9, 2014 @ 5:42 am
I drop a leave a response each time I like a post on a site or I
have something to valuable to contribute to the conversation. It’s a result of the sincerness displayed
in the article I looked at. And after this article What I learned about shared pool management | Coskan’s Approach to
Oracle. I was actually excited enough to drop
a thought 😉 I actually do have a few questions for you if you do not mind.
Could it be just me or do some of the remarks appear like they are
coming from brain dead visitors? 😛 And, if you are
writing on additional places, I would like to keep up with everything fresh you have to post.
Could you make a list all of your public sites like your linkedin profile, Facebook page or twitter feed?
Comment by startlogic — July 11, 2014 @ 2:56 pm
[…] https://coskan.wordpress.com/2007/09/14/what-i-learned-about-shared-pool-management/ […]
Pingback by Metalink Master Note ID | sandarbhoracle — July 15, 2014 @ 5:54 pm
[…] query is from here. however, the V$sqlarea only has the first 1000 char of the […]
Pingback by Shared Pool | My Blog — October 2, 2014 @ 9:08 pm
[…] https://coskan.wordpress.com/2007/09/14/what-i-learned-about-shared-pool-management/ […]
Pingback by On Shared Pool | Links to Various sources — January 27, 2015 @ 4:24 am
[…] https://coskan.wordpress.com/2007/09/14/what-i-learned-about-shared-pool-management/ […]
Pingback by Shared Pool | clusterclouds — January 27, 2015 @ 4:37 am
[…] https://coskan.wordpress.com/2007/09/14/what-i-learned-about-shared-pool-management/ […]
Pingback by Shared Pool | Links to Various sources — February 23, 2015 @ 10:06 am
Hi Coskan,
Thanks for posting the details. My question is can a single statement (about 600 lines SQL create table as ) using parallel(16) hint (yes, no table alias to parallel) cause 4031? Last few days we identified one specific user causing this and crashed the PMON. For now I have asked that user to stop doing it. I also unset sga_target after trace file indicated lots of SGA resizing ops that was waiting. some MOS articles pointing to unsetting it. I am using 11.1.0.7 on Solaris.10.
Thanks again
Kumar Ramalingam
Comment by Sankarakumar Ramalingam — September 23, 2016 @ 1:36 pm
xxx gonzo video porno pornhub.com viva video
Comment by xxx porno — September 4, 2017 @ 5:15 pm
Coskan,
This is a great article. I have same question as posed by Vinu about discrepancies between stats reported by v$sql and v$sqlarea. One possible explanation provided by some of my colleagues is not all cursors shown in v$sql are in cache and hence show up in v$sqlarea. So I tried to run join against v$open_cursor and v$sql and this helps to some extent but even with this I am not 100% satisfied. I opened an SR with oracle too.
Comment by krish — April 9, 2018 @ 6:00 am