Topic
  • 5 replies
  • Latest Post - ‏2012-10-23T12:05:04Z by SystemAdmin
SystemAdmin
SystemAdmin
3105 Posts

Pinned topic Too many getpages using descending index

‏2006-08-14T11:35:33Z |
We are working with DB2 V7 on z/OS 1.4.
We run an SQL on a table, using a descending index (also datapages are
read). When we get rows in the middle of the index (with "fetch first n
rows"), we see an explainable amount of getpages. On the other hand, when
we reach the end of the index (getting the last qualifiyng rows via the
index), we get many more getpages (as well from the index as from the
tablespace), giving a worse response. The tablespace and index are recently
reorged and the statistics are up-to-date.
Can anybody give me an explanation for that and perhaps tell me how we can
avoid these extra getpages ?
Thank you in advance !
Updated on 2012-10-23T12:05:04Z at 2012-10-23T12:05:04Z by SystemAdmin
  • SystemAdmin
    SystemAdmin
    3105 Posts

    Re: Too many getpages using descending index

    ‏2006-08-14T12:02:32Z  
    In article
    <740463672.1155555364338.JavaMail.wassrvr@ltsgwas009.sby.ibm.com>,
    rvdb@cipal.be says...
    > Can anybody give me an explanation for that and perhaps tell me how we can
    > avoid these extra getpages ?
    >

    Take a look at INFO APAR II13579 for some general performance hints.

    You may also want to open a PMR with your local support centre, collect
    docs and have them send it over to the DB2 performance team in SVL. (One
    of the folks who works on that team is a Belgian.)

    Dougie Lawson
    UK Software Support
    IBM Farnborough
  • SystemAdmin
    SystemAdmin
    3105 Posts

    Re: Too many getpages using descending index

    ‏2006-08-14T19:44:00Z  
    Could you send the query, create table
    and create index statements?

    Do you have an abnormal data distribution
    on the column's data in that index?

    Could you send the access path of the query?

    Is this dynamic SQL, and are you using literals
    in the query?

    How many rows have been inserted near the end of the index,
    since the reorg? Are the new values in the index
    ever-ascending?

    Try removing predicates from the query,
    and checking getpage counts between the
    middle-of-index queries and the end-of-index queries.
    If you have high index screening on the middle-of-index
    scans, but poor screening on end-of-index scans, then
    that would explain the additional tablespace getpages.
    But if the ratio of index/tablespace getpages is the
    same between the middle and end of index scans, then
    I'd just say you were qualifying more rows.
    If you have a sort or materialization of the result set,
    then the fetch first clause would not reduce the
    getpages for materialization, yet still reduce the
    number of rows returned.

    Hope that's a start,
    Thanks
    Chris Kittell

    > We are working with DB2 V7 on z/OS 1.4.
    > We run an SQL on a table, using a descending index
    > (also datapages are
    > read). When we get rows in the middle of the index
    > (with "fetch first n
    > rows"), we see an explainable amount of getpages. On
    > the other hand, when
    > we reach the end of the index (getting the last
    > qualifiyng rows via the
    > index), we get many more getpages (as well from the
    > index as from the
    > tablespace), giving a worse response. The tablespace
    > and index are recently
    > reorged and the statistics are up-to-date.
    > Can anybody give me an explanation for that and
    > perhaps tell me how we can
    > avoid these extra getpages ?
    > Thank you in advance !
    >
  • SystemAdmin
    SystemAdmin
    3105 Posts

    Re: Too many getpages using descending index

    ‏2006-08-15T19:48:01Z  
    Could you send the query, create table
    and create index statements?

    Do you have an abnormal data distribution
    on the column's data in that index?

    Could you send the access path of the query?

    Is this dynamic SQL, and are you using literals
    in the query?

    How many rows have been inserted near the end of the index,
    since the reorg? Are the new values in the index
    ever-ascending?

    Try removing predicates from the query,
    and checking getpage counts between the
    middle-of-index queries and the end-of-index queries.
    If you have high index screening on the middle-of-index
    scans, but poor screening on end-of-index scans, then
    that would explain the additional tablespace getpages.
    But if the ratio of index/tablespace getpages is the
    same between the middle and end of index scans, then
    I'd just say you were qualifying more rows.
    If you have a sort or materialization of the result set,
    then the fetch first clause would not reduce the
    getpages for materialization, yet still reduce the
    number of rows returned.

    Hope that's a start,
    Thanks
    Chris Kittell

    > We are working with DB2 V7 on z/OS 1.4.
    > We run an SQL on a table, using a descending index
    > (also datapages are
    > read). When we get rows in the middle of the index
    > (with "fetch first n
    > rows"), we see an explainable amount of getpages. On
    > the other hand, when
    > we reach the end of the index (getting the last
    > qualifiyng rows via the
    > index), we get many more getpages (as well from the
    > index as from the
    > tablespace), giving a worse response. The tablespace
    > and index are recently
    > reorged and the statistics are up-to-date.
    > Can anybody give me an explanation for that and
    > perhaps tell me how we can
    > avoid these extra getpages ?
    > Thank you in advance !
    >
    Yes, maybe you have skewed data . Are you using COLCOUNT,FREQVAL on the runstats ? If its static sql you could consider using the REOPT clause and see if db2 changes the access path .Maybe your RID pool is too small and after reading past the limit db2 changes to a TS scan ? You can see if the query might be using the RID pool if its doing List Prefetch .
    HTH.
    Regards
  • SystemAdmin
    SystemAdmin
    3105 Posts

    Re: Too many getpages using descending index

    ‏2006-08-16T08:21:25Z  
    Could you send the query, create table
    and create index statements?

    Do you have an abnormal data distribution
    on the column's data in that index?

    Could you send the access path of the query?

    Is this dynamic SQL, and are you using literals
    in the query?

    How many rows have been inserted near the end of the index,
    since the reorg? Are the new values in the index
    ever-ascending?

    Try removing predicates from the query,
    and checking getpage counts between the
    middle-of-index queries and the end-of-index queries.
    If you have high index screening on the middle-of-index
    scans, but poor screening on end-of-index scans, then
    that would explain the additional tablespace getpages.
    But if the ratio of index/tablespace getpages is the
    same between the middle and end of index scans, then
    I'd just say you were qualifying more rows.
    If you have a sort or materialization of the result set,
    then the fetch first clause would not reduce the
    getpages for materialization, yet still reduce the
    number of rows returned.

    Hope that's a start,
    Thanks
    Chris Kittell

    > We are working with DB2 V7 on z/OS 1.4.
    > We run an SQL on a table, using a descending index
    > (also datapages are
    > read). When we get rows in the middle of the index
    > (with "fetch first n
    > rows"), we see an explainable amount of getpages. On
    > the other hand, when
    > we reach the end of the index (getting the last
    > qualifiyng rows via the
    > index), we get many more getpages (as well from the
    > index as from the
    > tablespace), giving a worse response. The tablespace
    > and index are recently
    > reorged and the statistics are up-to-date.
    > Can anybody give me an explanation for that and
    > perhaps tell me how we can
    > avoid these extra getpages ?
    > Thank you in advance !
    >
    Chris,
    I tried to look more in detail to the things you mentioned.
    With removing predicates from the query, I found out that there were many less rows qualifying at the end of the index, because of the extra predicates. And because of reading via the descending index, which I thought was good, because it prevents an extra sorting of the results, many more data pages had to be read before qualifying rows were found.
    Thanks very much for reminding me of those things. Now I know what the reason is, I can try to find a solution for the problem.
    Rita V.
  • SystemAdmin
    SystemAdmin
    3105 Posts

    Re: Too many getpages using descending index

    ‏2012-10-23T12:05:04Z  
    Chris,
    I tried to look more in detail to the things you mentioned.
    With removing predicates from the query, I found out that there were many less rows qualifying at the end of the index, because of the extra predicates. And because of reading via the descending index, which I thought was good, because it prevents an extra sorting of the results, many more data pages had to be read before qualifying rows were found.
    Thanks very much for reminding me of those things. Now I know what the reason is, I can try to find a solution for the problem.
    Rita V.
    Experiencing the exact same problem

    In a select with fetch first n rows only, getpages calls explode towards end of resultset.

    In my example we have FETCH FIRST 1000 ROWS ONLY actual result is 808 ROWS, row nr 800 causes over 1 million getpage calls.

    Iterating over resultset with java.

    Torbjørn