Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2003
    Posts
    1,605

    Unanswered: SQL Explain: 80 costs; Elapset time: 72 sec.

    Hi,
    using DB2 v9 FP2 on Linux/Intel I have strange problem. Only one SQL is having a performance problem.

    SQL statement:
    Code:
    SELECT * FROM MYSCHEMA.MYTABLE WHERE COL1 = '0006' AND COL2 = 735518
    Primary key on table is: COL1, COL2, COL3

    Explain:
    Code:
    Access Plan:
    -----------
    	Total Cost: 		80,257
    	Query Degree:		1
    
          Rows 
         RETURN 
         (   1) 
          Cost 
           I/O 
           |
           300 
         TBSCAN 
         (   2) 
         80,257 
           76 
           |
           300 
     TABLE: MYSCHEMA  
     MYTABLE
    Strange is SQL runs for 72 second, according to access plan I would say it is normal bellow 1 second. It is very strange. Table is very small: 2000 rows. Keep in mind all other SQLs on database are running inside 1 to 3 second frame. I have made explain for other SQLs - on of them having million of total costs but running only 3 second.

    I suspect there is some kind of application problem... Application is running inside WAS 6 and accessing DB2 through connection pooling.

    Any idea why SQL runs for 72 seconds?

    Regards,
    Grofaty
    Last edited by grofaty; 08-23-07 at 03:11.

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Explain says it is doing a table scan. This may be OK for 2000 rows. But you may have lockwait time. What is your locktimeout set to?

    First do a runstats as follows:
    runstats on table MYSCHEMA.MYTABLE with distribution on key columns and indexes all

    If that does not help, then alter the table and set to volatile. This will encourage index access and cause fewer locking problems.
    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
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    You should take your query, run it through "db2batch" and see how long DB2 really takes. If the query finishes quickly (in less than a second with just 2000 rows), then your application has a problem. (With application I mean everything that is not DB2, i.e. including WAS.)
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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