Shulou(Shulou.com)06/01 Report--

In Oracle, all changes to the database record redo, so how much redo will be generated by different operations? You can count the number of redo logs generated by querying in the following ways.

(1) use of AUTOTRACE in SQL*Plus.

When autotrace tracing is enabled in SQL*Plus, when a specific DML statement is executed, Oracle displays statistics for that statement, where the redo Size column represents the number of redo generated by the operation in Bytes:

SCOTT@seiang11g > set autotrace traceonly statistics

Note: if the following error occurs when starting autotrace tracing: SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled.

For solutions, please refer to another blog post: SP2-0618: Cannot find the Session Identifier.Check PLUSTRACE role is enabled

SCOTT@seiang11g > create table emp1 as select * from emp

Table created.

SCOTT@seiang11g >

SCOTT@seiang11g > insert into emp1 select * from emp1

14 rows created.

Statistics

15 recursive calls

22 db block gets

33 consistent gets

5 physical reads

1872 redo size

834 bytes sent via SQL*Net to client

791 bytes received via SQL*Net from client

3 SQL*Net roundtrips to/from client

2 sorts (memory)

0 sorts (disk)

14 rows processed

(2) query through v$mystat.

Oracle records the statistics of the current session through the v$mystat view, from which we can also query the redo generation of session:

SCOTT@seiang11g > set autot off

SCOTT@seiang11g >

SCOTT@seiang11g > select a.name from v$statname b.value from v$statname a recorder mystat b

2 where a.statistic# = b.statistic# and a.name='redo size'

NAME VALUE

-

Redo size 29140

SCOTT@seiang11g >

SCOTT@seiang11g > insert into emp1 select * from emp1

28 rows created.

SCOTT@seiang11g >

SCOTT@seiang11g > select a.name from v$statname b.value from v$statname a recorder mystat b

2 where a.statistic# = b.statistic# and a.name='redo size'

NAME VALUE

-

Redo size 30708

SCOTT@seiang11g >

SCOTT@seiang11g > select 30708-29140 from dual

30708-29140

-

1568

(3) query through v$sysstat.

For the amount of global Redo generated in the database, you can query it through the v$sysstat view:

SYS@seiang11g > select name,value from v$sysstat where name='redo size'

NAME VALUE

-

Redo size 548518160

What is obtained from the v$sysstat view is the cumulative log generation since the database instance was started. You can roughly estimate the daily log generation of the database based on the instance startup time:

SYS@seiang11g > alter session set nls_date_format='yyyy-mm-dd hh34:mi:ss'

Session altered.

SYS@seiang11g >

SYS@seiang11g > select

2 (select value/1024/1024/1024 from v$sysstat where name='redo size'

3) /

4 (select round (sysdate-)

5 (select startup_time from v$instance

6) from dual

7) redo_gb_per_day

8 from dual

REDO_GB_PER_DAY

-

. 102173401

If the database is running in archive mode, the above Redo generation does not represent the size of the archive log due to other factors, but it can be used as a reference by some weighting.

As for the amount of archive logs generated, it can be estimated based on the number of archive logs over a period of time through the v$archived_log view. The main information of the archive log is recorded in this view:

SYS@seiang11g > select name,completion_time,blocks*block_size/1024/1024 MB

2 from v$archived_log where status ='A'

NAME COMPLETION_TIME MB

-

/ u01/app/oracle/arch/arch_1_949237404_8.log 2017-07-13 13:37:10 1.74072266

/ u01/app/oracle/arch/arch_1_949237404_9.log 2017-09-13 17:09:40 35.9506836

/ u01/app/oracle/arch/arch_1_949237404_10.log 2017-09-13 22:00:47 42.2592773

/ u01/app/oracle/arch/arch_1_949237404_11.log 2017-09-14 05:00:33 36.9936523

/ u01/app/oracle/arch/arch_1_949237404_12.log 2017-09-14 19:00:36 36.9335938

/ u01/app/oracle/arch/arch_1_949237404_13.log 2017-09-15 01:06:21 35.8876953

/ u01/app/oracle/arch/arch_1_949237404_14.log 2017-09-15 15:00:10 35.8935547

/ u01/app/oracle/arch/arch_1_949237404_15.log 2017-09-15 22:00:37 37.5634766

/ u01/app/oracle/arch/arch_1_949237404_16.log 2017-09-16 06:00:28 42.2397461

/ u01/app/oracle/arch/arch_1_949237404_17.log 2017-09-16 14:00:16 43.9946289

/ u01/app/oracle/arch/arch_1_949237404_18.log 2017-09-16 22:00:25 44.0483398

/ u01/app/oracle/arch/arch_1_949237404_19.log 2017-09-17 06:00:25 40.4213867

/ u01/app/oracle/arch/arch_1_949237404_20.log 2017-09-17 14:00:25 42.0063477

/ u01/app/oracle/arch/arch_1_949237404_21.log 2017-09-17 22:00:28 42.7241211

