Exam 1z0-083 topic 1 question 139 discussion

Agree with D

I think D https://docs.oracle.com/cd/E11882_01/server.112/e41573/sql_tune.htm

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 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

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.

It's B.

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.

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"