AWR Report – Automatic Workload Repository
It will generated a performance related server reports.
10 g Feature. Prior to 10 g ‘Stats pack report’ has used.
| AWR | Stats Pack |
| Text (or) HTML | Text |
| Any timeframe with ASH | Any timeframe |
| Server level all resources reports |
Parameters: Statistics_level = Basic/Typical/All – Based on the parameter report will be generated.
The information will gathered from following five categories.
| Base Statistics | Matrix Statistics | SQL Statistics | Active Sessions | Advisory Results |
| Physical Reads, CPU | Base+ Memory, Object memory utilization, Every past 10 minutes once | Logical reads from the query | All active session with RAM usage | SQL,SQL Tuning, Memory, Segment & Undo |
Advisory Types
| SQL Advisor | SQL Tuning Advisor | Memory Advisor | Segment Advisor | Undo Advisor |
| Suggestion on Query rewriting | Suggestion on index creation | Suggestion on SGA target | Suggestion for shrink space compact (or) cascade | Undo retention grantee need or not |
By default oracle will generate the snapshot of the DB server performance every 60 minutes. It will run by the background process MMON – Memory manager.
Snapshot will be stored in SYSAUX tablespace. It has a default 7 days retention. We can change the interval and retention.
How to get the generated snapshot
Sql> select snap_id,startup_time from dba_hist_snapshot;
How to generate the snapshot manually.
Sql> Exec dbms_workload_repository.create_snapshot;
Change the default interval and retention:
Sql> Exec dbms_workload_repository.snapshot_settings (interval = > 15, retention => 2880; – Retention in minutes.
Generate the AWR report
Sql> @?/rdbms/admin/awrrpt.sql;
It will ask following parameters.
Report type: Html/text: HTML
Enter no of days: 2 – Display only last 2 days of the snap id
Snap id of first: starting id
Snap id of second: End id
Report Name: /opt/awrtest.html
It mostly 56 to 60 page report.
Generate the ADDM report
ADDM will get the data from AWR and will gather information from all the advisory and generate the report with problem and recommendations.
Sql> @?/rdbms/admin/addmrpt.sql;
Snap id of first: starting id
Snap id of second: End id
Report Name: /opt/addmtest.txt
EX: $ vi /opt/addmtest.txt
Problem 1: sql id (96%)
Recommendation: SQL tuning
Problem 1: SGA_traget – 1500m (36%)
Recommendation: SGA_traget – 1900 M
Generate the ASH report
ASH – active session history. It will get the data from AWR. This mostly run in the known timeframe, which occurs frequently.
Sql> @?/rdbms/admin/ashrpt.sql;
Snap time of first: 03:00:00
Snap time of second: 04:00:00
Report Name: /opt/ashtest.html
Note: AWR report is the base report for ADDM and ASH.
How to analyze the reports
There are some important parameters, which will give an overall idea of the issues.
AWR
- Elapsed time vs DB time
If the DB time is less than the elapsed time, the DB does not have a load or issues.
Elapsed < DB Time – DB has load and it has some problem
- Load Profile
Check the load profile redo size etc.
- Instance Efficiency Percentages (Target 100%) – RAM issue
Check the memory parameters the hit ratio should be 90% above and parse should be less than 80%.
- Top 5 Timed Foreground Events
Sequential read – Index problem, may be need an index or rebuild
Scattered read – Full table scan high, Need to create an index.
Direct path read – I/O high.
Once, we get the top 5, then we can take each of them and need to analyze further level.
Like, PGA – Check the sorting and Buffer pool size – Check it from size factor 1.00
- Main report
Check the “SQL Statistics” – Check SQL ordered by Elapsed Time & SQL ordered by CPU Time
- Advisory Statistics
Check the advisory of SGA and PGA current and recommended size. 1.0 is current and over some period constant value is recommended.
ADDM
The ADDM will give the issue and recommendations. Just, compare with your AWR finding with ADDM.
11g onwards, Locking problem, ADDM only reports, AWR will not shows that.
ASH
Ash will has an exact issue happen on the time. Check the “Top SQL with Top Events”.
