Databases Reference
In-Depth Information
Database Advisors
Oracle's advisors deliver database performance, recoverability, and diagnostic service
improvement recommendations via Oracle Enterprise Manager. Each database advisor
has a unique role. Many can be set to automatically take action if preferred.
The Automated Database Diagnostic Monitor (ADDM) performs real-time database
performance diagnostics, recommends potential solutions to performance problems,
and quantifies benefits of those solutions. ADDM, usually set to run every hour, mon‐
itors current snapshots of the database state and workload gathered in the AWR. ADDM
can also be manually triggered to analyze historical data for specific time periods. Real-
time ADDM detects and diagnoses transient problems that include high CPU loads, I/
O in CPU bound situations, over allocated memory, interconnect issues, session and
process limits being reached, and hangs and deadlocks. In Oracle Database 12
c
, real-
time ADDM is automatically run every three seconds, enabling DBAs to resolve dead‐
locks, hangs, shared pool connection issues, and other similar situations without having
to restart the Oracle Database.
Other Oracle Database performance-related advisors include:
SQL Tuning Advisor
Available for the Oracle Database since Oracle Database 11
g
, the SQL Tuning Ad‐
visor analyzes SQL statements, object statistics, access paths, and degree of paral‐
lelism to make improvement recommendations such as creating a new SQL profile
or choosing an alternate execution plan from the AWR. In automatic mode, it will
automatically create a better plan if that plan will yield significant improvement.
SQL Access Advisor
Also available since Oracle Database 11
g
, the SQL Access Advisor provides recom‐
mendations on materialized views, indexes, and materialized view logs that should
be created, dropped, and retained. The Advisor also makes recommendations about
partitioning to improve performance.
SPM Evolve Advisor
New in Oracle Database 12
c
, the SQL Plan Management (SPM) Evolve Advisor
enables scheduling of testing of new plans added to the SQL plan baseline, described
in
Chapter 4
, compares the new plans versus accepted plans for cost, and automat‐
ically accepts plans that have a much lower cost.
Memory Advisors
The Memory Advisor is an expert system that provides automatic memory man‐
agement and eliminates manual adjustment of the SGA and PGA when enabled
(and recommended in Oracle Database 11
g
or more recent releases). If just auto‐
matic shared memory is enabled instead, you will have access to the Shared Pool
(SGA) Advisor and PGA Advisor. Finally, if you are manually managing shared
