asked on

Load on DB2

In my application is in executing state for quite some time, which has increased load on DB. I got a snapshot for that application

below query is requiring a lot of CPU .Can I see how to optimize this query?

SELECT IMCVEHICLES.VID, YEAR, MAKE_CODE, MAKE_DESCRIPTION, MODEL_CODE, MODEL_DESCRIPTION, SUBMODEL_DESCRIPTION,ENGINE_TYPE, LITER, FUEL, FUEL_DELIVERY, ASPIRATION FROM IMCVEHICLES, IMCVIN WHERE IMCVIN.VIN LIKE ? AND (IMCVEHICLES.VID = IMCVIN.VID1 OR IMCVEHICLES.VID = IMCVIN.VID2 OR IMCVEHICLES.VID = IMCVIN.VID3 OR IMCVEHICLES.VID = IMCVIN.VID4 OR IMCVEHICLES.VID = IMCVIN.VID5 OR IMCVEHICLES.VID = IMCVIN.VID6 OR IMCVEHICLES.VID = IMCVIN.VID7) AND DISPLAYABLE_FLAG = 1 ORDER BY MODEL_DESCRIPTION, LITER, ENGINE_TYPE, FUEL, FUEL_DELIVERY, ASPIRATION FOR FETCH ONLY

I tried putting indexes on VID1,&VID7 didnt help.
I am attaching my snapshot.

Application Snapshot
 
 
 
Application handle                         = 984
 
Application status                         = UOW Executing
 
Status change time                         = Not Collected
 
Application code page                      = 1208
 
Application country/region code            = 1
 
DUOW correlation token                     = GAFA0517.D8B0.0191A1174849
 
Application name                           = java
 
Application ID                             = GAFA0517.D8B0.0191A1174849
 
Sequence number                            = 4844
 
TP Monitor client user ID                  =
 
TP Monitor client workstation name         =
 
TP Monitor client application name         =
 
TP Monitor client accounting string        =
 
 
 
Connection request start timestamp         = 03/11/2009 10:48:49.999422
 
Connect request completion timestamp       = 03/11/2009 10:48:49.999621
 
Application idle time                      =
 
CONNECT Authorization ID                   = IMCCRP
 
Client login ID                            = wasadmin
 
Configuration NNAME of client              = usamt12ux13app
 
Client database manager product ID         = SQL08028
 
Process ID of client application           = 11642
 
Platform of client application             = LINUX
 
Communication protocol of client           = TCP/IP
 
 
 
Inbound communication address              = 10.250.5.23 55472
 
 
 
Database name                              = RPRODIMC
 
Database path                              = /media/SAN/disk1/db2inst1/db2inst1/NODE0000/SQL00005/
 
Client database alias                      = PRODIMC
 
Input database alias                       =
 
Last reset timestamp                       =
 
Snapshot timestamp                         = 03/11/2009 11:18:03.340926
 
The highest authority level granted        =
 
        Direct DBADM authority
 
        Direct CREATETAB authority
 
        Direct BINDADD authority
 
        Direct CONNECT authority
 
        Direct CREATE_NOT_FENC authority
 
        Direct LOAD authority
 
        Direct IMPLICIT_SCHEMA authority
 
        Direct CREATE_EXT_RT authority
 
        Direct QUIESCE_CONN authority
 
        Indirect CREATETAB authority
 
        Indirect BINDADD authority
 
        Indirect CONNECT authority
 
        Indirect IMPLICIT_SCHEMA authority
 
Coordinating database partition number     = 0
 
Current database partition number          = 0
 
Coordinator agent process or thread ID     = 17995
 
Agents stolen                              = 0
 
Agents waiting on locks                    = 0
 
Maximum associated agents                  = 1
 
Priority at which application agents work  = 0
 
Priority type                              = Dynamic
 
 
 
Lock timeout (seconds)                     = 600
 
Locks held by application                  = 6
 
Lock waits since connect                   = 2
 
Time application waited on locks (ms)      = 118
 
Deadlocks detected                         = 0
 
Lock escalations                           = 0
 
Exclusive lock escalations                 = 0
 
Number of Lock Timeouts since connected    = 0
 
Total time UOW waited on locks (ms)        = Not Collected
 
 
 
Total sorts                                = 32871
 
Total sort time (ms)                       = 195447
 
Total sort overflows                       = 23023
 
 
 
Data pages copied to extended storage      = Not Collected
 
Index pages copied to extended storage     = Not Collected
 
Data pages copied from extended storage    = Not Collected
 
Index pages copied from extended storage   = Not Collected
 
Buffer pool data logical reads             = Not Collected
 
Buffer pool data physical reads            = Not Collected
 
Buffer pool temporary data logical reads   = Not Collected
 
Buffer pool temporary data physical reads  = Not Collected
 
Buffer pool data writes                    = Not Collected
 
Buffer pool index logical reads            = Not Collected
 
Buffer pool index physical reads           = Not Collected
 
Buffer pool temporary index logical reads  = Not Collected
 
Buffer pool temporary index physical reads = Not Collected
 
Buffer pool index writes                   = Not Collected
 
Total buffer pool read time (milliseconds) = Not Collected
 
Total buffer pool write time (milliseconds)= Not Collected
 
Time waited for prefetch (ms)              = Not Collected
 
Unread prefetch pages                      = Not Collected
 
Direct reads                               = Not Collected
 
Direct writes                              = Not Collected
 
Direct read requests                       = Not Collected
 
Direct write requests                      = Not Collected
 
Direct reads elapsed time (ms)             = Not Collected
 
Direct write elapsed time (ms)             = Not Collected
 
 
 
Number of SQL requests since last commit   = 13
 
Commit statements                          = 4843
 
Rollback statements                        = 0
 
Dynamic SQL statements attempted           = 14429
 
Static SQL statements attempted            = 4843
 
Failed statement operations                = 250
 
Select SQL statements executed             = 10301
 
Update/Insert/Delete statements executed   = 586
 
DDL statements executed                    = 0
 
Inactive stmt history memory usage (bytes) = 0
 
Internal automatic rebinds                 = 0
 
Internal rows deleted                      = 0
 
Internal rows inserted                     = 0
 
Internal rows updated                      = 0
 
Internal commits                           = 1
 
Internal rollbacks                         = 0
 
Internal rollbacks due to deadlock         = 0
 
Binds/precompiles attempted                = 0
 
Rows deleted                               = 12
 
Rows inserted                              = 359
 
Rows updated                               = 193
 
Rows selected                              = 18834
 
Rows read                                  = 132554884
 
Rows written                               = 65974899
 
 
 
UOW log space used (Bytes)                 = Not Collected
 
Previous UOW completion timestamp          = Not Collected
 
Elapsed time of last completed uow (sec.ms)= Not Collected
 
UOW start timestamp                        = Not Collected
 
UOW stop timestamp                         = Not Collected
 
UOW completion status                      = Not Collected
 
 
 
Open remote cursors                        = 1
 
Open remote cursors with blocking          = 229
 
Rejected Block Remote Cursor requests      = 381
 
Accepted Block Remote Cursor requests      = 9920
 
Open local cursors                         = 0
 
Open local cursors with blocking           = 0
 
Total User CPU Time used by agent (s)      = 314.490000
 
Total System CPU Time used by agent (s)    = 343.250000
 
Host execution elapsed time                = 0.000039
 
 
 
Package cache lookups                      = 10372
 
Package cache inserts                      = 10
 
Application section lookups                = 14429
 
Application section inserts                = 291
 
Catalog cache lookups                      = 68
 
Catalog cache inserts                      = 0
 
Catalog cache overflows                    = 0
 
Catalog cache high water mark              = 0
 
 
 
Workspace Information
 
 
 
 Shared high water mark                    = 0
 
 Total shared overflows                    = 0
 
 Total shared section inserts              = 0
 
 Total shared section lookups              = 0
 
 Private high water mark                   = 3131289
 
 Total private overflows                   = 0
 
 Total private section inserts             = 291
 
 Total private section lookups             = 10349
 
 
 
Most recent operation                      = Fetch
 
Cursor name                                = SQL_CURSH200C45
 
Most recent operation start timestamp      = 03/11/2009 11:07:09.301911
 
Most recent operation stop timestamp       =
 
Agents associated with the application     = 1
 
