Using AWR-Based Tools

Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

 Objectives

After completing this lesson, you should be able to do the

following:
• Tune automatic maintenance tasks
• Generate ADDM reports
• Generate Compare Period ADDM reports
• Generate Active Session History (ASH) reports

7-2 Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
 Automated Maintenance Task

Maintenance
window Run Job1 Run Job2 Run Job3 Run Job3 Run Job4
… with with with with with …
very high very high high medium low
priority. priority. priority. priority. priority.

DBA_AUTOTASK_TASK

MMON

SQL
ABP

Space

Job1 … Jobn
Stats

Scheduler Job Classes

7-3 Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
 Configuring Automated Maintenance Tasks

7-4 Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
 ADDM Performance Monitoring

60 minutes
MMON

In-memory
statistics
Snapshots
SGA

ADDM

AWR

ADDM results

7-5 Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
 DB Time-Graph and ADDM Methodology

Root node

Symptoms

User SQL SQL CPU I/O Database

connect optimization execution capacity capacity locks

Root causes

Undersized Insufficient I/O

buffer cache bandwidth
Dimension 1 Dimension 2

7-6 Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
 Top Performance Issues Detected

Excessive logon/logoff
Memory undersizing
Hot blocks and objects w/SQL
Not detected
by Statspack RAC service issues
Locks and ITL contention
Checkpointing causes
PL/SQL, Java time
Streams, AQ, and RMAN
Top SQL
I/O issues
ADDM
Parsing identifies
Configuration issues top issues.
Application usage

7-7 Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
 Changing ADDM Attributes

1. Ensure that STATISTICS_LEVEL is set to TYPICAL or

ALL.
2. ADDM analysis of I/O performance depends on the
expected speed of the I/O subsystem:
a. Measure your I/O subsystem speed.
b. Set the expected speed.

SQL> exec DBMS_ADVISOR.SET_DEFAULT_TASK_PARAMETER(-

'ADDM', 'DBIO_EXPECTED', 8000);

SELECT parameter_value, is_default

FROM dba_advisor_def_parameters
WHERE advisor_name = 'ADDM' AND
parameter_name = 'DBIO_EXPECTED';

7-8 Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
 Retrieving ADDM Reports by Using SQL
SELECT dbms_advisor.GET_TASK_REPORT(task_name)
FROM dba_advisor_tasks
WHERE task_id = (
SELECT max(t.task_id)
FROM dba_advisor_tasks t,
dba_advisor_log l
WHERE t.task_id = l.task_id AND
t.advisor_name = 'ADDM' AND
[Link] = 'COMPLETED');

SQL> @?/rdbms/admin/addmrpt
Instance DB Name Snap Id Snap Started Level
----------- ------------ --------- ------------------ -----
orcl ORCL 434 06 Jun 2019 00:00 1
…
Enter value for begin_snap: 434
Enter value for end_snap: 436
…
Enter value for report_name:
Generating the ADDM report for this analysis ...

7-9 Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
 12c

Compare Periods ADDM: Analysis

Causes Effects Map effects to causes

Identify performance with a rule set
Identify system changes
difference

• Configuration changes • Computes problem • Rules triggered by

– DB version impacts with ADDM performance changes
• Workload changes methodology ▬ SGA_TARGET
– Change in SQLs – In base period decrease can
– Database time – In compare period cause I/O
consumed by • Measures the difference increase
these SQLs

7 - 10 Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

 Using the DBMS_ADDM Package

Compare two periods within the same instance:

Comparison “current” Period Base “earlier” Period

to
Snap_ID 123 to 124 Snap_ID 121 to 122

SQL> SELECT dbms_addm.compare_instances (

2 base_dbid => 1319927350,
3 base_instance_id => 1,
4 base_begin_snap_id => 121,
5 base_end_snap_id => 122,
6 comp_dbid => 1319927350,
7 comp_instance_id => 1,
8 comp_begin_snap_id => 123,
9 comp_end_snap_id => 124,
10 report_type => 'XML')
11 FROM dual;

7 - 11 Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

 Using the DBMS_ADDM Package

Functions to compare periods:

• COMPARE_INSTANCES
• COMPARE_DATABASES
• COMPARE_CAPTURE_REPLAY_REPORT
• COMPARE_REPLAY_REPLAY_REPORT

7 - 12 Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

 Active Session History: Overview

• Stores the history of database time

• Samples session activity in the system including:
– SQL identifier of a SQL statement
– Object number, file number, and block number
– Wait event identifier and parameters
– Session identifier and session serial number
– Module and action name
– Client identifier of the session
– Service hash identifier
– Blocking session
• Is always on for first fault analysis
• Replaying the workload is not needed

7 - 13 Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

 Active Session History: Mechanics

Viewers go
unlatched.
V$ACTIVE_SESSION_HISTORY

Statistics
1sec

ASH Direct path 1sec

Rolling
inserts 1sec
No use buffer
of SQL
V$SESSION Every
Recent history
60 minutes
1 out
MMON of 10
SGA

MMNL
Workload repository
When 66% full

WRH$_ACTIVE_SESSION_HISTORY (partitioned)
DBA_HIST_ACTIVE_SESS_HISTORY

7 - 14 Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

 Accessing ASH Data

• V$ACTIVE_SESSION_HISTORY
• DBA_HIST_ACTIVE_SESS_HISTORY
• ASH report
• Enterprise Manager Diagnostic Pack performance pages

7 - 15 Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

 ASH Report: General Section

V$ACTIVE_SESSION_HISTORY
DBA_HIST_ACTIVE_SESS_HISTORY

7 - 16 Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

 ASH Report Structure

7 - 17 Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

 ASH Viewer

In [Link]
SQLNET.ALLOWED_LOGON_VERSION=8

7 - 18 Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

 Quiz

In the Active Session History report, you can see only the last
hour of data, because only one hour of data is held in memory.
a. True
b. False

7 - 19 Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

 Quiz

The DB time%, also labeled Impact% or Benefit% in the ADDM

report, is a percentage of:
a. Elapsed time related to response time
b. Total CPU time used by the item reported
c. Total time in databases calls by all process
d. Total elapsed time used by database calls

7 - 20 Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

 Summary

In this lesson, you should have learned how to:

• Tune Automatic Maintenance Tasks
• Generate ADDM reports
• Generate ASH reports

7 - 21 Copyright © 2019, Oracle and/or its affiliates. All rights reserved.