Unstructured Data & Application Development
SecureFiles
Multimedia Spatial
PL/SQL
XML DB Java
PHP
SecureFiles
Managing Enterprise Information
• Organizations need to efficiently and securely manage
Structured SemiStructured XML PDF Unstructured
• Simplicity and performance of file systems makes it attractive to store file data in file systems, while keeping relational data in DB • Enterprise applications manipulate both files and relational data • e.g. Document Management, Media, Medical, CAD, Imaging
Files belong with Relational Data
Structured SemiStructured XML PDF Unstructured
• Two data managers for one application is one too many • The application must patch over the gap • This split compromises security, robustness, and management
Oracle SecureFiles
Consolidated Secure Management of Data • SecureFiles is a new 11g feature designed to break the performance barrier keeping file data out of databases • Next-generation LOBs - faster, and with more capabilities • transparent deduplication, compression and encryption • leverage the security, reliability, and scalability of database • superset of LOB interfaces allows easy migration from LOBs
• Enables consolidation of file data with associated relational data • single security model • single view of data • single management of data • scalable to any level using SMP scale-up, or grid scaleout
Designed from Scratch
• SecureFiles is a major rearchitecture of how the database handles unstructured (file) data
• not an incremental improvement to LOBs
• Entirely new:
• • • • • • • disk format network protocol versioning and sharing mechanisms caching and locking redo and undo algorithms space and memory management cluster consistency algorithms
High Performance
Query Performance
1000
MB/s MB/s
Insert Performance
100 1 0.01
SF 1 10 100 1000 10000
10 0.1
SF 1 10 100 1000 10000
NFS 0.2254 2.4889 11.26 32.674 72.733 0.4212 3.6011 10.881 33.448 74.117 File Size (KB)
NFS 0.0782 0.9121 6.9089 26.669 52.315 0.4737 8.2762 37.983 44.944 46.592 File Size (KB)
• (Preliminary) performance compared to Linux NFS/Ext3
• application does inserts/queries of a metadata row and image file • tests run using both SecureFiles and NFS/ext3 in metadata journalling only (default for NFS)
• SecureFiles is faster across the board
• up to 2x faster for Queries, 6x for Inserts
Advanced Features - Compression
• Huge storage savings
• industry standard compression algorithms • 2-3x compression for typical files (doc, pdf, xml) • minimal CPU overhead during compression
• Automatically detects if SecureFile data is compressible
• skips compression for already compressed data and when space savings are minimal or zero
• Two levels of compression provide different compression ratios
• compression Levels: MEDIUM (default), HIGH • higher the degree of compression, higher the latency and CPU overhead incurred
• SecureFiles Compression is independent of table or index compression • Server-side compression
• allows for random reads and writes to SecureFile data • can be specified at a partition level
• Part of the Advanced Compression Option
Advanced Features - Deduplication
Secure hash
• Enables storage of a single physical image for duplicate data • Significantly reduces space consumption • Dramatically improves writes and copy operations • No adverse impact on read operations
• may actually improve read performance for cache data
• Duplicate detection happens within a table, partition or sub-partition • Specially useful for content management, email applications and data archival applications • Part of the Advanced Compression Option
Advanced Features - Encryption
• Extends Transparent Data Encryption (TDE) syntax to SecureFile data
• old LOB or BasicFiles data can not be encrypted
• Performed at Block level • Support for industry-standard encryption algorithms
• • • • 3DES168 AES128 AES192 (default) AES256
• Encrypt on a per-column basis • Part of the Advanced Security Option
SecureFile Interfaces
• SecureFiles can be accessed by both database clients and file system clients • Database clients use extended LOB interfaces
• JDBC, ODBC, OCI, .NET, PL/SQL • 11g has a highly optimized streaming protocol for SecureFiles
• File system clients use the file system protocols implemented in the XML DB repository
• FTP access • WebDav Access • http Access
Integration with Other Products and Features
• Secure is fully integrated with
• • • • XML DB (Binary XML) Oracle InterMedia Oracle Spatial Content DB
• Out of box benefits for new installations
• by setting db_securefiles= FORCE or ALWAYS
• Migration path being worked out for existing installations • Efforts underway to integrate with Stellant
Using SecureFiles
• Old LOBs are still supported and are referred to as „BASICFILE‟
• Default LOB storage type in Oracle Database 11g
• New init.ora parameter „db_securefile‟ to manage LOB storage policy
• • • • • PERMITTED – allow SecureFiles to be created (Default) NEVER – disallow new SecureFile FORCE – create all LOBs as SecureFiles ALWAYS – attempt to create SecureFiles, but fall back to BasicFiles IGNORE – ignore attempts to create SecureFiles
• Requires
• locally managed tablespaces with ASSM is required to use SecureFiles • compatibility set to 11.1 or higher
Migration to SecureFiles
•
•
Requires table rebuild
can be done at the partition level
•
• • • •
Online Redefinition is the preferred migration technique for SecureFiles
no need to take the table or partition offline. additional storage equal to the entire table and all LOB segments must be available. global indexes need to be rebuilt. recommend setting NOLOGGING storage attribute for destination SecureFile columns during migration to avoid performance problems with redo generation if the destination table is partition, online redefinition can be done in parallel
•
The Best of Files and Databases
• SecureFiles have all the leading-edge file system capabilities
• Deduplication, Encryption, Compression, Logging
• SecureFiles have advanced DB capabilities not in file systems
• • • • • • • • Transactions, Read Consistency, Flashback Readable Standby, Consistent Backup, Point in Time Recovery Fine Grained Auditing, Label Security XML indexing, XML Queries, XPath Real Application Clusters Automatic Storage Management Partitioning and ILM Search across meta-data and file content
• Capabilities go far beyond any other database or file system
• having the best of both worlds removes the need to compromise
Multimedia
Multimedia in Oracle Database
• Why put images in the database?
• multimedia content subject to database enforced referential integrity and transaction control • metadata extraction, editing and indexing. • media-specific features such as metadata editing, thumb nailing and format conversion • simplifies secure delivery of content to streaming media servers
• New in Oracle Database 11g
• image size limits increased, up to 128TB • new securefiles datatype for increased performance, compression, and deduplication • improved DICOM support
DICOM Medical Imaging Support
• Used by virtually all medical devices (CT, MRI, PET, …) • Database support the data management needs of clinical medicine and life sciences
• Multi-terabyte image archives with security, auditing and standards conformance • Image quality control – ensure well-formed images
• Built-in support for metadata extraction and searching • Support for privacy regulations (HIPPA), annotation and format conversion
Location and Spatial
Location and Spatial in Oracle Database
From
Location AUSTRIA AUSTRIA AUSTRIA AUSTRIA AUSTRIA AUSTRIA AUSTRIA AUSTRIA AUSTRIA AUSTRIA AUSTRIA AUSTRIA BOSNIA BOSNIA BRAZIL CANADA CANADA CANADA CANADA CANADA CANADA CANADA CANADA CHINA CHINA FINLAND FINLAND FRANCE FRANCE FRANCE FRANCE FRANCE FRANCE FRANCE FRANCE FRANCE FRANCE FRANCE Client Name Usage **Hallein Municipality Local authority **Ludesch Local Government ARG Verrmessung, Dornbirn Survey and mapping ILF-Dornbirn -8 ILF-Innsbrueck - 2 ILF-Prague - 2 ILF-Vienna - 2 ILF-Villah - 1 Ingenieurgemeinschaft Laesser-Fezlmayr (ILF), Engineering company Lochau Municipality, Vorarlberg Local government Manahl, Feldkirch Engineering company Vorarlberg Erdgas, Dornbirn Gas distribution City of Zageb(CV) Local government Computech (CV) Reseller Systenge Reseller City of Edmonton Local government City of Luduc Local government District of Oak Bay Local government Energy & Mines (Ottawa) Energy & Mines (Quebec) Geopower Technologies, Inc. Reseller H.H. Pillar Corp. University of Toronto Education Beihai Urban Construction Beijing Urban Archive Local government Pohjois-Satakunnan paikkatietopalvelu OY GIS systems house Tampere muncipality (PCX 100 USER LICENCE) Local government Cabinet Dulac Survey and mapping District Bayonne - Anglet - Biarritz Local government consortium EPA Cergy-Pontoise New town development France Telecom Telecommunic. company Gaz de France Gas distribtuion Institut Geographique National (IGN) National mapping agency ITMI Software developer/integrator Municipality of Dijon Local government Nancy District Local government School of IGN IGN's training school University of Caen Educational
To…
Site facilities near
Locate
customers
on the wireless Web
Reveal
customer
buying patterns
customers
Understand
Locate resources near
customer
demographics
customers
3D & Web Services Support
• Comprehensive 3D infrastructure for modeling, visualization, simulation
• Meets business requirements for 3D simulations & models of • Cityscapes, viewscapes, viewsheds, line-of-sight • Hazard assessments, urban models, city planning • As-built and reverse engineering structures
• OGC & ISO TC211 Enterprise Web Services Support
• Meets requirements to provide spatial features as a service • Full transaction support for SOA architectures used by mapping agencies, energy, utilities, public sector
PL/SQL Enhancements in Oracle Database 11g
PL/SQL Enhancements
• Every new major release of Oracle Database brings PL/SQL enhancements in these categories
• Transparent and parameterized performance improvements • New language features that you use in your programs to get better performance
• New language features that bring functionality that you earlier couldn‟t achieve (or could achieve only with cumbersome workarounds) and that make programming more comfortable
• Tools support (debugging, performance tracing, etc)
Transparent performance:
Fine Grained Dependency Tracking
The challenge
create table t(a number) / create view v as select a from t / alter table t add(Unheard_Of number) / select status from User_Objects where Object_Name = 'V' /
• View v ends up invalid in 10.2 because we know only that its dependency parent has changed – at the granularity of the whole object
Fine Grained Dependency Tracking
• In 11.1 we track dependencies at the level of element within unit
• so we know that these changes have no consequence
• I classified this as a transparent performance improvement
• It‟s certainly transparent!
• Unnecessary recompilation certainly consumes CPU • Don’t forget that the “4068” family of errors has a different cause: recompiling at least one stateful package body that a second concurrent session has instantiated
Parameterized Performance:
Real native compilation
The challenge
• Through 10.2, PL/SQL compiled to a native DLL is significantly faster than PL/SQL compiled for interpretation by the PVM • Oracle translates PL/SQL source to C code and leaves the last step to a 3rd party C compiler
• BUT… some customers‟ religion forbids a C compiler on a production box!
• AND… other customers‟ religion forbids paying to license a C compiler when they‟ve already paid to license Oracle Database!
Real native compilation
• In 11.1, Oracle translates PL/SQL source directly to the DLL for the current hardware
• Moreover, Oracle does the linking and loading so that the filesystem directories are no longer needed
• So PL/SQL native compilation will work out of the box – and without compromising religion • Only one parameter remains: the on/off switch,
PLSQL_Code_Type
Real native compilation
• As a bonus, it‟s faster!
• Compilation with real native is twice as fast as with C native
• The Whetstone benchmark is 2.5x faster with real native than with C native at run-time
• Contrived tests have shown 20x run-time speedup
Usability of the language:
Sequence in a PL/SQL expression
The challenge
create or replace trigger Trg before insert on My_Table for each row declare s number; begin -- Annoying locution select My_Seq.Nextval into s from Dual; :New.PK := s; end; /
Sequence in a PL/SQL expression
create or replace trigger Trg before insert on My_Table for each row
begin
:New.ID := My_Seq.Nextval; end; /
Tools support
PL/Scope
Challenge
Inherit code, find a problem in one area that links elsewhere, got lost following reference after reference Impact Analysis: how do you determine who references a particular variable?
Solution
PL/Scope
Usage
alter session set PLScope_Settings = 'identifiers:all' create or replace... Select …. from … User_Identifiers where Object_Name = ...
UI expected in SQL Developer 2.0
PL/SQL Hierarchical Profiler
Challenge
Where is time really being spent in PL/SQL code?
Solution
PL/SQL Hierarchical Profiler reports dynamic execution profile organised by subprogram accounts for SQL and PL/SQL separately generates hypertexted HTML reports
Usage
> EXECUTE DBMS_HPROF.START_PROFILING('PLSHPROF_DIR', 'test.trc'); % plshprof
UI expected in SQL Developer 2.0
PL/SQL enhancements
Summary
Performance
• Finer grained dependency tracking • Real PL/SQL native compilation • Intra-unit inlining • SQL & PL/SQL Result Caches • The compound trigger
Functionality
• Dynamic SQL functional completeness
• DBMS_Sql security • Fine grained access control for Utl_TCP, etc • Regexp_Count(), etc in SQL and PL/SQL • Support for “super” • Create a disabled trigger; specify trigger firing order • “when others then null” compile-time warning
Usability
• Sequence in a PL/SQL expression • The continue statement • Named and mixed notation from SQL
Tools
• PL/Scope • PL/SQL Hierarchical Profiler
XML DB Enhancements in Oracle Database 11g
XML Use Cases
Performance XML-OR
Schema Optimized Persistence
Majority of current Customers
Unstructured
Structured
Schema Less Persistence XML-LOB
XML-Binary Flexibility
In Place Schema Evolution
• 10.2 copyEvolve() unloads and reloads the data when the XML Schema changes
• very flexible but expensive: time to evolve schema is proportionate to the amount of data being managed
• 11.1 In Place Schema Evolution allows simple changes to registered XML schemas with zero down-time
• no data copy required • schema change takes a few seconds regardless of amount of data. • changes to the XML must not invalidate existing documents • add optional elements and attributes • adding new values to enumerations • increase in length
XMLType Partitioning
Challenge
… […] … …
• repeating elements typify XML documents and are managed using nested tables
Solution
• leverage 11.1 REF partitioning • nested Tables are partitioned using the same key as the top level XMLType table • supports “partition maintenance” operations • supports “partition pruning”
XML Misc Improvements
• Significant performance improvements • Large node handling eliminates current 64K limit on size of a text-node. • Stream based replication support for LOB-based XML storage • Events: trigger support for the XML DB repository.
• e.g. inserting a purchase order into “approved” folder triggers ordering process
Binary XML
Challenge • 10.2 schema-less XML not well optimized Solution • 11.1 binary XML optimized for schema-less and flexible schema-based XML • Post Parse compact representation of XML • Flexible XML Schema Support • High performance fragment access and extraction with new XML Index
Binary XML
• Single format for “on-disk”, “in-memory” and “on-the-wire” • Reduced CPU and Memory overhead
• Single format avoids parse and serialize issue between tiers
• Reduced Network Overhead
• Post-parse format used when XML moves between tiers
Database
App Server
Binary XML Binary XML
Web Cache
Binary XML
Client
Oracle Binary XML
XML Index
• New universal index for Binary and LOB based XMLType • All possible paths are indexed by default • XML index optimization
• asynchronous operation possible • path sub-setting controls what is indexed • partial re-indexing (binary XML and securefiles only)
• Repository integration with XML Index on XML DB repository
Native Database Web Service
DBA WSDL
PL/SQL
SOAP
?
SOA
DB WS
SQL
XQuery
Easy: a few steps, no other software installation and no coding Secure: HTTP authentication and easily integrate with WS Security Products High performance : C-based engine, natively build in to the Oracle DB
Java Enhancements in Oracle Database 11g
JDBC Performance
Saving 1 Million Roundtrips per Day
Prefetch in First Roundtrip (JDBC-Thin)
• Saves 1 Database Roundtrip per SELECT Statement • Benefit for AD-Hoc Queries
New Native AQ protocol (JDBC-Thin)
• AQ/OJMS Operations 40%-300% Faster • Reduced server-side CPU Utilization
Advanced Security in JDBC-Thin
• AES Encryption • SHA1 Check-Summing • 3rd Party Authentication
• Radius • Kerberos • SSL
• OS Authentication
Java Standards
JDBC 4.0
• • • • • • SQLXML RowId NChar & Nclob Wrapper Interface SQLException Hierarchy conn.createBlob, createClob, etc
Disconnect Java VM upgrade from database
• Java 5 (i.e., JDK 1.5) in base 11.1 release • Java 6 (i.e., JDK 1.6) and newer JDKs, in patch sets
From Black Box to White Box Monitoring
• JMX Instrumentation • Support standard JMX-enabled tools(i.e., JConsole)
Java in the Database Mass Adoption
Challenge
• Performance
Solution
• New JIT compiler compiles Java in the database natively and transparently (on the fly) without the need of a C compiler • Enabled by default, Out-of-the-Box • Up to an Order of magnitude (~x 10) speed-up for pure Java, 10g versus 11g, out-of-the-box
Challenge
• Ease of Use
Solution
• JDK-Like interface, with code on filesystem, using faniliar notation, e.g. –{classpath}, -D system properties • Eases the migration for developers from JDK VM world
PHP
PHP users have asked for Database Connection Pooling
• Web apps issue hundreds connects per second • Connection establishment is expensive • PHP Does not allow real multi-threading
• persistent vs non persistent connections
• Existing Client-side connection pooling not possible • Options:
• apache process can repeatedly connect/disconnect • apache process can hold onto private connection
• Results in inefficient use of the database resources
Database Resident Connection Pool
No Connection Pooling
11g Database Resident Connection Pooling
Q&A