Diagnosing the Database and Managing Performance - OCP: Oracle Database 12c Administrator Certified Professional - page 424

FiGURe 9.28 SQL Tuning Advisor recommendations: SQL statement original
explain plan
Using the SQL Access Advisor to Tune a Workload
The SQL Access Advisor is a tuning tool that assists the DBA by offering recommendations
for indexes, partitioning, and materialized view logs for a workload.
Indexing recommendations may include B-tree, bitmap, and function-based indexes. The
SQL Access Advisor may recommend partitioning tables, new partitioned indexes, and new
partitioned materialized views. It also provides recommendations on how to improve the
performance of materialized views by using Fast Refresh and Query Rewrite.
You can manually execute the SQL Access Advisor functions and procedures included in
the DBMS_ADVISOR package. For this exercise, we will use Enterprise Manager.
1. From the database home page in EM, select Performance ➢ SQL ➢ SQL Access Advisor,
as shown in Figure 9.29.
2. From the Initial Options page, shown in Figure 9.30, choose the Recommend New
Access Structures option. Click Continue.
3. First, select the workload source. We'll use the current and recent SQL activity that is
in the cache, shown in Figure 9.31. Click Next to continue.
4. Next, choose the depth and breadth of recommendation options, shown in Figure 9.32.
For this exercise, we just want to view index recommendations, so select the Indexes box.