RE: Middle Tier spawning sessions, possible performance issues
Title: RE: Middle Tier spawning sessions, possible performance issues Anjo, Actually I saw this in your (et al) famous YAPP paper. Still curious, Steve -Original Message-From: Orr, Steve Sent: Thursday, January 30, 2003 10:32 AMTo: Multiple recipients of list ORACLE-LSubject: RE: Middle Tier spawning sessions, possible performance issues I read somewhere that for apps that constantly logon/logoff (like web apps), one possible thing to do is increase the cache size for the AUDSES$ sequence. Anyone done this and seen improvements? Curiously, Steve Orr -Original Message- From: Anjo Kolk [mailto:[EMAIL PROTECTED]] Sent: Thursday, January 30, 2003 9:15 AM To: Multiple recipients of list ORACLE-L Subject: Re: Middle Tier spawning sessions, possible performance issues You are kind of on the right track. The number of sessions doesn't really matter. What matters is that they logon/logoff all the time. That is the worst thing that you can do in an Oracle database. Why? The session will allocate the cursors, parse them, close them everytime the session will logon/logoff. The symptoms that you will see. Latch contention (shared pool, library cache), shared pool fragmentation etc. Anjo. On Thursday 30 January 2003 05:14, [EMAIL PROTECTED] wrote: > I'm far more of a developer than a DBA, but when someone told me this it > set off a big red light in my head. > > We are using an Oracle Backend with a .net front front. One of our .net > guys told me that the middle tier they are using 'spawns' sessions. > > We have 2 pretty distinct skillsets here so fixing the middle tier is > probably way beyond my pervue. However, they told me that in a recent demo, > the performance degraded overtime. Its my understanding that generally this > is caused by one of two things. > > 1. Failure to use Bind Variables... we are using them everywhere. > > 2. Too many sessions. > > Am I on the right track here? How much would shared server mode help? This > may be an enormous issue since they are expecting 250 contiguous users. > Another option I tossed around was moving as much logic from the client > side to the database to avoid the session spawning. I know that generally > this is a good idea, but its difficult when the database people are lousy > in C# and the .net people our lousy in PL/SQL and SQL. > > Any opinions? -- Anjo Kolk http://www.oraperf.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Anjo Kolk INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Middle Tier spawning sessions, possible performance issues
Title: RE: Middle Tier spawning sessions, possible performance issues Any takers on this ?? -Original Message-From: Orr, Steve Sent: Thursday, January 30, 2003 10:32 AMTo: Multiple recipients of list ORACLE-LSubject: RE: Middle Tier spawning sessions, possible performance issues I read somewhere that for apps that constantly logon/logoff (like web apps), one possible thing to do is increase the cache size for the AUDSES$ sequence. Anyone done this and seen improvements? Curiously, Steve Orr -Original Message- From: Anjo Kolk [mailto:[EMAIL PROTECTED]] Sent: Thursday, January 30, 2003 9:15 AM To: Multiple recipients of list ORACLE-L Subject: Re: Middle Tier spawning sessions, possible performance issues You are kind of on the right track. The number of sessions doesn't really matter. What matters is that they logon/logoff all the time. That is the worst thing that you can do in an Oracle database. Why? The session will allocate the cursors, parse them, close them everytime the session will logon/logoff. The symptoms that you will see. Latch contention (shared pool, library cache), shared pool fragmentation etc. Anjo. On Thursday 30 January 2003 05:14, [EMAIL PROTECTED] wrote: > I'm far more of a developer than a DBA, but when someone told me this it > set off a big red light in my head. > > We are using an Oracle Backend with a .net front front. One of our .net > guys told me that the middle tier they are using 'spawns' sessions. > > We have 2 pretty distinct skillsets here so fixing the middle tier is > probably way beyond my pervue. However, they told me that in a recent demo, > the performance degraded overtime. Its my understanding that generally this > is caused by one of two things. > > 1. Failure to use Bind Variables... we are using them everywhere. > > 2. Too many sessions. > > Am I on the right track here? How much would shared server mode help? This > may be an enormous issue since they are expecting 250 contiguous users. > Another option I tossed around was moving as much logic from the client > side to the database to avoid the session spawning. I know that generally > this is a good idea, but its difficult when the database people are lousy > in C# and the .net people our lousy in PL/SQL and SQL. > > Any opinions? -- Anjo Kolk http://www.oraperf.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Anjo Kolk INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Middle Tier spawning sessions, possible performance issues
Title: RE: Middle Tier spawning sessions, possible performance issues I read somewhere that for apps that constantly logon/logoff (like web apps), one possible thing to do is increase the cache size for the AUDSES$ sequence. Anyone done this and seen improvements? Curiously, Steve Orr -Original Message- From: Anjo Kolk [mailto:[EMAIL PROTECTED]] Sent: Thursday, January 30, 2003 9:15 AM To: Multiple recipients of list ORACLE-L Subject: Re: Middle Tier spawning sessions, possible performance issues You are kind of on the right track. The number of sessions doesn't really matter. What matters is that they logon/logoff all the time. That is the worst thing that you can do in an Oracle database. Why? The session will allocate the cursors, parse them, close them everytime the session will logon/logoff. The symptoms that you will see. Latch contention (shared pool, library cache), shared pool fragmentation etc. Anjo. On Thursday 30 January 2003 05:14, [EMAIL PROTECTED] wrote: > I'm far more of a developer than a DBA, but when someone told me this it > set off a big red light in my head. > > We are using an Oracle Backend with a .net front front. One of our .net > guys told me that the middle tier they are using 'spawns' sessions. > > We have 2 pretty distinct skillsets here so fixing the middle tier is > probably way beyond my pervue. However, they told me that in a recent demo, > the performance degraded overtime. Its my understanding that generally this > is caused by one of two things. > > 1. Failure to use Bind Variables... we are using them everywhere. > > 2. Too many sessions. > > Am I on the right track here? How much would shared server mode help? This > may be an enormous issue since they are expecting 250 contiguous users. > Another option I tossed around was moving as much logic from the client > side to the database to avoid the session spawning. I know that generally > this is a good idea, but its difficult when the database people are lousy > in C# and the .net people our lousy in PL/SQL and SQL. > > Any opinions? -- Anjo Kolk http://www.oraperf.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Anjo Kolk INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Middle Tier spawning sessions, possible performance issues
You are kind of on the right track. The number of sessions doesn't really matter. What matters is that they logon/logoff all the time. That is the worst thing that you can do in an Oracle database. Why? The session will allocate the cursors, parse them, close them everytime the session will logon/logoff. The symptoms that you will see. Latch contention (shared pool, library cache), shared pool fragmentation etc. Anjo. On Thursday 30 January 2003 05:14, [EMAIL PROTECTED] wrote: > I'm far more of a developer than a DBA, but when someone told me this it > set off a big red light in my head. > > We are using an Oracle Backend with a .net front front. One of our .net > guys told me that the middle tier they are using 'spawns' sessions. > > We have 2 pretty distinct skillsets here so fixing the middle tier is > probably way beyond my pervue. However, they told me that in a recent demo, > the performance degraded overtime. Its my understanding that generally this > is caused by one of two things. > > 1. Failure to use Bind Variables... we are using them everywhere. > > 2. Too many sessions. > > Am I on the right track here? How much would shared server mode help? This > may be an enormous issue since they are expecting 250 contiguous users. > Another option I tossed around was moving as much logic from the client > side to the database to avoid the session spawning. I know that generally > this is a good idea, but its difficult when the database people are lousy > in C# and the .net people our lousy in PL/SQL and SQL. > > Any opinions? -- Anjo Kolk http://www.oraperf.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Anjo Kolk INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Middle Tier spawning sessions, possible performance issues
250 connections from the middle tier does sound a bit suspect, way over the top. I'm assuming 'spawns' relates to establishing connections as and when required and then disconnecting when done. I think you will find that there is quite an Oracle overhead in handling all of the connect/disconnect requests that Oracle is coping with in a non connection pooled environment. Ideally, you shouldn't be spawning connection from the middle tier. You should look at connection pooling ie. configure a fixed pool of connections per middle tier server, 20/40/whatever (and have more than one middle tier server, load balanced) and have these connect when the app/web server starts up. You then round robin the connections to the clients when required, and throw them back in the pool when finished. Ade -Original Message- Sent: 30 January 2003 14:35 To: Multiple recipients of list ORACLE-L R - I haven't yet been the victim of .net (thankfully), and I hope someone with direct experience will reply. But just in case, I'll mention a couple of ideas. Try to sample the SQL that is being inflicted on Oracle. Microsoft interfaces tend to have default settings for the lowest common denominator (like accessing a flat file). I've seen them do stuff like pull the entire table over just to verify that it hasn't changed. This sort of thing can usually be corrected by using other than default settings. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, January 30, 2003 7:15 AM To: Multiple recipients of list ORACLE-L I'm far more of a developer than a DBA, but when someone told me this it set off a big red light in my head. We are using an Oracle Backend with a .net front front. One of our .net guys told me that the middle tier they are using 'spawns' sessions. We have 2 pretty distinct skillsets here so fixing the middle tier is probably way beyond my pervue. However, they told me that in a recent demo, the performance degraded overtime. Its my understanding that generally this is caused by one of two things. 1. Failure to use Bind Variables... we are using them everywhere. 2. Too many sessions. Am I on the right track here? How much would shared server mode help? This may be an enormous issue since they are expecting 250 contiguous users. Another option I tossed around was moving as much logic from the client side to the database to avoid the session spawning. I know that generally this is a good idea, but its difficult when the database people are lousy in C# and the .net people our lousy in PL/SQL and SQL. Any opinions? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: <[EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Live Life in Broadband www.telewest.co.uk The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Statements and opinions expressed in this e-mail may not represent those of the company. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender immediately and delete the material from any computer. == -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Adrian Roe INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services
Middle Tier spawning sessions, possible performance issues
I'm far more of a developer than a DBA, but when someone told me this it set off a big red light in my head. We are using an Oracle Backend with a .net front front. One of our .net guys told me that the middle tier they are using 'spawns' sessions. We have 2 pretty distinct skillsets here so fixing the middle tier is probably way beyond my pervue. However, they told me that in a recent demo, the performance degraded overtime. Its my understanding that generally this is caused by one of two things. 1. Failure to use Bind Variables... we are using them everywhere. 2. Too many sessions. Am I on the right track here? How much would shared server mode help? This may be an enormous issue since they are expecting 250 contiguous users. Another option I tossed around was moving as much logic from the client side to the database to avoid the session spawning. I know that generally this is a good idea, but its difficult when the database people are lousy in C# and the .net people our lousy in PL/SQL and SQL. Any opinions? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: <[EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Middle Tier spawning sessions, possible performance issues
R - I haven't yet been the victim of .net (thankfully), and I hope someone with direct experience will reply. But just in case, I'll mention a couple of ideas. Try to sample the SQL that is being inflicted on Oracle. Microsoft interfaces tend to have default settings for the lowest common denominator (like accessing a flat file). I've seen them do stuff like pull the entire table over just to verify that it hasn't changed. This sort of thing can usually be corrected by using other than default settings. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, January 30, 2003 7:15 AM To: Multiple recipients of list ORACLE-L I'm far more of a developer than a DBA, but when someone told me this it set off a big red light in my head. We are using an Oracle Backend with a .net front front. One of our .net guys told me that the middle tier they are using 'spawns' sessions. We have 2 pretty distinct skillsets here so fixing the middle tier is probably way beyond my pervue. However, they told me that in a recent demo, the performance degraded overtime. Its my understanding that generally this is caused by one of two things. 1. Failure to use Bind Variables... we are using them everywhere. 2. Too many sessions. Am I on the right track here? How much would shared server mode help? This may be an enormous issue since they are expecting 250 contiguous users. Another option I tossed around was moving as much logic from the client side to the database to avoid the session spawning. I know that generally this is a good idea, but its difficult when the database people are lousy in C# and the .net people our lousy in PL/SQL and SQL. Any opinions? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: <[EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
