<Insert Picture Here> Oracle Developer Days Oracle Database 11g - Exploiting the full potential of the Oracle database for application development

(1)

<Insert Picture Here>

Oracle Developer Days

(2)

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

(3)

Database Development Features Database Caching Technologies Data Management

Application Development Tools and Frameworks Enterprise Manager

Agenda

(4)

PL/SQL

Use PL/SQL Hierarchical Profiler to identify hot spots

Callee Elapsed Aggregated

OE_API 3,011 595,781

get_order 8,605 592,712

get_order static SQL line 12 7,159 7,159 get_order static SQL line 57 576,948 576,948

place_order 58 58

place_order static SQL line 207 0 0

• See where your CPU time is going

• Hierarchical profiler shows context

(5)

Edition-Based Redefinition

Online Application Upgrade

• Allows multiple editions to be available simultaneously

• Compilation of editioned objects transparent to application

• Views, Synonyms and PL/SQL objects can be editioned

Base Edition

New Edition

Database objects Application v1

(6)

Edition-Based Redefinition

Online Application Upgrade

• Compile objects in new edition without affecting online users

• Deploy new application version referencing new edition

• Current users are not effected, they reference base edition

• Phase in new application version over time

New Edition Base Edition Application v1 Application v2 View Procedure View Procedure 1 2 3 4 1 2 3 4

(7)

XML DB

Manage XML Content within the Oracle Database

• Consolidate storage of

relational and XML data in

Oracle 11g

• Natively store XML content

• XMLTYPE columns

• XML Tables

• Index of XML for fast access

• Unload relational data to XML

• Use XML Standards including

XQuery and XML Schema

XML

XML Oracle Database

(8)

XML DB – Example

Query XML Data in XMLTYPE Columns

Year Department Estimate Actual

2010 Accounting 570,000 560,000

2010 Operations 255,000 265,000

2009 Accounting 540,000 555,000

2009 Operations 240,000 242,000

select xtab.year, d.dname, xtab.actual, xtab.estimate from WWV_DEMO_DEPT d,

XMLTable ('/Budget/History/Budget' passing BUDGET COLUMNS

YEAR VARCHAR2(4) PATH 'Year',

ESTIMATE NUMBER(20) PATH 'Estimate', ACTUAL NUMBER(20) PATH 'Actual') xtab

(9)

XML DB - Example

Use SQL to Unload Relational Data to XML

select xmlElement("Department", xmlAttributes( d.DEPTNO as "DepartmentId"), xmlElement("Name", d.DNAME), xmlElement("Employees”, ( select xmlAgg( xmlElement("Employee", xmlForest(e.ENAME as "Name", e.HIREDATE as "StartDate”) ) ) from EMP e

where e.DEPTNO = d.DEPTNO) )) as XML from DEPT d XML <Department DepartmentId="10"> <Name>ACCOUNTING</Name> <Employees> <Employee employeeId="7782"> <Name>CLARK</Name> <StartDate>1981-06-09</StartDate> </Employee> <Employee employeeId="7839"> <Name>KING</Name> <StartDate>1981-11-17</StartDate> </Employee> <Employee employeeId="7934"> <Name>MILLER</Name> <StartDate>1982-01-23</StartDate> </Employee>

(10)

SQL Analytics

Industry’s most complete implementation

• Ranking functions

• Window Aggregate

functions

• LAG/LEAD functions

• Reporting Aggregate

functions

• Statistical Aggregates

• Linear Regression

• Descriptive Statistics

• Correlations

• Cross Tabs

• Hypothesis Testing

• Distribution Fitting

Statistics SQL Analytics

(11)

SQL Analytics - Example

Listing values of a Group By column

select deptno,

listagg( ename, '; ' )

within group(order by ename) as department_employees

from emp

group by deptno

DEPTNO DEPARTMENT EMPLOYEES

10 CLARK; KING; MILLER

20 ADAMS; FORD; JONES; SCOTT; SMITH

30 ALLEN; BLAKE; JAMES; MARTIN; TURNER; WARD

(12)

SQL Analytics - Example

Referencing Previous Row Values

select application,

to_char(run_date,’day’) run_date,

rows_processed,

rows_processed - lag(rows_processed, 1, 0) over (order by rows_processed )as difference

from application_log order by run_date

Application Run Date Rows Processed Difference

Daily Load Monday 850 0

Daily Load Tuesday 875 25

Daily Load Wednesday 860 -15

Daily Load Thursday 890 30

(13)

SQL PIVOT

Rotate rows into columns and vice versa

• Create aggregated cross-tabular result set

• Project many columns as filtered summaries of one

column

select * from sales

pivot

(sum(amount)

for quarter in

‘Q1’,’Q2’,’Q3’,’Q4’);

PROD QUARTER AMOUNT Shoe s Q1 2000 Shoe s Q2 1000 Jeans Q1 1000 Jeans Q3 500 Jeans Q3 100 Jeans Q4 1000 PROD Q1 Q2 Q3 Q4

Shoes 2000 1000 Null Null

