Shulou(Shulou.com)06/01 Report--

1. View the current AWR save policy

Select * from dba_hist_wr_control

DBID,SNAP_INTERVAL,RETENTION,TOPNSQL

860524039 860524039 860524039 860

The above results show that one SNAPSHOT is generated per hour and retained for 7 days.

two。 Adjust AWR configuration

AWR configurations are all configured through the dbms_workload_repository package

2.1 adjust the frequency and retention strategy of snapshot generated by AWR, for example, change the collection interval to 30 minutes. And keep it for 5 days (note: all in minutes):

Exec dbms_workload_repository.modify_snapshot_settings (interval= > 30, retention= > 5 / 24 / 60)

2.2 close AWR and set interval to 0 to turn off automatic snapshot capture

2.3 create a snapshot manually

Exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ()

2.4 View Snapshot

Select * from sys.wrh$_active_session_history

2.5 manually delete snapshots of a specified range

Exec WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE (low_snap_id = > 22, high_snap_id = > 32, dbid = > 3310949047)

2.6Create baseline

Exec dbms_workload_repository.create_baseline (56, 5, 9, 5, 9, 5, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 5, 9, 9, 9, 9, 5, 9, 5, 9, 9, 5, 9, 5, 9, 5, 9, 5, 9, 5, 9, 5, 9, 5, 9, 5, 9, 5, 9, 5, 9, 5, 9, 5, 9, 5, 9, 5, 9, 5, 9, 5, 9, 5, 9, 5, 9, 5, 9, 5, 9, 5, 4, 4, 9, 9, 4, 4, 4, 3, 4, 9, 1')

2.7 Delete baseline

Exec DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE (baseline_name = > 'apply_interest_1', cascade = > FALSE)

3. Production AWR report

$ORACLE_HOME/rdbms/admin/awrrpt.sql

4.1 Snapshots (Snapshot)

Before the operation of report generation, the keyword snap has appeared many times, you must be full of doubts about it, where did this thing come from who let it come? In fact, Snap is short for Snapshot, which is the embodiment of AWR in terms of automaticity. Although you didn't create it, AWR automatically created it for you (of course, you can also create snapshot manually), and created it regularly (hourly) and cleared it regularly (keep it for the last 7 days).

Snapshots is a collection of historical data at a certain point in time, which can be used by ADDM (Automatic Database Diagnostic Monitor) for performance comparison. By default, AWR can automatically generate Snapshots performance data at an hourly rate and retain it for 7 days, and DBA can manually create, delete, or modify snapshots through the DBMS_WORKLOAD_REPOSITORY process if necessary.

Tip: DBA permission is required to call the DBMS_WORKLOAD_REPOSITORY package.

4.1.1 manually create Snapshots

Create a Snapshots manually through the DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT process, for example:

SQL > exec dbms_workload_repository.create_snapshot ()

PL/SQL procedure successfully completed.

You can then view the Snapshots information you just created through the DBA_HIST_SNAPSHOT view.

4.1.2 manually delete Snapshots

Deleting Snapshots is another process that uses the DBMS_WORKLOAD_REPOSITORY package: DROP_SNAPSHOT_RANGE, which, when executed, can delete more than one Snapshots at a time by specifying the scope of the snap_id, for example:

SQL > select count (0) from dba_hist_snapshot where snap_id between 7509 and 7518

COUNT (0)

-

ten

SQL > begin

2 dbms_workload_repository.drop_snapshot_range (

3 low_snap_id = > 7509

4 high_snap_id = > 7518

5 dbid = > 3812548755)

6 end

7 /

PL/SQL procedure successfully completed.

SQL > select count (0) from dba_hist_snapshot where snap_id between 7509 and 7518

COUNT (0)

-

0

Note that when snapshots is deleted, the ASH records associated with it are also cascaded.

4.1.3 modify Snapshots settings

Through the MODIFY_SNAPSHOT_SETTINGS process, DBA can adjust settings including the frequency of snapshot collection, snapshot retention time, and the number of SQL captured. Corresponding to the three parameters of MODIFY_SNAPSHOT_SETTINGS:

Retention: sets the time for snapshots to be saved, in minutes. The minimum value that can be set is 1 day, and the maximum is 100 years. Setting the parameter value to 0 means that the collected snapshot information is permanently retained.

Interval: sets the frequency of snapshot collection in minutes. The minimum value that can be set is 10 minutes and the maximum is 1 year. If you set the parameter value to 0, the AWR property is disabled.

Topnsql: specifies the number of SQL collected to compare resource consumption, with a minimum of 30 and a maximum of 100000000.

To view the relevant settings of the current snapshot collection, you can view them in the DBA_HIST_WR_CONTROL view, for example:

SQL > select * from dba_hist_wr_control

DBID SNAP_INTERVAL RETENTION TOPNSQL

3812548755 + 00000 01 DEFAULT 00lv 00.0 + 00007 00lv 00lv 00.0

For example, modify the settings of snap_intrval through the MODIFY_SNAPSHOT_SETTTINGS process:

SQL > exec dbms_workload_repository.modify_snapshot_settings (interval= > 120)

PL/SQL procedure successfully completed.

SQL > select * from dba_hist_wr_control

DBID SNAP_INTERVAL RETENTION TOPNSQL

3812548755 + 00000 02 DEFAULT 00lv 00.0 + 00007 00lv 00lv 00.0

4.2 Baselines (baseline)

Baseline, literally translated as a baseline, is understood in a way that its name implies, which is the basic line for comparison. Because Baseline contains performance data at a specified point in time, it can be used to compare with state data at other points in time to analyze performance problems.

When creating Baseline, Snapshots exists as a part of it, so generally speaking, when AWR automatically maintains snapshots, if baseline is defined, snapshots related to baseline will not be deleted, even expired snapshots, which is equivalent to manually retaining the historical information of statistical data. DBA can compare it with existing snapshots at an appropriate time to generate relevant statistical reports.

Users can manually create or delete Baseline through the relevant procedures in the DBMS_WORKLOAD_REPOSITORY package.

4.2.1 create Baseline

Create a Baseline using the CREATE_BASELINE procedure, which is executed by specifying the snap_id of the start and result, respectively, and then defining a name for the baseline, for example:

SQL > BEGIN

2 DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE (start_snap_id = > 7550)

3 end_snap_id = > 7660

4 baseline_name = > am_baseline)

