目录在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语句。
ORACLE统计每个会话生成的日志大小：等您坐沙发呢！