Differences among processes, sessions, and connections

-- ======================================
-- Differences among processes, sessions, and connections
-- ======================================

When using Oracle database, connection and session are one of the words we often encounter. At first glance it looks like one thing, but the fact is not. Zero,
One or more sessions. Ah, why? Yes, that's right. This is also the reason we often misunderstand.
Each session is independent of other sessions, even multiple sessions in the same connection.

I. definitions between several terms(Refer to Oracle 9i & 10g programming art)

Connection: A physical path from the customer to the Oracle instance. The connection can be established on the network or through the IPC Mechanism. Normally
A connection is established between a customer process and a dedicated server or scheduler.

Session: a session is a logical entity in an instance. This is your session state, that is, a group of memory for a specific session.
When talking about "database connection", most people first think of "session ". You need to execute SQL, submit transactions, and run stored procedures on sessions on the server.

2. view the relationship between them through an example
1. No connection, no session, no process

--> No session server process is established when no connection is established <br/> [Oracle @ odbp ~] $ PS-Ef | grep oracleorcl <br/> Oracle 5685 5446 0 00:00:00 pts/1 grep oracleorcl </P> <p> [Oracle @ odbp ~] $ Sqlplus/nolog </P> <p> SQL * Plus: Release 10.2.0.4.0-production on Mon Jun 27 19:30:49 2011 <br/> copyright (c) 1982,200 7, Oracle. all rights reserved. </P> <p> idle> Ho PS-Ef | grep oracleorcl --> no session server processes can be seen during nolog logon. <br/> Oracle 5691 5686 0 pts/ 0 00:00:00/bin/bash-c ps-Ef | grep oracleorcl <br/>

2. Single connection, single session, single process

--> Log On As Scott. A corresponding server process is generated. <br/> idle> conn Scott/tiger <br/> connected. <br/> Scott @ orcl> select Sid, serial #, username from V $ session where username is not null; </P> <p> Sid serial # username <br/> ---------- ------------------------- <br/> 159 5 Scott </P> <p> Scott @ SQL> Ho PS-ef | grep oracleorcl <br/> Oracle 5696 5686 0? 00:00:00 oracleorcl (description = (local = yes) (address = (Protocol = beq ))) <br/> Oracle 5699 5686 0 00:00:00 pts/0/bin/bash-c ps-Ef | grep oracleorcl <br/>

3. No connection, no session, single process

--> Disconnect is used to disconnect the session, but the corresponding server process is not revoked, when exit is used, the corresponding server process is released <br/> Scott @ SQL> disconnect <br/> disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0-production <br/> the partitioning, OLAP, data mining and real application testing options </P> <p> --> enable another session session2 to check whether Scott's session exists, the following query does not show the Scott user's session <br/> sys @ orcl> select Sid, serial #, username from V $ session where username = 'Scott '; </P> <p> No rows selected </P> <p> Scott @ SQL> Ho PS-Ef | grep 5696 --> the corresponding background process still exists. <br/> oracle 5696 5686 0? 00:00:00 oracleorcl (description = (local = yes) (address = (Protocol = beq ))) <br/> Oracle 5702 5686 0 00:00:00 pts/0/bin/bash-c ps-Ef | grep 5696 </P> <p> Scott @ orcl> exit <br/> disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0-production <br/> with the partitioning, OLAP, data Mining and real application testing options <br/> [Oracle @ odbp admin] $ PS-Ef | grep 5696 --> after exit, the corresponding process 5696 is released <br/> Oracle 4082 16943 0 00:00:00 pts/0 grep 5696 <br/>

4. A single connection, multiple sessions, and a single process

--> View the corresponding session and background process <br/> --> use Scott to log on to session1 <br/> idle> conn Scott/tiger; <br/> connected. </P> <p> --> Log On with the sys account in session2 <br/> sys @ orcl> select Sid, serial #, username from V $ session where username is not null; </P> <p> Sid serial # username <br/> ---------- ------------------------------ <br/> 141 4 sys <br/> 159 5 Scott </P> <p> --> enable autotrace in session1 <br/> Scott @ orcl> set autotrace On </P> <p> --> you can see that when querying the V $ session view of session2, an additional account is Scott, however, Sid and serial # are different from previous records <br/> sys @ orcl> set linesize 160 <br/> sys @ orcl> select spid, S. sid, S. serial #, S. status, S. username, P. program <br/> 2 from V $ PROCESS p, V $ session S <br/> 3 where p. ADDR = S. paddr <br/> 4 and S. username = 'Scott '; </P> <p> spid Sid serial # status username Program <br/> ---------------------------------------------------------- ------- ------------------------------------ <Br/> 4602 159 5 inactive Scott oracle@oradb.robinson.com (TNS V1-V3) <br/> 4602 139 25 inactive Scott oracle@oradb.robinson.com (TNS V1-V3) </P> <p> sys @ orcl> Ho PS-Ef | grep 4602 <br/> Oracle 4602 4499 0? 00:00:00 oracleorcl (description = (local = yes) (address = (Protocol = beq ))) <br/> Oracle 4856 4655 0 00:00:00 pts/3/bin/bash-c ps-Ef | grep 4602 </P> <p> sys @ orcl> Ho PS-ef | grep oracleorcl <br/> Oracle 4602 4499 0? 00:00:00 oracleorcl (description = (local = yes) (address = (Protocol = beq) <br/> Oracle 4656 4655 0? 00:00:00 oracleorcl (description = (local = yes) (address = (Protocol = beq ))) <br/> Oracle 4859 4655 0 00:00:00 pts/3/bin/bash-c ps-Ef | grep oracleorcl </P> <p> --> the preceding query results show that, the Scott user has only one background process with the spid of 4062. <br/>

