Anything to optimize from this SQL ?

Hello

Anything to optimize from this SQL ?
hi all ,

I have an SQL statement which takes 9' to execute in normal time ,
unfortunately it is executed at night (in a test environment) at peak CPU
used period , and it takes nearly 2 hours to be executed (the job is 90% on
CPU delay) , i've tried to add indexes on the columns but they are not
enough filtering and are not used.

The SQL is :

SELECT B.MOT_DIR_R , B.MOT_DIR , A.C_VAL_AFC , B.D_DEB_VALI_2
FROM ER.VVA508 A , ER.VVA532 B
WHERE A.C_EMET = B.C_EMET
AND A.I_VAL_SICO = 'O'
AND A.Y_STA_COD <> 'A'

The optimizer decides to use a merge scan join (so TS scan on the two
tablespace)

VVA508 is compressed , so i think that i should try to uncompress it
(COMP=NO) to avoid extra CPU consumption
Is there anything i should do ?

Thanks



Terry Purcell

Re: Anything to optimize from this SQL ?
(in response to Hello)
Christophe,

Here are a few questions (and one or two thoughts):

- Did you say 9 minutes vs 2 hours? If so, don't run it during peak time.

- Anything you can do to relieve the load during the peak period (has anyone
done a performance audit)?

- Is there any IF/THEN/ELSE logic in the program that can be added as WHERE
clause predicates in the query?

- What is the compression ratio?

- What indexes are out there (including column cardinalities and
clusterratios)?

- How many rows are in each table?

- What is the filtering (I know you said not enough) of A.I_VAL_SICO (value
'O') and A.Y_STA_COD (values <> 'A')?

- Does the join provide any filtering (do all rows in A have a match in B)?

- If you want to throw indexes at it, then these two will allow index only
and avoid sorts if DB2 chooses merge scan. Best use of index lookaside and
sequential detection also. But what is the impact to other processes?
On A: I_VAL_SICO, C_EMET, Y_STA_COD, C_VAL_AFC
On B: C_EMET, MOT_DIR_R , MOT_DIR , D_DEB_VALI_2

- Due to the tablespace scan of both tables, DB2 must sort both tables (A
after local predicates applied, and all of B). Are you confident that your
SORTPOOL, bufferpools, sort work database are all sized correctly for
optimal sorting?

Regards
Terry Purcell
Yevich Lawson & Assoc Inc (YL&A)
IBM Gold Consultant - DB2
IBM Certified Solutions Expert
DB2 V7 Database Administration OS/390
http://www.ylassoc.com


