************************************************************************************** * Description: Knowledge of Oracle database connection, session, and process concepts * Date: 02:29 PM EST, 03/26/2018 ************************************************************************************** <1> Understanding Oracle connection, session, and process concepts will help optimzing database performance utility system resource efficiently: | |__ o. Session - is a context, which gets restored as a piece of data within Oracle PGA memory from connection starts until termination. | The data includes which user from which Oracle client server name/IP, through which application such as SQL Delveloper, | what time connected to the database. These info can be queried from v$session. | |__ o. Connection - is focusing on describing client/server network communication rule upon "Physical Layer" protocal, | whereas session is considered as ruling out communication method on "Application Layer" protocal. | |__ o. But, generally, session and connection are created at the same time. <2> Oracke PMON and Session/Connection: | |__ o. The Oracle background process PMON will check each session connecting status periodically. If the physical connection terminated, PMON will clean up the session data from PGA and release occupied resource. <3> Oracle paramter PROCESS: | |__ o. Oracle uses parameter PROCESS to limit the process count that connects to database SGA. | |__ o. when you query v$session, you may see some sessions' username are "SYS-BKP". | This is because, Oracle background process like DBWR, LGWR, ARCHIVER alse needs to connect database to perform sepecific work. | So, a session will also need to be created as for a noraml db user. | |__ o. For dedicate connection mode, each session will start an individual OS process talking with SGA. | Therefore, for dedicate connection database, one session is dedicated to one process. But, one process can serve 1 or 0 session. | "process_parameter_value" > "normal_db_user_connection_count" + "background_process_count". | |__ o. For shared connection mode, one process can map to 1 or multiple sessions. <4> Increase process and session parameter: | |__ 1) Session value is depending on process value. | |__ 2) So, usually, you should only tune process, and session value will be increased/decreased accordingly. | |__ 3) If process is too low, so allowed session count is also low. So, ora-err "maxium session exceed" may come up. | |__ 4) If process is too high, so allowed session count is aslo high. So, ora-err "can not allocated memory" for PGA may come up. | If server RAM is not sufficient, and PGA size is set low for above case. | |__ 5) Usually, sessions=(1.1*process+5), but the numbe may not be accurate. <5> Reference: | |__ o. https://blog.csdn.net/bapinggaitianli/article/details/46705881
Your Comments