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

grofaty · #1 (**permalink**) 08-23-07, 02:07

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

Marcus_A · #2 (**permalink**) 08-23-07, 03:39

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.

stolze · #3 (**permalink**) 08-23-07, 03:56

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.)

Internet Services

Web Development

Digital Marketing

Consumer Tech