Question Re: Plan Analyze ASA-9 in Technology Q&A

Plan Analyze ASA-9

former_SQLA_member1694872 — Mon, 04 Feb 2013 08:53:24 GMT

I changed the query. Both should return the same amount of tuples. Where there were JOIN, changed to KEY JOIN (based on the concept of using the KEY JOIN). Some fields that the type CHAR that receive only numeric values have changed for the type INTEGER. (I turned off the primary and foreign keys and created again using the new fields of INTEGER). The indices were also deleted and re-created using the field of type INTEGER. The WHERE clause has not changed. Remains the same filter.

I changed the query code and grabbed the implementation plans and would like to make a comparison between them. How to correctly analyze the execution plan in ASA-9?

before: after

Re: Plan Analyze ASA-9

jeff_albion — Mon, 04 Feb 2013 09:49:25 GMT

Trivially, we can tell you where the differences are between the graphical representations:

TBVENPEDIDO is taking up 76.76% of your total execution time whereas it took 31.47% before. It is likely scanning more data. To find out why this is the case, you need to look at the node details to see where the differences are.
TBUNICLIFOR is taking up more of your execution time to match against TBVENPEDIDO, so it may be scanning more data out also. Again, the node details would tell you more.
There is a conversion of join strategies between these tables (JNL -> JH), possibly due to a change in runtime conditions or join cardinality estimations.

But without the actual plan details ("with statistics") on each node, it's difficult to comment on why this is the case, simply by looking at the graphical representation. You need to capture the SQL statements and plan with statistics. Can these full plan files (*.xml / *.saplan) be provided?

Do both of these queries really return the same set of results? Or are they looking at different data sets? Did you change the actual SQL statement, or just the predicates for your WHERE clause?

Re: Plan Analyze ASA-9

VolkerBarth — Mon, 04 Feb 2013 10:56:31 GMT

Well, it might be noted that it's just "taking up more relative execution time" - without knowing the timing details, we can't even tell which query is faster (which you have already made very clear, there's no doubt about that!)...

Re: Plan Analyze ASA-9

former_SQLA_member1694872 — Mon, 04 Feb 2013 11:49:01 GMT

I changed the query. Both should return the same amount of tuples. Where there were JOIN, changed to KEY JOIN (based on the concept of using the JOIN KEY). Some fields that the type CHAR that receive only numeric values have changed for the type INTEGER. (I turned off the primary and foreign keys and created again using the new fields of INTEGER). The indices were also deleted and re-created using the field of type INTEGER. The WHERE clause has not changed. Remains the same filter.

Re: Plan Analyze ASA-9

Former Member — Tue, 05 Feb 2013 09:50:13 GMT

Please post the plan files. To save a plan, use dbisql Plan Viewer, choose 'node and detailed statistics' ( the query will be executed), generate the plan, use Save as to save the plan in a file. The plan file contains many types of statistics, beyond your pictures above, which can be used to analyze the query execution as well as the query optimization process.