Sorting issues

[gwise@pressganey.com]

I have 2 databases with the same layout (but slightly different data)
on 2 different servers. I have a query I'm trying to run which
performs poorly on one of the servers due to sort issues, but performs
fine on the other server. Here's a portion of the query plan I'm
seeing on the good performing server:

FETCH
( 12)
70.0116
5.1845
/---+---\
1.66958 4.27945e+07
IXSCAN TABLE: USERID
( 13) STD_DEMO_DATA
48.5708
3.51655
|
4.27945e+07
INDEX: USERID
SFRD


This is what I would expect to see and this is good. But on the other
server, the same query gives me this:

FETCH
( 12)
47.2245
34
/---+---\
74.4065 6.90179e+07
RIDSCN TABLE: USERID
( 13) STD_DEMO_DATA
15.1934
2
|
74.4065
SORT
( 14)
15.1904
2
|
74.4065
IXSCAN
( 15)
15.1687
2
|
6.90179e+07
INDEX: USERID
SFRD


This is evil. Now, I might expect this if my data were not clustered
by the SFRD index, but it is as you can see by this snippet of
reorgchk:

SCHEMA.NAME INDCARD LEAF ELEAF LVLS NDEL KEYS
LEAF_RECSIZE NLEAF_RECSIZE LEAF_PAGE_OVERH EAD NLEAF_PAGE_OVER HEAD F4
F5 F6 F7 F8 REORG
--------------------------------------------------------------------------------------------------------------------------------------------------------------
Table: USERID.STD_DEMO _DATA
Index: USERID.SFRD
6.9e+07 86265 0 3 0
7425364 9 9 1048
1048 99 91 0 0 0 -----

As you can hopefully see, this shows that I am 99% clustered on that
index. Now, there is no clustering index, but given I'm 99%
clustered, does that matter? What am I missing that is influencing
the optimizer to pick the poor plan when I'm 99% clustered.

Thanks in advance for any help,
Greig Wise -- DBA Manager

[Serge Rielau]

Re: Sorting issues

Yours stats are off.
Teh first (good) plan thinks it's dealing with 2 rows (more or less),
while the other presumes 70 rows coming out of the scan.

How close are these numbers to the truth?
Rid sorting 70 rows should not have a big impact.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Stefano P.]

Re: Sorting issues

Yours stats are off.

I'm ignorant about DB2 *not* for i5/OS:
starting from V5R2, the query engine in i5/OS can get information about
the selectivity of a query using statistics too (if collected) but in
the past it has (always) tried to guess the number of records chosen in
every table by the query itself using information ("meta data") given by
indexes (both logical files and sql indexes).

Does DB2 for LUW (or z/OS) get this information only from statistics or
from indexes too?

Cheers
Serge

TIA
Stefano P.

--
"Niuna impresa, per minima che sia,
può avere cominciamento e fine senza queste tre cose:
e cioè senza sapere, senza potere, senza con amor volere"
[Anonimo fiorentino, XIV sec.]

(togliere le "pinzillacchere " dall'indirizzo email ;-)

[gwise@pressganey.com]

Re: Sorting issues

On Feb 1, 5:07 pm, Serge Rielau <srie...@ca.ibm .comwrote:

Yours stats are off.
Teh first (good) plan thinks it's dealing with 2 rows (more or less),
while the other presumes 70 rows coming out of the scan.
>
How close are these numbers to the truth?
Rid sorting 70 rows should not have a big impact.
>
Cheers
Serge
>
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

OK. That's interesting. Now, let me actually show you the whole
"bad" query plan (below) -- maybe I should have done that in the first
place. So, you can see that sort is being done inside a loop which
iterates 3100 times. So, I'm guessing that the 70 rows expected
coming back from the index scan is an average or something --
depending on which iteration we're in that could be more or less. So
that maybe explains why I'm seeing like 80 sort overflows when I run
this query even though I have my sort heap set to 45K pages?

Which kind of brings me back to my original question which is why I'm
doing this sort at all given I'm 99% clustered.

Thanks again for the help.

Greig

Query Plan:


Rows
RETURN
( 1)
Cost
I/O
|
3129
GRPBY
( 2)
147838
106554
|
3129
LMTQ
( 3)
147838
106554
|
3129
GRPBY
( 4)
147837
106554
|
232818
NLJOIN
( 5)
147823
106554
/----------+---------\
3129 74.4065
TBSCAN FETCH
( 6) ( 12)
241.467 47.2245
166.015 34
| /---+---\
3129 74.4065 6.90179e+07
SORT RIDSCN TABLE: USERID
( 7) ( 13) STD_DEMO_DATA
240.722 15.1934
166.015 2
| |
3129 74.4065
FETCH SORT
( 8) ( 14)
239.279 15.1904
166.015 2
/---+---\ |
3129 34508 74.4065
RIDSCN TABLE: USERID IXSCAN
( 9) CSS_DEFN ( 15)
78.3991 15.1687
9.97421 2
| |
3129 6.90179e+07
SORT INDEX: USERID
( 10) SFRD
78.2879
9.97421
|
3129
IXSCAN
( 11)
76.845
9.97421
|
34508
INDEX: USERID
CLI_SITE_SERV2

[Serge Rielau]

Re: Sorting issues

gwise@pressgane y.com wrote:

On Feb 1, 5:07 pm, Serge Rielau <srie...@ca.ibm .comwrote:

>Yours stats are off.
>Teh first (good) plan thinks it's dealing with 2 rows (more or less),
>while the other presumes 70 rows coming out of the scan.
>>
>How close are these numbers to the truth?
>Rid sorting 70 rows should not have a big impact.

OK. That's interesting. Now, let me actually show you the whole
"bad" query plan (below) -- maybe I should have done that in the first
place. So, you can see that sort is being done inside a loop which
iterates 3100 times. So, I'm guessing that the 70 rows expected
coming back from the index scan is an average or something --
depending on which iteration we're in that could be more or less. So
that maybe explains why I'm seeing like 80 sort overflows when I run
this query even though I have my sort heap set to 45K pages?

Aha! OK, so it's the sort overflows that kill your performance.
So your 2 I/O presumed in the RIDSCN aren't really 2 I/O.

I think it's time to unveil the entire db2exfmt output... (including
your settings and th equery.

I presume this is a DPF system? (The GROUP BY/TQ/GROUP BY suggests that)

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Serge Rielau]

