Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2007
    Posts
    27

    Unanswered: Question regarding a query

    Hi All,
    db2 V8.2fp14
    OS: AIX

    i have a question regarding the below query

    SELECT m.value ,
    m.unitTypeForValue ,
    m.measurementSeq ,
    m.classType ,
    m.pipelineRunDate ,
    m.name
    FROM UDB.CS_Measurement m
    WHERE m.name = ?
    AND m.periodSeq = ?
    AND m.positionSeq = ?
    AND m.payeeSeq = ?

    Access Plan:
    -----------
    Total Cost: 26.361
    Query Degree: 12

    Rows
    RETURN
    ( 1)
    Cost
    I/O
    |
    1
    LTQ
    ( 2)
    26.361
    3
    |
    1
    FETCH
    ( 3)
    26.1561
    3
    /---+---\
    12.9989 3.61639e+07
    IXSCAN TABLE: UDB
    ( 4) CS_MEASUREMENT
    17.4407
    2
    |
    3.61639e+07
    INDEX: UDB
    CS_MEASURE_IX1


    Total no of records in the table : 36 million.

    INDEX_NAME: INDEX_COLUMNS: UNIQUERULE:
    ----------- --------------------------------------------------- -----------
    CS_MEASUREMENT_PER +POSITIONSEQ+PERIODSEQ D
    CS_MEASURE_IX1 +PERIODSEQ+POSITIONSEQ D
    CS_MEASUREMENT_PK +MEASUREMENTSEQ+PIPELINERUNSEQ P
    CS_MEASUREMENT_AK1 +NAME+PAYEESEQ+POSITIONSEQ+PERIODSEQ+PIPELINERUNSE Q U


    CLONAME: COLCARD
    -------------- -------
    NAME 18
    PAYEESEQ 20992
    POSITIONSEQ 21221
    PERIODSEQ 147
    PIPELINERUNSEQ 1120

    this query is taking long time even though it is using index scan.

    My question is why is this query using the index CS_MEASURE_IX1 istead of CS_MEASUREMENT_AK1 as CS_MEASUREMENT_AK1 has total
    5 columns out of which 4 columns are in the whereclause of the query.

    is it because of the first column of the CS_MEASUREMENT_AK1 index has low cardinality this index is not beeing used?
    any suggestion on improving the performance of this query?

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I think that intra-partition parallelism may play a role in it. What happens if you set the degree of parallelism to 1 before running the query? Do you really have 12 processors to play with?

    Also, can you post the output of "REORGCHK CURRENT STATISTICS ON TABLE UDB.CS_Measurement"?
    Last edited by n_i; 12-06-08 at 10:40.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    This is indeed unexpected, at least from the information you provided.
    Could you try replacing "AND m.periodSeq = ?" by "AND m.periodSeq = ? + 0"
    (that is, assuming periodSeq is numeric; otherwise add "|| ''").
    This should discourage the use of CS_MEASURE_IX1, hence possibly turn into using CS_MEASUREMENT_AK1.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  4. #4
    Join Date
    Nov 2007
    Posts
    27
    seting the query degree to 1 also did not forced to use the CS_MEASUREMENT_AK1 index. even if i include all the columns in this index in the where clause the index is not changed.
    this table does not need reorg.

    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: UDB.CS_MEASUREMENT
    UDB CS_MEASUREMENT 3.6e+07 0 1e+06 1e+06 - 1.86e+10 0 97 100 ---
    ----------------------------------------------------------------------------------------

    Index statistics:

    F4: CLUSTERRATIO or normalized CLUSTERFACTOR > 80
    F5: 100 * (KEYS * (ISIZE + 9) + (CARD - KEYS) * 5) / ((NLEAF - NUM EMPTY LEAFS -1) * (INDEXPAGESIZE - 96) > MIN(50, (100- PCTFREE))
    F6: (100 - PCTFREE) * ( FLOOR[ (100 - min(10, pctfree)) / 100 * (indexPageSize - 96) / (ISIZE + 12) ] ** (NLEVELS - 2) ) * (indexPageSize - 96) / (KEYS * (ISIZE + 9) + (CARD - KEYS) * 5) < 100
    F7: 100 * (NUMRIDS DELETED / (NUMRIDS DELETED + CARD)) < 20
    F8: 100 * (NUM EMPTY LEAFS / NLEAF) < 20

    SCHEMA NAME CARD LEAF ELEAF LVLS ISIZE NDEL KEYS F4 F5 F6 F7 F8 REORG
    -------------------------------------------------------------------------------------------------
    Table: UDB.CS_MEASUREMENT
    UDB CS_MEASURE_IX1 4e+07 17146 0 3 16 0 3e+06 38 84 3 0 0 *----
    UDB CS_MEASUREMENT_AK1 4e+07 2e+05 0 4 54 0 4e+07 0 88 31 0 0 *----
    UDB CS_MEASUREMENT_PER 4e+07 17146 0 3 16 0 3e+06 0 84 3 0 0 *----
    UDB CS_MEASUREMENT_PK 4e+07 61714 0 3 16 0 4e+07 100 89 0 0 0 -----
    -------------------------------------------------------------------------------------------------


    evven adding 0 to Periodseq did not helped its the same access plan.

    any other suggestions please?

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Don't know if you can afford a reorg on that table, but I think that reorganizing it using CS_MEASUREMENT_AK1 might help.
    ---
    "It does not work" is not a valid problem statement.

Posting Permissions

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