steven george sr. delivery manager mark fuller sr. pr. instructor rick pandya pr. instructor jf...
Post on 20-Dec-2015
225 views
TRANSCRIPT
![Page 1: Steven George Sr. Delivery Manager Mark Fuller Sr. Pr. Instructor Rick Pandya Pr. Instructor JF Verrier Pr. Curriculum Developer Oracle Corporation](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649d4a5503460f94a2717c/html5/thumbnails/1.jpg)
![Page 2: Steven George Sr. Delivery Manager Mark Fuller Sr. Pr. Instructor Rick Pandya Pr. Instructor JF Verrier Pr. Curriculum Developer Oracle Corporation](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649d4a5503460f94a2717c/html5/thumbnails/2.jpg)
Steven GeorgeSr. Delivery Manager
Mark FullerSr. Pr. Instructor
Rick PandyaPr. Instructor
JF VerrierPr. Curriculum Developer
Oracle Corporation
![Page 3: Steven George Sr. Delivery Manager Mark Fuller Sr. Pr. Instructor Rick Pandya Pr. Instructor JF Verrier Pr. Curriculum Developer Oracle Corporation](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649d4a5503460f94a2717c/html5/thumbnails/3.jpg)
Oracle Database 10 g:
Managing the Self-Managing Database
![Page 4: Steven George Sr. Delivery Manager Mark Fuller Sr. Pr. Instructor Rick Pandya Pr. Instructor JF Verrier Pr. Curriculum Developer Oracle Corporation](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649d4a5503460f94a2717c/html5/thumbnails/4.jpg)
Objectives
• Understanding the self-management capabilities of Oracle Database 10
g
• Customizing the self-management capabilities of Oracle Database 10
g
![Page 5: Steven George Sr. Delivery Manager Mark Fuller Sr. Pr. Instructor Rick Pandya Pr. Instructor JF Verrier Pr. Curriculum Developer Oracle Corporation](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649d4a5503460f94a2717c/html5/thumbnails/5.jpg)
Manageability Goal
• Automatic versus Manual• Intelligence versus Data
½ Cost
• Adaptive versus Oversized• Integrated versus Third Party
• Preventive versus Corrective• Act and Succeed versus Trial and Error
Reduce Administration Cost
Reduce Capital Expenditure
Reduce Failure Cost
![Page 6: Steven George Sr. Delivery Manager Mark Fuller Sr. Pr. Instructor Rick Pandya Pr. Instructor JF Verrier Pr. Curriculum Developer Oracle Corporation](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649d4a5503460f94a2717c/html5/thumbnails/6.jpg)
How DBAs Spend Their Time
Load Data 6%
MaintainSoftware 6%
Install 6% Create and Configure 12%
Manage DatabaseSystem 55%
![Page 7: Steven George Sr. Delivery Manager Mark Fuller Sr. Pr. Instructor Rick Pandya Pr. Instructor JF Verrier Pr. Curriculum Developer Oracle Corporation](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649d4a5503460f94a2717c/html5/thumbnails/7.jpg)
Database Management Challenges
Applicationand SQL
Management
StorageManagement
Backup andRecovery
Management
SystemResource
Management
SpaceManagement
?
?
??
?
![Page 8: Steven George Sr. Delivery Manager Mark Fuller Sr. Pr. Instructor Rick Pandya Pr. Instructor JF Verrier Pr. Curriculum Developer Oracle Corporation](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649d4a5503460f94a2717c/html5/thumbnails/8.jpg)
Solution: Self-Managing Database
Applicationand SQL
Management
StorageManagement
Backup andRecovery
Management
SystemResource
Management
SpaceManagement
Fix Advise
AlertMonitor
CommonManageabilityInfrastructure
AutomaticManagement
Enterprise
ManagerDatabaseConsole
![Page 9: Steven George Sr. Delivery Manager Mark Fuller Sr. Pr. Instructor Rick Pandya Pr. Instructor JF Verrier Pr. Curriculum Developer Oracle Corporation](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649d4a5503460f94a2717c/html5/thumbnails/9.jpg)
Common Manageability Infrastructure: Automatic Workload Repository
AutomaticWorkload
Repository
ServerAlerts
AutomatedTasks
AdvisoryFramework
Data Warehouseof the Database Automatic collection
of important statistics
Direct memoryaccess
Automatic Proactive
Efficient
![Page 10: Steven George Sr. Delivery Manager Mark Fuller Sr. Pr. Instructor Rick Pandya Pr. Instructor JF Verrier Pr. Curriculum Developer Oracle Corporation](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649d4a5503460f94a2717c/html5/thumbnails/10.jpg)
Automatic Workload Repository
SGA
In-memorystatistics
AWRStatistics
ASH
SYSAUXMMON
7:00 a.m.7:30 a.m.
8:00 a.m.
WR Schema
Snapshot 1
Snapshot 2
Snapshot 3
Snapshot 4
8:30 a.m.
8:30am
ADDM finds top problems
BG
BG
…
FG
FG
…
DBA
Sevendays
![Page 11: Steven George Sr. Delivery Manager Mark Fuller Sr. Pr. Instructor Rick Pandya Pr. Instructor JF Verrier Pr. Curriculum Developer Oracle Corporation](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649d4a5503460f94a2717c/html5/thumbnails/11.jpg)
Statistics Level
STATISTICS_LEVEL
BASIC TYPICAL ALL
Recommendeddefault value
Additional statistics for manual SQL diagnostics
Turn offall self-tuning capabilities
![Page 12: Steven George Sr. Delivery Manager Mark Fuller Sr. Pr. Instructor Rick Pandya Pr. Instructor JF Verrier Pr. Curriculum Developer Oracle Corporation](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649d4a5503460f94a2717c/html5/thumbnails/12.jpg)
Configuring The Workload Repository
![Page 13: Steven George Sr. Delivery Manager Mark Fuller Sr. Pr. Instructor Rick Pandya Pr. Instructor JF Verrier Pr. Curriculum Developer Oracle Corporation](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649d4a5503460f94a2717c/html5/thumbnails/13.jpg)
Manually Creating Snapshots
![Page 14: Steven George Sr. Delivery Manager Mark Fuller Sr. Pr. Instructor Rick Pandya Pr. Instructor JF Verrier Pr. Curriculum Developer Oracle Corporation](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649d4a5503460f94a2717c/html5/thumbnails/14.jpg)
Database Feature Usage Metric Collection
MMONDB Feature Usage
Statistics DB High-Water Mark
StatisticsAdvanced Replication, Oracle Streams, AQ,Virtual Private Database,Audit options, …
size of largest segment,maximum number of sessions,maximum number of tables,maximum size of the database,
maximum number of data files,…
DBA_FEATURE_USAGE_STATISTICS DBA_HIGH_WATER_MARK_STATISTICS
OracleDatabase
10g
AWR
HOST CONFIGURATION INFO
EM Repository: ECM
EM Console
Once a week
![Page 15: Steven George Sr. Delivery Manager Mark Fuller Sr. Pr. Instructor Rick Pandya Pr. Instructor JF Verrier Pr. Curriculum Developer Oracle Corporation](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649d4a5503460f94a2717c/html5/thumbnails/15.jpg)
Common Manageability Infrastructure:Server Alerts
AutomaticWorkload
Repository
ServerAlerts
AutomatedTasks
AdvisoryFramework
Enabled by defaultTimely generation
Push model
Automatic Proactive
Efficient
![Page 16: Steven George Sr. Delivery Manager Mark Fuller Sr. Pr. Instructor Rick Pandya Pr. Instructor JF Verrier Pr. Curriculum Developer Oracle Corporation](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649d4a5503460f94a2717c/html5/thumbnails/16.jpg)
Server Alerts
Oracle Server(SGA)
ServerAlertsQueue
Enterprise
Manager
Server monitors itself
MMON
AWR
AutomaticNotification
GuidedResolution
![Page 17: Steven George Sr. Delivery Manager Mark Fuller Sr. Pr. Instructor Rick Pandya Pr. Instructor JF Verrier Pr. Curriculum Developer Oracle Corporation](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649d4a5503460f94a2717c/html5/thumbnails/17.jpg)
Server Alerts Delivery Process
AdvancedQueue
Queue
SubscribingClients
FG
BG
Push
ThirdParty
ConsoleUpdate
Page or
e-mailDBAs
EM(EMD)
![Page 18: Steven George Sr. Delivery Manager Mark Fuller Sr. Pr. Instructor Rick Pandya Pr. Instructor JF Verrier Pr. Curriculum Developer Oracle Corporation](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649d4a5503460f94a2717c/html5/thumbnails/18.jpg)
Alert
Server-Generated Alert Types
MMON
85% Warning
97% CriticalAlert Cleared
Cleared
ThresholdAlerts
Non-Threshold
Alerts
Metric-Based
Event-Based
Alert Alert Alert
SnapshotToo Old
ResumableSession
Suspended
Recovery Area Low On
Free Space
![Page 19: Steven George Sr. Delivery Manager Mark Fuller Sr. Pr. Instructor Rick Pandya Pr. Instructor JF Verrier Pr. Curriculum Developer Oracle Corporation](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649d4a5503460f94a2717c/html5/thumbnails/19.jpg)
Out-of-the-box Alerts
ResumableSession
Suspended
SnapshotToo Old
Recovery Area
Low On Free Space
Tablespace
85% Warning97% Critical
TablespaceSpace Usage
LocallyManaged
![Page 20: Steven George Sr. Delivery Manager Mark Fuller Sr. Pr. Instructor Rick Pandya Pr. Instructor JF Verrier Pr. Curriculum Developer Oracle Corporation](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649d4a5503460f94a2717c/html5/thumbnails/20.jpg)
EM Interface to Alerts
![Page 21: Steven George Sr. Delivery Manager Mark Fuller Sr. Pr. Instructor Rick Pandya Pr. Instructor JF Verrier Pr. Curriculum Developer Oracle Corporation](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649d4a5503460f94a2717c/html5/thumbnails/21.jpg)
Setting Alert Thresholds
![Page 22: Steven George Sr. Delivery Manager Mark Fuller Sr. Pr. Instructor Rick Pandya Pr. Instructor JF Verrier Pr. Curriculum Developer Oracle Corporation](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649d4a5503460f94a2717c/html5/thumbnails/22.jpg)
Common Manageability Infrastructure:Automated Tasks
AutomaticWorkload
Repository
ServerAlerts
AutomatedTasks
AdvisoryFramework
Pre-packaged routinemaintenance tasks
Resource usage controlled
Statistics collection task scheduledout-of-the-box
Automatic Proactive
Efficient
![Page 23: Steven George Sr. Delivery Manager Mark Fuller Sr. Pr. Instructor Rick Pandya Pr. Instructor JF Verrier Pr. Curriculum Developer Oracle Corporation](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649d4a5503460f94a2717c/html5/thumbnails/23.jpg)
Automatic Optimizer Statistics Collection
Automatic statistics collection
DBA tracks and gathers statistics
Resolvestwo issues
Targetsright objects
Determinesright samples
![Page 24: Steven George Sr. Delivery Manager Mark Fuller Sr. Pr. Instructor Rick Pandya Pr. Instructor JF Verrier Pr. Curriculum Developer Oracle Corporation](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649d4a5503460f94a2717c/html5/thumbnails/24.jpg)
Gather Statistics Job
AUTO_TASKS_JOB_CLASS
AUTO_TASKS_CONSUMER_GROUP
gather_stats_job
WEEKNIGHT_WINDOW
10pm–6am Mon to Fri
MAINTENANCE_WINDOW_GROUP
WEEKEND_WINDOW
12am Sat to 12am Mon
![Page 25: Steven George Sr. Delivery Manager Mark Fuller Sr. Pr. Instructor Rick Pandya Pr. Instructor JF Verrier Pr. Curriculum Developer Oracle Corporation](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649d4a5503460f94a2717c/html5/thumbnails/25.jpg)
Adding New Tasks Using EM
![Page 26: Steven George Sr. Delivery Manager Mark Fuller Sr. Pr. Instructor Rick Pandya Pr. Instructor JF Verrier Pr. Curriculum Developer Oracle Corporation](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649d4a5503460f94a2717c/html5/thumbnails/26.jpg)
D E M O N S T R A T I O N
Creating Automated Tasks
![Page 27: Steven George Sr. Delivery Manager Mark Fuller Sr. Pr. Instructor Rick Pandya Pr. Instructor JF Verrier Pr. Curriculum Developer Oracle Corporation](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649d4a5503460f94a2717c/html5/thumbnails/27.jpg)
Common Manageability Infrastructure:Advisory Framework
AutomaticWorkload
Repository
ServerAlerts
AutomatedTasks
AdvisoryFramework
Uniform interface Fully integrated
Automatic Proactive
Efficient
![Page 28: Steven George Sr. Delivery Manager Mark Fuller Sr. Pr. Instructor Rick Pandya Pr. Instructor JF Verrier Pr. Curriculum Developer Oracle Corporation](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649d4a5503460f94a2717c/html5/thumbnails/28.jpg)
Advisory Framework
ADDM
SQL Tuning
Access
Memory
Space
PGA
SGA
Segment Advisor
Undo
Buffer Cache
Shared Pool
SeamlessintegrationAWR
Commondata source
![Page 29: Steven George Sr. Delivery Manager Mark Fuller Sr. Pr. Instructor Rick Pandya Pr. Instructor JF Verrier Pr. Curriculum Developer Oracle Corporation](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649d4a5503460f94a2717c/html5/thumbnails/29.jpg)
Guided Tuning Session
Create an advisor task
Adjust task parameters
Acceptresults?
Implementrecommendations
YesNo
Perform analysis
EnterpriseManager DatabaseConsole
![Page 30: Steven George Sr. Delivery Manager Mark Fuller Sr. Pr. Instructor Rick Pandya Pr. Instructor JF Verrier Pr. Curriculum Developer Oracle Corporation](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649d4a5503460f94a2717c/html5/thumbnails/30.jpg)
Advisory Central
![Page 31: Steven George Sr. Delivery Manager Mark Fuller Sr. Pr. Instructor Rick Pandya Pr. Instructor JF Verrier Pr. Curriculum Developer Oracle Corporation](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649d4a5503460f94a2717c/html5/thumbnails/31.jpg)
Solution: Self-Managing Database
Applicationand SQL
Management
StorageManagement
Backup andRecovery
Management
SystemResource
Management
SpaceManagement
Fix Advise
AlertMonitor
CommonInfrastructure
AutomaticManagement
Enterprise
ManagerDatabaseConsole
![Page 32: Steven George Sr. Delivery Manager Mark Fuller Sr. Pr. Instructor Rick Pandya Pr. Instructor JF Verrier Pr. Curriculum Developer Oracle Corporation](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649d4a5503460f94a2717c/html5/thumbnails/32.jpg)
Performance Monitoring Solutions
Snapshots
ADDM ADDMResults
MMON
Alerts
In-memorystatistics
AWR
SGA
ReactiveMonitoring
Proactive Monitoring within
Oracle ServerDBA
![Page 33: Steven George Sr. Delivery Manager Mark Fuller Sr. Pr. Instructor Rick Pandya Pr. Instructor JF Verrier Pr. Curriculum Developer Oracle Corporation](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649d4a5503460f94a2717c/html5/thumbnails/33.jpg)
ADDM Performance Monitoring
Snapshots
ADDM
MMONIn-memorystatistics
AWR
SGA
ADDMResults
EM
30 minutes
ADDMResults
![Page 34: Steven George Sr. Delivery Manager Mark Fuller Sr. Pr. Instructor Rick Pandya Pr. Instructor JF Verrier Pr. Curriculum Developer Oracle Corporation](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649d4a5503460f94a2717c/html5/thumbnails/34.jpg)
System Wait
RAC Waits
IO Waits
Concurrency
Buffer Busy
Parse Latches
Buf Cache latches
Root CausesSymptoms
ADDM Problem Classification System
…… ……
……
……Non-problem areas
![Page 35: Steven George Sr. Delivery Manager Mark Fuller Sr. Pr. Instructor Rick Pandya Pr. Instructor JF Verrier Pr. Curriculum Developer Oracle Corporation](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649d4a5503460f94a2717c/html5/thumbnails/35.jpg)
Accessing ADDM Advice
![Page 36: Steven George Sr. Delivery Manager Mark Fuller Sr. Pr. Instructor Rick Pandya Pr. Instructor JF Verrier Pr. Curriculum Developer Oracle Corporation](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649d4a5503460f94a2717c/html5/thumbnails/36.jpg)
ADDM Recommendations
![Page 37: Steven George Sr. Delivery Manager Mark Fuller Sr. Pr. Instructor Rick Pandya Pr. Instructor JF Verrier Pr. Curriculum Developer Oracle Corporation](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649d4a5503460f94a2717c/html5/thumbnails/37.jpg)
Performance Monitoring Solutions
Snapshots
ADDM ADDMResults
MMON
Alerts
In-memorystatistics
AWR
SGA
ReactiveMonitoring
Proactive Monitoring within
Oracle ServerDBA
![Page 38: Steven George Sr. Delivery Manager Mark Fuller Sr. Pr. Instructor Rick Pandya Pr. Instructor JF Verrier Pr. Curriculum Developer Oracle Corporation](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649d4a5503460f94a2717c/html5/thumbnails/38.jpg)
Performance Management Approach
CPU/Waits
SQL Sessions
Host
Oracle
CPU Bottlenecks Memory Bottlenecks
Uses ASH and AWR
![Page 39: Steven George Sr. Delivery Manager Mark Fuller Sr. Pr. Instructor Rick Pandya Pr. Instructor JF Verrier Pr. Curriculum Developer Oracle Corporation](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649d4a5503460f94a2717c/html5/thumbnails/39.jpg)
EM Product Layout
Enterprise-wide Console Page
Target Databases Page
Database Home Page
Database Performance Page
Drilldowns
SQL Session
![Page 40: Steven George Sr. Delivery Manager Mark Fuller Sr. Pr. Instructor Rick Pandya Pr. Instructor JF Verrier Pr. Curriculum Developer Oracle Corporation](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649d4a5503460f94a2717c/html5/thumbnails/40.jpg)
Database Performance Page
![Page 41: Steven George Sr. Delivery Manager Mark Fuller Sr. Pr. Instructor Rick Pandya Pr. Instructor JF Verrier Pr. Curriculum Developer Oracle Corporation](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649d4a5503460f94a2717c/html5/thumbnails/41.jpg)
Concurrency Wait Class: Drill Down
![Page 42: Steven George Sr. Delivery Manager Mark Fuller Sr. Pr. Instructor Rick Pandya Pr. Instructor JF Verrier Pr. Curriculum Developer Oracle Corporation](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649d4a5503460f94a2717c/html5/thumbnails/42.jpg)
Solution: Self-Managing Database
Applicationand SQL
Management
StorageManagement
Backup andRecovery
Management
SystemResource
Management
SpaceManagement
Fix Advise
AlertMonitor
CommonInfrastructure
AutomaticManagement
Enterprise
ManagerDatabaseConsole
![Page 43: Steven George Sr. Delivery Manager Mark Fuller Sr. Pr. Instructor Rick Pandya Pr. Instructor JF Verrier Pr. Curriculum Developer Oracle Corporation](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649d4a5503460f94a2717c/html5/thumbnails/43.jpg)
Automate the SQL Tuning Process
DBA
ADDM
SQL Tuning Advisor
High-loadSQL
SQL Workload
I can doit for you!
![Page 44: Steven George Sr. Delivery Manager Mark Fuller Sr. Pr. Instructor Rick Pandya Pr. Instructor JF Verrier Pr. Curriculum Developer Oracle Corporation](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649d4a5503460f94a2717c/html5/thumbnails/44.jpg)
SQL Tuning Advisor Overview
Add MissingIndex Run
Access Advisor
RestructureSQL
Plan Tuning(SQL Profile)
AutomaticTuning Optimizer
SQL AnalysisOptimization
Mode
Access Analysis Optimization
Mode
Plan Tuning Optimization
Mode
Statistics CheckOptimization
Mode
DetectStale or Missing
Statistics
ComprehensiveSQL Tuning
SQL Tuning Advisor
![Page 45: Steven George Sr. Delivery Manager Mark Fuller Sr. Pr. Instructor Rick Pandya Pr. Instructor JF Verrier Pr. Curriculum Developer Oracle Corporation](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649d4a5503460f94a2717c/html5/thumbnails/45.jpg)
Plan Tuning Flow
Optimizer
(Tuning Mode)
createsubmit
output
SQL Profile
DatabaseUsers
Well-TunedPlan
SQL TuningAdvisor
use
No application code
change Optimizer
(Normal Mode)
![Page 46: Steven George Sr. Delivery Manager Mark Fuller Sr. Pr. Instructor Rick Pandya Pr. Instructor JF Verrier Pr. Curriculum Developer Oracle Corporation](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649d4a5503460f94a2717c/html5/thumbnails/46.jpg)
SQL Tuning Advisor Usage Model
SQLTuning Advisor
ADDM High-load SQL
Cursor Cache
STS
Custom
Filter/Rank
Sources
ManualSelection
Automatic Selection
AWR
DBA
AWR
![Page 47: Steven George Sr. Delivery Manager Mark Fuller Sr. Pr. Instructor Rick Pandya Pr. Instructor JF Verrier Pr. Curriculum Developer Oracle Corporation](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649d4a5503460f94a2717c/html5/thumbnails/47.jpg)
D E M O N S T R A T I O N
Resolving Performance
Problems
![Page 48: Steven George Sr. Delivery Manager Mark Fuller Sr. Pr. Instructor Rick Pandya Pr. Instructor JF Verrier Pr. Curriculum Developer Oracle Corporation](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649d4a5503460f94a2717c/html5/thumbnails/48.jpg)
Solution: Self-Managing Database
Applicationand SQL
Management
StorageManagement
Backup andRecovery
Management
SpaceManagement
Fix Advise
AlertMonitor
CommonInfrastructure
AutomaticManagement
Enterprise
ManagerDatabaseConsole
SystemResource
Management
![Page 49: Steven George Sr. Delivery Manager Mark Fuller Sr. Pr. Instructor Rick Pandya Pr. Instructor JF Verrier Pr. Curriculum Developer Oracle Corporation](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649d4a5503460f94a2717c/html5/thumbnails/49.jpg)
Overview of Automatic Shared Memory Management
Java Pool
DatabaseBuffer Cache
Redo Log Buffer
Shared Pool
Large Pool
SGA
Fixed SGA
Automatically set the optimal size
MMAN
![Page 50: Steven George Sr. Delivery Manager Mark Fuller Sr. Pr. Instructor Rick Pandya Pr. Instructor JF Verrier Pr. Curriculum Developer Oracle Corporation](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649d4a5503460f94a2717c/html5/thumbnails/50.jpg)
Benefits of Automatic Shared Memory Management
DB_CACHE_SIZESHARED_POOL_SIZELARGE_POOL_SIZEJAVA_POOL_SIZE
Total SGA size
SGA_TARGET
![Page 51: Steven George Sr. Delivery Manager Mark Fuller Sr. Pr. Instructor Rick Pandya Pr. Instructor JF Verrier Pr. Curriculum Developer Oracle Corporation](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649d4a5503460f94a2717c/html5/thumbnails/51.jpg)
Using EM to ConfigureAutomatic Shared Memory Management
![Page 52: Steven George Sr. Delivery Manager Mark Fuller Sr. Pr. Instructor Rick Pandya Pr. Instructor JF Verrier Pr. Curriculum Developer Oracle Corporation](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649d4a5503460f94a2717c/html5/thumbnails/52.jpg)
D E M O N S T R A T I O N
Automating Memory
Management
![Page 53: Steven George Sr. Delivery Manager Mark Fuller Sr. Pr. Instructor Rick Pandya Pr. Instructor JF Verrier Pr. Curriculum Developer Oracle Corporation](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649d4a5503460f94a2717c/html5/thumbnails/53.jpg)
Solution: Self-Managing Database
Applicationand SQL
Management
StorageManagement
Backup andRecovery
Management
SystemResource
Management
Fix Advise
AlertMonitor
CommonInfrastructure
AutomaticManagement
Enterprise
ManagerDatabaseConsole
SpaceManagement
![Page 54: Steven George Sr. Delivery Manager Mark Fuller Sr. Pr. Instructor Rick Pandya Pr. Instructor JF Verrier Pr. Curriculum Developer Oracle Corporation](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649d4a5503460f94a2717c/html5/thumbnails/54.jpg)
Data
Segment Shrink Overview
HWM
HWM
ShrinkOperation
Data UnusedSpace
UnusedSpace
Reclaimed Space
Online and in-place
Data
![Page 55: Steven George Sr. Delivery Manager Mark Fuller Sr. Pr. Instructor Rick Pandya Pr. Instructor JF Verrier Pr. Curriculum Developer Oracle Corporation](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649d4a5503460f94a2717c/html5/thumbnails/55.jpg)
How Can I Shrink Segments?
ALTER TABLE employees SHRINK SPACE CASCADE;
ALTER … SHRINK SPACE [CASCADE]
TABLE INDEX MATERIALIZED VIEW MATERIALIZED VIEW LOG
MODIFY PARTITION MODIFY LOB
ALTER TABLE employees ENABLE ROW MOVEMENT; 1
2
MODIFY SUBPARTITION
![Page 56: Steven George Sr. Delivery Manager Mark Fuller Sr. Pr. Instructor Rick Pandya Pr. Instructor JF Verrier Pr. Curriculum Developer Oracle Corporation](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649d4a5503460f94a2717c/html5/thumbnails/56.jpg)
Segment Advisor
![Page 57: Steven George Sr. Delivery Manager Mark Fuller Sr. Pr. Instructor Rick Pandya Pr. Instructor JF Verrier Pr. Curriculum Developer Oracle Corporation](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649d4a5503460f94a2717c/html5/thumbnails/57.jpg)
Growth Trend Report
![Page 58: Steven George Sr. Delivery Manager Mark Fuller Sr. Pr. Instructor Rick Pandya Pr. Instructor JF Verrier Pr. Curriculum Developer Oracle Corporation](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649d4a5503460f94a2717c/html5/thumbnails/58.jpg)
D E M O N S T R A T I O N
Proactively Managing Space
![Page 59: Steven George Sr. Delivery Manager Mark Fuller Sr. Pr. Instructor Rick Pandya Pr. Instructor JF Verrier Pr. Curriculum Developer Oracle Corporation](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649d4a5503460f94a2717c/html5/thumbnails/59.jpg)
Segment Resource Estimation
![Page 60: Steven George Sr. Delivery Manager Mark Fuller Sr. Pr. Instructor Rick Pandya Pr. Instructor JF Verrier Pr. Curriculum Developer Oracle Corporation](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649d4a5503460f94a2717c/html5/thumbnails/60.jpg)
Automatic Undo Retention Tuning
• Proactive tuning– Undo retention is tuned for longest-running query.– Query duration information is collected every 30
seconds.
• Reactive tuning– Undo retention is gradually lowered under space
pressure.– Oldest unexpired extents are used first.
• Enabled by default
![Page 61: Steven George Sr. Delivery Manager Mark Fuller Sr. Pr. Instructor Rick Pandya Pr. Instructor JF Verrier Pr. Curriculum Developer Oracle Corporation](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649d4a5503460f94a2717c/html5/thumbnails/61.jpg)
D E M O N S T R A T I O N
Using Automatic Undo Retention
Tuning
![Page 62: Steven George Sr. Delivery Manager Mark Fuller Sr. Pr. Instructor Rick Pandya Pr. Instructor JF Verrier Pr. Curriculum Developer Oracle Corporation](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649d4a5503460f94a2717c/html5/thumbnails/62.jpg)
Undo Advisor
![Page 63: Steven George Sr. Delivery Manager Mark Fuller Sr. Pr. Instructor Rick Pandya Pr. Instructor JF Verrier Pr. Curriculum Developer Oracle Corporation](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649d4a5503460f94a2717c/html5/thumbnails/63.jpg)
Redo Logfile Size Advisor
![Page 64: Steven George Sr. Delivery Manager Mark Fuller Sr. Pr. Instructor Rick Pandya Pr. Instructor JF Verrier Pr. Curriculum Developer Oracle Corporation](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649d4a5503460f94a2717c/html5/thumbnails/64.jpg)
Solution: Self-Managing Database
Applicationand SQL
Management
StorageManagement
SystemResource
Management
SpaceManagement
Fix Advise
AlertMonitor
CommonInfrastructure
AutomaticManagement
Enterprise
ManagerDatabaseConsole
Backup andRecovery
Management
![Page 65: Steven George Sr. Delivery Manager Mark Fuller Sr. Pr. Instructor Rick Pandya Pr. Instructor JF Verrier Pr. Curriculum Developer Oracle Corporation](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649d4a5503460f94a2717c/html5/thumbnails/65.jpg)
Automatic Backup (DBCA Setup)
![Page 66: Steven George Sr. Delivery Manager Mark Fuller Sr. Pr. Instructor Rick Pandya Pr. Instructor JF Verrier Pr. Curriculum Developer Oracle Corporation](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649d4a5503460f94a2717c/html5/thumbnails/66.jpg)
Optimized Incremental Backup
Optimizes incremental backups
• Tracks which blocks have changed since last backup
Oracle Database 10g has integrated change tracking.
• New Change Tracking File is introduced.
• Changed blocks are tracked as redo is generated.
• Database backup automatically uses changed block list.
List of Changed Blocks
RedoGeneration
Redo Log
101100101011000011101001011010101110011
ChangeTrackingFile
SGA
![Page 67: Steven George Sr. Delivery Manager Mark Fuller Sr. Pr. Instructor Rick Pandya Pr. Instructor JF Verrier Pr. Curriculum Developer Oracle Corporation](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649d4a5503460f94a2717c/html5/thumbnails/67.jpg)
Defining Flash Recovery Area
![Page 68: Steven George Sr. Delivery Manager Mark Fuller Sr. Pr. Instructor Rick Pandya Pr. Instructor JF Verrier Pr. Curriculum Developer Oracle Corporation](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649d4a5503460f94a2717c/html5/thumbnails/68.jpg)
Flash Recovery Area Space Management
Disk limit is reached and a new file needs to be written into the Recovery Area.
12
Backup filesto be deleted
Database filebackup
1234
Warning is issued to user.
Recovery Area
Space pressure occurs.
RMAN updates
list of files that
may be deleted.
Oracle deletes files that are nolonger requiredon disk.
![Page 69: Steven George Sr. Delivery Manager Mark Fuller Sr. Pr. Instructor Rick Pandya Pr. Instructor JF Verrier Pr. Curriculum Developer Oracle Corporation](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649d4a5503460f94a2717c/html5/thumbnails/69.jpg)
Suggested Strategy
![Page 70: Steven George Sr. Delivery Manager Mark Fuller Sr. Pr. Instructor Rick Pandya Pr. Instructor JF Verrier Pr. Curriculum Developer Oracle Corporation](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649d4a5503460f94a2717c/html5/thumbnails/70.jpg)
Flashback Error Correction
• Flashback Database – Restore database to time– Uses flashback logs
• Flashback Drop– Restore dropped table– Uses recycle bin
• Flashback Table – Restore all rows in table to time– Uses UNDO in database
• Flashback Transaction Query– Query a committed Txn
• Flashback Versions Query– Query changes to rows over time
Order
Order Line
DatabaseCustomer
![Page 71: Steven George Sr. Delivery Manager Mark Fuller Sr. Pr. Instructor Rick Pandya Pr. Instructor JF Verrier Pr. Curriculum Developer Oracle Corporation](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649d4a5503460f94a2717c/html5/thumbnails/71.jpg)
Solution: Self-Managing Database
Applicationand SQL
Management
Backup andRecovery
Management
SystemResource
Management
SpaceManagement
Fix Advise
AlertMonitor
CommonInfrastructure
AutomaticManagement
Enterprise
ManagerDatabaseConsole Storage
Management
![Page 72: Steven George Sr. Delivery Manager Mark Fuller Sr. Pr. Instructor Rick Pandya Pr. Instructor JF Verrier Pr. Curriculum Developer Oracle Corporation](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649d4a5503460f94a2717c/html5/thumbnails/72.jpg)
What Is Automatic Storage Management
Database
Tablespace
Segment
Extent
ASM managesOracle files.
ASM solvesmanagement problems
of Oracle databases.
ASM does notreplace existing
concepts.
![Page 73: Steven George Sr. Delivery Manager Mark Fuller Sr. Pr. Instructor Rick Pandya Pr. Instructor JF Verrier Pr. Curriculum Developer Oracle Corporation](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649d4a5503460f94a2717c/html5/thumbnails/73.jpg)
Hierarchy
Tablespace
Segment
Extent
Oracleblock
Data file
Physicalblock
ASMdisk group
ASM disk
ASM file
Allocation unitFile system
file orraw device
Database
Or
![Page 74: Steven George Sr. Delivery Manager Mark Fuller Sr. Pr. Instructor Rick Pandya Pr. Instructor JF Verrier Pr. Curriculum Developer Oracle Corporation](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649d4a5503460f94a2717c/html5/thumbnails/74.jpg)
ASM Benefits
Reducesadministration
complexity
Reduces the costof managing storage
Supports RAC
Improves performance,
scalability,and reliability
![Page 75: Steven George Sr. Delivery Manager Mark Fuller Sr. Pr. Instructor Rick Pandya Pr. Instructor JF Verrier Pr. Curriculum Developer Oracle Corporation](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649d4a5503460f94a2717c/html5/thumbnails/75.jpg)
ASM Files
Database file
ASM file automatically spread inside disk group dgroupA
CREATE TABLESPACE sample DATAFILE '+dgroupA';
1234
1 2 3 4
Automatic ASM filecreation
![Page 76: Steven George Sr. Delivery Manager Mark Fuller Sr. Pr. Instructor Rick Pandya Pr. Instructor JF Verrier Pr. Curriculum Developer Oracle Corporation](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649d4a5503460f94a2717c/html5/thumbnails/76.jpg)
SQL Statements Issued to ASM Instances
ASM Instance
CREATE DISKGROUP
ALTER DISKGROUP DROP DISKGROUP
ALTER SYSTEMRESTRICTED SESSION
![Page 77: Steven George Sr. Delivery Manager Mark Fuller Sr. Pr. Instructor Rick Pandya Pr. Instructor JF Verrier Pr. Curriculum Developer Oracle Corporation](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649d4a5503460f94a2717c/html5/thumbnails/77.jpg)
Enterprise Manager and ASM
![Page 78: Steven George Sr. Delivery Manager Mark Fuller Sr. Pr. Instructor Rick Pandya Pr. Instructor JF Verrier Pr. Curriculum Developer Oracle Corporation](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649d4a5503460f94a2717c/html5/thumbnails/78.jpg)
D E M O N S T R A T I O N
Using Automatic Storage
Management
![Page 79: Steven George Sr. Delivery Manager Mark Fuller Sr. Pr. Instructor Rick Pandya Pr. Instructor JF Verrier Pr. Curriculum Developer Oracle Corporation](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649d4a5503460f94a2717c/html5/thumbnails/79.jpg)
How Oracle Database 10 g DBAs
Spend Their Time
Load Data 3%MaintainSoftware 3%
Install 3%Create and
Configure 6%
Manage DatabaseSystem 25%
Proactive andStrategic
Planning 50+%
![Page 80: Steven George Sr. Delivery Manager Mark Fuller Sr. Pr. Instructor Rick Pandya Pr. Instructor JF Verrier Pr. Curriculum Developer Oracle Corporation](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649d4a5503460f94a2717c/html5/thumbnails/80.jpg)
Summary
• Oracle Database 10 g’s self-management
capabilities work out-of-the-box.• Customization of Oracle Database 10
g’s self-management capabilities can be done through Enterprise Manager.
• Oracle Database 10 g is a self-managing
database which reduces administration overhead and enables DBAs to become proactive strategists.
![Page 81: Steven George Sr. Delivery Manager Mark Fuller Sr. Pr. Instructor Rick Pandya Pr. Instructor JF Verrier Pr. Curriculum Developer Oracle Corporation](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649d4a5503460f94a2717c/html5/thumbnails/81.jpg)
AQ&Q U E S T I O N SQ U E S T I O N S
A N S W E R SA N S W E R S
![Page 82: Steven George Sr. Delivery Manager Mark Fuller Sr. Pr. Instructor Rick Pandya Pr. Instructor JF Verrier Pr. Curriculum Developer Oracle Corporation](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649d4a5503460f94a2717c/html5/thumbnails/82.jpg)
Reminder – please complete the OracleWorld online session survey
Thank you.