1

Topic: Grows FRA on 2 minutes

Prompt how to look at the reason of growth FRA? - Similar any tables change, and indexes are rebuilt, but to find I can not in any way. SOS!!!

2

Re: Grows FRA on 2 minutes

Really there are no ideas?

3

Re: Grows FRA on 2 minutes

, many thanks! Very curiously.

4

Re: Grows FRA on 2 minutes

, still plainly did not understand, only started to read, this tool allows to write to text broad gulls parallely with ? Somehow can hardly is more detailed tell?

5

Re: Grows FRA on 2 minutes

eupro wrote:

, still plainly did not understand, only started to read, this tool allows to write to text broad gulls parallely with ? Somehow can hardly is more detailed tell?

he allows to take broad gulls and to look that in them for operations were

6

Re: Grows FRA on 2 minutes

Alexander Ryndin, thanks!
And can is a bit more detailed? The tool is absolutely not familiar, I am afraid of affairs to do.

7

Re: Grows FRA on 2 minutes

Alexander Ryndin, I simply remember that somehow solved this task, somehow found already that such index is permanently rebuilt, only I do not remember as = (And without this mechanism, normal sequel-inquiry.

8

Re: Grows FRA on 2 minutes

Pancake, yes look at adjacent subjects, only recently about the big generation  the topic was...
Every day same.
Really nobody uses search from a word absolutely???

9

Re: Grows FRA on 2 minutes

Q.Tarantino, thanks!

10

Re: Grows FRA on 2 minutes

Type under the declaration...

11

Re: Grows FRA on 2 minutes

mefman, and you Mail of Russia wrote out?:-D

12

Re: Grows FRA on 2 minutes

eupro wrote:

mefman, and you Mail of Russia wrote out?:-D

13

Re: Grows FRA on 2 minutes

eupro wrote:

Alexander Ryndin, thanks!
And can is a bit more detailed? The tool is absolutely not familiar, I am afraid of affairs to do.

Using LogMiner to Analyze Redo Log Files:
https://docs.oracle.com/cd/B28359_01/se … m#i1005553

14

Re: Grows FRA on 2 minutes

Aliona, thanks big! I will now be raked with remaining, I will start to study

15

Re: Grows FRA on 2 minutes

Aliona, well in general yes, interesting, it were possible to look at updates/inserty under what tables. But it after all not about that, probably? For example, if a small label permanently , we certainly see it, but nearby there is a megatable on terabytes, with megaindexes, and on its line - we after all it do not see only one update, and one update at not the most successful index floods in  all terabytes from a label therefore as the index will be rebuilt. Or I not that write something?

16

Re: Grows FRA on 2 minutes

eupro wrote:

Or I not that write something?

You in the first post asked a question why it is filled FRA, to you some persons hinted on LogMiner.
Now what problem?

17

Re: Grows FRA on 2 minutes

Aliona;
I speak

mefman wrote:

type under the declaration...

To it with  it is necessary to begin:
What is  .

18

Re: Grows FRA on 2 minutes

eupro wrote:

Aliona, well in general yes, interesting, it were possible to look at updates/inserty under what tables. But it after all not about that, probably? For example, if a small label permanently , we certainly see it, but nearby there is a megatable on terabytes, with megaindexes, and on its line - we after all it do not see only one update, and one update at not the most successful index floods in  all terabytes from a label therefore as the index will be rebuilt. Or I not that write something?

From Internet open spaces:

begin
sys. DBMS_LOGMNR.ADD_LOGFILE (' /mnt/DATABASE_arch/DATABASE_1_321_655345323.arc ');
sys. DBMS_LOGMNR.START_LOGMNR (Options => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
end;
create table redo_sql as SELECT l. SCN, l. TIMESTAMP, l. USERNAME, l. OPERATION, l. SEG_OWNER, l. SEG_NAME, l. SSN, l. RS_ID, l. RBASQN, l. RBABLK, l. RBABYTE, l.DATA_OBJ#, l. REDO_VALUE, l. SQL_REDO from V$LOGMNR_CONTENTS l where seg_owner is not null
with t1 as
(select rs_id;
ssn;
rbablk;
rbabyte;
rbablk * 512 + rbabyte curpos;
lead (rbablk * 512 + rbabyte, 1, rbablk * 512 + rbabyte)
over (partition by substr (rs_id, 1, 9) order by
rbasqn;
rbablk;
rbabyte;
scn;
redo_value)
nextpos;
lead (rbablk * 512 + rbabyte, 1, rbablk * 512 + rbabyte)
over (partition by substr (rs_id, 1, 9) order by
rbasqn;
rbablk;
rbabyte;
scn;
redo_value)
- (rbablk * 512 + rbabyte)
redo_size;
operation;
case
when operation = ' INTERNAL ' and data_obj#! = 0 then
(select t0.owner
from dba_objects t0
where t0.object_id = t1.data_obj#)
else
seg_owner
end
seg_owner;
case
when operation = ' INTERNAL ' and data_obj#! = 0 then
(select t0.object_name nome
from dba_objects t0
where t0.object_id = t1.data_obj#)
else
seg_name
end
seg_name;
sql_redo;
redo_value
from redo_sql t1);
t2 as (select seg_owner, seg_name, SubStr (SQL_REDO, 1, 50) sql_text, count (*) cnt, sum (redo_size) redo_size from
(select rs_id, ssn, rbablk, rbabyte, curpos, nextpos, redo_size, seg_owner, seg_name, operation, sql_redo from t1
order by substr (rs_id, 1, 9), ssn, rbablk, rbabyte, redo_value)
group by seg_owner, seg_name, SubStr (SQL_REDO, 1, 50))
select Nvl (i.table_name, a.seg_name), Nvl (sql_text, ' INDEX '), sum (cnt), sum (redo_size)
from t2 a, dba_indexes i
where i.owner (+) =a.seg_owner and i.index_name (+) =a.seg_name
group by Nvl (i.table_name, a.seg_name), sql_text
order by 4 desc

19

Re: Grows FRA on 2 minutes

Takurava, thanks! The big human thanks

20

Re: Grows FRA on 2 minutes

Two jambs - removed the superfluous:

begin
sys. DBMS_LOGMNR.ADD_LOGFILE (' /mnt/DATABASE_arch/DATABASE_1_321_655345323.arc ');
sys. DBMS_LOGMNR.START_LOGMNR (Options => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
end;

And here are necessary all the line long without restriction:

create table redo_sql as SELECT l. SCN, l. TIMESTAMP, l. USERNAME, l. OPERATION, l. SEG_OWNER, l. SEG_NAME, l. SSN, l. RS_ID, l. RBASQN, l. RBABLK, l. RBABYTE, l.DATA_OBJ#, l. REDO_VALUE, l. SQL_REDO from V$LOGMNR_CONTENTS l