Transcript Oracle Database 10 g

Steven George
Sr. Delivery Manager
Mark Fuller
Sr. Pr. Instructor
Rick Pandya
Pr. Instructor
JF Verrier
Pr. Curriculum Developer
Oracle Corporation
g
10 :
Oracle Database
Managing the SelfManaging Database
Objectives
• Understanding the self-management
capabilities of Oracle Database 10 g
• Customizing the self-management
capabilities of Oracle Database 10 g
Manageability Goal
Reduce Administration Cost
•
•
Automatic versus Manual
Intelligence versus Data
Reduce Capital Expenditure
•
•
Adaptive versus Oversized
Integrated versus Third Party
Reduce Failure Cost
•
•
Preventive versus Corrective
Act and Succeed versus Trial and Error
½ Cost
How DBAs Spend Their Time
Install 6%
Create and
Configure 12%
Load Data 6%
Manage Database
System 55%
Maintain
Software 6%
Database Management Challenges
?
?
Application
and SQL
Management
Storage
Management
System
Resource
Management
?
?
Backup and
Recovery
Management
Space
Management
?
Solution: Self-Managing Database
Enterprise
Manager
Database
Console
Application
and SQL
Management
Storage
Management
Monitor
Fix
Backup and
Recovery
Management
Alert
Automatic
Management
Common
Manageability
Infrastructure
System
Resource
Management
Advise
Space
Management
Common Manageability Infrastructure:
Automatic Workload Repository
Automated
Tasks
Automatic
Server
Alerts
Proactive
Advisory
Framework
Automatic
Workload
Repository
Data Warehouse
of the Database
Automatic collection
of important statistics
Efficient
Direct memory
access
Automatic Workload Repository
ADDM finds
top problems
MMON
SYSAUX
WR Schema
BG
…
BG
FG
…
FG
In-memory
statistics
AWR
ASH
Statistics
7:00 a.m.
7:30 a.m.
8:00 a.m.
8:30 a.m.
Snapshot 1
Snapshot 2
Snapshot 3
Snapshot 4
SGA
8:30am
DBA
Seven
days
Statistics Level
STATISTICS_LEVEL
BASIC
TYPICAL
ALL
Turn off
all self-tuning
capabilities
Recommended
default value
Additional
statistics for
manual SQL
diagnostics
Configuring The Workload Repository
Manually Creating Snapshots
Database Feature Usage Metric Collection
Oracle
Database
10g
Once a week
DB Feature Usage
Statistics
MMON
Advanced Replication,
Oracle Streams, AQ,
Virtual Private Database,
Audit options, …
AWR
DB High-Water Mark
Statistics
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
HOST CONFIGURATION INFO
EM Repository: ECM
EM Console
Common Manageability Infrastructure:
Server Alerts
Automatic
Automated
Tasks
Server
Alerts
Proactive
Advisory
Framework
Automatic
Workload
Repository
Efficient
Push model
Enabled by default
Timely generation
Server Alerts
Enterprise
Manager
Automatic
Notification
Guided
Resolution
Server
Alerts
Queue
Server monitors
itself
Oracle Server
(SGA)
MMON
AWR
Server Alerts Delivery Process
Subscribing
Console
Clients
Update
Third
Party
Queue
BG
Advanced
Queue
Push
FG
EM
(EMD)
Page
or
e-mail
DBAs
Server-Generated Alert Types
Metric-Based
Alert
97% Critical
Cleared
Threshold
Alerts
Alert
85% Warning
Cleared
MMON
NonThreshold
Alerts
Snapshot
Too Old
Resumable
Session
Suspended
Recovery Area
Low On
Free Space
Alert
Alert
Alert
Event-Based
Out-of-the-box Alerts
97% Critical
85% Warning
Locally
Managed
Tablespace
Tablespace
Space Usage
Resumable
Session
Suspended
Recovery Area
Low On
Free Space
Snapshot
Too Old
EM Interface to Alerts
Setting Alert Thresholds
Common Manageability Infrastructure:
Automated Tasks
Automatic
Automated
Tasks
Server
Alerts
Proactive
Advisory
Framework
Automatic
Workload
Repository
Pre-packaged routine
maintenance tasks
Resource usage
controlled
Efficient
Statistics collection
task scheduled
out-of-the-box
Automatic Optimizer Statistics Collection
DBA tracks and
gathers statistics
Targets
right objects
Resolves
two issues
Automatic statistics
collection
Determines
right samples
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
Adding New Tasks Using EM
D E M O N S T R A T I O N
Creating
Automated Tasks
Common Manageability Infrastructure:
Advisory Framework
Automatic
Automated
Tasks
Server
Alerts
Proactive
Advisory
Framework
Automatic
Workload
Repository
Uniform interface
Efficient
Fully integrated
Advisory Framework
SQL Tuning
PGA
Buffer Cache
Memory
SGA
ADDM
Access
Space
Shared Pool
Segment Advisor
Undo
Common
data source
AWR
Seamless
integration
Guided Tuning Session
Create an advisor task
Adjust task parameters
Enterprise
Manager
Database
Console
Perform analysis
No
Accept
results?
Yes
Implement
recommendations
Advisory Central
Solution: Self-Managing Database
Enterprise
Manager
Database
Console
Application
and SQL
Management
Storage
Management
Monitor
Fix
Backup and
Recovery
Management
Alert
Automatic
Management
System
Resource
Management
Advise
Space
Management
Common
Infrastructure
Performance Monitoring Solutions
SGA
In-memory
statistics
MMON
Snapshots
Alerts
DBA
Reactive
Monitoring
ADDM
Proactive
Monitoring within
Oracle Server
ADDM
Results
AWR
ADDM Performance Monitoring
SGA
In-memory
statistics
30 minutes
MMON
Snapshots
ADDM
EM
ADDM
Results
ADDM
Results
AWR
ADDM Problem Classification System
…
…
RAC Waits
Buffer Busy
Concurrency
Parse Latches
System Wait
Buf Cache latches
IO Waits
…
Non-problem areas
Symptoms
Root Causes
…
Accessing ADDM Advice
ADDM Recommendations
Performance Monitoring Solutions
SGA
In-memory
statistics
MMON
Snapshots
Alerts
DBA
Reactive
Monitoring
ADDM
Proactive
Monitoring within
Oracle Server
ADDM
Results
AWR
Performance Management Approach
Host
CPU Bottlenecks
Memory Bottlenecks
Oracle
CPU/Waits
Uses ASH and AWR
SQL
Sessions
EM Product Layout
Enterprise-wide Console Page
Target Databases Page
Database Home Page
Database Performance Page
Drilldowns
SQL
Session
Database Performance Page
Concurrency Wait Class: Drill Down
Solution: Self-Managing Database
Enterprise
Manager
Database
Console
Application
and SQL
Management
Storage
Management
Monitor
Fix
Backup and
Recovery
Management
Alert
Automatic
Management
System
Resource
Management
Advise
Space
Management
Common
Infrastructure
Automate the SQL Tuning Process
I can do
it for you!
SQL Workload
DBA
ADDM
High-load
SQL
SQL Tuning
Advisor
SQL Tuning Advisor Overview
Automatic
Tuning Optimizer
Comprehensive
SQL Tuning
Statistics Check
Optimization
Mode
Detect
Stale or Missing
Statistics
Plan Tuning
Optimization
Mode
Plan Tuning
(SQL Profile)
Access Analysis
Optimization
Mode
Add Missing
Index Run
Access Advisor
SQL Analysis
Optimization
Mode
SQL Tuning
Advisor
Restructure
SQL
Plan Tuning Flow
submit
Optimizer
(Tuning Mode)
create
SQL Tuning
Advisor
SQL
Profile
use
No application
code
change
Optimizer
(Normal Mode)
Database
Users
output
Well-Tuned
Plan
SQL Tuning Advisor Usage Model
Automatic Selection
AWR
ADDM
High-load SQL
Sources
AWR
Cursor Cache
Custom
Manual
Selection
STS
Filter/Rank
DBA
SQL
Tuning
Advisor
D E M O N S T R A T I O N
Resolving
Performance
Problems
Solution: Self-Managing Database
Enterprise
Manager
Database
Console
Application
and SQL
Management
Storage
Management
Monitor
Fix
Backup and
Recovery
Management
Alert
Automatic
Management
System
Resource
Management
Advise
Space
Management
Common
Infrastructure
Overview of Automatic Shared Memory
Management
Shared Pool
Database
Buffer Cache
Redo Log
Buffer
Java Pool
Fixed SGA
Large Pool
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
Using EM to Configure
Automatic Shared Memory Management
D E M O N S T R A T I O N
Automating
Memory
Management
Solution: Self-Managing Database
Enterprise
Manager
Database
Console
Application
and SQL
Management
Storage
Management
Monitor
Fix
Backup and
Recovery
Management
Alert
Automatic
Management
System
Resource
Management
Advise
Space
Management
Common
Infrastructure
Segment Shrink Overview
Data
Unused
Space
Data
Unused
Space
HWM
Shrink
Operation
Online and in-place
Reclaimed Space
Data
HWM
How Can I Shrink Segments?
ALTER … SHRINK SPACE [CASCADE]
TABLE
INDEX
MATERIALIZED VIEW
MODIFY PARTITION
MATERIALIZED VIEW LOG
MODIFY SUBPARTITION
MODIFY LOB
ALTER TABLE employees ENABLE ROW MOVEMENT;
1
ALTER TABLE employees SHRINK SPACE CASCADE; 2
Segment Advisor
Growth Trend Report
D E M O N S T R A T I O N
Proactively
Managing Space
Segment Resource Estimation
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
D E M O N S T R A T I O N
Using Automatic
Undo Retention
Tuning
Undo Advisor
Redo Logfile Size Advisor
Solution: Self-Managing Database
Enterprise
Manager
Database
Console
Application
and SQL
Management
Storage
Management
Monitor
Fix
Backup and
Recovery
Management
Alert
Automatic
Management
System
Resource
Management
Advise
Space
Management
Common
Infrastructure
Automatic Backup (DBCA Setup)
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
SGA
Redo
Generation
1011001010110
0001110100101
1010101110011
Change
Tracking
File
Redo Log
Defining Flash Recovery Area
Flash Recovery Area Space Management
Database file
backup
1
2
3
4
Recovery
Area
Disk limit is
reached and a
new file needs
to be written
into the
Recovery Area.
Space pressure
occurs.
Warning is issued
to user.
RMAN updates
1
list of files that
2
may be deleted. Backup files
to be deleted
Oracle deletes
files that are no
longer required
on disk.
Suggested Strategy
Flashback Error Correction
Database
•
Customer
•
Order
•
Order Line
•
•
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
Solution: Self-Managing Database
Enterprise
Manager
Database
Console
Application
and SQL
Management
Storage
Management
Monitor
Fix
Backup and
Recovery
Management
Alert
Automatic
Management
System
Resource
Management
Advise
Space
Management
Common
Infrastructure
What Is Automatic Storage Management
Database
Tablespace
ASM solves
management problems
of Oracle databases.
Segment
Extent
ASM manages
Oracle files.
ASM does not
replace existing
concepts.
Hierarchy
ASM
disk group
Database
Tablespace
Segment
Extent
Oracle
block
ASM file
Data file
Or
File system
file or
raw device
ASM disk
Allocation unit
Physical
block
ASM Benefits
Reduces the cost
of managing storage
Reduces
administration
complexity
Supports
RAC
Improves
performance,
scalability,
and reliability
ASM Files
CREATE TABLESPACE sample DATAFILE '+dgroupA';
Database file
Automatic
ASM file
creation
1
2
3
4
1
2
3
4
ASM file automatically spread inside disk group dgroupA
SQL Statements Issued to ASM Instances
CREATE DISKGROUP
ALTER SYSTEM
RESTRICTED SESSION
ASM Instance
ALTER DISKGROUP
DROP DISKGROUP
Enterprise Manager and ASM
D E M O N S T R A T I O N
Using Automatic
Storage
Management
How Oracle Database 10 g DBAs
Spend Their Time
Proactive and
Strategic
Planning 50+%
Install 3%
Maintain
Software 3%
Manage Database
System 25%
Create and
Configure 6%
Load Data 3%
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.
QUESTIONS
ANSWERS
Reminder –
please complete the OracleWorld
online session survey
Thank you.