Number of hash joins                       = 188
 
Number of hash loops                       = 0
 
Number of hash join overflows              = 0
 
Number of small hash join overflows        = 0
 
Statement type                             = Dynamic SQL Statement
 
Statement                                  = Fetch
 
Section number                             = 45
 
Application creator                        = NULLID
 
Package name                               = SYSSH200
 
Consistency Token                          = SYSLVL01
 
Package Version ID                         =
 
Cursor name                                = SQL_CURSH200C45
 
Statement database partition number        = 0
 
Statement start timestamp                  = 03/11/2009 11:07:09.301911
 
Statement stop timestamp                   =
 
Elapsed time of last completed stmt(sec.ms)= 0.000039
 
Total Statement user CPU time              = 311.150000
 
Total Statement system CPU time            = 340.690000
 
SQL compiler cost estimate in timerons     = 36689
 
SQL compiler cardinality estimate          = 881
 
Degree of parallelism requested            = 1
 
Number of agents working on statement      = 1
 
Number of subagents created for statement  = 1
 
Statement sorts                            = 23009
 
Total sort time                            = 194497
 
Sort overflows                             = 23009
 
Rows read                                  = 131960949
 
Rows written                               = 65969670
 
Rows deleted                               = 0
 
Rows updated                               = 0
 
Rows inserted                              = 0
 
Rows fetched                               = 0
 
Buffer pool data logical reads             = Not Collected
 
Buffer pool data physical reads            = Not Collected
 
Buffer pool temporary data logical reads   = Not Collected
 
Buffer pool temporary data physical reads  = Not Collected
 
Buffer pool index logical reads            = Not Collected
 
Buffer pool index logical reads            = Not Collected
 
Buffer pool temporary index logical reads  = Not Collected
 
Buffer pool temporary index physical reads = Not Collected
 
Blocking cursor                            = YES
 
Dynamic SQL statement text:
 
SELECT IMCVEHICLES.VID, YEAR, MAKE_CODE, MAKE_DESCRIPTION, MODEL_CODE, MODEL_DESCRIPTION, SUBMODEL_DESCRIPTION,ENGINE_TYPE, LITER, FUEL, FUEL_DELIVERY, ASPIRATION FROM IMCVEHICLES, IMCVIN WHERE IMCVIN.VIN LIKE ? AND (IMCVEHICLES.VID = IMCVIN.VID1 OR IMCVEHICLES.VID = IMCVIN.VID2 OR IMCVEHICLES.VID = IMCVIN.VID3 OR IMCVEHICLES.VID = IMCVIN.VID4 OR IMCVEHICLES.VID = IMCVIN.VID5 OR IMCVEHICLES.VID = IMCVIN.VID6 OR IMCVEHICLES.VID = IMCVIN.VID7) AND DISPLAYABLE_FLAG = 1 ORDER BY MODEL_DESCRIPTION, LITER, ENGINE_TYPE, FUEL, FUEL_DELIVERY, ASPIRATION FOR FETCH ONLY
 
 
 
    Agent process/thread ID                       = 17995
 
 
 
Agent process/thread ID                    = 17995
 
  Agent Lock timeout (seconds)             = 600
 
  Memory usage for agent:
 
 
 
    Memory Pool Type                       = Application Control Heap
 
       Current size (bytes)                = 32768
 
       High water mark (bytes)             = 49152
 
       Configured size (bytes)             = 33734656
 
 
 
    Memory Pool Type                       = Application Heap
 
       Current size (bytes)                = 3768320
 
       High water mark (bytes)             = 3899392
 
       Configured size (bytes)             = 8617984

Open in new window

vvsrk76

ASKER

Please help me in this.

Kent Olsen

Hi vvsrk76,

I've reformatted your script so that it's readable. (Formatting and indenting are your friends.....)

I'm assuming that the join key(s) is/are the only duplicated column names between the tables. If not, the join is doing a lot more work that it needs to.

The join operation builds a derived table that is the result of merging two entire tables. My guess is that you're looking at an awful lot of data in the two tables. After the tables are joined you filter on colums INCVIN.VIN and IMCVEHICLES.VID. The filter operation requires a full table scan of the derived table. Even with indexing of the two base tables, it is likely that the equivalent of full table scans are required to build the derived table.

