Joined: 26 May 2005 Posts: 178 Location: Copenhagen, Denmark
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.
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