View previous topic :: View next topic
|
Author |
Message |
vini_srcna
Active User
Joined: 26 May 2005 Posts: 178 Location: Copenhagen, Denmark
|
|
|
|
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 |
|
|
PeD
Active User
Joined: 26 Nov 2005 Posts: 459 Location: Belgium
|
|
|
|
For the clearness then to be more prepared to help, please use the tags ( code /code). |
|
Back to top |
|
|
vini_srcna
Active User
Joined: 26 May 2005 Posts: 178 Location: Copenhagen, Denmark
|
|
|
|
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 |
|
|
socker_dad
Active User
Joined: 05 Dec 2006 Posts: 177 Location: Seattle, WA
|
|
|
|
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 |
|
|
jontyjun
New User
Joined: 12 Sep 2006 Posts: 12 Location: India
|
|
|
|
Hi vini_srcna,
Please share the info if you understood the DBA Results.
Regards,
Arjun. |
|
Back to top |
|
|
|