Building Innovative Applications With Oracle Database 11g

Building Innovative Applicationswith Oracle Database 11gMark Drake, Manager, Product Management

The following is intended to outline our generalproduct direction. It is intended for informationpurposes only, and may not be incorporated into anycontract. It is not a commitment to deliver anymaterial, code, or functionality, and should not berelied upon in making purchasing decisions.The development,development releaserelease, and timing of anyfeatures or functionality described for Oracle’sproducts remains at the sole discretion of Oracle.

Agenda Database SQL,SQL PL/SQL,PL/SQL SecureSFilesFil Analytics Secure Files XML Performance By Design Query Cache In-Memory Database Cache Partitioning High Availability Architecture Enterprise Manager Packs SQL Developer Data Modeler SQL Developer Insert Picture Here

Enhanced SQL and PL/SQL CapabilitiesOracle Database 11g PivotNew analytical window functionsReal Time SQL MonitoringSequences– No Longer need a select PL/SQL Hierarchical Profiler PL/Scope5

Pivot SQL Command Simpler and more maintainable Better performanceSELECT * FROM (SELECT dname ,JOBSELECTdname,FROM EMP e,dept dsum(decode(job,'PRESIDENT',1,0)) president,where e.deptno d.deptno)sum(decode(job,'MANAGER',1,0)) MANAGER,PIVOTsum(decode(job,'ANALYST',1,0)) ANALYST,(COUNT(*) FOR JOB IN (sum(decode(job,'SALESMAN',1,0)) SALESMAN,'PRESIDENT' as President,sum(decode(job,'CLERK',1,0)) CLERK'MANAGER' as MManager,FROM EMP e,deptd t d'ANALYST' as Analyst,where e.deptno d.deptno'SALESMAN' as Salesman,GROUP BY dname'CLERK' as Clerk))6

PL/SQL New Features Direct Sequence Assignmentsg– Old select my seq.nextval into l var from dual;– New l var : my seq.nextval; PL/SQL Hierarchical Profiler PL/Scope7

Statistics and SQL Analytics Ranking functions rank,k dense rank,dk cume dist,di t percent rank,tk ntiletil Window Aggregate functions(moving and cumulative) Avg, sum, min, max, count, variance, stddev,first value, last value LAG/LEAD functions Direct inter-row reference using offsets Reporting Aggregate functions SumSum, avg,avg min,min maxmax, variancevariance, stddevstddev, countcount,ratio to report Statistical Aggregates Correlation, linear regression family, covariance Linear regression Fitting of an ordinary-least-squares regressionline to a set of number pairs. Frequently combined with the COVAR POP,COVAR SAMP, and CORR functions.N t StatisticsNote:St ti ti andd SQL AAnalyticsl ti are iincludedl d d iin OOraclelDatabase Standard Edition Descriptive Statistics average, standardt d dddeviation,i tivariance,imin,i max, mediandi(via percentile count), mode, group-by & roll-up DBMS STAT FUNCS: summarizes numerical columnsof a table and returns count, min, max, range, mean,stats mode, variance, standard deviation, median,quantile values, /- n sigma values, top/bottom 5 values Correlations Pearson’s correlation coefficients, Spearman's andKendall's (both nonparametric). Cross Tabs Enhanced with % statistics: chi squared, phi coefficient,Cramer's V, contingency coefficient, Cohen's kappa Hypothesis Testing Student t-test , F-test, Binomial test, Wilcoxon SignedRanks test, Chi-square, Mann Whitney test, KolmogorovSmirnov test, One-way ANOVA Distribution Fitting Kolmogorov-Smirnov Test, Anderson-Darling Test, ChiSquared Test, Normal, Uniform, Weibull, Exponential

Insert Picture Here Demo

