Transcript Document

Using Automatic Workload Repository
Copyright © 2006, Oracle. All rights reserved.
Objectives
After completing this lesson, you should be able to do
the following:
• Create and manage Automatic Workload
Repository (AWR) snapshots
• Generate AWR reports
• Create snapshot sets and compare periods
• Generate ADDM reports
• Generate ASH reports
6-2
Copyright © 2006, Oracle. All rights reserved.
Automatic Workload Repository: Overview
External clients
EM
SQL*Plus …
SGA
Efficient
in-memory
statistics
collection
Internal clients
6-3
V$
DBA_*
MMON
ADDM
AWR
snapshots
Self-tuning … Self-tuning
component
component
Copyright © 2006, Oracle. All rights reserved.
Automatic Workload Repository Data
•
Base statistics:
– SQL and optimizer
statistics
– OS statistics
– Wait classes
– Time statistics
•
•
•
•
•
6-4
Metrics
Active Session History
Advisor results
Snapshot statistics
Database feature usage
V$SYSSTAT
V$SQL
V$SEGMENT_STATISTICS
V$SYS_TIME_MODEL
V$SYSMETRIC_HISTORY
V$SYSTEM_WAIT_CLASS
V$OSSTAT
V$ACTIVE_SESSION_HISTORY
DBA_ADVISOR_*
DBA_HIST_*
DBA_FEATURE_*
DBA_HIGH_WATER_MARK_*
DBA_TAB_STATS_HISTORY
Copyright © 2006, Oracle. All rights reserved.
Workload Repository
MMON
ADDM finds
top problems.
SYSAUX
SGA
In-memory
statistics
6:00 a.m.
7:00 a.m.
8:00 a.m.
9:00 a.m.
9:30 a.m.
6-5
Copyright © 2006, Oracle. All rights reserved.
Snapshot 1
Snapshot 2
Snapshot 3
Snapshot 4
AWR Snapshot Sets
Relevant period
in the past
DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE ( start_snap_id IN NUMBER ,
end_snap_id
IN NUMBER ,
baseline_name IN VARCHAR2);
6-6
Copyright © 2006, Oracle. All rights reserved.
AWR Snapshot Purging Policy
SYSAUX tablespace
60 min
sys schema
Snapshot
7
days
Snapshot
Snapshot
Snapshot
Snapshot
6-7
Copyright © 2006, Oracle. All rights reserved.
MMON
Every night
AWR Snapshot Settings
DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS ( retention IN NUMBER DEFAULT NULL,
interval IN NUMBER DEFAULT NULL,
topnsql
IN NUMBER DEFAULT NULL);
6-8
Copyright © 2006, Oracle. All rights reserved.
Database Control and AWR
6-9
Copyright © 2006, Oracle. All rights reserved.
Generating AWR Reports in EM
6-10
Copyright © 2006, Oracle. All rights reserved.
Generating AWR Reports in SQL*Plus
6-11
Copyright © 2006, Oracle. All rights reserved.
Snapshot Sets and Period Comparisons
6-12
Copyright © 2006, Oracle. All rights reserved.
Compare Periods: Benefits
DBA
≠
≠
DBA
6-13
Copyright © 2006, Oracle. All rights reserved.
Compare Periods: General
6-14
Copyright © 2006, Oracle. All rights reserved.
Compare Periods: Report
6-15
Copyright © 2006, Oracle. All rights reserved.
AWR Data
Base
statistics
SQL
statistics
Metrics
Advisor
results
ASH
AWR
6-16
Copyright © 2006, Oracle. All rights reserved.
DBMS_WORKLOAD_REPOSITORY Package
Procedure Name
6-17
Description
CREATE_SNAPSHOT
Procedure to create a manual snapshot
immediately
DROP_SNAPSHOT_RANGE
Procedure to drop a range of snapshots
CREATE_BASELINE
Procedure to create a single AWR
baseline
DROP_BASELINE
Procedure to drop a single AWR
baseline
MODIFY_SNAPSHOT_SETTINGS
Procedure to modify the snapshot
settings
Copyright © 2006, Oracle. All rights reserved.
ADDM Performance Monitoring
60 minutes
MMON
In-memory
statistics
Snapshots
SGA
ADDM
AWR
ADDM results
6-18
Copyright © 2006, Oracle. All rights reserved.
ADDM and Database Time
User gets
a response.
User sends
a request.
Wide area network
Application server
Local area network
Oracle database
Wall-clock time
Database time
Connect
Execute
Fetch
Fetch
Fetch
Execute
User 1
User 2
User n
6-19
Copyright © 2006, Oracle. All rights reserved.
DBTime-graph and ADDM Methodology
Root node
Symptoms
User
connect
SQL
optimization
SQL
execution
CPU
capacity
I/O
capacity
Database
locks
Root causes
Undersized
buffer cache
Dimension 1
6-20
Dimension 2
Copyright © 2006, Oracle. All rights reserved.
Insufficient I/O
bandwidth
Top Performance Issues Detected
Excessive logon/logoff
Memory undersizing
Not detected
by Statspack
Hot blocks and objects w/SQL
RAC service issues
Locks and ITL contention
Checkpointing causes
PL/SQL, Java time
Streams, AQ, and RMAN
Top SQL
I/O issues
Parsing
Configuration issues
Application usage
6-22
Copyright © 2006, Oracle. All rights reserved.
ADDM
identifies
top issues.
Database Control and ADDM Findings
6-23
Copyright © 2006, Oracle. All rights reserved.
ADDM Analysis Results
1
2
3
6-24
Copyright © 2006, Oracle. All rights reserved.
ADDM Recommendations
6-25
Copyright © 2006, Oracle. All rights reserved.
Database Control and ADDM Task
6-26
Copyright © 2006, Oracle. 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';
6-27
Copyright © 2006, Oracle. 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
l.status = 'COMPLETED');
SQL> @?/rdbms/admin/addmrpt
…
Enter value for begin_snap: 8
Enter value for end_snap: 10
…
Enter value for report_name:
Generating the ADDM report for this analysis ...
6-28
Copyright © 2006, Oracle. All rights reserved.
Active Session History: Overview
•
•
Stores the history of database time
Samples session activity in the system including:
–
–
–
–
–
–
–
–
•
•
6-29
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
No need to replay the workload
Copyright © 2006, Oracle. All rights reserved.
Active Session History: Mechanics
Viewers go
unlatched.
V$ACTIVE_SESSION_HISTORY
Statistics
1sec
ASH
No use
of SQL
V$SESSION
Direct path
inserts
1sec
1sec
Rolling
buffer
Recent history
Every
60 minutes
1 out
of 10
MMON
SGA
MMNL
Workload repository
When 66% full
WRH$_ACTIVE_SESSION_HISTORY (partitioned)
DBA_HIST_ACTIVE_SESSION_HISTORY
6-30
Copyright © 2006, Oracle. All rights reserved.
ASH Sampling: Example
Session1
Wait I/O
Wait Lock
Wait Block
Wait I/O
Wait I/O
Wait Lock
Wait I/O
…
1sec
1sec
V$ACTIVE_SESSION_HISTORY
Session2
active
Sess1 Wait I/O
Sess1 Wait I/O
Sess1 Wait Block
…
…
ASH
Sessionn
active
Wait Block
…
Time
Active
6-31
Session3
active
Inactive
sessions
Copyright © 2006, Oracle. All rights reserved.
Accessing ASH Data
6-32
•
•
•
Dump to trace file
V$ACTIVE_SESSION_HISTORY
DBA_HIST_ACTIVE_SESS_HISTORY
•
•
•
ASH report
EM Diagnostic Pack performance pages
ADDM
Copyright © 2006, Oracle. All rights reserved.
Dump ASH to File
The generated file contains ASH samples for the last
number of minutes specified:
SQL> oradebug setmypid
SQL> oradebug dump ashdump 10
<<<ACTIVE SESSION HISTORY - PROCESS TRACE DUMP HEADER BEGIN>>>
DBID, INSTANCE_NUMBER, SAMPLE_ID, SAMPLE_TIME, SESSION_ID, SESSION_SERIAL#, USER_ID,
SQL_ID, SQL_CHILD_NUMBER, SQL_PLAN_HASH_VALUE, SERVICE_HASH, SESSION_TYPE, SQL_OPCODE,
BLOCKING_SESSION, BLOCKING_SESSION_SERIAL# QC_SESSION_ID, QC_INSTANCE_ID, XID,
CURRENT_OBJ#, CURRENT_FILE#, CURRENT_BLOCK#, EVENT_ID, SEQ#, P1, P2, P3, WAIT_TIME,
TIME_WAITED, FORCE_MATCHING_SIGNATURE, PROGRAM, MODULE, ACTION, CLIENT_ID
<<<ACTIVE SESSION HISTORY - PROCESS TRACE DUMP HEADER END>>>
<<<ACTIVE SESSION HISTORY - PROCESS TRACE DUMP BEGIN>>>
1090770270,1,1317127,"08-26-2005 01:11:40.505471000", 162, 1, 0, "", 0, 0, 165959219, 2,
0, 4294967295, 0,0,0,,5129,1,11242,86156091,1664,0,0,0,0,64620,0,"oracle@edrsr14p1
(CJQ0)","","","",1090770270,1,1317116,"08-26-2005 01:11:29.505471000", 142,20,24,
"0hbv80w9ypy0n",0,3304045827, 3427055676, 1,3,4294967291, 0,0,0,,8751, 3,2486,
1421975091,24975,1413697536,1,0,620,0,17258348159868772889,"emagent@edrsr14p1 (TNS V1V3)","emagent@edrsr14p1 (TNS V1-V3)","",""
…
<<<ACTIVE SESSION HISTORY - PROCESS TRACE DUMP END>>>
6-33
Copyright © 2006, Oracle. All rights reserved.
Sifting the ASHes
•
GROUP BY and COUNT
– Proxy for nonidle elapsed time
– Proportions of actual time spent
•
•
Can analyze any time slice
Example: Returns most active SQL in the past
minute
SELECT
sql_id, count(*),
round(count(*)/sum(count(*)) over (), 2) pctload
FROM
v$active_session_history
WHERE
sample_time > sysdate -1/24/60 and
session_type <> 'BACKGROUND'
GROUP BY sql_id
ORDER BY count(*) desc;
6-34
Copyright © 2006, Oracle. All rights reserved.
Generating ASH Reports
6-35
Copyright © 2006, Oracle. All rights reserved.
ASH Report Script
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
6-36
define dbid
= '';
define inst_num
= '';
define report_type = 'html';
define begin_time = '09:00';
define duration
= 480;
define report_name = '/tmp/sql_ashrpt.txt';
define slot_width = '';
define target_session_id
= '';
define target_sql_id
= 'abcdefghij123';
define target_wait_class
= '';
define target_service_hash = '';
define target_module_name = '';
define target_action_name = '';
define target_client_id
= '';
@?/rdbms/admin/ashrpti
Copyright © 2006, Oracle. All rights reserved.
ASH Report: General Section
V$ACTIVE_SESSION_HISTORY
DBA_HIST_ACTIVE_SESSION_HISTORY
6-37
Copyright © 2006, Oracle. All rights reserved.
ASH Report Structure
6-38
Copyright © 2006, Oracle. All rights reserved.
ASH Report: Activity Over Time
6-39
Copyright © 2006, Oracle. All rights reserved.
Practice Overview:
Use AWR-Based Tools
This practice covers the following topics:
• Generate an AWR report
• Create a preserved set on snapshots
• Generate an ADDM report
• Generate an ASH Report
6-40
Copyright © 2006, Oracle. All rights reserved.
Summary
In this lesson, you should have learned how to:
• Create and manage AWR snapshots
• Generate AWR reports
• Create snapshot sets and compare periods
• Generate ADDM reports
• Generate ASH reports
6-41
Copyright © 2006, Oracle. All rights reserved.