Set autotrace completed action

When set autotrace is enabled, a new session is usually created to monitor the current operation and return statistical information. The procedure is described below <br/>. execute a query in session1, then the original SESSION (, 5) executes the DML or dql operation <br/> B. the newly created SESSION () starts to query the V $ sesstat view to remember the initial statistical value of the actual session (that is, the session that runs DML). <br/> C. DML or dql operations in the original SESSION () <br/> D. the new session () will query the V $ sesstat view again, calculate the statistical information based on the difference with the previous one, and generate the execution plan and statistical information for the execution. <br/>

For how to enable set autotrace, see enable autotrace.

--> The following shows the query in session1 <br/> Scott @ orcl> select count (1) from EMP; </P> <p> count (1) <br/> ---------- <br/> 14 </P> <p> execution plan <br/> ---------------------------------------------------- <br/> plan hash value: 2937609675 </P> <p> ----------------------------------------------------------------- <br/> | ID | operation | Name | rows | cost (% CPU) | time | <br/> ----------------------------------------------------------------- <br/> | 0 | SELECT statement | 1 | 1 (0) | 00:00:01 | <br/> | 1 | sort aggregate | 1 | <br/> | 2 | index full scan | pk_emp | 14 | 1 (0) | 00:00:01 | <br/> Statistics </P> <p> Statistics <br/> 296 recursive cballs <br/> 0 dB block gets <br/> 54 consistent gets <br/> 1 physical reads <br/> 0 redo size <br/> 411 bytes sent via SQL * Net to client <br/> 385 bytes encoded ed via SQL * net from client <br/> 2 SQL * Net roundtrips to/from client <br/> 6 sorts (memory) <br/> 0 sorts (Disk) <br/> 1 rows processed </P> <p> Scott @ orcl> set autotrace off; <br/> --> run the query again in session2, you can see that the session and 25 have been released <br/> sys @ orcl>/</P> <p> spid Sid serial # status username Program <br/> -------------- ---------- -------- --------------------- quit ------------------------------------------------------------------------------------------------------- <br/> 4602 159 5 inactive Scott oracle@oradb.robinson.com (TNS V1-V3) <br/>

5. The SID remains unchanged. The serial # changes.

--> Exit all sessions. The following describes the changes in serial # With sid unchanged. <br/> [Oracle @ oradb ~] $ PS-Ef | grep oracleorcl --> at this time, Oracle database does not have any server processes <br/> Oracle 26767 16943 0 00:00:00 pts/0 grep oracleorcl <br/> [Oracle @ oradb ~] $ Sqlplus Scott/tiger @ orcl </P> <p> connected to: <br/> Oracle Database 10g Enterprise Edition Release 10.2.0.4.0-production <br/> with the partitioning, OLAP and Data Mining options </P> <p> Scott @ orcl> select Sid, serial #, username from V $ session where username = 'Scott '; </P> <p> Sid serial # username <br/> ---------- ------------------------------ <br/> 134 39 Scott </P> <p> Scott @ orcl> exit <br/> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0-production <br/> with the partitioning, OLAP and Data Mining options <br/> [uniread] Saved history (652 lines) <br/> [Oracle @ oradb ~] $ Sqlplus Scott/tiger @ orcl </P> <p> connected to: <br/> Oracle Database 10g Enterprise Edition Release 10.2.0.4.0-production <br/> with the partitioning, OLAP and Data Mining options </P> <p> Scott @ orcl> select Sid, serial #, username from V $ session where username = 'Scott '; </P> <p> Sid serial # username <br/> ---------- ------------------------------ <br/> 134 41 Scott <br/> --> from the above situation, we can see that although the Scott user exits and then log on again, the same SID is still used. Therefore, when executing the kill session, you must pay attention to the SID, serial # <br/> --> value of the two, avoid kill sessions that should not be killed <br/>

For more information about kill sessions, see Oracle kill sessions

Iii. Relationship between session and process settings
Session: specifies the number of sessions allowed in an instance, that is, the number of concurrent users that can log on to the database at the same time.
Process: specifies the number of processes that an instance can run simultaneously at the operating system level, including background processes and server processes.
According to the above analysis, a background process may correspond to several sessions at the same time. Therefore, the sessions value is usually greater than the processes value.
General Formula
Sessions = 1.1 * processes + 5

