1 / 66

<Insert Picture Here>

<Insert Picture Here>. Oracle in the .NET Environment. Tomer Avissar Senior Support consultant Tomer.avissar@oracle.com http://oracleatdotnet.blogspot.com. Agenda. <Insert Picture Here>. Oracle and Microsoft Oracle and .NET Getting Started PL/SQL Development Lifecycle with Visual Studio

carina
Download Presentation

<Insert Picture Here>

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. <Insert Picture Here> Oracle in the .NET Environment Tomer Avissar Senior Support consultant Tomer.avissar@oracle.com http://oracleatdotnet.blogspot.com

  2. Agenda <Insert Picture Here> • Oracle and Microsoft • Oracle and .NET Getting Started • PL/SQL Development Lifecycle with Visual Studio • Oracle Data Provider for .NET (ODP.NET) • Oracle Providers for ASP.NET • .NET Stored Procedures

  3. <Insert Picture Here> Oracle and Microsoft – Collaborating Today and in the Future

  4. Oracle’s Commitment to .NET Visual Studio .NET Plug-ins .NET Stored Procedures ODAC 2006 64-bit ODP.NET ODP.NET ODP.NET ODAC 11g 2002 2003 Q2 2005 Q3 2005 Q3 2006 Q1 2007 2007, 2008 .NET 1.0 .NET 1.1 VS 2003 .NET 2.0 & VS 2005 .NET 3.0 Windows Vista, Win2k8

  5. Engineering • Close collaboration with MS Engineering teams • Access to pre-release drops of Microsoft products Marketing • VSIP Premier Marketing partner • Sponsorship at respective events • Joint training, road shows, collateral etc. Support • Microsoft Premier Support Agreement • Collaboration of support teams Relationship with Microsoft

  6. <Insert Picture Here> Dewey Forrester Director, Platform Evangelism Microsoft “We do not let the areas of competition get in the way of the areas in which we can bring a great combined offering to market.”

  7. <Insert Picture Here> Oracle and .NET – Getting Started

  8. Oracle Technology Network – .NET Developer Center • http://otn.oracle.com/dotnet • Free downloads of Visual Studio tools, ODP.NET and Oracle Database XE. • Whitepapers • Sample Code • Demo Videos • Help Forums • “How to” step by step tutorials • Latest Oracle on .NET News

  9. .NET Development Environment Support Oracle Providers for ASP.NET Oracle Developer Tools for VS.NET IIS (ASP.NET)‏ MTS/ COM+ C++, C#, VB .NET Application Develop Deploy Visual Studio Environment Application IIS Deploy Database Development Oracle Data Provider for .NET Oracle Database Extensions for .NET

  10. Develop on Windows, Database on any Platform Windows ODT and ODP.NET Linux Visual Studio Environment Unix Windows Other Oracle 11g,10g, 9i, or 8i

  11. <Insert Picture Here> PL/SQL Development Lifecycle with Visual Studio

  12. SQL and PL/SQL Development Lifecycle • Create Schema Objects, PL/SQL procedures, functions, packages • Oracle Wizards – eg Oracle Stored Procedure Wizard, Table Designer • Query Window – Ad Hoc SQL • Microsoft Query Designer • Run SQL*Plus Script for existing scripts • Create Roles and grant privileges to them • Grant and Revoke Privileges Wizard • Create SQL and PL/SQL scripts • Generate Create Script from existing schema objects • Store scripts in source control • Oracle Database Project

  13. SQL and PL/SQL Development Lifecycle • Edit SQL and PL/SQL Scripts • Oracle SQL Editor – file based • Oracle PL/SQL Editor – database based • Tune SQL • Explain plan feature in Oracle Query Window • Create client side .NET code • (C#, VB.NET, ASP.NET)‏ • Use Oracle Data Provider for .NET to call PL/SQL • Debug .NET and PL/SQL together • PL/SQL Debugger in Visual Studio • Deploy

  14. PL/SQL Development Lifecycle • Oracle Developer Tools for Visual Studio • Tightly integrated “Add-in” for Visual Studio 2008/2005 and Visual Studio .NET 2003 • 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 today • http://otn.oracle.com/dotnet

  15. PL/SQL Development LifecycleOracle Developer Tools for Visual Studio • Fully integrated with Visual Studio 2008 and 2005 • Fully integrated with Visual Studio .NET 2003 • Oracle Explorer • Automatic code generation – Winform and ASP.NET • Oracle Wizards and Designers • Oracle Data Window • Oracle Database Project • Edit and Run SQL scripts • SQL*Plus is built in • Source control integration

  16. PL/SQL Development LifecycleOracle Developer Tools for Visual Studio • Integrated PL/SQL Editor and Debugger • Oracle Query Window • Ad Hoc SQL • Explain Plan • .NET Stored Procedure Deployment • Integrated help system – SQL, PL/SQL keywords

  17. D E M O N S T R A T I O N PL/SQL Lifecycle

  18. Create Schema Objects • Oracle Wizards • Table Designer • PL/SQL Package Wizard • Table Import Wizard to import data • ..many others (one Wizard/Designer for every schema type)‏ • Run SQL*Plus Script

  19. D E M O N S T R A T I O N Creating Schema Objects

  20. Create Roles and Grant Privileges • Oracle Query Window • Grant and Revoke Privileges Dialog

  21. D E M O N S T R A T I O N Privileges Wizard

  22. Visual Studio Source Control Integration • Configuring Source Control in Visual Studio • Generate Create Script to Source Control • Oracle Database Project Features

  23. D E M O N S T R A T I O N Source Control Integration

  24. PL/SQL and SQL Editing • Oracle PL/SQL Editor – database based • Collapsible Regions • Syntax Coloring • Integrated Online Help • Supports Debugging • Oracle SQL Editor – file based

  25. D E M O N S T R A T I O N SQL and PL/SQL Editors

  26. SQL Tuning • Explain Plan feature in Oracle Query Window • Options page to choose what metrics to see

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

  28. PL/SQL Debugging in Visual Studio • Direct Database Debugging • “Step into” a SP directly from Server Explorer • Application Debugging • Step from application code (eg C# or ASP.NET code) directly into PL/SQL and then return back • External Application Debugging • Set breakpoints and debug SPs called by external applications running on other machines or platforms

  29. PL/SQL Debugging Configuration • GRANT debug privileges as SYSDBA • 9.2, 10g: GRANT DEBUG ANY PROCEDURE TO username • 10g: GRANT DEBUG CONNECT SESSION TO username • Set port range in Debugging Options page • Tools -> Options->Oracle Developer Tools • Compile PL/SQL units for Debug • Via menu in PL/SQL editor or in Oracle Explorer

  30. Direct Database Debugging • “Step Into” from Server Explorer • “Run Debug” from Server Explorer • Enter parameters manually

  31. Application Debugging Mode • Step from .NET code into PL/SQL and back • Check off “Tools -> Oracle Application Debugging” • ODT automatically starts listener using port in range given in Options page • Uncheck "Enable the Visual Studio hosting process" in the .NET Project Properties Debug tab

  32. D E M O N S T R A T I O N PL/SQL Debugging

  33. <Insert Picture Here> Oracle Data Provider for .NET (ODP.NET)‏

  34. ODP.NET - Basics • Supports Oracle8i and higher Oracle DB Server • DB server can be on any operating system • Uses 9.2 client or higher • Database client on Windows • Supports 32-bit, 64-bit x64, and 64-bit Itanium • Available as free download • http://otn.oracle.com/dotnet

  35. ODP.NET Object Model Disconnected Layer Connected Layer (ODP.NET)‏ Data Layer Oracle DataAdapter Oracle CommandBuilder DataSet Oracle DataReader Oracle Command Oracle Transaction Oracle Connection Oracle

  36. D E M O N S T R A T I O N ODP.NET

  37. ODP.NET Features • Full PL/SQL support • Packaged, non-packaged, anonymous, autonomous • Batch SQL available with anonymous PL/SQL • Native Oracle data types • REF Cursors, LOBs, XMLType, TimeStamp, etc. • Safe type mapping to .NET data types • Connection pooling • Min, Max, Timeout, Lifetime, Increment, Decrement

  38. ODP.NET Features • Transactions • Enterprise Services via Oracle Services for MTS • Local (Implicit and Explicit)‏ • System.Transactions • Savepoints • Parameters • PL/SQL Associative Array data type • LOBs • Ability to pre-fetch or defer LOB retrieval

  39. ODP.NET Features • XML • XMLType data type • Query and Save XML from the DB • Both relational and object-relational • Interoperate with MS XML APIs (e.g. XmlReader)‏ • Supports XSLT, XPATH, and XML Schema • RAC • Automatic load balancing • Automatic connection cleanup

  40. ODP.NET Features • Database Change Notification • Performance Tuning • Statement caching • FetchSize and RowSize • 64-bit AMD64/EM64T and Itanium • Security • Proxy authentication • Application security context

  41. <Insert Picture Here> ODP.NET Performance Features

  42. Connection Pool • Min Pool Size = # connections at steady state or average load • Max Pool Size = # connections at maximum capacity • Min and Max Pool Size always obeyed ahead of other CP parameters • Incr Pool Size = connection growth rate from steady state • Decr Pool Size = connection decrease rate from steady state • Pool size checked every 3 minutes • Validate Connection = should be false in most cases • Extra round trip • Writing your own exception handler is likely faster

  43. ODP.NET Connection Pool Monitoring • Monitor CP via ODP.NET performance counters and/or tracing • Counters available with ODP.NET 11.1.0.6.20 or higher

  44. Multiple Users – One Connection Pool to Unite Them All • Use proxy authentication • Effective for uniquely-identified users or groups of users, while maintaining CP benefits • Has numerous security benefits • Creates two sessions, but uses one connection • Use application context • Distinguishes individuals in connection pool with a string identifier • Set OracleConnection.ClientId, ActionName, and/or ModuleName • Lighter weight than proxy authentication, but does not provide as fine grained security features

  45. Commands – Bind Variables • Bind variables • Prevents re-parsing of frequently executed statements • Works with SQL and PL/SQL statements • Improves subsequent command executions • Literal value changes in commands 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

  46. Commands – Statement Caching • Statement caching • Retains 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 used with bind variables • From ODP.NET 10.2.0.2.20, on by default • Caches the last ten executed statements • Developer can choose which statements to cache

  47. D E M O N S T R A T I O N Statement Caching

  48. 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

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

More Related