Oracle產生redo日誌量大小統計
在Oracle中，對於資料庫的修改操作都會記錄redo，那麼不同的操作會產生多少redo呢？可以透過以下一些方式來查詢來統計產生的redo日誌量。
（1）SQL*Plus中使用AUTOTRACE的使用。
當在SQL*Plus中啟用autotrace跟蹤後，在執行了特定的DML語句時，Oracle會顯示該語句的統計資訊，其中，redo Size一欄表示的就是該操作產生的redo的數量，其單位為Bytes：
SCOTT@seiang11g>set autotrace traceonly statistics
注意：如果在啟動autotrace跟蹤的時候，出現如下報錯：SP2-0618:
Cannot find the Session Identifier.
Check PLUSTRACE role is enabled.
解決方法請參考另一篇博文：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）透過v$mystat查詢。
Oracle透過v$mystat檢視記錄當前session的統計資訊，我們也可以從該檢視中查詢得到session的redo生成情況：
SCOTT@seiang11g>set autot off
SCOTT@seiang11g>
SCOTT@seiang11g>select a.name,b.value from v$statname a,v$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,b.value from v$statname a,v$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）透過v$sysstat查詢。
對於資料庫全域性Redo的生成量，可以透過v$sysstat檢視來查詢得到：
SYS@seiang11g>select name,value from v$sysstat where name='redo size';
NAME VALUE
---------------------------------------------------------------- ----------
redo size 548518160
從v$sysstat檢視中得到的是自資料庫例項啟動以來的累積日誌生成量，可以根據例項啟動時間大致估算每天資料庫的日誌生成量：
SYS@seiang11g>alter session set nls_date_format='yyyy-mm-dd hh24: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
如果資料庫執行在歸檔模式下，由於其他因素的影響，以上Redo生成量並不代表歸檔日誌的大小，但是可以透過一定的加權提供參考。
至於歸檔日誌的生成量，可以透過v$archived_log檢視，根據一段時間的歸檔日誌量進行估算得到。該檢視中記錄了歸檔日誌的主要資訊：
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
某日全天的日誌生成可以透過如下查詢計算：
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
最近日期的日誌生成統計：
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
根據每日歸檔的生成量，我們也可以反過來估計每日的資料庫活動性及週期性，並決定空間分配等問題。
擴充：
（一）以下指令碼可以用於列出最近Oracle資料庫每小時估算的redo重做日誌產生量，因為估算資料來源於archivelog的產生量和大小，所以資料是近似值，可供參考:
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 <= (1 * 24) + 3),
v$database
WHERE log_mode = 'ARCHIVELOG')
SELECT hour_end_time, NVL(ROUND(SUM(size_mb), 3), 0) size_mb, i.instance_name
FROM(
SELECT hour_end_time, CASE WHEN(hour_end_time - (1 / 24)) > 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.sequence#, 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 <= TRUNC(SYSDATE, 'HH')
GROUP BY hour_end_time, i.instance_name
ORDER BY hour_end_time
/
執行結果：
HOUR_END_TIME SIZE_MB INSTANCE_NAME
------------------- ---------- ----------------
2017-09-17 14:00:00 5.25 seiang11g
2017-09-17 15:00:00 5.374 seiang11g
2017-09-17 16:00:00 5.374 seiang11g
2017-09-17 17:00:00 5.374 seiang11g
2017-09-17 18:00:00 5.374 seiang11g
2017-09-17 19:00:00 5.374 seiang11g
2017-09-17 20:00:00 5.374 seiang11g
2017-09-17 21:00:00 5.374 seiang11g
2017-09-17 22:00:00 5.374 seiang11g
2017-09-17 23:00:00 2.79 seiang11g
2017-09-18 00:00:00 2.77 seiang11g
2017-09-18 01:00:00 2.77 seiang11g
2017-09-18 02:00:00 2.77 seiang11g
2017-09-18 03:00:00 2.77 seiang11g
2017-09-18 04:00:00 2.77 seiang11g
2017-09-18 05:00:00 2.77 seiang11g
2017-09-18 06:00:00 2.77 seiang11g
2017-09-18 07:00:00 2.77 seiang11g
2017-09-18 08:00:00 2.77 seiang11g
2017-09-18 09:00:00 2.77 seiang11g
2017-09-18 10:00:00 2.77 seiang11g
2017-09-18 11:00:00 2.77 seiang11g
2017-09-18 12:00:00 .005 seiang11g
2017-09-18 13:00:00 0 seiang11g
2017-09-18 14:00:00 0 seiang11g
（二）Oracle查詢最近幾天每小時歸檔日誌產生數量的指令碼，指令碼內容如下所示：
SELECT SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5) Day,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'00',1,0)) H00,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'01',1,0)) H01,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'02',1,0)) H02,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'03',1,0)) H03,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'04',1,0)) H04,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'05',1,0)) H05,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'06',1,0)) H06,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'07',1,0)) H07,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'08',1,0)) H08,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'09',1,0)) H09,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'10',1,0)) H10,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'11',1,0)) H11,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'12',1,0)) H12,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'13',1,0)) H13,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'14',1,0)) H14,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'15',1,0)) H15,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'16',1,0)) H16,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'17',1,0)) H17,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'18',1,0)) H18,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'19',1,0)) H19,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'20',1,0)) H20,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'21',1,0)) H21,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'22',1,0)) H22,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'23',1,0)) H23,
COUNT(*) TOTAL
FROM v$log_history a
WHERE first_time>=to_char(sysdate-10)
GROUP BY SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5)
ORDER BY SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5) DESC;
修改天數，可以修改WHERE first_time>=to_char(sysdate-11)
執行結果：
參考連結：
作者：SEian.G（苦練七十二變，笑對八十一難）
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31015730/viewspace-2145194/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle 日誌產生大小的計算Oracle
- 每天產生REDO歸檔日誌量
- oracle redo日誌產生量測試及比較1Oracle Redo
- 【oracle】關於日誌產生量的計算Oracle
- oracle redo日誌產生量測試及比較2_insertOracle Redo
- Oracle調整redo log日誌大小Oracle
- Oracle資料庫減少redo日誌產生方式Oracle資料庫
- 減少日誌產生量小結
- 減少oracle日誌的產生Oracle
- 歸檔日誌大小統計
- oracle檢視昨天產生歸檔日誌檔案總量Oracle
- 【TUNE_ORACLE】Oracle檢查點（四）檢查點對redo日誌的影響和redo日誌大小設定建議Oracle
- 修改online redo日誌檔案大小
- Oracle中undo 如何產生RedoOracle
- MySQL 5.6修改REDO日誌的大小和個數MySql
- 線上非rman備份產生和普通操作產生的redo大小比較
- Oracle redo日誌內容探索(一)Oracle Redo
- 轉roger大師_計算sql語句產生的redo和undo大小SQL
- 歸檔日誌大小 與使用rman 備份後的歸檔日誌產生的備份集大小的關係
- oracle 統計redo統計當天日誌生成和最近日期日誌生成統計Oracle
- REDO日誌管理
- oracle歸檔模式下imp匯入資料,產生的歸檔日誌大小記錄Oracle模式
- Oracle redo日誌內容探索之二Oracle Redo
- MySQL如何計算統計redo log大小MySql
- 【REDO】Oracle 日誌挖掘，分析歸檔日誌線上日誌主要步驟Oracle
- 線上修改REDO LOG的大小及增加新的日誌組
- Oracle11g調整redo日誌大小、組數和每組成員數Oracle
- 非IMU模式下DML語句產生的REDO日誌內容格式解讀模式
- MySQL 日誌系統 redo log、binlogMySql
- 檢視每日產生的日誌量大小
- redo日誌損壞
- redo重做日誌管理
- MySQL 日誌 undo | redoMySql
- Oracle redo 日誌切換時間頻率Oracle Redo
- oracle redo 日誌刪除後的恢復Oracle Redo
- oracle 修改日誌大小及增加日誌成員Oracle
- 修改oracle redo log的大小Oracle Redo
- Oracle 什麼時候select會產生redo?Oracle