Home » RDBMS Server » Performance Tuning » latch: session allocation and CPU
latch: session allocation and CPU [message #290775] Mon, 31 December 2007 13:53 Go to next message
panelan
Messages: 9
Registered: December 2006
Location: Seoul, R.O.Korea
Junior Member

I have serious problem on DBMS.
Sometimes it has lots of 'latch: session allocation' waits and DBMS performance getting lower.

At that time total connections are almost 4000 and active connections are between 300 and 400.
Yes maybe the reason should be there are a lot of session on DBMS.

However, that amounts of connections are not first time event. but lots of 'latch: session allocation' wats are started on this december.

I doubt on high CPU utilization but as you people know that latch is kind of memory lock. but on DBMS, 'latch: session allocation' is problem. There was some latchs but it is release quickly and it's fewer.

On H/W machine, CPU utilization is abnormaly high. When issue occured average CPU utilization is more than 90% and memory utilization is about 60%.

That's why I think there could be some kind of performance relationship between CPU and 'latch: session allocation.
and I have no idea about that.

If you people have any information between CPU and 'latch: session allocation, please let me know that. I appreciate.

P.S. The RDBMS version is 10.2.0.2 and SGA MAX and target is 20Gb.

[Updated on: Mon, 31 December 2007 14:06]

Report message to a moderator

Re: latch: session allocation and CPU [message #290778 is a reply to message #290775] Mon, 31 December 2007 14:29 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/84315/74940/
Which of the above recommendations have you followed & what were the results?
>SGA MAX and target is 20Gb.
WHY so LARGE?

Re: latch: session allocation and CPU [message #290886 is a reply to message #290778] Wed, 02 January 2008 00:49 Go to previous messageGo to next message
panelan
Messages: 9
Registered: December 2006
Location: Seoul, R.O.Korea
Junior Member

I'm not sure your recommendations are suitable for this issue.
It would be grateful, if you explain how new ASH help me to investigate this issue.

Also, SGA target is recommendation from local oracle. Our DBMS system had low buffer hit ratio and we have enough server memory, so local oracle recommand that increasing SGA target upto 20Gb. Before we had 8Gb for SGA target.

[Updated on: Wed, 02 January 2008 00:50]

Report message to a moderator

Re: latch: session allocation and CPU [message #290897 is a reply to message #290775] Wed, 02 January 2008 01:04 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>Our DBMS system had low buffer hit ratio
So with a low BCHR, by increasing SGA size , it will LOWER the ratio by increasing the size of the base SGA.
How lame!
I can show you how to reach any desired value of BCHR; which is a mythical indicator of performance.
Ready, Fire, AIM!
Re: latch: session allocation and CPU [message #290903 is a reply to message #290886] Wed, 02 January 2008 01:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Increasing SGA when there is no use is a source of BAD performances.
So Ana's question is an important one.
If your only reason is there is RAM so we allocate it, then this is a bad reason.

Regards
Michel
Re: latch: session allocation and CPU [message #290937 is a reply to message #290897] Wed, 02 January 2008 04:12 Go to previous messageGo to next message
panelan
Messages: 9
Registered: December 2006
Location: Seoul, R.O.Korea
Junior Member

Hmmm....
I don't understand why the focus of this issue is moving into SGA target. If the large SGA target could be the reason of lot's of 'latch: session allocation' issue, please let me know.

I want to show you the local oracle reports about our DBMS but I don't have any right to do. Mainly performance issue on SGA target is Physical Reads. Local oracle consultant thought Physical Reads on our DBMS can be reduced to increase SGA size. Also the DBMS BCHR is lower and our server has lots of memories so even if we increase SGA size 8Gb to 20Gb, memory utilization will be increased just 10% more. So we decided increasing SGA target to 20Gb.
As the result, Physical Reads reduced 75% and BCHR increased to 98%.

Because of large SGA, our DBMS performance is better. I don't think on-line transaction performance is better but we have heavy batch programs then that programs performance is get better.
Re: latch: session allocation and CPU [message #291082 is a reply to message #290937] Wed, 02 January 2008 23:37 Go to previous message
trantuananh24hg
Messages: 668
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
hmm! I have not ever seen the server which had so big physical memory like yours. The SGA_TARGET is to be set by 20GB, it means that your machine's physcial memory about 40GB or 50GB.

You can post your report of SGA by connecting as sysdba.

Collect by Statspack, ASH or AWR to generate exactly about latch, and post its portion here.

Previous Topic: Choose your Buffer Cache Hit Ratio
Next Topic: Group by clause
Goto Forum:
  


Current Time: Sat Dec 03 22:30:15 CST 2016

Total time taken to generate the page: 0.06306 seconds