RE: New Connection for Each Query

The best way to determine if the application is logging in and out repeatedly is to turn on auditing of sessions. Then query the dba_audit_session view. Don't leave the auditing on too long if you are getting thousands of logins, or you will fill up the system tablespace. Here is how to do it:

connect sys as sysdba
alter system set audit_trail=DB scope=spfile; shutdown immediate
startup
audit session;

• Let the system activity go back to normal and wait long enough to collect some audit rows.

select * from dba_audit_session;
noaudit session;
truncate table sys.aud$;

If you do find that there are many connections connecting and disconnecting repeatedly, then one possible solution is to configure the database for Multi Threaded Server (MTS).

Pat

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Charlotte Hammond Sent: Wednesday, June 11, 2008 10:39 AM
To: oracle-l_at_freelists.org
Subject: Re: New Connection for Each Query

Hi Kyle & Mark,
Thanks for the ideas. Unfortunately this is a legacy app (on Oracle 9i) with no "switch" to turn on connection pooling. Kyle - I'll try your suggestion of running nothing but logons at the current rate (only 2 per second but the CPUs are slow anyway) to see what load I get. Can't do this easily just now due to constant use of the system but hopefully get time to try it later. Thank you!
Charlotte

• Original Message ---- From: "kyle Hailey" <kylelf_at_xxxxxxxxx>
  • To: mark.powell_at_xxxxxxx
  • Date: Mon, 9 Jun 2008 17:22:46 -0700 Probably the best method is just to measure it yourself if you can. Write a script that connects/disconnects at a high rate and measure the CPU effect on the machine when nothing else is running. Run the script at the logon rate of your current applciaiton. You can get the logon rate from statspack.

If you are on 10g you can look at the time model for connection time , ie "connection management call elapsed time" in view v$sys_time_model. Look at this with AWR report or Statspack to get the delta and see how much time you spend connecting much/most of which can be CPU, but I've had smaller than expected values from this statistic before (1/10th of what I expected) so I just set up a script to logon/logoff when nothing else was going on the machine and measured how much CPU was being used. It's quite expensive. In my case , if I recall correctly, 4 connections a second was a full CPU the machine.
--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l

[CONFIDENTIALITY AND PRIVACY NOTICE] Information transmitted by this email is proprietary to Medtronic and is intended for use only by the individual or entity to which it is addressed, and may contain information that is private, privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please delete this mail from your records.  

To view this notice in other languages you can either select the following link or manually copy and paste the link into the address bar of a web browser: http://emaildisclaimer.medtronic.com
--

http://www.freelists.org/webpage/oracle-l Received on Wed Jun 11 2008 - 11:28:36 CDT