EXPLAIN statement and RDB$SQL package

[sim1984]

Many DBMSs use a separate EXPLAIN statement to display plans. There are a number of advantages over using isc_info_sql_get_plan to get the plan:

1. We can easily set the query plan output format.
2. You can set other plan output options (for example, the depth of output of recursive plans)
3. Plan output size is unlimited 64K

The EXPLAIN statement can return the plan as a BLOB cursor column or as isc_exec_proc (single row).

Syntax:

EXPLAIN [ ( option [, ...] ) ] statement

option :=
   ANALYZE
   FORMAT={PLAIN | TREE}
   DEPTH=n
   CARDINALITY
   COST

ANALYZE - executes a query without returning data and substitutes the actual measured values into the plan

FORMAT - output format. By default TREE . PLAIN - legacy plan. TREE - tree-like (explain). In the future, it can be extended to other formats JSON, XML...

DEPTH - depth of recursive output (output of plans of internal procedures). The default is 1.

CARDINALITY - output cardinality score for plan nodes.

COST - output cost estimates for plan nodes.

[aafemt]

IMHO isc_info_sql_get_plan should be declared deprecated in favor if IStatement::getPlan() that should change parameter from bool to bitset with options you mentioned. EXPLAIN can be ISQL-only operator.

[mrotteveel]

IMHO isc_info_sql_get_plan should be declared deprecated in favor if IStatement::getPlan() that should change parameter from bool to bitset with options you mentioned. EXPLAIN can be ISQL-only operator.

Doesn't that still use isc_info_sql_get_plan below the surface, at least for remote connections?

[mrotteveel]

Also making it ISQL-only doesn't sound very useful to me. A lot of people don't use ISQL, so making something that is ISQL-only limits the usefulness.

[aafemt]

The only alternative for ISQL is API and we have getPlan() there. Yes it uses isc_info_sql_get_plan under the hood but only because nobody has time and will to clean it out.

[sim1984]

Other DBMSs do not restrict this statement to their tools. For example MySQL, Postgres

[mrotteveel]

The only alternative for ISQL is API and we have getPlan() there. Yes it uses isc_info_sql_get_plan under the hood but only because nobody has time and will to clean it out.

Just cleaning it out wouldn't be sufficient: there would need to be a replacement in the wire protocol... and given the op_info_* is the way to transfer that information across remote connections, you can't get rid of it without introducing something new.

[mrotteveel]

Also, you're ignoring an alternative, and that is it to make it a statement, which returns a result set or single row with the plan information.

[aafemt]

Technically one cannot bypass API using any tool so there is no difference if a programmer writes db->execute("EXPLAIN... SELECT...") or db->prepare("SELECT...")->getPlan().

Access to a BLOB or result set requires more code than access to plan.

[dyemanov]

Smells like some completely new concept to us -- non-DML statement that returns something.

[sim1984]

The main thing in this ticket is not so much the task of bypassing the plan size restrictions, but to give convenience to administrators and testers in setting the plan output form.
For example, recursive output of plans is planned in 5.0, but switching the output depth through the config is not very convenient.
Or, for example, the output of a plan with an estimate of cardinality, which will break all tests.

[aafemt]

Package DBMS_SQL with method GET_PLAN() can do the trick.

[sim1984]

Technically one cannot bypass API using any tool so there is no difference if a programmer writes db->execute("EXPLAIN... SELECT...") or db->prepare("SELECT...")->getPlan().

Access to a BLOB or result set requires more code than access to plan.

Any changes to the API or network protocol are very slow to take root among users. Adding features to the language, on the contrary, is fast, because nothing needs to be rewritten.

[AlexPeshkoff]

Restrict this statement to ISQL is bad idea. But @sim1984 :
You should be more specific with format of returned data. Our SQL statements currently do not return just "BLOB cursor column". They always return cursor (or result set - here for us this 2 are identical). Talking about single row with single BLOB column is more correct, just let's call it "result set with single row with single BLOB column" - and use of generic SQL statement becomes quite logical, cause plan can be accessed using it with any tool, able to select BLOBs.

Next, why not use (optional certainly) format where each plan row is a row of returned result set, containing required fields like ID, PAR_ID (to support hierarchy), COST, CARDINALITY, etc.). That can be really nice for analyzing plans at client side (XML/JASON are good, but clents need additional support for them, on contrary fetch from cursor is able for probably any client out-of-the-box).

[dyemanov]

The main thing in this ticket is not so much the task of bypassing the plan size restrictions, but to give convenience to administrators and testers in setting the plan output form. For example, recursive output of plans is planned in 5.0, but switching the output depth through the config is not very convenient. Or, for example, the output of a plan with an estimate of cardinality, which will break all tests.

It can be handled with session management statements. Although I admit handling it per-statement could be handier.

[asfernandes]

Package DBMS_SQL with method GET_PLAN() can do the trick.

I like this idea.