Files in the Database Reinvented Files are an integral part of modern database applications Best of Both Worlds File Capabilities File System InterfaceHigh PerformanceCompressionEncryptionDeduplicationHSM Database Capabilities TTransactionsti Query Consistency Advanced Backup andRecovery Powerful Security Flashback Scale up SMPs Scale out Clusters Product images, contracts, XML, ETL files, manuals, etc. Applications developers want to store business data filesin the database to benefit from transactional consistency,and unify HA and Security Poor performance, limited functionality, and lack of access byexisting file based tools have held them back Oracle Database 11g reinvents files in the databaseprovides superp fast and ppowerful file storageg SecureFiles p Removes performance barrier to storing files in the database DBFS provides a file system interface to files in the DB Enables existing file based tools to easily access DB files

SecureFiles Performance Performance compared to Linux FS– Tests run using both SecureFiles and ext3 in metadatajournaling onlyonly, no networkFile ReadsFile 0LinuxFilesLOBs0.010.10110File Size(MB)1006050403020100SecureFilesLinux FilesLOBs0.010.10110File Size (MB)100

Cube Organized Materialized ViewsTransparent to SQL QueriesMaterialized ViewsSQL RefreshOLAP Cube

XML Use cases

Oracle’s XML Vision Enable a singleg source of truth for XML Provide the best platform for managing all your XML– Flexibility to allow optimal processing of data-centric andcontent centric XMLcontent-centric– Deliver Oracle’s commitment to Reliability, Security,Availability and Scalability DDrivei andd iimplementlt kkey XML StStandardsd d Support SQL-centric, XML-centric and documentcentric development paradigms

PerformanceeOracle and XML: Sustained InnovationXQueryXMLAPI’s1998BinaryXMLStorage& IndexingXMLStStorage&Repository200120042007

Why XML in the Database? XML contains mission critical information– Interchange with external organizations– Web Services Need to manage XML effectively and efficiently––––Number and size of documents increasingReliability, Scalability, AvailabilitySecurityCompliance Accurate and fast information location and retrieval

What is XML DB? XMLType: Native flexible, scalable, XML storage, processingand indexing XQuery, XML Schema, XSLT: enable XML centric development XMLDB repository: XML specific content management– File / Folder access metaphors– Xlink for integrity management– Xinclude for compoundpdocuments SQL/XML for SQL-Centric XML publish and SQL/Xqueryinteraction SQL/XML and XML/SQL interoperability Standards compliant

Advanced XML CapabilitiesXML ApplicationJDBCFilesXMLTypeDocument and dataCentric AccessFoldersXMLSchemaNative XQueryQyEngineACLSXQueryXML and ve storage forschema-based andschema-less XML.NETXDKOCISOAPHTTPFTPDocumentor MessageWebDavEventsDOMXML views ofrelational Content18

Insert Picture Here Demo

Why Java in the Database? Implement thingsg yyou cannot do in PL/SQL Reuse Java classes and libraries to extend database– Reduce development time and costs– ExtendE t d databased t bfunctionalityftilit–e.g., custom image transformation and data formatconversion (GIF, PNG, JPEG) Partition Java applications between middle-tier anddatabase– Compute bound applications in client/middle-tierclient/middle tier– Data, data-and-compute intensive applications in thedatabase Reuse Java skills for database development

Java in the Database: Use Cases Trigger-based NotificationSystem using RMI Secure Credit-Card Processingusing JSSE Custom Alert applications thatmonitor business data Sending emails with attachmentfrom within the database Produce PDF files from ResultSet Execute external OS commandsand external procedures ImplementI lMd5 CRC Publish Repository Content toPortal Portable Logistic Applications Implement Parsers for various FileFormats (txt,(txt zip,zip xml,xml binary) Implement Image Transformation andFormat Conversion (GIF, PNG, JPEG,etc)) Implement database-resident ContentManagement System HTTP Call-Out JDBC Call-Out RMI Call-Out to SAP Web Services Call-Out Messaging across Tiers RESTful Database Web Services* Lucene Domain Index** ne%20Domain%20Index

What’s New for Java in Oracle Database 11g Standard compliance:– IPv6 Support via System properties ses Standards Compliance:pJDK 1.5 Supportpp Performance: Innovative JIT Compiler– Transparent, On-the-fly compilation, no C compiler required– CompiledCil d JJava codesd persistedi t d ffor ffuturetiinvocationsti Ease of Deployment: JMX Instrumentation forMonitoringg– Management MBean, standard JMX tools (e.g., JConsole) Ease of Development: JDK Command-line likeI t fInterface

