Hi All,

I've severe latching problem in my database, when I look in AWR but confused what is causing it.
Can someone guide me on this?
NoWait Waiter
Latch Name Where Misses Sleeps Sleeps

---

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

---

----------

---

In memory undo latch ktiFlush: child 0 5 0
In memory undo latch kturbk 0 2 2
cache buffers chains kcbchg: kslbegin: bufs not 0 573 69
cache buffers chains kcbgtcr: fast path 0 380 554
cache buffers chains kcbgtcr: kslbegin excl 0 298 547
cache buffers chains kcbrls: kslbegin 0 183 153
cache buffers chains kcbzgb: scan from tail. no 0 83 0
cache buffers chains kcbibr 0 31 40
cache buffers chains kcbchg: kslbegin: call CR 0 10 70
cache buffers chains kcbgcur: kslbegin 0 4 13
cache buffers chains kcbget: pin buffer 0 3 72
cache buffers chains kcbnew: new latch again 0 3 18
cache buffers chains kcbzwb 0 2 3
cache buffers chains kcbbxsv 0 1 3
cache buffers chains kcbcge 0 1 5
cache buffers lru chain kcbzgws 0 45 0
cache buffers lru chain kcbibr 0 43 97
cache buffers lru chain kcbo_link_q 0 20 5
cache buffers lru chain kcbw_quiesce_granule 0 5 0
cache buffers lru chain kcbgtcr:CR Scan:KCBRSKIP 0 1 0
enqueues ksqdel 0 1 0
kks stats kks stats alloc/free 0 1 1
library cache kglobpn: child: 0 2,978 4,471
library cache kgldti: 2child 0 2,417 12
library cache kglLockCursor 0 1,604 3,398
library cache kglpin 0 1,426 538
library cache kglpndl: child: after proc 0 1,185 7
library cache kglhdgn: child: 0 766 1,485
library cache kglpnp: child 0 618 8,902
library cache kglhdgc: child: 0 331 0
library cache kgldte: child 0 0 95 809
library cache kglpndl: child: before pro 0 92 1,800
library cache kglic 0 50 0
library cache kglnti 0 30 0
library cache kglati 0 28 0
library cache kglobld 0 15 21
library cache kglScanDependency 0 7 2
library cache kglukp: child 0 6 5
library cache kglhdbrnl: child 0 1 0
library cache lock kgllkdl: child: no lock ha 0 10,017 217
library cache lock kgllkdl: child: cleanup 0 87 119
library cache lock kgllkal: child: multiinsta 0 80 48
library cache lock alloc kgllkget 0 1 1
library cache pin kglpndl 0 34 6
library cache pin kglpnp: child 0 17 23
library cache pin kglpnal: child: alloc spac 0 13 35
object queue header oper kcbo_switch_cq 0 12 5
object queue header oper kcbw_link_q 0 9 11
object queue header oper kcbo_link_q:reget 0 5 0
object queue header oper kcbw_unlink_q 0 2 10
redo allocation kcrfw_redo_gen: redo alloc 0 13 0
row cache objects kqreqd: reget 0 7 0
row cache objects kqreqd 0 1 0
session allocation ksuprc 0 44 7
session allocation ksudlc 0 24 25
session allocation ksuxds: not user session 0 16 1
session allocation ksucri 0 9 62
session allocation kspallmod 0 2 0
shared pool kghalo 0 2,269 126
shared pool kghupr1 0 690 3,122

Thanks for the help.

Thanks,

Rana.

'{
DB Name DB Id Instance Inst Num Release RAC Host

---

-----------

---

--------

---

---

---

orvprd 3135597156 orvprd 1 10.2.0.3.0 NO ix205

Snap Id Snap Time Sessions Curs/Sess

---

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

---

---------
Begin Snap: 8273 22-Jun-09 03:00:39 132 22.4
End Snap: 8275 22-Jun-09 05:00:42 135 19.3
Elapsed: 120.05 (mins)
DB Time: 565.08 (mins)

Cache Sizes
~~~~~~~~~~~ Begin End

---

----------
Buffer Cache: 7,392M 7,264M Std Block Size: 8K
Shared Pool Size: 2,768M 2,896M Log Buffer: 14,340K

Load Profile
~~~~~~~~~~~~ Per Second Per Transaction

