1.> In case of JDBC or any other form of connection pooling what happens to memory (PGA) if the oracle server is shutdown and there are, say, 50 number of connections pooled? Is the memory released back to OS?
2.> In case of Net8 connection will exiting through sqlplus will release the memory (PGA) to OS if we issue command shutdown immediate at the server?

Thanks for the reply.
When you say "
pga is in the dedicated/shared server processes on the server. if you shutdown -- they "go away", the memory is NOT in the java application." does that imply that if connections are pooled (on different machine than the oracle server) and if there is no client activity(i.e no DML issued) there will be still memory occupied by the pooled connections on oracle server in form of PGA?
Also if we use OS tool (We have HP-Unix) such as GLANCE it shows that there is memory occupied by Oracle process. However if we query V$PROCESS view the column PGA_USED_MEM shows different values.
Do i conclude that as long as connection is live(but not acxtive i.e no DML's) memory associated with it PGA is not released to OS?
Please comment

Thank You.

1) When will we have to go for JDBC connection pooling and for Net8 connection pooling? Which one is better? Could you please explain the pros and cons of the two?
2) How to create Net*8 connection pooling?

Thanx a lot.

From the JDBC developer guide:
"Connection pooling functionality minimizes
expensive operations in the creation and closing of sessions."
The manual does not explain futher. Could you please elaborate this?
And the concept of the connection pooling is to cache the connection physically. How could I find the number of connections cached at OS (unix) level?

Thanks for answer my query.

Trinh

trinh:

You can use lsof command under Unix to see what sockets the process has opened (lsof stands for list opened files).

I started sqlplus in one console (PID=4425)

and executed in another window:

lsof -p 4425

...
sqlplus 4425 p_jarmuz 6u IPv4 6708519 TCP pitr.asco.de:45609->bill.asco.de:1521 (ESTABLISHED)
...

You can read more about lsof in man section 8.

HTH,
Regards

Piotr,
On the SUn Solaris:
$ man lsof
No manual entry for lsof.
$

What's the platform of yours? thanks

trinh

Linux Debian/Intel x86

According to the man pages it should be also supported by Solaris, SunOS, AIX, HP-UX literally all Unix flavors.

As for Solaris itself:
Solaris 2.5.1, 2.6, 7, 8 BETA, and 8 BETA-Refresh

Check your OS version. And maybe on Solaris this command does not come standard and you must install it separately. You want to ask your sysadmin.

Regards

I have tried to figure out if the jdbc connection pooling configured in our 3-tier web application that includes 9iAS apache, oc4j and the infrastructure DB, and the customer DB as the backend. For some reasons we can not obtain the information of Connection Pooling from the developers.

On the Infra, I found 6 sessions were using " JDBC Thin Client". And on the customer DB, all the sessions were using" JDBC Thin Client". What these tell us? If the JDBC Connection Pooling adopted, only those database connections shown with " JDBC Thin Client" might use the cached connections? The rest of connections were not considered as "cached" connects at all?

select count(*),PROGRAM ,username from v$session where username not in ('SYS') group by PROGRAM ,username order by PROGRAM;

On the 9ias Infrastructure:

COUNT(*) PROGRAM USERNAME
---------- --------------------------------------------- --------------------
5 JDBC Thin Client DCM
1 JDBC Thin Client ORASSO_PS
9 httpd@domain.mil (TNS V1-V3) ORASSO
9 httpd@domain.mil (TNS V1-V3) ORASSO_PUBLIC
93 oidldapd@domain.mil (TNS V1-V3) ODSCOMMON
1 oidmon@domain.mil (TNS V1-V3) ODSCOMMON

On the backend

COUNT(*) PROGRAM USERNAME
---------- --------------------------------------------- ------------
17 JDBC Thin Client product_owner

By the theory, the connection pooling is a frame work for caches of database connections --- "Physically". Some of the connection have been "established", which can be verified from v$session, and others may be the open . These open connections in the pool are no way to be verified at the database level, because they have not been used for the database connections yet. How can I find/verify those have not been used the connections in the pool? Because they should be the "sockets" , can very find out at OS level. I used the following unix command, it gives all the established connections, but I want to find out not-yet connections:
lsof -i TCP:1521.
If you or anyone could help to find out the open connections of jdbc at OS level, I will be very thankful.

As you suggested to use 9iAS monitoring tools to find out the jdbc connection pooling infor, I used the OEM Website, it give very misleading results as such as
One time:
JDBC Usage:
Open JDBC Connections 11,207
Total JDBC Connections 22,387
Active Transactions 0
Transaction Commits 0
Transaction Rollbacks 0

