Oracle is 9204
Problem segment is table. There was no recompilations in near past.
Yes, shared_pool_size looks small.
SQL> select * from v$sgastat;
POOL NAME BYTES
----------- -------------------------- ----------
fixed_sga 733560
buffer_cache 1493172224
log_buffer 3408896
shared pool errors 51576
shared pool enqueue 936920
shared pool DML lock 475336
shared pool KGK heap 7000
shared pool KQR L PO 1427632
shared pool KQR M PO 778752
shared pool KQR S SO 10504
shared pool sessions 1500720
POOL NAME BYTES
----------- -------------------------- ----------
shared pool sql area 94244912
shared pool 1M buffer 2098176
shared pool KGLS heap 1474784
shared pool processes 644000
shared pool db_handles 580000
shared pool parameters 8544
shared pool free memory 10441192
shared pool PL/SQL DIANA 850168
shared pool ksfv subheap 11992
shared pool FileOpenBlock 3796104
shared pool PL/SQL MPCODE 1380032
POOL NAME BYTES
----------- -------------------------- ----------
shared pool library cache 42488408
shared pool miscellaneous 35129008
shared pool MTTR advisory 283856
shared pool PLS non-lib hp 2088
shared pool joxs heap init 4240
shared pool sim memory hea 1003912
shared pool table definiti 4512
shared pool transaction co 1544
shared pool trigger defini 9544
shared pool trigger inform 2144
shared pool trigger source 2496
POOL NAME BYTES
----------- -------------------------- ----------
shared pool Checkpoint queue 6159360
shared pool dictionary cache 3229952
shared pool KSXR receive buffers 1034000
shared pool message pool freequeue 940944
shared pool KSXR pending messages que 853952
shared pool event statistics per sess 6233760
shared pool fixed allocation callback 1744
large pool free memory 16777216
On 11/27/06, Mladen Gogala <mgogala_at_verizon.net> wrote:
>
> On 11/27/2006 03:40:33 PM, Ranko Mosic wrote:
> > Hi List,
> > This is hanganalyze dump file:
> >
> > Found 15 objects waiting for <cnode/sid/sess_srno/proc_ptr/ospid/wait_event>
> > <0/190/6488/0xdb6bde98/12701/library cache lock>
> > Open chains found:
> > Chain 1 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> :
> > <0/79/27/0xdb6dc6a0/15367/No Wait>
> > -- <0/190/6488/0xdb6bde98/12701/library cache lock>
> > -- <0/185/318/0xdb6dad78/23551/library cache lock>
> > -- <0/12/144/0xdb6d2fb0/1666/library cache pin>
> > Other chains found:
> > Chain 2 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> :
> > <0/16/66/0xdb6dc198/23559/library cache lock>
> > Chain 3 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> :
> > <0/17/100/0xdb6c5c60/23962/library cache pin>
> > Chain 4 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> :
> > <0/32/12/0xdb6dcba8/15369/No Wait>
> > Chain 5 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> :
> > <0/39/1075/0xdb6e0d10/22710/library cache pin>
> >
> > --
> >
> > shared_pool_size is 160M. sga is 1.7g.
> >
> > Would increase help ?
>
>
> Ranko, what do V$SGASTAT and V$RESOURCE_LIMIT tell you? What is the name space (P3) of that pin/lock?
> May be pinning it in the SGA would help? Your shared pool size is very small compared to the rest of
> the SGA. What is the rest of the SGA being used for?
>
> --
> Mladen Gogala
> http://www.mladen-gogala.com
>
>
--
Regards,
Ranko Mosic
Contract Senior Oracle DBA
B. Eng, Oracle 10g, 9i Certified Database Professional
Phone: 416-450-2785
email: mosicr_at_rogers.com
http://ca.geocities.com/mosicr@rogers.com/ContractSeniorOracleDBARankoMosicMain.html
--
http://www.freelists.org/webpage/oracle-l
Received on Mon Nov 27 2006 - 16:23:23 CST
