Database Reference
In-Depth Information
SQL Performance Analyzer tasks can be created by clicking any of the links and working through the wizards.
The tasks are then scheduled as an EM Job in the EM12c environment. You may check the status by manually
refreshing the interface view or by leaving the View Data option at its default, which uses a 15-second interval.
SQL Access Advisor
The SQL Access Advisor provides a graphical interface for evaluating existing objects in the database for efficiency or
recommending new objects that may increase performance through physical optimization options. Figure
9-42
shows
the initial advisor options available from the interface.
Figure 9-42.
The SQL Access Advisor performance options for database targets
The SQL Access Advisor (accessible from the Performance drop-down, but part of the Advisor Central features)
has two options. The first inspects current objects and makes recommendations based on what is already present
in the system. The second option inspects historical SQL for recommendations on indexes and materialized views
that could assist in performance gains. As with any advisor, you must review and verify the SQL Access Advisor
recommendations before implementing them.
SQL Tuning Sets
A
SQL tuning set
is a collection of SQL statements, which can be bound by executing schema, application
module/action, or a set of bind variables, cursor fetches, execution count, command type, or optimizer cost.
SQL tuning sets can be migrated to other databases, allowing the administrator to perform tuning tests on other
environments. Production performance issues can be migrated to a secondary stage environment to duplicate the
issues experienced and resolution scenarios tested.
■
Understanding the sQl is essential for an administrator who wants to understand usage and optimization in
their database environment. the sQl tuning advisor and the sQl access advisor utilize sQl tuning sets to give the best
advice on optimizing the oracle environment.
Note
SQL Plan Control
Another option for controlling performance is to create and store SQL plans. SQL plans are one of the features utilized
by SQL profiles and can be viewed in the SQL Plans section by clicking Performance
➤
SQL
➤
SQL Plan Control from
the Database performance home page (see Figure
9-43
).
