Transcript PL/SQL Enhancements in Oracle Database 11g

Unstructured Data &
Application Development
SecureFiles
Multimedia
Spatial
PL/SQL
XML DB
Java
PHP
<Insert Picture Here>
SecureFiles
Managing Enterprise Information
• Organizations need to efficiently and securely manage
Structured
SemiStructured
XML
Unstructured
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
Files belong with Relational Data
Structured
SemiStructured
XML
Unstructured
PDF
• 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
Insert Performance
100
MB/s
MB/s
1000
10
0.1
1
10
100
1000
10000
NFS 0.2254 2.4889 11.26 32.674 72.733
SF
0.4212 3.6011 10.881 33.448 74.117
1
0.01
1
10
100
1000
10000
NFS 0.0782 0.9121 6.9089 26.669 52.315
SF
File Size (KB)
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
<Insert Picture Here>
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
Locate
To…
Reveal
customer
customers
Site
facilities
near
on the
wireless Web
customers
buying
patterns
Understand
customer
demographics
Locate
resources
near
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
<Insert Picture Here>
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)
<Insert Picture Here>
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
<Insert Picture Here>
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
<Insert Picture Here>
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;
/
<Insert Picture Here>
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
UI expected in SQL Developer 2.0
where Object_Name = ...
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
Functionality
• Finer grained dependency tracking
• Dynamic SQL functional completeness
• Real PL/SQL native compilation
• DBMS_Sql security
• Intra-unit inlining
• Fine grained access control for Utl_TCP, etc
• SQL & PL/SQL Result Caches
• Regexp_Count(), etc in SQL and PL/SQL
• The compound trigger
• Support for “super”
Usability
• Sequence in a PL/SQL expression
• The continue statement
• Named and mixed notation from SQL
Tools
• PL/Scope
• PL/SQL Hierarchical Profiler
• Create a disabled trigger; specify trigger firing
order
• “when others then null” compile-time warning
<Insert Picture Here>
XML DB Enhancements in Oracle Database 11g
XML Use Cases
Performance
XML-OR
Schema Optimized
Persistence
Majority of current
Customers
Unstructured
Schema Less
Persistence
XML-LOB
XML-Binary
Flexibility
Structured
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
<PurchaseOrder>
<Date>…</Date>
<Ref>…</Ref>
<LineItems>
<LineItem>…
<LineItem>…
<LineItem>
<LineItems>
• 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
Web
Cache
App
Server
Binary XML
Binary XML
Oracle Binary XML
Client
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
DB WS
SQL
SOAP
?
SOA
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
<Insert Picture Here>
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
<Insert Picture Here>
Q&A