Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

High Redo generation - Online index rebuild

Veera_VMar 2 2015 — edited Mar 3 2015

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

MYDATEOWNERSEGMENT_NAMESpace used (MB)Total Object Size (MB)Percent of Total Disk Usage
02/23/15GLPRODPK_ORDER_ITEM12.32837296818.50.02
02/24/15GLPRODPK_ORDER_ITEM9.564842224818.50.02
02/25/15GLPRODPK_ORDER_ITEM1368.188604818.53.1
02/26/15GLPRODPK_ORDER_ITEM20.32518005818.50.03
02/27/15GLPRODPK_ORDER_ITEM7.176231384818.50.01
02/28/15GLPRODPK_ORDER_ITEM1.867542267818.50.01
03/01/15GLPRODPK_ORDER_ITEM1.339572906818.50.01
03/02/15GLPRODPK_ORDER_ITEM2.19827652818.50

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_TIMESUM(DB_BLOCK_CHANGES_DELTA)
2015_02_2399520
2015_02_24278576
2015_02_2536928
2015_02_2664224
2015_02_2763664
2015_02_282896
2015_03_0115920
2015_03_02124000

Regards,

Veera

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Mar 31 2015
Added on Mar 2 2015
7 comments
2,155 views