SQL PROFILES
The query optimizer can sometimes produce inaccurate estimates about an attribute of a statement due to lack of information, leading to poor execution plans. Traditionally, users have corrected this problem by manually adding hints to the application code to guide the optimizer into making correct decisions. For packaged applications, changing application code is not an option and the only alternative available is to log a bug with the application vendor and wait for a fix.
A SQL Profile is stored persistently in the data dictionary, so it does not require any application code changes.After the SQL profiling completes, the query optimizer uses the information stored in the SQL profile, in conjunction with regular database statistics, to generate execution plans.
Automatic SQL tuning deals with this problem with its SQL profiling capability. The Automatic Tuning Optimizer creates a profile of the SQL statement called a SQL Profile, consisting of auxiliary statistics specific to that statement. The query optimizer under normal mode makes estimates about cardinality, selectivity, and cost that can sometimes be off by a significant amount resulting in poor execution plans. SQL Profile addresses this problem by collecting additional information using sampling and partial execution techniques to verify and, if necessary, adjust these estimates.
The scope of a SQL Profile can be controlled by the CATEGORY profile attribute. This attribute determines which user sessions can apply the profile. You can view the CATEGORY attribute for a SQL Profile in CATEGORY column of the DBA_SQL_PROFILES view. By default, all profiles are created in the DEFAULT category. This means that all user sessions where the SQLTUNE_CATEGORY initialization parameter is set to DEFAULT can use the profile.
SQL Profiles apply to the following statement types:
* SELECT statements
* UPDATE statements
* INSERT statements (only with a SELECT clause)
* DELETE statements
* CREATE TABLE statements (only with the AS SELECT clause)
* MERGE statements (the update or insert operations)
So, sql profiles simply take the query - execute bits of it, figure out how many rows are actually returned (or look at the execution HISTORY to see how many rows are actually flowing from the row sources as well) and STORE that information as extended statistics - and use that information the next time the query is hard parsed
stored outlines are a set of hints that say "use this index, do this table first, do that next, use this access path, perform this filter then that filter"....
sql profiles are more like extended statistics - they are the result of "analyzing a query", the information provided to the optimizer is not HOW to perform the query - but rather better information about how many rows will flow out of a step in the plan, how selective something is.
say you have a query, you generate a stored outline for it. You now add an index to the underlying table. This index would be GREAT for the query. A stored outline won't use it, the stored outline says "use this index - query that table - then do this". Since the GREAT index did not exist when the outline was generated - it won't be used.
Profiles only take effect when resource limits are "turned on" for the database as a whole.
Specify the RESOURCE_LIMIT initialization parameter.
RESOURCE_LIMIT = TRUE
STORED OUTLINES
A stored outline is a collection of hints associated with a specific SQL statement that allows a standard execution plan to be maintained, regardless of changes in the system environment or associated statistics.
Despite the effectiveness of using SQL Outlines, it has the following Disadvantages
1.The biggest flaw is that the behavior of Stored Outlines in association to cursor sharing is totally dependent of the cursor_sharing parameter, which means that if cursor_sharing is set to exact (which is the default), and we need to force a hint to a statement that doesn't use bind variables, creating an outline for all the different literals can range from aggravating to impossible.Note that there is a one-to-one correspondence between SQL text and its outline. If you specify a different literal in a predicate, then a different outline applies. To avoid this, replace literals in applications with bind variables.
The query optimizer can sometimes produce inaccurate estimates about an attribute of a statement due to lack of information, leading to poor execution plans. Traditionally, users have corrected this problem by manually adding hints to the application code to guide the optimizer into making correct decisions. For packaged applications, changing application code is not an option and the only alternative available is to log a bug with the application vendor and wait for a fix.
A SQL Profile is stored persistently in the data dictionary, so it does not require any application code changes.After the SQL profiling completes, the query optimizer uses the information stored in the SQL profile, in conjunction with regular database statistics, to generate execution plans.
Automatic SQL tuning deals with this problem with its SQL profiling capability. The Automatic Tuning Optimizer creates a profile of the SQL statement called a SQL Profile, consisting of auxiliary statistics specific to that statement. The query optimizer under normal mode makes estimates about cardinality, selectivity, and cost that can sometimes be off by a significant amount resulting in poor execution plans. SQL Profile addresses this problem by collecting additional information using sampling and partial execution techniques to verify and, if necessary, adjust these estimates.
The scope of a SQL Profile can be controlled by the CATEGORY profile attribute. This attribute determines which user sessions can apply the profile. You can view the CATEGORY attribute for a SQL Profile in CATEGORY column of the DBA_SQL_PROFILES view. By default, all profiles are created in the DEFAULT category. This means that all user sessions where the SQLTUNE_CATEGORY initialization parameter is set to DEFAULT can use the profile.
SQL Profiles apply to the following statement types:
* SELECT statements
* UPDATE statements
* INSERT statements (only with a SELECT clause)
* DELETE statements
* CREATE TABLE statements (only with the AS SELECT clause)
* MERGE statements (the update or insert operations)
So, sql profiles simply take the query - execute bits of it, figure out how many rows are actually returned (or look at the execution HISTORY to see how many rows are actually flowing from the row sources as well) and STORE that information as extended statistics - and use that information the next time the query is hard parsed
stored outlines are a set of hints that say "use this index, do this table first, do that next, use this access path, perform this filter then that filter"....
sql profiles are more like extended statistics - they are the result of "analyzing a query", the information provided to the optimizer is not HOW to perform the query - but rather better information about how many rows will flow out of a step in the plan, how selective something is.
say you have a query, you generate a stored outline for it. You now add an index to the underlying table. This index would be GREAT for the query. A stored outline won't use it, the stored outline says "use this index - query that table - then do this". Since the GREAT index did not exist when the outline was generated - it won't be used.
Profiles only take effect when resource limits are "turned on" for the database as a whole.
Specify the RESOURCE_LIMIT initialization parameter.
RESOURCE_LIMIT = TRUE
STORED OUTLINES
A stored outline is a collection of hints associated with a specific SQL statement that allows a standard execution plan to be maintained, regardless of changes in the system environment or associated statistics.
Despite the effectiveness of using SQL Outlines, it has the following Disadvantages
1.The biggest flaw is that the behavior of Stored Outlines in association to cursor sharing is totally dependent of the cursor_sharing parameter, which means that if cursor_sharing is set to exact (which is the default), and we need to force a hint to a statement that doesn't use bind variables, creating an outline for all the different literals can range from aggravating to impossible.Note that there is a one-to-one correspondence between SQL text and its outline. If you specify a different literal in a predicate, then a different outline applies. To avoid this, replace literals in applications with bind variables.
2.It has some performance implications since when outlines are enabled Oracle will check if an outline exists for each new statement it parses.
3.And in top of it all, the implementation and usage of the feature are unintuitive and often clumsy.
3.And in top of it all, the implementation and usage of the feature are unintuitive and often clumsy.
No comments:
Post a Comment