The query seems to 'know' that it only wants data for a few vehicles. Filter by the VIN first, then join the tables.

Good Luck,
Kent

--
--  Original
--
SELECT 
  IMCVEHICLES.VID, 
  YEAR, 
  MAKE_CODE, 
  MAKE_DESCRIPTION, 
  MODEL_CODE, 
  MODEL_DESCRIPTION, 
  SUBMODEL_DESCRIPTION,
  ENGINE_TYPE, 
  LITER, 
  FUEL, 
  FUEL_DELIVERY, 
  ASPIRATION 
FROM IMCVEHICLES, IMCVIN 
WHERE IMCVIN.VIN LIKE ? 
  AND (IMCVEHICLES.VID = IMCVIN.VID1 
   OR IMCVEHICLES.VID = IMCVIN.VID2 
   OR IMCVEHICLES.VID = IMCVIN.VID3 
   OR IMCVEHICLES.VID = IMCVIN.VID4 
   OR IMCVEHICLES.VID = IMCVIN.VID5 
   OR IMCVEHICLES.VID = IMCVIN.VID6 
   OR IMCVEHICLES.VID = IMCVIN.VID7)
  AND DISPLAYABLE_FLAG = 1 
ORDER BY MODEL_DESCRIPTION, LITER, ENGINE_TYPE, FUEL, FUEL_DELIVERY, ASPIRATION 
FOR FETCH ONLY
--
-- New query
--
SELECT 
  IMCVEHICLES.VID, 
  YEAR, 
  MAKE_CODE, 
  MAKE_DESCRIPTION, 
  MODEL_CODE, 
  MODEL_DESCRIPTION, 
  SUBMODEL_DESCRIPTION,
  ENGINE_TYPE, 
  LITER, 
  FUEL, 
  FUEL_DELIVERY, 
  ASPIRATION 
FROM 
(
  SELECT * FROM imcvin WHERE vin LIKE ?
) imcvin, imcvehicles
 
WHERE
  AND (IMCVEHICLES.VID = IMCVIN.VID1 
   OR IMCVEHICLES.VID = IMCVIN.VID2 
   OR IMCVEHICLES.VID = IMCVIN.VID3 
   OR IMCVEHICLES.VID = IMCVIN.VID4 
   OR IMCVEHICLES.VID = IMCVIN.VID5 
   OR IMCVEHICLES.VID = IMCVIN.VID6 
   OR IMCVEHICLES.VID = IMCVIN.VID7)
  AND DISPLAYABLE_FLAG = 1 
ORDER BY MODEL_DESCRIPTION, LITER, ENGINE_TYPE, FUEL, FUEL_DELIVERY, ASPIRATION 
FOR FETCH ONLY

Open in new window

vvsrk76

ASKER

Thank you.
I am going to test the new query performance.

Thank you once again.

vvsrk76

ASKER

No luck for me. I used prepared statement in my java code(Takes Long time).
Same query runs fine(20ms) when I run in DB2 Command Editor.
Below is my Java Code

c = getConnection();
                  System.out.println("BeforePrepare: " + vinMatch);
                  ps = c.prepareStatement(sql);
                  System.out.println("AfterPrepare: " + vinMatch);
                  ps.setObject(1, vinMatch.toString());
                  System.out.println("BeforeExcute: " + vinMatch);
                  rs = ps.executeQuery();
                  System.out.println("AfterExcute: " + vinMatch);

This is the Output in my console

