SHARED POOL PROBLEM

[bjsarma]

Hi,

I am facing major problem in my staging database with shared pool. From my shared pool memory is not getting relased. If i do alter system flush shared_pool then also, nothing is getting released.
The statitics from v$sgastat are as follows

fixed_sga 75804
db_block_buffers 204800000
log_buffer 1024000
shared pool free memory 40288724
shared pool miscellaneous 32637016
shared pool trigger defini 9648
shared pool db_block_hash_buckets 931096
shared pool ktlbk state objects 157504
shared pool fixed allocation callback 640
shared pool transaction_branches 184000
shared pool KGK heap 9340
shared pool trigger inform 120
shared pool table definiti 7708
shared pool PLS non-lib hp 2096
shared pool sessions 722260
shared pool enqueue_resources 294912
shared pool State objects 386192
shared pool table columns 19660
shared pool db_handles 150000
shared pool KGFF heap 9812
shared pool PX subheap 6176
shared pool DML locks 176640
shared pool PL/SQL MPCODE 609248
shared pool PX msg pool 64496
shared pool dictionary cache 442260
shared pool transactions 328256
shared pool PL/SQL DIANA 1234752
shared pool message pool freequeue 124552
shared pool library cache 9752896
shared pool db_block_buffers 6800000
shared pool sql area 23241932
shared pool db_files 185516
shared pool processes 242400
shared pool KQLS heap 2052748
shared pool event statistics per sess 1152400
java pool free memory 20000768

Every time, miscellaneous is not getting released. I don't know why.
When i flush shared pool, my proportion of the free memory that is available is reducing day by day.

What is this 'Miscellaneos'?

Please urgently help me

Thanks in advance
Regards
Jags

[anandkl]

could u please be more clear on the kind of error ur getting.

cheese
anandkl

[gpsingh]

what is the oracle version , u might be hitting a bug.

[bjsarma]

The error that appears is


ORA-04031: unable to allocate 4096 bytes of shared memory ("shared pool","BEGIN :1 := sf_episode_patca...","PL/SQL MPCODE","BAMIMA: Bam Buffer") ",


Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production


Regards
Jags

[bjsarma]

Hi,

Is there any new patch released by Oracle, that flushes shared pool fully.

I am facing the problem with shared pool, where the shared pool is not freeing the memory even though i flush it using

ALTER SYSTEM FLUSH SHARED_POOL.


Regards
Jags

[marist89]

Originally posted by bjsarma
Is there any new patch released by Oracle, that flushes shared pool fully.

8.1.7.2