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