introduction to oracle database 11g – the innovation continues pat shuff solutions architect...
TRANSCRIPT
![Page 1: Introduction to Oracle Database 11g – The Innovation Continues Pat Shuff Solutions Architect Oracle Corporation BROUG May 17, 2007](https://reader037.vdocuments.us/reader037/viewer/2022102808/56649de95503460f94ae43b5/html5/thumbnails/1.jpg)
<Insert Picture Here>
Introduction to Oracle Database 11g –The Innovation ContinuesPat ShuffSolutions ArchitectOracle Corporation
BROUGMay 17, 2007
![Page 2: Introduction to Oracle Database 11g – The Innovation Continues Pat Shuff Solutions Architect Oracle Corporation BROUG May 17, 2007](https://reader037.vdocuments.us/reader037/viewer/2022102808/56649de95503460f94ae43b5/html5/thumbnails/2.jpg)
Sample Oracle Innovations
• Automatic Storage Management
• Flashback technologies
• XMLDB
• Application Express
• Real Application Clusters
![Page 3: Introduction to Oracle Database 11g – The Innovation Continues Pat Shuff Solutions Architect Oracle Corporation BROUG May 17, 2007](https://reader037.vdocuments.us/reader037/viewer/2022102808/56649de95503460f94ae43b5/html5/thumbnails/3.jpg)
Oracle Database Innovation
Audit Vault Database Vault Grid Computing Self Managing Database XML Database Oracle Data Guard Real Application Clusters Flashback Query Virtual Private Database Built in Java VM Partitioning Support Built in Messaging Object Relational Support Multimedia Support Data Warehousing Optimizations Parallel Operations Distributed SQL & Transaction Support Cluster and MPP Support Multi-version Read Consistency Client/Server Support Platform Portability Commercial SQL Implementation
1977 2007
30 years of sustained innovation …
… continuing with Oracle Database 11g
![Page 4: Introduction to Oracle Database 11g – The Innovation Continues Pat Shuff Solutions Architect Oracle Corporation BROUG May 17, 2007](https://reader037.vdocuments.us/reader037/viewer/2022102808/56649de95503460f94ae43b5/html5/thumbnails/4.jpg)
Oracle’s Mission
Deliver the best informationwith the highest Quality of Service at the lowest cost
![Page 5: Introduction to Oracle Database 11g – The Innovation Continues Pat Shuff Solutions Architect Oracle Corporation BROUG May 17, 2007](https://reader037.vdocuments.us/reader037/viewer/2022102808/56649de95503460f94ae43b5/html5/thumbnails/5.jpg)
Oracle’s Mission
Deliver the best informationwith the highest Quality of Service
at the lowest cost
Oracle Database must be the fastest, most scaleable, most
reliable,most secure, easiest to use,for all types of information…
![Page 6: Introduction to Oracle Database 11g – The Innovation Continues Pat Shuff Solutions Architect Oracle Corporation BROUG May 17, 2007](https://reader037.vdocuments.us/reader037/viewer/2022102808/56649de95503460f94ae43b5/html5/thumbnails/6.jpg)
![Page 7: Introduction to Oracle Database 11g – The Innovation Continues Pat Shuff Solutions Architect Oracle Corporation BROUG May 17, 2007](https://reader037.vdocuments.us/reader037/viewer/2022102808/56649de95503460f94ae43b5/html5/thumbnails/7.jpg)
The following is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a
commitment to deliver any material, code, or functionality, and should not be relied upon
in making purchasing decisions.The development, release, and timing of any
features or functionality described for Oracle’s products remains at the sole
discretion of Oracle.
![Page 8: Introduction to Oracle Database 11g – The Innovation Continues Pat Shuff Solutions Architect Oracle Corporation BROUG May 17, 2007](https://reader037.vdocuments.us/reader037/viewer/2022102808/56649de95503460f94ae43b5/html5/thumbnails/8.jpg)
Managing Storage and Data
Managing & Using Information
Maintaining Availability, Security and Performance
Managing Systems And Managing Change
![Page 9: Introduction to Oracle Database 11g – The Innovation Continues Pat Shuff Solutions Architect Oracle Corporation BROUG May 17, 2007](https://reader037.vdocuments.us/reader037/viewer/2022102808/56649de95503460f94ae43b5/html5/thumbnails/9.jpg)
ManagingStorage
andData
![Page 10: Introduction to Oracle Database 11g – The Innovation Continues Pat Shuff Solutions Architect Oracle Corporation BROUG May 17, 2007](https://reader037.vdocuments.us/reader037/viewer/2022102808/56649de95503460f94ae43b5/html5/thumbnails/10.jpg)
Automatic Storage Management
• The preferred and best storage manager for Oracle Databases
• Easier to manage than file systems• Performance of raw volumes• Built-in to Oracle database • Shared storage pool for all databases
• Free, and widely adopted• >65% of 10g RAC deployments on ASM
• >25% of 10g customers already using ASM
• Many VLDB over 10TB
ASM DiskASM Disk
ASM DiskASM Disk
ASM Disk
![Page 11: Introduction to Oracle Database 11g – The Innovation Continues Pat Shuff Solutions Architect Oracle Corporation BROUG May 17, 2007](https://reader037.vdocuments.us/reader037/viewer/2022102808/56649de95503460f94ae43b5/html5/thumbnails/11.jpg)
Automatic Storage Management
• Spreads database files evenly across storage arrays
• Storage arrays can be easily added or remove• transparent data redistribution
• Data mirrored across arrays• Tolerates failure of disks or arrays
New ASM features in Oracle 11g:• ASM Fast Disk Resync• ASM Preferred Mirror Read• ASM Rolling Upgrade• Larger extent, allocation unit sizes
ASM DiskASM Disk
ASM DiskASM Disk
ASM Disk
![Page 12: Introduction to Oracle Database 11g – The Innovation Continues Pat Shuff Solutions Architect Oracle Corporation BROUG May 17, 2007](https://reader037.vdocuments.us/reader037/viewer/2022102808/56649de95503460f94ae43b5/html5/thumbnails/12.jpg)
• Oracle 9i compresses data only during bulk load; useful for DW and ILM
• Oracle 11g compresses w/ inserts, updates• Typical compression ratio of 2x to 3x• Database directly reads compressed data
eliminating decompression overhead• Strategy: compress db’s 10 largest tables
• Shrink table data by 50%, increase CPU by 5%
• Savings cascade to all db copies: test, dev, standby, mirrors, archiving, backup, etc.
Data Compressionfor All Applications
![Page 13: Introduction to Oracle Database 11g – The Innovation Continues Pat Shuff Solutions Architect Oracle Corporation BROUG May 17, 2007](https://reader037.vdocuments.us/reader037/viewer/2022102808/56649de95503460f94ae43b5/html5/thumbnails/13.jpg)
Oracle SecureFilesHigh-Performance Large Objects
• High-performance transactionalaccess to large object data• documents, medical, CAD, imaging …• low-latency, high throughput, concurrent access• space-optimized storage
• Protect your valuable data .. in the db!• transactions• transparent encryption• compression and de-duplication• database-quality security, reliability, and scalability
• Better security, single view and management of data• Superset of LOB interfaces – easy migration
![Page 14: Introduction to Oracle Database 11g – The Innovation Continues Pat Shuff Solutions Architect Oracle Corporation BROUG May 17, 2007](https://reader037.vdocuments.us/reader037/viewer/2022102808/56649de95503460f94ae43b5/html5/thumbnails/14.jpg)
020406080
100120
0.1 1 10 100
LOBs
Linux Files
SecureFiles
• Innovative technology for high performance large object data•Smart buffering, write gathering, intelligent locking
•Fast bulk data transfers, LOB prefetch
• Much faster than LOBs with more capabilities
• File system-like performance with database functionality!
File Size (MB)
SecureFiles Breaks thePerformance Barrier!
File Read Performance(MB/second)
![Page 15: Introduction to Oracle Database 11g – The Innovation Continues Pat Shuff Solutions Architect Oracle Corporation BROUG May 17, 2007](https://reader037.vdocuments.us/reader037/viewer/2022102808/56649de95503460f94ae43b5/html5/thumbnails/15.jpg)
DatabaseEncryption
• Encrypt at a column level (10g)
• New: encrypt entire tablespaces
• Redo, undo, backups also encrypted
• Key management done by database
• Transparent to applications
![Page 16: Introduction to Oracle Database 11g – The Innovation Continues Pat Shuff Solutions Architect Oracle Corporation BROUG May 17, 2007](https://reader037.vdocuments.us/reader037/viewer/2022102808/56649de95503460f94ae43b5/html5/thumbnails/16.jpg)
Growing Data Volumes
Source: 2005 TopTen Program, November 2005 © Winter Corporation, Waltham, MA, USA
0
20
40
60
80
100
1998 1999 2000 2001 2002 2003 2004 2005
DatabaseSize(TB)
Size of the largest data warehouse in
Winter Corp Survey245% increase
from 2003to 2005!
![Page 17: Introduction to Oracle Database 11g – The Innovation Continues Pat Shuff Solutions Architect Oracle Corporation BROUG May 17, 2007](https://reader037.vdocuments.us/reader037/viewer/2022102808/56649de95503460f94ae43b5/html5/thumbnails/17.jpg)
Information Lifecycle ManagementOptimize storage cost and performance
• Use Flashback Data Archive for long-term storage of “old” data
• Use table, index partitioning to separate data into different tiers
• Use new ILM assistant to establish policies, create scripts
DIGITAL DATA STORAGE
High Performance Storage Tier
Low CostStorage Tier
Online ArchiveStorage Tier
Active Less Active Historical Archive
Offline ArchiveStorage Tier
![Page 18: Introduction to Oracle Database 11g – The Innovation Continues Pat Shuff Solutions Architect Oracle Corporation BROUG May 17, 2007](https://reader037.vdocuments.us/reader037/viewer/2022102808/56649de95503460f94ae43b5/html5/thumbnails/18.jpg)
Flashback Data ArchiveTotal Data Recall
• 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
User Tablespaces
Flashback Data Archive
Oracle 11g Database
Select * from orders AS OF ‘Midnight 31-Dec-2004’
Changes ArchiveArchiveTablesTables
![Page 19: Introduction to Oracle Database 11g – The Innovation Continues Pat Shuff Solutions Architect Oracle Corporation BROUG May 17, 2007](https://reader037.vdocuments.us/reader037/viewer/2022102808/56649de95503460f94ae43b5/html5/thumbnails/19.jpg)
Flashback Data ArchiveTotal Data Recall
• Access Historical Data –”AS OF”
• Generate Reports –“ROW VERSIONS”
• Information Lifecycle Management (ILM)
• Auditing• Data Recovery• Enforce Data Retention
Policies
User Tablespaces
Flashback Data Archive
Oracle 11g Database
Select * from orders AS OF ‘Midnight 31-Dec-2004’
Changes ArchiveArchiveTablesTables
![Page 20: Introduction to Oracle Database 11g – The Innovation Continues Pat Shuff Solutions Architect Oracle Corporation BROUG May 17, 2007](https://reader037.vdocuments.us/reader037/viewer/2022102808/56649de95503460f94ae43b5/html5/thumbnails/20.jpg)
Oracle Partitioning10 years of innovation
Core functionality
Oracle8 Range partitions, global range index
Oracle8i Hash and composite range-hash partitioning
Oracle9i List partitioning
Oracle9i R2 Composite range-list partitioning
Oracle 10g Global hash indexes
Oracle 10g R2 1M partitions per table
![Page 21: Introduction to Oracle Database 11g – The Innovation Continues Pat Shuff Solutions Architect Oracle Corporation BROUG May 17, 2007](https://reader037.vdocuments.us/reader037/viewer/2022102808/56649de95503460f94ae43b5/html5/thumbnails/21.jpg)
Oracle Partitioning10 years of innovation
Core functionality
Oracle8 Range partitions, global range index
Oracle8i Hash and composite range-hash partitioning
Oracle9i List partitioning
Oracle9i R2 Composite range-list partitioning
Oracle 10g Global hash indexes
Oracle 10g R2 1M partitions per table
Partitioning by referenceVirtual column partitioningNew composite partitioning: range-range, list-range, list-list, list-hash
![Page 22: Introduction to Oracle Database 11g – The Innovation Continues Pat Shuff Solutions Architect Oracle Corporation BROUG May 17, 2007](https://reader037.vdocuments.us/reader037/viewer/2022102808/56649de95503460f94ae43b5/html5/thumbnails/22.jpg)
New PartitioningFeatures
• New composite partitioning schemes
• Partition (or index) on virtual (computed) columns • Partition advisor• Automatic range partition creation• Partition by REFERENCE (primary key of parent)
Range List Hash
Range 11g 9i 8i
List 11g 11g 11g
![Page 23: Introduction to Oracle Database 11g – The Innovation Continues Pat Shuff Solutions Architect Oracle Corporation BROUG May 17, 2007](https://reader037.vdocuments.us/reader037/viewer/2022102808/56649de95503460f94ae43b5/html5/thumbnails/23.jpg)
Partitioningby REFERENCE
Table ORDERS
Jan 2006
... ...
Feb 2006
Table LINEITEMS
Jan 2006
... ...
Feb 2006
• RANGE(order_date)• Primary key order_id
• RANGE(order_date)• Foreign key order_id
• Partitioning key inherited through PK-FK relationship
• Avoids redundant storage, maintenance of order_date
![Page 24: Introduction to Oracle Database 11g – The Innovation Continues Pat Shuff Solutions Architect Oracle Corporation BROUG May 17, 2007](https://reader037.vdocuments.us/reader037/viewer/2022102808/56649de95503460f94ae43b5/html5/thumbnails/24.jpg)
Well-tunedSQL &
Schema
Partitioning Advisor
• Considers entire query workload to improve query performance
• Advises on partitioning methods • Range (equal-interval), range
key and interval• Hash, hash key
• Integrated, non-conflicting advice with Indexes, MVs
SQL Workload
PackagedApps
Custom Apps
SQL Advisor
SQL PlanTuning
SQLStructureAnalysis
AccessAnalysis
SQLProfile
SQLAdvice
Indexes& MVs
PartitionAnalysis
Partition Advice
New!
![Page 25: Introduction to Oracle Database 11g – The Innovation Continues Pat Shuff Solutions Architect Oracle Corporation BROUG May 17, 2007](https://reader037.vdocuments.us/reader037/viewer/2022102808/56649de95503460f94ae43b5/html5/thumbnails/25.jpg)
Oracle ILM Assistant
• Manages your ILM environment via a GUI interface• Define lifecycle definitions• Manage security & compliance
• Advises when data needs to be moved, generates scripts• Requires Oracle Application Express• Supports Oracle Database 9i and up• Tool downloaded from OTN (available now!)
![Page 26: Introduction to Oracle Database 11g – The Innovation Continues Pat Shuff Solutions Architect Oracle Corporation BROUG May 17, 2007](https://reader037.vdocuments.us/reader037/viewer/2022102808/56649de95503460f94ae43b5/html5/thumbnails/26.jpg)
• Oracle storage suite built-out over last decade
• Each component continuously enhanced to add more value
• Best of breed in each area
8.0 8i 9i 9.2 10g 10.2 11g
• RMAN• Data Guard
• Flashback• XML DB, ILM, Compression
• ASM, RMAN Disk Backup
• Oracle Secure Backup, Encryption
• Secure Files
Oracle’s Storage Strategy –Sustained Innovation
![Page 27: Introduction to Oracle Database 11g – The Innovation Continues Pat Shuff Solutions Architect Oracle Corporation BROUG May 17, 2007](https://reader037.vdocuments.us/reader037/viewer/2022102808/56649de95503460f94ae43b5/html5/thumbnails/27.jpg)
Managingand
UsingInformation
![Page 28: Introduction to Oracle Database 11g – The Innovation Continues Pat Shuff Solutions Architect Oracle Corporation BROUG May 17, 2007](https://reader037.vdocuments.us/reader037/viewer/2022102808/56649de95503460f94ae43b5/html5/thumbnails/28.jpg)
Managing All Your Information
• Oracle Strategy: evolve the Oracle database to manage all enterprise Information• Meld db and file metaphors• Enable integration of all enterprise
information sources• Enable rich information retrieval
capabilities• Provide solutions built on top of
the database
• Uniform management of content and metadata• Scalable, secure, highly available,
integrated, robust, available on all platforms
MultimediaMultimedia management
Location & SpatialLocation and Proximity Searching
Secure Enterprise SearchEnterprise Search Solution
XML DBIntegrated Native XML Database
Text & OntologyText and Semantic Processing
RelationalCharacters, Numbers, Dates, LOBs
Content DBEnterprise Content Management
![Page 29: Introduction to Oracle Database 11g – The Innovation Continues Pat Shuff Solutions Architect Oracle Corporation BROUG May 17, 2007](https://reader037.vdocuments.us/reader037/viewer/2022102808/56649de95503460f94ae43b5/html5/thumbnails/29.jpg)
Oracle XML Support
• SQL access to XML content and XML access to relational content
• Flexible native XML storage delivers optimized application performance
• Repository integration enables document centric access, security and integrity and development
• Full support for key XML standards including the W3C XQuery recommendation and SQL/XML
![Page 30: Introduction to Oracle Database 11g – The Innovation Continues Pat Shuff Solutions Architect Oracle Corporation BROUG May 17, 2007](https://reader037.vdocuments.us/reader037/viewer/2022102808/56649de95503460f94ae43b5/html5/thumbnails/30.jpg)
Extending Oracle’sXML Lead
Comprehensive XML storage and indexingand efficient end-to-end XML processing
• Binary XML - Compact and efficient storage representation• Complements existing object storage and text storage models• XMLtype storage format transparent to developer• Single, compact XML representation across client, mid-tier, db• Plan to place our Binary XML format into open source
• XML Path Indexing for schema-less XML documents
Performance
![Page 31: Introduction to Oracle Database 11g – The Innovation Continues Pat Shuff Solutions Architect Oracle Corporation BROUG May 17, 2007](https://reader037.vdocuments.us/reader037/viewer/2022102808/56649de95503460f94ae43b5/html5/thumbnails/31.jpg)
Extending Oracle’sXML Lead
Performance improvements in many areas
• Recursive schema handling• Scalable XSL output• XQuery and SQL/XML query optimization with
schema-less XML• XML update optimizations• Asynchronous XML index updates
Performance
![Page 32: Introduction to Oracle Database 11g – The Innovation Continues Pat Shuff Solutions Architect Oracle Corporation BROUG May 17, 2007](https://reader037.vdocuments.us/reader037/viewer/2022102808/56649de95503460f94ae43b5/html5/thumbnails/32.jpg)
Extending Oracle’sXML Lead
Enhanced productivity and flexibility
• Standards Support• XQuery 1.0 support• XDB as HTTP Server – expose PL/SQL as Web Services• Content Repository API for Java (JSR 170) support• Support for JCR 1.0, SQL:2007• XLink/Xinclude support
• XMLDB Repository Triggers
XML application development
![Page 33: Introduction to Oracle Database 11g – The Innovation Continues Pat Shuff Solutions Architect Oracle Corporation BROUG May 17, 2007](https://reader037.vdocuments.us/reader037/viewer/2022102808/56649de95503460f94ae43b5/html5/thumbnails/33.jpg)
Extending Oracle’sXML Lead
Making XMLDB Mainstream
• XML type support in streams & logical standby
• In-place XML schema evolution
• XML language translation support
• Asynchronous XMLindex updates
• Large text node handling
Operational completeness
![Page 34: Introduction to Oracle Database 11g – The Innovation Continues Pat Shuff Solutions Architect Oracle Corporation BROUG May 17, 2007](https://reader037.vdocuments.us/reader037/viewer/2022102808/56649de95503460f94ae43b5/html5/thumbnails/34.jpg)
Oracle XMLDB –Sustained Innovation
1998 2001 2004 2007
Pe
rfo
rma
nc
e
XMLXMLStorage &Storage &RepositoryRepositoryXMLXML
API’sAPI’s
XQueryXQuery
Binary XMLBinary XMLStorageStorage
& Indexing& Indexing
![Page 35: Introduction to Oracle Database 11g – The Innovation Continues Pat Shuff Solutions Architect Oracle Corporation BROUG May 17, 2007](https://reader037.vdocuments.us/reader037/viewer/2022102808/56649de95503460f94ae43b5/html5/thumbnails/35.jpg)
Multimedia and Spatial Enhancements
• New spatial features• 3-D geometry, surface, and point cloud
storage and indexing • Spatial web services• Spatial routing engine enhancements• Scalability, manageability, reliability,
usability enhancements
• Multimedia and medical imaging • 3X performance improvement for
common image processing operations• Large media handling (up to 128 TB)• DICOM medical imaging support • Java Advanced Imaging (JAI) support
![Page 36: Introduction to Oracle Database 11g – The Innovation Continues Pat Shuff Solutions Architect Oracle Corporation BROUG May 17, 2007](https://reader037.vdocuments.us/reader037/viewer/2022102808/56649de95503460f94ae43b5/html5/thumbnails/36.jpg)
Text andSemantic Technology
Oracle Text• Enterprise Manager support• Incremental, online indexing• Web services• Composite indexes speed text,
relational queries• User-defined relevance ranking• More advanced new multilingual
and linguistic support, including Arabic and Hebrew
Semantic Technology• Query using concepts and terms
related to a keyword • applications in life sciences,
defense, health care, enterprise information integration
• New in 11g:• Improved query/optimizer
support• Improved bulk loading• native, lightweight OWL
inferencing
![Page 37: Introduction to Oracle Database 11g – The Innovation Continues Pat Shuff Solutions Architect Oracle Corporation BROUG May 17, 2007](https://reader037.vdocuments.us/reader037/viewer/2022102808/56649de95503460f94ae43b5/html5/thumbnails/37.jpg)
• Detail data is stored in tables• Applications use SQL queries• Summary data is managed in
cubes (like materialized view)• Databases manages cube refresh
as data changes• Fast, incremental• Cost-based aggregation
• SQL queries automatically re-written to access the cube
• Application is unchanged, but updates and queries are faster
SQL QuerySQL Query
Fast OLAP via SQLCubes as Materialized Views
![Page 38: Introduction to Oracle Database 11g – The Innovation Continues Pat Shuff Solutions Architect Oracle Corporation BROUG May 17, 2007](https://reader037.vdocuments.us/reader037/viewer/2022102808/56649de95503460f94ae43b5/html5/thumbnails/38.jpg)
• Any SQL application can leverage OLAP Option performance
• Cube technology is optimized for business intelligence• Excellent performance for ad-hoc
query loads• Highly optimized incremental refresh
and aggregation• Content rich calculations are easily
queried with SQL
• Seamless fit with warehouse administration process
SQL QuerySQL Query
OLAP for EveryData Warehouse
![Page 39: Introduction to Oracle Database 11g – The Innovation Continues Pat Shuff Solutions Architect Oracle Corporation BROUG May 17, 2007](https://reader037.vdocuments.us/reader037/viewer/2022102808/56649de95503460f94ae43b5/html5/thumbnails/39.jpg)
Oracle Record of OLAP Innovation
SQL Star Queries
Cubes but noSQL access
SQLViews over
Cubes
TransparentSQL Rewrite
to Cubes
![Page 40: Introduction to Oracle Database 11g – The Innovation Continues Pat Shuff Solutions Architect Oracle Corporation BROUG May 17, 2007](https://reader037.vdocuments.us/reader037/viewer/2022102808/56649de95503460f94ae43b5/html5/thumbnails/40.jpg)
Oracle Data MiningEnhanced, Automated, Simplified
• Enhanced data mining in SQL• Eases application use of models
• Data Models as schema objects• simpler administration, security
• Java API standards compliance• Automated and embedded data
transformations• (aka “Supermodels”)• eases model building and scoring
• New predictive analytic data mining procedures • Regressions, segmentation, profiling,
groupingOn-the-fly Data Mining in SQL
Find the top 5 stores that are the furthest below forecast
SELECT store_id, loc, sales, forecast FROM
(SELECT * FROM
(SELECT s.*, PREDICTION (CREATE REGRESSION FOR sales USING *)OVER () as forecast FROM stores)
ORDER BY forecast-sales)
WHERE ROWNUM < 6;
![Page 41: Introduction to Oracle Database 11g – The Innovation Continues Pat Shuff Solutions Architect Oracle Corporation BROUG May 17, 2007](https://reader037.vdocuments.us/reader037/viewer/2022102808/56649de95503460f94ae43b5/html5/thumbnails/41.jpg)
Enhanced SQL –PIVOT and UNPIVOT
• Rotate rows into columns and vice versa• Create aggregated cross-tabular result set• Use to combine multiple rows to one, enabling inter-row computations
without self-join`
PROD QUARTER AMOUNT
Shoes Q1 2000
Shoes Q2 1000
Jeans Q1 1000
Jeans Q3 500
Jeans Q3 100
Jeans Q4 1000
PROD Q1 Q2 Q3 Q4
Shoes 2000 1000 Null Null
Jeans 1000 Null 600 1000
SELECT * FROM salesPIVOT (sum(amount) FOR quarter in ‘Q1’,’Q2’,’Q3’,’Q4’);
![Page 42: Introduction to Oracle Database 11g – The Innovation Continues Pat Shuff Solutions Architect Oracle Corporation BROUG May 17, 2007](https://reader037.vdocuments.us/reader037/viewer/2022102808/56649de95503460f94ae43b5/html5/thumbnails/42.jpg)
1977
Oracle8i Text Spatial Media
Oracle9i XML DB Repository SQL/XML
Oracle8 VLDB LOB’s Object-
relational Extensibility
Oracle 10g ULDB Location
Services XQuery
Oracle 11g Secure Files Ontology DICOM Secure Search Binary XML XML Index
1999
2001
2004
20072007
ALL Your Data –Sustained Innovation
![Page 43: Introduction to Oracle Database 11g – The Innovation Continues Pat Shuff Solutions Architect Oracle Corporation BROUG May 17, 2007](https://reader037.vdocuments.us/reader037/viewer/2022102808/56649de95503460f94ae43b5/html5/thumbnails/43.jpg)
Maintaining Availability,
Security and Performance
![Page 44: Introduction to Oracle Database 11g – The Innovation Continues Pat Shuff Solutions Architect Oracle Corporation BROUG May 17, 2007](https://reader037.vdocuments.us/reader037/viewer/2022102808/56649de95503460f94ae43b5/html5/thumbnails/44.jpg)
The Availability Challenge
System Changes
Data Changes
PlannedDowntime
Storage Failure
Human Error
Corruption
Site Failure
Server Failures
UnplannedDowntime Data
Failures
![Page 45: Introduction to Oracle Database 11g – The Innovation Continues Pat Shuff Solutions Architect Oracle Corporation BROUG May 17, 2007](https://reader037.vdocuments.us/reader037/viewer/2022102808/56649de95503460f94ae43b5/html5/thumbnails/45.jpg)
Server Scale-Out withReal Application Clusters
• Great scalability & availability• Pools standard low cost servers,
improves server utilization• Runs applications unchanged• 1000s of production customers
Designed to Tolerate Server Failures
Pool of database servers
Active/passiveclustered servers
New: 11g fine tunes performance, scaling,fail-over, management
![Page 46: Introduction to Oracle Database 11g – The Innovation Continues Pat Shuff Solutions Architect Oracle Corporation BROUG May 17, 2007](https://reader037.vdocuments.us/reader037/viewer/2022102808/56649de95503460f94ae43b5/html5/thumbnails/46.jpg)
Storage Scale-Out withAutomated Storage Management
• ASM pools storage from modular storage arrays
• Automatically remirrors when disk or array fails
Designed to Tolerate Storage Array Failure
Pool of Storagefor all databases
Dedicateddisks fordatabasestorage
New: 11g fine tunes recovery from corrupt blocks and crashed storage arrays
![Page 47: Introduction to Oracle Database 11g – The Innovation Continues Pat Shuff Solutions Architect Oracle Corporation BROUG May 17, 2007](https://reader037.vdocuments.us/reader037/viewer/2022102808/56649de95503460f94ae43b5/html5/thumbnails/47.jpg)
Error Investigation with Flashback
• Flashback Query• Query all data at point in time
Tx 1
Tx 2
Tx 3
select * from Emp AS OF ‘2:00 P.M.’where …
• Flashback Versions Query• See all versions of a row
between times• See transactions that changed
the row
select * from empVERSIONS BETWEEN‘2:00 PM’ and ‘3:00 PM’where …
![Page 48: Introduction to Oracle Database 11g – The Innovation Continues Pat Shuff Solutions Architect Oracle Corporation BROUG May 17, 2007](https://reader037.vdocuments.us/reader037/viewer/2022102808/56649de95503460f94ae43b5/html5/thumbnails/48.jpg)
Error Correction with FlashbackCorrect errors at any level
• Flashback Database – restore database to time
• Flashback Table - restore contents of tables to time
Database
Customer
Order
New: Flashback Transaction –back out transaction and all
subsequent conflicting transactions
![Page 49: Introduction to Oracle Database 11g – The Innovation Continues Pat Shuff Solutions Architect Oracle Corporation BROUG May 17, 2007](https://reader037.vdocuments.us/reader037/viewer/2022102808/56649de95503460f94ae43b5/html5/thumbnails/49.jpg)
Data Recovery Advisor
• Diagnoses persistent data failures
• Presents appropriate repair actions
• Intelligently determines plan for recovery, selecting repair option
• Data file restore/recovery, media recovery, Flashback database, etc.
• Validates plan w.r.t. availability of media components required
• Can automatically apply planTime to Repair
Recovery
Investigation and Planning
Reduces Downtime by Eliminating Confusion
Uncertainty and confusionduring an outage causes
delays and errors
![Page 50: Introduction to Oracle Database 11g – The Innovation Continues Pat Shuff Solutions Architect Oracle Corporation BROUG May 17, 2007](https://reader037.vdocuments.us/reader037/viewer/2022102808/56649de95503460f94ae43b5/html5/thumbnails/50.jpg)
Customers Don’t Benefitfrom Disaster Recovery
• Cost – choose no DR, or under-configure DR
• Rarely used – so little confidence in fail-over
• Data loss – leads to downstream problems
• Slow – faster to fix problems than fail-over
• Limited protection – site failures only
![Page 51: Introduction to Oracle Database 11g – The Innovation Continues Pat Shuff Solutions Architect Oracle Corporation BROUG May 17, 2007](https://reader037.vdocuments.us/reader037/viewer/2022102808/56649de95503460f94ae43b5/html5/thumbnails/51.jpg)
Oracle Data Guard –Practical Disaster Protection
• Synchronous or asynchronous log shipping• Corruptions don’t propagate• Configurable for zero data loss• Automatic fail over in seconds to standby (10.2)• Uses far less bandwidth than remote mirroring• Thousands of production customers
ProductionDatabase
StandbyDatabase
Log
Shipping
![Page 52: Introduction to Oracle Database 11g – The Innovation Continues Pat Shuff Solutions Architect Oracle Corporation BROUG May 17, 2007](https://reader037.vdocuments.us/reader037/viewer/2022102808/56649de95503460f94ae43b5/html5/thumbnails/52.jpg)
Snapshot Standby
• Use Standby Database for testing and development
• Eliminates cost of DR
• Preserves zero data loss while in test/dev mode
• But no real time query or fast failover
Physical Standby Apply Logs
Snapshot Standby Perform Testing
Continuous Redo Shipping
Open Database
Back out Changes
![Page 53: Introduction to Oracle Database 11g – The Innovation Continues Pat Shuff Solutions Architect Oracle Corporation BROUG May 17, 2007](https://reader037.vdocuments.us/reader037/viewer/2022102808/56649de95503460f94ae43b5/html5/thumbnails/53.jpg)
Real-Time Query withPhysical Standby
• Previously available with Logical Standby• Available with Physical Standby in 11.1• Handles all data types, very fast, simple operation• Eliminates cost of DR: all hardware used for production
Real-time Queries
ProductionDatabase
Logical orPhysicalStandbyDatabase
Continuous
Log Shipping
ContinuousReal-Time
Query
![Page 54: Introduction to Oracle Database 11g – The Innovation Continues Pat Shuff Solutions Architect Oracle Corporation BROUG May 17, 2007](https://reader037.vdocuments.us/reader037/viewer/2022102808/56649de95503460f94ae43b5/html5/thumbnails/54.jpg)
Benefits of Data Guardfor Disaster Recovery
• No cost – hardware and software
• Zero data loss - over long distances
• Fast Automatic Failover
• Covers all common failures – not just site failures
• Works transparently for existing applications
Bonus – Big Reduction in Planned Downtime
![Page 55: Introduction to Oracle Database 11g – The Innovation Continues Pat Shuff Solutions Architect Oracle Corporation BROUG May 17, 2007](https://reader037.vdocuments.us/reader037/viewer/2022102808/56649de95503460f94ae43b5/html5/thumbnails/55.jpg)
Reducing Planned Downtimefor System Changes
• Scaling Servers on Demand • Add RAC nodes online w/o data movement
• Scaling storage on demand• Add ASM disks online w/ auto data rebalancing
• Online patching• RAC rolling upgrades for complex patches, CPUs• Rolling upgrades w/ standby for patch sets, version
changes
Database
Storage
New: simple one-off patches can be applied to a running Oracle instance
![Page 56: Introduction to Oracle Database 11g – The Innovation Continues Pat Shuff Solutions Architect Oracle Corporation BROUG May 17, 2007](https://reader037.vdocuments.us/reader037/viewer/2022102808/56649de95503460f94ae43b5/html5/thumbnails/56.jpg)
Data Security: Oracle Products
IdentityManagement
• Oracle Identity Management
DataProtection
• Advanced Security• Secure Backup
AccessControl
• Database Vault• Label Security
Monitoring• Audit Vault• EM Configuration Pack
Core Platform Security
![Page 57: Introduction to Oracle Database 11g – The Innovation Continues Pat Shuff Solutions Architect Oracle Corporation BROUG May 17, 2007](https://reader037.vdocuments.us/reader037/viewer/2022102808/56649de95503460f94ae43b5/html5/thumbnails/57.jpg)
Oracle Advanced Security
Transparent Data Encryption
• Column level encryption (10gR2)
• Tablespace level encryption• For encrypting entire application data
• Supports foreign keys and range scan
• LOB encryption
• Master Key protection in hardware using PKCS #11
![Page 58: Introduction to Oracle Database 11g – The Innovation Continues Pat Shuff Solutions Architect Oracle Corporation BROUG May 17, 2007](https://reader037.vdocuments.us/reader037/viewer/2022102808/56649de95503460f94ae43b5/html5/thumbnails/58.jpg)
Oracle Database Vault
• Controls privileged users, enforces separation of duties• Administrators can’t access application data• Site-specific controls limit access by any user
Coming soon for Oracle9i, 10g, 11g
DBA
FIN DBA Fin Realm
HR DBA
Fin
HR Realm HR
HR DBA
CREATE …
Outside business hours
FIN user
SELECT …
Unexpected IP address
![Page 59: Introduction to Oracle Database 11g – The Innovation Continues Pat Shuff Solutions Architect Oracle Corporation BROUG May 17, 2007](https://reader037.vdocuments.us/reader037/viewer/2022102808/56649de95503460f94ae43b5/html5/thumbnails/59.jpg)
Oracle Audit Vault
9i, 10gR1,10gR2 ApplicationsNon
Oracle DB
OS
Secure AuditWarehouse
Monitor
Policies
Report/Alert
Console
Audit Collectors
App Server
Collector SDK
Enterprise-wideAudit Solution for
Compliance & Security
• Collects audit info from multiple sources
• SDK for customization
• Monitor security-sensitive activities
• Tamper-evident repository
• Alerting policies
Coming soon
![Page 60: Introduction to Oracle Database 11g – The Innovation Continues Pat Shuff Solutions Architect Oracle Corporation BROUG May 17, 2007](https://reader037.vdocuments.us/reader037/viewer/2022102808/56649de95503460f94ae43b5/html5/thumbnails/60.jpg)
Core DatabaseSecurity Enhancements
• Secure configuration by default
• Password management settings
• Audit sensitive administrative operations
• Stronger password verifier
• PKI / Kerberos authentication for super privileged DBAs
![Page 61: Introduction to Oracle Database 11g – The Innovation Continues Pat Shuff Solutions Architect Oracle Corporation BROUG May 17, 2007](https://reader037.vdocuments.us/reader037/viewer/2022102808/56649de95503460f94ae43b5/html5/thumbnails/61.jpg)
1977 2007
Oracle Audit Vault Oracle Database Vault
DB Security Evaluation #19 Transparent Data Encryption EM Configuration Scanning Fine Grained Auditing (9i) Secure application roles Client Identifier / Identity propagation Oracle Label Security (2000) Proxy authentication Enterprise User Security Global roles Virtual Private Database (8i)
Database Encryption API Strong authentication (PKI, Kerberos, RADIUS) Native Network Encryption (Oracle7) Database Auditing Government customer
Oracle Database SecuritySustained Innovation
![Page 62: Introduction to Oracle Database 11g – The Innovation Continues Pat Shuff Solutions Architect Oracle Corporation BROUG May 17, 2007](https://reader037.vdocuments.us/reader037/viewer/2022102808/56649de95503460f94ae43b5/html5/thumbnails/62.jpg)
Sustain Optimal Performance with Self-Managing Database
Sto
rag
e
Ba
cku
p
Me
mo
ry
Ap
ps
/SQ
L
Sc
he
ma
RA
C
Re
co
ve
ry
Re
plic
ati
on
Auto-TuningTuning
Advisory
InstrumentationLow Impact
Integrated
Adaptive
![Page 63: Introduction to Oracle Database 11g – The Innovation Continues Pat Shuff Solutions Architect Oracle Corporation BROUG May 17, 2007](https://reader037.vdocuments.us/reader037/viewer/2022102808/56649de95503460f94ae43b5/html5/thumbnails/63.jpg)
Sustain Optimal Performance with Self-Managing Database
Sto
rag
e
Ba
cku
p
M
em
ory
A
pp
s/S
QL
Sc
he
ma
R
AC
Re
co
ve
ry
Re
plic
ati
on
Auto-TuningTuning
Advisory
InstrumentationLow Impact
Integrated
Adaptive
![Page 64: Introduction to Oracle Database 11g – The Innovation Continues Pat Shuff Solutions Architect Oracle Corporation BROUG May 17, 2007](https://reader037.vdocuments.us/reader037/viewer/2022102808/56649de95503460f94ae43b5/html5/thumbnails/64.jpg)
Automatic SQL Tuning
• Captures high-load SQL
• Tunes SQL by creating SQL profiles
• Optionally implements greatly improved SQL plans
• Reports analysis
• Runs runs in maintenance window
Nightly
Well-tunedSQL
SQL Workload
PackagedApps
Custom Apps
Automatic SQL Tuning
SQLProfiles
SQLAnalysis
Report
Manually implement
![Page 65: Introduction to Oracle Database 11g – The Innovation Continues Pat Shuff Solutions Architect Oracle Corporation BROUG May 17, 2007](https://reader037.vdocuments.us/reader037/viewer/2022102808/56649de95503460f94ae43b5/html5/thumbnails/65.jpg)
AutomaticMemory Tuning
• Automatically adapts to workload changes
• Maximizes memory use efficiency
• Adjusts PGA, SGA, o/s memory
• Single dynamic memory parameter
• Helps eliminate out-of-memory errors
OS Memory
DB Process Memory
DB Shared Memory
OS Memory
DB Shared Memory
DB Process Memory
![Page 66: Introduction to Oracle Database 11g – The Innovation Continues Pat Shuff Solutions Architect Oracle Corporation BROUG May 17, 2007](https://reader037.vdocuments.us/reader037/viewer/2022102808/56649de95503460f94ae43b5/html5/thumbnails/66.jpg)
Database Result Cache
Table 1
Table 2 Table 3
join
join
Group by
• Automatically caches results of queries, query blocks, or pl/sql function calls• Cache is shared across statements and sessions on server• Significant speed up for read-only / read-mostly data• Full consistency and proper semantics
• Cache refreshed when any underlying table updated
query 1executes
![Page 67: Introduction to Oracle Database 11g – The Innovation Continues Pat Shuff Solutions Architect Oracle Corporation BROUG May 17, 2007](https://reader037.vdocuments.us/reader037/viewer/2022102808/56649de95503460f94ae43b5/html5/thumbnails/67.jpg)
Database Result Cache
Table 1
Table 2 Table 3
join
join
Group bycachedresult
• Automatically caches results of queries, query blocks, or pl/sql function calls• Cache is shared across statements and sessions on server• Significant speed up for read-only / read-mostly data• Full consistency and proper semantics
• Cache refreshed when any underlying table updated
result iscached
![Page 68: Introduction to Oracle Database 11g – The Innovation Continues Pat Shuff Solutions Architect Oracle Corporation BROUG May 17, 2007](https://reader037.vdocuments.us/reader037/viewer/2022102808/56649de95503460f94ae43b5/html5/thumbnails/68.jpg)
Database Result Cache
• Automatically caches results of queries, query blocks, or pl/sql function calls• Cache is shared across statements and sessions on server• Significant speed up for read-only / read-mostly data• Full consistency and proper semantics
• Cache refreshed when any underlying table updated
Table 5 Table 5
join
Table 4
join
Group by
joinquery 2 uses cachedresult transparently
Table 1
Table 2 Table 3
join
join
Group bycachedresult
![Page 69: Introduction to Oracle Database 11g – The Innovation Continues Pat Shuff Solutions Architect Oracle Corporation BROUG May 17, 2007](https://reader037.vdocuments.us/reader037/viewer/2022102808/56649de95503460f94ae43b5/html5/thumbnails/69.jpg)
Database Result Cache
Table 1
Table 2 Table 3
join
join
Group by
Table 5 Table 5
join
Table 4
join
Group by
join
cachedresult
• Automatically caches results of queries, query blocks, or pl/sql function calls• Cache is shared across statements and sessions on server• Significant speed up for read-only / read-mostly data• Full consistency and proper semantics
• Cache refreshed when any underlying table updated
query 2 uses cachedresult transparently
![Page 70: Introduction to Oracle Database 11g – The Innovation Continues Pat Shuff Solutions Architect Oracle Corporation BROUG May 17, 2007](https://reader037.vdocuments.us/reader037/viewer/2022102808/56649de95503460f94ae43b5/html5/thumbnails/70.jpg)
Other TransparentPerformance Improvements
• Auto-compiled PL/SQL and Java• Compression for all data• XML performance improvements• Spatial, multimedia and semantic
optimizations• Streams performance improvements• SecureFiles faster than files or LOBs• RAC and ASM optimizations
![Page 71: Introduction to Oracle Database 11g – The Innovation Continues Pat Shuff Solutions Architect Oracle Corporation BROUG May 17, 2007](https://reader037.vdocuments.us/reader037/viewer/2022102808/56649de95503460f94ae43b5/html5/thumbnails/71.jpg)
Managing Systems and
Managing Change
![Page 72: Introduction to Oracle Database 11g – The Innovation Continues Pat Shuff Solutions Architect Oracle Corporation BROUG May 17, 2007](https://reader037.vdocuments.us/reader037/viewer/2022102808/56649de95503460f94ae43b5/html5/thumbnails/72.jpg)
<Insert Picture Here>
“The art of progress is to preserve order amid
change and to preserve change amid order.”
Alfred North Whitehead:Principia Mathematica
![Page 73: Introduction to Oracle Database 11g – The Innovation Continues Pat Shuff Solutions Architect Oracle Corporation BROUG May 17, 2007](https://reader037.vdocuments.us/reader037/viewer/2022102808/56649de95503460f94ae43b5/html5/thumbnails/73.jpg)
Lifecycle of Change Management
Make Change
Set Up TestEnvironments
Test
Diagnose & Resolve
Problems
Preserve Order Amid Change
Patches & Workarounds
Diagnose Problems
Provision for Production
![Page 74: Introduction to Oracle Database 11g – The Innovation Continues Pat Shuff Solutions Architect Oracle Corporation BROUG May 17, 2007](https://reader037.vdocuments.us/reader037/viewer/2022102808/56649de95503460f94ae43b5/html5/thumbnails/74.jpg)
Database ReplayRealistic Testing
• Recreate actual production database workload• Capture production workload incl. concurrency • Replay workload in test with production timing• Analyze & fix issues before production
Middle Tier
Capture DB Workload
Storage
OracleDB servers Replay DB
Workload
ProductionEnvironment
Test (RAC)Environment`
![Page 75: Introduction to Oracle Database 11g – The Innovation Continues Pat Shuff Solutions Architect Oracle Corporation BROUG May 17, 2007](https://reader037.vdocuments.us/reader037/viewer/2022102808/56649de95503460f94ae43b5/html5/thumbnails/75.jpg)
SQL Performance Analyzer
Capture SQL
Storage
OracleDB servers
ExecuteSQL
Queries
ProductionEnvironment
Test (RAC)Environment`
Realistic Testing• Test impact of change on SQL performance
• Capture SQL incl incl. statistics & bind vars
• Re-execute SQL in test environment
• Use SQL Tuning Advisor to seed SQL plans
![Page 76: Introduction to Oracle Database 11g – The Innovation Continues Pat Shuff Solutions Architect Oracle Corporation BROUG May 17, 2007](https://reader037.vdocuments.us/reader037/viewer/2022102808/56649de95503460f94ae43b5/html5/thumbnails/76.jpg)
Oracle Enterprise Manager 10g R3
O/S
Database
Middleware
Applications
• Configuration Management
• Service Level Management
• Application Performance Mgmt
• Lifecycle Management
• Dashboards
• Integrated view of applicationsand infrastructure
• Manage service levels,diagnostics and remediation
• Automate capacity on demand • Simplify deployment
![Page 77: Introduction to Oracle Database 11g – The Innovation Continues Pat Shuff Solutions Architect Oracle Corporation BROUG May 17, 2007](https://reader037.vdocuments.us/reader037/viewer/2022102808/56649de95503460f94ae43b5/html5/thumbnails/77.jpg)
Oracle 10g R3 Grid Control
Reliable Deployment Automatic provisioning
from gold image
• Greatly simplify RAC provisioning with automation
• Single click RAC scale-out and scale-back
• Provision full RAC and Clusterware systems • Configures entire stack• Pre-requisite checks &
automatic fix-ups
Create production system
Stage Gold Image
2
3
11
Create reference system
Scale-out RAC nodes
4
![Page 78: Introduction to Oracle Database 11g – The Innovation Continues Pat Shuff Solutions Architect Oracle Corporation BROUG May 17, 2007](https://reader037.vdocuments.us/reader037/viewer/2022102808/56649de95503460f94ae43b5/html5/thumbnails/78.jpg)
Patch Automation
Proactively search Metalink for relevant patches
EM Support Workbench
Apply patch
Download available patch
Alert DBA
Reliable Deployment Proactive advisories from
Oracle Metalink
• Patches acquired based on configurations and feature usage (new in 11g DB)
• Stage once in library for multiple deployments
• Best practice driven patching
• Rolling patching support for RAC/ASM/Clusterware (new in 10gR3 Grid Control)
![Page 79: Introduction to Oracle Database 11g – The Innovation Continues Pat Shuff Solutions Architect Oracle Corporation BROUG May 17, 2007](https://reader037.vdocuments.us/reader037/viewer/2022102808/56649de95503460f94ae43b5/html5/thumbnails/79.jpg)
Automatic Diagnostic WorkflowDiagnostic Repository
Apply patch / workaround Use Repair Advisors
If unknown issue
Package incident & configUse Repair Advisor
Auto Incident Creation First-Failure Capture
Alert DBA Run Health Checks
Reduce Time to Problem Resolution
Error!
Check Metalink
If known issue
EM Support Workbench
![Page 80: Introduction to Oracle Database 11g – The Innovation Continues Pat Shuff Solutions Architect Oracle Corporation BROUG May 17, 2007](https://reader037.vdocuments.us/reader037/viewer/2022102808/56649de95503460f94ae43b5/html5/thumbnails/80.jpg)
• Highest Quality of Service• Performance• Scalability• Availability• Security
• Lowest Cost• Easier to Manage• Reduce risk of change
![Page 81: Introduction to Oracle Database 11g – The Innovation Continues Pat Shuff Solutions Architect Oracle Corporation BROUG May 17, 2007](https://reader037.vdocuments.us/reader037/viewer/2022102808/56649de95503460f94ae43b5/html5/thumbnails/81.jpg)
The preceding is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a
commitment to deliver any material, code, or functionality, and should not be relied upon
in making purchasing decisions.The development, release, and timing of any
features or functionality described for Oracle’s products remain at the sole
discretion of Oracle.
![Page 82: Introduction to Oracle Database 11g – The Innovation Continues Pat Shuff Solutions Architect Oracle Corporation BROUG May 17, 2007](https://reader037.vdocuments.us/reader037/viewer/2022102808/56649de95503460f94ae43b5/html5/thumbnails/82.jpg)
Q&A
![Page 83: Introduction to Oracle Database 11g – The Innovation Continues Pat Shuff Solutions Architect Oracle Corporation BROUG May 17, 2007](https://reader037.vdocuments.us/reader037/viewer/2022102808/56649de95503460f94ae43b5/html5/thumbnails/83.jpg)