As with all things, it depends.
If you're using OCI, I assume you're developing a client/ server application. I would generally want the database in that situation to support shared server connections and I would want to request a shared server connection when the application connected to the database. That improves the performance of the application by limiting the amount of work the database has to do in order to create a new session. That has the side effect of leaving more resources available on the server for others. Using shared server connections does involve a longer code path when the database executing subsequent queries since the query has to be sent to a shared server process but that generally isn't a big deal if the number of shared server processes is reasonable.
Opening a number of sessions could also be a problem if the DBA is not using automatic PGA management. If you're using manual PGA management, PGA is configured on a per-session basis so each session can allocate a separate SORT_AREA_SIZE for sorts among other PGA components. If you created a large number of sessions in a database using manual PGA management and each session tried to maximize its PGA usage, you could easily starve the server of RAM and cause performance problems for everyone. Assuming you are using Oracle 10.1 or later, however, automatic PGA management is available. In that case, the DBA configures a PGA_AGGREGATE_TARGET (or includes the PGA in the MEMORY_TARGET in 11g) and the database takes care of ensuring that the aggregate PGA across all sessions is limited so that the database runs out of resources.
If the database supports shared server connections, your application gets a shared server connection, and the database uses automatic PGA management, most DBAs won't care too much how many sessions you create.
Now, if you are creating many sessions so that you can do more work in parallel, that would create performance problems over and above the number of sessions. It's easy enough to configure the database to support 1000 sessions, for example, it's much harder to configure the database to not die in a pile if all 1000 session simultaneously issue a meaty query against a data warehouse. If your application is using up all the resources available to the database for your queries, the DBA would probably want to consider using Oracle Resource Manager to prioritize different applications and/or different users. For example, the DBA could configure Resource Manager so that if the CPU utilization goes to 100%, your application in the aggregate gets 50% of the CPU, some other application in the aggregate gets 25%, and all others get the remaining 25%. If no other requests were pending, your application would be free to use all 100% of the CPU.
If you are running things in parallel, it might also be useful to investigate Oracle's ability to run statements in parallel because that might involve less overhead than writing your own parallelization code. I would expect, for example, that it would be far easier and quite possibly faster to write a client application that serially submitted statements that used Oracle parallel query to execute rather than opening multiple sessions and executing each statement from a separate thread of your application while Oracle was not using parallel query to execute any of the statements. If you use parallel query, the database can also adjust the number of parallel slaves that are spawned so that when the database is particularly busy fewer parallel slaves are started and when the database is relatively idle more parallel slaves are started. That would potentially satisfy both the desire to make the application as efficient as possible as well as to share the resources of the server more equitably with other applications.
SQL is usually pretty good about closing linked server connections. BUT, I've seen SQL server 'hang' linked server queries of certain types if all of the result set isn't fully fetched and/or closed properly by the client. IOW, SQL doesn't think that the app has finished retrieving the data, so it doesn't close the connection to the other server. For whatever reason, the offending app didn't report any errors or query time outs (from it's perspective), but we did have to recycle the IIS application every night or it would eventually eat up all of the RAM on the IIS server.
I would start by running my usual "find any connection that is waiting" query:
SELECT
@@servername 'ServerInstance',es.session_id,er.blocking_session_id
,es.status
,es.login_name,DB_NAME(er.database_id) as database_name
,es.host_name
,es.program_name,er.command
,es.reads,es.writes
,es.cpu_time
,er.wait_type,er.wait_time,er.last_wait_type
,er.wait_resource
,CASE es.transaction_isolation_level WHEN 0 THEN 'Unspecified'
WHEN 1 THEN 'ReadUncommitted'
WHEN 2 THEN 'ReadCommitted'
WHEN 3 THEN 'Repeatable'
WHEN 4 THEN 'Serializable'
WHEN 5 THEN 'Snapshot'
END AS transaction_isolation_level
,last_request_start_time
,last_request_end_time
,OBJECT_NAME(st.objectid, er.database_id) as object_name
,SUBSTRING(st.text, er.statement_start_offset / 2,
(CASE WHEN er.statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max), st.text)) * 2
ELSE er.statement_end_offset END - er.statement_start_offset) / 2) AS query_text
,ph.query_plan
FROM sys.dm_exec_connections ec
LEFT OUTER JOIN sys.dm_exec_sessions es ON ec.session_id = es.session_id
LEFT OUTER JOIN sys.dm_exec_requests er ON ec.connection_id = er.connection_id
OUTER APPLY sys.dm_exec_sql_text(sql_handle) st
OUTER APPLY sys.dm_exec_query_plan(plan_handle) ph
WHERE ec.session_id <> @@SPID
-- AND es.status = 'running'
AND (
es.status != 'Sleeping'
OR wait_time > 0
OR es.session_id in (select blocking_session_id from sys.dm_exec_requests where blocking_session_id != 0)
)
ORDER BY es.session_id
I'd look for any sort of wait type like "OLEDB" (which should indicate linked server activity, and possibly other things) and try to tie those back to code that accesses the oracle server. I'd also pay attention to the last request start and end times.
Best Answer
Please try the below query to see if it's helps: