ASSM Segment Extends in RAC Even if There is Free Space

(Doc ID 1601444.1)

Last updated on OCTOBER 10, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.3 to 11.2.0.3 [Release 10.2 to 11.2]
Information in this document applies to any platform.
***Checked for relevance on 01-Sep-2017***

Symptoms

ASSM segments may extend in RAC more than necessary under concurrent insert/update workloads.


dbms_space.space_usage output shows that unformatted and fs4 blocks were  increasing.
 .
  Time       part_name         unformatted  fs1   fs2   fs3    fs4    full  
 ----------+------------------+-----------+-----+-----+-----+-------+-------+
  15:03:14 | PT_00199_R200808 |      1984 |  19 |  21 |  31 |   475 |  1500 |
  15:23:12 | PT_00199_R200808 |      2528 |  19 |  26 |  37 |  1581 |  1855 |
  15:39:14 | PT_00199_R200808 |      3224 |  22 |  39 |  55 |  2618 |  2128 |
  15:51:10 | PT_00199_R200808 |      4608 |  29 |  45 |  60 |  3051 |  2333 |
  16:01:14 | PT_00199_R200808 |      5800 |  27 |  56 |  59 |  3712 |  2512 |
    :
  19:35:10 | PT_00199_R200808 |     44048 |  82 | 222 | 222 | 20609 |  6143 |
  19:41:19 | PT_00199_R200808 |     45304 |  83 | 226 | 232 | 21275 |  6246 |
  19:49:20 | PT_00199_R200808 |     46640 |  85 | 238 | 235 | 21829 |  6379 |
  19:59:14 | PT_00199_R200808 |     47960 |  89 | 242 | 237 | 22369 |  6549 |
  20:05:13 | PT_00199_R200808 |     49280 |  91 | 246 | 235 | 22981 |  6653 |


  From the Bitmap dump we can observe following points:
  - Number of full extents increase over time on both instances.   It seems there are more activities from inst1.
  - Number of free extents that has affinity with inst1 increases while free extents from inst2 decreases. Inst1 is stealing
   a couple of extents from inst2.

 

 

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms