{"id":2106,"date":"2025-02-02T22:00:01","date_gmt":"2025-02-02T14:00:01","guid":{"rendered":"http:\/\/www.821121.com\/?p=2106"},"modified":"2025-05-09T22:02:18","modified_gmt":"2025-05-09T14:02:18","slug":"ash%e5%88%86%e6%9e%90","status":"publish","type":"post","link":"http:\/\/www.821121.com\/?p=2106","title":{"rendered":"ASH\u5206\u6790"},"content":{"rendered":"<p>ASH\u5206\u6790\u5e38\u89c4\u6d41\u7a0b\u548cSQL\u8bed\u53e5<\/p>\n<h2>step1 \u9a8c\u8bc1\u5bfc\u51fa\u7684ASH\u65f6\u95f4\u8303\u56f4<\/h2>\n<pre><code class=\"language-sql\">select \n t.dbid, t.instance_number, min(sample_time), max(sample_time), count(*) session_count\n  from DBA_HIST_ACTIVE_SESS_HISTORY t\n group by t.dbid, t.instance_number\n order by dbid, instance_number;<\/code><\/pre>\n<h2>step2 \u786e\u8ba4\u95ee\u9898\u53d1\u751f\u7684\u7cbe\u786e\u65f6\u95f4\u8303\u56f4<\/h2>\n<pre><code class=\"language-sql\">select \n dbid, instance_number, sample_id, sample_time, count(*) session_count\n  from DBA_HIST_ACTIVE_SESS_HISTORY t\n group by dbid, instance_number, sample_id, sample_time\n order by dbid, instance_number, sample_time;<\/code><\/pre>\n<h2>step3 \u786e\u5b9a\u6bcf\u4e2a\u91c7\u6837\u70b9\u7684top n event<\/h2>\n<pre><code class=\"language-sql\">select t.dbid,\n       t.sample_id,\n       t.sample_time,\n       t.instance_number,\n       t.event,\n       t.session_state,\n       t.c session_count\n  from (select t.*,\n               rank() over(partition by dbid, instance_number, sample_time order by c desc) r\n          from (select \n                 t.*,\n                 count(*) over(partition by dbid, instance_number, sample_time, event) c,\n                 row_number() over(partition by dbid, instance_number, sample_time, event order by 1) r1\n                  from DBA_HIST_ACTIVE_SESS_HISTORY t\n                where sample_time &gt;\n                    to_timestamp(&#039;2022-09-27 07:00:00&#039;,\n                                 &#039;yyyy-mm-dd hh24:mi:ss&#039;)\n                and sample_time &lt;\n                    to_timestamp(&#039;2022-09-27 08:00:00&#039;,\n                                 &#039;yyyy-mm-dd hh24:mi:ss&#039;)\n                ) t\n         where r1 = 1) t\n where r &lt; 3\n order by dbid, instance_number, sample_time, r;<\/code><\/pre>\n<h2>step4 \u89c2\u5bdf\u6bcf\u4e2a\u91c7\u6837\u70b9\u7684\u7b49\u5f85\u94fe<\/h2>\n<pre><code class=\"language-sql\">`select \n level                     lv,\n connect_by_isleaf         isleaf,\n connect_by_iscycle        iscycle,\n t.dbid,\n t.sample_id,\n t.sample_time,\n t.instance_number,\n t.session_id,\n t.sql_id,\n t.session_type,\n t.event,\n t.session_state,\n t.blocking_inst_id,\n t.blocking_session,\n t.blocking_session_status\n  from DBA_HIST_ACTIVE_SESS_HISTORY t\nwhere sample_time &gt;\n                    to_timestamp(&#039;2022-09-27 07:20:00&#039;,\n                                 &#039;yyyy-mm-dd hh24:mi:ss&#039;)\n                and sample_time &lt;\n                    to_timestamp(&#039;2022-09-27 07:40:00&#039;,\n                                 &#039;yyyy-mm-dd hh24:mi:ss&#039;)\n start with blocking_session is not null\nconnect by nocycle\n prior dbid = dbid\n       and prior sample_time = sample_time\n       and prior blocking_inst_id = instance_number\n       and prior blocking_session = session_id\n       and prior blocking_session_serial# = session_serial#\n order siblings by dbid, sample_time;`<\/code><\/pre>\n<h2>step5 \u57fa\u4e8e\u7b2c4\u6b65\u7684\u539f\u7406\u6765\u627e\u51fa\u6bcf\u4e2a\u91c7\u6837\u70b9\u7684\u6700\u7ec8top holder<\/h2>\n<pre><code class=\"language-sql\">select t.lv,\n       t.iscycle,\n       t.dbid,\n       t.sample_id,\n       t.sample_time,\n       t.instance_number,\n       t.session_id,\n       t.sql_id,\n       t.session_type,\n       t.event,\n       t.seq#,\n       t.session_state,\n       t.blocking_inst_id,\n       t.blocking_session,\n       t.blocking_session_status,\n       t.c blocking_session_count\n  from (select t.*,\n               row_number() over(partition by dbid, instance_number, sample_time order by c desc) r\n          from (select t.*,\n                       count(*) over(partition by dbid, instance_number, sample_time, session_id) c,\n                       row_number() over(partition by dbid, instance_number, sample_time, session_id order by 1) r1\n                  from (select \n                         level              lv,\n                         connect_by_isleaf  isleaf,\n                         connect_by_iscycle iscycle,\n                         t.*\n                          from DBA_HIST_ACTIVE_SESS_HISTORY t\n                        where sample_time &gt;\n                    to_timestamp(&#039;2022-09-27 07:30:00&#039;,\n                                 &#039;yyyy-mm-dd hh24:mi:ss&#039;)\n                and sample_time &lt;\n                    to_timestamp(&#039;2022-09-27 07:40:00&#039;,\n                                 &#039;yyyy-mm-dd hh24:mi:ss&#039;)\n                         start with blocking_session is not null\n                        connect by nocycle\n                         prior dbid = dbid\n                               and prior sample_time = sample_time\n                               and prior blocking_inst_id = instance_number\n                               and prior blocking_session = session_id\n                               and prior\n                                    blocking_session_serial# = session_serial#) t\n                 where t.isleaf = 1) t\n         where r1 = 1) t\n where r &lt; 3\n order by dbid, sample_time, r;<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"<p>ASH\u5206\u6790\u5e38\u89c4\u6d41\u7a0b\u548cSQL\u8bed\u53e5 step1 \u9a8c\u8bc1\u5bfc<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[49],"tags":[],"class_list":["post-2106","post","type-post","status-publish","format-standard","hentry","category-oracle-19c"],"_links":{"self":[{"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/posts\/2106","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.821121.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=2106"}],"version-history":[{"count":1,"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/posts\/2106\/revisions"}],"predecessor-version":[{"id":2108,"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/posts\/2106\/revisions\/2108"}],"wp:attachment":[{"href":"http:\/\/www.821121.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2106"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.821121.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2106"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.821121.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2106"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}