After reading the Oracle docs, it´s still not clear for me if an Oracle process is created for every SQL statement or just for every connection (or not really related to both).
Any comment here would be really great.
Thanks.
3 Answers
A process is created per connection, which can then execute SQL statements for the duration of the connection.
A SQL statement may use more than one Oracle background process (for things like Parallel Query) during its execution, but these aren't forked specifically for the SQL statement.
Note that I'm ignoring things like connection pooling (DRCP) and shared servers (MTS) to keep the answer simple.
-
Thanks Phil. The explanation is concise and crystal clear.Juan– Juan2012-03-08 10:44:59 +00:00Commented Mar 8, 2012 at 10:44
The Overview of Server Processes from the 11.2 Oracle Concepts Guide includes the following information:
In dedicated server connections, the client connection is associated with one and only one server process. On Linux, 20 client processes connected to a database instance are serviced by 20 server processes.
Each client process communicates directly with its server process. This server process is dedicated to its client process for the duration of the session.
-
Linking to the Oracle docs, so little space for doubt :) Thanks!Juan– Juan2012-03-10 08:46:59 +00:00Commented Mar 10, 2012 at 8:46
Whenever a SQL statement is fired from oracle, it just creates a psedocode of that SQL Statement and stores it in the Library cache (Shared SQL Area).
This is stored basically for fast retrieval and reuse the SQL statements present in Cache. So, there would not be any process created when a SQL statement is fired. But as Phil said,
A SQL statement may use more than one Oracle background process (for things like Parallel Query) .
SQL statement uses the background process but doesn't create it.
This link might be useful for you. Kindly Check this.