At a other time
JDBC Usage:
Open JDBC Connections 0
Total JDBC Connections 0
Active Transactions 0
Transaction Commits 0
Transaction Rollbacks 0

We know the result were not correct/realistic.

I was configuring the db to be MTS then i was told that JDBC only uses SID only to connect to the db and not tnsnames.ora Is this true ?

If that's the case how do i configure some servers to use dedicated/shared server via overiding thru tnsnames (server=dedicated) or (server=shared) ?

Since the Oracle manual states than one can only be on a shared server settings using net8. And if the what the developer told me was true then configuring the db for MTS would be useless.

We're using jdbc thin.

Mr. Kyte can you post a link on how i can configure the listener ? I was reading 8i's Net8 Administrator's Guide
Release 8.1.6 chapter 7 (Configuring the Listener). And i didn't find any topic regarding the configuration of listener.ora for MTS.

I'm remotely administering our Db's and i issued netasst on my prompt and this is what i get and it seems to hang....

/export/home/oracle-> netasst
java.lang.NullPointerException
at oracle.ewt.lwAWT.BufferedFrame._init(Compiled Code)
at oracle.ewt.lwAWT.BufferedFrame.<init>(Compiled Code)
at oracle.sysman.emSDK.client.appContainer.ApplicationFrame.<init>(Compi
led Code)
at oracle.sysman.emSDK.client.appContainer.WebApplication.main(Compiled
Code)


Is it launching a gui ?

Thanks.

That means to configure the DB to MTS init.ora file is where i tinker right ? i thought i might have to add something in the listener.ora file.

Anyways since we're not using port 1521 i DO need to add the local_listener=<value> in the

mts_dispatcher=" (address= ... ) (lis=<lisneter_local_value>) "

right ?

Hi Tom,
It is my understanding that if you want your listener to run on any other port other than 1521, and you need database to be able to register with it, you need to set the local_listener init.ora parameter to point it to port you are using for your listener.

Is it possible to run the listener on any other port other than 1521 and NOT set the local_listener init.ora parameter? Will the connection via listener to the DB still work?

Why I ask this is because, At our client site the PORT setting in tnsnames.ora file uses different port than 1521 and when I checked our client development server, the local_listener init.ora parameter was not set.

But we can still connect to that DB? Is there something else that has been set that eliminates the need to set the local_listener parameter and be still able to connect?
OR maybe I'm missing someting?
Thanks,

Is it possible to configure JDBC connection pooling so that users DO NOT loose their unique identity? i.e., each user logs in with their unique user name & password.

Tom

We have got the proxy authentication working which is great and solves a huge problem for us, but how do we integrate this into the application server connection pooling? Do we need to write a java class to maintain the pool ourselves, one that also creates the proxy connection? In otherwords, is this a viable method to use with an application server other than 9IAS?

Thanks
Robert

Thanks Tom!

Hi Tom,
We are using java Applications to insert records into our database. The Java Applications use JDBC Oracle OCI Driver.
Earlier we used the thin driver. This has improved the performance by a vast degree. But one problem is still persistent.
The Java Applications use TCP protocol and Connection Pooling. The sessions are closed but the sockets opened on the server side are not released. We were wondering whether there is somthing which can be done in ths database to overcome with this problem. Or what should be do in order to close this sockets on the server side.
Any suggestions are welcome as always.
Thanks as always....... :D

The following link is not working anymore.

</code> http://asktom.oracle.com/wa/ask/owa/ask_tom_pkg.search <code>

Could you please fix this?

Tom

We use jdbc through connection pools to a 9ir2 oracle database.

we are getting a very strange error:

TNS-04610: No literals left, reached end of NV pair

This seems to occur sporadically from our live app server to the database during the day. I say sporadically because at this early stage we have not been able to pinpoint when it happens except for:

in a test page which just connects and counts from a table,
when you alter eg the sid in the connect string so that it doesn't resolve to any db on the machine, we consistently get this error, but only through jdbc driver.

I've checked the connection string on the live server but all seems ok and we only occasionally get this error.


I cannot find anything anywhere on metalink or the internet regarding a TNS 04610 error, please do you know what it is??

I was having similar problems and found that my URL for the java thin client had an incorrect address for the Oracle server that I was trying to hit. That… and I had some classpath problems. I was wondering if you had more information on this error as I’m one that wants to discover exactly why I was getting the error (where it comes from, why it’s a TNS and not an ORA error, etc). I was reading the description for the problem here and now I’m curious as what may be happening with this gentleman’s issue. It’s seems a bit more complicated than just a wrong hostname/ip address as was the case for me. Just curious… don’t expect anything really. But as previously stated... nobody has anything on this error with a casual search.

Hi,
The above thread was very useful in understanding the Oracle connection pooling.
But I have certain observations and doubts when I implemented the same in our setup. We configured the setup to use MTS and from the v$session table I am able to see that the sessions that are being made are in SHARED mode. My doubts are
1. When I compare the timed statistics from the AWR report of the runs done in DEDICATED mode and SHARED server mode, I dont see any improvement in the connection management time or logons or in the parsing time. Should'nt I find some improvment in these areas when I do the runs in SHARED server mode, because the connections are already existing and the memory used is in the SGA ?
2. One more observation is that, our application creates a lot of short lived processes, these processes connect to the database, do some functionality and then terminate. When I monitor the v$session table, the session id's are distinct for each process, should'nt the new incoming process use the same session which was used by the earlier session (which has just terminated) ?? Is my understanding wrong about this ??
3. What is the relation between the connections present in the DISPATCHER process and the sessions which I see in the v$session table ?

Please make me understand.
Thanks

Below is the data which I have used for my comparison.
The parameters in the init.ora file are
shared_servers = 20
max_shared_servers = 25
large_pool_size=256M
shared_server_sessions=150
dispatchers = '(ADDRESS =(PROTOCOL=TCP)(LISTENER=LISTENER11)(POOL=on)(CONNECTIONS=300)(SESSIONS=500))'
LOCAL_LISTENER = 'LISTENER11'---- which is listening in the shared server mode.
max_dispatchers = 10

From the AWR reports (time model statistics)

For Dedicated server mode
=========================
Statistic Name Time (s) % of DB Time
DB CPU 432.97 97.21
sql execute elapsed time 359.73 80.77
parse time elapsed 6.66 1.50
failed parse elapsed time 1.70 0.38
hard parse elapsed time 1.64 0.37
connection management call elapsed time 0.62 0.14
sequence load elapsed time 0.07 0.01
PL/SQL execution elapsed time 0.04 0.01
repeated bind elapsed time 0.01 0.00
DB time 445.40
background elapsed time 52.58
background cpu time 6.97

For Shared server mode
=======================
Statistic Name Time (s) % of DB Time
DB CPU 456.51 95.72
sql execute elapsed time 383.06 80.32
parse time elapsed 6.08 1.28
hard parse elapsed time 0.65 0.14
failed parse elapsed time 0.64 0.14
connection management call elapsed time 0.49 0.10
PL/SQL execution elapsed time 0.04 0.01
sequence load elapsed time 0.04 0.01
repeated bind elapsed time 0.01 0.00
DB time 476.91
background elapsed time 57.28
background cpu time 7.22

Thanks

Tom,
We had an issue the other day regarding a "JDBC Thin Client" application using oracle v11.1.0.7.

In this asktom post you say:

1) jdbc connection pooling is typically done by having a common username log into the database -- the software then maintains a pool of open connections all under the same user account. when your program needs a connect, one of the common connections is returned to you. when you are done, it is given back to the pool for the next guy. In
the database you all appear to be the same user in the same session.

Net8 connection pooling allows many sessions to share the same resources. Each session is separate and distince and will typically have a unique userid associated with it. You do not lose your identity.
---

The below issue happened on oracle 11.1.0.7 on linux with linux jdbc app servers using oracle dedicated servers.

We had an issue the other day where locks were done by a jdbc application and part of the application and corresponding oracle session hung for hours. An oracle kill 'sid,serial#' did not take effect immediately (got pending I think the wording was). We proceeded to kill the LOCAL=yes linux process, and of course the session then went away.
Afterword I got wondering how safe our kill was considering "connection pooling" is likely involved. I had remembered reading this asktom post that described "connection pooling". I came back to this post and realized I did not have an answer to my concern.

With connection pooling going on, with dedicated server, maybe one linux killing of a LOCAL=NO process could affect many pieces or threads of the jdbc application.

What do you think?

Or is it the case at the moment of the kill, only one jdbc thread (the offending one) can be active and thus affected?

We also have, on another machine, an application running shared server with jdbc connection pooling going on. Killing a shared oracle (ie, D000) process would likely have much more of an affect on killing multiple parts of the application.

Tom, Thanks again for all you do. Your postings are a great resource to the Oracle community.

This probably doesn't make sense, but just for education purpose--would there be any problems in using both JDBC connection pooling (or application server connection pooling) together with Oracle shared server?