Which package provides API’s for the SQL Tuning Advisor?
A.
DBMS_MONITOR
B.
DBMS_STATS
C.
DBMS_SQLTUNE
D.
DBMS_ADVISOR
Explanation:
Answer option C is correct.
The recommended interface for running the SQL Tuning Advisor is the Oracle Enterprise Manager.
However, if Oracle Enterprise Manager is unavailable, you can run the SQL Tuning Advisor using procedures in the DBMS_SQLTUNE package. The DBMS_SQLTUNE package provides the following three interrelated areas of functionality:
– SQL Tuning Advisor Subprograms
– SQL Profile Subprograms
– SQL Tuning Set Subprograms
Answer option B is incorrect. With the DBMS_STATS package you can view and modify optimizer statistics gathered for database objects. The Oracle RDBMS allows you to collect statistics of many different kinds as an aid to improve performance. This package is concerned with optimizer statistics only. Given that Oracle sets automatic statistics collection of this kind by default, this package is intended for only specialized cases. The statistics of interest to be viewed or modified can reside in the dictionary or in a table created in the user’s schema for this purpose. You can also collect and manage user-defined statistics for tables and domain indexes using this package.
Answer option D is incorrect. DBMS_ADVISOR is part of the Server Manageability Suite of Advisors, a set of expert systems that identifies and helps resolve performance problems relating to the various database server components.
Answer option A is incorrect. The DBMS_MONITOR package lets you use PL/SQL for controlling additional tracing and statistics gathering.
C