Your database supports a DSS workload that involves the execution of complex queries:
Currently, the library cache contains the ideal workload for analysis. You want to analyze some of
the queries for an application that are cached in the library cache.
What must you do to receive recommendations about the efficient use of indexes and materialized
views to improve query performance?
A.
Create a SQL Tuning Set (STS) that contains the queries cached in the library cache and run
the SQL Tuning Advisor (STA) on the workload captured in the STS.
B.
Run the Automatic Workload Repository Monitor (ADDM).
C.
Create an STS that contains the queries cached in the library cache and run the SQL
Performance Analyzer (SPA) on the workload captured in the STS.
D.
Create an STS that contains the queries cached in the library cache and run the SQL Access
Advisor on the workload captured in the STS.
Explanation:
* SQL Access Advisor is primarily responsible for making schema modification
recommendations, such as adding or dropping indexes and materialized views. SQL Tuning
Advisor makes other types of recommendations, such as creating SQL profiles and restructuring
SQL statements.
* The query optimizer can also help you tune SQL statements. By using SQL Tuning Advisor and
SQL Access Advisor, you can invoke the query optimizer in advisory mode to examine a SQL
statement or set of statements and determine how to improve their efficiency. SQL Tuning Advisor
and SQL Access Advisor can make various recommendations, such as creating SQL profiles,
restructuring SQL statements, creating additional indexes or materialized views, and refreshing
optimizer statistics.
Note:
* Decision support system (DSS) workload
* The library cache is a shared pool memory structure that stores executable SQL and PL/SQL
code. This cache contains the shared SQL and PL/SQL areas and control structures such as locks
and library cache handles.
Tuning SQL Statements
C
Why D is incorrect?
Sorry, D is correct.
D is correct.
From the student guide, it mentioned “Even though you can submit multiple statements to be analyzed in a single task, each statement is analyzed independently. To obtain tuning recommendations that consider overall performance of a set of SQL, use the SQL Access Advisor.”
For ADDM, “By building on the data captured in the AWR, the ADDM enables the Oracle Database server to diagnose its own performance and determine how identified problems can be resolved.”
The SQL Performance Analyzer can be used to predict and prevent potential performance
problems for any database environment change that affects the structure of the SQL
execution plans.
So the best answer should be D.
D
Here is a great Weblog You may Discover Intriguing that we encourage you to visit.
that may be the end of this article. Here youll locate some sites that we feel youll enjoy, just click the hyperlinks over
the time to read or check out the subject material or sites we have linked to below the
D.
Sql Access Advisor provide advice about index and materialized view.
https://docs.oracle.com/cd/B19306_01/server.102/b14211/advisor.htm#i1008370