1 / 61

Oracle and .NET: Best Practices for Performance and Deployment

Oracle and .NET: Best Practices for Performance and Deployment. Christian Shay Principal Product Manager, Oracle. Program Agenda. Optimization Process Optimizing ODP.NET Performance Connections Data Retrieval and Updates ODP.NET Data Types Caching ODP.NET Deployment. Program Agenda.

glynis
Download Presentation

Oracle and .NET: Best Practices for Performance and Deployment

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Oracle and .NET: Best Practices for Performance and Deployment Christian ShayPrincipal Product Manager, Oracle

  2. Program Agenda • Optimization Process • Optimizing ODP.NET Performance • Connections • Data Retrieval and Updates • ODP.NET Data Types • Caching • ODP.NET Deployment

  3. Program Agenda • Optimizing Oracle DB Performance from Visual Studio • SQL Tuning Advisor • Oracle Performance Analyzer

  4. Oracle’s .NET Products • Oracle Developer Tools for Visual Studio • Tightly integrated “Add-in” for Visual Studio 2012, 2010, 2008 • Oracle Data Provider for .NET (ODP.NET) • ADO.NET compliant data provider • Native access to Oracle database • Utilize advanced Oracle Database features • RAC, performance, security, data types, XML, etc. • Both available for free download: • http://otn.oracle.com/dotnet/

  5. Optimization Process

  6. Oracle .NET Application Performance – Optimization Steps • .NET data access tuning • Use ODP.NET best practices • SQL tuning • Use SQL Tuning Advisor in Visual Studio • Database tuning under real world conditions • Oracle Performance Analyzer in Visual Studio detects issues you have missed • May need to modify application based on findings • Can be used during testing phase or production

  7. Optimizing ODP.NET Performance

  8. Connections • Connection pooling (CP) • OracleConnection by default includes a connection pool • Monitor and tune them using Windows Performance Monitor • Close/Dispose connections (and all objects) explicitly • Do not necessarily rely on the garbage collector • Recommendation applies to all ODP.NET objects

  9. Monitoring Connections • ODP.NET performance counters • Available with ODP.NET 11.1.0.6.20 or higher • Monitor with Windows Performance Monitor • ODP.NET tracing

  10. Connection Management – Clusters and Standbys • RAC automatic connection load balancing • Load Balancing = true • RAC and Data Guard automatic “bad” connection removal • HA Events = true

  11. Commands – Bind Variables • Prevents re-parsing of frequently executed statements • Works with SQL and PL/SQL statements • Improves subsequent command executions • Literal value changes forces a re-parse and re-optimization • Literal values should become bind variables • Executed statements stored in Oracle shared pool • Re-parsing and re-optimization uses CPU and requires shared pool locks

  12. Commands – Statement Caching • Retains previously parsed statement in shared pool • Cursor stays open on the client side for faster re-use • No additional lookup needed on server • Metadata remains on the client • Caches most recently used statements • Works with SQL and PL/SQL statements • Best with bind variables • Self-tuned cache size – on by default • No code changes needed

  13. Commands – Data Retrieval • Control how much data is retrieved per DB roundtrip • Too much data retrieved – excessive client-side memory used • Too little data retrieved – additional round trips • Use OracleCommand.RowSize and OracleDataReader.FetchSize to control result size • RowSize populated after statement execution • Set dynamically at run-time • FetchSize can be set as multiple of RowSize

  14. D E M O N S T R A T I O N Fetch Size and Row Size

  15. Commands – Statement Batching • Use OracleDataAdapter.UpdateBatchSize to batch updates from DataSet • Execute multiple commands in one DB roundtrip • Use anonymous PL/SQL • Useful for disparate or similar statements

  16. Commands – Mass Data Movement with Arrays • PL/SQL associative arrays • Pass large amounts of data between .NET and DB of the same data type • Use parameter array binding • Useful if executing the same statement multiple times • Bind variables are the same, variable values can be different • One execution for each element in the bound array • Remember: PL/SQL associative arrays and parameter array binding are two different concepts

  17. Promotable Transactions • Promote local transactions to distributed at run time • Better application performance • Lower resource usage • On by default • Requirements • First connection to Oracle Database 11g (11.1) or higher • Subsequent connections to any other version or database

  18. ODP.NET Data Types • Avoid unnecessary data type conversions • .NET vs. ODP.NET Types • OracleDataReader Type Accessors • OracleParameter.DbType vs. OracleParameter.OracleDbType • Option to store Oracle data types in DataSet • OracleDataAdapter.ReturnProviderSpecificTypes = true • Available in ADO.NET 2.0

  19. REF Cursors • Retrieve data as needed • Control data retrieved via FetchSize • Fill a DataSet with just a portion of the REF Cursor result • Explicit control over what REF Cursor data is retrieved and how • Defers result set retrieval until needed • Pass REF Cursors back as input stored procedure parameters • OracleRefCursor class

  20. SecureFiles and LOBs • Data retrieval options • Defer retrieval (default) with LOB locator • Retrieve data immediately with OracleCommand.InitialLOBFetchSize • Retrieve a chunk using Read method • Use Search method to find data to be retrieved

  21. SecureFiles and LOBs • Update/Insert/Delete • Modify LOB without retrieving the data to the client side • Uses LOB locator • Use SecureFile data type in Oracle Database 11g

  22. Oracle .NET Caching Solutions • Oracle .NET database caches • Client Result Cache • Continuous Query Notification (CQN) • TimesTen In-Memory Database • Automatically updates/alerts client cache upon server changes • Each serves separate caching requirements

  23. Oracle Client Result Cache • Automatically updating cache of query result sets • Benefits • Easy to use • No code changes required • Snapshot consistent • Cache refreshes without user intervention • More scalability and performance • Data retrieval from client, rather than server • No additional round trips

  24. Oracle Client Result Cache Updates • 1. Upon data change, client receives change notification on subsequent round trip (or max lag) • Invalidation notifications piggyback on existing client round trips • No changes to the cache results yet • 2. Cache waits for next execution to refresh results • Does not initiate an independent round trip • No unnecessary DB traffic • Cache entries do not timeout • Uses Least Recent Used algorithm

  25. Oracle Continuous Query Notification (CQN) • Easy to use automated cache with more programmatic control • Also known as Database Change Notification • Benefits over Client Result Cache • More control over how cache behavior • What if multiple users access the same results? • What if only a subset of the cached data is required? • How long should a query be cached? • Do I want additional logic executed when the cache is refreshed?

  26. Data Change Application Execute() OracleCommand Data Dictionary TABLE Notification Request Add Dependency OnChange OracleDependency Notification Queue Invalidate() Listener

  27. Oracle TimesTen In-Memory Database • Fully featured relational database • Oracle compatible SQL and PL/SQL with ODP.NET • Persistent and durable • Transactions with ACID properties • Flexible options for durability • Exceptional performance • Instantaneous response time, high throughput, embeddable • Memory-Optimized Relational Database

  28. ODP.NET Deployment

  29. ODP.NET Unmanaged Driver Managed Database Unmanaged ODP.NET Unmanaged DLLs ODP.NET Oracle.DataAccess.dll NET OCI Oracle

  30. ODP.NET Fully Managed Database Managed ODP.NET Oracle.ManagedDataAccess.dll Oracle

  31. Managed ODP.NET • 100% managed provider • Assembly • Oracle.ManagedDataAccess.dll • No other Oracle Client files required • Unless using distributed transactions • Namespaces • Oracle.ManagedDataAccess.Client • Oracle.ManagedDataAccess.Types

  32. Distributed Transactions • Requires an additional managed DLL (for now) • Oracle.ManagedDataAccessDTC.dll • If not using distributed transactions, no need to deploy additional DLL • No direct reference to DLL directly • Implicitly loaded • Two versions with same name: 32-bit and x64 • Still must match 32-bit or 64-bit COM calls

  33. Managed ODP.NET Networking Configuration • Option 1: single configuration file deployment • .NET config file contains TNS and sqlnet.ora settings • i.e. Machine.config, web.config, app.config • Option 2: multi-file configuration • .NET config file • Tnsnames.ora and sqlnet.ora • TNS_ADMIN in config or ORA files in working directory • Option 3: no configuration files • Store connect info in Data Source attribute

  34. Managed ODP.NET Networking Configuration • Data source alias in .NET config file. • Data source alias in the tnsnames.ora file at the location specified by 'TNS_ADMIN' in the .NET config file. • Data source alias in the tnsnames.ora file present in the same directory as the .exe. • Data source alias in the tnsnames.ora file present at %TNS_ADMIN% • Data source alias in the tnsnames.ora file present at %ORACLE_HOME%\network\admin • Order of precedence

  35. D E M O N S T R A T I O N Deploying Managed ODP.NET

  36. ODP.NET Unmanaged Driver Managed Database Unmanaged ODP.NET Unmanaged DLLs ODP.NET Oracle.DataAccess.dll NET OCI Oracle

  37. .NET Deployment Platform Target • Select either “x86” or “x64” • Depends on platform target • Ensure ODP.NET version supports that platform • Do not use “AnyCPU” (default) • “AnyCPU” instructs assembly to run natively on current CPU • x86 or x64 • May not be intended ODP.NET platform version installed • Unmanaged ODP.NET

  38. Deployment: Instant Client (IC) Benefits • Control over install process • Xcopy • Fine grain control over installation process • Great for large scale deployments or ISV packaging • OUI – great for small scale deployments • Smaller install size compared to standard install • Xcopy install – smallest footprint • OUI install – small footprint • Unmanaged ODP.NET

  39. Managing Multiple Oracle Homes in .NET • Solution for ODP.NET 10.2.0.4 and higher • ODP.NET reads .NET config files for location of unmanaged Oracle DLLs • Each app can use different Oracle client DLLs even if same ODP.NET managed version is used • Unmanaged ODP.NET

  40. Managing Multiple Oracle Homes in .NET • DLL search order • 1. Current application directory • 2. Application config or web.config • 3. Machine.config • 4. Windows Registry • HKLM\Software\Oracle\ODP.NET\<version>\DllPath • 5. Windows System Path setting • Unmanaged ODP.NET

  41. Optimizing Oracle DB Performance from Visual Studio

  42. Performance Tuning in Visual Studio –SQL Tuning Advisor • Use when designing new SQL statements • Tune ad-hoc SQL statements in Query Window • Tune bad SQL found by Oracle Performance Analyzer • Use if SQL is performing poorly under load

  43. SQL Tuning Advisor • Requirements • ADVISOR privilege • Oracle Database license for Oracle Diagnostic Pack • Oracle Database license for the Oracle Tuning Pack • How to run: • Oracle Query Window “Tune SQL” button • Oracle Performance Monitor – Tune SQL button

  44. SQL Tuning Advisor • Implement Findings Button • Automatically fix the problem for certain finding types • View Report Button • View more details about how to fix a problem

  45. D E M O N S T R A T I O N SQL Tuning Advisor

  46. Performance Tuning in Visual Studio –Oracle Performance Analyzer • Detects performance issues in an application’s use of the database under load • Requirements • SYSDBA • Oracle Database license for Oracle Diagnostic Pack • Can be use during testing • Can be also used on production applications

  47. Oracle Performance Analyzer

  48. Oracle Performance Analyzer • Simple to use • Run your application • Enter amount of time to analyze • Press Start to start timer • Sufficient “database time” required to get results • View findings and actions • Implement recommended actions

  49. AWR and ADDM • Built into the Oracle Database 10g • Automatic Workload Repository (AWR) • Evolution of statspack • Built-in repository • Captures performance statistics at regular intervals • Automatic Database Diagnostic Monitor (ADDM) • Methodically analyses captured AWR stats • Generates recommendations

More Related