Checks Points. In Case face huge archive log generation type of issues.
1. Check Jobs, schedules.
2. Check DB activity like SQL,TABLE,PROCEDURE, TRIGGER.
3. Session wise SQL redo generates.
4. Redo log group full / small redo log file size.
5. DB link distributed work.
6. Session lock.
7. Dead locks.
8. Generate AWR/ASH/ADDM report (see section Top 5 Wait Event,Physical Segemnt/Logical).
Note:- Non Impact (SMON redo generation, fregmentation)
***********************************************************************************
## Archiving generation report:
Per day the volume in MBytes of archived logs generated, deleted and of those that haven't yet been deleted by RMAN
SELECT SUM_ARCH.DAY,
SUM_ARCH.GENERATED_MB,
SUM_ARCH_DEL.DELETED_MB,
SUM_ARCH.GENERATED_MB - SUM_ARCH_DEL.DELETED_MB "REMAINING_MB"
FROM ( SELECT TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY') DAY,
SUM (ROUND ( (blocks * block_size) / (1024 * 1024), 2))
GENERATED_MB
FROM V$ARCHIVED_LOG
WHERE ARCHIVED = 'YES'
GROUP BY TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY')) SUM_ARCH,
( SELECT TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY') DAY,
SUM (ROUND ( (blocks * block_size) / (1024 * 1024), 2))
DELETED_MB
FROM V$ARCHIVED_LOG
WHERE ARCHIVED = 'YES' AND DELETED = 'YES'
GROUP BY TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY')) SUM_ARCH_DEL
WHERE SUM_ARCH.DAY = SUM_ARCH_DEL.DAY(+)
ORDER BY TO_DATE (DAY, 'DD/MM/YYYY');
***********************************************************************************
## Date & Time wise log generation.
set pages 1000
select to_char(COMPLETION_TIME,'DD-MM-YYYY hh24:mm') Hour,thread# , round(sum(BLOCKS*BLOCK_SIZE)/1048576) MB,count(*) Archives from v$archived_log
group by to_char(COMPLETION_TIME,'DD-MM-YYYY hh24:mm'),thread# order by 1 ;
Note: See the output Date & time when archive log frequently generates. After that create AWR/ASH report and see the TOP wait events.
***********************************************************************************
## Hour wise archive generation:
SELECT to_date(first_time) DAY,
to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'999') "00",
to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'999') "01",
to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'999') "02",
to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'999') "03",
to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'999') "04",
to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'999') "05",
to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'999') "06",
to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'999') "07",
to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'999') "08",
to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'999') "09",
to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'999') "10",
to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'999') "11",
to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'999') "12",
to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'999') "13",
to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'999') "14",
to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'999') "15",
to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'999') "16",
to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'999') "17",
to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'999') "18",
to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'999') "19",
to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'999') "20",
to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'999') "21",
to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'999') "22",
to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'999') "23", count(*) TOT
from
v$log_history
where to_date(first_time) > sysdate -8
GROUP by
to_char(first_time,'YYYY-MON-DD'), to_date(first_time)
order by to_date(first_time)
/
***********************************************************************************
## Archive generation number with volume: day/hour wise
select trunc(COMPLETION_TIME,'DD') Day, thread#, round(sum(BLOCKS*BLOCK_SIZE)/1048576/1024) GB,count(*) Archives_Generated from v$archived_log group by trunc(COMPLETION_TIME,'DD'), thread# order by 1;
select to_char(COMPLETION_TIME,'DD-MON-YY HH24') Hour, round(sum(BLOCKS*BLOCK_SIZE)/1048576/1024) gb, count(*) Archives from v$archived_log group by to_char(COMPLETION_TIME,'DD-MON-YY HH24') order by 1 ;
***********************************************************************************
## Currently session wise redo generation:
select b.inst_id,
lpad((b.SID || ',' || lpad(b.serial#,5)),11) sid_serial,
b.username,
machine,
b.osuser,
b.status,
a.redo_mb
from (select n.inst_id, sid,
round(value/1024/1024) redo_mb
from gv$statname n, gv$sesstat s
where n.inst_id=s.inst_id
and n.name = 'redo size'
and s.statistic# = n.statistic#
order by value desc
) a,
gv$session b
where b.inst_id=a.inst_id
and a.sid = b.sid
and rownum <= 30
;
***********************************************************************************
## Which segments are generating redo logs:
SELECT to_char(begin_interval_time,'YY-MM-DD HH24') snap_time,
dhso.object_name,
sum(db_block_changes_delta) BLOCK_CHANGED
FROM dba_hist_seg_stat dhss,
dba_hist_seg_stat_obj dhso,
dba_hist_snapshot dhs
WHERE dhs.snap_id = dhss.snap_id
AND dhs.instance_number = dhss.instance_number
AND dhss.obj# = dhso.obj#
AND dhss.dataobj# = dhso.dataobj#
AND begin_interval_time BETWEEN to_date('15-10-31 08:00','YY-MM-DD HH24:MI')
AND to_date('15-10-31 10:00','YY-MM-DD HH24:MI')
GROUP BY to_char(begin_interval_time,'YY-MM-DD HH24'),
dhso.object_name
HAVING sum(db_block_changes_delta) > 0
ORDER BY sum(db_block_changes_delta) desc ;
***********************************************************************************
## What sql was causing the redo log generation:
SELECT to_char(begin_interval_time,'YYYY_MM_DD HH24') WHEN,
dbms_lob.substr(sql_text,4000,1) SQL,
dhss.instance_number INST_ID,
dhss.sql_id,
executions_delta exec_delta,
rows_processed_delta rows_proc_delta
FROM dba_hist_sqlstat dhss,
dba_hist_snapshot dhs,
dba_hist_sqltext dhst
WHERE upper(dhst.sql_text) LIKE '%USR_RACUNI_MV%'
AND ltrim(upper(dhst.sql_text)) NOT LIKE 'SELECT%'
AND dhss.snap_id=dhs.snap_id
AND dhss.instance_number=dhs.instance_number
AND dhss.sql_id=dhst.sql_id
AND begin_interval_time BETWEEN to_date('15-10-31 07:00','YY-MM-DD HH24:MI')
AND to_date('15-10-31 16:00','YY-MM-DD HH24:MI');
***********************************************************************************
## Session wise programe redo entris genrate
col username for a15
col program for a20
col name for a20
select * from
(select /*+ first_rows */ a.username,a.sid,a.program,b.name,c.value
from v$session a,v$statname b,v$sesstat c
where a.sid=c.sid and b.statistic#=c.statistic# and b.name='redo entries' order by c.value desc)
where rownum < 11
;
***********************************************************************************
## For RAC Env.
Author: Riyaj Shamsudeen
spool redosize.log
col begin_interval_time format a30
set lines 160 pages 1000
col end_interval_time format a30
set colsep '|'
alter session set nls_date_format='DD-MON-YYYY';
with redo_sz as (
SELECT sysst.snap_id, sysst.instance_number, begin_interval_time ,end_interval_time , startup_time,
VALUE - lag (VALUE) OVER ( PARTITION BY startup_time, sysst.instance_number
ORDER BY begin_interval_time, startup_time, sysst.instance_number) stat_value,
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)) DELTA
FROM sys.wrh$_sysstat sysst , DBA_HIST_SNAPSHOT snaps
WHERE (sysst.dbid, sysst.stat_id) IN ( SELECT dbid, stat_id FROM sys.wrh$_stat_name WHERE stat_name='redo size' )
AND snaps.snap_id = sysst.snap_id
AND snaps.dbid =sysst.dbid
AND sysst.instance_number=snaps.instance_number
and begin_interval_time > sysdate-90
)
select instance_number,
to_date(to_char(begin_interval_time,'DD-MON-YYYY'),'DD-MON-YYYY') dt
, sum(stat_value) redo1
from redo_sz
group by instance_number,
to_date(to_char(begin_interval_time,'DD-MON-YYYY'),'DD-MON-YYYY')
order by instance_number, 2
/
spool off;
1. Check Jobs, schedules.
2. Check DB activity like SQL,TABLE,PROCEDURE, TRIGGER.
3. Session wise SQL redo generates.
4. Redo log group full / small redo log file size.
5. DB link distributed work.
6. Session lock.
7. Dead locks.
8. Generate AWR/ASH/ADDM report (see section Top 5 Wait Event,Physical Segemnt/Logical).
Note:- Non Impact (SMON redo generation, fregmentation)
***********************************************************************************
## Archiving generation report:
Per day the volume in MBytes of archived logs generated, deleted and of those that haven't yet been deleted by RMAN
SELECT SUM_ARCH.DAY,
SUM_ARCH.GENERATED_MB,
SUM_ARCH_DEL.DELETED_MB,
SUM_ARCH.GENERATED_MB - SUM_ARCH_DEL.DELETED_MB "REMAINING_MB"
FROM ( SELECT TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY') DAY,
SUM (ROUND ( (blocks * block_size) / (1024 * 1024), 2))
GENERATED_MB
FROM V$ARCHIVED_LOG
WHERE ARCHIVED = 'YES'
GROUP BY TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY')) SUM_ARCH,
( SELECT TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY') DAY,
SUM (ROUND ( (blocks * block_size) / (1024 * 1024), 2))
DELETED_MB
FROM V$ARCHIVED_LOG
WHERE ARCHIVED = 'YES' AND DELETED = 'YES'
GROUP BY TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY')) SUM_ARCH_DEL
WHERE SUM_ARCH.DAY = SUM_ARCH_DEL.DAY(+)
ORDER BY TO_DATE (DAY, 'DD/MM/YYYY');
***********************************************************************************
## Date & Time wise log generation.
set pages 1000
select to_char(COMPLETION_TIME,'DD-MM-YYYY hh24:mm') Hour,thread# , round(sum(BLOCKS*BLOCK_SIZE)/1048576) MB,count(*) Archives from v$archived_log
group by to_char(COMPLETION_TIME,'DD-MM-YYYY hh24:mm'),thread# order by 1 ;
***********************************************************************************
## Hour wise archive generation:
SELECT to_date(first_time) DAY,
to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'999') "00",
to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'999') "01",
to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'999') "02",
to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'999') "03",
to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'999') "04",
to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'999') "05",
to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'999') "06",
to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'999') "07",
to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'999') "08",
to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'999') "09",
to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'999') "10",
to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'999') "11",
to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'999') "12",
to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'999') "13",
to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'999') "14",
to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'999') "15",
to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'999') "16",
to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'999') "17",
to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'999') "18",
to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'999') "19",
to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'999') "20",
to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'999') "21",
to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'999') "22",
to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'999') "23", count(*) TOT
from
v$log_history
where to_date(first_time) > sysdate -8
GROUP by
to_char(first_time,'YYYY-MON-DD'), to_date(first_time)
order by to_date(first_time)
/
***********************************************************************************
## Archive generation number with volume: day/hour wise
select trunc(COMPLETION_TIME,'DD') Day, thread#, round(sum(BLOCKS*BLOCK_SIZE)/1048576/1024) GB,count(*) Archives_Generated from v$archived_log group by trunc(COMPLETION_TIME,'DD'), thread# order by 1;
select to_char(COMPLETION_TIME,'DD-MON-YY HH24') Hour, round(sum(BLOCKS*BLOCK_SIZE)/1048576/1024) gb, count(*) Archives from v$archived_log group by to_char(COMPLETION_TIME,'DD-MON-YY HH24') order by 1 ;
***********************************************************************************
## Currently session wise redo generation:
select b.inst_id,
lpad((b.SID || ',' || lpad(b.serial#,5)),11) sid_serial,
b.username,
machine,
b.osuser,
b.status,
a.redo_mb
from (select n.inst_id, sid,
round(value/1024/1024) redo_mb
from gv$statname n, gv$sesstat s
where n.inst_id=s.inst_id
and n.name = 'redo size'
and s.statistic# = n.statistic#
order by value desc
) a,
gv$session b
where b.inst_id=a.inst_id
and a.sid = b.sid
and rownum <= 30
;
***********************************************************************************
## Which segments are generating redo logs:
SELECT to_char(begin_interval_time,'YY-MM-DD HH24') snap_time,
dhso.object_name,
sum(db_block_changes_delta) BLOCK_CHANGED
FROM dba_hist_seg_stat dhss,
dba_hist_seg_stat_obj dhso,
dba_hist_snapshot dhs
WHERE dhs.snap_id = dhss.snap_id
AND dhs.instance_number = dhss.instance_number
AND dhss.obj# = dhso.obj#
AND dhss.dataobj# = dhso.dataobj#
AND begin_interval_time BETWEEN to_date('15-10-31 08:00','YY-MM-DD HH24:MI')
AND to_date('15-10-31 10:00','YY-MM-DD HH24:MI')
GROUP BY to_char(begin_interval_time,'YY-MM-DD HH24'),
dhso.object_name
HAVING sum(db_block_changes_delta) > 0
ORDER BY sum(db_block_changes_delta) desc ;
***********************************************************************************
## What sql was causing the redo log generation:
SELECT to_char(begin_interval_time,'YYYY_MM_DD HH24') WHEN,
dbms_lob.substr(sql_text,4000,1) SQL,
dhss.instance_number INST_ID,
dhss.sql_id,
executions_delta exec_delta,
rows_processed_delta rows_proc_delta
FROM dba_hist_sqlstat dhss,
dba_hist_snapshot dhs,
dba_hist_sqltext dhst
WHERE upper(dhst.sql_text) LIKE '%USR_RACUNI_MV%'
AND ltrim(upper(dhst.sql_text)) NOT LIKE 'SELECT%'
AND dhss.snap_id=dhs.snap_id
AND dhss.instance_number=dhs.instance_number
AND dhss.sql_id=dhst.sql_id
AND begin_interval_time BETWEEN to_date('15-10-31 07:00','YY-MM-DD HH24:MI')
AND to_date('15-10-31 16:00','YY-MM-DD HH24:MI');
***********************************************************************************
## Session wise programe redo entris genrate
col username for a15
col program for a20
col name for a20
select * from
(select /*+ first_rows */ a.username,a.sid,a.program,b.name,c.value
from v$session a,v$statname b,v$sesstat c
where a.sid=c.sid and b.statistic#=c.statistic# and b.name='redo entries' order by c.value desc)
where rownum < 11
;
***********************************************************************************
## For RAC Env.
Author: Riyaj Shamsudeen
spool redosize.log
col begin_interval_time format a30
set lines 160 pages 1000
col end_interval_time format a30
set colsep '|'
alter session set nls_date_format='DD-MON-YYYY';
with redo_sz as (
SELECT sysst.snap_id, sysst.instance_number, begin_interval_time ,end_interval_time , startup_time,
VALUE - lag (VALUE) OVER ( PARTITION BY startup_time, sysst.instance_number
ORDER BY begin_interval_time, startup_time, sysst.instance_number) stat_value,
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)) DELTA
FROM sys.wrh$_sysstat sysst , DBA_HIST_SNAPSHOT snaps
WHERE (sysst.dbid, sysst.stat_id) IN ( SELECT dbid, stat_id FROM sys.wrh$_stat_name WHERE stat_name='redo size' )
AND snaps.snap_id = sysst.snap_id
AND snaps.dbid =sysst.dbid
AND sysst.instance_number=snaps.instance_number
and begin_interval_time > sysdate-90
)
select instance_number,
to_date(to_char(begin_interval_time,'DD-MON-YYYY'),'DD-MON-YYYY') dt
, sum(stat_value) redo1
from redo_sz
group by instance_number,
to_date(to_char(begin_interval_time,'DD-MON-YYYY'),'DD-MON-YYYY')
order by instance_number, 2
/
spool off;
No comments:
Post a Comment