What two statements are true regarding the recommendations received from the SQL Access Advisor?

What two statements are true regarding the recommendations received from the SQL
Access Advisor? (Choose two.)

What two statements are true regarding the recommendations received from the SQL
Access Advisor? (Choose two.)

A.
It cannot generate recommendations that support multiple workload queries.

B.
It can recommend partitioning on tables provided that the workloads have some
predicates and joins on the columns of the NUMBER or DATE type.

C.
It can recommend partitioning only on tables that have at least 10,000 rows.

D.
It can recommend only B-tree indexes and not bitmap or function-based indexes.

Show Hint

Leave a Reply 2

Your email address will not be published. Required fields are marked *

Jake from SF

Jake from SF

The answers are not accurate:

https://docs.oracle.com/cd/B19306_01/server.102/b14211/advisor.htm#CHDEEBAI

Materialized views and indexes are essential when tuning a database to achieve optimum performance for complex, data-intensive queries. The SQL Access Advisor helps you achieve your performance goals by recommending the proper set of materialized views, materialized view logs, and indexes for a given workload. Understanding and using these structures is essential when optimizing SQL as they can result in significant performance improvements in data retrieval. The advantages, however, do not come without a cost. Creation and maintenance of these objects can be time consuming, and space requirements can be significant.

The SQL Access Advisor recommends bitmap, function-based, and B-tree indexes. A bitmap index offers a reduced response time for many types of ad hoc queries and reduced storage requirements compared to other indexing techniques. B-tree indexes are most commonly used in a data warehouse to index unique or near-unique keys.

Another component of the SQL Access Advisor also recommends how to optimize materialized views so that they can be fast refreshable and take advantage of general query rewrite.

The SQL Access Advisor can be run from Oracle Enterprise Manager (accessible from the Advisor Central page) using the SQL Access Advisor Wizard or by invoking the DBMS_ADVISOR package. The DBMS_ADVISOR package consists of a collection of analysis and advisory functions and procedures callable from any PL/SQL program. Figure 17-1 illustrates how the SQL Access Advisor recommends materialized views for a given workload obtained from a user-defined table or the SQL cache. If a workload is not provided, it can generate and use a hypothetical workload also.

Reply