Capture performance statistics

Go back to OCM 12c Preparation Project 

Hands On Lab – Index

[1] Measuring Database Performance
[2] Gathering Database Statistics
[3] Automatic Performance Diagnostics
[4] Comparing Database Performance Over Time
[5] Analyzing Sampled Data

[1] Measuring Database Performance
-> About Database Statistics
#Time Model Statistics
(V$SESS_TIME_MODEL and V$SYS_TIME_MODEL views).

#Active Session History Statistics
V$ACTIVE_SESSION_HISTORY
DBA_HIST_ACTIVE_SESS_HISTORY 
Note:
Active session history sampling is also available for Active Data Guard 
physical standby instances and Oracle Automatic Storage Management (
Oracle ASM) instances. On these instances, the current session activity 
is collected and displayed in the V$ACTIVE_SESSION_HISTORY view, but not 
written to disk.

#Wait Events Statistics
The V$SYSTEM_EVENT view shows wait event statistics for the foreground 
activities of a database instance and the wait event statistics for the 
database instance. The V$SYSTEM_WAIT_CLASS view shows these foreground 
and wait event statistics at the instance level after aggregating to wait 
classes. V$SESSION_EVENT and V$SESSION_WAIT_CLASS show wait event and 
wait class statistics at the session level.

#Session and System Statistics
A large number of cumulative database statistics on a system and session 
level are accessible using the V$SYSSTAT and V$SESSTAT views.

-> Interpreting Database Statistics
Using Hit Ratios
Using Wait Events with Timed Statistics
Using Wait Events without Timed Statistics
Using Idle Wait Events
Comparing Database Statistics with Other Factors
Using Computed Statistics

[2] Gathering Database Statistics
-> About Gathering Database Statistics
By default, Oracle Database captures snapshots once every hour and 
retains them in the database for 8 days. With these default settings, 
a typical system with an average of 10 concurrent active sessions can 
require approximately 200 to 300 MB of space for its AWR data.
DBA_HIST_WR_CONTROL

-> Managing the Automatic Workload Repository

#Creating Snapshots Using the Command-Line Interface

BEGIN
DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ();
END;
/

#Dropping Snapshots Using the Command-Line Interface

BEGIN
DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE (low_snap_id => 22, 
high_snap_id => 32, dbid => 3310949047);
END;
/

#Modifying Snapshot Settings

BEGIN
DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS( retention => 43200, 
interval => 30, topnsql => 100, dbid => 3310949047);
END;
/

The retention period is specified as 43200 minutes (30 days).
The interval between each snapshot is specified as 30 minutes.
The number of Top SQL to flush for each SQL criteria is specified as 100.

To verify the current settings for your database, 
use the DBA_HIST_WR_CONTROL view.

------------------
Managing Baselines
------------------
#Creating a Baseline

BEGIN
DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE (start_snap_id => 270, 
end_snap_id => 280, baseline_name => 'peak baseline', 
dbid => 3310949047, expiration => 30);
END;
/

BEGIN
DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE (baseline_name => 'peak baseline',
cascade => FALSE, dbid => 3310949047);
END;
/

BEGIN
DBMS_WORKLOAD_REPOSITORY.RENAME_BASELINE (
old_baseline_name => 'peak baseline', 
new_baseline_name => 'peak mondays', 
dbid => 3310949047);
END;
/

BEGIN
DBMS_WORKLOAD_REPOSITORY.MODIFY_BASELINE_WINDOW_SIZE (
window_size => 30, 
dbid => 3310949047);
END;
/

----------------------------
Managing Baseline Templates
----------------------------
-Creating a Single Baseline Template

BEGIN
DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE_TEMPLATE (
start_time => '2012-04-02 17:00:00 PST', 
end_time => '2012-04-02 20:00:00 PST', 
baseline_name => 'baseline_120402', 
template_name => 'template_120402', expiration => 30, 
dbid => 3310949047);
END;
/

-Creating a Repeating Baseline Template

BEGIN
DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE_TEMPLATE (
day_of_week => 'monday', hour_in_day => 17,
duration => 3, expiration => 30,
start_time => '2012-04-02 17:00:00 PST', 
end_time => '2012-12-31 20:00:00 PST', 
baseline_name_prefix => 'baseline_2012_mondays_', 
template_name => 'template_2012_mondays',
dbid => 3310949047);
END;
/

-Dropping a Baseline Template

BEGIN
DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE_TEMPLATE (
template_name => 'template_2012_mondays',
dbid => 3310949047);
END;
/

#Transporting Automatic Workload Repository Data

Extracting AWR Data
@$ORACLE_HOME/rdbms/admin/awrextr.sql

Loading AWR Data
@$ORACLE_HOME/rdbms/admin/awrload.sql

#Using Automatic Workload Repository Views

