Shared pool free space
Summary
Here is some useful information about Shared Pool size and free space. First of all you can find the parameters related to shared pool with the query:
SELECT NAME, VALUE/(1024*1024) SHARED_POOL_SIZE_MB FROM V$PARAMETER WHERE NAME LIKE '%shared_pool%'; NAME SHARED_POOL_SIZE_MB ---------------------------------------------------- ------------------- shared_pool_size 11488 shared_pool_reserved_size 574.4
You can find the free space in Shared pool with the query:
SELECT POOL,NAME, ROUND(BYTES/(1024*1024),2) FREE_MB FROM V$SGASTAT WHERE POOL='shared pool' AND NAME='free memory' ORDER BY BYTES DESC; POOL NAME FREE_MB ------------ -------------------------- ---------- shared pool free memory 8130.13
After oracle 10g the shared pool is divided in subpools. To find how many subpools your instance has you must follow the Metalink note 455179.1 because it depends on cpu number and init parameters.
How To Determine The Default Number Of Subpools Allocated During Startup [ID 455179.1]
I think the maximum number of subpools that you can have is 7.
Each subpool is protected by a separate shared pool latch and each subpool has its own freelists and LRU list. To monitor how much space each subpool is using execute the query:
SELECT KSMCHIDX "SubPool", ROUND(SUM(KSMCHSIZ)/1024/1024) USED_MBYTES
FROM SYS.X$KSMSP
GROUP BY KSMCHIDX
ORDER BY KSMCHIDX ASC;
SubPool USED_MBYTES
---------- ----------
1 944
2 1424
3 1216
4 864
if you want to change the number of subpools from 7 to 4 for example then use the following hidden parameter:_kghdsidx_count = 4
Tip: Actually the subpools are not only as many as the oracle algorithm creates or what ever value the parameter _kghdsidx_count has. There is always one more! The subpool with KSMCHIDX=0. You can check this out with
select distinct ksmdsidx from x$ksmss order by ksmdsidx asc;
KSMDSIDX
----------
0
1
2
3
4
This is why when you startup the database not all memory allocated for shared pool is immediately given to subpool heaps. Some memory stays free allocated to subpool 0 and will be used to feature increases for the other pools. This mechanism allows some subpools to grab more memory than the others. So it is no strange if you monitor subpool imbalances!
From what i know a subpool heap cannot give memory back to some other subpool! I assume that is due to the fact that each subpool is protected by a separate shared pool latch and each subpool has its own freelists and LRU list.
using this query you can monitor all the subpools and 0 subpool free space
SELECT TO_CHAR(SYSDATE,'DD-MM-YYYY HH24:MI:SS') DT, KSMSSNAM NAME,
KSMDSIDX SUBPOOL, ROUND(KSMSSLEN/1024/1024) MBYTES
FROM X$KSMSS
WHERE KSMSSLEN > (10*1024*1024) OR KSMSSNAM='free memory' --more than 10MBytes
ORDER BY NAME, SUBPOOL;
DT NAME SUBPOOL MBYTES
------------------- -------------------------- ---------- ----------
04-05-2011 13:36:43 ASH buffers 1 68
04-05-2011 13:36:43 CCursor 1 11
04-05-2011 13:36:43 Checkpoint queue 1 195
04-05-2011 13:36:43 FileIdentificatonBlock 1 11
04-05-2011 13:36:43 FileOpenBlock 1 15
04-05-2011 13:36:43 KQR M PO 1 12
04-05-2011 13:36:43 KTI-UNDO 1 16
04-05-2011 13:36:43 db_block_hash_buckets 1 45
04-05-2011 13:36:43 event statistics per sess 1 20
04-05-2011 13:36:43 free memory 0 24112
04-05-2011 13:36:43 free memory 1 67
04-05-2011 13:36:43 library cache 1 20
04-05-2011 13:36:43 private strands 1 31
04-05-2011 13:36:43 sessions 1 12
04-05-2011 13:36:43 sql area 1 49
To eliminate imbalances of the subpools you can set the _kghdsidx_count = 1. Then you will only have the 0 subpool and one subpool. If you set _kghdsidx_count = 1 then it is obvious that is working "like an Oracle9i" style.
A very common error related to shared pool misconfiguration or problems is:
ORA-04031: unable to allocate x bytes of shared memory
To monitor if you have ORA-04031 errors you can use the query:
SELECT KGHLUSHRPOOL "SUBPOOL", KGHLURCR "PINS AND|RELEASES",
KGHLUTRN, KGHLUFSH, KGHLUOPS, KGHLUNFU "ORA-4031|ERRORS",
KGHLUNFS "LAST ERROR|SIZE"
FROM SYS.X$KGHLU
WHERE INST_ID = USERENV('Instance') ;
PINS AND
SUBPOOL PINS AND|RELEASES KGHLUTRN KGHLUFSH RELEASES ORA-4031|ERRORS LAST ERROR|SIZE
---------- ----------------- ---------- ---------- ---------- --------------- ---------------
0 0 559 0 4057 0 0
1 460039 1147916 0 4562952 0 0
If you want to see what Oracle advises for Shared pool size check this out:
SELECT SHARED_POOL_SIZE_FOR_ESTIMATE || ' MB' SHARED_POOL_SIZE_MB, SHARED_POOL_SIZE_FACTOR*100 || '%' RESIZE_SHARED_POOL_PERC, ESTD_LC_SIZE, ESTD_LC_MEMORY_OBJECTS, ESTD_LC_TIME_SAVED, ESTD_LC_TIME_SAVED_FACTOR, ESTD_LC_MEMORY_OBJECT_HITS FROM V$SHARED_POOL_ADVICE;
To check how much space is free at the shared_pool_reserved use this query:
SELECT ROUND(FREE_SPACE/(1024*1014), 2) SHARED_POOL_RESERVED_FREE_MB, ROUND(USED_SPACE/(1024*1024),2) SHARED_POOL_RESERVED_USED_MB FROM V$SHARED_POOL_RESERVED;
_____________________________________________________________________________
Shared Pool Statistics Begin End ------ ------ Memory Usage %: 5.49 43.79 % SQL with executions>1: 71.54 94.53 % Memory for SQL w/exec>1: 66.33 94.91 SELECT SQL_ID,EXECUTIONS,DISK_READS,to_char(LAST_LOAD_TIME,'DD-MON-YYYY HH24:MI:SS') FROM V$SQLAREA WHERE SQL_ID in(select SQL_ID from v$sql_plan where options in('FULL SCAN','FULL') AND OBJECT_OWNER NOT IN('SYS','SYSTEM')) ORDER BY disk_reads desc; SQL> SQL> desc V$SGA_RESIZE_OPS Name Null? Type ---------------------------------------- -------- --------------------------- COMPONENT VARCHAR2(64) OPER_TYPE VARCHAR2(13) OPER_MODE VARCHAR2(9) PARAMETER VARCHAR2(80) INITIAL_SIZE NUMBER TARGET_SIZE NUMBER FINAL_SIZE NUMBER STATUS VARCHAR2(9) START_TIME DATE END_TIME DATE SQL> select COMPONENT ,INITIAL_SIZE,TARGET_SIZE,FINAL_SIZE,STATUS from V$SGA_RESIZE_OPS 2 ; SQL> select pool,name,sum(bytes)/1024/1024/1024 from v$sgastat where pool like 'shared%' group by pool,name order by sum(bytes)/1024/1024/1024 desc; SQL> select pool,name,sum(bytes)/1024/1024/1024 from v$sgastat where pool like 'shared%' group by pool,name order by sum(bytes)/1024/1024/1024 desc; SQL> desc v$sgastat; Name Null? Type ----------------------------------------------------------------------------------- -------- -------------------------------------------------------- POOL VARCHAR2(12) NAME VARCHAR2(26) BYTES NUMBER SQL> select * from v$sgastat where pool like 'SHARED%'; no rows selected SQL> select * from v$sgastat where pool like 'shared%'; SQL> select COMPONENT,sum(CURRENT_SIZE)/1024/1024/1024,sum(MIN_SIZE)/1024/1024/1024 from v$SGA_DYNAMIC_COMPONENTS where component='shared pool' group by COMPONENT; COMPONENT SUM(CURRENT_SIZE)/1024/1024/1024 SUM(MIN_SIZE)/1024/1024/1024 ------------------------------ -------------------------------- ---------------------------- DEFAULT 2K buffer cache .109375 .109375 DEFAULT 4K buffer cache 0 0 shared pool 17.53125 17.53125 DEFAULT 8K buffer cache 0 0 DEFAULT buffer cache 2.9375 2.9375 DEFAULT 16K buffer cache 0 0 ASM Buffer Cache 0 0 streams pool .109375 .109375 KEEP buffer cache 1.765625 1.765625 large pool .25 .25 java pool .140625 .140625 DEFAULT 32K buffer cache 0 0 RECYCLE buffer cache 3.515625 3.515625 13 rows selected. select COMPONENT,sum(CURRENT_SIZE)/1024/1024/1024,sum(MIN_SIZE)/1024/1024/1024 from v$SGA_DYNAMIC_COMPONENTS where component='shared pool' group by COMPONENT; SQL> select NAMESPACE,GETHITS,GETHITRATIO,RELOADS,INVALIDATIONS from V_$LIBRARYCACHE; NAMESPACE GETHITS GETHITRATIO RELOADS INVALIDATIONS --------------- ---------- ----------- ---------- ------------- SQL AREA 10264686 .898111808 21751 1540 TABLE/PROCEDURE 1143854 .993568774 1146 0 BODY 204106 .998757095 122 0 TRIGGER 254492 .998250555 163 0 INDEX 7880 .982176243 9 0 CLUSTER 391 .970223325 1 0 OBJECT 0 1 0 0 PIPE 91517 .996320287 0 0 JAVA SOURCE 0 1 0 0 JAVA RESOURCE 0 1 0 0 JAVA DATA 0 1 0 0 11 rows selected. select * from emp select * from EMP NAME_COL_PLUS_SHOW_PARAM ------------------------------------------------------------------------------ TYPE ----------- VALUE_COL_PLUS_SHOW_PARAM ------------------------------------------------------------------------------ cursor_sharing string EXACT select sum(value)/1024/1024 from v$sga; SQL> SQL> rem SQL> rem FUNCTION: Generate a report of SQL Area Memory Usage SQL> rem showing SQL Text and memory catagories SQL> rem SQL> rem sqlmem.sql SQL> rem SQL> COLUMN sql_text FORMAT a60 HEADING Text word_wrapped SQL> COLUMN sharable_mem HEADING Shared|Bytes SQL> COLUMN persistent_mem HEADING Persistent|Bytes SQL> COLUMN loads HEADING Loads SQL> COLUMN users FORMAT a15 HEADING "User" SQL> COLUMN executions HEADING "Executions" SQL> COLUMN users_executing HEADING "Used By" SQL> START title132 "Users SQL Area Memory Use" SP2-0310: unable to open file "title132.sql" SQL> SPOOL rep_out\&db\sqlmem Enter value for db: SET LONG 2000 PAGES 59 LINES 132 SP2-0768: Illegal SPOOL command Usage: SPOOL { <file> | OFF | OUT } where <file> is file_name[.ext] [CRE[ATE]|REP[LACE]|APP[END]] SQL> BREAK ON users SQL> COMPUTE SUM OF sharable_mem ON USERS SQL> COMPUTE SUM OF persistent_mem ON USERS SQL> COMPUTE SUM OF runtime_mem ON USERS SQL> SELECT 2 username users, sql_text, Executions, loads, users_executing, 3 sharable_mem, persistent_mem 4 FROM 5 sys.v_$sqlarea a, dba_users b 6 WHERE 7 a.parsing_user_id = b.user_id 8 ; SELECT username users, Executions, loads, users_executing, sharable_mem, persistent_mem FROM sys.v_$sqlarea a, dba_users b WHERE a.parsing_user_id = b.user_id 2 ; SQL> desc stats$sql_summary select sql_fulltext from v$sqlarea where sql_fulltext like '%ALLOCATION_OWNER_TYPE in%'; select sql_fulltext from v$sqlarea where sql_fulltext like '%ALLOCATION_OWNER_TYPE in%' and rownum<=2 SQL> select sql_fulltext from v$sqlarea where sql_fulltext like '%ALLOCATION_OWNER_TYPE in%' and rownum<=5; select 'sga heap('||KSMCHIDX||',0)'sga_heap,ksmchcom ChunkComment, 2 decode(round(ksmchsiz/1000),0,'0-1K', 1,'1-2K', 2,'2-3K', 3 3,'3-4K',4,'4-5K',5,'5-6k',6,'6-7k',7,'7-8k',8,'8-9k', 9,'9-10k','> 10K') 4 "Size", 5 count(*), ksmchcls "Status", sum(ksmchsiz) "Bytes" from x$ksmsp 6 where KSMCHCOM = 'free memory' group by 'sga heap('||KSMCHIDX||',0)', 7 ksmchcom, ksmchcls, decode(round(ksmchsiz/1000),0,'0-1K', 1,'1-2K', 2,'2-3K', 8 3,'3-4K',4,'4-5K',5,'5-6k',6,'6-7k',7,'7-8k',8,'8-9k', 9,'9-10k','> 10K'); SQL> select NAMESPACE,RELOADS,INVALIDATIONS from v$librarycache; NAMESPACE RELOADS INVALIDATIONS --------------- ---------- ------------- SQL AREA 30938 1613 TABLE/PROCEDURE 2100 0 BODY 189 0 TRIGGER 315 0 INDEX 15 0 CLUSTER 2 0 OBJECT 0 0 PIPE 0 0 JAVA SOURCE 0 0 JAVA RESOURCE 0 0 JAVA DATA 0 0 11 rows selected. SQL> rem FUNCTION: Generate a library cache report SQL> column namespace heading "Library Object" SQL> column gets format 9,999,999 heading "Gets" SQL> SQL> column gethitratio format 999.99 heading "Get Hit%" SQL> column pins format 9,999,999 heading "Pins" SQL> column pinhitratio format 999.99 heading "Pin Hit%" SQL> column reloads format 99,999 heading "Reloads" SQL> column invalidations format 99,999 heading "Invalid" SQL> column db format a10 SQL> set pages 58 lines 80 SQL> ttitle "Library Caches Report" SQL> define output = lib_cache SQL> spool &output SQL> select namespace, gets, gethitratio*100 gethitratio, pins, pinhitratio*100 pinhitratio, reloads, invalidations from v$librarycache; Mon May 31 page 1 Library Caches Report Library Object Gets Get Hit% Pins Pin Hit% Reloads Invalid --------------- ---------- -------- ---------- -------- ------- ------- SQL AREA ########## 89.19 ########## 98.63 31,014 1,613 TABLE/PROCEDURE 1,702,941 99.45 ########## 99.98 2,100 0 BODY 246,756 99.88 ########## 100.00 189 0 TRIGGER 320,823 99.83 5,608,778 99.98 315 0 INDEX 8,205 97.85 19,379 98.96 15 0 CLUSTER 560 97.50 1,121 98.57 2 0 OBJECT 0 100.00 0 100.00 0 0 PIPE 114,550 99.63 118,975 99.65 0 0 JAVA SOURCE 0 100.00 0 100.00 0 0 JAVA RESOURCE 0 100.00 0 100.00 0 0 JAVA DATA 0 100.00 0 100.00 0 0 SQL> select POOL, NAME,BYTES/1024/1024 from v$sgastat where name = 'free memory'; POOL NAME BYTES/1024/1024 ------------ -------------------------- --------------- shared pool free memory 9088.51419 large pool free memory 255.75 java pool free memory 144 streams pool free memory 112 SQL> select sid,serial#,event from v$session where type='USER' and status='ACTIVE'; SQL> select NAMESPACE,RELOADS,INVALIDATIONS from v$librarycache; NAMESPACE RELOADS INVALIDATIONS 18004258448 select sum(value) from v$sga; select sum(bytes) from v$sgastat; select sum(current_size) from v$sga_dynamic_components; select * from v$sga_dynamic_free_memory; SQL> show parameter statistics_level NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ statistics_level string TYPICAL SQL> select count(1) from v$shared_pool_advice; COUNT(1) ---------- 0 SQL> select count(1) from V$SGA_TARGET_ADVICE ; COUNT(1) ---------- 0 SQL> SELECT NAME, BYTES FROM v$sgainfo; 2 NAME BYTES -------------------------------- ---------- Fixed SGA Size 2137568 Redo Buffers 14639104 Buffer Cache Size 9059696640 Shared Pool Size 1.8707E+10 Large Pool Size 268435456 Java Pool Size 150994944 Streams Pool Size 117440512 Granule Size 16777216 Maximum SGA Size 2.9360E+10 Startup overhead in Shared Pool 738197504 Free SGA Memory Available 1040187392 11 rows selected. SQL> select sum(bytes) from v$sgastat where pool='shared pool'; SUM(BYTES) ---------- 1.8707E+10 SELECT SQL_ID,EXECUTIONS,DISK_READS,to_char(LAST_LOAD_TIME,'DD-MON-YYYY HH24:MI:SS') FROM V$SQLAREA WHERE SQL_ID in(select SQL_ID from v$sql_plan where options in('FULL SCAN','FULL') AND OBJECT_OWNER NOT IN('SYS','SYSTEM')) ORDER BY disk_reads desc; select count(1) from x$ksmlru; rem FUNCTION: Generate a report of SQL Area Memory Usage rem showing SQL Text and memory catagories rem rem sqlmem.sql rem COLUMN sql_text FORMAT a60 HEADING Text word_wrapped COLUMN sharable_mem HEADING Shared|Bytes COLUMN persistent_mem HEADING Persistent|Bytes COLUMN loads HEADING Loads COLUMN users FORMAT a15 HEADING "User" COLUMN executions HEADING "Executions" COLUMN users_executing HEADING "Used By" START title132 "Users SQL Area Memory Use" SPOOL rep_out\&db\sqlmem SET LONG 2000 PAGES 59 LINES 132 BREAK ON users COMPUTE SUM OF sharable_mem ON USERS COMPUTE SUM OF persistent_mem ON USERS COMPUTE SUM OF runtime_mem ON USERS SELECT username users, sql_text, Executions, loads, users_executing, sharable_mem, persistent_mem FROM sys.v_$sqlarea a, dba_users b WHERE a.parsing_user_id = b.user_id rem FUNCTION: Generate a summary of SQL Area Memory Usage rem FUNCTION: uses the sqlsummary view. rem showing user SQL memory usage rem rem sqlsum.sql rem column areas heading Used|Areas column sharable format 999,999,999 heading Shared|Bytes column persistent format 999,999,999 heading Persistent|Bytes column runtime format 999,999,999 heading Runtime|Bytes column username format a15 heading "User" column mem_sum format 999,999,999 heading Mem|Sum start title80 "Users SQL Area Memory Use" spool rep_out\&db\sqlsum set pages 59 lines 80 break on report compute sum of sharable on report compute sum of persistent on report compute sum of runtime on report compute sum of mem_sum on report select username, sum(sharable_mem) Sharable, sum( persistent_mem) Persistent, sum( runtime_mem) Runtime , count(*) Areas, sum(sharable_mem+persistent_mem+runtime_mem) Mem_sum from sql_summary group by username order by 2; spool off SELECT username users, Executions, loads, users_executing, sharable_mem, persistent_mem FROM sys.v_$sqlarea a, dba_users b WHERE a.parsing_user_id = b.user_id select POOL, NAME,BYTES/1024/1024 from v$sgastat where name = 'free memory'; rem rem FUNCTION: Generate a summary of SQL Area Memory Usage rem FUNCTION: uses the sqlsummary view. rem showing user SQL memory usage rem rem sqlsum.sql rem column areas heading Used|Areas column sharable format 999,999,999 heading Shared|Bytes column persistent format 999,999,999 heading Persistent|Bytes column runtime format 999,999,999 heading Runtime|Bytes column username format a15 heading "User" column mem_sum format 999,999,999 heading Mem|Sum start title80 "Users SQL Area Memory Use" spool rep_out\&db\sqlsum set pages 59 lines 80 break on report compute sum of sharable on report compute sum of persistent on report compute sum of runtime on report compute sum of mem_sum on report select sum(sharable_mem) Sharable, sum( persistent_mem) Persistent, sum( runtime_mem) Runtime , count(*) Areas, sum(sharable_mem+persistent_mem+runtime_mem) Mem_sum from stats$sql_summary alter system flush shared_pool; select COMPONENT,sum(CURRENT_SIZE)/1024/1024/1024,sum(MIN_SIZE)/1024/1024/1024 from v$SGA_DYNAMIC_COMPONENTS where component='shared pool' group by COMPONENT; select COMPONENT,sum(CURRENT_SIZE)/1024/1024/1024,sum(MIN_SIZE)/1024/1024/1024 from v$SGA_DYNAMIC_COMPONENTS group by COMPONENT select POOL, NAME,BYTES/1024/1024 from v$sgastat where name = 'free memory'
