Home -> Mailing Lists -> Oracle-L -> Fwd: Re: jdbc - connection pool and connect frequency

Fwd: Re: jdbc - connection pool and connect frequency

From: Radoulov, Dimitre <cichomitiko_at_gmail.com>
Date: Fri, 03 Dec 2010 13:12:06 +0100
Message-ID: <4CF8DE96.7080305_at_gmail.com> 





Resend due to over quoting.





So I run a little test on one of my test machines (Oracle 10.2 , 
Solaris) with only EM dbconsole runing:



At 11:32 we have 11 records in dba_audit_trail for the user SYSMAN:



11:32:11 SQL> l


   1  select username, timestamp, count(1)
   2  from dba_audit_session


   3  where username = 'SYSMAN'


   4  group by username, timestamp


   5* order by 2


11:32:12 SQL> /



USERNAME                       TIMESTAMP           COUNT(1)
------------------------------ ----------------- ----------
SYSMAN                         03-dec-2010 10:50          7
SYSMAN                         03-dec-2010 10:50          1
SYSMAN                         03-dec-2010 10:50          1
SYSMAN                         03-dec-2010 10:53          1
SYSMAN                         03-dec-2010 10:53          1
                                                  ----------
sum                                                      11





Elapsed: 00:00:00.00




6 records in v$session:



11:32:13 SQL> select username, logon_time
11:32:20   2  from v$session
11:32:30   3  where username='SYSMAN'
11:32:35   4  order by 2;

USERNAME                       LOGON_TIME
------------------------------ -----------------
SYSMAN                         03-dec-2010 10:49
SYSMAN                         03-dec-2010 10:50
SYSMAN                         03-dec-2010 10:50
SYSMAN                         03-dec-2010 10:50
SYSMAN                         03-dec-2010 10:50
SYSMAN                         03-dec-2010 10:53





6 rows selected.



Elapsed: 00:00:00.00


11:32:40 SQL>


And the following entries in the listener log:



03-DEC-2010 10:50:21 * 



(CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVICE_NAME=<my_sid>)) 

* (ADDRESS=(PROTOCOL=tcp)(HOST=<my_host>)(PORT=36915)) * establish * 
<my_sid> * 0


03-DEC-2010 10:50:21 * 



(CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVICE_NAME=<my_sid>)) 

* (ADDRESS=(PROTOCOL=tcp)(HOST=<my_host>)(PORT=36914)) * establish * 
<my_sid> * 0


03-DEC-2010 10:50:21 * 



(CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVICE_NAME=<my_sid>)) 

* (ADDRESS=(PROTOCOL=tcp)(HOST=<my_host>)(PORT=36916)) * establish * 
<my_sid> * 0


03-DEC-2010 10:50:22 * 



(CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVICE_NAME=<my_sid>)) 

* (ADDRESS=(PROTOCOL=tcp)(HOST=<my_host>)(PORT=36918)) * establish * 
<my_sid> * 0


03-DEC-2010 10:50:22 * 



(CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVICE_NAME=<my_sid>)) 

* (ADDRESS=(PROTOCOL=tcp)(HOST=<my_host>)(PORT=36919)) * establish * 
<my_sid> * 0


03-DEC-2010 10:50:22 * 



(CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVICE_NAME=<my_sid>)) 

* (ADDRESS=(PROTOCOL=tcp)(HOST=<my_host>)(PORT=36920)) * establish * 
<my_sid> * 0


03-DEC-2010 10:50:22 * 



(CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVICE_NAME=<my_sid>)) 

* (ADDRESS=(PROTOCOL=tcp)(HOST=<my_host>)(PORT=36921)) * establish * 
<my_sid> * 0


03-DEC-2010 10:50:23 * 



(CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVICE_NAME=<my_sid>)) 

* (ADDRESS=(PROTOCOL=tcp)(HOST=<my_host>)(PORT=36922)) * establish * 
<my_sid> * 0


03-DEC-2010 10:50:23 * 



(CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVICE_NAME=<my_sid>)) 

* (ADDRESS=(PROTOCOL=tcp)(HOST=<my_host>)(PORT=36923)) * establish * 
<my_sid> * 0


03-DEC-2010 10:53:00 * 



(CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVICE_NAME=<my_sid>)) 

* (ADDRESS=(PROTOCOL=tcp)(HOST=<my_host>)(PORT=36946)) * establish * 
<my_sid> * 0


03-DEC-2010 10:53:03 * 



(CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVICE_NAME=<my_sid>)) 

* (ADDRESS=(PROTOCOL=tcp)(HOST=<my_host>)(PORT=36948)) * establish * 
<my_sid> * 0


03-DEC-2010 10:53:03 * 



(CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SID=<my_sid>)(SERVER=DEDICATED)) 

