One of the following SQL profiles to implement. ġ- SQL Profile Finding (see explain plans section below)Ī potentially better execution plan was found for this statement. The database recommends running DBMS_SQLTUNE.ACCEPT_SQL_PROFILE to implement the profile, which enables the statement to run 98.53% faster. In this example, the database found a better plan for a SELECT statement that uses several expensive joins. For example, a profile may use the TABLE_STATS hint to set object statistics for tables when the statistics are missing or stale. Rather, the hints correct errors in the optimizer estimation algorithm that lead to suboptimal plans. These hints do not specify any particular plan.
Internally, a SQL profile is implemented using hints that address different types of problems. You can implement a new SQL profile for the statement. In this case, the Automatic SQL Tuning task again captures the statement as high-load SQL. The statement may appear as high-load or top SQL. In this case, performance of the SQL statement may degrade. Over time, profile content can become outdated. In general, you do not need to refresh SQL profiles. The profile continues to be relevant even if the data distribution or access path of the corresponding statement changes. As tables grow, or as indexes are created or dropped, the plan for a SQL profile can change. If either the optimizer environment or SQL profile changes, then the optimizer can create a new plan. In this example, the plan is in the SQL plan baseline for the statement. The optimizer uses the SQL profile and the environment to generate an execution plan. The supplemental statistics in the SQL profileįigure 22-1 shows the relationship between a SQL statement and the SQL profile for this statement. The environment, which contains the database configuration, bind variable values, optimizer statistics, data set, and so on When choosing plans, the optimizer has the following sources of information: For example, if a SQL profile exists for SELECT * FROM t WHERE x=5 AND y=10, then the profile stores the actual number of rows returned. A profile uses parts of the query to determine whether the estimated cardinalities are close to the actual cardinalities and, if a mismatch exists, uses the corrected cardinalities. The cardinality measure is based on sampling the WHERE clause rather than on statistical projection. The profile does not itself determine a specific plan.Ī SQL profile contains, among other statistics, a set of cardinality adjustments. The SQL profile contains supplemental statistics for the entire statement, not individual plans.
0 kommentar(er)
