PL/SQL Enhancements in Oracle Database 11g

1
(No Transcript)
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
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
SecureFile Innovations

• Write Gather Cache
• Cache above the storage layer buffers data up to
  4MB during writes before flushing to disk
• Allows for large contiguous space allocation for
  LOB data and reduced write latency.
• Intelligent Pre-fetching
• Improves read performance by pre-fetching LOB
  data from disk
• Overlaps disk IO with network latency to improve
  throughput
• New Space Management routine
• Automates new space allocation and freed space
  reclamation
• Optimized chunk size reduces fragmentation
• No more High Water Mark contention as with old
  LOBs
• Deletion and Reuse of entire LOBs not just
  individual chunks.

9
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

10
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

11
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

12
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

13
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

14
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

15
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

16
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

17
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

18
ltInsert Picture Heregt
Multimedia
19
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

20
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

21
Location and Spatial
22
Location and Spatial in Oracle Database
23
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

24
PL/SQL Enhancements in Oracle Database 11g
25
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 couldnt achieve (or could
  achieve only with cumbersome workarounds) and
  that make programming more comfortable
• Tools support (debugging, performance tracing,
  etc)

26
ltInsert Picture Heregt
Transparent performanceFine Grained
Dependency Tracking
27
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

28
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
• Its certainly transparent!
• Unnecessary recompilation certainly consumes CPU
• Dont 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

29
ltInsert Picture Heregt
Parameterized PerformanceReal native
compilation
30
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 theyve already paid to
  license Oracle Database!

31
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

32
Real native compilation

• As a bonus, its 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

33
ltInsert Picture Heregt
Usability of the languageSequence in a PL/SQL
expression
34
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 /
35
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 /
36
ltInsert Picture Heregt
Tools support
37
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
38
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
39
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

40
XML DB Enhancements in Oracle Database 11g
41
XML Use Cases
Majority of current Customers
Schema OptimizedPersistence
Schema LessPersistence
42
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

43
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

44
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

45
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

46
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

47
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

48
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
49
Java Enhancements in Oracle Database 11g
50
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

51
Advanced Security in JDBC-Thin

• AES Encryption
• SHA1 Check-Summing
• 3rd Party Authentication
• Radius
• Kerberos
• SSL
• OS Authentication

52
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)

53
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

54
PHP
55
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

56
Database Resident Connection Pool
57
ltInsert Picture Heregt