文章目录
1、如何度量增删改操作产生的日志量
- 在SQL*Plus中使用autotrace功能，根据统计信息获取
当在SQL*Plus中启用autotrace跟踪后，在执行了特定的DML语句时，Oracle会显示该语句的统计信息，其中，Redo size一栏表示的就是该操作产生的Redo的数量：
SQL> set autotrace trace stat
SQL> insert into test
2 select empno,ename from scott.emp;
已创建12行。
Statistics
----------------------------------------------------------
189 recursive calls
2 db block gets
37 consistent gets
4 physical reads
564 redo size
778 bytes sent via SQL*Net to client
823 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
12 rows processed
- 通过v$mystat查询：
Oracle通过v$mystat视图记录当前session的统计信息，也可以从该视图中查询得到session的Redo生成情况：
SQL> col name for a30
SQL> select a.name,b.value
2 from v$statname a,v$mystat b
3 where a.statistic#=b.statistic# and a.name='redo size';
NAME VALUE
------------------------------ ----------
redo size 5000
SQL> insert into test
2 select empno,ename from scott.emp;
已创建12行。
SQL> select a.name,b.value
2 from v$statname a,v$mystat b
3 where a.statistic#=b.statistic# and a.name='redo size';
NAME VALUE
------------------------------ ----------
redo size 5564
SQL> select 5564-5000 from dual;
5564-5000
----------
564
- 通过v$sysstat查询来估算每天产生日志量
对于数据库全局redo的生成量，可以通过v$sysstat视图来查询得到：
SQL> col value for 999999999999
SQL> select name,value from v$sysstat
2 where name='redo size';
NAME VALUE
------------------------------ -------------
redo size 11471552
从v$sysstat视图中得到的是自数据库实例启动以来累计日志生成量，可以根据实例启动时间来大致估算每天数据库的日志生成量：
SQL> select
(select value/1024/1024/1024 from v$sysstat where name='redo size')/
(select sysdate-(select startup_time from v$instance) from dual)
redo_gb_per_day from dual;
REDO_GB_PER_DAY
---------------
.073238253
2、根据归档日志估算数据增量情况
资料库地址：E:/1@Repository IT/1.0@Oracle/1.0.D@Oracle脚本&工具/oracle脚本/统计日志产生情况
- 按天统计
alter session set nls_date_format='yyyy-mm-dd';
set echo off
set feedback off
select logtime,
count(*) log_cnt,
round(sum(blocks * block_size) / 1024 / 1024 /1024) Gbsize
from (select a.THREAD#,
trunc(first_time) as logtime,
a.BLOCKS,
a.BLOCK_SIZE
from v$archived_log a
where a.DEST_ID = 1
and a.FIRST_TIME between to_date('2016-10-01 00:00','yyyy-mm-dd HH24:mi') and to_date('2016-10-20 18:00','yyyy-mm-dd HH24:mi'))
group by logtime
order by logtime desc;
- 按小时统计
alter session set nls_date_format='yyyy-mm-dd HH24';
set echo off
set feedback off
select logtime,
count(*) log_cnt,
round(sum(blocks * block_size) / 1024 / 1024) mbsize
from (select a.THREAD#,
trunc(first_time, 'hh') as logtime,
a.BLOCKS,
a.BLOCK_SIZE
from v$archived_log a
where a.DEST_ID = 1
and a.FIRST_TIME between to_date('2018-11-02 07:00','yyyy-mm-dd HH24:mi') and to_date('2018-11-02 12:00','yyyy-mm-dd HH24:mi'))
group by logtime
order by logtime desc;
- 按线程统计
select logtime,
thread#,
count(*) log_cnt,
round(sum(blocks * block_size) / 1024 / 1024) mbsize
from (select a.THREAD#,
trunc(first_time, 'hh') as logtime,
a.BLOCKS,
a.BLOCK_SIZE
from v$archived_log a
where a.DEST_ID = 1
and a.FIRST_TIME between to_date('2016-10-26 22:00','yyyy-mm-dd HH24:mi') and to_date('2016-10-27 01:00','yyyy-mm-dd HH24:mi'))
group by logtime,thread#
order by thread# asc,logtime desc;
3、DBA_HIST_SYSSTAT计算redo产生量(AWR中profile “redo size”)
- 每秒产生多少redo
set lines 160 pages 1000 echo off feedback off
col stat_name for a25
col date_time for a40
col BEGIN_INTERVAL_TIME for a20
col END_INTERVAL_TIME for a20
prompt "Enter the date in DD-Mon-YY Format and Stats you want to trend like 'redo size','physical reads','physical writes','session logical reads' etc."
WITH sysstat AS
(select sn.begin_interval_time begin_interval_time,
sn.end_interval_time end_interval_time,
ss.stat_name stat_name,
ss.value e_value,
lag(ss.value, 1) over(order by ss.snap_id) b_value
from dba_hist_sysstat ss, dba_hist_snapshot sn
where trunc(sn.begin_interval_time) >= sysdate-7
and ss.snap_id = sn.snap_id
and ss.dbid = sn.dbid
and ss.instance_number = sn.instance_number
and ss.dbid = (select dbid from v$database)
and ss.instance_number = (select instance_number from v$instance)
and ss.stat_name = 'redo size')
select to_char(BEGIN_INTERVAL_TIME, 'mm/dd/yy_hh24_mi') || to_char(END_INTERVAL_TIME, '_hh24_mi') date_time,
stat_name,
round((e_value - nvl(b_value,0)) / (extract(day from(end_interval_time - begin_interval_time)) * 24 * 60 * 60
+ extract(hour from(end_interval_time - begin_interval_time)) * 60 * 60
+ extract(minute from(end_interval_time - begin_interval_time)) * 60 + extract(second from(end_interval_time - begin_interval_time))),0) per_sec
from sysstat where(e_value - nvl(b_value,0)) > 0 and nvl(b_value,0) > 0
/
- 每小时产生多少redo
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
/
4、根据dba_hist_tbspc_space_usage表空间维度估算容量增长情况
从表空间历史使用空间维度，可以比较准确的估算出oracle数据库容量增长情况，以此来判断是否需要扩容…
- 按天来估算所有表空间每天增长量情况
select tmp.rtime,
tablespace_usedsize_kb,
tablespace_size_kb,
(tablespace_usedsize_kb -
LAG(tablespace_usedsize_kb, 1, NULL) OVER(ORDER BY tmp.rtime)) AS DIFF_KB
from (select min(rtime) rtime,
sum(tablespace_usedsize_kb) tablespace_usedsize_kb,
sum(tablespace_size_kb) tablespace_size_kb
from (select rtime,
e.tablespace_id,
(e.tablespace_usedsize) * (f.block_size) / 1024 tablespace_usedsize_kb,
(e.tablespace_size) * (f.block_size) / 1024 tablespace_size_kb
from dba_hist_tbspc_space_usage e,
dba_tablespaces f,
v$tablespace g
where e.tablespace_id = g.TS#
and f.tablespace_name = g.NAME
and f.contents not in ('TEMPORARY','UNDO'))
group by rtime) tmp,
(select min(rtime) rtime
from (select min(rtime) rtime,
sum(tablespace_usedsize_kb) tablespace_usedsize_kb,
sum(tablespace_size_kb) tablespace_size_kb
from (select rtime,
e.tablespace_id,
(e.tablespace_usedsize) * (f.block_size) / 1024 tablespace_usedsize_kb,
(e.tablespace_size) * (f.block_size) / 1024 tablespace_size_kb
from dba_hist_tbspc_space_usage e,
dba_tablespaces f,
v$tablespace g
where e.tablespace_id = g.TS#
and f.tablespace_name = g.NAME)
group by rtime) tmp
group by substr(rtime, 1, 10)) t2
where t2.rtime = tmp.rtime;
- 估算某表空间每天的增长量情况,筛选具体表空间
select tmp.rtime,
tablespace_usedsize_kb,
tablespace_size_kb,
(tablespace_usedsize_kb -
LAG(tablespace_usedsize_kb, 1, NULL) OVER(ORDER BY tmp.rtime)) AS DIFF_KB
from (select min(rtime) rtime,
sum(tablespace_usedsize_kb) tablespace_usedsize_kb,
sum(tablespace_size_kb) tablespace_size_kb
from (select rtime,
e.tablespace_id,
(e.tablespace_usedsize) * (f.block_size) / 1024 tablespace_usedsize_kb,
(e.tablespace_size) * (f.block_size) / 1024 tablespace_size_kb
from dba_hist_tbspc_space_usage e,
dba_tablespaces f,
v$tablespace g
where e.tablespace_id = g.TS#
and f.tablespace_name = g.NAME
and f.contents not in ('TEMPORARY','UNDO') and f.contents in (upper('&tbs_name')))
group by rtime) tmp,
(select min(rtime) rtime
from (select min(rtime) rtime,
sum(tablespace_usedsize_kb) tablespace_usedsize_kb,
sum(tablespace_size_kb) tablespace_size_kb
from (select rtime,
e.tablespace_id,
(e.tablespace_usedsize) * (f.block_size) / 1024 tablespace_usedsize_kb,
(e.tablespace_size) * (f.block_size) / 1024 tablespace_size_kb
from dba_hist_tbspc_space_usage e,
dba_tablespaces f,
v$tablespace g
where e.tablespace_id = g.TS#
and f.tablespace_name = g.NAME)
group by rtime) tmp
group by substr(rtime, 1, 10)) t2
where t2.rtime = tmp.rtime;
5、根据dba_hist_seg_stat估算对象数据增长情况
dba_hist_seg_stat数据字典视图里面记录了每个对象相对于前面快照，变化的数据块情况。
- 估算所有对象某段时间内数据块增长情况
column owner format a16
column object_name format a36
column start_day format a11
column block_increase format 9999999999
select obj.owner,
obj.object_name,
to_char(sn.BEGIN_INTERVAL_TIME, 'yyyy-mm-dd') start_day,
sum(a.db_block_changes_delta) block_increase
from dba_hist_seg_stat a, dba_hist_snapshot sn, dba_objects obj
where sn.snap_id = a.snap_id
and obj.object_id = a.obj#
and obj.owner not in ('SYS', 'SYSTEM')
and end_interval_time between to_timestamp('2019-09-24', 'yyyy-mm-dd') and
to_timestamp('2019-09-24', 'yyyy-mm-dd')
group by obj.owner,
obj.object_name,
to_char(sn.BEGIN_INTERVAL_TIME, 'yyyy-mm-dd')
order by obj.owner, obj.object_name;
- 估算某用户下对象，某段时间内数据块增长情况
column owner format a16
column object_name format a36
column start_day format a11
column block_increase format 9999999999
select obj.owner,
obj.object_name,
to_char(sn.BEGIN_INTERVAL_TIME, 'yyyy-mm-dd') start_day,
sum(a.db_block_changes_delta) block_increase
from dba_hist_seg_stat a, dba_hist_snapshot sn, dba_objects obj
where sn.snap_id = a.snap_id
and obj.object_id = a.obj#
and obj.owner in (upper('&usr_name'))
and obj.object_name = upper('&obj_name')
and end_interval_time between to_timestamp('2019-09-24', 'yyyy-mm-dd') and
to_timestamp('2019-09-24', 'yyyy-mm-dd')
group by obj.owner,
obj.object_name,
to_char(sn.BEGIN_INTERVAL_TIME, 'yyyy-mm-dd')
order by obj.owner, obj.object_name;
5、根据DBA_TAB_MODIFICATIONS统计表上增删改次数
Database Reference对DBA_TAB_MODIFICATIONS解释是，记录了自从上一次收集统计信息后，表上数据被修改的记录。所以，使用的时候需要先查询下上次收集统计信息是啥时候：
select OWNER,TABLE_NAME,to_char(LAST_ANALYZED,'yyyy-mm-dd hh24:mi:ss') from dba_tables where table_name=upper('table_name');
set linesize 200
col TABLE_OWNER for a20
col TABLE_NAME for a26
col TIMESTAMP for a16
col count(INSERTS) for 9999999
col count(UPDATES) for 9999999
col count(DELETES) for 9999999
select TABLE_OWNER,TABLE_NAME,to_char(TIMESTAMP,'yyyy-mm-dd hh24') TIMESTAMP,count(INSERTS),count(UPDATES),count(DELETES) from DBA_TAB_MODIFICATIONS where TABLE_NAME=upper('table_name') group by TABLE_OWNER,TABLE_NAME,to_char(TIMESTAMP,'yyyy-mm-dd hh24') order by TIMESTAMP desc;
2704
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
