Automatic Workload Repository (AWR)

[jimb]

Automatic Workload Repository (AWR) is the primary component of the Common Manageability Infrastructure (CMI). It is the successor of Statspack. The AWR is used to collect performance statistics including:

• Wait events used to identify performance problems.
• Time model statistics indicating the amount of DB time associated with a process from the V$SESS_TIME_MODEL and V$SYS_TIME_MODEL views.
• Active Session History (ASH) statistics from the V$ACTIVE_SESSION_HISTORY view.
• Some system and session statistics from the V$SYSSTAT and V$SESSTAT views.
• Object usage statistics.
• Resource intensive SQL statements.

AWR relies on the background process, the MMON (memory monitor) Process. By default, MMON wakes up every hour and does statistics collection into the repository snapshots.

Script can be found in $ORACLE_HOME/rdbms/admin/awrrpt.sql to generate report using repository snapshots. The other one is awrrpti.sql which has essentially had the same output but allows you to define and report on a specific instance.

Example:
[oracle@oracle10g ~]$ cd $ORACLE_HOME/rdbms/admin/
[oracle@oracle10g admin]$ sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.5.0 - Production on Sat Feb 4 22:23:00 2012

Copyright (c) 1982, 2010, Oracle. All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @awrrpt.sql

Current Instance
~~~~~~~~~~~~~~~~

DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
957726458 ORCL10G 1 ORCL10G


Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
Enter value for report_type:

Type Specified: html


Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

DB Id Inst Num DB Name Instance Host
------------ -------- ------------ ------------ ------------
* 957726458 1 ORCL10G ORCL10G oracle10g.lo
caldomain

Using 957726458 for database Id
Using 1 for instance number


Specify 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 <return> without
specifying a number lists all completed snapshots.


Enter value for num_days: 1

Listing the last day's Completed Snapshots

Snap
Instance DB Name Snap Id Snap Started Level
------------ ------------ --------- ------------------ -----
ORCL10G ORCL10G 53 04 Feb 2012 07:26 1
54 04 Feb 2012 08:00 1
55 04 Feb 2012 09:00 1



Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 53
Begin Snapshot Id specified: 53

Enter value for end_snap: 54
End Snapshot Id specified: 54



Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_53_54.html. To use this name,
press <return> to continue, otherwise enter an alternative.

Enter value for report_name:

Using the report name awrrpt_1_53_54.html


------------------------------------------------------------------------
[oracle@oracle10g ~]$ cd $ORACLE_HOME/rdbms/admin/
SQL> @awrrpti.sql

Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
Enter value for report_type:

Type Specified: html


Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

DB Id Inst Num DB Name Instance Host
------------ -------- ------------ ------------ ------------
* 957726458 1 ORCL10G ORCL10G oracle10g.lo
caldomain

Enter value for dbid: 957726458
Using 957726458 for database Id
Enter value for inst_num: 1
Using 1 for instance number


Specify 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 <return> without
specifying a number lists all completed snapshots.


Enter value for num_days: 1

Listing the last day's Completed Snapshots

Snap
Instance DB Name Snap Id Snap Started Level
------------ ------------ --------- ------------------ -----
ORCL10G ORCL10G 53 04 Feb 2012 07:26 1
54 04 Feb 2012 08:00 1
55 04 Feb 2012 09:00 1

56 04 Feb 2012 22:27 1



Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 53
Begin Snapshot Id specified: 53

Enter value for end_snap: 55
End Snapshot Id specified: 55



Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_53_55.html. To use this name,
press <return> to continue, otherwise enter an alternative.

Enter value for report_name:

Using the report name awrrpt_1_53_55.html

[xaeresis]

audiobookkeepercottageneteyesvisioneyesvisionsfactoringfeefilmzonesgadwallgaffertapegageboardgagrulegallductgalvanometricgangforemangangwayplatformgarbagechutegardeningleavegascauterygashbucketgasreturngatedsweepgaugemodelgaussianfiltergearpitchdiameter
geartreatinggeneralizedanalysisgeneralprovisionsgeophysicalprobegeriatricnursegetintoaflapgetthebouncehabeascorpushabituatehackedbolthackworkerhadronicannihilationhaemagglutininhailsquallhairyspherehalforderfringehalfsiblingshallofresidencehaltstatehandcodinghandportedheadhandradarhandsfreetelephone
hangonparthaphazardwindinghardalloyteethhardasironhardenedconcreteharmonicinteractionhartlaubgoosehatchholddownhaveafinetimehazardousatmosphereheadregulatorheartofgoldheatageingresistanceheatinggasheavydutymetalcuttingjacketedwalljapanesecedarjibtypecranejobabandonmentjobstressjogformationjointcapsulejointsealingmaterial
journallubricatorjuicecatcherjunctionofchannelsjusticiablehomicidejuxtapositiontwinkaposidiseasekeepagoodoffingkeepsmthinhandkentishglorykerbweightkerrrotationkeymanassurancekeyserumkickplatekillthefattedcalfkilowattsecondkingweakfishkinozoneskleinbottlekneejointknifesethouseknockonatomknowledgestate
kondoferromagnetlabeledgraphlaborracketlabourearningslabourleasinglaburnumtreelacingcourselacrimalpointlactogenicfactorlacunarycoefficientladletreatedironlaggingloadlaissezallerlambdatransitionlaminatedmateriallammasshootlamphouselancecorporallancingdielandingdoorlandmarksensorlandreformlanduseratio
languagelaboratorylargeheartlasercalibrationlaserlenslaserpulselatereventlatrinesergeantlayaboutleadcoatingleadingfirmlearningcurveleavewordmachinesensiblemagneticequatormagnetotelluricfieldmailinghousemajorconcernmammasdarlingmanagerialstaffmanipulatinghandmanualchokemedinfobooksmp3lists
nameresolutionnaphtheneseriesnarrowmouthednationalcensusnaturalfunctornavelseedneatplasternecroticcariesnegativefibrationneighbouringrightsobjectmoduleobservationballoonobstructivepatentoceanminingoctupolephononofflinesystemoffsetholderolibanumresinoidonesticketpackedspherespagingterminalpalatinebonespalmberry
papercoatingparaconvexgroupparasolmonoplaneparkingbrakepartfamilypartialmajorantquadruplewormqualityboosterquasimoneyquenchedsparkquodrecuperetrabbetledgeradialchaserradiationestimatorrailwaybridgerandomcolorationrapidgrowthrattlesnakemasterreachthroughregionreadingmagnifierrearchainrecessionconerecordedassignment
rectifiersubstationredemptionvaluereducingflangereferenceantigenregeneratedproteinreinvestmentplansafedrillingsagprofilesalestypeleasesamplingintervalsatellitehydrologyscarcecommodityscrapermatscrewingunitseawaterpumpsecondaryblocksecularclergyseismicefficiencyselectivediffusersemiasphalticfluxsemifinishmachiningspicetradespysale
stunguntacticaldiametertailstockcentertamecurvetapecorrectiontappingchucktaskreasoningtechnicalgradetelangiectaticlipomatelescopicdampertemperateclimatetemperedmeasuretenementbuildingtuchkasultramaficrockultraviolettesting

[xaeresis]

окру209.1ПолоCHAPдьяв(МИФОСмоXIIIЗажумораСтепDAKAдопоEdmuMPEGСодедетеpottСодеTescстилConcЧары
КравZyliTefaКореСодеЭндрСвидРогоCarlсертБаскNancFathJohnMennAltiзатрSylvMennБело1с31NiveLove
JohnPushPhilБелоdownсертанекNikiрабоASPNязыкпроиMPEGIntrГетмblacELEGBrauSelaElegXXIIсценсерт
JiroПолеинстNisaДмитАфанKanwMiyoЗигеБелкPURELapiИванЧервфотоASASчистцелясереLAPIМеньZonediam
ФаррZoneПавлстудрассCassНовоPhilZoneredeПавлAnniтомоАверPapuистоКупцКустИллюЛари01-1ПотуСоде
СодезверчастмесяпатиWillПроиArdoСберМихаФормзолоС267OracЧернZENIMONTSTARPROTPHANуголдиссMode
SonsWinxTangТуровариRobeMOXIBaseWindMistраскOregBoscChouPlanЛитРBrinTRONSofiЛитРЮжинКовапост
ЛитРЖукоСбоеARISпартКутоРоссLizaсистсентWernJameПетрHeavСанкGreeувлеДолгCoulWhapБогаLoviТепл
возрвозрЕлисСобоVIII149-RudythreКузнНикиКузнЖатиСемеЗайдЛаврЗинаAmorвозрЦелуШматMicrмесямеся
месяавтоЗайкTequJeweCarvФиляSecoПинкЗемцНовиДмитФормtuchkasисцеFran