Oracle Data Provider For 11g

3y ago
75 Views
3 Downloads
134.01 KB
15 Pages
Last View : 2d ago
Last Download : 2m ago
Upload by : Aiyana Dorn
Transcription

Oracle Data ProviderFor .NET 11gAn Oracle Technical White PaperOctober 2007

Oracle Data Provider For .NET 11gIntroduction . 3Oracle Data Provider for .NET . 4Performance . 4Connection Pooling and Statement Caching . 4Controlling Data Fetch Size . 5Optimizing LOB Data. 5Array Data . 664-bit .NET Framework . 6Performance – New for ODP.NET 11g . 6Client Result Cache . 6Faster LOB Fetching . 7Enhanced Statement Caching. 7Database Change Notification. 8Real Application Clusters (RAC). 10XML Features. 11ADO.NET 2.0 . 12Native Oracle Types. 13Other Major Features. 13Conclusion. 14Oracle Data Provider For .NET 11gPage 2

Oracle Data Provider For .NET 11gINTRODUCTIONOne of the great benefits of using Oracle products is their support for multipleprogramming frameworks. By supporting .NET, Java/J2EE, PHP, and C/C applications, all developers can use Oracle’s advanced database features, providingtrue flexibility for development organizations. Each of Oracle’s data access driversis designed to maximize performance of its respective framework and to enableaccess to the latest database features.Within the .NET realm, Oracle offers a multitude of products to developapplications with the Oracle database, including Oracle Developer Tools for VisualStudio .NET, Oracle Database Extensions for .NET, Oracle Providers forASP.NET, and Oracle Data Provider for .NET (ODP.NET).ODP.NET is a native .NET data access provider for Oracle databases. It offersstandard ADO.NET data access for .NET Framework 1.x, 2.0, and higher. Fordevelopers who have used another ADO.NET provider, basic ODP.NET dataaccess requires very little new to learn. It can be used with existing constructs, suchas the Microsoft Data Access Application Blocks (DAAB) for .NET. As such,ADO.NET developers can start using Oracle data sources very quickly.While ODP.NET shares common basic elements with other ADO.NET providers,ODP.NET’s unique value is its tight integration with the Oracle database.ODP.NET exposes many of the database’s unique capabilities, such as RealApplication Clusters (RAC) tuning, advanced security, and complex data types, to.NET developers. These features allow .NET middle-tiers to take advantage of theOracle database’s unique capabilities.This white paper focuses on ODP.NET data access and its unique capabilities,discussing .NET development features that are part of Oracle Database 11g andprior releases. Many ODP.NET 11g features are enabled with older Oracledatabase server releases, such as the Oracle Database 10g and Oracle Database 9i.This white paper does not cover ODP.NET features released after OracleDatabase 11g on Windows, such as ODP.NET 11.1.0.6.20.Oracle Data Provider For .NET 11gPage 3

ORACLE DATA PROVIDER FOR .NETThe Oracle Data Provider for .NET enablesfast data access performance to Oracledatabases. It supports the latest databasefunctionality available with OracleDatabase 11g.As with other .NET data providers, any .NET application, including C# .NET,Visual Basic .NET, and ASP.NET, can call ODP.NET. While most commonlyemployed in middle-tiers, ODP.NET can also be used within the database serveritself via .NET stored procedures.ODP.NET offers the best performance and greatest access to Oracle databasefeatures than any other .NET data provider. ODP.NET was designed specificallyto maximize Oracle database’s capabilities.ODP.NET 11g introduces new performance features available in Oracle Database11g, as well as enhance existing functionality in the Oracle database. As such, alldevelopers will benefit using the latest ODP.NET version whether they aredeploying new database applications or enhancing existing applications. Some ofthese unique Oracle features include data access performance tuning; databasechange notification; RAC connection pooling; XML support; native Oracle datatype support; and many other features, which will be described further in thispaper.PerformanceODP.NET includes numerous performancetuning features to optimize data retrievaland data changes. Some of these tuningoptions include connection pooling;statement caching; using LOB data types;and employing PL/SQL associative arrays.64-bit ODP.NET is supported on Windowsx64 and Windows Itanium.One of ODP.NET's key differentiators over other providers is its out-of-the-boxperformance and numerous tuning options. Under the covers, numerousoptimizations automatically ensure fast .NET access to Oracle data sources withoutrequiring any specific performance coding. In addition, ODP.NET has manytunable parameters for specific data retrieval and data update scenarios. Many ofthese optimizations were developed for retrieving and manipulating historicallylarge data types, such as LOBs and REF Cursors.Connection Pooling and Statement CachingOne of the most widely used performance optimizations is connection pooling,which is critical for applications with large numbers of users that connect anddisconnect from the database. ODP.NET creates a pool of connections withtunable settings that include connection lifetime and timeout, minimum andmaximum pool sizes, and the numbers of connections to increment or decrementfrom the pool at a time. These parameters give developers greater control overhow their application handles large user populations and the changes in thosepopulations over time. This ultimately leads to better application response timeand quality of service for end users.If a particular query or PL/SQL statement is executed multiple times, ODP.NETcan use statement caching to speed statement execution. By caching the servercursor created during the initial statement execution, statement caching eliminatesthe need to re-parse each statement before subsequent executions. Eachsubsequent statement execution reuses the saved parsed information, and thenexecutes the statement. The result set data itself is not cached, only the parsedstatement information. ODP.NET will still retrieve the latest data from theOracle Data Provider For .NET 11gPage 4

