Unstructured Data & Application Development SecureFiles Multimedia Spatial PL/SQL XML DB Java PHP

2. Unstructured Data & Application Development SecureFiles Multimedia Spatial PL/SQL XML DB Java PHP

3. SecureFiles

4. Managing Enterprise Information • Organizations need to efficiently and securely manage Semi-Structured Unstructured Structured XML PDF • 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

5. Semi-Structured Unstructured Structured XML PDF Files belong with Relational Data • Two data managers for one application is one too many • The application must patch over the gap • This split compromises security, robustness, and management

6. Oracle SecureFilesConsolidated 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 scale-out

7. 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

8. High Performance Query Performance Insert Performance • (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

9. 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

10. Advanced Features - Deduplication • 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 Secure hash

11. 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

12. 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

13. 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

14. 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

15. 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

16. 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

17. <Insert Picture Here> Multimedia

18. 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

19. 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

20. Location and Spatial

21. From Locate customerson the wireless Web To… Site facilities nearcustomers Reveal customer buying patterns Locate resources near customers Understandcustomer demographics Location and Spatial in Oracle Database

22. 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

23. PL/SQL Enhancements in Oracle Database 11g

24. 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)

25. <Insert Picture Here> Transparent performance:Fine Grained Dependency Tracking

26. 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

27. 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

28. <Insert Picture Here> Parameterized Performance:Real native compilation

29. 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 partyC 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!

30. 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

31. 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 nativethan with C native at run-time • Contrived tests have shown 20x run-time speedup

32. <Insert Picture Here> Usability of the language:Sequence in a PL/SQL expression

33. 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; /

34. 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; /

35. <Insert Picture Here> Tools support

36. alter session set PLScope_Settings = 'identifiers:all' create or replace... Select …. from … User_Identifiers where Object_Name = ... 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 UI expected in SQL Developer 2.0

37. > EXECUTE DBMS_HPROF.START_PROFILING('PLSHPROF_DIR', 'test.trc'); % plshprof 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 UI expected in SQL Developer 2.0

38. PL/SQL enhancements Summary • 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 Performance • Finer grained dependency tracking • Real PL/SQL native compilation • Intra-unit inlining • SQL & PL/SQL Result Caches • The compound trigger Usability • Sequence in a PL/SQL expression • The continue statement • Named and mixed notation from SQL Tools • PL/Scope • PL/SQL Hierarchical Profiler

39. XML DB Enhancements in Oracle Database 11g

40. Performance XML-OR Unstructured Structured XML-LOB XML-Binary Flexibility XML Use Cases Majority of current Customers Schema OptimizedPersistence Schema LessPersistence

41. 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

42. <PurchaseOrder> <Date>…</Date> <Ref>…</Ref> <LineItems> <LineItem>… <LineItem>… <LineItem> <LineItems> 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”

43. 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

44. 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

45. WebCache Database AppServer Client Binary XML Binary XML Binary XML Oracle Binary XML 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

46. 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

47. SOAP DBA WSDL Auto-Generate ? PL/SQL SQL XQuery Native Database Web Service DB WS SOA 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

48. Java Enhancements in Oracle Database 11g

49. JDBC PerformanceSaving 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

50. Advanced Security in JDBC-Thin • AES Encryption • SHA1 Check-Summing • 3rd Party Authentication • Radius • Kerberos • SSL • OS Authentication