Agenda Database SQL,SQL PL/SQL,PL/SQL SecureSFilesFil Analytics Secure Files XML Performance By Design Query Cache In-Memory Database Cache Partitioning High Availability Architecture Enterprise Manager Packs SQL Developer Data Modeler SQL Developer Insert Picture Here

Server SQL Results Cache Database caches results of queries, sub-queries, or PL/SQLfunction calls– Cache is shared across statements and sessions on server– Full consistency and proper semantics2x speedup on hit for worst case of trivial query100x speedup on hit for complex queriesStatement hints specify caching - /* result cache */Onlyy for veryy read intensive tables

OCI Consistent Client CacheApplication ServerDatabaseConsistentCachingSimplest queries can speedup: 50x in elapsed time 20x in CPU time Caches query results on client Primarily for caching small (10s or 100s of KB) readread-intensiveintensive tables– Queries where network overhead dominates– e.g. lookup tables CacheCis ffully consistent– Coherence messages bundled into responses to DB callsensure cache remain consistent– Like Cache Fusion extended out to clients

Client Query Result Cache Database Configuration (init.ora)client result cache size 200Mclient result cache lag 5000A li ti ServerApplicationS Client Configuration (sqlnet.ora)OCI QUERY CACHE SIZE 200MOCI QUERY CACHE MAXROWS 20Database Oracle Database 11gg Release 2– Table Annotation: transparent, no code changealter table emp result cache; Oracle Database 11g Release 1– Query Annotation: hint for Caching the Result Setselect /* result cache */ * from employees

Oracle In-Memory Database CacheAccelerator for Oracle Database ApplicationsTelco ServicesFinancial Services IMDB Cache is an Optionpfor the OracleDatabase Enterprise Edition Deployedp y in the applicationpptier, asa real-time, updatable cache forthe Oracle Database Reduced response time andincreased throughput forOracle Database applications Available for download on tenCRM, M & e

Oracle In-Memory Database CacheOptimize In-Memory Database TechnologyClient-ServerApplication Built usingg Oracle TimesTen as arelational cache database Entire database in memory Standard SQL with JDBC, ODBC, OCI,P *C PL/SQLPro*C, Persistent and durable Fast, consistent response times Cross-tierCross tier High Availability Cache Oracle Database tables in theapplication-tierTimesTenCli t mesTenLib iLibrariesJDBC / ODBC / OCI / PLSQLCheckpointFilesLogFilesMid-TierServer GroupsGoff relatedl t d tablest bl All or subset of rows and columnsDatabaseTier

Oracle In-Memory Database CacheLow-Cost Extreme Performance in the Middle-tier Scalable cache grid ReadRead-onlyonly and updatable caches Access cache tables like regular databasetables Joins/search,, insert/update/deletep Flexible caching options Pre-load or dynamic load data Local cache groups for consistentresponse time Global cache groups for locationtransparency and data sharingacross all cache nodes Automatic data synchronization with theOracle seCacheAutomaticSynchronizationUpdates

Oracle In Memory Database CacheApplication DevelopmentODBCOCSQLttClassesOCI, Pro*CC/C PL/SQLLanguagesJ2EE App ServersIMDB CACHENew in 11g release PL/SQL, Oracle Call Interface (OCI) and Pro*C SupportPlanned for CY2010 ODP.NETODP NET data provider,provider PHPOR Mapping

Order Matching ApplicationDemo Three different types of transactions:– Place market order– Place limit order– Process quote Business logic implemented inPL/SQL stored procedures Applicationppwritten in Java Execute 1000 times in one thread– Place an order– Process a quoteStartDemoPL/SQLExecuted onOOraclel abaseCachePL/SQLExecuted inIMDB Cache

Data Growth Impacts PerformanceSource: HP Presentation, Enterprise Information World