---

---

Redo size: 33,684.43 7,421.03
Logical reads: 133,813.99 29,480.60
Block changes: 4,946.72 1,089.81
Physical reads: 2.37 0.52
Physical writes: 12.92 2.85
User calls: 154.34 34.00
Parses: 438.57 96.62
Hard parses: 1.26 0.28
Sorts: 5,130.10 1,130.21
Logons: 0.07 0.02
Executes: 5,604.35 1,234.70
Transactions: 4.54

% Blocks changed per Read: 3.70 Recursive Call %: 98.52
Rollback per transaction %: 0.18 Rows per Sort: 2.85

Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 100.00 In-memory Sort %: 100.00
Library Hit %: 100.01 Soft Parse %: 99.71
Execute to Parse %: 92.17 Latch Hit %: 99.32
Parse CPU to Parse Elapsd %: 8.09 % Non-Parse CPU: 99.46

Shared Pool Statistics Begin End

---

---

Memory Usage %: 84.14 84.89
% SQL with executions>1: 97.57 97.11
% Memory for SQL w/exec>1: 95.66 95.31

Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time Wait Class

---

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

---

------

---

----------
CPU time 14,995 44.2
TCP Socket (KGAS) 611,193 6,230 10 18.4 Network
latch: library cache 21,661 4,756 220 14.0 Concurrenc
latch: shared pool 3,333 320 96 0.9 Concurrenc
db file sequential read 17,316 193 11 0.6 User I/O
-------------------------------------------------------------}'

Thanks for the help.

Rana.

It is possible that your application code does not used bind variables. Try to adapt following script :http://asktom.oracle.com/pls/asktom/f?p=100:11:2537622989124160::::P11_QUESTION_ID:1163635055580 to check your shared pool.

Hi,
There is a relation to parse here. You are right about hard parse (there are not that many), but there are many soft parses.
You can see that most of the latch contetion is on library cache latch (which is used in soft parse - to search the library cache for the cursor).

I had several cases in which many soft parses caused high library cache latch contention and this caused the server to hang with 100% CPU.

Liron Amitzi
Senior DBA consultant
http://www.dbsnaps.com
http://www.orbiumsoftware.com

Thanks Jonathan and Amitzi,

I bit description at the background is. Since this application uses lot of literal values a year back I'd suggested and changed cursor_sharing to similar. Since then there was improvement of around 25%.

Though I've cursor_sharing as similar, what I'm confused is about Library cache latches without having many hard parses.

Could this be a problem of SGA getting swapped?

I've 3 CPUs in my system and 16GB of RAM, but my SGA_TARGET is around 10GB and PGA_AGGREGATE_TARGET is 2 GB.

I've also check minperm% is set to 3 and maxperm% is 90.

Can you let me know your inputs on this please?

Thanks,

Rana.

Hi Rana,
There should be no relation to the SGA being swapped, since even if it is swapped, Oracle thinks it is in memory.

Curosr sharing improved your performance since it reduced hard parses, but soft parses still happen.
When you execute a query, you have 3 choices:
1. Oracle hash the query, doesn't find it in the shared pool and performs hard parse (execution paln, syntax, etc.)
2. Oracle hash the query, finds it and executes it (soft parse)
3. Oracle doesn't have to hash the query becuase you have a "handle" to the query and you just execute it. This is "no parse".

Now, regarding the soft parses, it's not easy. We need to change the sql execution to use "no parse" instead of soft parse.
Several questions:
1. What Oracle version are you using?
2. What application are you using?
3. What is the value of SESSION_CACHED_CURSORS parameter in your database (it may help in caching sql "handlers" to prevent soft parse)

Liron Amitzi
Senior DBA consultant
[http://www.dbsnaps.com]
[http://www.orbiumsoftware.com]

Hi Rana,

Does it make sense that this application is executing so many queries? Maybe you should contact the application provider.

I would try to increase session_cached_cursor to let's say 200. Also if the parameter CURSOR_SPACE_FOR_TIME is false, set it to true.

Be aware that we are changing the shared pool behavior here, so be careful and pay attention if there is any problem. Also it is always recommended to test the changes on a test environment first.

Liron Amitzi
Senior DBA consultant
[www.dbsnaps.com]
[www.orbiumsoftware.com]