Oracle Database 11g Release 1 For DBAs

1
Oracle Database 11g Release 1For DBAs

• Thomas Kyte
• http//asktom.oracle.com

2
The Beginning...

• Data Model with Structure
• Data Independent of Code
• Set-oriented
• 1977 the work begins

A Relational Model forLarge Shared Databanks
E.F. Codd - 1970
3
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

4
(No Transcript)
5
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

6
(No Transcript)
7
(No Transcript)
8
(No Transcript)
9
Encrypted Tablespaces
10
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
11
Standby Just got better
12
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

13
Standby Database

• Logical Standby has
• XMLType support
• DBMS_RLS DBMS_FGA support
• TDE support

14
Active Data Guard Develop Test on Standby DB
Production Database

• Use physical standby databasefor development
  testing
• Preserves zero data loss in test/dev mode
• Flashback DB to back-outchanges use as standby

Developers,Testers
Eliminates cost of idle DR system
Standby Database
15
Active Data Guard Report Backup from Standby
DB
Production Database

• Offload reporting to standby
• Simultaneously available for recovery
• Offload backups to standby
• Complete database and fast incremental backups

Reporting
Standby Database
Improves performance on production database
Backups
16
Real Application Testing
17
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

Test (RAC) Environment
MiddleTier
Production Environment
Capture DB Workload
OracleDB servers
Replay DBWorkload
Storage
18
Smaller more secure DMP files
19
Datapump

• COMPRESSION
• ALL, DATA_ONLY, METADATA_ONLY, NONE

• REUSE_DUMPFILES
• Ability to use DML error logging like features
• DATA_OPTIONS parameter

• ENCRYPTION
• All
• Data_only
• Metadata_only
• None
• Encrypted_columns_only
• PARTITION_OPTIONS
• Impdp
• None
• Departition
• Merge

Demo dp.sql
20
Virtual Columns
21
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
22
Partitioning just got better
23
Partitioning

• Composite Completely
• Virtual Column Partitioning
• Partition by Reference
• Interval Partitioning

24
Enhanced Partitioning

• Partition on virtual (computed) columns
• New composite partitioning

Range List Hash
Range 9i 8i
List
Range List Hash
Range 11g 9i 8i
List 11g 11g 11g
25
Partitioningby REFERENCE
Table ORDERS

• RANGE(order_date)
• Primary key order_id

...
...
Jan 2006
Feb 2006

• Partitioning key inherited through PK-FK
  relationship
• Avoids redundant storage, maintenance of
  order_date

Table LINEITEMS

• RANGE(order_date)
• Foreign key order_id

...
...
Demo part1.sql
Jan 2006
Feb 2006
26
PartitioningAutomation

• New INTERVAL partitioning
• Automatically creates a new partition when data
  outside the existing range is first inserted
• E.g., monthly partitions, automatic new partition
  first day of the month
• Composite partitioning interval, interval-list,
  interval-hash, and interval-range
• Automates partition management

ORDERS
Jul
Aug
Sep 1 2007
Demo part2.sql
27
Flashback Data Archive
28
Flashback Data ArchiveTotal Data Recall
Select from orders AS OF Midnight 31-Dec-2004

• 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

Archive Tables
Changes
User Tablespaces
Flashback Data Archive
Oracle 11g Database
29
Finer Grained Dependency Tracking
30
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

31
Cache More Stuff
32
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

33
Single Memory Setting
34
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

35
New Diagnostics Area
Demo diag.sql
36
Invisible Indexes
37
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

38
Database Resident Connection Pooling
39
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) well be able to
  have 50 app servers share 50 dedicated servers
• PHP right now, others later.

40
DDL that waits
41
Blocking DDL

• Sometimes, it mattered not how many times you hit
  the / key it was never going to happen

Demo wait.sql
42
Alter table T read only
43
True ONLINE index build
44
Disabled Triggers
45
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.

46
Data Recovery Advisor
47
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

48
ltInsert Picture Heregt
49
(No Transcript)
50
(No Transcript)