Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

Load on DB2

Avatar of vvsrk76
vvsrk76 asked on
JavaDB2
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
Avatar of Kent Olsen
Kent OlsenFlag of United States of America imageData Warehouse / Database Architect

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

Commented:
This problem has been solved!
Unlock 1 Answer and 21 Comments.
See Answers