{"id":180,"date":"2021-05-26T23:01:47","date_gmt":"2021-05-26T15:01:47","guid":{"rendered":"https:\/\/www.db2go.net\/?p=180"},"modified":"2021-05-26T23:01:47","modified_gmt":"2021-05-26T15:01:47","slug":"session-leak","status":"publish","type":"post","link":"https:\/\/www.db2go.net\/?p=180","title":{"rendered":"session leak"},"content":{"rendered":"<p>\u751f\u4ea7\u4e0a\u9047\u5230\u7684\u4e00\u4e2a\u95ee\u9898\uff0csession\u6570\u66b4\u589e\uff0cinactive\u8fbe\u52304-5000\uff0c\u4f46Cursors\/Session\u4ec5\u4e3a\u51e0\uff0c\u5982\u56fe\u6240\u793a\uff1a<br \/>\n<a class=\"wp-editor-md-post-content-link\" href=\"https:\/\/www.db2go.net\/wp-content\/uploads\/2021\/05\/wp_editor_md_7aaf029ee8dd28736f1f597a4191dba8.jpg\"><img decoding=\"async\" src=\"https:\/\/www.db2go.net\/wp-content\/uploads\/2021\/05\/wp_editor_md_7aaf029ee8dd28736f1f597a4191dba8.jpg\" alt=\"\" \/><\/a><\/p>\n<ol>\n<li>\n<p>\u9760awr\u6216\u8005ash\u90fd\u67e5\u4e0d\u51fa\u6765\u3002dba_hist_active_sess_history\u4e5f\u4e0d\u884c\uff0c\u9664\u975einactive\u7684session\u88ab\u8bb0\u5f55\uff0c\u4f46\u597d\u50cf\u8fd8\u6ca1\u7684\u8fd9\u4e2a\u8868\u6216\u89c6\u56fe\u3002<\/p>\n<\/li>\n<li>\n<p>\u53ea\u80fd\u5728\u51fa\u95ee\u9898\u7684\u65f6\u5019\u5bf9v$session\u8fdb\u884c\u76d1\u63a7\uff0c\u67e5\u770binactive\u7684session\u6240\u5bf9\u5e94\u7684machine\u3001program\u6216\u8005username\u3002<\/p>\n<\/li>\n<li>\n<p>\u811a\u672c\u5982\u4e0b<\/p>\n<\/li>\n<\/ol>\n<pre><code class=\"language-sql line-numbers\">sqlplus -s <span class=\"katex math inline\">DB_CONN_STR@<\/span>SH_DB_SID &lt; set feed off\nset verify off\nset line 132\nset pages 200\n\n\ncol username format a15\ncol sql_id format a20\ncol sql_address format a20\ncol machine format a30\ncol osuser format a15\ncol logon_time format a10\ncol program format a35\nbreak on report\ncompute sum of  cnt  on report\nselect status,count(*) cnt from v\\<span class=\"katex math inline\">session group by status;\nprompt .\n\n\nselect program,cnt,status from (select program,count(*) cnt,status from v\\$session group by program,status order by cnt desc) where rownum&lt;10;\n\n\nprompt .\n  select username,\n      sum(cnt) total_cnt,\n      sum(decode(status,'ACTIVE', cnt,0)) ACTIVE,\n      sum(decode(status,'INACTIVE', cnt,0)) INACTIVE,\n      sum(decode(status,'KILLED', cnt,0)) KILLED,\n      sum(decode(status,'SNIPED', cnt,0)) SNIPED,<\/span>issue_program1 \"JDBC Thin Client\",\n     <span class=\"katex math inline\">issue_program2  \"program1\",<\/span>issue_program3  \"program2\"\n     from (select program,username,status,count(*) cnt from V\\$SESSION   group by program,username,status  ) \n    group by username having sum(cnt)&gt;50 order by total_cnt desc;\n\n\nEOF\nexit\n<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"<p>\u751f\u4ea7\u4e0a\u9047\u5230\u7684\u4e00\u4e2a\u95ee\u9898\uff0csession\u6570\u66b4\u589e\uff0cinactive\u8fbe\u52304-5000\uff0c\u4f46Cursors\/Session&hellip;<\/p>\n <a href=\"https:\/\/www.db2go.net\/?p=180\" title=\"session leak\" class=\"entry-more-link\"><span>Read More<\/span> <span class=\"screen-reader-text\">session leak<\/span><\/a>","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"Layout":"","footnotes":""},"categories":[4],"tags":[],"class_list":["entry","author-suredandan","post-180","post","type-post","status-publish","format-standard","category-oracle"],"views":1187,"_links":{"self":[{"href":"https:\/\/www.db2go.net\/index.php?rest_route=\/wp\/v2\/posts\/180","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.db2go.net\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.db2go.net\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.db2go.net\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.db2go.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=180"}],"version-history":[{"count":1,"href":"https:\/\/www.db2go.net\/index.php?rest_route=\/wp\/v2\/posts\/180\/revisions"}],"predecessor-version":[{"id":181,"href":"https:\/\/www.db2go.net\/index.php?rest_route=\/wp\/v2\/posts\/180\/revisions\/181"}],"wp:attachment":[{"href":"https:\/\/www.db2go.net\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=180"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.db2go.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=180"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.db2go.net\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=180"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}