{"id":450,"date":"2021-09-09T19:26:05","date_gmt":"2021-09-09T11:26:05","guid":{"rendered":"http:\/\/dba.qishuo.xin\/?p=450"},"modified":"2022-04-23T22:12:29","modified_gmt":"2022-04-23T14:12:29","slug":"oracle%e7%ad%89%e5%be%85%e4%ba%8b%e4%bb%b6cursor-pin-s-wait-on-x","status":"publish","type":"post","link":"http:\/\/dba.qishuo.xin\/?p=450","title":{"rendered":"Oracle\u7b49\u5f85\u4e8b\u4ef6cursor: pin S wait on X"},"content":{"rendered":"<h3>1. \u4ec0\u4e48\u662fcursor\uff1apin S wait on X\u7b49\u5f85\u4e8b\u4ef6<\/h3>\n<p>\u5f53\u4e00\u4e2a\u4f1a\u8bdd\u5c1d\u8bd5\u5f97\u5230\u4e00\u4e2amutex pin\u7684\u65f6\u5019\uff0c\u4f46\u662f\u5176\u4ed6\u4f1a\u8bdd\u6b63\u5728\u4ee5exclusive\u6a21\u5f0f\u6301\u6709\u76f8\u540ccursor object\u7684mutex\uff0c\u6b64\u65f6\u7533\u8bf7mutex pin\u7684\u4f1a\u8bdd\u7b49\u5f85\u4e8b\u4ef6\u5373\u4e3acursor\uff1apin S wait on X \u3002<\/p>\n<ul>\n<li>\u68c0\u67e5\u5f53\u524d\u4f1a\u8bdd\u4e2d\u662f\u5426\u5b58\u5728\u7b49\u5f85\u4e8b\u4ef6<code>cursor: pin S wait on X<\/code><\/li>\n<\/ul>\n<pre><code class=\"language-sql\">SELECT event\n  FROM gv$session\n WHERE event LIKE &#039;%cursor%&#039;;<\/code><\/pre>\n<ul>\n<li>\u901a\u8fc7\u5386\u53f2\u91c7\u6837\u6570\u636e\u67e5\u8be2\u7b49\u5f85\u4e8b\u4ef6<code>cursor: pin S wait on X<\/code><\/li>\n<\/ul>\n<pre><code class=\"language-sql\">  SELECT du.username,\n         dhash.snap_id,\n         dhash.instance_number,\n         dhash.event,\n         dhash.sql_id,\n         dhash.blocking_inst_id,\n         dhash.blocking_session,\n         dhash.blocking_session_serial#,\n         count(*)\n    FROM dba_hist_active_sess_history dhash\n    LEFT JOIN dba_users du ON dhash.user_id = du.user_id\n   WHERE dhash.sample_time &gt;= to_timestamp(&#039;2021-09-09 16:00:00&#039;,&#039;yyyy-mm-dd hh24:mi:ss&#039;)\n     AND dhash.sample_time &lt;= to_timestamp(&#039;2021-09-09 17:00:00&#039;,&#039;yyyy-mm-dd hh24:mi:ss&#039;)\n     AND dhash.event=&#039;cursor: pin S wait on X&#039;\n   GROUP BY du.username,dhash.snap_id,dhash.instance_number,dhash.event,dhash.sql_id,\n            dhash.blocking_session,dhash.blocking_session_serial#, dhash.blocking_inst_id;<\/code><\/pre>\n<p><a href=\"http:\/\/dba.qishuo.xin\/wp-content\/uploads\/2021\/09\/wp_editor_md_1e736b722c337a3dc625a76290476cfc.jpg\" data-fancybox=\"images\" data-fancybox=\"images\"><img decoding=\"async\" src=\"http:\/\/dba.qishuo.xin\/wp-content\/uploads\/2021\/09\/wp_editor_md_1e736b722c337a3dc625a76290476cfc.jpg\" alt=\"\" \/><\/a><\/p>\n<p>sid\u4e3a22634, \u7684\u4f1a\u8bdd\u662f\u963b\u585e\u6e90\u3002\u67e5\u8be2\u8be5\u4f1a\u8bdd\u7684username:<\/p>\n<pre><code class=\"language-sql\">  SELECT DISTINCT\n         du.username,\n         dhash.snap_id,\n         dhash.instance_number,\n         dhash.session_id,\n         dhash.session_serial#,\n         dhash.event,\n         dhash.sql_id\n    FROM dba_hist_active_sess_history dhash\n    LEFT JOIN dba_users du ON dhash.user_id = du.user_id\n   WHERE dhash.sample_time &gt;= to_timestamp(&#039;2021-09-09 16:00:00&#039;,&#039;yyyy-mm-dd hh24:mi:ss&#039;)\n     AND dhash.sample_time &lt;= to_timestamp(&#039;2021-09-09 17:00:00&#039;,&#039;yyyy-mm-dd hh24:mi:ss&#039;)\n     AND dhash.session_id = 22634;<\/code><\/pre>\n<p><a href=\"http:\/\/dba.qishuo.xin\/wp-content\/uploads\/2021\/09\/wp_editor_md_d144973d8958fad63ad3620763d29526.jpg\" data-fancybox=\"images\" data-fancybox=\"images\"><img decoding=\"async\" src=\"http:\/\/dba.qishuo.xin\/wp-content\/uploads\/2021\/09\/wp_editor_md_d144973d8958fad63ad3620763d29526.jpg\" alt=\"\" \/><\/a><\/p>\n<ul>\n<li>\u901a\u8fc7\u5386\u53f2\u91c7\u6837\u6570\u636e\u67e5\u8be2\u7b49\u5f85\u4e8b\u4ef6<br \/>\n\u5728\u4e00\u4e2a\u91c7\u6837\u5468\u671f\u5185\u630910\u5206\u949f\u7edf\u8ba1\u53d1\u751f\u7684\u7b49\u5f85\u4e8b\u4ef6\u4fe1\u606f<\/li>\n<\/ul>\n<pre><code class=\"language-sql\">SELECT *\n  FROM (SELECT instance_number,\n               etime,\n               nvl(event,&#039;on cpu&#039;) events,\n               dbtime,\n               round(100*ratio_to_report(dbtime) OVER (partition by etime ),2) pct,\n               row_number() over(partition by etime order by dbtime  desc) rn\n         FROM (SELECT instance_number,\n                      substr(to_char(sample_time,&#039;yyyymmdd hh24:mi&#039;),1,13)||&#039;0&#039; AS etime,\n                      event,count(*)*10  AS dbtime\n                 FROM dba_hist_active_sess_history\n                WHERE sample_time between to_date(&#039;2021-09-09 16:00:00&#039;,&#039;yyyy-mm-dd hh24:mi:ss&#039;)\n                                      AND to_date(&#039;2021-09-09 17:00:00&#039;,&#039;yyyy-mm-dd hh24:mi:ss&#039;)\n                GROUP BY instance_number,\n                         SUBSTR(to_char(sample_time,&#039;yyyymmdd hh24:mi&#039;),1,13),\n                         event))\n WHERE rn &lt; = 5;<\/code><\/pre>\n<p><a href=\"http:\/\/dba.qishuo.xin\/wp-content\/uploads\/2021\/09\/wp_editor_md_5df458b92a5b74ae3bb1d5ec0bcf13f5.jpg\" data-fancybox=\"images\" data-fancybox=\"images\"><img decoding=\"async\" src=\"http:\/\/dba.qishuo.xin\/wp-content\/uploads\/2021\/09\/wp_editor_md_5df458b92a5b74ae3bb1d5ec0bcf13f5.jpg\" alt=\"\" \/><\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>1. \u4ec0\u4e48\u662fcursor\uff1apin S wait o&hellip;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2,21],"tags":[56,16,205],"class_list":{"0":"post-450","1":"post","2":"type-post","3":"status-publish","4":"format-standard","5":"hentry","6":"category-oracle","8":"tag-cursor-pin-s-wait-on-x","9":"tag-oracle","10":"tag-205"},"_links":{"self":[{"href":"http:\/\/dba.qishuo.xin\/index.php?rest_route=\/wp\/v2\/posts\/450","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/dba.qishuo.xin\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/dba.qishuo.xin\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/dba.qishuo.xin\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/dba.qishuo.xin\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=450"}],"version-history":[{"count":18,"href":"http:\/\/dba.qishuo.xin\/index.php?rest_route=\/wp\/v2\/posts\/450\/revisions"}],"predecessor-version":[{"id":1552,"href":"http:\/\/dba.qishuo.xin\/index.php?rest_route=\/wp\/v2\/posts\/450\/revisions\/1552"}],"wp:attachment":[{"href":"http:\/\/dba.qishuo.xin\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=450"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/dba.qishuo.xin\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=450"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/dba.qishuo.xin\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=450"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}