* (ADDRESS=(PROTOCOL=tcp)(HOST=<my_host>)(PORT=36949)) * establish * 
<my_sid> * 0


03-DEC-2010 10:53:03 * 



(CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVICE_NAME=<my_sid>)) 

* (ADDRESS=(PROTOCOL=tcp)(HOST=<my_host>)(PORT=36950)) * establish * 
<my_sid> * 0


03-DEC-2010 10:53:18 * 



(CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(sid=<my_sid>)(server=dedicated)) 

* (ADDRESS=(PROTOCOL=tcp)(HOST=<my_host>)(PORT=36954)) * establish * 
<my_sid> * 0


03-DEC-2010 11:02:07 * 



(CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVICE_NAME=<my_sid>)) 

* (ADDRESS=(PROTOCOL=tcp)(HOST=<my_host>)(PORT=37044)) * establish * 
<my_sid> * 0


03-DEC-2010 11:02:50 * 



(CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(sid=<my_sid>)(server=dedicated)) 

* (ADDRESS=(PROTOCOL=tcp)(HOST=<my_host>)(PORT=37057)) * establish * 
<my_sid> * 0


03-DEC-2010 11:31:34 * 



(CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVICE_NAME=<my_sid>)) 

* (ADDRESS=(PROTOCOL=tcp)(HOST=<my_host>)(PORT=37333)) * establish * 
<my_sid> * 0


03-DEC-2010 11:31:37 * 



(CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(sid=<my_sid>)(server=dedicated)) 

* (ADDRESS=(PROTOCOL=tcp)(HOST=<my_host>)(PORT=37334)) * establish * 
<my_sid> * 0




I believe that not all entries in the listener log file (see the entries 
after 10:53h) are fresh/new connections.



   1  select username, timestamp from


   2  dba_audit_trail


   3  where timestamp between


   4  to_date('03-DEC-2010 11:29:00')


   5  and to_date('03-DEC-2010 11:33:00')
   6* order by 2


11:50:23 SQL> /



USERNAME                       TIMESTAMP
------------------------------ --------------------
DBSNMP                         03-dec-2010 11:32:18






And there are __jdbc__ entries in the listener logfile that are not 
present in dba_audit_trail:



$ egrep '03-DEC-2010 11:(29|3[0-33])' listener_beaiv.log
03-DEC-2010 11:29:53 * service_update * <my_sid> * 0
03-DEC-2010 11:31:34 * 



(CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVICE_NAME=<my_sid>)) 

* (ADDRESS=(PROTOCOL=tcp)(HOST=<my_host>)(PORT=37333)) * establish * 
<my_sid> * 0


03-DEC-2010 11:31:35 * service_update * <my_sid> * 0
03-DEC-2010 11:31:37 * 



(CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(sid=<my_sid>)(server=dedicated)) 

* (ADDRESS=(PROTOCOL=tcp)(HOST=<my_host>)(PORT=37334)) * establish * 
<my_sid> * 0


03-DEC-2010 11:32:18 * 



(CONNECT_DATA=(SID=<my_sid>)(CID=(PROGRAM=perl_at_<my_host>)(HOST=<my_host>)(USER=oracle))) 

* (ADDRESS=(PROTOCOL=tcp)(HOST=<my_host>)(PORT=37341)) * establish * 
<my_sid> * 0


03-DEC-2010 11:32:30 * 



(CONNECT_DATA=(CID=(PROGRAM=)(HOST=<my_host>)(USER=oracle))(COMMAND=services)(ARGUMENTS=64)(SERVICE=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=<my_host>)(PORT=1524))))(VERSION=169870592)) 

* services * 0


03-DEC-2010 11:32:31 * 



(CONNECT_DATA=(CID=(PROGRAM=)(HOST=<my_host>)(USER=oracle))(COMMAND=status)(ARGUMENTS=64)(SERVICE=(ADDRESS=(PROTOCOL=TCP)(HOST=<my_host>)(PORT=1524)))(VERSION=169870592)) 

* status * 0


03-DEC-2010 11:33:33 * ping * 0


03-DEC-2010 11:33:33 * 



(CONNECT_DATA=(CID=(PROGRAM=)(HOST=<my_host>)(USER=oracle))(COMMAND=status)(ARGUMENTS=64)(SERVICE=(ADDRESS=(PROTOCOL=TCP)(HOST=<my_host>)(PORT=1524)))(VERSION=169870592)) 

* status * 0


03-DEC-2010 11:33:46 * 



(CONNECT_DATA=(SID=<my_sid>)(CID=(PROGRAM=perl_at_<my_host>)(HOST=<my_host>)(USER=oracle))) 

* (ADDRESS=(PROTOCOL=tcp)(HOST=<my_host>)(PORT=37361)) * establish * 
<my_sid> * 0






Regards


Dimitre



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


Received on Fri Dec 03 2010 - 06:12:06 CST












Original text of this message