Re: Sorting issues

From the names of the columns I suspect that you may have a strong
correlation, if not even a functional dependency between the CSS_ID and
the RECDATE.
Do you have column-group statistics for (RECDATE, CSS_ID)?
Collect these and that should give a much better insight into what
really goes on.
DB2 may still do RIDSCN, but I suspect you may loose the NLJOIN once DB2
figures out it's getting way more than 80 rows from the inner.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[gwise@pressganey.com]

Re: Sorting issues

On Feb 2, 9:02 am, Serge Rielau <srie...@ca.ibm .comwrote:

 From the names of the columns I suspect that you may have a strong
correlation, if not even a functional dependency between the CSS_ID and
the RECDATE.
Do you have column-group statistics for (RECDATE, CSS_ID)?
Collect these and that should give a much better insight into what
really goes on.
DB2 may still do RIDSCN, but I suspect you may loose the NLJOIN once DB2
figures out it's getting way more than 80 rows from the inner.
>
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

I'm having some trouble for some reason replying to this post. I
apologize if duplicate responses appear.

So I reran the stats as you suggested and it eliminated the RID sort
from the query plan (but not the NLJOIN). Is the runstats on column
new in v9? I never heard of that before. So, how is runstats on
((css_id, recdate)) different than runstats on table with distribution
and detailed indexes all? Given that css_id and recdate are the 2
columns in one of the indexes, it seems like it would collect those
kind of column stats by default.

Anyhow, the new query plan (without the sort) is actually slower on
unloaded system than the sorted one. I suspect that this is because
there is no contention for the tempspace and the sort overflows can
resolve quickly. I intend to test this out on Monday when there will
be more load on the system.

Thanks again for all the help it has been most educational.

Greig

[Serge Rielau]

Re: Sorting issues

gwise@pressgane y.com wrote:

On Feb 2, 9:02 am, Serge Rielau <srie...@ca.ibm .comwrote:

> From the names of the columns I suspect that you may have a strong
>correlation, if not even a functional dependency between the CSS_ID and
>the RECDATE.
>Do you have column-group statistics for (RECDATE, CSS_ID)?
>Collect these and that should give a much better insight into what
>really goes on.
>DB2 may still do RIDSCN, but I suspect you may loose the NLJOIN once DB2
>figures out it's getting way more than 80 rows from the inner.
>>
>Cheers
>Serge
>--
>Serge Rielau
>DB2 Solutions Development
>IBM Toronto Lab

>
>
I'm having some trouble for some reason replying to this post. I
apologize if duplicate responses appear.
>
So I reran the stats as you suggested and it eliminated the RID sort
from the query plan (but not the NLJOIN). Is the runstats on column
new in v9? I never heard of that before. So, how is runstats on
((css_id, recdate)) different than runstats on table with distribution
and detailed indexes all? Given that css_id and recdate are the 2
columns in one of the indexes, it seems like it would collect those
kind of column stats by default.

I think column group stats were introduced in DB2 V8.1.
As to how much of that information you get "for free" by both columns
being in an index is beyond my knowledge of the optimizer. Perhaps
someone else can answer that one.

Anyhow, the new query plan (without the sort) is actually slower on
unloaded system than the sorted one. I suspect that this is because
there is no contention for the tempspace and the sort overflows can
resolve quickly. I intend to test this out on Monday when there will
be more load on the system.

Yes, let's see teh loaded system results. Now, if only the RIDSCN is
removed does that get you back to the plan you had before (on the other
system) and that you liked?

Cheers
Serge


--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab