Transcript 11g Manageability New Features

<Insert Picture Here>
Oracle Database 11g Release 1
For DBAs
Thomas Kyte
http://asktom.oracle.com
The Beginning...
•
•
•
•
Data Model with Structure
Data Independent of Code
Set-oriented
1977 the work begins
First RDBMS: Version 2
June 1979
• FIRST Commercial SQL RDBMS
• Impressive First SQL
• Joins, Subqueries
• Outer Joins, Connect By
• A Simple Server
• No transactions, ‘Limited’ Reliability
• Portability from the Start
• Written in Fortran
• But multi-platform – PDP11, Dec VAX
Oracle7.3
February 1996
• Partitioned Views
• Bitmapped Indexes
• Asynchronous read ahead for
table scans
• Standby Database
• Deferred transaction recovery
on instance startup
• Updatable Join View
• SQLDBA no longer shipped.
• Index rebuilds
• DBV introduced
• Context Option
• PL/SQL - UTL_FILE
 Spatial Data Option
 Tablespaces changes - Coalesce,
Temporary Permanent,
 Trigger compilation, debug
 Unlimited extents on STORAGE
clause.
 Some init.ora parameters
modifiable - TIMED_STATISTICS
 HASH Joins, Antijoins
 Histograms
 Oracle Trace
 Advanced Replication Object
Groups
Encrypted
Tablespaces
Encrypted Tablespaces
• Oracle Database 10g Release 2 introduced column
encryption
• Could not range scan
• Primary/foreign key issues
• Tablespace encryption Removes those limitations
• Many encryption algorithms
•
•
•
•
3DES168
AES128
AES192
AES256
Demo: encrypt.sql
Standby Just
got better
Standby Database
• Logical Standby was…
• Limited in type support
• But was always open for business
• Physical Standby was…
• Easy
• But considered “not useful day to day”
Standby Database
• Logical Standby has…
• XMLType support
• DBMS_RLS & DBMS_FGA support
• TDE support
Active Data Guard:
Develop & Test on Standby DB
Production
Database
• Use physical standby database
for development & testing
Developers,
Testers
Standby
Database
• Preserves zero data loss in test/dev mode
• Flashback DB to back-out
changes & use as standby
Eliminates cost of
idle DR system
Active Data Guard:
Report & Backup from Standby DB
Production
Database
• Offload reporting to standby
• Simultaneously available for recovery
Reporting
• Offload backups to standby
Standby
Database
Backups
• Complete database and fast incremental
backups
Improves performance on
production database
Real
Application
Testing
Real Application Testing –
Database Replay
• Recreate actual production database workload
• Capture production workload incl. concurrency
• Replay workload in test with production timing
• Analyze & fix issues before production
Middle
Tier
Oracle
DB servers
Production
Environment
Capture DB
Workload
Storage
Test (RAC)
Environment`
Replay DB
Workload
Smaller more
secure DMP
files
Datapump
• COMPRESSION
• ALL, DATA_ONLY, METADATA_ONLY, NONE
• ENCRYPTION
•
•
•
•
•
All
Data_only
Metadata_only
None
Encrypted_columns_only
• REUSE_DUMPFILES
• Ability to use DML error
logging like features
• DATA_OPTIONS parameter
• PARTITION_OPTIONS
• Impdp
• None
• Departition
• Merge
Demo: dp.sql
Virtual
Columns
Virtual Columns
• Create Table
• Alter Table Add Column
• Are ‘column expressions’
• Expressions involving other columns in table
• Constants
• Deterministic functions
• Ease of use and Optimizer enhancement
Demo: vc.sql
Partitioning
just got better
Partitioning
• Composite Completely
• Virtual Column Partitioning
• Partition by Reference
• Interval Partitioning
Enhanced Partitioning
• Partition on virtual (computed) columns
• New composite partitioning
Range
List
Hash
Range
11g
9i
8i
List
11g
11g
11g
RANGE-RANGE
LIST-RANGE
LIST-LIST
Order Date by
Order Value
Region by
Order Value
Region by
Customer Type
Partitioning
by REFERENCE
Table ORDERS
...
Jan 2006
Feb 2006
...
• RANGE(order_date)
• Primary key order_id
• Partitioning key inherited
through PK-FK relationship
• Avoids redundant storage,
maintenance of order_date
Table LINEITEMS
...
Jan 2006
Feb 2006
• RANGE(order_date)
... • Foreign key order_id
Demo: part1.sql
Partitioning
Automation
New “INTERVAL” partitioning
• Automatically creates a new
partition when data outside the
existing range is first inserted
ORDERS
• E.g., monthly partitions, automatic
new partition first day of the month
Jul
Aug
Sep 1 2007
Sep
• Composite partitioning:
interval, interval-list, intervalhash, and interval-range
• Automates partition
management
Demo: part2.sql
Flashback Data
Archive
Flashback Data Archive
Total Data Recall
Select * from orders
AS OF ‘Midnight 31-Dec-2004’
Archive
Tables
User
Tablespaces
Flashback
Data Archive
Oracle 11g Database
• Tamper-proof data
archive
• Efficient storage and
retrieval of undo
• Keep data for months,
years, decades!
• Fast access to even very
old data
• View data, versions of
rows as of any time
• Control data retention
time, purging of data
Finer Grained
Dependency
Tracking
Finer Grained Dependency Tracking
• Fewer Invalidations
• Add to a specification – so what
• Add/Drop/Modify a column – so what
• Holds true with view modifications too
• Change a synonym pointer – so what
• Replace a procedure – so what
Cache More
Stuff
Cache More
• Everyone knows the fastest way to do
something is – to not do it
• Client Side Cache
• Server Results Cache (JIT-MV)
• PL/SQL Function results cache
Single Memory
Setting
Single Memory Parameter
• Well, Two
• MEMORY_TARGET
• MEMORY_MAX_TARGET
• Sizes PGA and SGA
• Not every platform:
• Linux, Solaris, Windows, HP-UX, AIX
• As with automatic SGA memory
management in 10g – you can set lower
bounds for various segments
New Diagnostics
Area
Demo: diag.sql
Invisible Indexes
Invisible Indexes
• Can create them that way
• Can alter them to be that way
• Why?
• Testing of course
• How will plans change if we add this index
• How will plans change if we remove this
index
• OPTIMIZER_USE_INVISIBLE_INDEXES
Database
Resident
Connection
Pooling
DRCP
• Anyone out there remember prespawned
servers?
• This is similar.
• Pool of dedicated servers that can be reused
• Instead of 50 app servers pooling 50
connections each (250 dedicated servers) –
we’ll be able to have 50 app servers share 50
dedicated servers
• PHP right now, others later.
DDL that waits
Blocking DDL
• Sometimes, it mattered not how many
times you hit the / key – it was never
going to happen
Demo: wait.sql
Alter table T read
only
True ONLINE
index build
Disabled Triggers
Disabled Triggers
create or replace trigger Trg
before insert on My_Table for each row
disable
begin
:New.ID := My_Seq.Nextvak;
end;
/
• Safer way to install code
• Same thoughts as invisible indexes.
Data Recovery
Advisor
Data Recovery Advisor
• Analyzes failures based on symptoms
• e.g. “Open failed” because datafiles missing
• Intelligently determines repair
strategies
• Aggregates failures for efficient repair
• e.g. for many bad blocks restore entire
file
• Presents only feasible repair options
• Are there backups?
• Is there a standby database?
• Ranked by repair time and data loss
• Can automatically perform repairs
<Insert Picture Here>
Q&A