当前位置: 首页 > BASIC > 正文

ORACLE统计每个会话生成的日志大小

      目录在ASH中没有统计会话的日志生成大小,只能通过v$sesstat来查询会话生成的总的日志大小。由于多数进程都是通过中间件连接过来,如果通过v$sesstat只能查询到会话总的日志大小,并不能查询到每段时间日志量大小,只能自己写个存储过程来不停的查询v$sesstat视图获取日志的大小。另外还可以利用logmnr来分析归档日志文件。

1 创建对象

CREATE TABLE SYSTEM.HTZ_REDO_SIZE_ANALYZE
(
  INST_ID       NUMBER,
  id            number,
  CREATE_TIME   DATE,
  SID           NUMBER,
  SERIAL#       NUMBER,
  USERNAME      VARCHAR2(30),
  PROGRAM       VARCHAR2(48),
  MACHINE       VARCHAR2(64),
  STATUS        VARCHAR2(8),
  SQL_ID        VARCHAR2(13),
  LAST_CALL_ET  NUMBER,
  SEQ#          NUMBER,
  PREV_SQL_ID   VARCHAR2(13),
  VALUE         NUMBER
) TABLESPACE SYSAUX NOLOGGING;

CREATE INDEX SYSTEM.IND_HTZ_REDO_SIZE_3 ON SYSTEM.HTZ_REDO_SIZE_ANALYZE (CREATE_TIME) TABLESPACE SYSAUX NOLOGGING;
CREATE INDEX SYSTEM.IND_HTZ_REDO_SIZE_2 ON SYSTEM.HTZ_REDO_SIZE_ANALYZE (SID) TABLESPACE SYSAUX NOLOGGING;
CREATE INDEX SYSTEM.IND_HTZ_REDO_SIZE_1 ON SYSTEM.HTZ_REDO_SIZE_ANALYZE (ID) TABLESPACE SYSAUX NOLOGGING;

create sequence scott.htz_sequence;

2 收集信息

DECLARE
   v_sequence   NUMBER;
BEGIN
   FOR counter IN 1 .. 200000
   LOOP
      SELECT SYSTEM.htz_sequence.NEXTVAL INTO v_sequence FROM DUAL;

      INSERT INTO SYSTEM.htz_redo_size_analyze
         SELECT a.inst_id,
                v_sequence,
                SYSDATE,
                a.sid,
                A.SERIAL#,
                a.username,
                a.program,
                a.machine,
                a.status,
                a.sql_id,
                a.last_call_et,
                a.seq#,
                a.prev_sql_id,
                e.VALUE
           FROM gv$session a, gv$statname d, gv$sesstat e
          WHERE     e.STATISTIC# = d.STATISTIC#
                AND e.sid(+) = a.sid
                AND a.inst_id = E.INST_ID
                AND e.inst_id = d.inst_id
                AND d.name = 'redo size'
                AND E.VALUE > 0
                AND (a.inst_id, a.paddr) NOT IN (SELECT inst_id, addr
                                                   FROM gv$process f
                                                  WHERE f.background = '1');

      COMMIT;
      DBMS_LOCK.sleep (5);
   END LOOP;
END;
/

3 分析数据

SELECT *
    FROM (SELECT inst_id,
                 create_time,
                 id,
                 program,
                 sid,
                 serial#,
                 sql_id,
                 prev_sql_id,
                 diff,
                 ROW_NUMBER ()
                    OVER (PARTITION BY inst_id, create_time ORDER BY diff DESC)
                    rownum1
            FROM (SELECT *
                    FROM (SELECT a.inst_id,
                                 TO_CHAR (a.create_time,
                                          'yyyy-mm-dd hh24:mi:ss')
                                    create_time,
                                 a.id,
                                 a.program,
                                 a.sid,
                                 a.serial#,
                                 a.sql_id,
                                 a.prev_sql_id,
                                 a.VALUE - b.VALUE diff
                            FROM SYSTEM.htz_redo_size_analyze a,
                                 SYSTEM.htz_redo_size_analyze b
                           WHERE     a.id - 1 = b.id
                                 AND a.sid = b.sid(+)
                                 AND a.serial# = b.serial#(+)
                                 AND a.program = b.program(+)
                                 AND a.inst_id = NVL ('&instid', a.inst_id)
                                 AND a.sid = NVL ('&sid', a.sid)
                                 AND a.inst_id = b.inst_id)
                   WHERE diff <> 0))
   WHERE rownum1 < 10
ORDER BY create_time, diff

