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

Optimize it through case study-- the basic concept of AWR

I. detailed explanation of the concept of Automatic Workload Repository

Automatic Workload Repository (AWR) collects, processes, and maintains performance statistics for problem diagnosis. This data exists both in the data block and in memory. The data collected by AWR can be viewed through reports and views.

Statistics processed and collected by AWR include:

1. Object statistics that determine block segment access paths and usage

two。 Time model statistics based on time usage of database activity, which can be viewed in V$SYS_TIME_MODEL and V$SESS_TIME_MODEL views

Some statistics for sytem and session collected in the 3.V$SYSSTAT and V$SESSTAT views

4. The SQL statements that generate high load are filtered on the system according to the conditions of Elapsed time and CPU time.

5.ASH Statistics-History of recent session activity

By default, the database has enabled AWR to collect statistics, which is controlled by STATISTICS_LEVEL initialization parameters. The STATISTICS_LEVEL parameter must be set to TYPICAL or ALL to enable AWR statistics collection. The default setting is TYPICAL. Setting STATISTICS_LEVEL to BASIC disables many Oracle Database features, including AWR, so this setting is not recommended. When STATISTICS_LEVEL is set to BASIC, you can still use the DBMS_WORKLOAD_REPOSITORY package to manually capture AWR statistics. However, many of the system statistics collected in memory, such as segment statistics and memory advisor information, will be disabled, in which case the statistics captured by manual snapshots may not be complete.

1 、 Snapshot

A snapshot is a collection of historical data for a specific period of time that ADDM uses for performance comparison. In 11g, Oracle Database automatically generates a snapshot of performance data every hour and keeps these statistics in the workload repository for 8 days. You can also create snapshots manually, but there is no need to do so. Statistics during the snapshot interval are analyzed by Automatic Database Diagnostic Monitor (ADDM).

AWR determines which SQL statements to capture according to the impact on the system load by comparing the differences between snapshots. Over time, the number of SQL statements that must be captured will gradually decrease.

2 、 Baseline

Baseline refers to performance data for a specific period of time, which is retained for comparison with other similar workload periods when performance problems arise. Snapshots included in Baseline are excluded from the automatic AWR cleanup process and are retained indefinitely.

There are many types of baseline in Oracle Database

Fixed Baseline:fixed baseline represents a fixed, continuous period of time that you specify. Before creating a fixed baseline, carefully consider the time period you selected as the baseline, because the baseline should represent that the system is running at good performance. You can compare this baseline with other baseline or snapshot captured during periods of poor performance in the future.

Moving Window Baseline: represents all AWR data that exists during the AWR retention period. It is useful when using adaptive thresholds because the database can use AWR data for the entire AWR retention period to calculate indicator values.

Oracle Database automatically maintains the system-defined moving window baseline. The default window size of the system-defined moving window baseline is the current AWR retention period, which defaults to 8 days. If you plan to use adaptive thresholds, consider using a longer movement window, such as 30 days, to accurately calculate the threshold. You can resize the moving window baseline to resize the moving window to be less than or equal to the number of days that the AWR is retained. Therefore, to increase the size of the mobile window, you must first increase the corresponding AWR retention period.

Baseline Template: you can use baseline template to create a baseline for a continuous period of time in the future. There are two baseline templates in Oracle: single and repeating

With single baseline template, you can create a baseline for a separate continuous period of time in the future. This technique is very useful in some cases. For example, if you want to capture AWR data during a system test scheduled for next week, you can create a single baseline template that automatically captures statistics for the period during which the test took place.

With repeating baseline template, you can create and delete baseline based on a repetitive schedule. This is useful when you want Oracle Database to capture statistics for consecutive periods automatically and continuously. For example, you might need to capture AWR data every Monday morning for up to a month. In this case, you can create a repeating baseline template to automatically create a baseline every Monday and automatically delete obsolete baseline within the specified expiration period.

Case study:

AWR can be applied in OEM graphical interface or sqlplus interface.

1. Enable it under OEM