VIN: %ACDJ58V_V_______
[3/13/09 8:03:17:256 PDT] 00000034 SystemOut O BeforeVINSQL: SELECT IMCVEHICLES.VID, YEAR, MAKE_CODE, MAKE_DESCRIPTION, MODEL_CODE, MODEL_DESCRIPTION, SUBMODEL_DESCRIPTION,ENGINE_TYPE, LITER, FUEL, FUEL_DELIVERY, ASPIRATION FROM (SELECT * FROM IMCVIN WHERE VIN LIKE ?) IMCVIN, IMCVEHICLES WHERE (IMCVEHICLES.VID = IMCVIN.VID1 OR IMCVEHICLES.VID = IMCVIN.VID2 OR IMCVEHICLES.VID = IMCVIN.VID3 OR IMCVEHICLES.VID = IMCVIN.VID4 OR IMCVEHICLES.VID = IMCVIN.VID5 OR IMCVEHICLES.VID = IMCVIN.VID6 OR IMCVEHICLES.VID = IMCVIN.VID7) AND DISPLAYABLE_FLAG = 1 ORDER BY MODEL_DESCRIPTION, LITER, ENGINE_TYPE, FUEL, FUEL_DELIVERY, ASPIRATION FOR FETCH ONLY
[3/13/09 8:03:17:256 PDT] 00000034 SystemOut O BeforePrepare: %ACDJ58V_V_______
[3/13/09 8:03:17:256 PDT] 00000034 SystemOut O AfterPrepare: %ACDJ58V_V_______
[3/13/09 8:03:17:256 PDT] 00000034 SystemOut O BeforeExcute: %ACDJ58V_V_______
[3/13/09 8:15:28:738 PDT] 00000024 ThreadMonitor W WSVR0605W: Thread "WebContainer : 1" (00000034) has been active for 731529 milliseconds and may be hung. There is/are 1 thread(s) in total in the server that may be hung.

SELECT 
  IMCVEHICLES.VID, 
  YEAR, 
  MAKE_CODE, 
  MAKE_DESCRIPTION, 
  MODEL_CODE, 
  MODEL_DESCRIPTION, 
  SUBMODEL_DESCRIPTION,
  ENGINE_TYPE, 
  LITER, 
  FUEL, 
  FUEL_DELIVERY, 
  ASPIRATION 
FROM 
(
  SELECT * FROM imcvin WHERE vin LIKE '%ACDJ58V_V_______'
) imcvin, imcvehicles
 
WHERE
  AND (IMCVEHICLES.VID = IMCVIN.VID1 
   OR IMCVEHICLES.VID = IMCVIN.VID2 
   OR IMCVEHICLES.VID = IMCVIN.VID3 
   OR IMCVEHICLES.VID = IMCVIN.VID4 
   OR IMCVEHICLES.VID = IMCVIN.VID5 
   OR IMCVEHICLES.VID = IMCVIN.VID6 
   OR IMCVEHICLES.VID = IMCVIN.VID7)
  AND DISPLAYABLE_FLAG = 1 
ORDER BY MODEL_DESCRIPTION, LITER, ENGINE_TYPE, FUEL, FUEL_DELIVERY, ASPIRATION 
FOR FETCH ONLY

Open in new window

vvsrk76

ASKER

is there any Performance issue in prepared statement with sql function "LIKE" ?

Thanks

Member_2_2484401

> is there any Performance issue in prepared statement with sql function "LIKE" ?

In general, the answer is YES. Using a LIKE comparison essentially disallows the use of most indexes ( especially when the wildcards are at the beginning of your comparison string). That can seriously slow down a query.

If you can do without the LIKE, you will be much better off.

Additionally, using the OR in the way that you are will also reduce the usefulness of indexes since it must check each condition separately for each row.

HTH,
DaveSlash

Kent Olsen

Executing a prepared statement is essentially the same as send SQL to DB2 from the command center. I would expect nearly identical performance.

My take is that there's a mismatch between what the JAVA code is sending and what DB2 is receiving. It's almost like DB2 is just waiting for the rest of the query to be sent.

Kent

vvsrk76

ASKER

In my application I have to use 'like'
Is there any solution to reduce the load?

ASKER CERTIFIED SOLUTION

Kent Olsen

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

vvsrk76

ASKER

Please help me .It is urgent.
It's high load on production. we have to fix this issue.
Please suggest any solution.

Thanks for your valuable time.

Kent Olsen

Hi vvsrk76,

In your Java code, you print out the value of the filter '%ACDJ58V_V_______'. Can you change that (or add more) to print out the SQL being submitted?

Kent

vvsrk76

ASKER

Below are system out SystemOut Statements