5 END

6 /

PL/SQL procedure successfully completed.

SQL > select dbid,baseline_name,start_snap_id,end_snap_id from dba_hist_baseline

DBID BASELINE_NAME START_SNAP_ID END_SNAP_ID

--

3812548755 am_baseline 7550 7660

4.2.2 Delete Baseline

Delete a Baseline using the DROP_BASELINE process. When deleting, you can choose whether or not to delete its associated Snapshots level through the cascade parameter, for example:

SQL > BEGIN

2 DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE (baseline_name = >-am_baseline)

3 cascade = > true)

4 END

5 /

PL/SQL procedure successfully completed.

SQL > select * from dba_hist_baseline

No rows selected

SQL > select * from dba_hist_snapshot where snap_id between 7550 and 7660

No rows selected

As shown in the above example, the cascade parameter value is specified as true when deleted, and the corresponding snap is also cascaded.

Whether it is EM or the awr*.sql script used in the previous demonstration, the essence is to access some of the relevant views in ORACLE to generate statistical data, so if DBA is confident in its understanding ability, it can also directly query the dynamic performance view (or related data dictionary) to get the part of performance data it wants. ORACLE saves this part of the performance statistics in the data dictionary at the beginning of DBA_HIST. To query all accessible DBA_HIST dictionaries of the current instance, you can use the following statement:

SQL > select * from dict where table_name like-DBA_HIST%.

TABLE_NAME COMMENTS

-

DBA_HIST_DATABASE_INSTANCE Database Instance Information

DBA_HIST_SNAPSHOT Snapshot Information

DBA_HIST_SNAP_ERROR Snapshot Error Information

DBA_HIST_BASELINE Baseline Metadata Information

DBA_HIST_WR_CONTROL Workload Repository Control Information

DBA_HIST_DATAFILE Names of Datafiles

DBA_HIST_FILESTATXS Datafile Historical Statistics Information

DBA_HIST_TEMPFILE Names of Temporary Datafiles

DBA_HIST_TEMPSTATXS Temporary Datafile Historical Statistics Information

DBA_HIST_COMP_IOSTAT I/O stats aggregated on component level

DBA_HIST_SQLSTAT SQL Historical Statistics Information

DBA_HIST_SQLTEXT SQL Text

..

....

There are many views named after DBA_HIST in the ORACLE database, so here are a few, such as:

V$ACTIVE_SESSION_HISTORY

This view is automatically maintained by ASH and collects information about the active session in the current system at a frequency of once per second. Although it is said to record the history of SESSION, this view is still different from V$SESSION.

SQL > desc v$active_session_history

Name Type Nullable Default Comments

--

SAMPLE_ID NUMBER Y

SAMPLE_TIME TIMESTAMP (3) Y

SESSION_ID NUMBER Y

SESSION_SERIAL# NUMBER Y

USER_ID NUMBER Y

SQL_ID VARCHAR2 (13) Y

SQL_CHILD_NUMBER NUMBER Y

SQL_PLAN_HASH_VALUE NUMBER Y

FORCE_MATCHING_SIGNATURE NUMBER Y

