【学习笔记】ORACLE统计每个会话生成的日志大小的方法案例
时间:2016-11-08 21:02 来源:Oracle研究中心 作者:HTZ 点击:
次
天萃荷净
Oracle研究中心学习笔记:分享一篇关于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 (TNOracle о S 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 | 认真就输
--------------------------------------ORACLE-DBA----------------------------------------
最权威、专业的Oracle案例资源汇总之【学习笔记】ORACLE统计每个会话生成的日志大小的方法案例