How to diagnose slow Oracle database running or hang problems

Starting from oracle11gr1, The dia0 background process starts to collect Hang analysis information and store it in the hanganalysiscache in the memory. It collects local Hang points every 3 seconds.

Starting from oracle11gr1, The dia0 background process starts to collect Hang analysis information and store it in the memory's hang analysis cache. It collects local Hang points every 3 seconds.

To diagnose the slow running of Oracle, you must first determine which diagnostic information to collect. You can use the following diagnostic methods:
1. Is the problem of slow database operation common or occurs at a specific time?
If the database runs slowly is a common problem, you can collect awr or statspack reports for this period of time when the problem occurs (usually the collection interval is one hour ). the following describes how to generate an awr report:
Awr collects persistent System Performance Statistics by sys users and stores the statistics in the sysaux tablespace. by default, snapshots are generated once an hour and retained for 7 days. the awr report outputs a series of statistics based on specified snapshots for performance analysis and other problems.
Run basic reports
Run the following script to generate an awr report:
$ ORACLE_HOME/rdbms/admin/awrrpt. SQL

You can determine the time interval for generating a snapshot Based on the reason for collecting the awr report. You can also specify the format (text or html) for generating the awr report ).

Generate various types of awr reports
You can run various SQL scripts to generate various types of awr reports according to various requirements. Each report has two formats (txt or html ):
Awrrpt. SQL
Displays various statistics within the specified snapshot range.

Awrrpti. SQL
Displays statistics of a specified snapshot range in a specific database and instance.

Awrsqrpt. SQL
Displays statistics of a specific SQL statement within a specified snapshot range. The report is run to check or investigate the performance of a specific SQL statement.

Awrsqrpi. SQL
Displays the statistics of a specific SQL statement in a specified snapshot range.

Awrddrpt. SQL
Compare the detailed performance data and configuration during the two selected time intervals

Awrddrpi. SQL
Compare the detailed performance quantity and configuration in a specific database and a peaceful coexistence instance during the two selected time intervals

Various awr-related operations
How to modify awr snapshot settings:
BEGIN
DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings (
Retention => 43200, -- Minutes (43200 = 30 Days ).
-- Current value retained if NULL.
Interval => 30); -- Minutes. Current value retained if NULL.
END;
/

Create an awr baseline:
BEGIN
DBMS_WORKLOAD_REPOSITORY.create_baseline (
Start_snap_id => 10,
End_snap_id = & gt; 100,

Baseline_name => 'awr First baseline ');
END;
/

A new dbms_workload_repository.create_baseline_template process is introduced in oracle11G to create an awr baseline template.
BEGIN
DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE_TEMPLATE (
Start_time => to_date ('& start_date_time', '& start_date_time_format '),
End_time => to_date ('& end_date_time', '& end_date_time_format '),
Baseline_name => 'morning ',
Template_name => 'morning ',
Expiration => NULL );
END;
/

"Expiration => NULL" means this baseline will always be valid.

Deletes an awr baseline.
BEGIN
DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE (
Baseline_name => 'awr First baseline ');
END;
/

You can also delete an awr baseline from an old database:
BEGIN
DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE (baseline_name => 'peak baseline ',
Cascade => FALSE, dbid => 3310949047 );
END;
/

Delete An awr snapshot:
BEGIN
DBMS_WORKLOAD_REPOSITORY.drop_snapshot_range (
(Low_snap_id => 40,
High_snap_id => 80 );
END;
/

You may also specify a template for the awr baseline created or deleted during the reporting period:
BEGIN
DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE_TEMPLATE (
Day_of_week => 'monday ',
Hour_in_day => 9,
Duration => 3,
Start_time => to_date ('& start_date_time', '& start_date_time_format '),
End_time => to_date ('& end_date_time', '& end_date_time_format '),
Baseline_name_prefix => 'Monday _ morning'
Template_name => 'Monday _ MORNING ',
Expiration => 30 );
END;
/

The baseline will be generated every Monday during the period from '& start_date_time' to '& end_date_time '.

A manually generated awr snapshot:
BEGIN
DBMS_WORKLOAD_REPOSITORY.create_snapshot ();
END;
/

Workload data Portfolio View:
V $ ACTIVE_SESSION_HISTORY-displays historical Active session information, which is sampled the same per second
V $ METRIC-display metrics
V $ METRICNAME-display the metrics related to each measurement standard group
V $ METRIC_HISTORY-display historical metrics
V $ METRICGROUP-Display All metric standard groups
DBA_HIST_ACTIVE_SESS_HISTORY-displays detailed information about historical active sessions.
DBA_HIST_BASELINE-displays baseline information
DBA_HIST_DATABASE_INSTANCE-display database environment information
DBA_HIST_SNAPSHOT-display snapshot Information
DBA_HIST_ SQL _PLAN-displays the SQL Execution Plan
DBA_HIST_WR_CONTROL-displays awr settings

If the database runs slowly at a specific time, an awr or statspack report can be generated when the problem exists. The report interval contains the time when the problem occurs. in addition, you can compare the reports for normal operation of databases with the same time interval to collect reports without any problems.

,