SQL_OPCODE NUMBER Y

PLSQL_ENTRY_OBJECT_ID NUMBER Y

PLSQL_ENTRY_SUBPROGRAM_ID NUMBER Y

PLSQL_OBJECT_ID NUMBER Y

PLSQL_SUBPROGRAM_ID NUMBER Y

SERVICE_HASH NUMBER Y

SESSION_TYPE VARCHAR2 (10) Y

SESSION_STATE VARCHAR2 (7) Y

QC_SESSION_ID NUMBER Y

QC_INSTANCE_ID NUMBER Y

BLOCKING_SESSION NUMBER Y

BLOCKING_SESSION_STATUS VARCHAR2 (11) Y

BLOCKING_SESSION_SERIAL# NUMBER Y

EVENT VARCHAR2 (64) Y

EVENT_ID NUMBER Y

EVENT# NUMBER Y

SEQ# NUMBER Y

P1TEXT VARCHAR2 (64) Y

P1 NUMBER Y

P2TEXT VARCHAR2 (64) Y

P2 NUMBER Y

P3TEXT VARCHAR2 (64) Y

P3 NUMBER Y

WAIT_CLASS VARCHAR2 (64) Y

WAIT_CLASS_ID NUMBER Y

WAIT_TIME NUMBER Y

TIME_WAITED NUMBER Y

XID RAW (8) Y

CURRENT_OBJ# NUMBER Y

CURRENT_FILE# NUMBER Y

CURRENT_BLOCK# NUMBER Y

PROGRAM VARCHAR2 (48) Y

MODULE VARCHAR2 (48) Y

ACTION VARCHAR2 (32) Y

CLIENT_ID VARCHAR2 (64) Y

All the operation-related columns in v$session are collected, and some columns are redundant in order to facilitate DBA to quickly get the data it needs when querying V$ACTIVE_SESSION_HISTORY.

DBA_HIST_ACTIVE_SESS_HISTORY

This view is similar to V$ACTIVE_SESSION_HISTORY 's structure and function, which records the operation records of active session. The difference is that V$ACTIVE_SESSION_HISTORY is automatically maintained in memory by ORACLE, subject to its available memory restrictions, not all records can be saved, while DBA_HIST_ACTIVE_SESS_HISTORY view is maintained to disk. To put it simply, that is to say, in general, the DBA_HIST_ACTIVE_SESS_HISTORY view has more data than V$ACTIVE_SESSION_HISTORY.

Tip: the above structure is not absolute, because the data in the DBA_HIST_ACTIVE_SESS_HISTORY dictionary is collected every 10 seconds by default, compared with once per second in V$ACTIVE_SESSION_HISTORY, so it is also possible to record more data in V$ACTIVE_SESSION_HISTORY. Relatively speaking, however, the data in the DBA_HIST dictionary lasts longer.

DBA_HIST_DATABASE_INSTANCE

This view is used to display database and instance information, such as DBID, instance name, database version, and so on. The first row of tables in the report is generated by this view. As shown in the figure:

If you analyze the awrrpt.sql script, you will find the following script, and the content information shown in the above table comes from the following script:

Select distinct

(case when cd.dbid = wr.dbid and

Cd.name = wr.db_name and

Ci.instance_number = wr.instance_number and

Ci.instance_name = wr.instance_name

Then *

Else

End) | | wr.dbid dbbid

, wr.instance_number instt_num

, wr.db_name dbb_name

, wr.instance_name instt_name

, wr.host_name host

From dba_hist_database_instance wr, v$database cd, v$instance ci

DBA_HIST_SNAPSHOT

This view is used to record snapshot information collected by the current database. I believe friends should remember that when you used a script to generate a report, a pile of lists were displayed after entering the snapshot interval. Yes, that is exactly what DBA_HIST_SNAPSHOT records. The corresponding code for this function is as follows:

Select to_char (s.startup_time,-dd Mon "at" HH24:mi:ss -) instart_fmt

, di.instance_name inst_name

, di.db_name db_name

, s.snap_id snap_id

, to_char (s.end_interval_time,-dd Mon YYYY HH24:mi -) snapdat

, s.snap_level lvl

From dba_hist_snapshot s

, dba_hist_database_instance di

Where s.dbid =: dbid

And di.dbid =: dbid

And s.instance_number =: inst_num

And di.instance_number =: inst_num

And di.dbid = s.dbid

And di.instance_number = s.instance_number

And di.startup_time = s.startup_time

And s.end_interval_time > = decode (& num_days)

, 0, to_date (- 31-JAN-9999,-DD-MON-YYYY)

, 3.14, s.end_interval_time

, to_date (: max_snap_time,-dd/mm/yyyy)-(& num_days-1))

Order by db_name, instance_name, snap_id

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.