Results 1 to 13 of 13
  1. #1
    Join Date
    Jun 2003
    Location
    Canada
    Posts
    37

    Unanswered: This one kills me

    I am being challenged with the following puzzle:

    There is a very small code table (30 rows, 2 cols - both CHAR). Table sits in its own tablespace which is not segmented or partitioned. PK index is built on first col.

    Simple SELECT statement picking col2 with col1 as key sometimes takes 28 seconds !!!!

    LOCKRULE is 'A'

    Table contains static data, unchanged since creation. Statsdate is recent.
    Explain shows query picks up PK index.

    DB2 UDB for S/390 v 7.2 with latest fixpacks.

    I would welcome any ideas.

    Julius

  2. #2
    Join Date
    Jun 2003
    Location
    Canada
    Posts
    37

    Re: This one kills me

    Two more additions:

    The very bad performance shows up irregularly.

    Also, I cannot easily reproduce as when I execute the same query, it is already being served from cache (+BP).

    Based on this I speculate that it may have something to de with DB2 serving the data from disk I/O vs. cache. However, as no indication of resource contention, even if physical read occurs, the query should return within subsecond elapse time.


    Julius

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    If the table is that small, then a simple SELECT should not be using the index. There is something wrong here. An index will not be used for a simple SELECT unless the table is more than 7-10 4K pages. Check your runstats again, and maybe check the card values in the catalog. Also do a reorg. Check your tablespace allocation.

    You might also check the catalog to make sure there is not another table in the same tablespace. I know you said that there are no others, but I would check the catalog to be sure.

    Are there any foreign keys on other tables which reference this table? Is there a program that updates this table that might be running? There is no telling what an application programmer might be doing without your knowledge.

    There is no way 28 second response time has anything to do with accessing the data from disk vs. bufferpool, unless you bufferpool is being flooded by dirty pages (updated pages not yet written to disk).

    The only other thing that I can think of is some kind of dynamic SQL problem such as contention on the catalog when determining the access path.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  4. #4
    Join Date
    Nov 2003
    Location
    Germany
    Posts
    62
    Hi

    As you said, your tablespace is not segmented, the size of it matters. For a segmented tablespace the number of pages, the table has rows on, is important, but for a simple tablespace the number of pages of the entire tablespace is important. So if your table is small, but your tablespace is big and the access path for your SQL is a tablespace scan, then 28 seconds could be possible.

    BTW, why did you create your tablespace simple and not segmented. We don't use simple tablespaces anymore, only segmented or partitioned.

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Simple tablespaces work well when you only have one table per tablespace (which is usually the case for user tables on DB2 for OS/390). In many cases simple tablespaces are better than segmented because of the insert rule.

    On a simple tablespace, the insert will first be attempted on the correct page according to sequence of the clustering index, or nearby pages, and then go to end of the table as a last resort.

    On segmented tablespaces, the insert will be attempted on the correct page according to sequence of the clustering index (just like on simple tablespaces), and then if that fails it goes through a series of determinations looking at the space maps, and if no space is available, it will put the row at the end of segment. (I don't recall the exact calculation).

    The point is that, while segmented tablespaces have a more complex space map, it must be maintained by DB2, and if there is no room on the correct page for an insert, DB2 can spend a lot time looking around, unlike a simple tablespace which goes to the end of the table instead of the end of the segment.

    Assuming that one does reorgs when needed to re-create freespace for inserts (according to the clustering index), then simple tablespaces usually have a better insert rule.

    Segmented tablespaces are better when mass deletes are frequently done, and no reorg is possible any time soon.

    The idea that simple tablespaces are to be avoided is folklore, and it is also incorrect folklore. Some IBM’ers recommend segmented tablespaces, because they think customer DBA’s are idiots (some are) and segmented tablespace might work better if you don’t reorg your tables, and they don't trust customers to make certain that there is only one table per tablespace.

    But for the absolute best performance with one table per tablespace, simple tablespaces are often the best choice.
    Last edited by Marcus_A; 02-24-04 at 05:11.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  6. #6
    Join Date
    Nov 2003
    Location
    Germany
    Posts
    62
    I disagree with your arguments, and I would say for the absolute best perfomance segmented tablespaces are better than simple tablespaces, even if it contains only one table.

    - Mass delete is far better thanin a simple TS
    - Tablespace scan only scans pages of the table not all pages
    - space will be better reclaimed
    - the space map contains more information, so rows can better be
    inserted, well admiittedly consuming a little bit more CPU, but I never
    heard about a benchmark regarding inserts into a simple or a
    segmented TS

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    If segmented tablespaces were always better, they would have replaced simple tablespaces long ago just like type II indexes replaced type I.

    I could not find the insert rule documented in the newer manuals (I originally found it in a very old Redbook that I no longer have), but that is the main concern in terms of performance, in addition to the maintenance of the space maps (which needs I/O and CPU time).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  8. #8
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I am not sure about the practical difference between simple and segmented tablespaces with regard to tablespace scans.

    From the Admin Guide:

    "Table space scans of non-segmented table spaces DB2 reads and examines every page in the table space, regardless of which table the page belongs to. It might also read pages that have been left as free space and space not yet reclaimed after deleting data."

    If there is only one table per tablespace, the first sentence is a non-issue. The second sentence does not seem to me to be a big issue because of the following:

    1. There usually are not that many freepages defined in the tablespace if it is usually scanned (data warehouse application vs. OLTP). The use of freepages is for OLTP applications where there is significant insert activity and data clustering needs to be maintained real-time. Even if a tablespace that had freepages was scanned, there would not be that many that were completely empty (else the freepage definition should be lowered or eliminated). If the tablespace were simple, it would avoid reading the spacemap pages.

    2. If one does a reorg after a mass delete, then the issue about scanning the empty pages is resolved. But I will agree that if one does frequent mass deletes without a reorg, then segmented has definite advantages.

    3. I have not heard that a simple tablespace which only has a few pages of data, has formatted pages for entire length of the primary space allocation after a reorg. Do you have any documentation on this?
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  9. #9
    Join Date
    Jun 2003
    Location
    Canada
    Posts
    37
    Thank you all who have contributed with comments or thoughts. I am still being haunted by the mystery. Some answers to Marcus' questions:


    Q: "If the table is that small, then a simple SELECT should not be using the index. There is something wrong here. An index will not be used for a simple SELECT unless the table is more than 7-10 4K pages."
    A: The table occupies a single page of 4K size. The SELECT is still picking up the index.


    Q: "Check your runstats again, and maybe check the card values in the catalog."
    A: RUNSTATS was done 10 days ago, no update on the table since then.
    CARD value is shown correctly as 30.


    Q: "Also do a reorg."
    A: I don't know how this would help here. Can you give me a hint?


    Q: "Check your tablespace allocation."
    A: I am doing it as we "speak".


    Q: "You might also check the catalog to make sure there is not another table in the same tablespace. I know you said that there are no others, but I would check the catalog to be sure."
    A: There is this one table in the tablespace along with a view built on the same table. The view's definition is a SELECT both fields on the table.


    Q: "Are there any foreign keys on other tables which reference this table?"
    A: No entries in SYSFOREIGNKEYS for this table.


    Q: "Is there a program that updates this table that might be running? There is no telling what an application programmer might be doing without your knowledge."
    A: I will have to find this out for certain. Though my guess is that there is none as this table is a static code table. Last update, according to catalog happened in 1997 !


    Q: "There is no way 28 second response time has anything to do with accessing the data from disk vs. bufferpool, unless you bufferpool is being flooded by dirty pages (updated pages not yet written to disk)."
    A: Agreed - too much difference.


    Q: "The only other thing that I can think of is some kind of dynamic SQL problem such as contention on the catalog when determining the access path."
    A: This one is a definite possibility, though I don't know how I would find it out. Pretty much all directs (non-view) queries against my nemesis table are dynamic. Any ideas?


    Thanks again,

    Julius

  10. #10
    Join Date
    Jun 2003
    Location
    Canada
    Posts
    37
    Originally posted by Marcus_A
    I am not sure about the practical difference between simple and segmented tablespaces with regard to tablespace scans.
    Our company has a policy that segmented tablespaces are to be used in all cases except:

    a) The table is very big to encourage parallelism and availability -> partitioned
    b) The table is very small or very frequently joined -> simple

    Julius

  11. #11
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Q: "If the table is that small, then a simple SELECT should not be using the index. There is something wrong here. An index will not be used for a simple SELECT unless the table is more than 7-10 4K pages."
    A: The table occupies a single page of 4K size. The SELECT is still picking up the index.


    This seems very strange to me. Do you have other very small tables you could try with an explain.

    Q: "Also do a reorg."
    A: I don't know how this would help here. Can you give me a hint?


    If the table has lots of formatted but empty pages, the reorg should fix that. That could be one reason why the index is being used.

    Q: "Is there a program that updates this table that might be running? There is no telling what an application programmer might be doing without your knowledge."
    A: I will have to find this out for certain. Though my guess is that there is none as this table is a static code table. Last update, according to catalog happened in 1997 !


    I believe that indicates the last time the table structure was changed, not the last time the data was changed.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  12. #12
    Join Date
    Nov 2003
    Location
    Germany
    Posts
    62
    Originally posted by jsasvari
    Our company has a policy that segmented tablespaces are to be used in all cases except:

    a) The table is very big to encourage parallelism and availability -> partitioned
    b) The table is very small or very frequently joined -> simple

    Julius
    I don't understand the second part of item b (or very frequently joined). I never heard about that. What should be the reason for that?

  13. #13
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Walter,

    My understanding is that on a simple tablespace, once a reorg is done, then DB2 only formats pages to the end of the data needed (with maybe one extra page) and leaves the extra space allocation unformatted. Therefore, DB2 will not scan these unformatted pages. Obviously after a delete at the end of the table, a reorg would need to be done to eliminate unused pages at the end of the table.

    If you have information that is different than this, I would like to see the documentation.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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