troubleshooting Question

Load on DB2

Avatar of vvsrk76
vvsrk76 asked on
DB2Java
21 Comments1 Solution1003 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
Kent Olsen
Data Warehouse / Database Architect

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 21 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 21 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros