TRACE clause of the
SELECT command, the server will
return detailed information about the execution process of a query.
The trace of a statement provides a detailed examination of its execution plan. This plan can be modeled as a tree, where each node represents an intermediate view involved in the execution of a query or an access to a data source via a wrapper.
TRACE clause shows the most relevant parameters for each node on
the query execution tree. The Administration Tool allows examining the trace information using much more
user-friendly graphical interface.
Among the parameters displayed by the
TRACE clause are:
- Node type. If the node is a view, this indicates the type of view (base view, union, join, selection, intersect, etc.). If it is an access to a source (wrapper), this indicates the type of data source (JDBC, Web Service, Web, etc.).
- Execution time. Time spent completely executing the node and all its children.
- Start time. The exact moment at which node processing begins in the execution plan.
- End of query time. The exact moment at which node processing (and that of all its children) ends in the execution plan.
- Time until the first tuple of results was obtained. Time spent until the node receives the first tuple to be processed.
- Number of tuples processed. Number of tuples processed by the node.
- Status. This indicates whether the node was correctly executed or whether an error occurred.
- Advanced parameters. These provide further details on each node type.
- In the case of wrapper-type nodes, the exact sub-queries executed on each data source and the connection data used to access each one are indicated.
- For each view-type node, it is indicated whether the cache has been used, whether swapping has been necessary, etc. are indicated.
- A parameter of particular interest for optimization reasons is “No Delegation Cause”. In the views defined based on tables from the same JDBC or ODBC data source, Virtual DataPort will try to delegate the entire process to the source database, obtaining all the tuples from the view through a single query. This strategy may save a significant amount of execution time in complex views. When Virtual DataPort is unable to delegate the entire process of a certain query to a source database, it will indicate a reason in this parameter. For example, the query may use an expression that includes a function that is not supported by the source database, which will force Virtual DataPort to post-process the results obtained. In light of the reason where the processing could not be delegated, it may be possible to rewrite the view so that it can be delegated.
- Error conditions. The trace also indicates any errors produced during node execution.
As an example, the figure below shows the execution trace of the following query:
SELECT * FROM INTERNET_INC TRACE
INTERNET_INC is a base view created on a table of the same name
accessible via a JDBC data source.
BASE PLAN ( name = INTERNET_INC startTime = Wed Jan 10 17:50:01 850 GMT+01:00 2007 endTime = Wed Jan 10 17:50:04 063 GMT+01:00 2007 responseTime = Wed Jan 10 17:50:04 053 GMT+01:00 2007 numRows = 4 state = OK completed = true fields = [IINC_ID, SUMMARY, TTIME, TAXID, SPECIFIC_FIELD1, SPECIFIC_FIELD2] search conditions =  filter conditions =  numOfFilteredTuples = 0 numOfDuplicatedTuples = 0 numOfSwappedTuples = 0 swapping = false JDBC WRAPPER ( name = internet_inc startTime = Wed Jan 10 17:50:02 070 GMT+01:00 2007 endTime = Wed Jan 10 17:50:04 063 GMT+01:00 2007 responseTime = Wed Jan 10 17:50:04 063 GMT+01:00 2007 numRows = 4 state = OK completed = true searchConditions =  orderByFields =  projectedFields = [IINC_ID, SUMMARY, TTIME, TAXID, SPECIFIC_FIELD1, SPECIFIC_FIELD2] JDBC ROUTE ( name = internet_inc#0 startTime = Wed Jan 10 17:50:03 782 GMT+01:00 2007 endTime = Wed Jan 10 17:50:04 063 GMT+01:00 2007 responseTime = Wed Jan 10 17:50:04 063 GMT+01:00 2007 numRows = 4 state = OK completed = true SQLSentence = SELECT t0.iinc_id, t0.summary, t0.ttime, t0.taxId, t0.specific_field1, t0.specific_field2 FROM test_vdb.internet_inc t0 parameters =  DBUri = jdbc:mysql://localhost/test_vdb userName = vdb connectionTime = 0 cachedStatus = false ) ) )
We strongly recommend using the Administration Tool to analyze the execution trace of queries, because it displays the trace in a graphic. See more information about this in the section Execution Trace of a Statement of the Administration Guide.