Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2009
    Posts
    2

    Unanswered: Problem with query performance

    Hi

    We're running DB2 V8 on AIX.

    We've got a table, containing about 3200000 rows, PK=ENS4009_SR_NR, clustered by ENS4009_SR_NR.

    We're experimenting with the query as below. The aim is to give back the first 10 rows to the user. The problem is when the volume of rows associated with ENS4009_PARTY_ID is high (like in this case 32000+), the query is very slow.

    I've created an index, similar to test.IDX811271104310000, but with the SR_NR as well
    (ens4009_party_id asc, ens4301_pty_tp_ind asc, ens4009_sr_nr desc), but for some reason db2 doesn't want to use that.

    Any ideas would be greatly appreciated!

    Thanx
    am
    ==================== STATEMENT ==========================================

    Isolation Level = Cursor Stability
    Blocking = Block Unambiguous Cursors
    Query Optimization Class = 5

    Partition Parallel = No
    Intra-Partition Parallel = No

    SQL Path = "SYSIBM", "SYSFUN", "SYSPROC", "srcdb1"


    SQL Statement:

    select *
    from test.ENS4009_SRV_REQ this_
    where this_.ENS4009_PARTY_ID='AAXOK123'and this_.ENS4301_PTY_TP_IND=
    'C'and this_.ENS4009_SR_NR <=2147483647
    order by this_.ENS4009_SR_NR desc
    fetch first 10 rows only


    Section Code Page = 1208

    Estimated Cost = 5399.725586
    Estimated Cardinality = 10.000000

    Index ANDing
    | Optimizer Estimate of Set Size: 32782
    | Index ANDing Bitmap Build Using Row IDs
    | | Optimizer Estimate of Set Size: 32782
    | | Access Table Name = test.ENS4009_SRV_REQ ID = 4,4
    | | | Index Scan: Name = test.IDX811271104310000 ID = 36
    | | | | Regular Index (Not Clustered)
    | | | | Index Columns:
    | | | | | 1: ENS4009_PARTY_ID (Ascending)
    | | | | | 2: ENS4301_PTY_TP_IND (Descending)
    | | | #Columns = 0
    | | | #Key Columns = 2
    | | | | Start Key: Inclusive Value
    | | | | | | 1: 'AAXOK123'
    | | | | | | 2: 'C'
    | | | | Stop Key: Inclusive Value
    | | | | | | 1: 'AAXOK123'
    | | | | | | 2: 'C'
    | | | Index-Only Access
    | | | Index Prefetch: Eligible 84
    | | | Isolation Level: Uncommitted Read
    | | | Lock Intents
    | | | | Table: Intent None
    | | | | Row : None
    | Index ANDing Bitmap Probe Using Row IDs
    | | Optimizer Estimate of Set Size: 32782
    | | Access Table Name = test.ENS4009_SRV_REQ ID = 4,4
    | | | Index Scan: Name = test.ENS4009_PTY_STS_SR ID = 8
    | | | | Regular Index (Not Clustered)
    | | | | Index Columns:
    | | | | | 1: ENS4009_PARTY_ID (Ascending)
    | | | | | 2: ENS4006_SR_STS_CD (Ascending)
    | | | | | 3: ENS4009_SR_NR (Descending)
    | | | #Columns = 0
    | | | #Key Columns = 1
    | | | | Start Key: Inclusive Value
    | | | | | | 1: 'AAXOK123'
    | | | | Stop Key: Inclusive Value
    | | | | | | 1: 'AAXOK123'
    | | | Index-Only Access
    | | | Index Prefetch: Eligible 261
    | | | Isolation Level: Uncommitted Read
    | | | Lock Intents
    | | | | Table: Intent None
    | | | | Row : None
    | | | Sargable Index Predicate(s)
    | | | | #Predicates = 1
    Insert Into Sorted Temp Table ID = t1
    | #Columns = 1
    | #Sort Key Columns = 1
    | | Key 1: (Ascending)
    | Sortheap Allocation Parameters:
    | | #Rows = 32783
    | | Row Width = 12
    | Piped
    | Duplicate Elimination
    List Prefetch Preparation
    | Access Table Name = test.ENS4009_SRV_REQ ID = 4,4
    | | #Columns = 64
    | | Fetch Using Prefetched List
    | | | Prefetch: 1172 Pages
    | | Lock Intents
    | | | Table: Intent Share
    | | | Row : Next Key Share
    | | Sargable Predicate(s)
    | | | #Predicates = 3
    | | | Insert Into Sorted Temp Table ID = t2
    | | | | #Columns = 64
    | | | | #Sort Key Columns = 1
    | | | | | Key 1: ENS4009_SR_NR (Descending)
    | | | | Sortheap Allocation Parameters:
    | | | | | #Rows = 10
    | | | | | Row Width = 408
    | | | | | Sort Limited To Estimated Row Count
    | | | | Piped
    Sorted Temp Table Completion ID = t2
    Access Temp Table ID = t2
    | #Columns = 64
    | Relation Scan
    | | Prefetch: Eligible
    Return Data to Application
    | #Columns = 66

    End of section

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    ENS4009_PARTY_ID should be the clustering index, not the PK (I would probably try it with this one column by itself or maybe first 2 columns only (leaving out DESC). It is very rare that a PK that is only a single column would be the proper clustering index. Change the clustering index and reorg the table and try again. Be warned that if this table has a lot of insert activity, you will need to reorg it on a regular basis.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Sorry. I should consider more.
    Last edited by tonkuma; 09-14-09 at 10:39.

  4. #4
    Join Date
    Sep 2009
    Posts
    2
    Hi Marcus

    Thanx for the advise. I agree and will test this to see the impact on other queries as well. Unfortunately this table is being accessed in different ways with different criteria. It is one of the main tables used in a call centre application.

    I would have thought that db2 would first identify the rows by using all columns in the new index (party_id, party_tp_cd, sr_nr) and then fetch the data for the first 10 rows only. But it looks like all the data is fetched first (33000 rows), and then only the first 10 rows are returned.

    I've changed the query as follows to get the best performance:

    with temp1 as
    (select ens4009_sr_nr
    from test.ENS4009_SRV_REQ
    where ENS4009_PARTY_ID='AAXOK123' and ENS4301_PTY_TP_IND='C'
    and ENS4009_SR_NR <= 2147483647
    order by ENS4009_SR_NR desc fetch first 10 rows only)select
    this_.*
    from test.ENS4009_SRV_REQ this_, temp1 tmp
    where this_.ENS4009_SR_NR = tmp.ens4009_sr_nr

    The challenge is to get Hibernate to generate this type of sql!

    Regards
    am

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by abriem
    I would have thought that db2 would first identify the rows by using all columns in the new index (party_id, party_tp_cd, sr_nr) and then fetch the data for the first 10 rows only. But it looks like all the data is fetched first (33000 rows), and then only the first 10 rows are returned.
    The fetch first 10 rows only applies only after the order by, so DB2 has to formulate the entire answer-set first.
    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
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Quote Originally Posted by abriem
    The challenge is to get Hibernate to generate this type of sql!
    Easy, create your userviews like that and expose that to hibernate instead of bare-tables. A userview is nothing more or less than a SQL-subroutine.

Posting Permissions

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