Oracle: Знакомство с Automatic Workload Repository
Посмотреть текущие настройки AWR
select * FROM DBA_HIST_WR_CONTROL;
Workload Repository Views
The following workload repository views are available:
V$ACTIVE_SESSION_HISTORY - Displays the active session history (ASH) sampled every second.
V$METRIC - Displays metric information.
V$METRICNAME - Displays the metrics associated with each metric group.
V$METRIC_HISTORY - Displays historical metrics.
V$METRICGROUP - Displays all metrics groups.
DBA_HIST_ACTIVE_SESS_HISTORY - Displays the history contents of the active session history.
DBA_HIST_BASELINE - Displays baseline information.
DBA_HIST_DATABASE_INSTANCE - Displays database environment information.
DBA_HIST_SNAPSHOT - Displays snapshot information.
DBA_HIST_SQL_PLAN - Displays SQL execution plans.
DBA_HIST_WR_CONTROL - Displays AWR settings.
Кусок репорта для примера:
AWR
Создать отчёт
@$ORACLE_HOME/rdbms/admin/awrrpt.sql
-- awrrpti.sql allows you to select a single instance
@$ORACLE_HOME/rdbms/admin/awrrpti.sql
-- awrrpti.sql allows you to select a single instance
@$ORACLE_HOME/rdbms/admin/awrrpti.sql
Посмотреть текущие настройки AWR
select * FROM DBA_HIST_WR_CONTROL;
Workload Repository Views
The following workload repository views are available:
V$ACTIVE_SESSION_HISTORY - Displays the active session history (ASH) sampled every second.
V$METRIC - Displays metric information.
V$METRICNAME - Displays the metrics associated with each metric group.
V$METRIC_HISTORY - Displays historical metrics.
V$METRICGROUP - Displays all metrics groups.
DBA_HIST_ACTIVE_SESS_HISTORY - Displays the history contents of the active session history.
DBA_HIST_BASELINE - Displays baseline information.
DBA_HIST_DATABASE_INSTANCE - Displays database environment information.
DBA_HIST_SNAPSHOT - Displays snapshot information.
DBA_HIST_SQL_PLAN - Displays SQL execution plans.
DBA_HIST_WR_CONTROL - Displays AWR settings.
ADDM (Automatic Database Diagnostic Monitor)
ADDM is a powerful self-diagnostic engine built directly into the kernel of the Oracle database. Using the AWR infrastructure, ADDM is able to holistically analyze the system, identify the major problem in the system (not the symptoms), and recommend corrective action (which some times may require use of a separate Oracle10g advisor). To focus the attention on the actual problems, ADDM also shows the areas of the system that have no problems.
Создать отчёт
$ORACLE_HOME/rdbms/admin/addmrpt.sql
Кусок репорта для примера:
FINDING 1: 28% impact (97 seconds)
----------------------------------
Individual database segments responsible for significant user I/O wait were found.
RECOMMENDATION 1: Segment Tuning, 28% benefit (97 seconds)
ACTION: Run "Segment Advisor" on TABLE "SCOTT.T" with object id 51924.
RELEVANT OBJECT: database object with id 51924
ACTION: Investigate application logic involving I/O on TABLE "SCOTT.T" with object id 51924.
RELEVANT OBJECT: database object with id 51924
RATIONALE: The SQL statement with SQL_ID "5328shb1qxs6u" spent
significant time waiting for User I/O on the hot object.
RELEVANT OBJECT: SQL statement with SQL_ID 5328shb1qxs6u
insert into a select * from t
SYMPTOMS THAT LED TO THE FINDING:
Wait class "User I/O" was consuming significant database time. (30% impact [105 seconds])
ADDITIONAL INFORMATION
----------------------
Wait class "Administrative" was not consuming significant database time.
Wait class "Application" was not consuming significant database time.
Wait class "Cluster" was not consuming significant database time.
Wait class "Concurrency" was not consuming significant database time.
----------------------------------
Individual database segments responsible for significant user I/O wait were found.
RECOMMENDATION 1: Segment Tuning, 28% benefit (97 seconds)
ACTION: Run "Segment Advisor" on TABLE "SCOTT.T" with object id 51924.
RELEVANT OBJECT: database object with id 51924
ACTION: Investigate application logic involving I/O on TABLE "SCOTT.T" with object id 51924.
RELEVANT OBJECT: database object with id 51924
RATIONALE: The SQL statement with SQL_ID "5328shb1qxs6u" spent
significant time waiting for User I/O on the hot object.
RELEVANT OBJECT: SQL statement with SQL_ID 5328shb1qxs6u
insert into a select * from t
SYMPTOMS THAT LED TO THE FINDING:
Wait class "User I/O" was consuming significant database time. (30% impact [105 seconds])
ADDITIONAL INFORMATION
----------------------
Wait class "Administrative" was not consuming significant database time.
Wait class "Application" was not consuming significant database time.
Wait class "Cluster" was not consuming significant database time.
Wait class "Concurrency" was not consuming significant database time.
