Oracle Database Discussions

Hi,

Please

Hi,

Please some input..

"temporary tabl" in shared pool is excessive high!!!

online flush doesn't work.

Why is this value "17592186044" so high for temporary tabl??



Thanks,
Ron Sahertian



==========================================

set echo off feed off

SGA POOLS MEMORY AVG # OTHER POOLS MEMORY

------------------------- ----------- ------ ---- -------------------- ---------

SHARED POOL -->17592186 OTHER POOLS -->1139 M

_________________________ ___________ ______ ____________________ _________

temporary tabl 17592186044 100% | | java pool 96 M

free memory 274 M 0% | | large pool 128 M

library cache 45 M 0% | | fixed_sga 722 K

miscellaneous 35 M 0% | | buffer_cache 912 M

PX msg pool 13 M 0% | | log_buffer 2306 K

sql area 11 M 0% | |

_________________________ ___________ ______ | | ____________________ _________



========================= =========== ====== ==== ==================== =========

ALL UGAs AND PGAs

UGA / PGA ... MEMORY AVG ESTIMATED MAX SIZE

pga memory (391 sessions) 788.7 M 2066 K 437 sessions (HWM)= 882 M

uga memory (391 sessions) 225.2 M 590 K 437 sessions (HWM)= 252 M

_________________________ ___________ ______ ____ ____________________ _________





=================================================================


select * from v$sgastat;

POOL NAME BYTES
----------- -------------------------- ----------
fixed_sga 739160
buffer_cache 956301312
log_buffer 2361344
shared pool branch 277712
shared pool errors 1193680
shared pool krvxrr 253056
shared pool enqueue 1494120
shared pool KGK heap 7000
shared pool KQR L PO 4007896
shared pool KQR L SO 722656
shared pool KQR M PO 2392760

POOL NAME BYTES
----------- -------------------------- ----------
shared pool KQR M SO 1245968
shared pool KQR S SO 45632
shared pool KQR X PO 110768
shared pool SEQ S.O. 124000
shared pool sessions 2095600
shared pool sql area 212432904
shared pool 1M buffer 2099200
shared pool KGLS heap 2494896
shared pool processes 924000
shared pool OUTLNCACHE 6528
shared pool PX subheap 1712144

POOL NAME BYTES
----------- -------------------------- ----------
shared pool db_handles 812000
shared pool parameters 488584
shared pool PX msg pool 13942264
shared pool constraints 195568
shared pool free memory 21197448
shared pool transaction 2310832
shared pool PL/SQL DIANA 4781680
shared pool trace buffer 1490944
shared pool FileOpenBlock 5284672
shared pool PL/SQL MPCODE 13137056
shared pool PL/SQL PPCODE 21072

POOL NAME BYTES
----------- -------------------------- ----------
shared pool PL/SQL SOURCE 2088
shared pool library cache 99530088
shared pool miscellaneous 36934496
shared pool PLS non-lib hp 2088
shared pool channel handle 183384
shared pool joxs heap init 4240
shared pool sim memory hea 984960
shared pool table definiti 15840
shared pool temporary tabl 1.8447E+19 <<<<<<<<<<<<<<<<????!!!!!
shared pool trigger defini 116832
shared pool trigger inform 10000

POOL NAME BYTES
----------- -------------------------- ----------
shared pool trigger source 4608
shared pool type object de 3360
shared pool Checkpoint queue 4106240
shared pool dictionary cache 4274432
shared pool qmps connections 341000
shared pool enqueue resources 309992
shared pool sim trace entries 786432
shared pool ksm_file2sga region 370496
shared pool ktlbk state objects 456672
shared pool KSXR receive buffers 1034000
shared pool session param values 7695040

POOL NAME BYTES
----------- -------------------------- ----------
shared pool db_block_hash_buckets 3964976
shared pool FileIdentificatonBlock 349824
shared pool KSXR large reply queue 167624
shared pool message pool freequeue 748880
shared pool KSXR pending messages que 853952
shared pool Temporary Tables State Ob 183760
shared pool event statistics per sess 8791600
shared pool fixed allocation callback 1168
shared pool replication session stats 235600
large pool free memory 134217728
java pool free memory 100663296

66 rows selected.