Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2014
    Location
    Long Island, New York
    Posts
    31

    Unanswered: Data access path question

    Hi,

    I have a simple query on a table with a primary key. The data access path on all but one database uses the primary key. On one database, the query does a table scan and ignores the PK. The table and PK structures are identical in all databases. I've checked multiple client sites as well as internal testing databases and they all use the PK. Only a single database at one client that has three production databases running under a single instance has this problem. I've tested this on Db2 9.7 and 10.5 on UNIX and Linux. I tried to REORG the table and RUNSTATS on the table and index. I even dropped the primary key and recreated it. Does anyone have an idea why this may be happing or what else I can do to research? Thank you.

    SELECT * FROM TAB1 WHERE RECNO >= 101000 ORDER BY RECNO;

    Click image for larger version. 

Name:	pk.jpg 
Views:	3 
Size:	818.5 KB 
ID:	17461

    Click image for larger version. 

Name:	nopk.jpg 
Views:	3 
Size:	812.6 KB 
ID:	17462

  2. #2
    Join Date
    Jul 2016
    Location
    Moscow
    Posts
    294
    Provided Answers: 45
    Hi,

    Can you provide both access plans for ixscan and tbscan from the db2exfmt utility?

    db2 connect to mydb
    db2 set current explain mode explain
    db2 set current explain snapshot explain
    db2 “select ...”
    db2exfmt -d mydb -1 -o exftm.txt
    Regards,
    Mark.

  3. #3
    Join Date
    Sep 2014
    Location
    Long Island, New York
    Posts
    31
    Here is the requested information..


    Query that uses table scan
    =============================

    Access Plan:
    -----------
    Total Cost: 2349.67
    Query Degree: 1

    Rows
    RETURN
    ( 1)
    Cost
    I/O
    |
    37821
    TBSCAN
    ( 2)
    2349.67
    2319
    |
    37821
    SORT
    ( 3)
    2349.67
    2319
    |
    37821
    TBSCAN
    ( 4)
    2177.63
    2319
    |
    37821
    TABLE: SCHEMA1
    TAB1
    Q1

    2) TBSCAN: (Table Scan)
    Cumulative Total Cost: 2349.67
    Cumulative CPU Cost: 1.69821e+09
    Cumulative I/O Cost: 2319
    Cumulative Re-Total Cost: 1.19116
    Cumulative Re-CPU Cost: 9.45679e+06
    Cumulative Re-I/O Cost: 0
    Cumulative First Row Cost: 2349.67
    Estimated Bufferpool Buffers: 2702

    Arguments:
    ---------
    MAXPAGES: (Maximum pages for prefetch)
    ALL
    PREFETCH: (Type of Prefetch)
    NONE
    SCANDIR : (Scan Direction)
    FORWARD
    SPEED : (Assumed speed of scan, in sharing structures)
    SLOW
    THROTTLE: (Scan may be throttled, for scan sharing)
    FALSE
    VISIBLE : (May be included in scan sharing structures)
    FALSE
    WRAPPING: (Scan may start anywhere and wrap)
    FALSE

    Input Streams:
    -------------
    3) From Operator #3

    Estimated number of rows: 37821
    Number of columns: 396
    Subquery predicate ID: Not Applicable

    Column Names:
    ------------
    +Q1.RECNO(A)+Q1.OVERRIDE_RATE_CODE
    ......

    Output Streams:
    --------------
    4) To Operator #1

    Estimated number of rows: 37821
    Number of columns: 396
    Subquery predicate ID: Not Applicable

    Column Names:
    ------------
    +Q2.RECNO(A)+Q2.OVERRIDE_RATE_CODE
    ......



    4) TBSCAN: (Table Scan)
    Cumulative Total Cost: 2177.63
    Cumulative CPU Cost: 3.32326e+08
    Cumulative I/O Cost: 2319
    Cumulative Re-Total Cost: 10.3235
    Cumulative Re-CPU Cost: 8.19598e+07
    Cumulative Re-I/O Cost: 0
    Cumulative First Row Cost: 6.81278
    Estimated Bufferpool Buffers: 2319

    Arguments:
    ---------
    CUR_COMM: (Currently Committed)
    TRUE
    LCKAVOID: (Lock Avoidance)
    TRUE
    MAXPAGES: (Maximum pages for prefetch)
    ALL
    PREFETCH: (Type of Prefetch)
    SEQUENTIAL
    ROWLOCK : (Row Lock intent)
    SHARE (CS/RS)
    SCANDIR : (Scan Direction)
    FORWARD
    SKIP_INS: (Skip Inserted Rows)
    TRUE
    SPEED : (Assumed speed of scan, in sharing structures)
    FAST
    TABLOCK : (Table Lock intent)
    INTENT SHARE
    TBISOLVL: (Table access Isolation Level)
    CURSOR STABILITY
    THROTTLE: (Scan may be throttled, for scan sharing)
    TRUE
    VISIBLE : (May be included in scan sharing structures)

    TRUE
    WRAPPING: (Scan may start anywhere and wrap)
    TRUE

    Predicates:
    ----------
    2) Sargable Predicate,
    Comparison Operator: Less Than or Equal (<=)
    Subquery Input Required: No
    Filter Factor: 1

    Predicate Text:
    --------------
    (0 <= Q1.RECNO)



    Input Streams:
    -------------
    1) From Object SCHEMA1.TAB1

    Estimated number of rows: 37821
    Number of columns: 397
    Subquery predicate ID: Not Applicable

    Column Names:
    ------------
    +Q1.$RID$+Q1.OVERRIDE_RATE_CODE
    ......

    Output Streams:
    --------------
    2) To Operator #3

    Estimated number of rows: 37821
    Number of columns: 396
    Subquery predicate ID: Not Applicable

    Column Names:
    ------------
    +Q1.OVERRIDE_RATE_CODE+Q1.PRIOR_OUTS_DATE
    .....


    ================================================== ==============================


    Query that uses Index scan
    =============================

    Access Plan:
    -----------
    Total Cost: 4404.08
    Query Degree: 1

    Rows
    RETURN
    ( 1)
    Cost
    I/O
    |
    57233
    FETCH
    ( 2)
    4404.08
    3638
    /---+----\
    57233 57233
    IXSCAN TABLE: SCHEMA1
    ( 3) TAB1
    206.756 Q1
    149
    |
    57233
    INDEX: SYSIBM
    SQL170802193336580
    Q1


    3) IXSCAN: (Index Scan)
    Cumulative Total Cost: 206.756
    Cumulative CPU Cost: 1.05284e+08
    Cumulative I/O Cost: 149
    Cumulative Re-Total Cost: 13.1323
    Cumulative Re-CPU Cost: 1.04259e+08
    Cumulative Re-I/O Cost: 0
    Cumulative First Row Cost: 6.81587
    Estimated Bufferpool Buffers: 150

    Arguments:
    ---------
    CUR_COMM: (Currently Committed)
    TRUE
    LCKAVOID: (Lock Avoidance)
    TRUE
    MAXPAGES: (Maximum pages for prefetch)
    149
    PREFETCH: (Type of Prefetch)
    SEQUENTIAL,READAHEAD
    ROWLOCK : (Row Lock intent)
    SHARE (CS/RS)
    SCANDIR : (Scan Direction)
    FORWARD
    SKIP_INS: (Skip Inserted Rows)
    TRUE
    TABLOCK : (Table Lock intent)
    INTENT SHARE
    TBISOLVL: (Table access Isolation Level)
    CURSOR STABILITY

    Predicates:
    ----------
    2) Start Key Predicate,
    Comparison Operator: Less Than or Equal (<=)
    Subquery Input Required: No
    Filter Factor: 1
    Predicate Text:
    --------------
    (0 <= Q1.RECNO)



    Input Streams:
    -------------
    1) From Object SYSIBM.SQL170802193336580

    Estimated number of rows: 57233
    Number of columns: 2
    Subquery predicate ID: Not Applicable

    Column Names:
    ------------
    +Q1.RECNO(A)+Q1.$RID$


    Output Streams:
    --------------
    2) To Operator #2

    Estimated number of rows: 57233
    Number of columns: 2
    Subquery predicate ID: Not Applicable

    Column Names:
    ------------
    +Q1.RECNO(A)+Q1.$RID$

  4. #4
    Join Date
    Jul 2016
    Location
    Moscow
    Posts
    294
    Provided Answers: 45
    Seems, that you provided the access plans for the query which selects all the table rows (predicate’s FF=1).
    There’s different number of rows in these tables.
    Since the query has ORDER BY clause, it’s basically the question when tbscan + sort becomes more expensive than full ixscan + fetch (without sort, since the rows are already sorted in the index). Or vice versa.
    Regards,
    Mark.

Tags for this Thread

Posting Permissions

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