Diagnosing Issues

You may experience situations where you incur performance issues or unexpected results/exceptions. The rest of this chapter will focus on query planning and processing issues. Configuration or operational issues related to the container are typically more isolated and easier to resolve.

General Diagnostic Process

  • When there is an issue, start by isolating a problem query as much as possible. If accessing through ODBC or OData, does a similar issue occur just through JDBC. In whatever scenario the issue occurs, the particulars matter - what sources, if there is a transaction, load, etc.

  • Don’t make too many assumptions

    • For example memory consumption can be heavily dependent upon drivers, and a resulting out of memory issue may only be indirectly related to Teiid

  • Start with the query plan - especially with performance issues

    • There may be simplifications or changes possible to views and procedures utilized by the user query.

    • Ensure that relevant costing metadata is set and/or that hints you have provided are being applied as expected.

  • Utilize Logging

    • Planning issues may be understood with the debug plan

    • The command log

    • A full debug/trace level log can shed even more light – but it may not be easy to follow.

      • You can correlate what is happening by context, thread, session id, and request id.

  • If no resolution is found, engage the community and utilize professional support

Query Plans

Once the problem has been isolated as much as possible, you should further examine the query plan. The only circumstance when this is not possible is when there are planning errors. In this case the logs, either full debug or just the debug plan, is still useful to then log an issue with the community or with support.

If you haven’t done so already, you should start by familiarizing yourself with Federated Planning - especially the sections on the query plan.

The final processor plan is generally what is meant when referring to by “the query plan”. The plan can be viewed in an XML or a plain text format.

Designer is typically the first place to look at query plans from the query execution view. It’s easiest to look at using Designer or an XML aware viewer as the plan trees can get large.

You can also use Teiid Extensions or SQL statements to get the plan:

SET SHOWPLAN ON
SELECT ...
SHOW PLAN

Once you have the plan, you can:

  • Double check that hints are taking effect

  • Make sure things seem correct

    • Look first at all of the source queries on the access nodes. Generally a missing pushdown, such as predicate is easy to spot

    • Focus on problem source queries and their parent nodes if you already have execution times

It’s also a good idea to validate query plans during the development and testing of a VDB. Also any engagement with the community or support will likely need the query plan as well.

If the plan is obtained from an executed query, then the plan will also show execution statistics. It is especially useful to see the stats when processing has finished and all rows have been fetched. While several stats are collected, it’s most useful to see “Node Output Rows” and “Node Next Batch Process Time”.

Example text form of a query plan showing stats:

ProjectNode
  + Relational Node ID:6
  + Output Columns:x (double)
  + Statistics:
    0: Node Output Rows: 6
    1: Node Next Batch Process Time: 2
    2: Node Cumulative Next Batch Process Time: 2
    3: Node Cumulative Process Time: 2
    4: Node Next Batch Calls: 8
    5: Node Blocks: 7
  + Cost Estimates:Estimated Node Cardinality: -1.0
  + Child 0:
    AccessNode
      + Relational Node ID:7
      + Output Columns
      + Statistics:
        0: Node Output Rows: 6
        1: Node Next Batch Process Time: 0
        2: Node Cumulative Next Batch Process Time: 0
        3: Node Cumulative Process Time: 0
        4: Node Next Batch Calls: 2
        5: Node Blocks: 1
...

In addition to the execution stats, note there are also cost estimates. The values for the cost estimates are derived from the statistic values set of each table/column about the row count, number of distinct values, number of null values, etc. Unlike systems that own the data, Teiid does not build histograms or other in-depth models of the data. Theses values are meant to be approximations with nominally distribution assumptions. The costing information from the metadata only matters for physical entities as we’ll recompute the higher values in planning after merge virtual and other plan modifications. If you see that join is being implemented inefficiently, then first make sure reasonable costing values are being set on the tables involved. Statistics can be gathered for some sources at design time or deploy time. In environments that fluctuate rapidly, you may need to issue runtime costing updates via system procedures.

Note: if you cardinality values are unknown - shown as 'Node Cardinality: -1.0' in the plan - and no hints are used, then the optimizer will not assume that dependent join plans should be used.

Pushdown Inhibited

One of the most common issues that causes performance problems is when not enough of the plan is pushed to a given source leading to too many rows being fetched and/or too much processing in Teiid.

Pushdown problems fall into two categories:

  • Something that cannot be pushed down. For example not all system functions are supported by each source. Formatting functions in particular are not broadly supported.

  • A planning or other issue that prevents other constructs from being pushed down

    • Temp tables or materialization can inhibit pushdown when joining

    • Window functions and aggregation when not pushed can prevent further pushdown

If pushdown is inhibited then the construct will be missing from the access node issuing the source query, and will instead be be at a higher node:

<node name="SelectNode">...<property name="Criteria"><value>pm1.g1.e2 = 1</value>
  <node name="AccessNode">...<property name="Query"><value>SELECT pm1.g1.e1, pm1.g1.e2 FROM pm1.g1</value>

When pushdown is inhibited by the source, it should be easy to spot in the debug plan with log line similar to:

LOW Relational Planner SubqueryIn is not supported by source pm1 - e1 IN /*+ NO_UNNEST */ (SELECT e1 FROM pm2.g1) was not pushed

Common Issues

Beyond pushdown being inhibited, other common issues are:

  • Slight differences in Teiid/Pushdown results

    • for example Teiid produces a different for a given function than the source

  • Source query form is not optimal or incorrect

  • There is an unaccounted for type conversion

    • for example there is no char(n) type in Teiid

    • A cast may cause a source index not to be used

  • Join Performance

    • Costing values not set leading to a non-performant plan.

    • Use hints if needed.

    • Teiid will replace outer with inner joins when possible, but just in case review outer join usage in the user query and view layers

XQuery

XQuery/XPath can be difficult to get correct when not assisted by tooling. Having an incorrect namespace for example could simply result in no results rather than exception.

With XMLQUERY/XMLTABLE each XPath/XQuery expression can have a large impact on performance. In particular descendant access '//' can be costly. Just accessing elements in the direct parentage is efficient though.

The larger the document being processed, the more careful you need to be to ensure that document projection and stream processing can be used. Streaming typically requires a simple context path - 'a/b/c'

Out of Memory

Out of memory errors can be difficult to track down. In almost all cases, it is best to determine the actual memory consumption utilizing a heap dump - which can be obtained using the vm HeapDumpOnOutOfMemoryError option or via a tool such as VisualVM. You may also simply increase the size of the heap, but that may simply delay the issue from reappearing.

Logging

The query plan alone does not provide a full accounting of processing. Some decisions are delayed until execution or can only be seen in the server logs:

  • The ENAHANCED SORT JOIN node may execute can execute one of three different join strategies depending on the actually row counts found, this will not be seen unless the query plan is obtained at the end of execution.

  • The effect of translation is not yet accounted for as the plan shows the engine form of the query

    • The full translation can be seen in with command logging at a trace level or with debug/trace logging in general.

  • The query plan doesn’t show the execution stats of individual the source queries, which is shown in the command log

  • The for full picture of execution down to all the batch fetches, you’ll just need the full server debug/trace log

Plan Debug Log

The logical plan optimization is represented by the planning debug log and is more useful to understand why planning decisions were made.

SET SHOWPLAN DEBUG
SELECT ...
SHOW PLAN

You will typically not need to use this level of detail to diagnose issues, but it is useful to provide the plan debug log to support when planning issues occur.

results matching ""

    No results matching ""