Oracle Database Discussions

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');

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)

Regards,

Veera