Dynamic Statistics Feedback for Remote Table Operators
IPE plans and executes single-row access and scalar subqueries, then captures the results for request fragments incrementally and plugs the results into the main query. The dynamic feedback from the request fragments provides reliable information for the planning of subsequent request fragments. This feedback enables optimizations such as row partition elimination, access path selection, join index rewrites, transitive closure, constant folding, and improved estimations.
IPE also captures dynamic statistics for remote table operators. Dynamic statistics collection is triggered automatically when eligible table operators are used without applying the cost and parse time thresholds from the static plan. Collecting dynamic statistics on remote tables, or table operators, provides improved cost estimation for subsequent planning.
Statistics, such as row count, skew, null count, and so forth, are collected from remote table operators during the execution of each plan fragment for a request fragment. For table operators, statistics feedback is obtained for every eligible query block in a plan fragment. Dynamic, runtime, and DBQL explains indicate this as FEEDBACK RETRIEVE. The optimizer identifies the statistics required for the planning, requests statistics feedback, and receives the statistics feedback response from the AMP. The optimizer updates or generates a derived statistics list based on the feedback and uses the derived statistics to provide the planning for the remainder of the request. Incremental planning the executes the remaining request fragments using the captured statistics.
Dynamic statistics collected for the intermediate spools during IPE planning are captured in XML and EXPLAIN. The XML element, IPEFeedback, a child element of "PlanStep," has two attributes: FeedbackKind and ActualRowCount. The value of FeedbackKind can be either IPEResults or IPEStats. IPEResults indicates that actual spool contents are fed back to the optimizer. IPEStats indicates that statistics on a result spool are fed back. ActualRowCount is the actual number of rows in the result spool.
For the cases where a dynamic plan is not significantly better or its run time performance is the same or less than the parsing time, a static plan is generated. Also, query bands can be used to indicate when the optimizer uses a dynamic plan.