Conference orarep::nomahs::dec_data_distributor

            <<< NOMAHS::DISK$NOMAHS1:[NOTES$LIBRARY]RDB_60.NOTE;1 >>>
       -< Oracle Rdb - Still a strategic database for DEC on Alpha AXP! >-
================================================================================
Note 2481.6   RDB$CHANGES causing RDB$SYSTEM to grow out of control       6 of 6
BROKE::BITHER                                        18 lines  22-OCT-1996 11:15
                  -< Customer considers v7 feature a bandaid >-
--------------------------------------------------------------------------------
Hi,

This customer is calling again re: the same problem.  I've sent them
the v7 release notes which specify that they can place the RDB$CHANGES
table into a storage area other than RDB$SYSTEM and they consider this
feature a bandaid and still want the problem with RDB$CHANGES growing and 
not using free space fixed.

They do NOT use DBKEY SCOPE ATTACH.
Users are sometimes attached to the database as long as 24 hrs at a
 time but not any longer.

I'll x-post this in DDAL notes.  

What can I tell the customer?

Thanks, Diane

    
    Having just reread the replies in the original note in the RDB_60
    conference, I see little that we can do within the Replication
    Option itself.  The one area that we do have control of is the size of
    the 10,000 byte VARCHAR column in the RDB$CHANGES table.  I haven't
    discussed with the Rdb engineers the possibility of us making that
    column size be customer-specifiable.  I'll add that to a wish list I
    maintain.  I can't promise when this question will come up and be
    addressed; it always depends on what other issues of compelling
    importance also exist.
    
    I don't know enough about the internals of Rdb to know why it is that
    one has to detach and reattach to a database to get earlier pages in
    the RDB$CHANGES table examined.  I realize that is how things are
    implemented today.  What I don't know is if it would (a) be possible to
    tell Rdb to reset its sight at the beginning of a table when looking
    for a place to insert a new row (would mean a change in Rdb, assuming
    it is doable), and (b) what side effects doing so might have.  I'll try
    to remember to discuss this with the Rdb engineers, but again I cannot
    promise you when.
    
    Claude

I suggest the customer actually try Rdb7.  The algorithm to reuse free space
has been revised in Rdb7.  Have them install multiversion and test it.

~The one area that we do have control of is the size of the 10,000 byte
~VARCHAR column in the RDB$CHANGES table.  I haven't discussed with the Rdb
~engineers the possibility of us making that column size be
~customer-specifiable. 

Claude (and others) this size is already customer specifiable.  The
VARCHAR(10000) is not really used.  We instead size the RDB$CHANGES rows
according to the page size.  So change the page size and you change the row
size.  In Rdb7 you can map the RDB$CHANGES table (and others) to their own
storage area with their own page sizes.

I am not sure we could be more flexible than that...

enjoy,

Ian

    
    I take the opportunity to ask since the topic is up:
    
    Currently (6.0) we experience degrading insert performance in 
    RDB$CHANGES over time. The RMU-screens states that it's 
    something to do with SPAMs, since the record store vs pages checked
    ratio is bad.
    Has anything changed in V7.0 to handle this problem ?
    What can be done in 6.1 (except repair/spams and repair/abm) ?
    
    Stefan
    

The AIP length is much larger than the stored rows, so Rdb is probably going
to assume all rows will be fragmented.  You might like to trim the AIP length
to a reasonable value using RMU/REPAIR (reset the logical area length).  Make
sure you take a FULL backup in case this doesn't work...  In Rdb7 you can
actually get the RDMS$DEBUG_FLAGS "J" to give some information on the actual
row length used...

In addition any indices on RDB$CHANGES will share the same logical area in
RDB$SYSTEM.  I am not familiar with the indices themselves but this may be a
problem also.

Ian

    
    Ian,
    when the compressed record is stored how does RDB decide if the page is
    to be set to threhold 3 ? Doesn't that involve the nominal record size,
    (in our case a size larger than the page size) ? This would  mean that
    as soon as a record is stored in rdb$changes tha page should be marked
    as full. 
    Could You clearify please !
    
    Stefan

~                 -< Is rdb$changes handled in a special way ? >-

No it is handled just like any other row being compressed.
    
~    when the compressed record is stored how does RDB decide if the page is
~    to be set to threhold 3 ?

It uses the thresholds!

~Doesn't that involve the nominal record size, (in our case a size larger than
~the page size) ?

It never uses the nominal record size when it exceeds the MAX_FREE_LEN on a
page, instead it uses the MAX_NEW_SEG_LEN to calculate the thresholds values.
It uses the actual stored length to determine if this store crossed a
threshold.

~This would  mean that as soon as a record is stored in
~rdb$changes tha page should be marked as full.  Could You clearify please !

To clarify, the thresholds are set pretty high for this logical area (0,0,0)
translates internally to something like (99,99,99) [this depends on the page
size].  So I think that the pages are rarely set to full.  I am still
investigating.

cheers,

Ian

~I am still investigating.

Well I have found that the default thresholds for RDB$CHANGES are not
appropriate.  I will work with Claude and the ROR team to make change to
correct this problem.  In the meantime I encourage people to define logical
area thresholds so that the pages checked versus stored ration is better.
This can be calculated when you know that the stored length of RDB$CHANGES
will always be less than the MAX_NEW_SEG_LEN for the storage area.

I have also posted a technical article describing the new locked free space
algorithm.  Please feel free to give it to interested customers.

thanks,

Ian