Proactively maintaining the database
Download
1 / 50

Proactively Maintaining the Database - PowerPoint PPT Presentation


  • 100 Views
  • Uploaded on

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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' Proactively Maintaining the Database' - mira


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

Objectives
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)


Oracle database 10 g self managing database

Spacemanagement

Backup andrecoverymanagement

Storagemanagement

Applicationand SQLmanagement

Systemresourcemanagement

Oracle Database 10g: Self-Managing Database

Monitor

Alert

Automaticmanagement

Fix

Advise

Commonmanageabilityinfrastructure


Automatic workload repository
Automatic Workload Repository

Automatedtasks

Automatic

Proactive

Serveralerts

Advisorframework

AutomaticWorkload Repository

Efficient

Direct memoryaccess

Data warehouseof the database

Automatic collection of important statistics


Automatic workload repository overview
Automatic Workload Repository: Overview

External clients

EM

SQL*Plus

SGA

V$

DBA_*

Efficientin-memorystatisticscollection

AWRsnapshots

MMON

Self-tuningcomponent

Self-tuningcomponent

ADDM

Internal clients


Automatic workload repository data
Automatic Workload Repository Data

  • New base statistics:

    • SQL and optimizerstatistics

    • OS statistics

    • Wait classes

    • Time statistics

  • Metrics

  • Active Session History

  • Advisor results

  • Snapshot statistics

  • Database feature usage

V$SYSSTAT

V$SQL

V$SEGMENT_STATISTICS

V$SYS_TIME_MODEL

V$SYSMETRIC_HISTORYV$SYSTEM_WAIT_CLASS

V$OSSTAT

V$ACTIVE_SESSION_HISTORY

DBA_ADVISOR_*

DBA_HIST_*

DBA_FEATURE_*DBA_HIGH_WATER_MARK_*

DBA_TAB_STATS_HISTORY


Active session history
Active Session History

Rolling buffer

SGA

Statistics

ASH

V$SESSION

Recent history

MMON

V$ACTIVE_SESSION_HISTORY

MMNL

AWR snapshots


Base statistics and metrics
Base Statistics and Metrics

V$SYSMETRIC, V$SESSMETRIC, V$SERVICEMETRIC, V$METRICNAME

V$FILEMETRIC, V$EVENTMETRIC, V$WAITCLASSMETRIC

Client 1

Client 2

Client 3

Client 4

MMON

Metric 1

Metric 2

Redo Generation/Tx

Everyminute

Base Statistic 1

Redo Generation

User Commit

User Rollback


Workload repository
Workload Repository

MMON

ADDM finds top problems

SYSAUX

SGA

6:00 a.m.

Snapshot 1

In-memorystatistics

7:00 a.m.

Snapshot 2

8:00 a.m.

Snapshot 3

9:00 a.m.

Snapshot 4

9:30 a.m.


Statistic levels
Statistic Levels

STATISTICS_LEVEL

BASIC

TYPICAL

ALL

Disable all

self-tuningcapabilities

Recommendeddefault value

Additional statisticsfor manualSQL diagnostics


Awr snapshot baselines
AWR Snapshot Baselines

Relevant periodin the past

DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE ( -

start_snap_id IN NUMBER ,

end_snap_id IN NUMBER ,

baseline_name IN VARCHAR2);


Awr snapshot purging policy
AWR Snapshot Purging Policy

SYSAUX tablespace

WR schema

60 min

Snapshot

MMON

7days

Snapshot

Snapshot

Snapshot

Snapshot

DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS ( -

retention IN NUMBER DEFAULT NULL,

interval IN NUMBER DEFAULT NULL);



Awr reports
AWR Reports

SQL> @?/rdbms/admin/awrrpt

Report_type: html

Num_days: 2

Begin_snap: 150

End_snap: 160

Report_name:


Statspack and awr
Statspack and AWR

WRschema

Statspackschema

Migration

Old application codeusing Statspackschema


Automatic routine administration tasks
Automatic Routine Administration Tasks

Automatedtasks

Automatic

Proactive

Serveralerts

Advisorframework

AutomaticWorkload Repository

Efficient

Prepackaged routinemaintenance tasks

Resource usage controlled

Statistics collection task scheduledout-of-the-box


Job scheduler concepts
Job Scheduler Concepts

Consumergroup

Resourceplan

Jobclass

Enabled

Managementwindow

Job

Windowgroup

