Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2003
    Posts
    85

    Question Unanswered: Performance Problem

    I have a "simple" query:
    select count(*) from dxtdb.dxtprem_bl where src_sys_entr_dt='1999-12-29'.
    In one environment it's taking a wall time of > 17 min., while on another "similar" environment it's taking > 35 minutes. (In an SP2 environment, the above query took > 5 minutes.)

    What's confusing is the EXPLAIN output. The EXPLAIN output on the 8GB servers was the same as for the SP2, yet on the 4GB servers instead of a relation scan, it was doing an index scan (and none of the indexes on the table contain the above column, period), with LIST Prefetch (with sorts ...).
    The stats are "similar" yet with differences. The db cfg for num_ioservers, avg_appls, num_iocleaners are the exact between the non-SP2 servers.
    I tried lowering the bufferpool allocation on the larger server to match the poorer performing server environment but I still got the same good EXPLAIN. I tried to match the clusterfactor for the index (from 0.79... to 0.83..., matching the value on the smaller server, which wasn't used on the larger server but used on the "smaller" server) and I still got the same EXPLAIN output on the larger server.

    2 AIX p630 servers environments:
    1 has more RAM (8GB vs. 4 GB). CPU speeds seem the same.
    insgipdm@paehowup2577[/home/insgipdm]>lsattr -E -l sys0 | egrep "realmem|frequency"
    frequency 484000000 System Bus Frequency False
    realmem 8388608 Amount of usable physical memory in Kbytes False
    Database manager configuration release level = 0x0a00

    CPU speed (millisec/instruction) (CPUSPEED) = 5.668131e-07
    Communications bandwidth (MB/sec) (COMM_BANDWIDTH) = 1.000000e+02

    insgipdm@njros1up3065[/home/insgipdm]>lsattr -E -l sys0 | egrep "realmem|frequency"
    frequency 333000000 System Bus Frequency False
    realmem 4194304 Amount of usable physical memory in Kbytes False
    Database manager configuration release level = 0x0a00

    CPU speed (millisec/instruction) (CPUSPEED) = 5.668131e-07
    Communications bandwidth (MB/sec) (COMM_BANDWIDTH) = 1.000000e+02
    Let me know what output or knobs are desired.
    I have the stats (from db2look) and db2expln output.

    Ruby
    Last edited by rubystep; 06-09-04 at 15:11.

  2. #2
    Join Date
    Sep 2003
    Posts
    84
    When you say similar environments I hope you mean that the data is same across the envs ?

  3. #3
    Join Date
    Sep 2003
    Posts
    85
    Yes, the difference in # of rows is around 2MM (where both server's database tables have over 120MM rows).

    HTH,
    Ruby

  4. #4
    Join Date
    Sep 2003
    Posts
    84
    It seems that you have gone into a great deal of detail regarding the comparison of the h/w. But may be comparing with the exact data in the 2 envs will be a good idea.
    What I am driving at is, if the number of rows qualifying by your query is different in the 2 envs and the total no. of rows are different then there may be a possible explanation why the optimiser is behaving differently in the 2 different envs.

    And then again when you say that it is doing an index scan even though there is no index defined on src_sys_entr_dt that seems a bit strange - did I get you correctly ?

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I need all DDL for the table, indexes, and tablespaces of the table in question, plus DDL for all system temporary tablespaces. Also need the disk hardware configuration.
    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
    Sep 2003
    Posts
    85
    As far as:
    It seems that you have gone into a great deal of detail regarding the comparison of the h/w. But may be comparing with the exact data in the 2 envs will be a good idea.
    What I am driving at is, if the number of rows qualifying by your query is different in the 2 envs and the total no. of rows are different then there may be a possible explanation why the optimiser is behaving differently in the 2 different envs.
    The result set in either environment is 0 (yes, zero) rows.

    And then again when you say that it is doing an index scan even though there is no index defined on src_sys_entr_dt that seems a bit strange - did I get you correctly ? Yes, that is correct.

Posting Permissions

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