O VIN: %ACDJ58V_V_______
[3/13/09 9:59:41:788 PDT] 00000035 SystemOut O BeforeVINSQL: SELECT IMCVEHICLES.VID, YEAR, MAKE_CODE, MAKE_DESCRIPTION, MODEL_CODE, MODEL_DESCRIPTION, SUBMODEL_DESCRIPTION,ENGINE_TYPE, LITER, FUEL, FUEL_DELIVERY, ASPIRATION FROM (SELECT * FROM IMCVIN WHERE VIN LIKE ?) IMCVIN, IMCVEHICLES WHERE DISPLAYABLE_FLAG = 1 AND (IMCVEHICLES.VID = IMCVIN.VID1 OR IMCVEHICLES.VID = IMCVIN.VID2 OR IMCVEHICLES.VID = IMCVIN.VID3 OR IMCVEHICLES.VID = IMCVIN.VID4 OR IMCVEHICLES.VID = IMCVIN.VID5 OR IMCVEHICLES.VID = IMCVIN.VID6 OR IMCVEHICLES.VID = IMCVIN.VID7) ORDER BY MODEL_DESCRIPTION, LITER, ENGINE_TYPE, FUEL, FUEL_DELIVERY, ASPIRATION FOR FETCH ONLY
[3/13/09 9:59:41:788 PDT] 00000035 SystemOut O BeforePrepare: %ACDJ58V_V_______
[3/13/09 9:59:41:788 PDT] 00000035 SystemOut O AfterPrepare: %ACDJ58V_V_______
[3/13/09 9:59:41:788 PDT] 00000035 SystemOut O BeforeExcute: %ACDJ58V_V_______
[3/13/09 10:11:51:044 PDT] 00000027 ThreadMonitor W WSVR0605W: Thread "WebContainer : 0" (00000035) has been active for 729303 milliseconds and may be hung. There is/are 1 thread(s) in total in the server that may be hung.
[3/13/09 10:19:41:750 PDT] 00000011 TimeoutManage I WTRN0006W: Transaction 0000012000C65F2D00000005000000495CEC4A5BA2664D406F6FD9C04AE7A1C0174F15A80000012000C65F2D00000005000000495CEC4A5BA2664D406F6FD9C04AE7A1C0174F15A800000001 has timed out after 1200 seconds.

Kent Olsen

Hi vvsrk76,

Change this one line of Java:

ps.setObject(1, vinMatch.toString());

to:

ps.setString(1, vinMatch.toString());

Kent

vvsrk76

ASKER

Sorry to take your valuable time.
In my production data base load is high. please provide any solution to me.

vvsrk76

ASKER

Thank you.

I will try this one.

vvsrk76

ASKER

No Luck.

vvsrk76

ASKER

Please help me in this

vvsrk76

ASKER

Thank you kent

Mick Barry

how does it run if you don't use a PreparedStatement?

vvsrk76

ASKER

Hi Kent,

Hi,
When I use below query it will only take 1 second(With Prepared statement only "like and ?").

SELECT
IMCVEHICLES.VID,
YEAR,
MAKE_CODE,
MAKE_DESCRIPTION,
MODEL_CODE,
MODEL_DESCRIPTION,
SUBMODEL_DESCRIPTION,
ENGINE_TYPE,
LITER,
FUEL,
FUEL_DELIVERY,
ASPIRATION
FROM IMCVEHICLES, IMCVIN
WHERE DISPLAYABLE_FLAG = 1 AND IMCVIN.VIN LIKE ?
AND IMCVEHICLES.VID = IMCVIN.VID1
ORDER BY MODEL_DESCRIPTION, LITER, ENGINE_TYPE, FUEL, FUEL_DELIVERY, ASPIRATION
FOR FETCH ONLY

If I add this one to my query it will take a quite long time (several minutes)

AND (IMCVEHICLES.VID = IMCVIN.VID1
OR IMCVEHICLES.VID = IMCVIN.VID2
OR IMCVEHICLES.VID = IMCVIN.VID3
OR IMCVEHICLES.VID = IMCVIN.VID4
OR IMCVEHICLES.VID = IMCVIN.VID5
OR IMCVEHICLES.VID = IMCVIN.VID6
OR IMCVEHICLES.VID = IMCVIN.VID7)

Please help me in this.

ghp7000

um, you say you created the indexes but did you do a runstats on the table?
runstats on table <table name> with distribution and detailed indexes all
commit
re run the code