proactively maintaining the database
DESCRIPTION
Proactively Maintaining the Database. Objectives. After completing this lesson, you should be able to do the following: Describe the Automatic Workload Repository (AWR) Define AWR snapshot baselines Subscribe applications to server-generated alerts Describe the advisor framework - PowerPoint PPT PresentationTRANSCRIPT
8Copyright © 2004, Oracle. All rights reserved.
Proactively Maintaining the Database
8-2 Copyright © 2004, Oracle. All rights reserved.
Objectives
After completing this lesson, you should be able to do the following:
• Describe the Automatic Workload Repository (AWR)
• Define AWR snapshot baselines
• Subscribe applications to server-generated alerts
• Describe the advisor framework
• Use the Automatic Database Diagnostic Monitor (ADDM)
8-3 Copyright © 2004, Oracle. All rights reserved.
Oracle Database 10g: Self-Managing Database
Applicationand SQL
management
Storagemanagement
Backup andrecovery
management
Systemresource
management
Spacemanagement
Fix Advise
AlertMonitor
Commonmanageabilityinfrastructure
Automaticmanagement
8-4 Copyright © 2004, Oracle. All rights reserved.
Automatic Workload Repository
AutomaticWorkload
Repository
Serveralerts
Automatedtasks
Advisorframework
Data warehouseof the database
Automatic collection of important statistics
Direct memoryaccess
Automatic Proactive
Efficient
8-5 Copyright © 2004, Oracle. All rights reserved.
Automatic Workload Repository: Overview
SGA
V$ DBA_*
ADDM Self-tuningcomponent
Self-tuningcomponent
…Internal clients
External clientsEM SQL*Plus …
Efficientin-memorystatisticscollection
AWRsnapshotsMMON
8-6 Copyright © 2004, Oracle. All rights reserved.
Automatic Workload Repository Data
• New base statistics:– SQL and optimizer
statistics– OS statistics– Wait classes– Time statistics
• Metrics
• Active Session History
• Advisor results
• Snapshot statistics
• Database feature usage
V$SYSSTATV$SQL
V$SEGMENT_STATISTICSV$SYS_TIME_MODEL
V$SYSMETRIC_HISTORYV$SYSTEM_WAIT_CLASS
V$OSSTATV$ACTIVE_SESSION_HISTORY
DBA_ADVISOR_*DBA_HIST_*
DBA_FEATURE_*DBA_HIGH_WATER_MARK_*
DBA_TAB_STATS_HISTORY
8-7 Copyright © 2004, Oracle. All rights reserved.
Active Session History
SGA
Statistics
V$SESSION
ASH
Recent history
Rolling buffer
AWR snapshots
MMON
MMNL
V$ACTIVE_SESSION_HISTORY
8-8 Copyright © 2004, Oracle. All rights reserved.
Base Statistics and Metrics
Base Statistic 1
Redo Generation
Metric 1 Metric 2 Redo Generation/Tx
Client 1 Client 2 Client 3 Client 4
MMON
User Commit User Rollback
V$SYSMETRIC, V$SESSMETRIC, V$SERVICEMETRIC, V$METRICNAME
V$FILEMETRIC, V$EVENTMETRIC, V$WAITCLASSMETRIC
Everyminute
8-9 Copyright © 2004, Oracle. All rights reserved.
Workload Repository
SYSAUX
SGA
In-memorystatistics
6:00 a.m.7:00 a.m.
8:00 a.m.
Snapshot 1Snapshot 2Snapshot 3
Snapshot 49:00 a.m.
9:30 a.m.
ADDM finds top problemsMMON
8-10 Copyright © 2004, Oracle. All rights reserved.
Statistic Levels
STATISTICS_LEVEL
BASIC TYPICAL ALL
Recommendeddefault value
Additional statisticsfor manual
SQL diagnostics
Disable all self-tuningcapabilities
8-11 Copyright © 2004, Oracle. All rights reserved.
AWR Snapshot Baselines
DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE ( - start_snap_id IN NUMBER , end_snap_id IN NUMBER , baseline_name IN VARCHAR2);
Relevant periodin the past
8-12 Copyright © 2004, Oracle. All rights reserved.
AWR Snapshot Purging Policy
DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS ( - retention IN NUMBER DEFAULT NULL, interval IN NUMBER DEFAULT NULL);
SYSAUX tablespace
WR schema
Snapshot
Snapshot
Snapshot
Snapshot
7days
Snapshot
60 min
MMON
8-13 Copyright © 2004, Oracle. All rights reserved.
Database Control and AWR
8-14 Copyright © 2004, Oracle. All rights reserved.
AWR Reports
SQL> @?/rdbms/admin/awrrpt
Report_type: html
Num_days: 2
Begin_snap: 150
End_snap: 160
Report_name:
8-15 Copyright © 2004, Oracle. All rights reserved.
Statspack and AWR
Statspackschema
WRschema
Migration
Old application codeusing Statspack
schema
8-16 Copyright © 2004, Oracle. All rights reserved.
Automatic Routine Administration Tasks
AutomaticWorkload
Repository
Serveralerts
Automatedtasks
Advisorframework
Prepackaged routinemaintenance tasks
Resource usage controlled
Statistics collection task scheduledout-of-the-box
Automatic Proactive
Efficient
8-17 Copyright © 2004, Oracle. All rights reserved.
Job Scheduler Concepts
Job
Jobclass
Managementwindow
Consumergroup
Windowgroup
Enabled
Disabled
Resourceplan
8-18 Copyright © 2004, Oracle. All rights reserved.
DBCA and Automated Tasks
8-19 Copyright © 2004, Oracle. All rights reserved.
Server-Generated Alerts
AutomaticWorkload
Repository
Automatedtasks
Advisorframework
Enabled by default Timely generation
Push model
Automatic Proactive
Efficient
Serveralerts
8-20 Copyright © 2004, Oracle. All rights reserved.
Server-Generated Alerts: Overview
Oracle Database
(SGA)
Data dictionary
Is there an issue?
There is an issue.
How can I resolve it?
DBA/EM
AWRmetrics
8-21 Copyright © 2004, Oracle. All rights reserved.
Alert Models: Architecture
Serveralerts
queue.
Oracle database
(SGA)
EMDData
dictionary
Pollstatistics
EMalerts
Server monitors itself.
MMON
AWR
AutomaticPushed
Notification
Serveralerts
Subscribingclients
Thirdparty
8-22 Copyright © 2004, Oracle. All rights reserved.
Server-Generated Alert Types
MMON
85% Warning
97% Critical Cleared
Cleared
Alert
SnapshotToo Old
ResumableSession
Suspended
Recovery Area Low On
Free Space
Metric-based
Event-based
Threshold(stateful)
alerts
Nonthreshold (stateless)
alerts
DBA_OUTSTANDING_ALERTS DBA_ALERT_HISTORY
8-23 Copyright © 2004, Oracle. All rights reserved.
Supplied Server-Generated Alerts
ResumableSession
Suspended
SnapshotToo Old
Recovery Area
Low On Free Space
Tablespace
85% Warning97% Critical
TablespaceSpace Usage
Database Control:SYSTEM metrics
8-24 Copyright © 2004, Oracle. All rights reserved.
Managing Server-Generated Alerts Using Database Control
Set up notification rules (paging, e-mail).
Correct the problem.
Review alert details and advice.
Enable alerts by setting thresholds.
Receive notification.
Verify that the problem is resolved.
8-25 Copyright © 2004, Oracle. All rights reserved.
Setting Alert Thresholds
8-26 Copyright © 2004, Oracle. All rights reserved.
Database Control Interface for Alerts
8-27 Copyright © 2004, Oracle. All rights reserved.
Alerts Notification
8-28 Copyright © 2004, Oracle. All rights reserved.
Viewing Metric Details
8-29 Copyright © 2004, Oracle. All rights reserved.
Metric and Alert Views
V$SYSMETRIC_HISTORYV$SYSMETRIC
V$SERVICEMETRICV$METRICNAME
...
DBA_HIST_SYSMETRIC_HISTORY...
DBA_OUTSTANDING_ALERTSDBA_ALERT_HISTORYDBA_THRESHOLDSV$ALERT_TYPES
...
Recent metrics
Metric history
Server alerts
8-30 Copyright © 2004, Oracle. All rights reserved.
PL/SQL Interface for Threshold Settings
DBMS_SERVER_ALERT
SET_THRESHOLD GET_THRESHOLD
EXECUTE DBMS_SERVER_ALERT.SET_THRESHOLD(DBMS_SERVER_ALERT.CPU_TIME_PER_CALL, DBMS_SERVER_ALERT.OPERATOR_GE, '8000',DBMS_SERVER_ALERT.OPERATOR_GE, '10000', 1,2,'orcl', DBMS_SERVER_ALERT.OBJECT_TYPE_SERVICE,'payroll');
EXECUTE DBMS_SERVER_ALERT.SET_THRESHOLD(6001, NULL, NULL, NULL, NULL, NULL, NULL, 'orcl', DBMS_SERVER_ALERT.OBJECT_TYPE_SERVICE,'payroll');
1
2
Resetting the threshold:
8-32 Copyright © 2004, Oracle. All rights reserved.
Alert Consumption: Manual Configuration
BEGIN dbms_aqadm.add_subscriber('SYS.ALERT_QUE', sys.aq$_agent('alrt_usr1','', 0)); dbms_aqadm.enable_db_access('alrt_usr1','alrt_usr1'); dbms_aqadm.grant_queue_privilege('DEQUEUE', 'alert_que','alrt_usr1', false);END;
BEGIN dbms_aq.dequeue('SYS.ALERT_QUE', dequeue_options, message_properties, message, message_handle);dbms_output.put_line('Reason: ' || dbms_server_alert.expand_message(userenv('LANGUAGE'),message.message_id, message.reason_argument_1, message.reason_argument_2,message.reason_argument_3,message.reason_argument_4,message.reason_argument_5));END;
8-33 Copyright © 2004, Oracle. All rights reserved.
Advisor Framework
AutomaticWorkload
Repository
Serveralerts
Automatedtasks
Advisorframework
Uniform interface Fully integrated
Automatic Proactive
Efficient
8-34 Copyright © 2004, Oracle. All rights reserved.
Advisor Framework: Overview
ADDM
SQL Tuning Advisor
SQL Access Advisor
Memory
Space
PGA Advisor
SGA
Segment Advisor
Undo Advisor
Buffer CacheAdvisor
Library CacheAdvisor
PGA
8-35 Copyright © 2004, Oracle. All rights reserved.
Using an Advisor for a Typical Tuning Session
1. Create an advisor task.
2. Adjust task parameters.
4. Acceptresults?
5. Implementrecommendations.
Yes
No
3. Perform analysis.
8-36 Copyright © 2004, Oracle. All rights reserved.
Database Control and Advisors
8-37 Copyright © 2004, Oracle. All rights reserved.
DBMS_ADVISOR Package
Procedure Description
CREATE_TASK Creates a new task in the repository
DELETE_TASK Deletes a task from the repository
EXECUTE_TASK Initiates execution of the task
INTERRUPT_TASK Suspends a task that is currently executing
GET_TASK_REPORT Provides a text report about recommendations
RESUME_TASK Causes a suspended task to resume
UPDATE_TASK_ATTRIBUTES
Updates task attributes
SET_TASK_PARAMETER Modifies a task parameter
MARK_RECOMMENDATION Marks one or more recommendations as accepted, rejected, or ignored
GET_TASK_SCRIPT Creates a script of all the recommendations that are accepted
8-38 Copyright © 2004, Oracle. All rights reserved.
Using PL/SQL to Invoke an Advisor
DECLARE taskid NUMBER;BEGIN dbms_advisor.create_task('ADDM',taskid,:tname); dbms_advisor.set_task_parameter(:tname, 'START_SNAPSHOT', 60); dbms_advisor.set_task_parameter(:tname, 'END_SNAPSHOT', 66); dbms_advisor.execute_task(:tname); END;/
SELECT dbms_advisor.get_task_report(:tname)FROM dba_advisor_tasks tWHERE t.task_name = :tname AND t.owner = SYS_CONTEXT('userenv', 'session_user');
8-39 Copyright © 2004, Oracle. All rights reserved.
Viewing Advisor Information
View Name Description
DBA_ADVISOR_DEFINITIONS Properties of the advisors
DBA_ADVISOR_TASKS Global information about the task
DBA_ADVISOR_LOG Task’s current status information
DBA_ADVISOR_PARAMETERS Task’s parameters
DBA_ADVISOR_COMMANDS Commands associated with actions
DBA_ADVISOR_OBJECTS Object referenced by tasks
DBA_ADVISOR_FINDINGS Findings discovered by the advisor
DBA_ADVISOR_RECOMMENDATIONS Task’s recommendations
DBA_ADVISOR_ACTIONS Actions associated with recommendations
DBA_ADVISOR_RATIONALE Rationales for the recommendations
DBA_ADVISOR_USAGE Usage information for each advisor
8-40 Copyright © 2004, Oracle. All rights reserved.
Automatic Database Diagnostic Monitor: Overview
• Is a performance-diagnostic engine in the database
• Automatically diagnoses performance problems
• Provides root-cause analysis with recommended solutions
• Identifies areas that have no problems
• Integrates all components and can be used on any database type:– OLTP– Data warehouse– Mixed
8-41 Copyright © 2004, Oracle. All rights reserved.
ADDM Performance Monitoring
Snapshots
ADDM ADDMresults
In-memorystatistics
AWR
SGA
60 minutes
ADDMresults
EM
MMON
8-42 Copyright © 2004, Oracle. All rights reserved.
RAC Waits
IO Waits
Concurrency
Buffer Busy
Parse Latches
Buffer Cache latches
Root causesSymptoms
ADDM Methodology
……
…
…Nonproblem areas
Where is timespent?
Goal: Perform the same workload in less time
System Wait
8-43 Copyright © 2004, Oracle. All rights reserved.
Detecting Top Performance Issues
Excessive logon/logoff
Memory undersizing
Hot blocks and objects w/SQL
RAC service issues
Locks and ITL contention
Checkpointing causes
PL/SQL, Java time
Top SQL
I/O issues
Parsing
Configuration issues
Application usage
Not detectedby Statspack
ADDM identifiestop issues
8-44 Copyright © 2004, Oracle. All rights reserved.
Database Control and ADDM Findings
8-45 Copyright © 2004, Oracle. All rights reserved.
Viewing ADDM Analysis Results
1
2
3
8-46 Copyright © 2004, Oracle. All rights reserved.
Viewing ADDM Recommendations
8-47 Copyright © 2004, Oracle. All rights reserved.
Creating an ADDM Task
8-48 Copyright © 2004, 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_defaultFROM dba_advisor_def_parametersWHERE advisor_name = 'ADDM' AND parameter_name = 'DBIO_EXPECTED';
8-49 Copyright © 2004, Oracle. All rights reserved.
Retrieving ADDM Reports with SQL
SELECT dbms_advisor.GET_TASK_REPORT(task_name)FROM dba_advisor_tasksWHERE 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: 8Enter value for end_snap: 10…Enter value for report_name: Generating the ADDM report for this analysis ...
8-50 Copyright © 2004, Oracle. All rights reserved.
Summary
In this lesson, you should have learned how to:
• Describe the AWR
• Define AWR snapshot baselines
• Subscribe applications to server-generated alerts
• Describe the advisor framework
• Use ADDM
8-51 Copyright © 2004, Oracle. All rights reserved.
Practice 8 Overview:Using Server-Generated Alerts
This practice covers the following topics:
• Configuring server-generated alerts
• Monitoring server-generated alerts