database server. Statement caching just allows these queries to be executed morequickly.When employing statement caching, SQL or PL/SQL statements should useparameters rather than literal values. Doing so takes full advantage of statementcaching since parsed information from parameterized statements can be reusedeven if the parameter values change in subsequent executions. If literal values wereused instead and those literal values changed, the parsed information could not bereused and the database would need to parse the statement anew.By default, ODP.NET will cache the last ten executed statements. The number ofstatements to cache and which statements to cache can be configured at theapplication level or machine level.Controlling Data Fetch SizeTo tune data retrieval performance, ODP.NET can specify a set amount of data toreturn for each database round trip. Many times, a developer may not need toretrieve the data queried all at once. The end user may be consuming portions ofthe data over a period of time.The query’s data fetches can be spaced in distinct chunks defined by the developerthrough two ODP.NET OracleCommand properties: FetchSize and RowSize.FetchSize tells ODP.NET how much data to retrieve per database roundtrip.RowSize indicates how large each row of data is. RowSize is a read-only propertythat is set after a query is executed. If a developer wishes to fetch ten rows of dataper database roundtrip, all that is required is to set FetchSize equal to ten multipliedby RowSize. The wonderful thing about RowSize is that its value can bedetermined at runtime. Consequently, if there is a schema change or a querychange in the future, there is no need to modify the code to ensure ten rows of dataare fetched per round trip.Optimizing LOB DataA similar fetch size tuning feature exists with LOB data types. These data types areused to store images and documents, which can sometimes be in the range ofgigabytes. For LOB applications, performance is often a key issue due to LOB’spotential data size and how LOB data is consumed. Sending gigabytes of databetween the server and client can clog a network unless data retrieval is handledintelligently.With ODP.NET, developers can specify how LOB data should be retrieved. UponLOB query execution, developers can choose to fetch all the LOB data immediatelyor defer the LOB fetch until the user attempts to read the data. The end user maynot need to read the data directly after the query execution. If developers chooseto defer the LOB fetch, they can then specify how much data to retrieve for eachLOB read call. If the end user only needs to read 10KB of data at a time,developers can retrieve just 10KB of data for each LOB read. This optimizes hownetworking resources between the server and client are used.Oracle Data Provider For .NET 11gPage 5

Moreover, ODP.NET developers can retrieve any portion of a LOB via randomaccess. Perhaps the end user may need only the last 100MB of data from a 1GBLOB. Developers can tune LOB retrieval to only fetch the last 100MB withoutreturning data from the first 900MB to the client. These tuning options provide.NET developers the flexibility to build better performing applications.Because LOB data can often be large, by default, the LOB data fetch is deferredafter a query is executed. When retrieving many sets of large LOBs, this behavior isoptimal to prevent overloading the network with LOB data delivered to the client.However, for small LOBs, this behavior can be slow, causing more database roundtrips than is necessary.To allow all the small LOB data to be fetched immediately, ODP.NET has anInitialLOBFetchSize property on the OracleCommand and OracleDataReaderclasses. If InitialLOBFetchSize is set to a value greater than zero, the initial LOBdata from all the LOBs queried is fetched in one round trip up to the number ofcharacters or bytes that is specified in this property. For instance, ifInitialLOBFetchSize were set to 10 KB, then the first 10 KB of all the LOBsselected would be retrieved to the client in one database round trip. This cansignificantly speed up applications consuming lots of small LOBs.Array DataOne of ODP.NET’s unique features is the ability to pass arrays between thedatabase and .NET Framework. Arrays make sharing large sets of data of the samedata type between the database and client much easier. ODP.NET uses PL/SQLassociative arrays in the database to pass data into and out of .NET arrays.64-bit .NET FrameworkWith 64-bit .NET Framework, .NET developers have access to more scalable andhigh-performing hardware systems. They have a choice between AMD64 and IntelEM64T processors for Windows x64 and Itanium processors for WindowsItanium. 64-bit systems have the capability to address larger amounts of memorydirectly than 32-bit systems can. They include optimized hardware components forhigh performance computing. Beginning with the 10.2.0.3 release, ODP.NETsupports both 64-bit .NET Frameworks with a native 64-bit data access driver foreach platform. Developers can now deploy their ODP.NET mid-tier as a 64-bitapplication to take advantage of the more scalable hardware.Performance – New for ODP.NET 11gNew Oracle Database 11g features areavailable that enhance ODP.NETperformance. These features include aclient result cache, faster LOB fetching,and faster performance with statementcaching.Oracle Database 11g introduces new performance optimizations, many of which.NET application developers can use without any changes to their existing clientcode. These new features include a client result cache, faster LOB retrievals, andenhanced statement caching.Client Result CacheWith Oracle Database 11g server and client, ODP.NET applications can use theOracle client result cache to improve response times of repeatedly executed queries.Oracle Data Provider For .NET 11gPage 6

