 Latch Contention [message #177593] |
Thu, 15 June 2006 11:53  |
vikramjogi
Messages: 71
Registered: June 2005
Location: Scranton
|
Member |
|
|
Hello All,
Please advice me on how to resolve latch contention. I have checked the application code, it is making use of the bind variables, I dont think the problem is with the shared pool sizing, I am not sure if the open_cursors and session_cached_cursors parameter values have any affect on the latch contention at all. Please shred some light on setting these parameters.
Thanks much,
Vikram
|
|
|
|
|
|
| Re: Latch Contention [message #177599 is a reply to message #177595] |
Thu, 15 June 2006 13:07  |
vikramjogi
Messages: 71
Registered: June 2005
Location: Scranton
|
Member |
|
|
Hello,
OS: AIX 5L
Oracle Version: 9.2.0.5
What evidence do you have that demonstrates latch contention is a problem in your database?
I have checked the statspack reports and reads like below
Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
------------ ---------------
Redo size: 8,315.86 18,654.33
Logical reads: 23,967.23 53,763.87
Block changes: 58.65 131.57
Physical reads: 290.10 650.77
Physical writes: 38.80 87.03
User calls: 170.33 382.10
Parses: 108.19 242.70
Hard parses: 1.00 2.24
Sorts: 49.18 110.33
Logons: 0.16 0.36
Executes: 149.96 336.40
Transactions: 0.45
% Blocks changed per Read: 0.24
Recursive Call %: 39.70
Rollback per transaction %: 5.91
Rows per Sort: 50.17
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 99.99 Redo NoWait %: 100.00
Buffer Hit %: 98.94 In-memory Sort %: 100.00
Library Hit %: 99.04 Soft Parse %: 99.08
Execute to Parse %: 27.85 Latch Hit %: 99.97
Parse CPU to Parse Elapsd %: 0.42 % Non-Parse CPU: 97.11
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 94.18 95.71
% SQL with executions>1: 86.64 86.71
% Memory for SQL w/exec>1: 99.14 99.17
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
latch free 3,153,465 920,160 61.94
SQL*Net message from client 541,226 483,702 32.56
row cache lock 10,401 30,627 2.06
rdbms ipc message 10,117 25,749 1.73
buffer busy waits 7,407 5,919 .40
Looking at the statspack report as far as i know it is pretty clear that latch free is the culprit. Please let me know if you need further information to advise me on what can be done.
Thankyou,
Vikram
|
|
|
|
|
|
| Re: Latch Contention [message #177610 is a reply to message #177604] |
Thu, 15 June 2006 13:59 |
vikramjogi
Messages: 71
Registered: June 2005
Location: Scranton
|
Member |
|
|
Thanks for the information. I have been googling and as a part of my searching I have posted this question here. And infact I thought that was the reason why we have forums. No one just posts it on the forum with out doing their homework.
Thanks,
Vikram
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
