Some features in the optimizer can be invoked many times during statement optimization and execution, and thus can make the trace grow beyond reason. They are:
Greedy search: with a N-table join, this could explore factorial(N) plans
Dynamic range optimization (known as "range checked for each record" in EXPLAIN output: each outer row causes a re-run of the range optimizer)
Subqueries: a subquery in WHERE clause may be executed once per row.
Those features can be excluded from tracing using the optimizer_trace_features system variable, which has these on/off switches:
greedy_search: if off, greedy search is not traced
range_optimizer: if off, range optimizer is not traced
dynamic_range: if off, only the first call to the range optimizer on this JOIN_TAB::SQL_SELECT is traced
repeated_subselect: if off, only the first execution of this Item_subselect is traced.
Copyright © 1997, 2014, Oracle and/or its affiliates. All rights reserved. Legal Notices