sql area

[netbar]

I am seeing excessive reloads being reported from v$librarycache
everytime I execute I see reloads increasing. Help this is the live production systems and sone queries are just not returning.

[hrishy]

Hi

You can pin large sql packages using dbms_shared_pool.keep

regards
Hrishy

[netbar]

Does the sql area only pertain to large packages. I am seeing the reloads grow currently at a vast rate and no packages I being executed.

Any other suggestions?

[hrishy]

Hi

well you should try increasing the SGA then..but i suggest you first start reading the oracle performance tuning manuals.

regards
Hrishy

[nagarjuna]

Originally posted by hrishy
Hi

well you should try increasing the SGA then..but i suggest you first start reading the oracle performance tuning manuals.

regards
Hrishy

give us output of v$sgastat

[netbar]

SQL> select * from v$sgastat;

POOL NAME BYTES
----------- -------------------------- ----------
fixed_sga 73888
db_block_buffers 1999994880
log_buffer 4194304
shared pool free memory 52488504
shared pool miscellaneous 4874496
shared pool PL/SQL MPCODE 1070328
shared pool KGK heap 5252
shared pool trigger defini 3680
shared pool message pool freequeue 124552
shared pool partitioning d 319180
shared pool db_block_buffers 33203040

POOL NAME BYTES
----------- -------------------------- ----------
shared pool PX msg pool 64496
shared pool sessions 2382380
shared pool KGFF heap 37384
shared pool State objects 1167024
shared pool db_block_hash_buckets 4168440
shared pool PLS non-lib hp 2096
shared pool messages 176000
shared pool db_handles 500000
shared pool KQLS heap 752636
shared pool DML locks 240000
shared pool dictionary cache 15780640

POOL NAME BYTES
----------- -------------------------- ----------
shared pool table definiti 4424
shared pool simulator trace entries 320000
shared pool table columns 19516
shared pool fixed allocation callback 1920
shared pool library cache 224020000
shared pool PL/SQL DIANA 358480
shared pool sql area 722504936
shared pool Checkpoint queue 230976
shared pool processes 796000
shared pool PX subheap 10080
shared pool log_buffer 131072

POOL NAME BYTES
----------- -------------------------- ----------
shared pool event statistics per sess 3836560
large pool free memory 2000000
java pool free memory 20000768

36 rows selected.


Thanks

Also the buffer cache ratio is 50% HELP!!

[pando]

you can either RTFM or contract some DBA

[netbar]

Thanks for the pleasant advice and enjoy your day!