/ u01/app/oracle/arch/arch_1_949237404_22.log 2017-09-18 11:00:07 36.0229492

The log generation for a certain day can be calculated by the following query:

SYS@seiang11g > select trunc (completion_time)

2 sum (Mb) / 1024 DAY_GB

3 from

4 (select name

5 completion_time

6 blocks*block_size/1024/1024 Mb

7 from v$archived_log

8 where completion_time between trunc (sysdate)-2 and trunc (sysdate)-1

9)

10 group by trunc (completion_time)

TRUNC (COMPLETION_TI DAY_GB

--

2017-09-16 00:00:00. 127229214

Log generation statistics for the most recent date:

SYS@seiang11g > select trunc (completion_time)

2 sum (mb) / 1024 day_gb

3 from

4 (select name

5 completion_time

6 blocks*block_size/1024/1024 mb

7 from v$archived_log

8)

9 group by trunc (completion_time)

TRUNC (COMPLETION_TI DAY_GB

--

2017-09-15 00:00:00. 10678196

2017-09-18 00:00:00. 035178661

2017-09-13 00:00:00. 076376915

2017-09-17 00:00:00. 122218609

2017-07-13 00:00:00. 065961361

2017-09-16 00:00:00. 127229214

2017-09-14 00:00:00. 072194576

According to the amount of daily archives generated, we can also estimate the daily database activity and periodicity, and determine space allocation and other issues.

Expand:

(1) the following script can be used to list the hourly production of redo redo logs estimated by the recent Oracle database. Because the estimated data comes from the production and size of archivelog, the data is approximate and can be used for reference:

WITH times AS

(SELECT / * + MATERIALIZE * /

Hour_end_time

FROM (SELECT (TRUNC (SYSDATE, 'HH') + (2 / 24))-(ROWNUM / 24) hour_end_time

FROM DUAL

CONNECT BY ROWNUM lag_next_time THEN (next_time + (1 / 24)-hour_end_time) * (size_mb / (next_time-lag_next_time)) ELSE 0 END + CASE WHEN hour_end_time

< lead_next_time THEN(hour_end_time - next_time) * (lead_size_mb / (lead_next_time - next_time)) ELSE 0 END + CASE WHEN lag_next_time >

(hour_end_time-(1 / 24)) THEN size_mb ELSE 0 END + CASE WHEN next_time IS NULL THEN (1 / 24) * LAST_VALUE (CASE WHEN next_time IS NOT NULL AND lag_next_time IS NULL THEN 0 ELSE (size_mb / (next_time-lag_next_time)) END IGNORE NULLS) OVER

ORDER BY hour_end_time DESC, next_time DESC) ELSE 0 END size_mb

FROM (

SELECT t.hour_end_time, arc.next_time, arc.lag_next_time, LEAD (arc.next_time) OVER (

ORDER BY arc.next_time ASC) lead_next_time, arc.size_mb, LEAD (arc.size_mb) OVER (

ORDER BY arc.next_time ASC) lead_size_mb

FROM times t, (

SELECT next_time, size_mb, LAG (next_time) OVER (

ORDER BY next_time) lag_next_time

FROM (

SELECT next_time, SUM (size_mb) size_mb

FROM (

SELECT DISTINCT a.sequencewriting, a.next_time, ROUND (a.blocks * a.block_size / 1024 / 1024) size_mb

FROM v$archived_log a, (

SELECT / * + no_merge * /

CASE WHEN TO_NUMBER (pt.VALUE) = 0 THEN 1 ELSE TO_NUMBER (pt.VALUE) END VALUE

FROM v$parameter pt

WHERE pt.name = 'thread') pt

WHERE a.next_time > SYSDATE-3 AND a.thread# = pt.VALUE AND ROUND (a.blocks * a.block_size / 1024 / 1024) > 0)

GROUP BY next_time)) arc

WHERE t.hour_end_time = (TRUNC (arc.next_time (+), 'HH') + (1 / 24))

WHERE hour_end_time > TRUNC (SYSDATE, 'HH')-1-(1 / 24)), v$instance I

WHERE hour_end_time = to_char (sysdate-10)

GROUP BY SUBSTR (TO_CHAR (first_time, 'MM/DD/RR HH:MI:SS'), 1meme 5)

ORDER BY SUBSTR (TO_CHAR (first_time, 'MM/DD/RR HH:MI:SS'), 1meme 5) DESC

To modify the number of days, you can modify WHERE first_time > = to_char (sysdate-11)

Execution result:

Reference link:

Http://www.dbtan.com/2009/12/how-many-redo-has-produced.html

Http://www.askmaclean.com/archives/script%E5%88%97%E5%87%BAoracle%E6%AF%8F%E5%B0%8F%E6%97%B6%E7%9A%84redo%E9%87%8D%E5%81%9A%E6%97%A5%E5%BF%97%E4%BA%A7%E7%94%9F%E9%87%8F.html

Http://www.jb51.net/article/119200.htm

Author: SEian.G (hard practice changes in 72, but it is difficult to laugh at 81)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.