Hi Guys,

When I explain the query with db2advis as below , it gives different results than the Explain Plan Graph via CE.

db2advis -d gaming -i MyQuery.sql -a db2admin/****** -n db2admin -t 5

The difference is huge. 2742.57 timeron in CE vs


Please help me be comfortable to use proper EXPLAIN.

DBFinder

Do not worry about timerons - they are only useful to compare costs of different parts of one query to each other.

Well, what the explains are for ?? Huge difference 2742 vs 274252.

Suppose I do visual Explain which tells me the query is Superfast. I tell my developer that query is superfast.He goes back and runs it. The query never returns.

This has actually happened.


Again if I do not worry about timerons, I am really interested to know what we do with visual explanation. May be I am missing something.

Thanks anyway !

DBFinder

you look at the path. See if you are doing tbscans and why. Sometimes they are warranted, most of the time they are not. See if you are using right indexes.

According to DB2 documentation, "The EXPLAIN statement captures information about the access plan chosen for the supplied explainable statement and places this information into the explain tables." On the basis the access path, given sufficient information about the environment, where the query will execute, you may be able to estimate run time. For example, on the basis of the EXPLAIN output, you discover how much I/O will be done, but that says nothing about how long that I/O will take - this depends on how well your I/O subsystem performs, which is something that EXPLAIN knows nothing about.

Yes I do.

But my concern is to know, which explain is right. With huge difference in both, I need to use the right one.

In Command Editor, when I explained this query there were no tabscans, all indexes were being used properly.

but with db2advis, it is suggesting 103 new indexes !!

In Visual Explain - for Operators (TBSCAN, RETURN etc) in the Cumulative Cost section, you get estimated CPU and IO instructions ... They are better properties for comparison

Again should I never use CE ?

CE showed me no index required. There may be any wrong setting of ENV that makes two different tools work different !!

Any Idea ??

That is exact reason I do not like the adviser and barely ever use it.

Are indexes built correctly? I am now trying to convince management to drop over 100gb worth of indexes from one db.

Do you care to post screen shot of both of your explains?

Any form of EXPLAIN will give you exactly the same information.
However, db2advis is NOT EXPLAIN. While EXPLAIN tells you how your SQL will work with the existing structures, db2advis makes recommendations on how to improve performance by modifying db design.

You can use db2pd to get the information on the indexes being used.

Cheers
Sathyaram

You will find a good discussion on using db2pd for knowing table and index usage at

db2pd to monitor table access

Cheers
Sathyaram

Just because they are used does not mean they are good

db2pd is helpful, but you can't reply on it as solely.

DBFinder, just curious. When you run the Explain in CE, do you just process an Explain or do you run the Query and Explain it? It may be possible to get different results with the different methods. The first method won't know the actual values of variables while the second method will have the actual values.

But if I had to make a choice of which on of your two Explain methods to trust (CE or DB2ADVIS), I would believe the one that shows the correct indexes being used instead of the one that tries to tell me I need 103 additional indexes. (I can't imagine ANY query I would want to write that would even attempt to use 103 indexes).

Agree with you, technically ... But when you say

"I am now trying to convince management to drop over 100gb worth of indexes from one db."

Isn't db2pd the tool you can use to justify dropping some of the indexes ???? Next stage, you have a smaller pool of indexes to challenge and tune - using Explain or whatever means , then you can rid of more unwanted indexes.

My approach ... That's all ... Doesn't mean it is good