You can capture the Optimizer query plan for logged SQL DML requests in the DBQL table DBC.DBQLXMLTbl. To do this, specify XMLPLAN for logging_option to enable query plan capture for executing DML queries as XML text.
Query plan information in XML format is particularly useful for diagnosing performance and query plan issues.
- DUMP EXPLAIN
- INSERT EXPLAIN
Also, the database does not log text definitions of objects referenced by SQL requests. Instead, the system logs a short identifier that can be used as a key to fetch the text definition from the data dictionary if it is needed.
- Apart from the respective content of the documents they produce, there is another important difference between XMLPLAN logging and INSERT EXPLAIN requests, as indicated by the following table.
BEGIN QUERY LOGGING … XMLPLAN INSERT EXPLAIN Logs query plans for executed queries in XML format. Captures query plans without executing the query.
XMLPLAN logging is ideal when you want to record query plans for your executing workloads and have found that capturing query plans for the requests in those workloads using INSERT EXPLAIN requests is too slow for your needs.
On the other hand, if you are only tuning a query and do not want to execute it, XMLPLAN logging is not as useful as capturing the query plan for a request using INSERT EXPLAIN requests.
In this case, executing an INSERT EXPLAIN INTO QCD_name IN XML request or an EXPLAIN IN XML SQL_request is a more analogous alternative. You cannot capture query plans in XML format using DUMP EXPLAIN requests.
- Runtime information from the traditional DBQL tables is also captured for a logged plan.
- XMLPLAN logging is more an extension to query logging than an extension to the Query Capture Facility.
The EXPLAIN option does not log rows for cached requests.