Execution Trace of a Statement¶

After executing a statement, you can request the trace of its execution. It is also possible to obtain the execution plan before executing the query. In both cases, the information displayed is the same, except for certain parameters that are only known at run time (e.g. execution time).

The execution plan is displayed in a tree diagram, where each node represents one of these elements:

• The tree root, which is called the “Execution node”. It contains information about the query.

• An intermediate view involved in the execution of the statement

• Or, a retrieval of data from a source.

The main attributes of these nodes are the following:

• Type. If the node is a view, it indicates the type of the view (base view, union, join, selection, projection, etc.). If it is an access to a data source (wrapper), this indicates the type of source (JDBC, ODBC, Web Service, etc.).

  The main node of the trace is “Execution” (the node at the top of the trace), whose attributes represent the state of the entire query.

• Execution time. Time (milliseconds) spent executing this node and its subnodes. In the “Execution” node, this is execution time of the query.

• Start time. Instant at which the Server began executing this node. In the “Execution” node, is the instant at which the Server received the query.

• End time. Instant at which the Server finished executing this node and its subnodes. In the “Execution” node, is the instant at which the Server finished the execution of the query.

• Response time. Instant at which the node received the first row to process. In the “Execution” node, is the instant at which the Server obtained the first row of the query’s result.

  If the node did not process any row, the value of this attribute is unknown.

• Effective time. Time spent to execute this node. It does not include the time spent waiting for other nodes of the execution tree to complete their work.

• Data serialization time (only displayed in the “Execution Plan” node). Time used by the server to transform the result of the query to a byte stream to be sent to the client application.

• Number of rows. Number of rows returned by the node. In the “Execution” node, it is the number of rows returned by the query.

• State. It indicates whether the node was correctly executed or whether an error occurred during its execution of the execution of one of its subnodes.

  The table Values of the “State” attribute of the nodes of a query’s execution trace of the appendix Execution Trace Information lists the possible values for this attribute.

• Completed. If yes, the node finished correctly. If no, there was an error during its execution or the execution of one of its subnodes.

• Waiting time for execution (only in the “Execution” node). Time (milliseconds) the query was waiting in the queue of queries before the Server began executing it.

  A query is hold in the queue of queries when the limit of concurrent requests is reached. This limit is configured on the tab “Concurrent requests” of the menu “Administration > Server configuration”. See more about this limit in the section Limiting the Number of Concurrent Requests.

  The Execution time includes the Waiting time for execution.

• Static optimization (only in the “Execution” node). Parameters regarding the Automatic simplifications described in the section Automatic Simplification of Queries.

  • Optimization time. Time spent by the Execution Engine to analyze the query plan.

  • Static optimized is yes if the query plan was simplified after being analyzed.

• Cost optimization (only in the “Execution” node). Parameters regarding the usage of the Cost-based optimizations described in the section Cost-Based Optimization. If the execution engine uses the cost optimizer to select an execution plan, the trace also contains some information about its decision:

  • Estimated number of rows, row size in bytes and data volume: displayed when the execution plan was obtained without executing the query. It shows how many rows the optimizer expected this node to return. For join nodes, the right branch always assumes the strategy hash or merge.

  • Indexes: indexes that the optimizer expects to be used by the underlying data source for retrieving data.

  • Total, Left Branch and Right Branch cost (displayed only in join nodes): total cost of the join operation and the costs for its branches.

• Advanced parameters. These provide further details on each node type. For example:

  • In the case of “route” nodes, the queries executed on the data source and the connection Uri to the source.

  • Swapping and number of swapped rows: for each node of the query, whether the data has been swapped to disk and the number of rows swapped to disk during the execution of the node.

  • “No delegation cause” explains why a query was not delegated to the source database.

    When a query uses two or more views that rely on the same JDBC or ODBC data source, Virtual DataPort will try to delegate the entire query to the source database. This way, it can obtain the whole result executing a single query. When Virtual DataPort cannot delegate the entire process of the query to a source database, it will show the icon beside the view that could not be delegated and the “No delegation cause” parameter will show the reason why the delegation was not possible. For example, the query may use a function that is not supported by the source database, which will force Virtual DataPort to post-process the results obtained.

    The “No delegation cause” message will help us to rewrite the view so it can be delegated.

The most relevant parameters are displayed for each node on the query execution tree. You can copy the value of each parameter by right-clicking it and then, clicking Copy or copy the entire trace by clicking Copy trace to clipboard ()

To save the execution trace and the diagram, click Save ().

The figure below displays the execution trace of this query:

SELECT *
FROM internet_inc
WHERE summary REGEXP_LIKE '.*(Incident)|(Error).*'

If the database does not support the operator REGEXP_LIKE, the query cannot be delegated to the database and the selection node is marked with this icon , meaning that the selection condition will be executed by Virtual DataPort.

The main usage of the trace is debugging. When there is an error during the execution of a query, the nodes that caused the error are marked in red. By clicking on them, you can see more information about the error. The appendix Execution Trace Information explains the meaning of the icons and colors of the execution trace of a query.

If the Automatic simplification of queries (see section Automatic Simplification of Queries) is enabled, the Execution trace may be very different to the hierarchy defined by the queried view.

internet_inc view execution trace¶

The figure below shows the execution trace of the incidents_sales join view in the case where the Web Service data source is not accessible.

Join view execution trace with errors¶

Besides displaying the execution trace of the query, you can see the “query plan” of a query before executing it, to know how the Server will execute the query, without executing it. This is very useful when dealing with a complex hierarchy of views because you can see the join strategy that the Server will use in each join operation (merge, hash, nested, etc.), how a query will be delegated to a database, etc.

To display this, click Query Plan in the “Execute view” dialog. The process of calculating a query plan is very similar to the actual execution of the query but without actually “hitting” the actual data sources. For example, to simulate the execution of the right branch of a nested join, the Server has to generate a value for the attributes of the right side of the join condition. That is because these values are mandatory and at runtime, the Server uses the values obtained from the left side of the join. However, when obtaining the query plan, it does not have the actual values so it uses the values listed in this table:

SET 'com.denodo.vdb.interpreter.execution.saveTrace' = '<save trace configuration>';

SET 'com.denodo.vdb.interpreter.execution.saveTraceDir' = '<directory to store the traces>';