Manually generate AWR Report in oracle database
Posted by Mir Sayeed Hassan on October 3rd, 2017
Manually generating the AWR Report in oracle database
SQL> @$ORACLE_HOME/rdbms/admin/awrrpt.sql; Current Instance ~~~~~~~~~~~~~~~~ DB Id DB Name Inst Num Instance ----------- ------------ -------- ------------ 4187434325 TESTDB 1 testdb 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: text (Input given by user) Type Specified: text Instances in this Workload Repository schema ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ DB Id Inst Num DB Name Instance Host ------------ -------- ------------ ------------ ------------ * 4187434325 1 TESTDB testdb ogg-test1 Using 4187434325 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 without specifying a number lists all completed snapshots. Enter value for num_days: 1 (Input given by user) Listing the last day's Completed Snapshots Snap Instance DB Name Snap Id Snap Started Level ---------- ------------ --------- ------------------ ----- testdb TESTDB 15829 03 Oct 2017 00:30 1 15830 03 Oct 2017 01:30 1 15831 03 Oct 2017 02:30 1 15832 03 Oct 2017 03:30 1 15833 03 Oct 2017 04:30 1 15834 03 Oct 2017 05:30 1 15835 03 Oct 2017 06:30 1 15836 03 Oct 2017 07:30 1 15837 03 Oct 2017 08:30 1 15838 03 Oct 2017 09:30 1 15839 03 Oct 2017 10:30 1 15840 03 Oct 2017 11:30 1 15841 03 Oct 2017 12:30 1 15842 03 Oct 2017 13:30 1 Specify the Begin and End Snapshot Ids ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Enter value for begin_snap: 15829 Begin Snapshot Id specified: 15829 Enter value for end_snap: 15832 End Snapshot Id specified: 15832 Specify the Report Name ~~~~~~~~~~~~~~~~~~~~~~~ The default report file name is awrrpt_1_15829_15832.txt. To use this name, press to continue, otherwise enter an alternative. Enter value for report_name: awrrpt_manual_1day_few_mins.txt You can view the complete report in text format as shown below ------- Few lines given below for sample reference ------------- -> Ordered by statistic name Statistic Total per Second per Trans -------------------------------- ------------------ -------------- ------------- db block changes 44,881 4.2 32.6 execute count 36,775 3.4 26.8 logons cumulative 698 0.1 0.5 opened cursors cumulative 33,406 3.1 24.3 parse count (total) 25,992 2.4 18.9 parse time elapsed 173 0.0 0.1 physical reads 475 0.0 0.4 physical writes 4,260 0.4 3.1 redo size 10,951,236 1,013.7 7,964.5 session cursor cache hits 22,553 2.1 16.4 session logical reads 199,682 18.5 145.2 user calls 2,095 0.2 1.5 user commits 1,105 0.1 0.8 user rollbacks 270 0.0 0.2 workarea executions - optimal 10,068 0.9 7.3 ------------------------------------------------------ Other Instance Activity Stats DB/Inst: TESTDB/testdb Snaps: 15829-15832 -> Ordered by statistic name -> Statistics with absolute values (should not be diffed) Statistic Begin Value End Value -------------------------------- --------------- --------------- logons current 54 52 opened cursors current 34 31 session cursor cache count 5,659,956 5,675,676 session pga memory 3.002525531E+11 3.010959829E+11 session pga memory max 3.836769490E+11 3.848633942E+11 session uga memory 1.652997581E+11 1.657349425E+11 session uga memory max 2.284121177E+11 2.290071116E+11 ------------------------------------------------------ Instance Activity Stats - Thread ActivityDB/Inst: TESTDB/testdb Snaps: 15829- -> Statistics identified by '(derived)' come from sources other than SYSSTAT Statistic Total per Hour -------------------------------- ------------------ --------- log switches (derived) 0 .00 ------------------------------------------------------ IOStat by Function summary DB/Inst: TESTDB/testdb Snaps: 15829-15832 -> 'Data' columns suffixed with M,G,T,P are in multiples of 1024 other columns suffixed with K,M,G,T,P are in multiples of 1000 -> ordered by (Data Read + Write) desc Reads: Reqs Data Writes: Reqs Data Waits: Avg Function Name Data per sec per sec Data per sec per sec Count Tm(ms) --------------- ------- ------- ------- ------- ------- ------- ------- ------- Others 286M 1.7 .026M 120M 0.7 .011M 26K 0.0 DBWR 0M 0.0 0M 33M 0.3 .003M 3046 0.0 LGWR 0M 0.0 0M 12M 0.3 .001M 6642 0.2 Buffer Cache Re 3M 0.0 0M 0M 0.0 0M 455 2.1 TOTAL: 289M 1.7 .027M 165M 1.3 .015M 36.2K 0.1 ------------------------------------------------------ IOStat by Filetype summary DB/Inst: TESTDB/testdb Snaps: 15829-15832