通過DBMS_LOCK.SLEEP實現SQL對V$檢視進行持續取樣

westzq1984發表於2012-10-14
SQL
V$檢視基於X$表，是記憶體實時資料，其沒有UNDO，也就是說沒有一致性讀
那麼可以通過一個SQL語句，取2個時間點的差值，直接取樣

為了實現取樣中間的時間停頓，需要使用dbms_lock.sleep寫一個func，睡眠指定時間
CREATE OR REPLACE FUNCTION F_SLEEP(AN_SEC NUMBER) 
RETURN NUMBER                                     
AS                                                
BEGIN                                             
  dbms_lock.sleep(AN_SEC);                        
  RETURN AN_SEC;                                  
END;                                              
/                                                 

下面是2個例子，一個是9i對ash取樣，獲得TOP SQL/WAIT/PROGRAM
--------------------------------------------------------------------------------
--
-- File name:   ash_top9i.sql
-- Author   :   zhangqiao
-- Copyright:   zhangqiaoc@olm.com.cn
-- Warning  :   Execution plan must use B for drive table,
--              and A must be connected by nested loop
--------------------------------------------------------------------------------

-- CREATE OR REPLACE FUNCTION F_SLEEP(AN_SEC NUMBER)
-- RETURN NUMBER
-- AS
-- BEGIN
--   dbms_lock.sleep(AN_SEC);
--   RETURN AN_SEC;
-- END;
-- /

SET pagesize 999 linesize 130
break on ID
col info for a50
set timing on

WITH SAMPLE AS (
select /*+MATERIALIZE*/* from(
-- **************************************************************************
  select /*+leading(b) use_nl(a)*/* FROM
  -- view a beg -------------------------------------------------------------
  (SELECT S.SID,S.USERNAME,S.COMMAND,S.OSUSER,
          S.PROCESS,S.MACHINE,S.TERMINAL,S.PROGRAM,S.SQL_HASH_VALUE,
          W.SEQ#,decode(W.STATE,'WAITING',W.EVENT,'ON CPU') EVENT,
          W.STATE,dbms_utility.get_time sample_id
     FROM V$SESSION S,V$SESSION_WAIT W
    WHERE S.SID = W.SID
      AND S.USERNAME IS NOT NULL
      AND S.STATUS = 'ACTIVE'
      AND s.SID <> (SELECT SID FROM V$MYSTAT WHERE ROWNUM=1)
    UNION ALL
   SELECT (0-F_SLEEP(1)) s1,null s2,null s3,null s4,null s5,null s6,
          null s7,null s8,null s9,null s10,null s11,null s12,null s13
     FROM dual ) a,
  -- view b beg -------------------------------------------------------------
  (select * from dual connect by rownum<=&&1) b
-- **************************************************************************
) where SID>0)
SELECT '[1] TOPSQL' id,to_char(SQL_HASH_VALUE) INFO ,
       substr(DECODE(COMMAND,1,'DDL',2,'INSERT',3,'Query',6,'UPDATE',7,'DELETE',
                     47,'PL/SQL_package_call',50,'Explain Plan',170,'CALL',189,
                     'MERGE',TO_CHAR(COMMAND)),1,8) OPCODE,COUNT(*)
  FROM SAMPLE
 GROUP BY SQL_HASH_VALUE,
       substr(DECODE(COMMAND,1,'DDL',2,'INSERT',3,'Query',6,'UPDATE',7,'DELETE',
                     47,'PL/SQL_package_call',50,'Explain Plan',170,'CALL',189,
                     'MERGE',TO_CHAR(COMMAND)),1,8)
HAVING count(*) > &1/10
 UNION ALL
SELECT '[2] TOPEET' ID,event,NULL,COUNT(*) FROM SAMPLE GROUP BY event
 UNION ALL
SELECT '[3] TOPPGM' id,substr(PROGRAM,1,INSTR(program,'@')-1) PROGRAM,NULL,COUNT(*)
  FROM SAMPLE GROUP BY substr(PROGRAM,1,INSTR(program,'@')-1) HAVING count(*) > &1/10
 ORDER BY 1,4 DESC;
 
undefine 1;

另一個是對v$sesstat取樣，獲得指定指標最高的會話
是另外一種實現topsess的方法。
以前用表函式實現的，參見
http://space.itpub.net/?uid-8242091-action-viewspace-itemid-720509

--------------------------------------------------------------------------------
--
-- File name:   tops.sql
-- Author:      zhangqiao
-- Copyright:   zhangqiaoc@olm.com.cn
--
--------------------------------------------------------------------------------

-- CREATE OR REPLACE FUNCTION F_SLEEP(AN_SEC NUMBER)
-- RETURN NUMBER
-- AS
-- BEGIN
--   dbms_lock.sleep(AN_SEC);
--   RETURN AN_SEC;
-- END;
-- /

SET pagesize 9999 linesize 100
SET VERIFY OFF TIMING ON
col statistic# FOR 999999
col NAME FOR a60

-- 3:statistic name
SELECT statistic#,NAME FROM v$statname WHERE NAME LIKE '%&&3%' and '&3' is not null

-- 1:statistic#
-- 2:sleep second
SELECT * FROM (
WITH
  a AS (SELECT /*+MATERIALIZE*/SID,VALUE FROM v$sesstat WHERE statistic#=&&1),
  b AS (SELECT /*+MATERIALIZE*/F_SLEEP(&&2) s1 FROM dual),
  c AS (SELECT /*+MATERIALIZE*/SID,VALUE FROM v$sesstat WHERE statistic#=&1)
SELECT c.sid,c.value - NVL(a.value,0) VALUE,round((c.value - NVL(a.value,0))/&2,0) rate
  FROM a,b,c
 WHERE c.sid = a.sid(+)
   AND c.value - NVL(a.value,0) > 0
 ORDER BY 2 DESC
) WHERE ROWNUM<=10;

undefine 1
undefine 2
undefine 3

使用以前的一個指令碼runsql.sh，可以實現對指令碼的迴圈呼叫

while (( 1<2 ))
do
sqlplus -s "/ as sysdba" <
set pagesize 9999 linesize 170
@$1
exit
EOF
sleep 10
done



來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/8242091/viewspace-746380/，如需轉載，請註明出處，否則將追究法律責任。

相關文章