Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2011
    Location
    Mumbai,India
    Posts
    49

    Unanswered: health indicator showing incorrect output.

    Hi,

    On sunday i have done offline reorg of table.

    Monday morning when i took database health snapshot it was showing me msg "REORG TABLE INDEX"

    Indicator Name = db.tb_reorg_req
    Value = 1
    Evaluation timestamp = 10/04/2011 10:29:41.058715
    Alert state = Attention

    Collection:

    Name = "SAMPLE"."TEST"
    Detail = REORG TABLE INDEX
    State = Attention
    Evaluation timestamp = 10/03/2011 08:53:37.000000


    Do i need to reorg index also after reorging table??? Is it mandatory??

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    reorg table will reorg data and index
    check status with reorgchk and see which indicator is on
    might be clusterratio.. and this index is not the clustering index..
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    Aug 2011
    Location
    Mumbai,India
    Posts
    49
    Ok... What should i do now??

  4. #4
    Join Date
    Aug 2011
    Location
    Mumbai,India
    Posts
    49
    Table statistics:

    F1: 100 * OVERFLOW / CARD < 5
    F2: 100 * (Effective Space Utilization of Data Pages) > 70
    F3: 100 * (Required Pages / Total Pages) > 80

    SCHEMA.NAME CARD OV NP FP ACTBLK TSIZE F1 F2 F3 REORG
    ----------------------------------------------------------------------------------------
    Table: SAMPLE.TEST
    4320110 0 290743 290743 - 1.13e+09 0 96 100 ---
    ----------------------------------------------------------------------------------------

    Index statistics:

    F4: CLUSTERRATIO or normalized CLUSTERFACTOR > 80
    F5: 100 * (Space used on leaf pages / Space available on non-empty leaf pages) > MIN(50, (100 - PCTFREE))
    F6: (100 - PCTFREE) * (Amount of space available in an index with one less level / Amount of space required for all keys) < 100
    F7: 100 * (Number of pseudo-deleted RIDs / Total number of RIDs) < 20
    F8: 100 * (Number of pseudo-empty leaf pages / Total number of leaf pages) < 20

    SCHEMA.NAME INDCARD LEAF ELEAF LVLS NDEL KEYS LEAF_RECSIZE NLEAF_RECSIZE LEAF_PAGE_OVERHEAD NLEAF_PAGE_OVERHEAD F4 F5 F6 F7 F8 REORG
    --------------------------------------------------------------------------------------------------------------------------------------------------------------
    Table: SAMPLE.TEST
    Index: SAMPLE.PK_T_EMAIL
    4320110 53073 0 4 0 4320110 32 32 628 628 28 91 9 0 0 *----
    --------------------------------------------------------------------------------------------------------------------------------------------------------------

    CLUSTERRATIO or normalized CLUSTERFACTOR (F4) will indicate REORG is necessary
    for indexes that are not in the same sequence as the base table. When multiple
    indexes are defined on a table, one or more indexes may be flagged as needing
    REORG. Specify the most important index for REORG sequencing.

    Tables defined using the ORGANIZE BY clause and the corresponding dimension
    indexes have a '*' suffix to their names. The cardinality of a dimension index
    is equal to the Active blocks statistic of the table.

  5. #5
    Join Date
    Aug 2011
    Location
    Mumbai,India
    Posts
    49
    F4: CLUSTERRATIO or normalized CLUSTERFACTOR > 80

    Currently CLUSTERRATIO is 28.

    Reorganizing the index SAMPLE.PK_T_EMAIL will help me resolve this???

  6. #6
    Join Date
    Aug 2011
    Location
    Mumbai,India
    Posts
    49
    SAMPLE.PK_T_EMAIL is a regular index. How can i increase its CLUSTERRATIO ??

  7. #7
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

Posting Permissions

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