[20230210]建立完善swcnm.sql脚本.txt

--//遇到asm例子出现阻塞的情况.正常使用tpt的swc脚本无法查询.因为asm实例在nomount状态.with subquery报错.
--//自己建立一个新的查询脚本.使用v$instance,v$thread作为子查询的子表.

SYS@test> startup nomount
ORACLE instance started.

Total System Global Area  805306368 bytes
Fixed Size                  8924064 bytes
Variable Size             297796704 bytes
Database Buffers          490733568 bytes
Redo Buffers                7852032 bytes

SYS@test> set verify off
SYS@test> @ swc sid||','||SERIAL#||',@'||inst_id||','||event 1=1
                              THEN ' ['||NVL((SELECT class FROM bclass WHERE r = s.p3),'undo @bclass '||s.p3)||']' ELSE null END,'ON CPU')
                                                                *
ERROR at line 22:
ORA-01219: database or pluggable database not open: queries allowed on fixed tables or views only
--//主要里面使用with子查询,使用别名不对.
--//改写如下:

$ cat swcnm.sql
prompt  @ swcnm sid||','||SERIAL#||',@'||inst_id||','||event 1=1
col path format a180
WITH
    v$instance AS (SELECT /*+ INLINE */ class, ROWNUM r from v$waitstat)
  , v$thread AS (
    SELECT
        REPLACE(SYS_CONNECT_BY_PATH(&1, '->'), '->', ' -> ')
               ||CASE WHEN CONNECT_BY_ISLEAF = 1 AND ses.blocking_session IS NOT NULL
                      THEN ' -> [idle blocker '||ses.blocking_instance||','||ses.blocking_session||' ('||ses.program||')]' ELSE NULL   
                 END path
    FROM (
        SELECT
            s.*
          , CASE WHEN s.type = 'BACKGROUND' AND s.program LIKE '%(DBW%)' THEN
              '(DBWn)'
            WHEN s.type = 'BACKGROUND' OR REGEXP_LIKE(s.program, '.*\([PJ]\d+\)') THEN
              REGEXP_REPLACE(SUBSTR(s.program,INSTR(s.program,'(')), '\d', 'n')
            ELSE
                '('||REGEXP_REPLACE(REGEXP_REPLACE(s.program, '(.*)@(.*)(\(.*\))', '\1'), '\d', 'n')||')'
            END || ' ' program2
          , NVL(s.event||CASE WHEN event like 'enq%' AND state = 'WAITING'
                              THEN ' [mode='||BITAND(p1, POWER(2,14)-1)||']'
                              WHEN s.event IN (SELECT name FROM v$event_name WHERE parameter3 = 'class#')
                              THEN ' ['||NVL((SELECT class FROM v$instance WHERE r = s.p3),'undo @bclass '||s.p3)||']' ELSE null END,'ON CPU')
                       || ' ' event2
          , TO_CHAR(CASE WHEN state = 'WAITING' THEN p1 ELSE null END, '0XXXXXXXXXXXXXXX') p1hex
          , TO_CHAR(CASE WHEN state = 'WAITING' THEN p2 ELSE null END, '0XXXXXXXXXXXXXXX') p2hex
          , TO_CHAR(CASE WHEN state = 'WAITING' THEN p3 ELSE null END, '0XXXXXXXXXXXXXXX') p3hex
        FROM
            gv$session s
    ) ses
    CONNECT BY NOCYCLE
        (    PRIOR ses.blocking_session  = ses.sid
         AND PRIOR ses.blocking_instance = ses.inst_id
        )
    START WITH (ses.state='WAITING' AND ses.wait_class!='Idle') AND &2
)
SELECT
    COUNT(*) sessions
  , path
FROM
    v$thread
GROUP BY
    path
ORDER BY
    sessions DESC
/

--//例子:
@ swcnm sid||','||SERIAL#||',@'||inst_id||','||event 1=1

--//简单测试:
--//先在nomunt下测试:
SYS@test> @ swcnm sid||','||SERIAL#||',@'||inst_id||','||event 1=1
@ swcnm sid||','||SERIAL#||',@'||inst_id||','||event 1=1
no rows selected

--//ok没有问题,进入open模式,继续测试:
SCOTT@test01p> @ ver1
PORT_STRING                    VERSION        BANNER                                                                               CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0           12.2.0.1.0     Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0

--//session 1:
SCOTT@test01p> @ spid
       SID    SERIAL# PROCESS                  SERVER    SPID                     PID  P_SERIAL# C50
---------- ---------- ------------------------ --------- -------------------- ------- ---------- --------------------------------------------------
       169      61475 9544:1400                DEDICATED 12952                     26          4 alter system kill session '169,61475' immediate;

SCOTT@test01p> create table t ( a number);
Table created.

SCOTT@test01p> insert into t values (1);
1 row created.

SCOTT@test01p> commit ;
Commit complete.

SCOTT@test01p> update t set a=1;
1 row updated.

--//session 2:
SCOTT@test01p> @ spid
       SID    SERIAL# PROCESS                  SERVER    SPID                     PID  P_SERIAL# C50
---------- ---------- ------------------------ --------- -------------------- ------- ---------- --------------------------------------------------
       178      25684 8384:7108                DEDICATED 3004                      42          7 alter system kill session '178,25684' immediate;

SCOTT@test01p> update t set a=2;

--//挂起!!
--//session 1:
SCOTT@test01p> @ swcnm sid||','||SERIAL#||',@'||inst_id||','||event 1=1
@ swcnm sid||','||SERIAL#||',@'||inst_id||','||event 1=1
  SESSIONS PATH
---------- -----------------------------------------------------------------------------------------------
         1  -> 178,25684,@1,enq: TX - row lock contention -> 169,61475,@1,SQL*Net message to client
         1  -> 178,25684,@1,enq: TX - row lock contention