Setting Oracle Parameters: processes, sessions, open_cursors

Processes

SQL> show parameter processes;
NAME                                 TYPE        VALUE
------------------------------------ ----------- --------
processes                            integer     1000

SQL> select count(*) from v$process;
  COUNT(*)
----------
        20

Sessions
Sessions = (Processes * 1.1) + 5

  1.  
  2. SQL> SHOW parameter sessions;
  3. NAME TYPE VALUE
  4. ------------------------------------ ----------- --------
  5. sessions integer 1105
  6.  
  7. -- Count all sessions
  8. SQL> SELECT count(*) FROM v$session;
  9. COUNT(*)
  10. ----------
  11. 530
  12.  
  13. -- Count user sessions
  14. SQL> SELECT COUNT(*) FROM V$SESSION
  15. WHERE USERNAME = 'USER1';
  16.  
  17. COUNT(*)
  18. ----------
  19. 27
  20.  
  21. -- View session details
  22. SQL> SELECT SID, SERIAL#, STATUS, SERVER FROM V$SESSION WHERE USERNAME='USER1';
  23.  
  24. SID SERIAL# STATUS SERVER
  25. ---------- ---------- -------- ---------
  26. 11 354 INACTIVE NONE
  27. 22 272 INACTIVE NONE
  28. 96 468 INACTIVE SHARED
  29. ...
  30. 1064 77 INACTIVE NONE
  31.  
  32. 26 rows selected.
  33.  
  34. -- Kill a session
  35. ALTER SYSTEM KILL SESSION 'sid, serial#'
  36.  
  37. -- For example
  38. ALTER SYSTEM KILL SESSION '23, 4241';
  39.  
  40. -- Session summary
  41. SQL> SELECT
  42. rpad(c.name||':',11)||rpad(' current logons='||
  43. (to_number(b.sessions_current)),20)||'cumulative logons='||
  44. rpad(substr(a.value,1,10),10)||'highwater mark='||
  45. b.sessions_highwater Information
  46. FROM
  47. v$sysstat a,
  48. v$license b,
  49. v$database c
  50. WHERE
  51. a.name = 'logons cumulative';
  52.  
  53. INFORMATION
  54. --------------------------------------------------------------------------------
  55.  
  56. XE: current logons=59 cumulative logons=3068 highwater mark=1084
  57.  
  58. -- Session detail
  59. SQL> SELECT
  60. substr(a.spid,1,9) pid,
  61. substr(b.sid,1,5) sid,
  62. substr(b.serial#,1,5) ser#,
  63. substr(b.machine,1,6) box,
  64. substr(b.username,1,10) username,
  65. -- b.server,
  66. substr(b.osuser,1,8) os_user,
  67. substr(b.program,1,30) program
  68. FROM v$session b, v$process a
  69. WHERE
  70. b.paddr = a.addr
  71. AND type='USER'
  72. ORDER BY username;
  73.  
  74. PID SID SER# BOX USERNAME OS_USER PROGRAM
  75. --------- ----- ----- ------ ---------- -------- ------------------
  76. 5984 803 7 CENTER DBSNMP NT AUTHO emagent.exe
  77. 5152 810 5 CENTER DBSNMP NT AUTHO emagent.exe
  78. 5152 225 4 BOX1 USER1 SYSTEM JDBC Thin Client
  79. 5152 223 18 BOX1 USER1 SYSTEM JDBC Thin Client
  80. ...
  81. 5152 586 434 BOX1 USER2 SYSTEM JDBC Thin Client
  82. 5152 166 50 BOX1 USER2 SYSTEM JDBC Thin Client
  83. ...
  84. 5152 228 3377 BOX1 USER3
  85. 5152 231 213 BOX1 USER3
  86. ...
  87. 5152 847 14 BOX1 SYSMAN OMS
  88. 5152 845 2 BOX1 SYSMAN OMS
  89. 6928 844 3 BOX1 SYSMAN OMS
  90.  
  91. PID SID SER# BOX USERNAME OS_USER PROGRAM
  92. --------- ----- ----- ------ ---------- -------- ------------------
  93. 5152 839 340 BOX1 SYSMAN OMS
  94. 7960 115 2646 DO\USR SYSTEM DO\User- sqlplus.exe
  95. 2320 1086 635 BOX1 SYSTEM ORACLE.EXE (J000)
  96.  
  97. 58 rows selected.
  98.  

Open Cursors
open_cursors per session

SQL> show parameter open_cursors;
NAME                                 TYPE        VALUE
------------------------------------ ----------- -------
open_cursors                         integer     600

SQL> select count(*) from v$session;
  COUNT(*)
----------
       530
This entry was posted in oracle. Bookmark the permalink.