NST_ID CREATE_TIME            ID PROGRAM                        SID  SERIAL# SQL_ID        PREV_SQL_ID         DIFF    ROWNUM1
------ ------------------- ----- ---------------------------- ----- -------- ------------- ------------- ---------- ----------
     1 2014-10-13 03:07:05  7413 sqlplus@orcl9i (TNS V1-V3)      34      215 aa6fxk28r7sv7 9qm1nuk2fxu92       3152          1
     1 2014-10-13 03:07:10  7414 sqlplus@orcl9i (TNS V1-V3)      34      215 aa6fxk28r7sv7 9qm1nuk2fxu92       1904          1
     1 2014-10-13 03:07:15  7415 sqlplus@orcl9i (TNS V1-V3)      34      215 aa6fxk28r7sv7 9qm1nuk2fxu92       1916          1
     1 2014-10-13 03:07:20  7416 sqlplus@orcl9i (TNS V1-V3)      34      215 aa6fxk28r7sv7 9qm1nuk2fxu92       1880          2
     1 2014-10-13 03:07:20  7416 sqlplus@orcl9i (TNS V1-V3)      21      445 bryaday8ykt5w a90ddv5yhzg6d   31237740          1
     1 2014-10-13 03:07:25  7417 sqlplus@orcl9i (TNS V1-V3)      34      215 aa6fxk28r7sv7 9qm1nuk2fxu92       1884          2
     1 2014-10-13 03:07:25  7417 sqlplus@orcl9i (TNS V1-V3)      21      445 bryaday8ykt5w a90ddv5yhzg6d   80017308          1
     1 2014-10-13 03:07:30  7418 sqlplus@orcl9i (TNS V1-V3)      34      215 aa6fxk28r7sv7 9qm1nuk2fxu92       1836          2
     1 2014-10-13 03:07:30  7418 sqlplus@orcl9i (TNS V1-V3)      21      445 bryaday8ykt5w a90ddv5yhzg6d   60001036          1
     1 2014-10-13 03:07:35  7419 sqlplus@orcl9i (TNS V1-V3)      34      215 aa6fxk28r7sv7 9qm1nuk2fxu92       2048          2
     1 2014-10-13 03:07:35  7419 sqlplus@orcl9i (TNS V1-V3)      21      445 bryaday8ykt5w a90ddv5yhzg6d   58623312          1
     1 2014-10-13 03:07:40  7420 sqlplus@orcl9i (TNS V1-V3)      34      215 aa6fxk28r7sv7 9qm1nuk2fxu92       2624          2
     1 2014-10-13 03:07:40  7420 sqlplus@orcl9i (TNS V1-V3)      21      445 bryaday8ykt5w a90ddv5yhzg6d   68383492          1
     1 2014-10-13 03:07:45  7421 sqlplus@orcl9i (TNS V1-V3)      34      215 aa6fxk28r7sv7 9qm1nuk2fxu92       2532          2
     1 2014-10-13 03:07:45  7421 sqlplus@orcl9i (TNS V1-V3)      21      445 bryaday8ykt5w a90ddv5yhzg6d   69960500          1
     1 2014-10-13 03:07:50  7422 sqlplus@orcl9i (TNS V1-V3)      34      215 aa6fxk28r7sv7 9qm1nuk2fxu92       1836          2
     1 2014-10-13 03:07:50  7422 sqlplus@orcl9i (TNS V1-V3)      21      445 bryaday8ykt5w bryaday8ykt5w   25038804          1
     1 2014-10-13 03:07:55  7423 sqlplus@orcl9i (TNS V1-V3)      34      215 aa6fxk28r7sv7 9qm1nuk2fxu92       1884          1

这样查询出每个会话间隔一定时间生成的日志量,如果相同的会话得到的SQL_ID都不一致的情况下,说明会话可能执行了多条SQL语句,那么我们还需要利用logmnr来分析归档日志文件,来定位会话在指定时间,执行了那些DML语句。

 

本文固定链接: http://www.htz.pw/2014/10/22/oracle%e7%bb%9f%e8%ae%a1%e6%af%8f%e4%b8%aa%e4%bc%9a%e8%af%9d%e7%94%9f%e6%88%90%e7%9a%84%e6%97%a5%e5%bf%97%e5%a4%a7%e5%b0%8f.html | 认真就输

该日志由 huangtingzhong 于2014年10月22日发表在 BASIC 分类下, 通告目前不可用,你可以至底部留下评论。
原创文章转载请注明: ORACLE统计每个会话生成的日志大小 | 认真就输
关键字: , , ,