DBA_HIST_ACTIVE_SESS_HISTORY 
DBA_HIST_BASELINE 
DBA_HIST_BASELINE_DETAILS 
DBA_HIST_BASELINE_TEMPLATE 
DBA_HIST_DATABASE_INSTANCE 
DBA_HIST_DB_CACHE_ADVICE 
DBA_HIST_DISPATCHER 
DBA_HIST_DYN_REMASTER_STATS 
DBA_HIST_IOSTAT_DETAIL 
DBA_HIST_SHARED_SERVER_SUMMARY 
DBA_HIST_SNAPSHOT 
DBA_HIST_SQL_PLAN 
DBA_HIST_WR_CONTROL

-> Generating Automatic Workload Repository Reports
Generating an AWR Report for the Local Database
@$ORACLE_HOME/rdbms/admin/awrrpt.sql

Generating an AWR Report for a Specific Database
@$ORACLE_HOME/rdbms/admin/awrrpti.sql

Generating an Oracle RAC AWR Report for the Local Database
@$ORACLE_HOME/rdbms/admin/awrgrpt.sql

Generating an Oracle RAC AWR Report for a Specific Database
@$ORACLE_HOME/rdbms/admin/awrgrpti.sql

Generating an AWR Report for a SQL Statement on the Local Database
@$ORACLE_HOME/rdbms/admin/awrsqrpt.sql

Generating an AWR Report for a SQL Statement on a Specific Database
@$ORACLE_HOME/rdbms/admin/awrsqrpi.sql

-> Generating Performance Hub Active Report
Performance Hub feature of EM Express provides an active report with a 
consolidated view of all performance data for a specified time period. 
The report is fully interactive; its contents are saved in a HTML file, 
which you can access offline using a web browser.


To generate a Performance Hub active report:
@$ORACLE_HOME/rdbms/admin/perfhubrpt.sql

[3] Automatic Performance Diagnostics

-> Overview of the Automatic Database Diagnostic Monitor

#ADDM Analysis

An ADDM analysis can be performed on a pair of AWR snapshots and a set of 
instances from the same database. 
The pair of AWR snapshots define the time period for analysis, and the set 
of instances define the target for analysis.

If you are using Oracle Real Application Clusters (Oracle RAC), 
then ADDM has three analysis modes:

1. Database
In Database mode, ADDM analyzes all instances of the database.

2. Instance
In Instance mode, ADDM analyzes a particular instance of the database.

3. Partial
In Partial mode, ADDM analyzes a subset of all database instances.

An ADDM analysis is performed each time an AWR snapshot is taken and the 
results are saved in the database.
The time period analyzed by ADDM is defined by the last two snapshots 
(the last hour by default). 
ADDM will always analyze the specified instance in Instance mode.

#Using ADDM with Oracle Real Application Clusters
If you are using Oracle RAC, then run ADDM in Database analysis mode to 
analyze the throughput performance of all instances of the database. 
In Database mode, ADDM considers DB time as the sum of the database time 
for all database instances. Using the Database analysis mode enables you 
to view all findings that are significant to the entire database in a 
single report, instead of reviewing a separate report for each instance.

The Database mode report includes findings about database resources 
(such as I/O and interconnect). 
The report also aggregates findings from the various instances if they 
are significant to the entire database. 
For example, if the CPU load on a single instance is high enough to affect 
the entire database, then the finding appears in the Database mode analysis, 
which points to the particular instance responsible for the problem.

#Real-Time ADDM Analysis

Introduced in Oracle Enterprise Manager Cloud Control 12c, Real-Time ADDM 
helps you to analyze and resolve problems in unresponsive or hung databases 
that traditionally require you to restart the database.

Real-Time ADDM runs through a set of predefined criteria to analyze the 
current performance of the database. 
After analyzing the problem, Real-Time ADDM helps you to resolve the 
identified issues—such as deadlocks, hangs, shared pool contention, and 
other exception situations—without having to restart the database.

1. Real-Time ADDM Connection Modes

Normal connection
In this mode, Real-Time ADDM performs a normal JDBC connection to the database. 
This mode is intended to perform extensive performance analysis of the database 
when some connectivity is available.

Diagnostic connection
In this mode, Real-Time ADDM performs a latch-less connection to the database. 
This mode is intended for extreme hang situations when a normal JDBC connection 
is not possible.

2. Real-Time ADDM Triggers

Starting with Oracle Database 12c, Real-Time ADDM proactively detects 
transient database performance issues. To do this, Real-Time ADDM runs 
automatically every 3 seconds and uses in-memory data to diagnose any 
performance spikes in the database.

