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,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
DB2Java
Last Comment
ghp7000
ASKER
Please help me in this.
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
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
ASKER
Thank you.
I am going to test the new query performance.
Thank you once again.
I am going to test the new query performance.
Thank you once again.
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("Before
ps = c.prepareStatement(sql);
System.out.println("AfterP
ps.setObject(1, vinMatch.toString());
System.out.println("Before
rs = ps.executeQuery();
System.out.println("AfterE
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,ENGIN
[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.
Same query runs fine(20ms) when I run in DB2 Command Editor.
Below is my Java Code
c = getConnection();
System.out.println("Before
ps = c.prepareStatement(sql);
System.out.println("AfterP
ps.setObject(1, vinMatch.toString());
System.out.println("Before
rs = ps.executeQuery();
System.out.println("AfterE
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,ENGIN
[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
ASKER
is there any Performance issue in prepared statement with sql function "LIKE" ?
Thanks
Thanks
> 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
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
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
ASKER
In my application I have to use 'like'
Is there any solution to reduce the load?
Is there any solution to reduce the load?
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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.
It's high load on production. we have to fix this issue.
Please suggest any solution.
Thanks for your valuable time.
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
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
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,ENGIN
[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 0000012000C65F2D0000000500
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,ENGIN
[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 0000012000C65F2D0000000500
Hi vvsrk76,
Change this one line of Java:
ps.setObject(1, vinMatch.toString());
to:
ps.setString(1, vinMatch.toString());
Kent
Change this one line of Java:
ps.setObject(1, vinMatch.toString());
to:
ps.setString(1, vinMatch.toString());
Kent
ASKER
Sorry to take your valuable time.
In my production data base load is high. please provide any solution to me.
In my production data base load is high. please provide any solution to me.
ASKER
Thank you.
I will try this one.
I will try this one.
ASKER
No Luck.
ASKER
Please help me in this
ASKER
Thank you kent
how does it run if you don't use a PreparedStatement?
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.
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.
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
runstats on table <table name> with distribution and detailed indexes all
commit
re run the code