(14)

Oracle Database JVM

Write Database Stored Procedures in Java

• Load Java in the database and expose via PL/SQL

• Leverage native and open source Java functionality

• Run Java where it makes the most sense

• Provide features not native to PL/SQL

• Complements Java deployed in the Mid Tier

• Improve performance of data heavy Java

• Leverage Java Skills

• New in Oracle Database 11gR1

• JIT Compiler

(15)

Oracle Database JVM

Write Database Stored Procedures in Java

Create or replace public class Hello {

static public void world() {

System.out.println("Hello World"); return; }

} /

create or replace procedure hello as language java

(16)

Additional Database Technologies

Oracle Spatial

Oracle Text Multimedia OLAP Cubes

Virtual Private DB Java Stored Procedures

(17)

Deployment

(18)

Database Caching Technologies

• SQL Result Set Cache

• Cache queries and sub queries

• Shared by all sessions

• Full consistency and proper semantics

• 2x - 100x speedup

• PL/SQL Function Cache

• Automatic invalidation

• Shared by all sessions

(19)

SQL Result Set Cache

Query Annotation and Alter Table Syntax

Query Annotation Syntax

select /*+ result_cache */ ename, job

from employees where deptno in (10,30,40);

Table Level Result Set Caching

(20)

PL/SQL Function Cache Example

CREATE FUNCTION format_name ( p_first_name IN VARCHAR2, p_last_name IN VARCHAR2) RETURN VARCHAR2 RESULT_CACHE IS v_name VARCHAR2(4000) BEGIN

RETURN p_first_name || ' ' || p_last_name; END format_name;

(21)

Database Caching Technologies

• Client Result Cache

• Recommended for small read-intensive tables

• 6X reduction in CPU usage

• 15% to 20% in response time

• Invoked by setting the init.ora parameters

• Database Smart Cache

• Extends SGA buffer cache

• Smart database caching

• Solaris and Oracle Enterprise Linux only

Buffer Cache

(22)

TimesTen In-Memory Database Cache

In-memory RDBMS for real-time applications

• Fast, consistent response

time with low latency

• Standard SQL and PL/SQL

• Cache subsets with read/

write; tables, rows,

columns

• Automatic

synchronization with

Oracle Database

• Built-in high availability

(HA) and data persistence

• Scale-out cache grid

Applications: Financial and Telco Services, Web Portal, CRM

IMDB Cache Grid

TimesTen DB Application

(23)

TimesTen In-Memory Database Cache

Flexible Cache Group Configuration

Full relational access with transaction support.

CREATE Asynchronous