This feature enables client-side caching of SQL query result sets in memory. Theclient result cache is completely transparent to ODP.NET applications, and itscache of result set data is automatically kept consistent with any session or databaseserver side changes that would alter the results.NET applications calling the same query multiple times see improved performancesince query results are retrieved locally. Local client processing is faster thanmaking database round trips to re-execute a query and fetch results. If applicationsare frequently running the same queries, they will experience a significantperformance improvement when their results are cached on the client, as well as areduction in database server load.On the database server, the client cache reduces the server CPU and network trafficload that would have been consumed for processing and returning the queryresults, thereby improving server scalability. ODP.NET statements from multiplesessions can match the same cached result set in the client process memory if theyhave similar schema, SQL text, bind values, and session settings. Otherwise, thequery execution occurs on the server. This means that multiple ODP.NET usersall have access to the same result cache, which minimizes cache redundancies andsaves memory.Because the client cache automatically stays consistent with database server data,developers do not need to write code to ensure the cache and server remain insynch. If a server change occurs that would invalidate the client-cached data, theOracle client will automatically invalidate the cache and update it the next time thequery is executed.Faster LOB FetchingODP.NET 11g improves the performance of small-sized LOB retrieval by reducingthe number of round-trips to the database required for pre-fetching the LOB data,length, and chunk size. This enhancement is available beginning with OracleDatabase 11g for use with either traditional LOBs or SecureFiles. Thisenhancement is transparent to the developer. It can be used like any other LOBdata type without any code changes to existing ODP.NET LOB code.Enhanced Statement CachingODP.NET 11g enhances the existing caching statement caching infrastructure tonow cache ODP.NET parameter contexts. This enhancement works with anycurrently supported Oracle database server version. .NET developers will see aperformance improvement when executing statement-cached queries. Thisenhancement is transparent to developers, requiring no code changes.Oracle Data Provider For .NET 11gPage 7

Database Change NotificationA constant challenge for client side cachesis keeping the data in synch with serverdata changes. Using database changenotification, ODP.NET clients are alertedwhen data on the server is modified, evenif there is no active connection back to thedatabase. This allows the client to ensureits data cache stays synchronized with thedatabase.Database change notification enables client applications to receive notificationswhen DML or DDL changes are made to a database object of interest, even whenthe client no longer has a connection to the database server. .NET developers cannow cache their data on the middle-tier without having to worry about the cacheddata becoming out of synch with the database. If a change happens to one of thecached data objects or rows of data, then ODP.NET will receive a notificationfrom the database. This feature can be used in the .NET Framework 1.x, 2.0, andhigher releases.To use database change notification, the client application registers a query with thedatabase. When a query has dependencies on underlying database objects and achange to a dependent object is committed, the database publishes a changenotification to the client application. The notification only contains metadata aboutwhat data or objects changed; it does not contain the changed data. .NETdevelopers can create a client event handler to reissue the registered query to obtainthe changed data.Database change notification is particularly useful for applications that use cachedresults. Traditionally, data caching by itself is effective at improving applicationscalability by allowing rapid access to data without making expensive roundtrips tothe database. But this scalability comes with a tradeoff, as there is no longer aguarantee that the data remains consistent with the database server after the initialquery. Thus, the cached client data runs the risk of becoming stale.Database change notification solves the stale data cache problem. Althoughdatabase change notification is similar to a trigger in that it responds to a particularevent, a trigger takes action immediately, whereas a database notification is just analert, not an action. It is up to the application to determine what action, if any, toundertake and when. The application can immediately refresh the stale objects,postpone the refresh, or ignore the notification. Each .NET application may wantto respond differently to a particular database change. Moreover, as additionalapplications are added to the database, it is often easier modifying the clientapplication’s event handler than modifying a database trigger. Modifying the triggermay require re-testing how existing applications work with the new database triggercode, while modifying just the new .NET application better isolates the testingboundaries.Web applications often cache a variety of data, not all of which needs to be updatedin real time. For example, a weather forecast may only be updated periodically.End users don’t need to query the database every time the web page is visited.Since many people will be requesting the same data, application performance andscalability are greatly enhanced by caching the results and retrieving the data fromthe cache. At some point, the weather forecast is updated and the cache must berefreshed. This may be done the instant the current weather forecast in thedatabase server has changed.Oracle Data Provider For .NET 11gPage 8

