660 likes | 886 Views
<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
E N D
<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 • Oracle Data Provider for .NET (ODP.NET) • Oracle Providers for ASP.NET • .NET Stored Procedures
<Insert Picture Here> Oracle and Microsoft – Collaborating Today and in the Future
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
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
<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.”
<Insert Picture Here> Oracle and .NET – Getting Started
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
.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
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
<Insert Picture Here> PL/SQL Development Lifecycle with Visual Studio
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
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
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
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
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
D E M O N S T R A T I O N PL/SQL Lifecycle
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
D E M O N S T R A T I O N Creating Schema Objects
Create Roles and Grant Privileges • Oracle Query Window • Grant and Revoke Privileges Dialog
D E M O N S T R A T I O N Privileges Wizard
Visual Studio Source Control Integration • Configuring Source Control in Visual Studio • Generate Create Script to Source Control • Oracle Database Project Features
D E M O N S T R A T I O N Source Control Integration
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
D E M O N S T R A T I O N SQL and PL/SQL Editors
SQL Tuning • Explain Plan feature in Oracle Query Window • Options page to choose what metrics to see
D E M O N S T R A T I O N SQL Tuning
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
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
Direct Database Debugging • “Step Into” from Server Explorer • “Run Debug” from Server Explorer • Enter parameters manually
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
D E M O N S T R A T I O N PL/SQL Debugging
<Insert Picture Here> Oracle Data Provider for .NET (ODP.NET)
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
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
D E M O N S T R A T I O N ODP.NET
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
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
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
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
<Insert Picture Here> ODP.NET Performance Features
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
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
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
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
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
D E M O N S T R A T I O N Statement Caching
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
D E M O N S T R A T I O N Fetch Size and Row Size