Professional Web Applications Themes

# Pages used in tablespace for index - IBM DB2

Small question, if my runstats are up to date, can I then determine how many pages a certain index is using by looking at the NLEAF value for that index in syscat.indexes? This is on UDB 7.2 if it makes a difference. If this is not the method on how to determine how many pages are used, what is? Thanks....

  1. #1

    Default # Pages used in tablespace for index

    Small question, if my runstats are up to date, can I then determine how many
    pages a certain index is using by looking at the NLEAF value for that index
    in syscat.indexes?
    This is on UDB 7.2 if it makes a difference.

    If this is not the method on how to determine how many pages are used, what
    is?

    Thanks.


    Erik Hendrix Guest

  2. #2

    Default Re: # Pages used in tablespace for index

    "Erik Hendrix" <hendrix_erikhotmail.com> wrote in message
    news:a5b008e6d7232371ad5a306c3c381ce5free.teranew s.com...
    > Small question, if my runstats are up to date, can I then determine how
    many
    > pages a certain index is using by looking at the NLEAF value for that
    index
    > in syscat.indexes?
    > This is on UDB 7.2 if it makes a difference.
    >
    > If this is not the method on how to determine how many pages are used,
    what
    > is?
    >
    > Thanks.
    >
    NLEAF will be an approximate number of pages. But there are also non-leaf
    pages that are used to point to the leaf pages. This is how DB2 uses the
    B-tree to find the exact index entry with the least number of I/O's.

    In a three level index (probably most moderate to large tables have a 3
    level index), there is the root page on the first level plus multiple
    non-leaf pages on the second level (the leaf pages are on the 3rd level).
    The total number of non-leaf pages obviously depends on the size of the
    index, but I would estimate that there are usually about 10 or less non-leaf
    pages on a an average 3 level index.

    If the number of index levels (NLEVELS) is 4, the number of non-leaf pages
    is probably 15 or more. If the number of levels is 2, there is only 1
    non-leaf page (the root page).


    Mark A Guest

  3. #3

    Default Re: # Pages used in tablespace for index


    "Erik Hendrix" <hendrix_erikhotmail.com> wrote in message
    news:a5b008e6d7232371ad5a306c3c381ce5free.teranew s.com...
    > Small question, if my runstats are up to date, can I then determine how
    many
    > pages a certain index is using by looking at the NLEAF value for that
    index
    > in syscat.indexes?
    > This is on UDB 7.2 if it makes a difference.
    >
    > If this is not the method on how to determine how many pages are used,
    what
    > is?
    >
    NLEAF is not the size of the index, it is the number of leaf pages in that
    index. Indexes use B-trees and the leaf pages are the pages at the top of
    the B-tree. There can also be a much larger number of non-leaf pages in the
    index so you must take those into account as well when determining the size
    of the index. In addition, the person who created the index may have
    established a high percentage of free space in the index to allow for new
    inserts into the underlying table.

    One way to determine the approximate size of an index is to use the Control
    Center. I just did the following to determine the size of one of the indexes
    in my system:
    - I went to one of the databases in my system and clicked on the + sign
    beside its name
    - on the tree which appeared below the database name, I clicked on the
    Indexes folder
    - on the list that appeared in the right hand pane, I selected the first
    index, IBM125, which is an index on SYSIBM.SYSWRAPPERS, and right-clicked
    for a context menu. I chose the "Estimate size..." option.
    - on the resulting dialog, I went to the "New total number of rows" field
    and typed in 50000. (I left the average row length alone but you can change
    it if you think the current value is no longer accurate.) Near the bottom of
    the dialog, I chose units of Pages from the "Display size in units of" drop
    down. The table in the center of the dialog changed to say that the
    estimated size of the table was 4999 pages and that the estimated size of
    the index was 1922 pages. It also showed minimum and maximum sizes for both
    the index and the table.

    There are probably queries you can run in the Command Center or from the DB2
    command line if that's what you'd prefer. I don't know these queries offhand
    but I expect that you'll find them in the DB2 manuals for your system,
    especially the Administration Guide.

    Rhino


    Rhino Guest

  4. #4

    Default Re: # Pages used in tablespace for index

    Thanks. Sadly enough we can not use the Control Center since we are running
    in 64-bit.
    When looking through the manual, I could only find some method on how to
    roughly calculate the size of a index.
    I just find it weird that DB2 does not seem to know how many pages a certain
    index is using. Or how many extents are assigned to this index. I would
    think that that would be something DB2 keeps track off.

    So if anyone has a query statement I would be able to run I would really
    appreciate it.

    Thanks.

    "Rhino" <rhino1NOSPAM.sympatico.ca> wrote in message
    news:Oe2Ma.2194$eF3.247906news20.bellglobal.com.. .
    >
    > "Erik Hendrix" <hendrix_erikhotmail.com> wrote in message
    > news:a5b008e6d7232371ad5a306c3c381ce5free.teranew s.com...
    > > Small question, if my runstats are up to date, can I then determine how
    > many
    > > pages a certain index is using by looking at the NLEAF value for that
    > index
    > > in syscat.indexes?
    > > This is on UDB 7.2 if it makes a difference.
    > >
    > > If this is not the method on how to determine how many pages are used,
    > what
    > > is?
    > >
    > NLEAF is not the size of the index, it is the number of leaf pages in that
    > index. Indexes use B-trees and the leaf pages are the pages at the top of
    > the B-tree. There can also be a much larger number of non-leaf pages in
    the
    > index so you must take those into account as well when determining the
    size
    > of the index. In addition, the person who created the index may have
    > established a high percentage of free space in the index to allow for new
    > inserts into the underlying table.
    >
    > One way to determine the approximate size of an index is to use the
    Control
    > Center. I just did the following to determine the size of one of the
    indexes
    > in my system:
    > - I went to one of the databases in my system and clicked on the + sign
    > beside its name
    > - on the tree which appeared below the database name, I clicked on the
    > Indexes folder
    > - on the list that appeared in the right hand pane, I selected the first
    > index, IBM125, which is an index on SYSIBM.SYSWRAPPERS, and right-clicked
    > for a context menu. I chose the "Estimate size..." option.
    > - on the resulting dialog, I went to the "New total number of rows" field
    > and typed in 50000. (I left the average row length alone but you can
    change
    > it if you think the current value is no longer accurate.) Near the bottom
    of
    > the dialog, I chose units of Pages from the "Display size in units of"
    drop
    > down. The table in the center of the dialog changed to say that the
    > estimated size of the table was 4999 pages and that the estimated size of
    > the index was 1922 pages. It also showed minimum and maximum sizes for
    both
    > the index and the table.
    >
    > There are probably queries you can run in the Command Center or from the
    DB2
    > command line if that's what you'd prefer. I don't know these queries
    offhand
    > but I expect that you'll find them in the DB2 manuals for your system,
    > especially the Administration Guide.
    >
    > Rhino
    >
    >

    Erik Hendrix Guest

  5. #5

    Default Re: # Pages used in tablespace for index

    This is probably the same information you found in the manual yourself but
    I'm posting it here just in case you saw something else. Even though the
    text describes this as an estimate, it seems to me that it would be accurate
    for indexes based on fixed length keys, which is what most people probably
    use. It would likely be a less accurate number if variable length keys were
    the basis of the index but that's because they can't predict the
    distribution of key length widths in your data.

    In fact, It wouldn't suprise me to hear that this is exactly the formula
    that gets used in the Control Center calculation which I mentioned in my
    earlier post, although you'd have to ask the lab folks to be sure....

    ----------------------------------------------------------------------------
    -------------------------------
    Index Space
    For each index, the space needed can be estimated as:

    (average index key size + 8) * number of rows * 2
    where:

    a.. The "average index key size" is the byte count of each column in the
    index key. Refer to the CREATE TABLE statement in the SQL Reference for
    information on how to calculate the byte count for columns with different
    data types. (When estimating the average column size for VARCHAR and
    VARGRAPHIC columns, use an average of the current data size, plus one byte.
    Do not use the maximum declared size.)
    b.. The factor of "2" is for overhead, such as non-leaf pages and free
    space.
    Note: For every column that allows NULLs, add one extra byte for the
    null indicator.

    Temporary space is required when creating the index. The maximum amount of
    temporary space required during index creation can be estimated as:

    (average index key size + 8) * number of rows * 3.2
    where the factor of "3.2" is for index overhead, and space required for
    sorting during index creation. Note: In the case of non-unique indexes, only
    four bytes are required to store duplicate key entries. The estimates shown
    above assume no duplicates. The space required to store an index may be
    over-estimated by the formula shown above.


    The following two formulas can be used to estimate the number of leaf pages
    (the second provides a more accurate estimate). The accuracy of these
    estimates depends largely on how well the averages reflect the actual data.
    Note: For SMS, the minimum required space is 12 KB. For DMS, the minimum is
    an extent.


    a.. A rough estimate of the average number of keys per leaf page is:
    (.9 * (U - (M*2))) * (D + 1)
    ----------------------------
    K + 6 + (4 * D)
    where:
    a.. U, the usable space on a page, is approximately equal to the page
    size minus 100. For a page size of 4096, U is 3996.
    b.. M = U / (8 + minimumKeySize)
    c.. D = average number of duplicates per key value
    d.. K = averageKeySize
    Remember that minimumKeySize and averageKeysize must have an extra byte
    for each nullable key part, and an extra byte for the length of each
    variable length key part.

    If there are include columns, they should be accounted for in
    minimumKeySize and averageKeySize.

    The .9 can be replaced by any (100 - pctfree)/100 value, if a percent free
    value other than the default value of ten percent was specified during index
    creation.

    b.. A more accurate estimate of the average number of keys per leaf page
    is:
    L = number of leaf pages = X / (avg number of keys on leaf page)
    where X is the total number of rows in the table.
    You can estimate the original size of an index as:

    (L + 2L/(average number of keys on leaf page)) * pagesize
    For DMS table spaces, add together the sizes of all indexes on a table, and
    round up to a multiple of the extent size for the table space on which the
    index resides.

    You should provide additional space for index growth due to INSERT/UPDATE
    activity, which may result in page splits.

    Use the following calculations to obtain a more accurate estimate of the
    original index size, as well as an estimate of the number of levels in the
    index. (This may be of particular interest if include columns are being used
    in the index definition.) The average number of keys per non-leaf page is
    roughly:

    (.9 * (U - (M*2))) * (D + 1)
    ----------------------------
    K + 12 + (8 * D)
    where:

    a.. U, the usable space on a page, is approximately equal to the page
    size minus 100. For a page size of 4096, U is 3996.
    b.. D is the average number of duplicates per key value on non-leaf
    pages (this will be much smaller than on leaf pages, and you may want to
    simplify the calculation by setting the value to 0).
    c.. M = U / (8 + minimumKeySize for non-leaf pages)
    d.. K = averageKeySize for non-leaf pages
    The minimumKeySize and the averageKeySize for non-leaf pages will be the
    same as for leaf pages, except when there are include columns. Include
    columns are not stored on non-leaf pages.

    You should not replace .9 with (100 - pctfree)/100, unless this value is
    greater than .9, because a maximum of 10 percent free space will be left on
    non-leaf pages during index creation.

    The number of non-leaf pages can be estimated as follows:

    if L > 1 then {P++; Z++}
    While (Y > 1)
    {
    P = P + Y
    Y = Y / N
    Z++
    }
    where:

    a.. P is the number of pages (0 initially).
    b.. L is the number of leaf pages.
    c.. N is the number of keys for each non-leaf page.
    d.. Y = L / N
    e.. Z is the number of levels in the index tree (1 initially).
    Total number of pages is:

    T = (L + P + 2) * 1.0002
    The additional 0.02 percent is for overhead, including space map pages.

    The amount of space required to create the index is estimated as:

    T * pagesize
    ----------------------------------------------------------------------------
    -------------------------------

    Rhino

    "Erik Hendrix" <hendrix_erikhotmail.com> wrote in message
    news:35f7e83518f5eb0153dac64a3040aa78free.teranew s.com...
    > Thanks. Sadly enough we can not use the Control Center since we are
    running
    > in 64-bit.
    > When looking through the manual, I could only find some method on how to
    > roughly calculate the size of a index.
    > I just find it weird that DB2 does not seem to know how many pages a
    certain
    > index is using. Or how many extents are assigned to this index. I would
    > think that that would be something DB2 keeps track off.
    >
    > So if anyone has a query statement I would be able to run I would really
    > appreciate it.
    >
    > Thanks.
    >

    Rhino Guest

Similar Threads

  1. Server-Side Pings and Index Pages
    By deane_b in forum Macromedia Contribute General Discussion
    Replies: 1
    Last Post: February 11th, 12:24 AM
  2. Index, Tablespace and performance
    By alexandre::aldeia digital in forum PostgreSQL / PGSQL
    Replies: 0
    Last Post: January 2nd, 10:43 PM
  3. Is there an index to the perl man pages?
    By Yehuda Berlinger in forum PERL Miscellaneous
    Replies: 1
    Last Post: August 25th, 12:04 AM
  4. Replies: 2
    Last Post: July 11th, 08:34 AM
  5. Replies: 11
    Last Post: January 9th, 07:46 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139