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 SnapshotApplication handle = 984Application status = UOW ExecutingStatus change time = Not CollectedApplication code page = 1208Application country/region code = 1DUOW correlation token = GAFA0517.D8B0.0191A1174849Application name = javaApplication ID = GAFA0517.D8B0.0191A1174849Sequence number = 4844TP 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.999422Connect request completion timestamp = 03/11/2009 10:48:49.999621Application idle time =CONNECT Authorization ID = IMCCRPClient login ID = wasadminConfiguration NNAME of client = usamt12ux13appClient database manager product ID = SQL08028Process ID of client application = 11642Platform of client application = LINUXCommunication protocol of client = TCP/IPInbound communication address = 10.250.5.23 55472Database name = RPRODIMCDatabase path = /media/SAN/disk1/db2inst1/db2inst1/NODE0000/SQL00005/Client database alias = PRODIMCInput database alias =Last reset timestamp =Snapshot timestamp = 03/11/2009 11:18:03.340926The 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 authorityCoordinating database partition number = 0Current database partition number = 0Coordinator agent process or thread ID = 17995Agents stolen = 0Agents waiting on locks = 0Maximum associated agents = 1Priority at which application agents work = 0Priority type = DynamicLock timeout (seconds) = 600Locks held by application = 6Lock waits since connect = 2Time application waited on locks (ms) = 118Deadlocks detected = 0Lock escalations = 0Exclusive lock escalations = 0Number of Lock Timeouts since connected = 0Total time UOW waited on locks (ms) = Not CollectedTotal sorts = 32871Total sort time (ms) = 195447Total sort overflows = 23023Data pages copied to extended storage = Not CollectedIndex pages copied to extended storage = Not CollectedData pages copied from extended storage = Not CollectedIndex pages copied from extended storage = Not CollectedBuffer pool data logical reads = Not CollectedBuffer pool data physical reads = Not CollectedBuffer pool temporary data logical reads = Not CollectedBuffer pool temporary data physical reads = Not CollectedBuffer pool data writes = Not CollectedBuffer pool index logical reads = Not CollectedBuffer pool index physical reads = Not CollectedBuffer pool temporary index logical reads = Not CollectedBuffer pool temporary index physical reads = Not CollectedBuffer pool index writes = Not CollectedTotal buffer pool read time (milliseconds) = Not CollectedTotal buffer pool write time (milliseconds)= Not CollectedTime waited for prefetch (ms) = Not CollectedUnread prefetch pages = Not CollectedDirect reads = Not CollectedDirect writes = Not CollectedDirect read requests = Not CollectedDirect write requests = Not CollectedDirect reads elapsed time (ms) = Not CollectedDirect write elapsed time (ms) = Not CollectedNumber of SQL requests since last commit = 13Commit statements = 4843Rollback statements = 0Dynamic SQL statements attempted = 14429Static SQL statements attempted = 4843Failed statement operations = 250Select SQL statements executed = 10301Update/Insert/Delete statements executed = 586DDL statements executed = 0Inactive stmt history memory usage (bytes) = 0Internal automatic rebinds = 0Internal rows deleted = 0Internal rows inserted = 0Internal rows updated = 0Internal commits = 1Internal rollbacks = 0Internal rollbacks due to deadlock = 0Binds/precompiles attempted = 0Rows deleted = 12Rows inserted = 359Rows updated = 193Rows selected = 18834Rows read = 132554884Rows written = 65974899UOW log space used (Bytes) = Not CollectedPrevious UOW completion timestamp = Not CollectedElapsed time of last completed uow (sec.ms)= Not CollectedUOW start timestamp = Not CollectedUOW stop timestamp = Not CollectedUOW completion status = Not CollectedOpen remote cursors = 1Open remote cursors with blocking = 229Rejected Block Remote Cursor requests = 381Accepted Block Remote Cursor requests = 9920Open local cursors = 0Open local cursors with blocking = 0Total User CPU Time used by agent (s) = 314.490000Total System CPU Time used by agent (s) = 343.250000Host execution elapsed time = 0.000039Package cache lookups = 10372Package cache inserts = 10Application section lookups = 14429Application section inserts = 291Catalog cache lookups = 68Catalog cache inserts = 0Catalog cache overflows = 0Catalog cache high water mark = 0Workspace 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 = 10349Most recent operation = FetchCursor name = SQL_CURSH200C45Most recent operation start timestamp = 03/11/2009 11:07:09.301911Most recent operation stop timestamp =Agents associated with the application = 1Number of hash joins = 188Number of hash loops = 0Number of hash join overflows = 0Number of small hash join overflows = 0Statement type = Dynamic SQL StatementStatement = FetchSection number = 45Application creator = NULLIDPackage name = SYSSH200Consistency Token = SYSLVL01Package Version ID =Cursor name = SQL_CURSH200C45Statement database partition number = 0Statement start timestamp = 03/11/2009 11:07:09.301911Statement stop timestamp =Elapsed time of last completed stmt(sec.ms)= 0.000039Total Statement user CPU time = 311.150000Total Statement system CPU time = 340.690000SQL compiler cost estimate in timerons = 36689SQL compiler cardinality estimate = 881Degree of parallelism requested = 1Number of agents working on statement = 1Number of subagents created for statement = 1Statement sorts = 23009Total sort time = 194497Sort overflows = 23009Rows read = 131960949Rows written = 65969670Rows deleted = 0Rows updated = 0Rows inserted = 0Rows fetched = 0Buffer pool data logical reads = Not CollectedBuffer pool data physical reads = Not CollectedBuffer pool temporary data logical reads = Not CollectedBuffer pool temporary data physical reads = Not CollectedBuffer pool index logical reads = Not CollectedBuffer pool index logical reads = Not CollectedBuffer pool temporary index logical reads = Not CollectedBuffer pool temporary index physical reads = Not CollectedBlocking cursor = YESDynamic 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 = 17995Agent 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
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, imcvehiclesWHERE 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
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
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, imcvehiclesWHERE 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
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.
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?
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.
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