Writethrough Cache Group custCache FROM customer ( cust_id

(number(10,0), ..

) , acct ( acct_id, .., foreign key (cust_id)

, orders (..);

Application

Transactions Cache Groups

Automatic Data Synchronization

(24)

Deployment

(25)

Advanced Compression Option

Significant reductions in data volumes

• Benefits for both OLTP and DW solutions

• Transparent to the application and works with all data

types

• EBS queries run 250% faster

OLTP Data Warehousing Archival No Compression

(26)

Advanced Compression Syntax Example

CREATE TABLE employees(

emplyee_id NUMBER,

first_name VARCHAR2(50),

last_name VARCHAR2(50)

)

COMPRESS FOR OLTP

;

(27)

Oracle SecureFiles

High Performance Large Object Storage

• Substantial Performance Improvement

• Performance comparable to or greater than file systems

• Compression

• De-duplication

• Encryption

• Compatible with existing Oracle BLOB/CLOB

syntax

• Compression and De-Duplication require

(28)

Oracle SecureFiles

Compression and De-Duplication

CREATE TABLE images (

image_id NUMBER,

image BLOB)

LOB(image)

STORE AS SECUREFILE (

(29)

Oracle Partitioning

Improve performance and manageability transparently

• Better query performance using partition

elimination

• Partitioned data is easier to backup and recover

Large Table _{Partition by} Column

Composite Partition

(30)

Oracle Partitioning

Combining compression and partitioning

Archival Compression DW Compression OLTP Compression

• Maximize performance of active data

• Increase compression and use lower cost

(31)

Flashback

Flashback, Flashback Data Archive

•

Flashback: Query, Table, Tablespace, Transaction, Database

•

Flashback available based on redo retention

•

Flashback Data Archive provides infinite flashback and requires the Total Recall database option

Time Current Data

(32)

Flashback Example

Flashback query to a point in time

select * from employees as of timestamp to_timestamp('2003-04-04 09:30:00', 'yyyy-mm-dd hh:mi:ss') where name = :b1;

(33)

Deployment

(34)

Real Application Clusters (RAC)

Highly Available and Scalable

• Virtualizes low cost servers into a shared resource

• Scale Out to support more users

(35)

• Provide High Availability with failover to stand by databases

• Leverage your read only standby database for queries, reports, and testing on live data

Oracle Data Guard

Active – Active High Availability

Primary Database

Active Stand-by Database

Redo Transport

(36)

Oracle Data Guard

Reader Farms

Updates Queries Queries Queries

Production Database

Reader Farm

(37)

Real Application Testing

Workload for 1,000s of Online Users Replayed

Production Workload Test

(38)

Deployment

(39)

Oracle Supports All Popular Application

Development Frameworks

(40)

Oracle SQL Developer

No Cost IDE for the Oracle Database

• Lightweight, graphical user interface

• Enhances database development productivity

• Browsing, creating, editing, debugging and reporting

• Integrated migration capabilities

• Easy installation

• Download and unzip

• Uses thin JDBC driver => No Oracle Home required

• Free, Extensible and fully supported

• 1.5 million users

(41)

Oracle SQL Developer

No cost IDE for the Oracle Database

Database Data Modeling

PL/SQL Unit Testing Database Migrations

(42)

Java

(43)

Oracle and Java

• Oracle is leading the Java community

• Oracle users Java everywhere

• Applications

• Middleware

• Database

(44)

Java Development Tools

Three Tools for the Java Development Community

• Eclipse

• Certified plugins on top of core Eclipse • Focus on Java coding

• EJB/JPA, Spring, WebServices, WebLogic

• NetBeans

• The IDE for Java FX Developers

• Supports Java EE 6.0, Java FX and Java ME • Community Focused

• Oracle Jdeveloper 11gR1

• IDE for Fusion Middleware Developers • Development Platform for Oracle ADF • Visual, Declarative, WYSIWYG

(45)

• Efficient Java Persistence with Oracle JDBC 11g

–

JDBC 4.0, IPV6

–

Built-in Advanced Security in JDBC-Thin

–

Support for Query Change Notification

–

Result Cache (JDBC-OCI): 5-8 x Faster

–

Native AQ interface: 40-300% Faster

–

LOB PreFetch, Zero Copy SecureFiles LOB

• Scalable and Reliable Java Persistence with

Universal Connection Pool

–

Fast Connection Failover (RAC, Data Guard)

–

Runtime Connection Load Balancing

JDBC and UCP

(46)

Cache Invalidation with Query Change

Notification

Java

1. Register the Query …

4.Invalidate cache

5.Repopulate cache …

Custom cache 3. _NotificationAutomatic

Notification when changes in the database invalidates

an existing query result set

2.DML (Change to the result set)

(47)

• Common framework for

• Fusion Applications

• Fusion Middleware

• Enhance productivity & ease-of-use

• Visual and declarative development

• Takes care of the “plumbing” code

• Modeling, coding, debugging, testing, profiling, deployment – implements best practices

• Over 150 cutting edge AJAX enabled user interface components

• Promotes SOA development

Java Development Tools - JDeveloper

(48)

• Database-Centric Web Application Development Tool

• No cost option of the Oracle Database

• Distributed with database 10gR2, 11gR1, 11gR2 and XE

• Latest Version available on OTN

(49)

New application development

• Data centric rich internet applications • Custom application development

• Rapid application development • Online reporting

Oracle Forms modernization

• Leverage SQL & PL/SQL skills

• Re-use DB packages

Desktop database consolidation

• Microsoft Access replacement

• Spreadsheet’s used for data collection

(50)

Oracle Application Express (APEX)

Runs within Database Self Service Provisioning

Leverages SQL Skills Rapid Browser Based Development

Point your browser and start developing Build reports and charts using SQL

(51)

• Full Support for .NET and Visual

Studio

• Same API’s, Same Tools

• Easy to Learn, Easy to Migrate • Freely Available

• Full Support for Oracle Database

• Run All Editions of the Oracle Database • Real Application Clusters (RAC)

• Data Guard, Data types, …

• Run Oracle Database Everywhere • Linux

• Unix (Sun, IBM, HP) • Microsoft Windows

Oracle Support for .NET

(52)

(53)

Open Source Language Drivers for Oracle

•

Building Agile Web Applications with

Oracle Database

•

Connection Scalability with Database

Resident Connection Pool (DRCP)

•

PHP OCI8

• Available on php.net, PECL, Zend Server, Unbreakable

Linux Network and http://oss.oracle.com/projects/php

•

Ruby OCI8

• http://ruby-oci8.rubyforge.org/en/ •

Python Cx-Oracle

• http://cx-oracle.sourceforge.net •

Perl DBD::Oracle

• http://search.cpan.org/~pythian/DBD-Oracle-1.23/ Oracle.pm

(54)

Deployment

(55)

• Applications to Disk Management

• Performance and Change Management

• Diagnostics and Tuning

Oracle Enterprise Manager

Manage Applications to Disk

Applications Middleware Database

Physical and Virtual Servers and Storage

(56)

Oracle Enterprise Manager

Automatic Performance Diagnostics

• Boost Administrator Productivity

• Eliminate Performance Bottlenecks

• Lower IT Management Costs

• Deliver Higher Quality of Service

Automatic Workload Repository Snapshots Performance & Management Advisors High Load SQL RAC Issues IO / CPU Issues Self-Diagnostic Engine