Which statement correctly describes the SQL profiling performed by the SQL Tuning Advisor?
Which statement correctly describes the SQL profiling performed by the SQL Tuning Advisor?
The SQL Tuning Advisor performs profiling by collecting auxiliary information for a SQL statement to eliminate estimation errors. This process involves creating SQL profiles, which contain corrections for poor optimizer estimates discovered during tuning. These profiles help improve the cardinality and selectivity estimates, guiding the optimizer to choose better execution plans without restructuring the SQL statements themselves.
Agree with D
I think D https://docs.oracle.com/cd/E11882_01/server.112/e41573/sql_tune.htm
To quote the white paper: A SQL profile is a set of auxiliary information specific to a SQL statement. Conceptually, a SQL profile is to a SQL statement what statistics are to a table or index. The database can use the auxiliary information to improve execution plans. A SQL profile contains corrections for poor optimizer estimates discovered by the SQL Tuning advisor. This information can improve optimizer cardinality and selectivity estimates, which in turn leads the optimizer to select better plans. Unlike hints and stored outlines, profiles do not tie the optimizer to a specific plan or subplan. Profiles fix incorrect estimates while giving the optimizer the flexibility to pick the best plan in different situations. https://www.oracle.com/technetwork/database/manageability/sql-profiles-technical-overview-128535.pdf So, D is the best answer here
Oracle White Paper—SQL Profiles: Technical Overview SQL Profiles: Technical Overview What is a SQL profile? A SQL profile is a set of auxiliary information specific to a SQL statement. Conceptually, a SQL profile is to a SQL statement what statistics are to a table or index. The database can use the auxiliary information to improve execution plans. A SQL profile contains corrections for poor optimizer estimates discovered by the SQL Tuning advisor. This information can improve optimizer cardinality and selectivity estimates, which in turn leads the optimizer to select better plans. The SQL profile does not contain information about individual execution plans. Rather, the optimizer has the following sources of information when choosing plans: • The environment, which contains the database configuration, bind variable values, optimizer statistics, data set, etc. • The supplemental statistics in the SQL profile Therefore, SQL profiles just guide the optimizer to a better plan.
it is B SQL Tuning Advisor is a mechanism for resolving problems related to suboptimally performing SQL statements. https://docs.oracle.com/en/database/oracle/oracle-database/19/tgsql/sql-tuning-advisor.html#GUID-8E1A39CB-A491-4254-8B31-9B1DF7B52AA1
But it will not restructure a SQL statement.
D SQL profiling is the verification by the Automatic Tuning Optimizer of its own estimates.
B A SQL profile contains additional statistics specific to a SQL statement and enables the optimizer to generate a better execution plan.
D Oracle SQL profile is one of the features provided by Oracle at an extra cost that you can use to guide the decision path the optimizer uses when selecting the execution plans.It comes with the Tuning pack activation,as a output to SQL Tuning advisor and provides auxiliary information to the optimizer to better the execution plans.
answer is D
D is right. SQL profiling is the verification by the Automatic Tuning Optimizer of its own estimates. By reviewing execution history and testing the SQL, the optimizer can ensure that it has the most accurate information available to generate execution plans. SQL profiling is related to but distinct from the steps of generating SQL Tuning Advisor recommendations and implementing these recommendations. src: https://docs.oracle.com/en/database/oracle/oracle-database/19/tgsql/sql-tuning-advisor.html#GUID-93AEDB23-107C-4357-8D7E-2BDE3D1893DF
It's B.
B. It is a set of recommendations by the optimizer to restructure a SQL statement to avoid suboptimal execution plans. "The recommendation relates to a collection of statistics on objects, creation of new indexes, restructuring of the SQL statement, or creation of a SQL profile"
read it again please