820 likes | 982 Views
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.
E N D
Steven GeorgeSr. Delivery Manager Mark FullerSr. Pr. Instructor Rick PandyaPr. Instructor JF VerrierPr. Curriculum Developer Oracle Corporation
Oracle Database 10g: Managing the Self-Managing Database
Objectives • Understanding the self-management capabilities of Oracle Database 10g • Customizing the self-management capabilities of Oracle Database 10g
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
How DBAs Spend Their Time Install 6% Create and Configure 12% Load Data 6% Manage DatabaseSystem 55% MaintainSoftware 6%
SpaceManagement Backup andRecoveryManagement StorageManagement Applicationand SQLManagement SystemResourceManagement ? ? ? ? ? Database Management Challenges
SpaceManagement Backup andRecoveryManagement StorageManagement Applicationand SQLManagement SystemResourceManagement Solution: Self-Managing Database Enterprise ManagerDatabaseConsole Monitor Alert AutomaticManagement Fix Advise CommonManageabilityInfrastructure
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
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
Statistics Level STATISTICS_LEVEL BASIC TYPICAL ALL Turn offall self-tuning capabilities Recommendeddefault value Additional statistics for manual SQL diagnostics
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
Common Manageability Infrastructure:Server Alerts AutomatedTasks Automatic Proactive ServerAlerts AdvisoryFramework AutomaticWorkload Repository Efficient Push model Enabled by default Timely generation
Oracle Server(SGA) Server Alerts Enterprise Manager GuidedResolution AutomaticNotification ServerAlertsQueue Server monitors itself AWR MMON
AdvancedQueue Server Alerts Delivery Process SubscribingClients ConsoleUpdate ThirdParty Queue BG Push FG EM(EMD) Page ore-mailDBAs
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
Out-of-the-box Alerts 97% Critical 85% Warning LocallyManaged Tablespace TablespaceSpace Usage ResumableSessionSuspended Recovery Area Low On Free Space SnapshotToo Old
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
Automatic Optimizer Statistics Collection DBA tracks and gathers statistics Targetsright objects Resolvestwo issues Determinesright samples Automatic statistics collection
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
D E M O N S T R A T I O N Creating Automated Tasks
Common Manageability Infrastructure:Advisory Framework AutomatedTasks Automatic Proactive ServerAlerts AdvisoryFramework AutomaticWorkload Repository Efficient Uniform interface Fully integrated
Advisory Framework SQL Tuning PGA Buffer Cache Memory SGA ADDM Access Shared Pool Space Segment Advisor Undo AWR Commondata source Seamlessintegration
Guided Tuning Session Create an advisor task Adjust task parameters EnterpriseManager DatabaseConsole Perform analysis No Yes Acceptresults? Implementrecommendations
SpaceManagement Backup andRecoveryManagement StorageManagement Applicationand SQLManagement SystemResourceManagement Solution: Self-Managing Database Enterprise ManagerDatabaseConsole Monitor Alert AutomaticManagement Fix Advise CommonInfrastructure
Performance Monitoring Solutions SGA In-memorystatistics MMON Snapshots Alerts ADDM ADDMResults Proactive Monitoring within Oracle Server AWR DBA ReactiveMonitoring
ADDM Performance Monitoring SGA 30 minutes In-memorystatistics MMON Snapshots ADDM ADDMResults EM AWR ADDMResults
ADDM Problem Classification System … … Buffer Busy RAC Waits System Wait … Parse Latches Concurrency Buf Cache latches IO Waits … Non-problem areas Symptoms Root Causes
Performance Monitoring Solutions SGA In-memorystatistics MMON Snapshots Alerts ADDM ADDMResults Proactive Monitoring within Oracle Server AWR DBA ReactiveMonitoring
Host CPU Bottlenecks Memory Bottlenecks Oracle CPU/Waits Uses ASH and AWR SQL Sessions Performance Management Approach
EM Product Layout Enterprise-wide Console Page Target Databases Page Database Home Page Database Performance Page Drilldowns SQL Session
SpaceManagement Backup andRecoveryManagement StorageManagement SystemResourceManagement Solution: Self-Managing Database Applicationand SQLManagement Enterprise ManagerDatabaseConsole Monitor Alert AutomaticManagement Fix Advise CommonInfrastructure
I can doit for you! ADDM Automate the SQL Tuning Process SQL Workload DBA High-loadSQL SQL Tuning Advisor
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
Optimizer (Tuning Mode) SQL TuningAdvisor Optimizer (Normal Mode) DatabaseUsers Plan Tuning Flow create submit SQL Profile use No application codechange output Well-TunedPlan
SQL Tuning Advisor Usage Model Automatic Selection AWR ADDM High-load SQL SQLTuning Advisor Sources ManualSelection AWR STS Cursor Cache Filter/Rank DBA Custom
D E M O N S T R A T I O N Resolving Performance Problems
SpaceManagement Backup andRecoveryManagement StorageManagement Solution: Self-Managing Database Applicationand SQLManagement Enterprise ManagerDatabaseConsole SystemResourceManagement Monitor Alert AutomaticManagement Fix Advise CommonInfrastructure
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
Benefits of Automatic Shared Memory Management DB_CACHE_SIZE SHARED_POOL_SIZE LARGE_POOL_SIZE JAVA_POOL_SIZE Total SGA size SGA_TARGET