Disabled



Server generated alerts
Server-Generated Alerts

Automatedtasks

Automatic

Proactive

Serveralerts

Advisorframework

AutomaticWorkload Repository

Efficient

Push model

Enabled by default

Timely generation


Server generated alerts overview
Server-Generated Alerts: Overview

Data dictionary

Is there an issue?

How can I resolve it?

Oracle Database(SGA)

DBA/EM

AWRmetrics

There is an issue.


Alert models architecture
Alert Models: Architecture

Subscribingclients

Serveralerts

EMalerts

Thirdparty

AutomaticPushedNotification

EMD

Pollstatistics

Datadictionary

Serveralertsqueue.

Server monitors itself.

Oracle database(SGA)

AWR

MMON


Server generated alert types
Server-Generated Alert Types

Metric-based

97% Critical

Cleared

Threshold(stateful)alerts

85% Warning

Cleared

MMON

DBA_OUTSTANDING_ALERTS

DBA_ALERT_HISTORY

Recovery Area Low On Free Space

ResumableSessionSuspended

SnapshotToo Old

Nonthreshold (stateless)alerts

Alert

Event-based


Supplied server generated alerts
Supplied Server-Generated Alerts

97% Critical

85% Warning

Tablespace

Database Control:SYSTEM metrics

TablespaceSpace Usage

SnapshotToo Old

ResumableSessionSuspended

Recovery Area Low On Free Space


Managing server generated alerts using database control
Managing Server-Generated Alerts Using Database Control

Enable alerts by setting thresholds.

Set up notification rules (paging, e-mail).

Receive notification.

Review alert details and advice.

Correct the problem.

Verify that the problem is resolved.






Metric and alert views
Metric and Alert Views

Metric history

DBA_HIST_SYSMETRIC_HISTORY

...

Server alerts

Recent metrics

DBA_OUTSTANDING_ALERTS

DBA_ALERT_HISTORY

DBA_THRESHOLDS

V$ALERT_TYPES

...

V$SYSMETRIC_HISTORY

V$SYSMETRIC

V$SERVICEMETRIC

V$METRICNAME

...


Pl sql interface for threshold settings
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');

1

Resetting the threshold:

EXECUTE DBMS_SERVER_ALERT.SET_THRESHOLD(6001, NULL, NULL, NULL, NULL, NULL, NULL, 'orcl', DBMS_SERVER_ALERT.OBJECT_TYPE_SERVICE,'payroll');

2


Alert consumption manual configuration
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;


Advisor framework
Advisor Framework

Automatedtasks

Automatic

Proactive

Serveralerts

Advisorframework

AutomaticWorkload Repository

Efficient

Uniform interface

Fully integrated


Advisor framework overview
Advisor Framework: Overview

PGA Advisor

PGA

SQL Tuning Advisor

Buffer CacheAdvisor

Memory

SGA

ADDM

SQL Access Advisor

Library CacheAdvisor

Segment Advisor

Space

Undo Advisor


Using an advisor for a typical tuning session
Using an Advisor for a Typical Tuning Session

1. Create an advisor task.

2. Adjust task parameters.

3. Perform analysis.

4. Acceptresults?

No

Yes

5. Implementrecommendations.



Dbms advisor package
DBMS_ADVISOR Package


Using pl sql to invoke an advisor
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 t

WHERE t.task_name = :tname AND

t.owner = SYS_CONTEXT('userenv',

'session_user');



Automatic database diagnostic monitor overview
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


Addm performance monitoring
ADDM Performance Monitoring

MMON

60 minutes

In-memorystatistics

Snapshots

SGA

ADDM

ADDMresults

EM

AWR

ADDMresults


Addm methodology
ADDM Methodology

Goal: Perform the same workload in less time

RAC Waits

Buffer Busy

System Wait

Parse Latches

Concurrency

Where is timespent?

Buffer Cache latches

IO Waits

Nonproblem areas

Symptoms

Root causes


Detecting top performance issues
Detecting Top Performance Issues

Not detectedby Statspack

ADDM identifiestop issues






Changing addm attributes
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';


Retrieving addm reports with sql
Retrieving ADDM Reports with 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 ...


Summary
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


Practice 8 overview using server generated alerts
Practice 8 Overview:Using Server-Generated Alerts

  • This practice covers the following topics:

    • Configuring server-generated alerts

    • Monitoring server-generated alerts


ad