1 / 50

Proactively Maintaining the Database

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

mira
Download Presentation

Proactively Maintaining the Database

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Proactively Maintaining the Database

  2. 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)

  3. Spacemanagement Backup andrecoverymanagement Storagemanagement Applicationand SQLmanagement Systemresourcemanagement Oracle Database 10g: Self-Managing Database Monitor Alert Automaticmanagement Fix Advise Commonmanageabilityinfrastructure

  4. Automatic Workload Repository Automatedtasks Automatic Proactive Serveralerts Advisorframework AutomaticWorkload Repository Efficient Direct memoryaccess Data warehouseof the database Automatic collection of important statistics

  5. Automatic Workload Repository: Overview External clients EM SQL*Plus … SGA V$ DBA_* Efficientin-memorystatisticscollection AWRsnapshots MMON Self-tuningcomponent Self-tuningcomponent ADDM … Internal clients

  6. 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

  7. Active Session History Rolling buffer SGA Statistics ASH V$SESSION Recent history MMON V$ACTIVE_SESSION_HISTORY MMNL AWR snapshots

  8. 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

  9. 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.

  10. Statistic Levels STATISTICS_LEVEL BASIC TYPICAL ALL Disable all self-tuningcapabilities Recommendeddefault value Additional statisticsfor manualSQL diagnostics

  11. 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);

  12. 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);

  13. Database Control and AWR

  14. AWR Reports SQL> @?/rdbms/admin/awrrpt Report_type: html Num_days: 2 Begin_snap: 150 End_snap: 160 Report_name:

  15. Statspack and AWR WRschema Statspackschema Migration Old application codeusing Statspackschema

  16. 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

  17. Job Scheduler Concepts Consumergroup Resourceplan Jobclass Enabled Managementwindow Job Windowgroup Disabled

  18. DBCA and Automated Tasks

  19. Server-Generated Alerts Automatedtasks Automatic Proactive Serveralerts Advisorframework AutomaticWorkload Repository Efficient Push model Enabled by default Timely generation

  20. 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.

  21. Alert Models: Architecture Subscribingclients Serveralerts EMalerts Thirdparty AutomaticPushedNotification EMD Pollstatistics Datadictionary Serveralertsqueue. Server monitors itself. Oracle database(SGA) AWR MMON

  22. 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

  23. Supplied Server-Generated Alerts 97% Critical 85% Warning Tablespace Database Control:SYSTEM metrics TablespaceSpace Usage SnapshotToo Old ResumableSessionSuspended Recovery Area Low On Free Space

  24. 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.

  25. Setting Alert Thresholds

  26. Database Control Interface for Alerts

  27. Alerts Notification

  28. Viewing Metric Details

  29. 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 ...

  30. 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

  31. 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;

  32. Advisor Framework Automatedtasks Automatic Proactive Serveralerts Advisorframework AutomaticWorkload Repository Efficient Uniform interface Fully integrated

  33. Advisor Framework: Overview PGA Advisor PGA SQL Tuning Advisor Buffer CacheAdvisor Memory SGA ADDM SQL Access Advisor Library CacheAdvisor Segment Advisor Space Undo Advisor

  34. 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.

  35. Database Control and Advisors

  36. DBMS_ADVISOR Package

  37. 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');

  38. Viewing Advisor Information

  39. 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

  40. ADDM Performance Monitoring MMON 60 minutes In-memorystatistics Snapshots SGA ADDM ADDMresults EM AWR ADDMresults

  41. 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

  42. Detecting Top Performance Issues Not detectedby Statspack ADDM identifiestop issues

  43. Database Control and ADDM Findings

  44. Viewing ADDM Analysis Results 1 2 3

  45. Viewing ADDM Recommendations

  46. Creating an ADDM Task

  47. 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';

  48. 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 ...

  49. 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

  50. Practice 8 Overview:Using Server-Generated Alerts • This practice covers the following topics: • Configuring server-generated alerts • Monitoring server-generated alerts

More Related