| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: intermittent library cache pin/lock problems
On Fri, 09 Dec 2005 17:24:17 GMT, netcomradeNSPAM_at_bookexchange.net
(NetComrade) wrote:
>We are seeing intermittent library cache pin/lock problems. The
>problem occurs for about a min and then goes away. This of course
>causes timeouts all over the place.
>
>Any clue on where to start?
>
>We generally have use bind variables..
>Relevant settings
>
>cursor_sharing string EXACT
>cursor_space_for_time boolean TRUE
>session_cached_cursors integer 100
>
>snapshot of sgastat:
>POOL NAME BYTES
>----------- ------------------------------ ----------
> fixed_sga 745744
> buffer_cache 301989888
> log_buffer 1311744
>shared pool errors 71368
>shared pool enqueue 15296336
>shared pool KGK heap 7000
>shared pool KQR L PO 840600
>shared pool KQR M PO 804584
>shared pool KQR S SO 10496
>shared pool KQR X PO 1517280
>shared pool sessions 25295920
>shared pool sql area 34301824
>shared pool KGLS heap 1743656
>shared pool processes 11152000
>shared pool db_handles 9860000
>shared pool parameters 6792
>shared pool free memory 108671032
>shared pool transaction 16894064
>shared pool PL/SQL DIANA 997064
>shared pool FileOpenBlock 16270720
>shared pool PL/SQL MPCODE 324152
>shared pool library cache 75286624
>shared pool miscellaneous 289621728
>shared pool pl/sql source 6424
>shared pool PLS non-lib hp 3320
>shared pool joxlod: in ehe 294808
>shared pool joxs heap init 1544
>shared pool sim memory hea 674624
>shared pool table definiti 3184
>shared pool trigger defini 3192
>shared pool trigger inform 664
>shared pool type object de 85136
>shared pool VIRTUAL CIRCUITS 19097080
>shared pool dictionary cache 3229952
>shared pool event statistics per sess 105823760
>shared pool fixed allocation callback 576
>large pool free memory 167669432
>large pool session heap 838963528
>java pool free memory 62521344
>java pool memory in use 4587520
Here's the top of an hourly statspack too:
Snap Id Snap Time Sessions Curs/Sess Comment
--------- ------------------ -------- ---------
-------------------
Begin Snap: 71 09-Dec-05 08:00:01 3,563 8.8
End Snap: 81 09-Dec-05 09:00:03 3,684 8.6
Elapsed: 60.03 (mins)
Cache Sizes (end)
Buffer Cache: 288M Std Block Size: 4K
Shared Pool Size: 400M Log Buffer: 1,024K
Load Profile
~~~~~~~~~~~~ Per Second Per
Transaction
---------------
---------------
Redo size: 23,198.51
770.49
Logical reads: 12,869.94
427.45
Block changes: 146.08
4.85
Physical reads: 585.81
19.46
Physical writes: 29.58
0.98
User calls: 1,170.96
38.89
Parses: 414.92
13.78
Hard parses: 18.53
0.62
Sorts: 52.37
1.74
Logons: 1.20
0.04
Executes: 584.78
19.42
Transactions: 30.11
% Blocks changed per Read: 1.14 Recursive Call %: 58.79
Rollback per transaction %: 51.67 Rows per Sort: 18.64
Instance Efficiency Percentages (Target 100%)
Buffer Nowait %: 99.98 Redo NoWait %: 100.00
Buffer Hit %: 95.57 In-memory Sort %: 99.72
Library Hit %: 96.73 Soft Parse %: 95.53
Execute to Parse %: 29.05 Latch Hit %: 99.89
Parse CPU to Parse Elapsd %: 3.98 % Non-Parse CPU: 80.49
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 93.00 80.49
% SQL with executions>1: 67.80 87.77
% Memory for SQL w/exec>1: 83.83 98.39
Top 5 Timed Events
Event Waits Time (s)Ela Time
-------------------------------------------- ------------ ----------- -------- library cache load lock 3,225 2,895 37.78 library cache pin 3,737 2,026 26.44 db file sequential read 1,041,149 1,208 15.76 CPU time 919 12.00 latch free 35,511 1341.75
 |
 |