A performance report is useful when its database scope and time window match the problem being investigated. Begin with the business symptom: which operation was slow, which service and pluggable database carried it, when it ran, and what response time or throughput should have been achieved.
Oracle DBA Lesson 95 Part 1 — Choose an AWR interval that answers the question
Automatic Workload Repository (AWR) reports summarize performance statistics between selected snapshots. They help identify where foreground database time accumulated and which statements contributed to the selected workload. A report covering an entire container database can include many applications. Connect its findings to the affected batch before choosing a change.
AWR reports and associated licensed features require Diagnostics Pack rights for the applicable offering. CONTROL_MANAGEMENT_PACK_ACCESS controls technical access. Its value does not establish contractual entitlement. The supplied AWR report scripts require the DBA role. Several DBA_HIST_ views, including DBA_HIST_SNAPSHOT, are permitted without Diagnostics Pack. That exception does not extend to awrrpt.sql, the repository packages generally, ASH, or all historical views. ASH is not a pack-free substitute. The same interval can be measured from timestamped dynamic views across one startup.
Choose identity and scope before totals
Record these fields together before reading any totals:
| Field | Purpose |
|---|---|
| DBID | Identifies the database whose snapshots are used |
| Instance number | Selects the instance within that database |
| Startup time | Establishes the instance lifetime for counter comparison |
| Container/report scope | Establishes whose workload is summarized |
| Service and affected PDB | Connects report contributors to the complaint |
| Capture times and time zone | Establishes the report's actual boundaries |
Snapshot numbers alone are insufficient identifiers across databases and scopes. Verify the report header and the snapshot records together. Keep the instance running across the selected interval. A restart changes the lifetime of cumulative counters; use a separate interval within one startup. Review collection errors and warnings before relying on a report.
CDB-level and PDB-level snapshots have separate identities and collection behavior. A CDB-level report includes CDB activity and contributions from PDBs; it does not automatically isolate one service. A PDB-level snapshot includes that PDB's statistics and some global context. Oracle 19c enables automatic CDB snapshots by default and disables automatic PDB snapshots by default. Verify the actual configuration and available data rather than assuming either default applies. Do not mix root and PDB snapshot identifiers.
Inspect available capture boundaries
This inventory lists recent snapshots in the connected context:
SELECT snap_id, dbid, instance_number, startup_time,
begin_interval_time_tz, end_interval_time_tz,
error_count, con_id
FROM dba_hist_snapshot
ORDER BY snap_id DESC
FETCH FIRST 10 ROWS ONLY;END_INTERVAL_TIME_TZ records the actual snapshot time with its time-zone information. The report runs between the selected beginning snapshot's capture and the selected ending snapshot's capture. A snapshot row's BEGIN_INTERVAL_TIME_TZ describes that snapshot's own collection interval. It is not automatically the beginning of whichever multi-snapshot report you later request.
ERROR_COUNT records snapshot collection errors. A nonzero count calls for investigation, including DBA_HIST_SNAP_ERROR where appropriate. Zero errors is one check; also inspect availability, continuity, report warnings and workload coverage. The recent-ten inventory can miss older snapshots or interleave identities. Narrow the search to the recorded DBID, instance and date range.
See DBA_HIST_SNAPSHOT.
Bracket the slow workload
Suppose a batch was slow from 10:05 to 10:25. The available captures are:
| Snapshot | Capture time | Same selected DBID, instance and startup |
|---|---|---|
| 500 | 10:00 | Required |
| 501 | 10:30 | Required |
| 502 | 11:00 | Required |
Choose 500 → 501, covering 30 elapsed minutes. It includes the complete 20-minute batch plus five minutes before and five minutes after. Retain those boundaries when interpreting the totals. The report summarizes everything captured within its scope and interval, including surrounding work.
Using 500 → 502 would add another 30 minutes of unrelated activity. Use that wider window only if the investigation needs it. Existing coarse snapshots cannot reconstruct an exact 10:05–10:25 AWR interval. State the limitation and correlate application timestamps and workload identifiers.
Generate and verify the report
Run the script from the installed Oracle home:
@?/rdbms/admin/awrrpt.sqlSQL*Plus expands ? to its Oracle home directory. The script prompts for HTML or text output, the number of days of snapshots to list, beginning and ending snapshot identifiers, and a report filename. Select beginning snapshot 500 and ending snapshot 501 only after verifying their identity and times.
awrrpt.sql reports on the local database instance. Oracle supplies awrrpti.sql when an explicit database and instance selection is required. Confirm the resulting header's DBID, instance, startup, container scope, snapshot IDs and actual elapsed time before reading its rankings. If data is absent, the scope is wrong, or the interval crosses a restart, resolve the issue before drawing conclusions.
See gathering database statistics.
Read DB time and the workload
DB time is foreground database-call time accumulated across sessions. It includes foreground CPU time and non-idle waits. Idle waits and time spent by instance background processes are outside this measure. Many time-model categories overlap: parsing and SQL execution are components of DB time, so adding every displayed category double-counts work.
For the 30-minute window:
| Quantity | Value |
|---|---|
| Report elapsed time | 30 min |
| DB time | 60 min |
| DB time ÷ elapsed time | 2 |
The ratio means two active foreground sessions on average over the selected interval. Concurrent sessions accumulate time simultaneously, so DB time can exceed wall time. The average does not mean precisely two sessions were active at every instant, and it is not a measure of CPU utilization. CPU work and non-idle waiting both contribute.
Read the load profile and dominant foreground non-idle waits alongside the top SQL. Keep units, totals and per-second or per-transaction rates distinct. Investigate an apparent I/O contribution by checking the relevant statements, access paths, workload volume and storage measurements. A large total may reflect much work, long individual waits or both; use the event's meaning and counts to interpret it. Match SQL and service evidence to the slow batch and compare its business response or throughput. Form one explanation that can be tested before making a change.
See measuring database performance.
Measure the interval from dynamic views
Without Diagnostics Pack rights, collect these dynamic-view measurements twice around the same bounded workload:
SHOW CON_NAME
SELECT systimestamp AS captured_at FROM dual;
SELECT dbid FROM v$database;
SELECT instance_number, startup_time FROM v$instance;
SELECT con_id, stat_name, value
FROM v$sys_time_model
WHERE stat_name IN ('DB time', 'DB CPU');
SELECT con_id, event, wait_class,
total_waits_fg, time_waited_micro_fg
FROM v$system_event
WHERE wait_class <> 'Idle';Preserve both timestamped spools and their identity. These separate queries are not an atomic snapshot; record collection timing and keep the workload interval long enough for meaningful interpretation. V$SYS_TIME_MODEL.VALUE and TIME_WAITED_MICRO_FG use microseconds. The foreground suffix avoids mixing background waits into a foreground comparison. In CDB root these views return instance-wide data with CON_ID = 0; check visibility and meaning when using a PDB. The views do not themselves isolate one service's workload.
Compute differences only across the same instance startup and compatible scope. For example, a DB-time difference of 3,600,000,000 µs is 3,600 seconds = 60 minutes. Dividing it by 1,800 elapsed seconds gives 2 average active foreground sessions. Compare event differences using matching event identity and scope. Reject negative/reset counters or a restart and collect a fresh interval. Time-model publication can lag an ongoing operation by up to five seconds, and separate captures can have boundary effects. Do not sum nested time-model statistics or assume event totals exactly reconcile with independently collected values.
See V$SYS_TIME_MODEL and V$SYSTEM_EVENT.
Recap
Choose snapshots whose DBID, instance, startup, container scope, and capture times contain the slow work, and account for the extra minutes around that batch. Read DB time divided by elapsed time as average active foreground sessions, then match SQL and service evidence to the batch. When the pair crosses a restart, or Diagnostics Pack rights are absent, compare one startup with the dynamic-view differences.
No comments:
Post a Comment