les04-准则-警告-基准ppt课件

1、4Copyright 2006, Oracle. All rights reserved.Metrics, Alerts, and Baselines4-2Copyright 2006, Oracle. All rights reserved.ObjectivesAfter completing this lesson, you should be able to do the following:View metrics by using the metrics history viewsCreate metric thresholdsView alertsCreate metric bas

2、elinesEnable adaptive thresholds4-3Copyright 2006, Oracle. All rights reserved.Metrics, Alerts, and BaselinesMetric: Rate of change in a cumulative statisticAlert: Event generated when a monitored metric crosses a thresholdBaseline: Data gathered from a “normal running database for performance compa

3、rison4-4Copyright 2006, Oracle. All rights reserved.Limitation of Base Statistics30 minutes30 minutesV$SYSSTATPhysical readsI/OI/OTimeTimeLow rateHigh rateStatistics just keep growing.2M2M4-5Copyright 2006, Oracle. All rights reserved.Typical Delta ToolsComparison of statistics at two points in time

4、 is needed.The following tools produce deltas:StatspackAWR reportsCustomized scripts4-6Copyright 2006, Oracle. All rights reserved.Oracle Database 10g Solution: MetricsBase statistic 1Redo generationMetric 1Metric 2Redo generation/TxClient 1Client 2Client 3Client 4V$METRICNAMEV$SYSMETRICV$SESSMETRIC

5、V$FILEMETRICV$EVENTMETRICMMONUser commitUser rollbackV$WAITCLASSMETRICV$SERVICEMETRIC4-7Copyright 2006, Oracle. All rights reserved.Benefits of MetricsDBAAnalyze differences.Query a metric.Without metricsWith metrics4-8Copyright 2006, Oracle. All rights reserved.Viewing Metric History InformationIn

6、memory:V$SYSMETRIC_HISTORYV$FILEMETRIC_HISTORYV$WAITCLASSMETRIC_HISTORYV$SERVICEMETRIC_HISTORYV$SESSION_WAIT_HISTORY (last 10 events)On disk:DBA_HIST_SYSMETRIC_SUMMARYDBA_HIST_SYSMETRIC_HISTORY (alerts only)DBA_HIST_SESSMETRIC_HISTORYDBA_HIST_SYSTEM_EVENT (cumulative)DBA_HIST_FILEMETRIC_HISTORY (ale

7、rts only)DBA_HIST_FILESTATXS (cumulative)DBA_HIST_WAITCLASSMET_HISTORY (alert)4-9Copyright 2006, Oracle. All rights reserved.Viewing Detailed Information for a Metric4-10Copyright 2006, Oracle. All rights reserved.Statistic Histograms30 minutesV$SYSSTATPhysical readsI/OTimeHigh rate1248163264 128 25

8、6 512222NumberofwaitsTimeperiodV$FILE_HISTOGRAMIs it areal problem?55004-11Copyright 2006, Oracle. All rights reserved.Histogram ViewsV$EVENT_HISTOGRAM: For each event such as “db file sequential readV$FILE_HISTOGRAM: For single block reads on a per data file basisV$TEMP_HISTOGRAM: For single block

9、reads on a per temp-file basis4-12Copyright 2006, Oracle. All rights reserved.Server-Generated AlertsServeralertsqueue.Metric exceedsthreshold.AWREnterprise ManagerOracle instance4-13Copyright 2006, Oracle. All rights reserved.Database Control Usage ModelSet up notification rules (paging, ).Correct

10、the problem.Review alert details and advice.Enable alerts by setting thresholds.Receive notification.Verify that the problem is resolved.4-14Copyright 2006, Oracle. All rights reserved.Setting Thresholds4-15Copyright 2006, Oracle. All rights reserved.Creating and Testing an AlertSpecify a threshold.

11、Create a test case.Check for an alert.2134-16Copyright 2006, Oracle. All rights reserved.Metric and Alert ViewsRecent metricsMetric historyV$SYSMETRIC_HISTORYV$SYSMETRICV$SERVICEMETRICV$METRICNAME.DBA_HIST_SYSMETRIC_HISTORY.DBA_OUTSTANDING_ALERTSDBA_ALERT_HISTORYDBA_THRESHOLDSV$ALERT_TYPES.Server al

12、erts4-17Copyright 2006, Oracle. All rights reserved.User-Defined SQL Metrics4-18Copyright 2006, Oracle. All rights reserved.Practice Overview: Work with MetricsThis practice covers the following topics:View metrics using the metrics history viewsCreate metric thresholdsView alertsClear alerts4-19Cop

13、yright 2006, Oracle. All rights reserved.Working with Metric BaselinesEnable metric baselines.Create a metric baseline.Static baselineMoving window baselineView the baseline statistics.Set a threshold on a baseline.Fixed threshold (percent of maximum) Adaptive threshold4-20Copyright 2006, Oracle. Al

14、l rights reserved.Enabling Metric Baselining4-21Copyright 2006, Oracle. All rights reserved.Creating Static Metric Baselines4-22Copyright 2006, Oracle. All rights reserved.Creating Static Metric Baselines4-23Copyright 2006, Oracle. All rights reserved.Time GroupingBaseline periodTimegroupedDayand ni

15、ghtHourof dayWeekdaysand weekendDayof weekAllhoursAlldays8am11am7am7am7pm7am7am7pmMoSuMoSuMoSa&SuDailyWeekly4-24Copyright 2006, Oracle. All rights reserved.Time Grouping: ConsiderationsMo Tu We Th Fr Sa SuNoSufficient data?Day of weekGrouped by7 daysMoving window sizeYesSufficient data?Day of we

16、ekGrouped by14 daysMoving window sizeYesSufficient data?Weekdays and weekendGrouped by7 daysMoving window sizeDatapointsWeekdays WeekendMo Tu We Th Fr Sa SuDatapointsDatapointsSufficiency level1 week ago2 weeks ago4-25Copyright 2006, Oracle. All rights reserved.Activating the StaticMetric Baseline4-

17、26Copyright 2006, Oracle. All rights reserved.Activating the Moving WindowMetric Baseline4-27Copyright 2006, Oracle. All rights reserved.Visualizing Metric Baseline Statistics4-28Copyright 2006, Oracle. All rights reserved.Setting Adaptive Alert Thresholds4-29Copyright 2006, Oracle. All rights reser

18、ved.Setting Adaptive Alert Thresholds4-30Copyright 2006, Oracle. All rights reserved.Metric Baseline and Adaptive Threshold: ConsiderationsBaselining must be enabled using Enterprise Manager.Only one moving window baseline can be defined.Multiple static baselines can be defined.Only one baseline can

19、 be active at a time.Adaptive thresholds require an active baseline.4-31Copyright 2006, Oracle. All rights reserved.Classification of Eligible MetricsDatabase Time (centiseconds per second)Response Time (per transaction)System Response Time (centiseconds)Performance MetricsExecutes (per second)Redo

20、Generated (per second)Network Bytes (per second)Physical Writes (per second)Physical Reads (per second)Current Logons CountUser Calls (per second)Number of Transactions (per second)Workload Volume Metrics Database Block Changes (per transaction)Enqueue Requests (per transaction)Total Parses (per transaction)Session Logical Reads (per transaction)Workload Type Metrics4-32Copyright 2006, Oracle. All rights reserved.Me