troubleshooting Question
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,ENGIN
I tried putting indexes on VID1,&VID7 didnt help.
I am attaching my snapshot.
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,ENGIN
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
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.
Our community of experts have been thoroughly vetted for their expertise and industry experience.