Real-Time ADDM triggers an analysis automatically when a performance 
problem is detected.
1. Every 3 seconds, the manageability monitor process (MMON) performs an 
action to obtain performance statistics without lock or latch.
2. The MMON process checks these statistics and triggers a Real-Time ADDM 
analysis if any of the issues listed in Table 7-1 are detected.
3. The MMON slave process creates the report and stores it in the AWR.
To view metadata for the report, use the DBA_HIST_REPORTS view.

#Real-Time ADDM Trigger Controls
Duration between reports
Oracle RAC control
Repeated triggers
Newly identified issues

-> Setting Up ADDM
CONTROL_MANAGEMENT_PACK_ACCESS
The default setting is DIAGNOSTIC+TUNING. 
Setting CONTROL_MANAGEMENT_PACK_ACCESS to NONE disables ADDM.

The STATISTICS_LEVEL parameter should be set to the TYPICAL or ALL to 
enable automatic database diagnostic monitoring. 
The default setting is TYPICAL. Setting STATISTICS_LEVEL to BASIC disables 
many Oracle Database features, including ADDM, and is strongly discouraged.

ADDM analysis of I/O performance partially depends on a single argument, 
DBIO_EXPECTED, that describes the expected performance of the I/O subsystem. 
The value of DBIO_EXPECTED is the average time it takes to read a single 
database block in microseconds. Oracle Database uses the default value of 10 
milliseconds, which is an appropriate value for most modern hard drives. 
If your hardware is significantly different—such as very old hardware or 
very fast RAM disks—then consider using a different value.

For example, if the measured value if 8000 microseconds, 
you should execute the following command as SYS user:

EXECUTE DBMS_ADVISOR.SET_DEFAULT_TASK_PARAMETER(
'ADDM', 'DBIO_EXPECTED', 8000);

-> Diagnosing Database Performance Problems with ADDM

1. Running ADDM in Database Mode

VAR tname VARCHAR2(30);
BEGIN
:tname := 'ADDM for 7PM to 9PM';
DBMS_ADDM.ANALYZE_DB(:tname, 137, 145);
END;
/

2. Running ADDM in Instance Mode

VAR tname VARCHAR2(30);
BEGIN
:tname := 'my ADDM for 7PM to 9PM';
DBMS_ADDM.ANALYZE_INST(:tname, 137, 145, 1);
END;
/

3. Running ADDM in Partial Mode

VAR tname VARCHAR2(30);
BEGIN
:tname := 'my ADDM for 7PM to 9PM';
DBMS_ADDM.ANALYZE_PARTIAL(:tname, '1,2,4', 137, 145);
END;
/

4. Displaying an ADDM Report

SET LONG 1000000 PAGESIZE 0;
SELECT DBMS_ADDM.GET_REPORT(:tname) FROM DUAL;


-> Views with ADDM Information

DBA_ADVISOR_FINDINGS
DBA_ADDM_FINDINGS
DBA_ADVISOR_FINDING_NAMES
DBA_ADVISOR_RECOMMENDATIONS
DBA_ADVISOR_TASKS

addmrpt.sql
run ADDM analysis on a pair on AWR snapshots and to display the textual 
ADDM report of the analysis.

addmrpti.sql 
SQL*Plus script that prompts for dbid and instance_number to run ADDM 
analysis on a pair on AWR snapshots and
display the textual ADDM report of the analysis.


[4] Comparing Database Performance Over Time
-> About Automatic Workload Repository Compare Periods Reports
-> Generating Automatic Workload Repository Compare Periods Reports

Generating an AWR Compare Periods Report for the Local Database
@$ORACLE_HOME/rdbms/admin/awrddrpt.sql

Generating an AWR Compare Periods Report for a Specific Database
@$ORACLE_HOME/rdbms/admin/awrddrpi.sql

Generating an Oracle RAC AWR Compare Periods Report for the Local Database
@$ORACLE_HOME/rdbms/admin/awrgdrpt.sql

Generating an Oracle RAC AWR Compare Periods Report for a Specific Database
@$ORACLE_HOME/rdbms/admin/awrgdrpi.sql

-> Interpreting Automatic Workload Repository Compare Periods Reports

[5] Analyzing Sampled Data
-> About Active Session History
-> Generating Active Session History Reports

Generating an ASH Report on the Local Database Instance
@$ORACLE_HOME/rdbms/admin/ashrpt.sql

Generating an ASH Report on a Specific Database Instance
@$ORACLE_HOME/rdbms/admin/ashrpti.sql

Generating an ASH Report for Oracle RAC
@$ORACLE_HOME/rdbms/admin/ashrpti.sql

-> Interpreting Results from Active Session History Reports

======================
Path to Documentation:
======================
Part II Diagnosing and Tuning Database Performance
5 Measuring Database Performance
6 Gathering Database Statistics
7 Automatic Performance Diagnostics
8 Comparing Database Performance Over Time
9 Analyzing Sampled Data

Thank you for visiting this blog 🙂