Oracle SQL和PL SQL发展生命周期及可视化工具开发说明书

ALM with Visual Studio: SQL and PL/SQL Development, Source Control, and Deployment
Christian Shay
Product Manager, Oracle
<Insert Picture Here>
Program Agenda
SQL and PL/SQL Development Lifecycle
–Overview
–Create Development Database - Oracle Multitenant –Create Users, Roles and Grant Privileges –Creating Schema Objects
–Visual Studio Source Control Integration –PL/SQL and SQL Editing –SQL and Performance Tuning –PL/SQL Debugging in Visual Studio
–Schema Compare and Deployment
.NET Developer Center on OTN
▪/dotnet
▪Free downloads of Visual Studio tools, ▪Whitepapers, Sample code
▪Demo Videos
▪Help Forums
▪New Feature request page
▪Latest Oracle on .NET News
Oracle .NET Development Environment
Visual Studio
Application
Development
Deploy
Deploy
Database
Development
Oracle Data Provider
for .NET
Oracle Developer Tools for Visual Studio
Oracle Database Extensions for .NET Oracle Providers for
.NET
Framework
Web or
Client/Server
Oracle Application Development Lifecycle
.NET Coding PL/SQL and
SQL
Editing/Debug
Source
Control
Deploy Create
Development Database Tune
Test
Application Development Lifecycle Overview
▪Oracle Developer Tools for Visual Studio
–Tightly integrated “Add-in” for Visual Studio 2012 and 2010▪
– 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
–/dotnet
Create Development Database
▪SQL Plus script
▪Oracle Multitenant
Multitenant Architecture
Components of a Multitenant Container Database (CDB)
Pluggable Databases (PDBs) PDBs Root CDB
Multitenant for Test and Development
Clone production, plug into development. Clone/destroy test instances
PDB Functionality in Server Explorer
▪Pluggable Databases Node
–New pluggable database: Fast provisioning from the seed –Plug: Plug in an XML file and some DBFs
▪Pluggable Databases Node
–Clone: Fast copy
–Unplug: Removes PDB from CDB and creates XML manifest –Open, Close: Equivalent to Startup and Shutdown
–Delete: Removes PDB from CDB and deletes files
Connect to PDBs in Server Explorer
▪Server Explorer connections automatically made to new or cloned pluggable databases
–TNSNAMES.ORA updated and connection to ADMIN made
▪No Server Explorer connection made when plugging in –Need to update TNSNAMES.ORA
–Connect descriptor same as container, but
▪service_name = pdb name
Oracle Multitenant with
▪ works implicitly with PDBs
–Connect to the Service_name of the PDB
–Hostname and port are same as container
▪Requires Oracle Database 12c and ODAC 12c
D E M O N S T R A T I O N
Multitenant
Create Users, Roles and Grant Privileges
▪User Designer
▪Role Designer
▪Grant and Revoke Privileges Dialog
D E M O N S T R A T I O N User/Role Designer Privileges Wizard
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) ▪Query Window
▪Run SQL*Plus Script
D E M O N S T R A T I O N Creating Schema
Objects
Visual Studio Source Control Integration
▪Configure Source Control in Visual Studio
▪Oracle Database Project
▪Generate Create Script to Source Control
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
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
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
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
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
D E M O N S T R A T I O N
SQL Tuning
Advisor
Oracle Performance Analyzer
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
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
AWR and ADDM
•AWR Snapshots
–A collection of database statistics and performance metrics
gathered at a single point in time.
–Two snapshots make up one analysis time period
–Oracle Database automatically takes periodic snapshots •ADDM Tasks
–An analysis of Oracle database performance over a period of time
–Requires two AWR Snapshots to define that time period
AWR and ADDM in Visual Studio
•AWR Snapshots
–New AWR Snapshot Dialog
–AWR Snapshots Node in Server Explorer
•ADDM Tasks
–New ADDM Task Dialog
–ADDM Tasks Node in Server Explorer
–ADDM Task results are displayed in Oracle Performance Analyzer
D E M O N S T R A T I O N Performance
Analyzer
Oracle Performance Analyzer
•“Manual” Method, instead of using Timer
–Run your application
–Create a Snapshot, via “New AWR Snapshot” dialog
–Wait desired time period
–Create second snapshot
–Create ADDM Task, via “New ADDM Task” dialog
–View Results. If insufficient database time, wait a while and create one more snapshot and another ADDM task
Oracle Performance Analyzer
•Manual Method Pros
–Can close Visual Studio during time period
–If insufficient database time, you can extend the time period by creating one more snapshot, without having to wait entire
duration again
–Can use database created AWR Snapshots if desired
–Can elect to monitor all statistics when creating the snapshots
▪GRANT debug privileges as SYSDBA
–9.2 or later: GRANT DEBUG ANY PROCEDURE TO username, and
–10g or later also requires: GRANT DEBUG CONNECT
SESSION TO username
▪Set port range and IP 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
▪New requirement in 12c:
–SYSDBA must grant ACL access on
▪IP Address
▪Port Range
▪Schema name
▪Use new “Grant Debugging Privileges dialog”
–Right click on Schema name to be granted both debugging roles and the ACL privileges
▪Or issue this PL/SQL as SYSDBA:
▪BEGIN
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE( HOST => ‘127.0.0.1',
LOWER_PORT => 65000,
UPPER_PORT => 65300,
ACE => XS$ACE_TYPE(PRIVILEGE_LIST =>
XS$NAME_LIST('jdwp'),
PRINCIPAL_NAME => 'HR',
PRINCIPAL_TYPE => XS_ACL.PTYPE_DB));
END;
Direct Database Debugging
▪Debug directly inside the database, no application code ▪“Step Into” from Server Explorer
▪“Run Debug” from Server Explorer
▪Enter parameters manually
–Not useful with array parameters or complex types
Application Debugging Mode
▪Step from .NET code into PL/SQL and back from one instance of Visual Studio
▪Useful for client server code (not web apps)
▪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
External Application Debugging
▪Dedicated VS instance for debugging PL/SQL only –Use additional VS instance for any .NET code (eg app) ▪10.2 client or later running on ANY platform
▪Set ORA_DEBUG_JDWP in client environment –SET ORA_DEBUG_JDWP=host=mymachine;port=4444
–Set in web app environment BEFORE connecting
▪Start Listener
–Tools-> “Start Oracle External Application Debugger”
D E M O N S T R A T I O N
PL/SQL
Debugging
Deployment
▪Run create scripts
▪Run diff scripts – Schema Compare
▪Plug Development PDB into Production CDB – Oracle Multitenant
Using PDBs for Deployment
▪Unplug from development/test
environment
▪Plug into production environment
Schema Compare Tool in Visual Studio
▪New in Oracle Developer Tools for Visual Studio –ODAC 12c
–Oracle Database 10.2 or higher
▪Compare two schemas in the same or different dbs –Visually inspect differences using UI
–Generate a diff script for deployment purposes
–Reverse schema compare to “rollback” changes
–Can compare down to granularity of schema type (eg compare all tables, or all packages, etc)
Schema Compare – View Differences
Schema Compare
Typical Use Case of Visual Studio Developer
▪Development schema identical to production schema ▪Development schema evolves to meet needs of app ▪Use Schema Compare to inspect what has changed ▪Use Schema Compare to generate diff script
▪Deploy diff script with app
D E M O N S T R A T I O N
Schema
Compare
Upcoming .NET Sessions
Thursday Sept 26
▪Hands-on Lab: Building .NET Applications with Oracle –11:00 AM - 1:30 PM, Marriott Marquis - Salon 3/4
▪Oracle and .NET: Best Practices for Performance and Deployment
–2:00 PM - 3:00 PM, Marriott Marquis - Golden Gate C2
Additional Oracle .NET Resources
▪OTN .NET Developer Center
–/dotnet
▪Twitter
–@OracleDOTNET
▪YouTube
–/user/OracleDOTNETTeam ▪For more questions
–*************************。