DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Table Segment Block Reuse.

  1. #1
    Join Date
    Nov 2001
    Location
    Sheffield, England, UK
    Posts
    78

    Table Segment Block Reuse.

    We have a few tables in a database that are subjected to INSERTS/DELETES, sometimes have updates - but none which increase row-length. (I.e. They only update dates, or Char(1) Alphabetic flag characters).

    This is a relatively new application, and in order to halt apparent early growth of some of these 'holding' tables, PCTFREE and PCTUSED have been ammended.

    Someone has altered PCTFREE to either 0 or 1, and PCTUSED to 99.

    From this PCTFREE, I would generally assume that ALL or 'almost' ALL of the available data space in each db_block assigned to the segment would be used to insert rows - with no space left over for updates.

    Obviously, from the types of data subjected to updates, I wouldn't imagine we would start to suffer from chained or migrated rows at all, or excessively at least!

    With the high setting of PCTUSED, I would expect that any block having just 1 row deleted, would cause it to come back on the free_list, thus causing high reuse of cleared out blocks.

    Data tends to be purged from these holding tables either daily or weekly - such that generally, they should be holding approximately the same number of rows at any one time. {From investigations this DOES tend to be the case, on the whole.}

    HOWEVER... and now I come to my problem!:

    These tables are now regularly requesting new extents, even though the max row retention within the tables is almost static.

    Can anyone enlighten me as to why this is the case?

    Does Oracle do something strangely anomalous in block/freelist management with such diverse settings for PCTFREE/USED, such that it is performing highly INEFFICIENTLY, when really the aim of setting them to these values was exactly the opposite!?!?

    :-)

    - Tony.

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    The PCTUSED and PCTFREE values seem appropriate to your application -- you might just chack that you are not gettin row chaining, but if the inserted rows fo not grow with update then you should be ok.

    Have you checked the number of rows per block to ensure that they really are filling up the blocks?

    And also, is it possible that there are direct path inserts happening? They would be a prime cause of the table extending as they would not be filling gaps in the table.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Check the INSERT statement.
    Is it like INSERT /*+ APPEND */?

    If it is a batch process, do you have a statement like,
    ALTER SESSION FORRCE PARALLEL DML PARALLEL 6;

    Those 2 above statements will insert new rows after the HWM, that may cause adding new extents to the table.

    Tamil

  4. #4
    Join Date
    Oct 2001
    Location
    Calcutta , India
    Posts
    78
    Why direct path insert will not fill up the gap ? Since it bypasses the memory and not able to know which block is ready to accept the new row .

    Rgards
    Deba

  5. #5
    Join Date
    Oct 2001
    Location
    Calcutta , India
    Posts
    78
    OK . Direct path insert always inserts the data after HWM. Thanks for the reason

    Regards

    Deba

  6. #6
    Join Date
    Nov 2001
    Location
    Sheffield, England, UK
    Posts
    78
    Aha!

    We might have hit something here!

    I believe this application may well use SQL*Loader, which may well have 'APPEND' set somewhere within the control files!

    Will check, and report back!

    Many thanks for that pointer.

    :-)

    - Tony.

  7. #7
    Join Date
    Nov 2001
    Location
    Sheffield, England, UK
    Posts
    78
    Hmmmm.

    Whilst we have several SQL*Loader control files that include APPEND, which may account for several largely oversized tables within the database, it (alas) does not provide the answer for all instances.

    Within one schema we have a set of packages that use UTL_FILE to read in values from files, followed by standard INSERT clause.

    At no point are there any session settings for PARALLEL DML activities within any of the code.

    During the whole of the last 24 hours, I have had a Korn Shell script executing (every hour), which has done table counts, block analysis, extent counts etc on various tables.

    At no point have any of the tables been targets of massive row inserts. Generally, row counts have increased, decreased, increased, decreased cyclically - but at the same time, the objects have been grabbing significant numbers of new extents.

    So, row counts remain relatively static - but extents continue to be allocated to the objects.

    Nothing appears to be running parallel DML, or including any APPEND clauses.

    I'm at a loss now.

    :-(

    Any new suggestions anyone?

  8. #8
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Define max extents for the table.
    One of the scripts may fail when new extent is needed.
    Check the script carefully.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width