select * from V$LOGMNR_CONTENTS 2 часа

NiceBigMan
Дата: 05.03.2007 17:42:59
Добрый день !!
Запрос к V$LOGMNR_CONTENTS длится уже два часа.
redo лог 100 мб с интервалом анализа в 10 минут.
Ожидается не более 100 записей.
Как убыстрить процесс?
RAK123
Дата: 06.03.2007 07:37:45
dbazine


Now, since the database is open, I can do my table copy. This is useful for thorough analyzing, as V$LOGMNR_CONTENTS is very slow and can contain Millions of rows on a busy production system.
create table SYSTEM.LOGMINER_CONTENTS_920 tablespace TOOLS as select * from v$logmnr_contents;Then we can finish:
execute dbms_logmnr.add_logfile(logfilename => 'C:\TEMP\redo01.log', -
options => dbms_logmnr.removefile);
execute dbms_logmnr.end_logmnr;And now, we can use our own table. We can create a few indexes, if we really need to work a lot with it.
select count(*) from SYSTEM.LOGMINER_CONTENTS_920;

COUNT(*)
----------
11037

select to_char(timestamp, 'DD-MON-YYYY HH24:MI:SS') timestamp,
scn, log_id, username, seg_owner, seg_name, seg_type, operation,
sql_redo
from SYSTEM.LOGMINER_CONTENTS_920
where username = 'TESTX'
and seg_owner = 'TESTX'
and seg_name = 'TABLE1';

TIMESTAMP SCN LOG_ID USERNAME
-------------------- ---------- ---------- -----------------
SQL_REDO
------------------------------------------------------------
18-MAR-2003 20:47:10 181209 10 TESTX
CREATE TABLE table1 (
rec_id VARCHAR2(12) NOT NULL,
emp_last_name VARCHAR2(30),
emp_first_name VARCHAR2(30),
salary NUMBER(8,2) )
TABLESPACE tools;

18-MAR-2003 20:47:16 181293 10 TESTX
insert into "TESTX"."TABLE1"("REC_ID","EMP_LAST_NAME","EMP_FIRST_NAME", ……..

18-MAR-2003 20:47:30 181409 11 TESTX
update "TESTX"."TABLE1" set "SALARY" = '20000,1' where "SALARY" = '1000 ……..
NiceBigMan
Дата: 06.03.2007 09:41:12
спасибо