Hi,
in my 10.2.0.3 RAC on Linux 64bit I've got huge shared pool reserved fragmentation caused by 'ges resource' (I believe) :
1 select
2 ksmchcom contents,
3 count(*) chunks,
4 sum(decode(ksmchcls, 'R-recr', ksmchsiz)) recreatable,
5 sum(decode(ksmchcls, 'R-freea', ksmchsiz)) freeable,
6 sum(ksmchsiz) total
7 from
8 sys.x$ksmspr
9 where
10 inst_id = userenv('Instance')
11 group by
12* ksmchcom
CONTENTS CHUNKS RECREATABLE FREEABLE TOTAL
---------------- ---------- ----------- ---------- ----------
free memory 4103 30600064
Heap0: KGL 1 52552 52552
ges resource 28948 153772056 153772056
character set o 1 5600 5600
reserved stoppe 464 22272 22272
init_heap_kfsg 24 241536 241536
PCursor 12 630624 630624
CCursor 9 472968 472968
permanent memor 854 8777984
sql area 5 162480 162480
PL/SQL PPCODE 1 8256 8256
PL/SQL MPCODE 2 38952 38952
Oracle gives me 4031 errors when trying to do AWR report and so (there is big chunk needed for that package) .
Here is shared pool from v$sgastat , is there any way I can workaround this w/o restarting database ?
Looks like flush is not helping here .
POOL NAME BYTES
------------ -------------------------- ----------
shared pool free memory 986830384
shared pool ges resource 783439200
shared pool sql area 538990568
shared pool KQR L PO 237902464
shared pool ges enqueues 149903480
shared pool partitioning d 133786448
shared pool ASM extent pointer array 126198288
shared pool obj stat memo 114742232
shared pool CCursor 101802312
shared pool library cache 82724104
shared pool mvobj part des 81807504
shared pool kglsim object batch 81113088
shared pool PCursor 77223200
shared pool kglsim heap 53561088
shared pool Cursor Stats 40435232
shared pool init_heap_kfsg 26153360
shared pool object level 18398496
shared pool gcs resources 17286064
shared pool KSXR pending messages que 17098432
shared pool KQR M SO 16815520
shared pool ASH buffers 16252928
shared pool ges big msg buffers 15936168
shared pool KGLS heap 14134048
shared pool gcs shadows 10887120
shared pool XDB Schema Cac 9531464
shared pool sql area:PLSQL 8675208
shared pool ges reserved msg buffers 8240008
shared pool rm request queue link 7905872
shared pool row cache 7506880
shared pool event statistics per sess 7142400
shared pool db_block_hash_buckets 5898240
shared pool object queue 5291888
shared pool Heap0: KGL 5231832
shared pool Sort Segment 4268048
shared pool KCB Table Scan Buffer 4194816
shared pool kglsim hash table bkts 4194304
shared pool KSFD SGA I/O b 4190248
shared pool trace buffer 4177920
shared pool sessions 4110608
shared pool Checkpoint queue 4098048
shared pool PL/SQL DIANA 3878000
shared pool PL/SQL MPCODE 2677472
shared pool FileOpenBlock 2470760
shared pool KQR X PO 2265336
shared pool FileIdentificatonBlock 2247328
shared pool state objects 1948352
shared pool transaction 1880560
shared pool VIRTUAL CIRCUITS 1811800
shared pool parameter table block 1660704
shared pool enqueue 1573008
shared pool kks stbkt 1572864
shared pool enqueue resources 1513888
shared pool ges resource hash table 1441792
shared pool procs: ksunfy 1416800
shared pool dbwriter coalesce buffer 1049088
shared pool DML lock 1036064
shared pool KSXR receive buffers 1036000
shared pool kgllk hash table 991232
shared pool ges regular msg buffers 980408
shared pool buffer handles 980008
shared pool db_files 875232
shared pool ges process array 863632
shared pool joxlod exec hp 850160
shared pool SGA - SWRF Metric CHBs 846824
shared pool KCL name table 786432
shared pool modification 773848
shared pool returns from metrics req 763904
shared pool message pool freequeue 757568
shared pool KCL partition table 720896
shared pool KQR L SO 683872
shared pool ksr message pool free que 657960
shared pool KQR ENQ 656352
shared pool obj htab chun 655872
shared pool partitioning i 627584
shared pool ktlbk state objects 592992
shared pool type object de 558512
shared pool object queue hash buckets 525312
shared pool call 518616
shared pool PX msg pool 516480
shared pool jsksncb: 7 489472
shared pool repository 463640
shared pool prmtzdini tz region 404888
shared pool KEWS sesstat values 396800
shared pool Wait History 372000
shared pool resize request state obje 366400
shared pool pso tbs: ksunfy 364000
shared pool KGSK scheduler 351120
shared pool character set memory 345792
shared pool max allowable # log files 337600
shared pool character set object 279808
shared pool SYSTEM PARAMETERS 278608
shared pool branch 277712
shared pool BRANCH TABLE SEGMENTED AR 276576
shared pool SGA - SWRF Metric Eidbuf 275040
shared pool work area tab 270144
shared pool KQR M PO 269712
shared pool simulator hash buckets 263168
shared pool KCL instance cache transf 262144
shared pool change tracking recovery 262144
shared pool trace buf hdr xtend 245616
shared pool sql area:KOKA 238832
shared pool parameter handle 230568
shared pool TXN TABLE SEGMENTED ARRAY 229408
shared pool replication session stats 229400
shared pool temporary table lock 202832
shared pool sys event stats 196672
shared pool sys event stats for Other 196672
shared pool constraints 195568
shared pool channel handle 194648
shared pool KTI SGA freeable small po 184320
shared pool Temporary Tables State Ob 183760
shared pool KQR S SO 183600
shared pool ksuloi: long op statistic 172000
shared pool KSXR large reply queue 168104
shared pool file # translation table 168080
shared pool multiblock re 164536
shared pool LRMPD SGA Table 161280
shared pool UNDO INFO SEGMENTED ARRAY 154096
shared pool kglsim main lru size 151040
shared pool PX subheap 150248
shared pool messages 145600
shared pool kksss-heap 135448
shared pool txncallback 131296
shared pool gcs res hash bucket 131072
shared pool osp allocation 126080
shared pool SEQ S.O. 124000
shared pool KGX 122976
shared pool table definiti 120384
shared pool latch nowait fails or sle 114848
shared pool alter system errs: kspnfy 114688
shared pool kmgsb circular statistics 108800
shared pool gcs commit sga state 106512
shared pool subheap 105592
shared pool ksws service events 101376
shared pool KCL lock contexts 100888
shared pool KSXR pending reply queue 100280
shared pool PL/SQL PPCODE 95240
shared pool name-service 78744
shared pool kglsim main lru count 76800
shared pool enqueue_hash 76080
shared pool KSK SGA 76080
shared pool MS alert log 72704
shared pool temporary tabl 70824
shared pool trace events array 68000
shared pool kgl lock hash table state 67760
shared pool kpscad: kpscscon 66080
shared pool partitioned file hash buc 65536
shared pool qmps connections 65280
shared pool dummy 65080
shared pool dlo fib struct 64032
shared pool network connections 62880
shared pool kksss 62456
shared pool UNDO STAT INFO 59904
shared pool File Space Usage 56136
shared pool dsktab_kfgsg 50440
shared pool event descriptor table 49360
shared pool returns from remote ops 49152
shared pool Nettimeout Histogram 48800
shared pool sort segment handle 47768
shared pool kghx free lists 45136
shared pool KCL buffer header 44864
shared pool idtab_kfksg 44296
--<snip>--