To receive database change notifications requires the database administrator togrant the CHANGE NOTIFICATION privilege to the application user. Afterconnecting to the database, .NET users can then register their specific queries ofinterest for change notification. The developer creates a .NET client-side eventhandler to direct what the application should do upon receiving a database changenotification. Usually, the event handler will re-query the database server andrefresh the cache.The following ODP.NET classes are used when building change notificationapplications: OracleDependency – Creates a dependency between an application and anOracle database. It enables the application

ASP.NET, and Oracle Data Provider for .NET (ODP.NET). ODP.NET is a native .NET data access provider for Oracle databases. It offers standard ADO.NET data access for .NET Framework 1.x, 2.0, and higher. For developers who have used another ADO.NET provider, basic ODP.NET data access requires very little new to learn.

Related Documents:

OBIA 11.1.1.10.1 OBIEE 11g ODI 11g Oracle DB 12.2 Out of Place Migration In Place Upgrade Out of Place Migration WebLogic 11g OAS 5.5 OBIA 11.1.1.10.2 OBIEE 11g ODI 11g DW WebLogic 11g OBIA

PeopleSoft Oracle JD Edwards Oracle Siebel Oracle Xtra Large Model Payroll E-Business Suite Oracle Middleware Performance Oracle Database JDE Enterprise One 9.1 Oracle VM 2.2 2,000 Users TPC-C Oracle 11g C240 M3 TPC-C Oracle DB 11g & OEL 1,244,550 OPTS/Sec C250 M2 Oracle E-Business Suite M

Oracle Advanced Analytics (Oracle Data Mining and Oracle R Enterprise) Data Warehousing Oracle OLAP, Oracle Spatial, OBIEE Expert presenters at major Oracle conferences www.vlamis.com (blog, papers, newsletters, services) Co-author of book “Oracle Essbase & Oracle OLAP” Beta tester for OBIEE 11g, Oracle 12c (in-memory)

Oracle 11g New Features for Administrators . Summary Sheets . Version. 2.3 . . Oracle Database 11g New Features for DBAs and Developers, by Sam R. Alapati and Charles Kim, Apress, ISBN: 978-1-59059-910-5 . 2 . Book . Oracle Database 11g New Features by Rober G. Freeman, Oracle Press . 3 .

Oracle e-Commerce Gateway, Oracle Business Intelligence System, Oracle Financial Analyzer, Oracle Reports, Oracle Strategic Enterprise Management, Oracle Financials, Oracle Internet Procurement, Oracle Supply Chain, Oracle Call Center, Oracle e-Commerce, Oracle Integration Products & Technologies, Oracle Marketing, Oracle Service,

Oracle E-Business Suite (R12) integration with OID/OAM 11g covers steps to installation OID 11g, OAM 11g, OHS 11g, WebGate 10g. This book also covers integration of OID-OAM, OID-EBS, and OAM-EBS for Single Sign-On including deployment of EBS AccessGate,

Oracle is a registered trademark and Designer/2000, Developer/2000, Oracle7, Oracle8, Oracle Application Object Library, Oracle Applications, Oracle Alert, Oracle Financials, Oracle Workflow, SQL*Forms, SQL*Plus, SQL*Report, Oracle Data Browser, Oracle Forms, Oracle General Ledger, Oracle Human Resources, Oracle Manufacturing, Oracle Reports,

Oracle Exadata 11g Technical Boot Camp Online Training Oracle Exadata 11g Technical Boot Camp - CellCLI, DCLI and ADRCI Oracle Exadata 11g Technical Boot Camp - Sizing for the Database Machine Sample Questions Which Exadata Storage Server users can edit configuration files? A. sys B. guest C. master D. root