Start listener: [Oracle @ RH6 admin] $lsnrctl startLSNRCTL for Linux: Version 11.2.0.1.0-Production on 31-OCT-2014 17:14:31Copyright (c) 1991, 2009, Oracle. All rights reserved.Starting / u01/app/oracle/product/11.2.0/db_1/bin/tnslsnr: please wait...TNSLSNR for Linux: Version 11.2.0.1.0-ProductionSystem parameter file is / u01/app/oracle/product/11.2.0/db_1/network/admin/listener.oraLog messages written to / u01/app/oracle/diag/tnslsnr/RH6/listener/alert/log.xmlListening on: (DESCRIPTION= (ADDRESS= (PROTOCOL=tcp) (HOST=RH6) (PORT=1521)) Connecting To (DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (HOST=RH6) (PORT=1521)) STATUS of the LISTENER----Alias LISTENERVersion TNSLSNR for Linux: Version 11.2.0.1.0-ProductionStart Date 31-OCT-2014 17:14:31Uptime 0 days 0 hr. 0 min. 0 secTrace Level offSecurity ON: Local OS AuthenticationSNMP OFFListener Parameter File / u01/app/oracle/product/11.2.0/db_1/network/admin/listener.oraListener Log File / u01/app/oracle/diag/tnslsnr/RH6/listener/alert/log.xmlListening Endpoints Summary... (DESCRIPTION= (ADDRESS= (PROTOCOL=tcp) (HOST=RH6) (PORT=1521) The listener supports no servicesThe command completed successfully starts OEM Service: [oracle@RH6 admin] $emctl start dbconsoleOracle Enterprise Manager 11g Database Control Release 11.2.0.1.0Copyright (c) 1996, 2009 Oracle Corporation. All rights reserved. https://RH6:1158/em/console/aboutApplicationStarting Oracle Enterprise Manager 11g Database Control. Started.-Logs are generated in directory / u01/app/oracle/product/11.2.0/db_1/RH6_prod/sysman/ log [oracle @ RH6 admin] $netstat-an | grep 1158tcp 0 0:: : 1158:: * LISTEN [oracle@RH6 admin] $emctl status dbconsoleOracle Enterprise Manager 11g Database Control Release 11.2.0.1.0Copyright (c) 1996 2009 Oracle Corporation. All rights reserved. https://RH6:1158/em/console/aboutApplicationOracle Enterprise Manager 11g is running.-Logs are generated in directory / u01/app/oracle/product/11.2.0/db_1/RH6_prod/sysman/log

Manage Database through OEM

View snapshot on the performance page

Establish AWR Report

View awr report

2. Create awr report through sqlplus

1) check snapshot02:33:25 SYS@ prod > desc dba_hist_snapshot; Name Null? Type SNAP_ID NOT NULL NUMBER DBID NOT NULL NUMBER INSTANCE_NUMBER NOT NULL NUMBER STARTUP_TIME NOT NULL TIMESTAMP (3) BEGIN_INTERVAL_TIME NOT NULL TIMESTAMP (3) END_INTERVAL_TIME NOT NULL TIMESTAMP (3) FLUSH_ELAPSED INTERVAL DAY ( 5) TO SECOND (1) SNAP_LEVEL NUMBER ERROR_COUNT NUMBER SNAP_FLAG NUMBER 02:22:53 SYS @ prod > col BEGIN_INTERVAL_TIME for a4002 SYS@ prod 23 SYS@ prod > col END_INTERVAL_TIME for a4002 13 SYS@ prod > select SNAP_ID Dbid,SNAP_LEVEL,BEGIN_INTERVAL_TIME END_INTERVAL_TIME from dba_hist_snapshot SNAP_ID DBID SNAP_LEVEL BEGIN_INTERVAL_TIME END_INTERVAL_TIME-- -- 11441.01.518 04-NOV-14 01.29.48.000 AM 04-NOV-14 01.41.01.518 AM 219724276 1 04-NOV-14 01.41.01.518 AM 04-NOV-14 02.01.49. 722 AM 116 219724276 1 04-NOV-14 02.01.49.722 AM 04-NOV-14 02.02.31.757 AM 2), Generate awr report02:23:13 SYS@ prod > @? / rdbms/admin/awrrptCurrent Instance~ DB Id DB Name Inst Num Instance- 219724276 PROD 1 prodElapsed: 00:00:00.05Elapsed: 00:00:00.00Specify the Report Type~~~Would you like an HTML report Or a plain text report?Enter 'html' for an HTML report Or 'text' for plain textDefaults to' html'Enter value for report_type: htmlType Specified: htmlElapsed: 00:00:00.00Instances in this Workload Repository schema~~~~ DB Id Inst Num DB Name Instance Host- 219724276 1 PROD Prod RH6.51CTO reminds you Do not spam! 219724276 1 PROD prod rh7.cuug.net* 219724276 1 PROD prod RH6Using 219724276 for database IdUsing 1 for instance numberSpecify the number of days of snapshots to choose from~~~Entering the number of days (n) will result in the most recent (n) days of snapshots being listed. Pressing withoutspecifying a number lists all completed snapshots.Enter value for num_days: 1Listing the last day's Completed Snapshots SnapInstance DB Name Snap Id Snap Started Level-- -prod PROD 11404 Nov 2014 01:41 1115 04 Nov 2014 02:01 1116 04 Nov 2014 02:02 13) View report

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.