USER MANUALS

TRACE Clause

By adding the TRACE clause at the end of a SELECT statement, the server will return execution trace of the query, when you execute the query from the administration tool. If you execute the query from another client (e.g. a JDBC client), you will not obtain this information.

The execution trace of a query provides detailed information of how the execution engine processed the query. This plan is 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.

The Administration Tool adds the clause TRACE to all the queries it executes and it provides a graphical interface to analyze the execution trace of the queries (see more about this in the section Execution Trace of a Statement of the Administration Guide). In addition, you can use the Trace Viewer to load the trace of a query executed on another administration tool.

This is some of the information that the execution trace provides for each node:

  • 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. For example:

    • 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.

Execution trace
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
        )
    )
)
Add feedback