IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Query takes 67% of the CPU


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
vini_srcna

Active User


Joined: 26 May 2005
Posts: 178
Location: Copenhagen, Denmark

PostPosted: Fri Dec 28, 2007 6:24 pm
Reply with quote

Hi All,

I have a complaint from DBA saying that one the queries takes 67% of the CPU. Query has left outer join. I copied this query from one of the existing packages to do the same function. This query is used in online program.

DBA has sent me the following information which i could not decode.

SQL_CALL STMTÆ SECTÆ SQL TIMEPCT CPUPCT INDB2_TIME INDB2_CPU
-------- ----- ----- ---------- ------- ------- ------------ ------------
SELECT 01451 00002 5476504 22.09% 39.64% 00:00.000232 00:00.000090
OPEN 01390 00001 12810 63.09% 31.13% 00:00.283133 00:00.030186
SELECT 01490 00004 5431293 6.23% 11.79% 00:00.000066 00:00.000027
SELECT 01473 00003 5476504 2.76% 10.13% 00:00.000029 00:00.000023
FETCH 01550 00001 5431746 1.22% 5.24% 00:00.000013 00:00.000012
SELECT 01519 00005 107072 4.58% 1.92% 00:00.002461 00:00.000223
CLOSE 01564 00001 12811 .00% .00% 00:00.000009 00:00.000008

I dont event know what those columns say. The query is pasted below. query uses the synonyms instead of the qulaified tables.

DECLARE ERID_CURSOR CURSOR FOR
SELECT DISTINCT B.INV_ID
FROM ORG_STRUKTUR_S A
LEFT OUTER JOIN IP_IP_REL_S B
ON SUBSTR(A.IP_ID,1,10) = B.INV_ID_2
WHERE B.IR_KD = 'PRIMAER'
AND (CURRENT TIMESTAMP <= B.TIL_TS OR B.TIL_TS IS NULL)
AND A.OEST_NV = 'RAADGIV'
AND A.OEST_REL_TP IN ('DIREKTE','SAMLAGT','STAB')
AND A.IP_ID_2 = :USER_ID;

DBA says its at the OPEN statement where the query is actually executed. EXPLAIN says it is going for Index scan with MATCHCOLS = 3 for first table and MATCHCOLS = 1 for the second table. COST_CATEGORY is B and i guess its because of RANGE predicates in the WHERE clause and Host variable. (Reason for COST CATEGORY is Host variable).

Do anyone of you has any idea about the data DBA has sent..?. Is that from any of the monitor tools..?. Does COST_TABLE, STRUCTURE_TABLE, PREDICATE_TABLE has something to do with EXPLAIN in DB2 V8 for Z/os..?.

Sorry for lots of questions. I still have lots of questions. Performance tuning is always been a difficult job for me. Please help me with this issue.

Here is the explain results:

PLANNO METHOD TNAME ACCESSTYPE MATCHCOLS INDEXONLY
------ ------ ------------------ ---------- --------- ---------
1 0 ORG_STRUKTUR_S N 3 Y
2 1 IP_IP_REL_S I 1 N
3 3 0 N
Back to top
View user's profile Send private message
PeD

Active User


Joined: 26 Nov 2005
Posts: 459
Location: Belgium

PostPosted: Fri Dec 28, 2007 6:28 pm
Reply with quote

For the clearness then to be more prepared to help, please use the tags ( code /code).
Back to top
View user's profile Send private message
vini_srcna

Active User


Joined: 26 May 2005
Posts: 178
Location: Copenhagen, Denmark

PostPosted: Fri Dec 28, 2007 6:43 pm
Reply with quote

Thanks PeD.

Here it is in the Code format.

Code:
SQL_CALL  STMTÆ  SECTÆ  SQL        TIMEPCT CPUPCT  INDB2_TIME   INDB2_CPU   
--------  -----  -----  ---------- ------- ------- ------------ ------------
SELECT    01451  00002     5476504  22.09%  39.64% 00:00.000232 00:00.000090
OPEN      01390  00001       12810  63.09%  31.13% 00:00.283133 00:00.030186
SELECT    01490  00004     5431293   6.23%  11.79% 00:00.000066 00:00.000027
SELECT    01473  00003     5476504   2.76%  10.13% 00:00.000029 00:00.000023
FETCH     01550  00001     5431746   1.22%   5.24% 00:00.000013 00:00.000012
SELECT    01519  00005      107072   4.58%   1.92% 00:00.002461 00:00.000223
CLOSE     01564  00001       12811    .00%    .00% 00:00.000009 00:00.000008


Explain results:

Code:
PLANNO  METHOD  TNAME                  TABNO  ACCESSTYPE  MATCHCOLS  INDEXONL
 ------  ------  --------------------  ------  ----------  ---------  --------
      1       0  ORG_STRUKTUR_S             1  N                   3  Y       
      2       1  IP_IP_REL_S                2  I                   1  N       
      3       3                             0                      0  N       


The no of rows in the tables are:

IP_IP_REL_S: 18450790
ORG_STRUKTUR_S: 68652
Back to top
View user's profile Send private message
socker_dad

Active User


Joined: 05 Dec 2006
Posts: 177
Location: Seattle, WA

PostPosted: Mon Dec 31, 2007 10:28 pm
Reply with quote

To help us help you, please give the offending query/queries, as well as table information, including keys and foreign keys.

This will help us better analyze your query and make intelligent suggestions.
Back to top
View user's profile Send private message
jontyjun

New User


Joined: 12 Sep 2006
Posts: 12
Location: India

PostPosted: Thu Jan 03, 2008 2:10 pm
Reply with quote

Hi vini_srcna,

Please share the info if you understood the DBA Results.

Regards,
Arjun.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts RC query -Time column CA Products 3
No new posts Dynamically pass table name to a sele... DB2 2
No new posts Query on edit primary command CLIST & REXX 5
No new posts Query on edit primary command CLIST & REXX 1
No new posts Issue with EXEC CICS QUERY SECURITY c... CICS 6
Search our Forums:

Back to Top