--> For example, in the following system settings, the processes value is 150 and the session value is 170. </P> <p> Scott @ orcl> select name, value from V $ parameter where name = 'processs '; </P> <p> name value <br/> -------------------- ------------------ <br/> processes 150 </P> <p> Scott @ orcl> select name, value from V $ parameter where name = 'session '; </P> <p> name value <br/> -------------------- ------------------ <br/> sessions 170 </P> <p> Scott @ orcl> select 150*1.1 + 5 from dual; </P> <p> 150*1.1 + 5 <br/> ---------- <br/> 170 <br/>

Iv. More references
Differences between processes, sessions and connections

V. Quick Reference

For performance optimization, see

Oracle hard parsing and soft Parsing

Sharedpool Tuning)

Buffercache adjustment and optimization (1)

Use of Oracle table cache (cachingtable)

 

For the Oracle architecture, see

Oracle tablespace and data files

Oracle Password File

Oracle parameter file

Oracle online redo log file)

Oracle Control File)

Oracle archiving logs

Oracle rollback and undo)

Oracle database instance startup and Shutdown Process

Automated Management of Oracle10g SGA

Oracle instances and Oracle databases (Oracle Architecture)

 

For more information about the flash back feature, see

Flashback Database)

Flashback drop & recyclebin)

Oracle flash back features (flashback query, flashbacktable)

Oracle flash back feature (flashback version, flashback transaction)

 

For more information about user-managed backup and recovery, see

Oracle cold backup

Oracle Hot Backup

Concept of Oracle backup recovery

Oracle instance recovery

Oracle recovery based on user management (describes media recovery and processing in detail)

System tablespace management and Backup Recovery

Sysaux tablespace management and recovery

 

For information on RMAN backup recovery and management, see

RMAN overview and architecture

RMAN configuration, Monitoring and Management

Detailed description of RMAN backup

RMAN restoration and recovery

Create and use rmancatalog

Create RMAN storage script based on catalog

Catalog-based RMAN backup and recovery

Use RMAN to migrate a file system database to ASM

RMAN backup path confusion (when using plus archivelog)

 

For Oracle faults, see

Error Handling for ORA-32004

ORA-01658 error.

CRS-0215 error handling

ORA-00119, ORA-00132 error handling

Another spfile setting error causes the database to fail to start.

Misunderstanding and setting of the parameter fast_start_mttr_target = 0

Spfile error causing database startup failure (ORA-01565)

 

For more information about ASM, see

Create an ASM instance and an ASM Database

Management of ASM disks and directories

Use asmcmd to manage the ASM directory and files

 

For more information about SQL and PLSQL, see

Common sqlplus commands

Replace variables with SQL * Plus Environment Settings

SQL plus paging using uniread

SQL Basics--> SELECT query

SQL Basics--> Use of new_value

SQL Basics--> Set operation (Union and Union all)

SQL Basics--> Common functions

SQL Basics--> View (createview)

SQL Basics--> Create and manage tables

SQL Basics--> Multi-Table query

SQL Basics--> Filtering and sorting

SQL Basics--> Subquery

SQL Basics--> Grouping and grouping Functions

SQL Basics--> Hierarchical query (startby... connect by prior)

SQL Basics--> Rollup and cube operators implement data aggregation

PL/SQL--> Cursor

PL/SQL--> Exception Handling)

PL/SQL--> Language basics

PL/SQL--> Process Control

PL/SQL--> PL/SQL records

PL/SQL--> Create and manage packages

PL/SQL--> Implicit cursor (SQL % found)

PL/SQL--> Package overloading and initialization

PL/SQL--> Use of dbms_ddl package

PL/SQL--> DML triggers

PL/SQL--> Instead of trigger

PL/SQL--> Stored Procedure

PL/SQL--> Function

PL/SQL--> Dynamic SQL

PL/SQL--> Common Errors of dynamic SQL

 

Other Oracle features

Common Oracle directory structure (10 Gb)

Use OEM, SQL * Plus, and iSQL * Plus to manage Oracle instances

Logging mode (logging, force logging, nologging)

Logging and nologging on table and index segments

Oralceomf Functions

Oracle users, object permissions, and system Permissions

Oracle role and configuration file

Oracle Partition Table

Oracle External table

Use external tables to manage Oracle alarm logs (alaert _ $ Sid. Log)

Cluster table and cluster Table Management (index clustered tables)

Use of Data Pump expdp export tool

Use of Data Pump impdp import tool

Import and Export Oracle Partition Table Data

SQL * loader usage

Enable User Process Tracking

Configure dynamic service registration for non-default ports

Configure the Oracle client to connect to the database

Difference between systemsys and sysoper sysdba

Oracle_sid, db_name, instance_name, db_domian, global_name

Complete Oracle patches (Oracle 9i 10g 11g path)

Upgrade oracle10.2.0.1 to 10.2.0.4

Oracle kill session