atyoical table extents

  My application experienced an unusual problem. We have two indexes that   point to a very large table. On the problematic evening in question,   each index atypically extended twice (20M per extent).

  Our application runs on a sparc1000 under Solaris 2.3. We are running   Oracle 7.1.6. We have one large table, approx 8 million rows.   Typically, it grows by 60,000 per night. The two indexes in question   point to this table. Activity is mostly inserts, a few updates, even   fewer deletes. Our indexes were growing at a steady rate until this   pheonomena.

  When this problem occurred, in addition to the indexes extending, we   generated 60 archive log files, (we usually have 24). A graph of our   disk i/o normal shows a spike followed by a steady decline over a 3 hour   period. In contrast, when this problem occurred, the graph shows a spike   that did not start to decline for four hours, quite a heavy performance   hit. The following is our ddl for one of the indexes.

  create index "daily_uv_date"
  on "valu_daily_unit_values" (as_of_date)   pctfree 5
  initrans 10
  maxtrans 255
  storage
  (initial 513M
  next 20M
  minextents 1
  maxextents 121
  pctincrease 0
  freelists 1)

  Note: We had dropped and recreated the two indexes a week prior to this.

  My id is dandret_at_nationwide.com

  I've started running an analyze daily and storing the info from   index_stats to help in future occurences.

  Any ideas would be appreciated! Received on Tue Mar 26 1996 - 00:00:00 CET