Informix

Expand all | Collapse all

nb of extents ?? dbschema vs oncheck

  • 1.  nb of extents ?? dbschema vs oncheck

    Posted Thu February 04, 2021 03:31 AM

    Hi to All

    Can anyone help me to understand please :) , why extents sizes is differents between table schema and oncheck -pe ?

    1/ pages per extent 

    dbspace with page size = 8k
    table first extent : 32k means 4 pages,
    table next extent : 32k means also 4 pages

    2/ The table

    { TABLE "user".table1 row size = 300 number of columns = 12 index size = 86 }

    create table "user".table1
    (
    record_type char(2),
    fil1 char(1),
    proc_id char(6),
    fil2 char(1),
    inst_name char(50),
    fil3 char(1),
    inst_id char(11),
    fil4 char(1),
    settle_date char(8),
    fil5 char(1),
    bnumber char(4),
    fil6 char(214),
    unique (record_type,proc_id,inst_name,inst_id,settle_date,batch_number) constraint "user".u_table1
    ) in sgb_8k_da extent size 32 next size 32 lock mode page;



    3/ infos with lester sysmaster (2019) query tableinfo_upd.sql

    nb of extents for the table : 17 ,   rows_per_page : 27  (see below)

    so 17 extents (of 32ko according to tabel schema) * 4 pages/extents * 27 rows/page = 1836 rows

    but num_rows according to sysmaster query is 350 202 766 (same as "select count(*) :)" )

    output of sysmaster query:

    database db
    tabname table1
    dbspace dbs_8k_da
    partnum 35651595
    row_size 300
    num_columns 0
    num_indexes 0
    num_extents 17
    page_size 8192
    pages_total 14930142
    pages_used 13470996
    pages_data 13469338
    pages_free 1459146
    num_rows 350202766
    rowfit Row smaller the pagesize
    rows_per_page 27
    free_rows 39396942
    create_date 2021-01-28 14:11:43
    lockreqs 0
    lockwts 0
    deadlks 0
    lktouts 0
    isreads 0
    iswrites 0
    isrewrites 0
    isdeletes 0
    bufreads 51
    bufwrites 0
    seqscans 0
    pagreads 0
    pagwrites 0
    total_io 51
    buff_read_percent 0,00000000000000
    buff_write_percent 0,00000000000000
    total_bytes_scann+ 0


    4/ oncheck -pe (the extent size are not 32k) !

    db:'user'.table1 3224579 1000000
    db:'user'.table1 4097155 2456443
    db:'user'.table1 3 6553596
    db:'user'.table1 3 2097152
    db:'user'.table1 122881 29680
    db:'user'.table1 1020633 7500
    db:'user'.table1 1036325 7500
    db:'user'.table1 3629330 69271
    db:'user'.table1 4205673 125000
    db:'user'.table1 4712817 125000
    db:'user'.table1 5829961 125000
    db:'user'.table1 6457105 250000
    db:'user'.table1 7209249 250000
    db:'user'.table1 8473537 490000
    db:'user'.table1 13704829 448000
    db:'user'.table1 16316665 448000
    db:'user'.table1 20166649 448000

    thanks in advance

    ------------------------------
    John Smith
    ------------------------------


  • 2.  RE: nb of extents ?? dbschema vs oncheck

    Posted Thu February 04, 2021 05:35 AM
    John:

    The reason is extent compression. If a new extent is contiguous with an existing extent they are combined into a single extent. But the next size only doubles after 16 extentsat the current size. 

    Art

    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------



  • 3.  RE: nb of extents ?? dbschema vs oncheck

    Posted Thu February 04, 2021 06:52 AM
    Okay, so the right information is to have with oncheck
    Thanks Art

    ------------------------------
    John Smith
    ------------------------------



  • 4.  RE: nb of extents ?? dbschema vs oncheck

    Posted Thu February 04, 2021 08:50 AM
    John:

    Your query has the correct information as well. 17 extents containing a total of 14,930,142 pages (FWIW: getting close to the 2^24 page limit), 13,469,338 of them are data pages containing 350,202,766 rows. The only thing there that is incorrect are the number of rows per page. Lester's query didn't add the four bytes per row for the slot table entry for the rows so it overestimated the number of rows per page. That should be 26 not 27. And if you do the math:

    350202766 / 13469338 = 25.99999   # Probably some pages are not full.

    Rows per page = (pagesize - 28) / (rowsize + 4) = (8092 - 28) / (300 + 4) = 8064 / 304 = 26.52631


    num_extents 17
    page_size 8192
    pages_total 14930142
    pages_used 13470996
    pages_data 13469338
    pages_free 1459146
    num_rows 350202766
    ​...
    rows_per_page 27

    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------



  • 5.  RE: nb of extents ?? dbschema vs oncheck

    Posted Fri February 05, 2021 06:04 AM
    Yes, 
    Thank you Art :)

    ------------------------------
    John Smith
    ------------------------------