1 / 82

Steven George Sr. Delivery Manager Mark Fuller Sr. Pr. Instructor Rick Pandya Pr. Instructor

Steven George Sr. Delivery Manager Mark Fuller Sr. Pr. Instructor Rick Pandya Pr. Instructor JF Verrier Pr. Curriculum Developer Oracle Corporation. Oracle Database 10 g : Managing the Self-Managing Database. Objectives.

gwyn
Download Presentation

Steven George Sr. Delivery Manager Mark Fuller Sr. Pr. Instructor Rick Pandya Pr. Instructor

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. Steven GeorgeSr. Delivery Manager Mark FullerSr. Pr. Instructor Rick PandyaPr. Instructor JF VerrierPr. Curriculum Developer Oracle Corporation

  2. Oracle Database 10g: Managing the Self-Managing Database

  3. Objectives • Understanding the self-management capabilities of Oracle Database 10g • Customizing the self-management capabilities of Oracle Database 10g

  4. Manageability Goal Reduce Administration Cost • Automatic versus Manual • Intelligence versus Data Reduce Capital Expenditure • Adaptive versus Oversized • Integrated versus Third Party • ½ Cost Reduce Failure Cost • Preventive versus Corrective • Act and Succeed versus Trial and Error

  5. How DBAs Spend Their Time Install 6% Create and Configure 12% Load Data 6% Manage DatabaseSystem 55% MaintainSoftware 6%

  6. SpaceManagement Backup andRecoveryManagement StorageManagement Applicationand SQLManagement SystemResourceManagement ? ? ? ? ? Database Management Challenges

  7. SpaceManagement Backup andRecoveryManagement StorageManagement Applicationand SQLManagement SystemResourceManagement Solution: Self-Managing Database Enterprise ManagerDatabaseConsole Monitor Alert AutomaticManagement Fix Advise CommonManageabilityInfrastructure

  8. Common Manageability Infrastructure: Automatic Workload Repository AutomatedTasks Automatic Proactive ServerAlerts AdvisoryFramework AutomaticWorkload Repository Efficient Data Warehouseof the Database Direct memoryaccess Automatic collection of important statistics

  9. Automatic Workload Repository ADDM finds top problems MMON SYSAUX WR Schema BG In-memorystatistics … 7:00 a.m. Snapshot 1 BG 7:30 a.m. Sevendays Snapshot 2 AWRStatistics 8:00 a.m. ASH 8:30 a.m. FG Snapshot 3 … Snapshot 4 FG SGA 8:30am DBA

  10. Statistics Level STATISTICS_LEVEL BASIC TYPICAL ALL Turn offall self-tuning capabilities Recommendeddefault value Additional statistics for manual SQL diagnostics

  11. Configuring The Workload Repository

  12. Manually Creating Snapshots

  13. Database Feature Usage Metric Collection OracleDatabase 10g Once a week DB Feature UsageStatistics DB High-Water MarkStatistics MMON size of largest segment,maximum number of sessions,maximum number of tables,maximum size of the database, maximum number of data files,… Advanced Replication, Oracle Streams, AQ,Virtual Private Database,Audit options, … AWR DBA_FEATURE_USAGE_STATISTICS DBA_HIGH_WATER_MARK_STATISTICS EM Console HOST CONFIGURATION INFO EM Repository: ECM

  14. Common Manageability Infrastructure:Server Alerts AutomatedTasks Automatic Proactive ServerAlerts AdvisoryFramework AutomaticWorkload Repository Efficient Push model Enabled by default Timely generation

  15. Oracle Server(SGA) Server Alerts Enterprise Manager GuidedResolution AutomaticNotification ServerAlertsQueue Server monitors itself AWR MMON

  16. AdvancedQueue Server Alerts Delivery Process SubscribingClients ConsoleUpdate ThirdParty Queue BG Push FG EM(EMD) Page ore-mailDBAs

  17. Server-Generated Alert Types Metric-Based Alert 97% Critical Cleared ThresholdAlerts Alert 85% Warning Cleared MMON ResumableSessionSuspended Recovery Area Low On Free Space SnapshotToo Old Non-ThresholdAlerts Alert Alert Alert Event-Based

  18. Out-of-the-box Alerts 97% Critical 85% Warning LocallyManaged Tablespace TablespaceSpace Usage ResumableSessionSuspended Recovery Area Low On Free Space SnapshotToo Old

  19. EM Interface to Alerts

  20. Setting Alert Thresholds

  21. Common Manageability Infrastructure:Automated Tasks AutomatedTasks Automatic Proactive ServerAlerts AdvisoryFramework AutomaticWorkload Repository Efficient Statistics collection task scheduledout-of-the-box Pre-packaged routinemaintenance tasks Resource usage controlled

  22. Automatic Optimizer Statistics Collection DBA tracks and gathers statistics Targetsright objects Resolvestwo issues Determinesright samples Automatic statistics collection

  23. Gather Statistics Job MAINTENANCE_WINDOW_GROUP WEEKNIGHT_WINDOW WEEKEND_WINDOW 10pm–6am Mon to Fri 12am Sat to 12am Mon gather_stats_job AUTO_TASKS_JOB_CLASS AUTO_TASKS_CONSUMER_GROUP

  24. Adding New Tasks Using EM

  25. D E M O N S T R A T I O N Creating Automated Tasks

  26. Common Manageability Infrastructure:Advisory Framework AutomatedTasks Automatic Proactive ServerAlerts AdvisoryFramework AutomaticWorkload Repository Efficient Uniform interface Fully integrated

  27. Advisory Framework SQL Tuning PGA Buffer Cache Memory SGA ADDM Access Shared Pool Space Segment Advisor Undo AWR Commondata source Seamlessintegration

  28. Guided Tuning Session Create an advisor task Adjust task parameters EnterpriseManager DatabaseConsole Perform analysis No Yes Acceptresults? Implementrecommendations

  29. Advisory Central

  30. SpaceManagement Backup andRecoveryManagement StorageManagement Applicationand SQLManagement SystemResourceManagement Solution: Self-Managing Database Enterprise ManagerDatabaseConsole Monitor Alert AutomaticManagement Fix Advise CommonInfrastructure

  31. Performance Monitoring Solutions SGA In-memorystatistics MMON Snapshots Alerts ADDM ADDMResults Proactive Monitoring within Oracle Server AWR DBA ReactiveMonitoring

  32. ADDM Performance Monitoring SGA 30 minutes In-memorystatistics MMON Snapshots ADDM ADDMResults EM AWR ADDMResults

  33. ADDM Problem Classification System … … Buffer Busy RAC Waits System Wait … Parse Latches Concurrency Buf Cache latches IO Waits … Non-problem areas Symptoms Root Causes

  34. Accessing ADDM Advice

  35. ADDM Recommendations

  36. Performance Monitoring Solutions SGA In-memorystatistics MMON Snapshots Alerts ADDM ADDMResults Proactive Monitoring within Oracle Server AWR DBA ReactiveMonitoring

  37. Host CPU Bottlenecks Memory Bottlenecks Oracle CPU/Waits Uses ASH and AWR SQL Sessions Performance Management Approach

  38. EM Product Layout Enterprise-wide Console Page Target Databases Page Database Home Page Database Performance Page Drilldowns SQL Session

  39. Database Performance Page

  40. Concurrency Wait Class: Drill Down

  41. SpaceManagement Backup andRecoveryManagement StorageManagement SystemResourceManagement Solution: Self-Managing Database Applicationand SQLManagement Enterprise ManagerDatabaseConsole Monitor Alert AutomaticManagement Fix Advise CommonInfrastructure

  42. I can doit for you! ADDM Automate the SQL Tuning Process SQL Workload DBA High-loadSQL SQL Tuning Advisor

  43. Statistics Check Optimization Mode Plan Tuning Optimization Mode Access Analysis Optimization Mode SQL Tuning Advisor SQL Analysis Optimization Mode SQL Tuning Advisor Overview AutomaticTuning Optimizer ComprehensiveSQL Tuning DetectStale or MissingStatistics Plan Tuning(SQL Profile) Add MissingIndex RunAccess Advisor RestructureSQL

  44. Optimizer (Tuning Mode) SQL TuningAdvisor Optimizer (Normal Mode) DatabaseUsers Plan Tuning Flow create submit SQL Profile use No application codechange output Well-TunedPlan

  45. SQL Tuning Advisor Usage Model Automatic Selection AWR ADDM High-load SQL SQLTuning Advisor Sources ManualSelection AWR STS Cursor Cache Filter/Rank DBA Custom

  46. D E M O N S T R A T I O N Resolving Performance Problems

  47. SpaceManagement Backup andRecoveryManagement StorageManagement Solution: Self-Managing Database Applicationand SQLManagement Enterprise ManagerDatabaseConsole SystemResourceManagement Monitor Alert AutomaticManagement Fix Advise CommonInfrastructure

  48. Overview of Automatic Shared Memory Management Shared Pool Database Buffer Cache Redo Log Buffer Java Pool Large Pool Fixed SGA SGA MMAN Automatically set the optimal size

  49. Benefits of Automatic Shared Memory Management DB_CACHE_SIZE SHARED_POOL_SIZE LARGE_POOL_SIZE JAVA_POOL_SIZE Total SGA size SGA_TARGET

More Related