Conference orarep::nomahs::rdb_60

Mary Ann,

Just a WAG, but you don't have unallocated clumps early in the physical 
area do you?

G'day,

Mark.

    Don't think so!  What's an "unallocated clump"?  According to the dump
    I'm looking at, all clumps are allocated to indexes.  (Uniform area
    used to store only sorted indexes.)
    
    I just got off the phone with Jay in support and several things are
    bugging me.  One is that my brand new just created last night under
    V6.1.1 (is that right, rcs?) unique index shows up as length of 215 in
    AIP.  The node size is 430, all the pages have 254 bytes free - guess
    what?
    
    The other thing that is bugging me is that I'm seeing lots of threshold
    1s in my uniform area.  I thought uniform pages were either full (3) or
    not full (0).  
    
    What seems to be happening is that when I use the syntax straight from
    the GDBPT to solve the problem for duplicate indices, it's taking that
    threshold value and using it as threshold 1 rather than 3.
    
    I don't have the energy to try to construct a whole bunch of tests
    tonight, but it sure seems to me like this behaviour has changed
    somewhere along the way.  
    
    I've dumped a lot of pages (even those from last week - note #5089) and
    I don't remember seeing threshold 1 in a uniform area before!
    
    Mary Ann - who really needs the week-end!!!

    >I just got off the phone with Jay in support and several things are
    >bugging me.
    
    	A common enough occurance.  :-)

    Shame on you!  I knew I was making a mistake trying to note after
    working 43 hours in 2 1/2 days!!!  But you could have let me get by!
    B-)
    
    Jay was VERY helpful AND very sympathetic!!
    
    MAH

~    bugging me.  One is that my brand new just created last night under
~    V6.1.1 (is that right, rcs?) unique index shows up as length of 215 in
~    AIP.  The node size is 430, all the pages have 254 bytes free - guess
~    what?

How did you define the index?  DId you actually say NODE SIZE 430?  Or did you
let it default?  In Rdb7 we will default to 430 (or something appropriate) for
UNIQUE indices.  In previous versions we only used the node size if you
specified it.

Now if you did say NODE SIZE 430 then I want to see the script in a BUG report
to understand why it wasn't applied.
    
~    The other thing that is bugging me is that I'm seeing lots of threshold
~    1s in my uniform area.  I thought uniform pages were either full (3) or
~    not full (0).  

If you define THRESHOLDS then they can be 0,1,2 or 3.  If you don't (i.e. the
thresholds in rmu/dump/larea=rdb$aip are (0,0,0) then the page is either empty
0 or full 3.
   
~    What seems to be happening is that when I use the syntax straight from
~    the GDBPT to solve the problem for duplicate indices, it's taking that
~    threshold value and using it as threshold 1 rather than 3.

What is the syntax you are using?  For instance THRESHOLDS (70) is the same as
(70,100,100)
    
Ian

                  <<< Note 5113.4 by ORAREP::ODIXIE::HODGES >>>
                            -< Yes you COULD have! >-
    
>>    Jay was VERY helpful AND very sympathetic!!
    
Mary Ann, that's part of his cover.

:-).

    
>>>    Jay was VERY helpful AND very sympathetic!!
    
>Mary Ann, that's part of his cover.

>:-).

I thought Jay's philosophy is "The customer's always right." 

Gotta keep those customer satisfactions scores high.

    So, now I'm confused... which is pretty easy to do these days...
    
    Under what circumstances would you want to set thresholds 1 and 2 for a
    uniform area?
    
    Thanks, 
    
    John

~    Under what circumstances would you want to set thresholds 1 and 2 for a
~    uniform area?

Thresholds are used to manage different sized records for a page.  e.g. when a
page is too full for a large record you want it marked so, but also have it
marked so that a smaller record can be stored.

So when would you have different sized records for a page in a uniform area?

	- a SORTED INDEX which allows duplicates

		the btree node and the duplicate node are different sizes

	- a table which is compressed

		different rows have different compressions

	- segmented strings

		different segments have different sizes

I guess it is time I finished my Thresholds Technical Report :-)
Ian

    Yes, PLEASE finish that report!!!!  I've read the documentation on this
    so many times I can quote it from memory and all of a sudden the
    results I'm getting aren't the same results I've gotten in the past!!!
    
    The documentation, GDBPT page 3-126 & 127, clearly implies that 
    	
    	(threshold is (75)) 
    
    will cause the page to be marked as full at 75%.  To me, that says 75
    becomes the threshold 3 not the threshold 1.
    
    Regarding the node size, I think we only specify it on duplicate nodes. 
    Again, there was nothing that I saw in the documentation to imply that
    it wasn't going to use the REAL node size for a UNIQUE index under V6.*
    
    So, yes PLEASE finish that article!!!  I'll even proof read it for
    you!!!
    
    Thanks,
    Mary Ann

~    The documentation, GDBPT page 3-126 & 127, clearly implies that 
~    	
~    	(threshold is (75)) 
~    
~    will cause the page to be marked as full at 75%.  To me, that says 75
~    becomes the threshold 3 not the threshold 1.

This is wrong (and fixed in Rdb7 I believe - if not please submit a
documentation BUG report).  This is the same as saying (75, 100, 100).  This
basically turns off the FULL setting because the page must have zero free
space to be marked as full.  Instead you want (n,m,75). e.g. (75,75,75). 
Choosing good values for n and m may help in other cases.

Here is the corrections I sent to the documentation folks following a QAR:

The QAR deals with the THRESHOLDS [ IS | ARE ] ( val1 [, val2 [, val3]] )
syntax as described in the SQL Reference manual.


In CREATE STORAGE AREA there needs to be added these sentences:

    When only val1 is specified then this is equivalent to (val1, 100, 100).
    When only val1 and val2 are specified then this is equivalent to (val1,
    val2, 100).  i.e. the trailing unspecified thresholds will default 100%

Replace this sentence:

    If no thresholds are specified for the area then they will default to
    (70,85,95).

** note that the default thresholds have been wrong in the docs for a long
time as (70,85,90).


In CREATE STORAGE MAP there needs to be added these sentences:

    When only val1 is specified then this is equivalent to (val1, 100, 100).
    When only val1 and val2 are specified then this is equivalent to (val1,
    val2, 100).  i.e. the trailing unspecified thresholds will default 100%

Replace this sentence:

    If no thresholds are specified for the area then they will default to
    (0,0,0).  This will cause the SPAM algorithm to set thresholds based
    on the nominal record length for the logical area.  i.e. the NODE SIZE
    for the index, or the uncompressed length of the table row for a table.

Add this paragraph

    You cannot specify the THRESHOLDS storage map attribute for any area which
    is MIXED format.  Instead set the THESHOLDS for the storage area on the ADD
    or CREATE STORAGE AREA clause of ALTER and CREATE DATABASE, or IMPORT.

CREATE INDEX references the text from CREATE STORAGE MAP, however, it may be
worth duplicating this last paragraph in CREATE INDEX also.

    Thanks Ian.  I don't have V7 docs at home so I'll check this out when I
    get back to Houston tomorrow and log a BUG if the V7 docs don't clear
    things up for me!
    
    I think I understand now.  I need to specifically set the node size on 
    unique indices and I need to specify all 3 thresholds on non-unique 
    indices.  
    
    Gee, it sounds so simple to have caused me so much grief the last 2 weeks!!
    
    
    Thanks again,
    Mary Ann