proactively maintaining the database n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Proactively Maintaining the Database PowerPoint Presentation
Download Presentation
Proactively Maintaining the Database

Loading in 2 Seconds...

play fullscreen
1 / 50

Proactively Maintaining the Database - PowerPoint PPT Presentation


  • 121 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

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