-----Original Message-----
From: DB2 Data Base Discussion List
[mailto:[login to unmask email]On Behalf Of Christophe Radier
Sent: Wednesday, January 08, 2003 11:20 AM
To: [login to unmask email]
Subject: Anything to optimize from this SQL ?


hi all ,

I have an SQL statement which takes 9' to execute in normal time ,
unfortunately it is executed at night (in a test environment) at peak CPU
used period , and it takes nearly 2 hours to be executed (the job is 90% on
CPU delay) , i've tried to add indexes on the columns but they are not
enough filtering and are not used.

The SQL is :

SELECT B.MOT_DIR_R , B.MOT_DIR , A.C_VAL_AFC , B.D_DEB_VALI_2
FROM ER.VVA508 A , ER.VVA532 B
WHERE A.C_EMET = B.C_EMET
AND A.I_VAL_SICO = 'O'
AND A.Y_STA_COD <> 'A'

The optimizer decides to use a merge scan join (so TS scan on the two
tablespace)

VVA508 is compressed , so i think that i should try to uncompress it
(COMP=NO) to avoid extra CPU consumption
Is there anything i should do ?

Thanks








John McKown

Re: Anything to optimize from this SQL ?
(in response to Terry Purcell)
> -----Original Message-----
> From: Christophe Radier [mailto:[login to unmask email]
> Sent: Wednesday, January 08, 2003 11:20 AM
> To: [login to unmask email]
> Subject: Anything to optimize from this SQL ?
>
>
> hi all ,
>
> I have an SQL statement which takes 9' to execute in normal time ,
> unfortunately it is executed at night (in a test environment)
> at peak CPU
> used period , and it takes nearly 2 hours to be executed (the
> job is 90% on
> CPU delay) , i've tried to add indexes on the columns but
> they are not
> enough filtering and are not used.
>
> The SQL is :
>
> SELECT B.MOT_DIR_R , B.MOT_DIR , A.C_VAL_AFC , B.D_DEB_VALI_2
> FROM ER.VVA508 A , ER.VVA532 B
> WHERE A.C_EMET = B.C_EMET
> AND A.I_VAL_SICO = 'O'
> AND A.Y_STA_COD <> 'A'
>
> The optimizer decides to use a merge scan join (so TS scan on the two
> tablespace)
>

This is not an answer to your question, but another question that your
question triggered in my, admitted ignorant, mind. Which of these SQL
queries is "better"? The one in the original message (see above) or the
following one, which I think will yield the same results.

SELECT B.MOT_DIR_R , B.MOT_DIR , A.C_VAL_AFC , B.D_DEB_VALI_2
FROM ER.VVA508 AS A
INNER JOIN ER.VVA532 AS B
ON A.C_EMET = B.C_EMET
WHERE A.I_VAL_SICO = 'O'
AND A.Y_STA_COD <> 'A'

I've been reading Joe Celko's "SQL for Smarties" and one of my newest "brain
storms" is to use the newer syntax.

--
John McKown
Senior Technical Specialist
UICI Insurance Center
Applications & Solutions Team
+1.817.255.3225



Terry Purcell

Re: Anything to optimize from this SQL ?
(in response to John McKown)
John,

For performance they are the same.

So it comes down to preference. Considerations include:
readability/documentation, conformance to outer join syntax, ability to
group ON clauses for each join etc.

I prefer the ON clause style, and I refer to it as the "explicit join
syntax". Not sure if I made that up myself or stole it. But I still
sometimes write queries the old way out of habit.

Regards
Terry Purcell
Yevich Lawson & Assoc Inc (YL&A)
IBM Gold Consultant - DB2
IBM Certified Solutions Expert
DB2 V7 Database Administration OS/390
http://www.ylassoc.com


-----Original Message-----
From: DB2 Data Base Discussion List
[mailto:[login to unmask email]On Behalf Of McKown, John
Sent: Wednesday, January 08, 2003 4:24 PM
To: [login to unmask email]
Subject: Re: Anything to optimize from this SQL ?


> -----Original Message-----
> From: Christophe Radier [mailto:[login to unmask email]
> Sent: Wednesday, January 08, 2003 11:20 AM
> To: [login to unmask email]
> Subject: Anything to optimize from this SQL ?
>
>
> hi all ,
>
> I have an SQL statement which takes 9' to execute in normal time ,
> unfortunately it is executed at night (in a test environment)
> at peak CPU
> used period , and it takes nearly 2 hours to be executed (the
> job is 90% on
> CPU delay) , i've tried to add indexes on the columns but
> they are not
> enough filtering and are not used.
>
> The SQL is :
>
> SELECT B.MOT_DIR_R , B.MOT_DIR , A.C_VAL_AFC , B.D_DEB_VALI_2
> FROM ER.VVA508 A , ER.VVA532 B
> WHERE A.C_EMET = B.C_EMET
> AND A.I_VAL_SICO = 'O'
> AND A.Y_STA_COD <> 'A'
>
> The optimizer decides to use a merge scan join (so TS scan on the two
> tablespace)
>

This is not an answer to your question, but another question that your
question triggered in my, admitted ignorant, mind. Which of these SQL
queries is "better"? The one in the original message (see above) or the
following one, which I think will yield the same results.

SELECT B.MOT_DIR_R , B.MOT_DIR , A.C_VAL_AFC , B.D_DEB_VALI_2
FROM ER.VVA508 AS A
INNER JOIN ER.VVA532 AS B
ON A.C_EMET = B.C_EMET
WHERE A.I_VAL_SICO = 'O'
AND A.Y_STA_COD <> 'A'

I've been reading Joe Celko's "SQL for Smarties" and one of my newest "brain
storms" is to use the newer syntax.

--
John McKown
Senior Technical Specialist
UICI Insurance Center
Applications & Solutions Team
+1.817.255.3225








Hello

Re: Anything to optimize from this SQL ?
(in response to Terry Purcell)
Hi all ,

Thank you for your differents advices and suggestions .

Here are some more informations on the problem :

We have 5 development environments and they runs in parallell on the same
machine -

So the 5 same programs ( accessing different databases - but on the same
DB2, DB2A) are executed at the same time.
There is no easy way to change it (it is what they said !)

I run the program alone , and it takes 9 minutes to run - At night , when
they are 5 they takes 2 hours.
The MVS monitor shows 95% waiting for CPU time for all these jobs.
The only SQL executed in the program brings back 4 millions rows.
These rows are put in a sequential file , and the next step is a LOAD
REPLACE LOG NO of another Table.

i noticed that the Load is also very slow : (40 to 80 minutes for 4
millions rows)
The reload phase took 25mn
The sort phase took 37mn
ICE091I 0 OUTPUT LRECL = 38, TYPE = F
ICE055I 0 INSERT 8662100, DELETE 8662100
ICE054I 0 RECORDS - IN: 0, OUT: 0
ICE134I 0 NUMBER OF BYTES SORTED: 329159800
ICE165I 0 TOTAL WORK DATA SET TRACKS ALLOCATED: 7650 , TRACKS USED: 1620
The Rebuild phase took 15 mn

Another thing i noticed is that on another DB2B, the same program , running
at the same time
on the same MVS the program runs only in 30mn. (So , we have 6 parallell
environments , sorry ..)


So my conclusion is that DB2A is overloaded , if i want it to absorb these
5 parallell SQL , i must
resize all the pools .

I will try with the index only access solution proposed by Terry this night
and keep you informed tomorrow.



Statistics info :



The compression rate is 66% for VVA508

356000 rows in VVA508 (11800 pages)
503772 rows in VVA532 (5800 pages)

- The SQL "brings back" 4 Millions rows

Filtering factors :

57788 rows with I_VAL_SICO= 'O'
351204 rows with I_STA_COD <> 'A'


Indexes :

TABLE (ER.VVA508)
I
+--- INDEX (ER.VVI5081) CLUST UNIQUE
I I
I +--- IXCOL (C_VAL_AFC)
I +--- IXCOL (C_VAL)
I
+--- INDEX (ER.VVI5082)
I I
I +--- IXCOL (C_VAL)
I
+--- INDEX (ER.VVI5083)
I I
I +--- IXCOL (C_EMET)
I
+--- INDEX (ER.VVI5084)
I I
I +--- IXCOL (D_MAJ)
I
+--- INDEX (ER.VVI5085)
I I
I +--- IXCOL (D_SRCE)
I +--- IXCOL (C_VAL_AFC)


TABLE (ER.VVA532)

+--- INDEX (ER.VVI5321) CLUST
I +--- IXCOL (MOT_DIR_R)
I +--- IXCOL (MOT_DIR)
I
+--- INDEX (ER.VVI5322)
I
+--- IXCOL (C_EMET)





NAME 1stKEYCARD FULLKEYCARD NLEAF NLVLS CLUSTRATIO
------------------ ---------- ----------- ---------- ----- ----------
VVI5081 356033 356033 2358 3 0.9842
VVI5082 256258 256258 1620 3 0.7650
VVI5083 106797 106797 756 3 0.6175
VVI5084 4181 4181 502 3 0.2112
VVI5085 4592 356033 1865 3 0.9020


VVI5321 24412 71824 1254 3 0.9998
VVI5322 109560 109560 967 3 0.1044

>>>>>>>>>>>>>
Christophe,

Here are a few questions (and one or two thoughts):

- Did you say 9 minutes vs 2 hours? If so, don't run it during peak time.

- Anything you can do to relieve the load during the peak period (has anyone
done a performance audit)?

- Is there any IF/THEN/ELSE logic in the program that can be added as WHERE
clause predicates in the query?

- What is the compression ratio?

- What indexes are out there (including column cardinalities and
clusterratios)?

- How many rows are in each table?

- What is the filtering (I know you said not enough) of A.I_VAL_SICO (value
'O') and A.Y_STA_COD (values <> 'A')?

- Does the join provide any filtering (do all rows in A have a match in B)?

- If you want to throw indexes at it, then these two will allow index only
and avoid sorts if DB2 chooses merge scan. Best use of index lookaside and
sequential detection also. But what is the impact to other processes?
On A: I_VAL_SICO, C_EMET, Y_STA_COD, C_VAL_AFC
On B: C_EMET, MOT_DIR_R , MOT_DIR , D_DEB_VALI_2

- Due to the tablespace scan of both tables, DB2 must sort both tables (A
after local predicates applied, and all of B). Are you confident that your
SORTPOOL, bufferpools, sort work database are all sized correctly for
optimal sorting?

Regards
Terry Purcell
Yevich Lawson & Assoc Inc (YL&A)
IBM Gold Consultant - DB2
IBM Certified Solutions Expert
DB2 V7 Database Administration OS/390
http://www.ylassoc.com


-----Original Message-----
From: DB2 Data Base Discussion List
[mailto:[login to unmask email]On Behalf Of Christophe Radier
Sent: Wednesday, January 08, 2003 11:20 AM
To: [login to unmask email]
Subject: Anything to optimize from this SQL ?


hi all ,

I have an SQL statement which takes 9' to execute in normal time ,
unfortunately it is executed at night (in a test environment) at peak CPU
used period , and it takes nearly 2 hours to be executed (the job is 90% on
CPU delay) , i've tried to add indexes on the columns but they are not
enough filtering and are not used.

The SQL is :

SELECT B.MOT_DIR_R , B.MOT_DIR , A.C_VAL_AFC , B.D_DEB_VALI_2
FROM ER.VVA508 A , ER.VVA532 B
WHERE A.C_EMET = B.C_EMET
AND A.I_VAL_SICO = 'O'
AND A.Y_STA_COD <> 'A'

The optimizer decides to use a merge scan join (so TS scan on the two
tablespace)

VVA508 is compressed , so i think that i should try to uncompress it
(COMP=NO) to avoid extra CPU consumption
Is there anything i should do ?

Thanks



Hello

Re: Anything to optimize from this SQL ?
(in response to Nick Cianci)
Hi all ,

Thank you for your differents advices and suggestions .

Here are some more informations on the problem :

We have 5 development environments and they runs in parallell on the same
machine -

So the 5 same programs ( accessing different databases - but on the same
DB2, DB2A) are executed at the same time.
There is no easy way to change it (it is what they said !)

I run the program alone , and it takes 9 minutes to run - At night , when
they are 5 they takes 2 hours.
The MVS monitor shows 95% waiting for CPU time for all these jobs.
The only SQL executed in the program brings back 4 millions rows.
These rows are put in a sequential file , and the next step is a LOAD
REPLACE LOG NO of another Table.

i noticed that the Load is also very slow : (40 to 80 minutes for 4
millions rows)
The reload phase took 25mn
The sort phase took 37mn
ICE091I 0 OUTPUT LRECL = 38, TYPE = F
ICE055I 0 INSERT 8662100, DELETE 8662100
ICE054I 0 RECORDS - IN: 0, OUT: 0
ICE134I 0 NUMBER OF BYTES SORTED: 329159800
ICE165I 0 TOTAL WORK DATA SET TRACKS ALLOCATED: 7650 , TRACKS USED: 1620
The Rebuild phase took 15 mn

Another thing i noticed is that on another DB2B, the same program , running
at the same time
on the same MVS the program runs only in 30mn. (So , we have 6 parallell
environments , sorry ..)


So my conclusion is that DB2A is overloaded , if i want it to absorb these
5 parallell SQL , i must
resize all the pools .

I will try with the index only access solution proposed by Terry this night
and keep you informed tomorrow.



Statistics info :



The compression rate is 66% for VVA508

356000 rows in VVA508 (11800 pages)
503772 rows in VVA532 (5800 pages)

- The SQL "brings back" 4 Millions rows

Filtering factors :

57788 rows with I_VAL_SICO= 'O'
351204 rows with I_STA_COD <> 'A'


Indexes :

TABLE (ER.VVA508)
I
+--- INDEX (ER.VVI5081) CLUST UNIQUE
I I
I +--- IXCOL (C_VAL_AFC)
I +--- IXCOL (C_VAL)
I
+--- INDEX (ER.VVI5082)
I I
I +--- IXCOL (C_VAL)
I
+--- INDEX (ER.VVI5083)
I I
I +--- IXCOL (C_EMET)
I
+--- INDEX (ER.VVI5084)
I I
I +--- IXCOL (D_MAJ)
I
+--- INDEX (ER.VVI5085)
I I
I +--- IXCOL (D_SRCE)
I +--- IXCOL (C_VAL_AFC)


TABLE (ER.VVA532)

+--- INDEX (ER.VVI5321) CLUST
I +--- IXCOL (MOT_DIR_R)
I +--- IXCOL (MOT_DIR)
I
+--- INDEX (ER.VVI5322)
I
+--- IXCOL (C_EMET)





NAME 1stKEYCARD FULLKEYCARD NLEAF NLVLS CLUSTRATIO
------------------ ---------- ----------- ---------- ----- ----------
VVI5081 356033 356033 2358 3 0.9842
VVI5082 256258 256258 1620 3 0.7650
VVI5083 106797 106797 756 3 0.6175
VVI5084 4181 4181 502 3 0.2112
VVI5085 4592 356033 1865 3 0.9020


VVI5321 24412 71824 1254 3 0.9998
VVI5322 109560 109560 967 3 0.1044

>>>>>>>>>>>>>
Christophe,

Here are a few questions (and one or two thoughts):

- Did you say 9 minutes vs 2 hours? If so, don't run it during peak time.

- Anything you can do to relieve the load during the peak period (has anyone
done a performance audit)?

- Is there any IF/THEN/ELSE logic in the program that can be added as WHERE
clause predicates in the query?

- What is the compression ratio?

- What indexes are out there (including column cardinalities and
clusterratios)?

- How many rows are in each table?

- What is the filtering (I know you said not enough) of A.I_VAL_SICO (value
'O') and A.Y_STA_COD (values <> 'A')?

- Does the join provide any filtering (do all rows in A have a match in B)?

- If you want to throw indexes at it, then these two will allow index only
and avoid sorts if DB2 chooses merge scan. Best use of index lookaside and
sequential detection also. But what is the impact to other processes?
On A: I_VAL_SICO, C_EMET, Y_STA_COD, C_VAL_AFC
On B: C_EMET, MOT_DIR_R , MOT_DIR , D_DEB_VALI_2

- Due to the tablespace scan of both tables, DB2 must sort both tables (A
after local predicates applied, and all of B). Are you confident that your
SORTPOOL, bufferpools, sort work database are all sized correctly for
optimal sorting?

Regards
Terry Purcell
Yevich Lawson & Assoc Inc (YL&A)
IBM Gold Consultant - DB2
IBM Certified Solutions Expert
DB2 V7 Database Administration OS/390
http://www.ylassoc.com


-----Original Message-----
From: DB2 Data Base Discussion List
[mailto:[login to unmask email]On Behalf Of Christophe Radier
Sent: Wednesday, January 08, 2003 11:20 AM
To: [login to unmask email]
Subject: Anything to optimize from this SQL ?


hi all ,

I have an SQL statement which takes 9' to execute in normal time ,
unfortunately it is executed at night (in a test environment) at peak CPU
used period , and it takes nearly 2 hours to be executed (the job is 90% on
CPU delay) , i've tried to add indexes on the columns but they are not
enough filtering and are not used.

The SQL is :

SELECT B.MOT_DIR_R , B.MOT_DIR , A.C_VAL_AFC , B.D_DEB_VALI_2
FROM ER.VVA508 A , ER.VVA532 B
WHERE A.C_EMET = B.C_EMET
AND A.I_VAL_SICO = 'O'
AND A.Y_STA_COD <> 'A'

The optimizer decides to use a merge scan join (so TS scan on the two
tablespace)

VVA508 is compressed , so i think that i should try to uncompress it
(COMP=NO) to avoid extra CPU consumption
Is there anything i should do ?

Thanks






Terry Purcell

Re: Anything to optimize from this SQL ?
(in response to Hello)
Christophe,

It doesn't sound like this SQL is your problem. Your problem appears to be
on a larger scale.

I would suggest that someone takes a look at your system configuration and
compares it to your current workload (contact me offline if you want more
details). This would include evaluating all other SQL to see where resource
usage can be reduced.

Regards
Terry Purcell
Yevich Lawson & Assoc Inc (YL&A)
IBM Gold Consultant - DB2
IBM Certified Solutions Expert
DB2 V7 Database Administration OS/390
http://www.ylassoc.com


-----Original Message-----
From: DB2 Data Base Discussion List
[mailto:[login to unmask email]On Behalf Of Christophe Radier
Sent: Thursday, January 09, 2003 10:33 AM
To: [login to unmask email]
Subject: Re: Anything to optimize from this SQL ?


Hi all ,

Thank you for your differents advices and suggestions .

Here are some more informations on the problem :

We have 5 development environments and they runs in parallell on the same
machine -

So the 5 same programs ( accessing different databases - but on the same
DB2, DB2A) are executed at the same time.
There is no easy way to change it (it is what they said !)

I run the program alone , and it takes 9 minutes to run - At night , when
they are 5 they takes 2 hours.
The MVS monitor shows 95% waiting for CPU time for all these jobs.
The only SQL executed in the program brings back 4 millions rows.
These rows are put in a sequential file , and the next step is a LOAD
REPLACE LOG NO of another Table.

i noticed that the Load is also very slow : (40 to 80 minutes for 4
millions rows)
The reload phase took 25mn
The sort phase took 37mn
ICE091I 0 OUTPUT LRECL = 38, TYPE = F
ICE055I 0 INSERT 8662100, DELETE 8662100
ICE054I 0 RECORDS - IN: 0, OUT: 0
ICE134I 0 NUMBER OF BYTES SORTED: 329159800
ICE165I 0 TOTAL WORK DATA SET TRACKS ALLOCATED: 7650 , TRACKS USED: 1620
The Rebuild phase took 15 mn

Another thing i noticed is that on another DB2B, the same program , running
at the same time
on the same MVS the program runs only in 30mn. (So , we have 6 parallell
environments , sorry ..)


So my conclusion is that DB2A is overloaded , if i want it to absorb these
5 parallell SQL , i must
resize all the pools .

I will try with the index only access solution proposed by Terry this night
and keep you informed tomorrow.



Statistics info :



The compression rate is 66% for VVA508

356000 rows in VVA508 (11800 pages)
503772 rows in VVA532 (5800 pages)

- The SQL "brings back" 4 Millions rows

Filtering factors :

57788 rows with I_VAL_SICO= 'O'
351204 rows with I_STA_COD <> 'A'


Indexes :

TABLE (ER.VVA508)
I
+--- INDEX (ER.VVI5081) CLUST UNIQUE
I I
I +--- IXCOL (C_VAL_AFC)
I +--- IXCOL (C_VAL)
I
+--- INDEX (ER.VVI5082)
I I
I +--- IXCOL (C_VAL)
I
+--- INDEX (ER.VVI5083)
I I
I +--- IXCOL (C_EMET)
I
+--- INDEX (ER.VVI5084)
I I
I +--- IXCOL (D_MAJ)
I
+--- INDEX (ER.VVI5085)
I I
I +--- IXCOL (D_SRCE)
I +--- IXCOL (C_VAL_AFC)


TABLE (ER.VVA532)

+--- INDEX (ER.VVI5321) CLUST
I +--- IXCOL (MOT_DIR_R)
I +--- IXCOL (MOT_DIR)
I
+--- INDEX (ER.VVI5322)
I
+--- IXCOL (C_EMET)





NAME 1stKEYCARD FULLKEYCARD NLEAF NLVLS CLUSTRATIO
------------------ ---------- ----------- ---------- ----- ----------
VVI5081 356033 356033 2358 3 0.9842
VVI5082 256258 256258 1620 3 0.7650
VVI5083 106797 106797 756 3 0.6175
VVI5084 4181 4181 502 3 0.2112
VVI5085 4592 356033 1865 3 0.9020


VVI5321 24412 71824 1254 3 0.9998
VVI5322 109560 109560 967 3 0.1044

>>>>>>>>>>>>>
Christophe,

Here are a few questions (and one or two thoughts):

- Did you say 9 minutes vs 2 hours? If so, don't run it during peak time.

- Anything you can do to relieve the load during the peak period (has anyone
done a performance audit)?

- Is there any IF/THEN/ELSE logic in the program that can be added as WHERE
clause predicates in the query?

- What is the compression ratio?

- What indexes are out there (including column cardinalities and
clusterratios)?

- How many rows are in each table?

- What is the filtering (I know you said not enough) of A.I_VAL_SICO (value
'O') and A.Y_STA_COD (values <> 'A')?

- Does the join provide any filtering (do all rows in A have a match in B)?

- If you want to throw indexes at it, then these two will allow index only
and avoid sorts if DB2 chooses merge scan. Best use of index lookaside and
sequential detection also. But what is the impact to other processes?
On A: I_VAL_SICO, C_EMET, Y_STA_COD, C_VAL_AFC
On B: C_EMET, MOT_DIR_R , MOT_DIR , D_DEB_VALI_2

- Due to the tablespace scan of both tables, DB2 must sort both tables (A
after local predicates applied, and all of B). Are you confident that your
SORTPOOL, bufferpools, sort work database are all sized correctly for
optimal sorting?

Regards
Terry Purcell
Yevich Lawson & Assoc Inc (YL&A)
IBM Gold Consultant - DB2
IBM Certified Solutions Expert
DB2 V7 Database Administration OS/390
http://www.ylassoc.com


-----Original Message-----
From: DB2 Data Base Discussion List
[mailto:[login to unmask email]On Behalf Of Christophe Radier
Sent: Wednesday, January 08, 2003 11:20 AM
To: [login to unmask email]
Subject: Anything to optimize from this SQL ?


hi all ,

I have an SQL statement which takes 9' to execute in normal time ,
unfortunately it is executed at night (in a test environment) at peak CPU
used period , and it takes nearly 2 hours to be executed (the job is 90% on
CPU delay) , i've tried to add indexes on the columns but they are not
enough filtering and are not used.

The SQL is :

SELECT B.MOT_DIR_R , B.MOT_DIR , A.C_VAL_AFC , B.D_DEB_VALI_2
FROM ER.VVA508 A , ER.VVA532 B
WHERE A.C_EMET = B.C_EMET
AND A.I_VAL_SICO = 'O'
AND A.Y_STA_COD <> 'A'

The optimizer decides to use a merge scan join (so TS scan on the two
tablespace)

VVA508 is compressed , so i think that i should try to uncompress it
(COMP=NO) to avoid extra CPU consumption
Is there anything i should do ?

Thanks








Nick Cianci

Re: Anything to optimize from this SQL ?
(in response to Terry Purcell)
Christophe,
I believe that Terry has already answered this, but ....

Looking at the stat's for this query, the join is not doing any filtering
for you, so the only thing that might help is another index on A.I_VAL_SICO,
and probably including A.Y_STA_COD, A.C_EMET, & A.C_VAL_AFC. If you can
afford the extra index on "A", then have a play with it in a test
environment. So long as the other columns aren't to big you may as well
include them and avoid the extra I/O. This may help a LITTLE bit if it goes
back to a nested loop, but then you may need to look at a new index (with
all columns for Index only access) on table "B" as well to avoid the random
I/O that you'd hit there.

Sorry to take so long getting back to you.

Cheers,
Nick F. Cianci
IBM Certified Solutions Expert
- DB2 UDB V7.1 Database Administration for OS/390

EDS Canberra Solution Centre
DB2 DataBase Administrator
Phone: +61 2) 6275-5863
+61 (0)408-64 06 01

-----Original Message-----
From: Christophe Radier [mailto:[login to unmask email]

Hi all ,

Thank you for your differents advices and suggestions .

Here are some more informations on the problem :

We have 5 development environments and they runs in parallell on the same
machine -

So the 5 same programs ( accessing different databases - but on the same
DB2, DB2A) are executed at the same time. There is no easy way to change it
(it is what they said !)

I run the program alone , and it takes 9 minutes to run - At night , when
they are 5 they takes 2 hours. The MVS monitor shows 95% waiting for CPU
time for all these jobs. The only SQL executed in the program brings back 4
millions rows. These rows are put in a sequential file , and the next step
is a LOAD REPLACE LOG NO of another Table.

i noticed that the Load is also very slow : (40 to 80 minutes for 4 millions
rows)
The reload phase took 25mn
The sort phase took 37mn
ICE091I 0 OUTPUT LRECL = 38, TYPE = F
ICE055I 0 INSERT 8662100, DELETE 8662100
ICE054I 0 RECORDS - IN: 0, OUT: 0
ICE134I 0 NUMBER OF BYTES SORTED: 329159800
ICE165I 0 TOTAL WORK DATA SET TRACKS ALLOCATED: 7650 , TRACKS USED: 1620
The Rebuild phase took 15 mn

Another thing i noticed is that on another DB2B, the same program , running
at the same time on the same MVS the program runs only in 30mn. (So , we
have 6 parallell environments , sorry ..)


So my conclusion is that DB2A is overloaded , if i want it to absorb these
5 parallell SQL , i must
resize all the pools .

I will try with the index only access solution proposed by Terry this night
and keep you informed tomorrow.

Statistics info :

The compression rate is 66% for VVA508

356000 rows in VVA508 (11800 pages)
503772 rows in VVA532 (5800 pages)

- The SQL "brings back" 4 Millions rows

Filtering factors :

57788 rows with I_VAL_SICO= 'O'
351204 rows with I_STA_COD <> 'A'


Indexes :

TABLE (ER.VVA508)
I
+--- INDEX (ER.VVI5081) CLUST UNIQUE
I I
I +--- IXCOL (C_VAL_AFC)
I +--- IXCOL (C_VAL)
I
+--- INDEX (ER.VVI5082)
I I
I +--- IXCOL (C_VAL)
I
+--- INDEX (ER.VVI5083)
I I
I +--- IXCOL (C_EMET)
I
+--- INDEX (ER.VVI5084)
I I
I +--- IXCOL (D_MAJ)
I
+--- INDEX (ER.VVI5085)
I I
I +--- IXCOL (D_SRCE)
I +--- IXCOL (C_VAL_AFC)

TABLE (ER.VVA532)

+--- INDEX (ER.VVI5321) CLUST
I +--- IXCOL (MOT_DIR_R)
I +--- IXCOL (MOT_DIR)
I
+--- INDEX (ER.VVI5322)
I
+--- IXCOL (C_EMET)

NAME 1stKEYCARD FULLKEYCARD NLEAF NLVLS CLUSTRATIO
------------------ ---------- ----------- ---------- ----- ----------
VVI5081 356033 356033 2358 3 0.9842
VVI5082 256258 256258 1620 3 0.7650
VVI5083 106797 106797 756 3 0.6175
VVI5084 4181 4181 502 3 0.2112
VVI5085 4592 356033 1865 3 0.9020


VVI5321 24412 71824 1254 3 0.9998
VVI5322 109560 109560 967 3 0.1044

>>>>>>>>>>>>>

< .. snip .. >