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博客

通过DBMS_LOCK.SLEEP实现SQL对V$视图进行持续采样

最新文章