IBM FileNet P8, Version 5.2.1            

Problems with query execution plans

The query execution plans that database query tools generate are typically not correct because Content Platform Engine uses bind values and not literals.

To obtain the proper query execution plan for the actual query as executed by Content Platform Engine, use a similar method that is appropriate for your database. For example, use profiling techniques or capture the query execution plan from the shared pool. Obtaining a query execution plan can be problematic if not done correctly for several reasons:
Table 1. Query execution plan problems
Reason Comment
Different tools for query execution Content Platform Engine queries are issued through the JDBC thin driver by using certain API calls. If a query is executed by using command line or GUI database tools, the query execution plan can be different from the Content Platform Engine query execution plan. Where feasible, obtain the query execution plan for the query as executed by Content Platform Engine.
Bind markers Content Platform Engine queries use bind markers for the values. If a query is executed by using literals, such as uxy_property = 'value' for the values rather than "uxy_property = ?", the query execution plan might be different. It depends on how the Content Platform Engine query was first compiled based on any "bind peeking" provided by the database vendor. For more information, see Database SQL.
Raw data types for Oracle Oracle Explain plans can sometimes be incorrect when improper hextoraw() conversions are used or when they involve raw data types. (Content Platform Engine uses raw data types for Id columns.) For more information, see SQL tuning: GUID property search condition.


Last updated: March 2016
p8ppt277.htm

© Copyright IBM Corporation 2016.