Hi ,
I have noticed that past 25th feb night excessive redo logs generated on db b/w 18-21:00 hrs.
I verified archived logs counts high (normaly 10-20 logs will generate per hour but during the index rebuild 200 logs genereated from each thread# per hour)
I need to confirm rego generation was high due to online index rebuild.(it was a one time rebuild request after few months)
To verify I ran below query:
DB version: 11.2.0.3
2 Node Rac
SELECT * FROM (
SELECT TO_CHAR(END_INTERVAL_TIME, 'MM/DD/YY') MYDATE,
C.OWNER,C.SEGMENT_NAME, SUM(SPACE_USED_DELTA) / 1024 / 1024 "Space used (MB)",
AVG(C.BYTES) / 1024 / 1024 "Total Object Size (MB)",
ROUND(SUM(SPACE_USED_DELTA) / SUM(C.BYTES) * 100, 2) "Percent of Total Disk Usage"
FROM
DBA_HIST_SNAPSHOT SN,
DBA_HIST_SEG_STAT A,
DBA_OBJECTS B,
DBA_SEGMENTS C
WHERE BEGIN_INTERVAL_TIME > TRUNC(SYSDATE)- 7
AND SN.SNAP_ID = A.SNAP_ID
AND B.OBJECT_ID = A.OBJ#
AND B.OWNER = C.OWNER
AND B.OBJECT_NAME = C.SEGMENT_NAME
AND C.SEGMENT_NAME = 'PK_ORDER_ITEM'
GROUP BY TO_CHAR(END_INTERVAL_TIME, 'MM/DD/YY'),C.OWNER,C.SEGMENT_NAME)
ORDER BY TO_DATE(MYDATE, 'MM/DD/YY');
MYDATE | OWNER | SEGMENT_NAME | Space used (MB) | Total Object Size (MB) | Percent of Total Disk Usage |
02/23/15 | GLPROD | PK_ORDER_ITEM | 12.32837296 | 818.5 | 0.02 |
02/24/15 | GLPROD | PK_ORDER_ITEM | 9.564842224 | 818.5 | 0.02 |
02/25/15 | GLPROD | PK_ORDER_ITEM | 1368.188604 | 818.5 | 3.1 |
02/26/15 | GLPROD | PK_ORDER_ITEM | 20.32518005 | 818.5 | 0.03 |
02/27/15 | GLPROD | PK_ORDER_ITEM | 7.176231384 | 818.5 | 0.01 |
02/28/15 | GLPROD | PK_ORDER_ITEM | 1.867542267 | 818.5 | 0.01 |
03/01/15 | GLPROD | PK_ORDER_ITEM | 1.339572906 | 818.5 | 0.01 |
03/02/15 | GLPROD | PK_ORDER_ITEM | 2.19827652 | 818.5 | 0 |
I found that 25th feb 1.3 GB space used. (SEGMENT=INDEX)
I ran for above query for TABLE and INDEX segments for the tables under this schema. Only INDEX segments deviation is high on 25th feb comparing other days.
I gone through few sites and noted that to identify rego generation, db_block_changes_delta column is used .So I ran below query and results similar for all the dates.
Please advise why it shows and I need to look any other information.
SELECT to_char(begin_interval_time,'YYYY_MM_DD') snap_time,
SUM(db_block_changes_delta)
FROM dba_hist_seg_stat dhss,
dba_hist_seg_stat_obj dhsso,
dba_hist_snapshot dhs
WHERE dhs.snap_id = dhss.snap_id
AND dhs.instance_number = dhss.instance_number
AND dhss.obj# = dhsso.obj#
AND dhss.dataobj# = dhsso.dataobj#
AND dhsso.object_name = 'PK_ORDER_ITEM'
GROUP BY to_char(begin_interval_time,'YYYY_MM_DD')
ORDER By snap_time
below shows 24th block changes high but 25th index rebuild happened. For a nother table all week db block changes for INDEX segment is similar size)
SNAP_TIME | SUM(DB_BLOCK_CHANGES_DELTA) |
2015_02_23 | 99520 |
2015_02_24 | 278576 |
2015_02_25 | 36928 |
2015_02_26 | 64224 |
2015_02_27 | 63664 |
2015_02_28 | 2896 |
2015_03_01 | 15920 |
2015_03_02 | 124000 |
Regards,
Veera