oracle生成日志,【学习笔记】ORACLE统计每个会话生成的日志大小的方法案例

【学习笔记】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统计每个会话生成的日志大小的方法案例