{"id":726,"date":"2009-04-09T16:58:21","date_gmt":"2009-04-09T21:58:21","guid":{"rendered":"http:\/\/jianmingli.com\/wp\/?p=726"},"modified":"2012-04-05T09:21:13","modified_gmt":"2012-04-05T14:21:13","slug":"setting-oracle-parameters-processes-sessions-open_cursors","status":"publish","type":"post","link":"https:\/\/jianmingli.com\/wp\/?p=726","title":{"rendered":"Setting Oracle Parameters: processes, sessions, open_cursors"},"content":{"rendered":"<p><strong>Processes<\/strong><\/p>\n<pre>\r\nSQL> show parameter processes;\r\nNAME                                 TYPE        VALUE\r\n------------------------------------ ----------- --------\r\nprocesses                            integer     1000\r\n\r\nSQL> select count(*) from v$process;\r\n  COUNT(*)\r\n----------\r\n        20\r\n<\/pre>\n<p><strong>Sessions<\/strong><br \/>\nSessions = (Processes * 1.1) + 5<\/p>\n<pre lang=\"sql\">\r\nSQL> show parameter sessions;\r\nNAME                                 TYPE        VALUE\r\n------------------------------------ ----------- --------\r\nsessions                             integer     1105\r\n\r\n-- Count all sessions\r\nSQL> select count(*) from v$session;\r\n  COUNT(*)\r\n----------\r\n       530\r\n\r\n-- Count user sessions\r\nSQL> SELECT COUNT(*) FROM V$SESSION\r\nWHERE USERNAME = 'USER1';\r\n\r\n  COUNT(*)\r\n----------\r\n        27\r\n\r\n-- View session details\r\nSQL> SELECT SID, SERIAL#, STATUS, SERVER FROM V$SESSION WHERE USERNAME='USER1';\r\n\r\n       SID    SERIAL# STATUS   SERVER\r\n---------- ---------- -------- ---------\r\n        11        354 INACTIVE NONE\r\n        22        272 INACTIVE NONE\r\n        96        468 INACTIVE SHARED\r\n        ...\r\n      1064         77 INACTIVE NONE\r\n\r\n26 rows selected.\r\n\r\n-- Kill a session\r\nALTER SYSTEM KILL SESSION 'sid, serial#'\r\n\r\n-- For example\r\nALTER SYSTEM KILL SESSION '23, 4241';\r\n\r\n-- Session summary\r\nSQL> select\r\n rpad(c.name||':',11)||rpad(' current logons='||\r\n (to_number(b.sessions_current)),20)||'cumulative logons='||\r\n rpad(substr(a.value,1,10),10)||'highwater mark='||\r\n b.sessions_highwater Information\r\nfrom\r\n v$sysstat a,\r\n v$license b,\r\n v$database c\r\nwhere\r\n a.name = 'logons cumulative';\r\n\r\nINFORMATION\r\n--------------------------------------------------------------------------------\r\n\r\nXE:         current logons=59  cumulative logons=3068      highwater mark=1084\r\n\r\n-- Session detail\r\nSQL> select\r\n substr(a.spid,1,9) pid,\r\n substr(b.sid,1,5) sid,\r\n substr(b.serial#,1,5) ser#,\r\n substr(b.machine,1,6) box,\r\n substr(b.username,1,10) username,\r\n--       b.server,\r\n substr(b.osuser,1,8) os_user,\r\n substr(b.program,1,30) program\r\nfrom v$session b, v$process a\r\nwhere\r\n b.paddr = a.addr\r\n and type='USER'\r\n order by username;\r\n\r\nPID       SID   SER#  BOX    USERNAME   OS_USER  PROGRAM\r\n--------- ----- ----- ------ ---------- -------- ------------------\r\n5984      803   7     CENTER DBSNMP     NT AUTHO emagent.exe\r\n5152      810   5     CENTER DBSNMP     NT AUTHO emagent.exe\r\n5152      225   4     BOX1 USER1       SYSTEM   JDBC Thin Client\r\n5152      223   18    BOX1 USER1       SYSTEM   JDBC Thin Client\r\n...\r\n5152      586   434   BOX1 USER2    SYSTEM   JDBC Thin Client\r\n5152      166   50    BOX1 USER2    SYSTEM   JDBC Thin Client\r\n...\r\n5152      228   3377  BOX1 USER3\r\n5152      231   213   BOX1 USER3\r\n...\r\n5152      847   14    BOX1 SYSMAN              OMS\r\n5152      845   2     BOX1 SYSMAN              OMS\r\n6928      844   3     BOX1 SYSMAN              OMS\r\n\r\nPID       SID   SER#  BOX    USERNAME   OS_USER  PROGRAM\r\n--------- ----- ----- ------ ---------- -------- ------------------\r\n5152      839   340   BOX1 SYSMAN              OMS\r\n7960      115   2646  DO\\USR SYSTEM     DO\\User- sqlplus.exe\r\n2320      1086  635   BOX1            SYSTEM   ORACLE.EXE (J000)\r\n\r\n58 rows selected.\r\n<\/pre>\n<p><strong>Open Cursors<\/strong><br \/>\nopen_cursors <strong>per session<\/strong><\/p>\n<pre>\r\nSQL> show parameter open_cursors;\r\nNAME                                 TYPE        VALUE\r\n------------------------------------ ----------- -------\r\nopen_cursors                         integer     600\r\n\r\nSQL> select count(*) from v$session;\r\n  COUNT(*)\r\n----------\r\n       530\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>Processes SQL> show parameter processes; NAME TYPE VALUE &#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212; &#8212;&#8212;&#8212;&#8211; &#8212;&#8212;&#8211; processes integer 1000 SQL> select count(*) from v$process; COUNT(*) &#8212;&#8212;&#8212;- 20 Sessions Sessions = (Processes * 1.1) + 5 SQL> show parameter sessions; NAME TYPE VALUE &#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212; &#8212;&#8212;&#8212;&#8211; &#8212;&#8212;&#8211; &hellip; <a href=\"https:\/\/jianmingli.com\/wp\/?p=726\">Continue reading <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_exactmetrics_skip_tracking":false,"_exactmetrics_sitenote_active":false,"_exactmetrics_sitenote_note":"","_exactmetrics_sitenote_category":0,"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[35],"tags":[],"class_list":["post-726","post","type-post","status-publish","format-standard","hentry","category-oracle"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p8cRUO-bI","_links":{"self":[{"href":"https:\/\/jianmingli.com\/wp\/index.php?rest_route=\/wp\/v2\/posts\/726","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/jianmingli.com\/wp\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/jianmingli.com\/wp\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/jianmingli.com\/wp\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/jianmingli.com\/wp\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=726"}],"version-history":[{"count":10,"href":"https:\/\/jianmingli.com\/wp\/index.php?rest_route=\/wp\/v2\/posts\/726\/revisions"}],"predecessor-version":[{"id":4583,"href":"https:\/\/jianmingli.com\/wp\/index.php?rest_route=\/wp\/v2\/posts\/726\/revisions\/4583"}],"wp:attachment":[{"href":"https:\/\/jianmingli.com\/wp\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=726"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jianmingli.com\/wp\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=726"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jianmingli.com\/wp\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=726"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}