Twin cities oracle users group
Download
1 / 92

Twin Cities Oracle Users Group - PowerPoint PPT Presentation


  • 251 Views
  • Updated On :

Twin Cities Oracle Users Group. A Comparison of SQL Server 2000 and Oracle 9i Databases. Fred Potthoff, Adjunct Faculty Jerry Heath, Resident Faculty Metropolitan State Univ - MIS Dept. January 15, 2004. Introduction. Why Compare the databases?

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Twin Cities Oracle Users Group' - Michelle


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Twin cities oracle users group l.jpg

Twin Cities Oracle Users Group

A Comparison of

SQL Server 2000

and

Oracle 9i Databases

Fred Potthoff, Adjunct Faculty

Jerry Heath, Resident Faculty

Metropolitan State Univ - MIS Dept

January 15, 2004


Introduction l.jpg
Introduction

  • Why Compare the databases?

    • We often run both databases at one site

    • SQL Server is growing up - size / installs


Introduction3 l.jpg
Introduction

  • Who I am - fred.potthoff@metrostate.edu

    • Community Faculty - MIS Dept

    • MBA from UCLA - Computer Info Systems

    • 20+ years with relational databases

      • Developer and DBA

      • Ingres, Teradata/Sharebase, Oracle, SQL Server

  • Who I am - jerry.heath@metrostate.edu

    • Resident Faculty, Associate Professor

    • Formerly at Univ of Washington and Hawaii

    • 15+ years teaching

    • Set Up SQL Server and Oracle Databases



Overview5 l.jpg
Overview

Oracle Had 11% increase in DB sales in 2003

Microsoft had 18% increase in DB sales 2003


Overview 2002 erp apps l.jpg
Overview - 2002 ERP Apps

  • % of Installations Oracle SQLServer

    • SAP 76% 3%

    • PeopleSoft 72% 7%

    • Siebel 81% 4%

  • ERP Market Share

    2001 50% 21%

    2002 54% 21%


Topics of discussion l.jpg
Topics of Discussion

1. Architecture

2. Installation

3. Transactions and Record Locking

4. Performance Tuning

5. Database Management

6. Backup and Recovery

7. Cost and Licensing Arrangements


1 architecture l.jpg
1 - Architecture

  • Definition of Database / Instance

  • Memory Configuration

  • Threads

  • Background Processes

  • File Structure


1a instances l.jpg
1a - Instances

  • Details about this topic

  • Supporting information and examples

  • How it relates to your audience



1b memory configuration l.jpg
1b - Memory Configuration

Oracle

SQL

Server







1e file structure17 l.jpg
1e - File Structure

SQL Server

For Backup Information


2 installation l.jpg
2 - Installation

  • Release Notation

  • Product Types

  • Platforms

  • Install Comparison

    • Oracle

    • SQL Server 2000


2a release notation l.jpg
2a - Release Notation

  • Oracle

    • 9.2.0.1.0

      • Major release

      • Maintenance release

      • Application server release

      • Component specific release

      • Platform specific release

  • SQL Server 2000

    • 8.0

      • Major release

      • Minor release

    • Apply service packs - sp3a


2b product types l.jpg
2b - Product Types

  • Enterprise

  • Standard

  • Licensing:

    • Per Processor

    • Units

      • Oracle - Named User

      • SQL Server 2000 - Client Access License


2c oracle platforms l.jpg
2c - Oracle Platforms

  • Operating System

    • UNIX - IBM’s AIX, HP-UX, Sun Solaris

    • LINUX

    • Windows NT 4.0, service pack 5

    • Windows 2000, service pack 1

    • Windows 2003

    • Windows XP

  • Hardware - IBM, HP, Sun, + Intel

    • Pentium 166 MHz or higher

    • 128 MB RAM - 256 MB better

    • Hard Disk: 140 MB System

      4.5 GB Home (FAT)

      or 2.8 GB Home (NTFS)


2c sql server 2000 platforms l.jpg
2c - SQL Server 2000 Platforms

  • Operating System

    • Windows NT 4.0, service pack 5

    • Windows 2000 Server

    • Windows 2003 Server

  • Hardware

    • Pentium 166 MHz or higher

    • 64 MB RAM - 128 MB better

    • Hard Disk: 95-270 MB (250 usual)

      50 MB min Analysis Serv

      80 MB English Query


2d oracle db creation l.jpg
2d - Oracle DB Creation

  • Oracle Command Line Script or GUI

  • Command Line Script on NT

    • oradim.exe

    • SQL*Plus

    • SQLNet Config

  • GUI

    • Database Configuration Assistant


2d sql server 2000 db creation l.jpg
2d - SQL Server 2000 DB Creation

  • Installation CD

  • Installation wizard on the CD


2d oracle steps l.jpg
2d - Oracle Steps

1. Plan database tables, indexes, estimate space

2. Plan layout of OS files that make DB

3. Consider Oracle Managed Files

4. Select Global Database Name

5. Create / modify server parameter file(s)


2d oracle steps26 l.jpg
2d - Oracle Steps

6. Select DB Character Set

7. Determine time zones supported

8. Set up undo tablespace

9. Develop backup / recovery strategy

10. Startup and shutdown databases


2d sql server 2000 steps l.jpg
2d - SQL Server 2000 Steps

1. Plan database tables, indexes, estimate space

2. Plan layout of OS files that make DB

Possible Layout:

Drive RAID Contents

C: 1 OS+installed apps

E: 1or 10 System DB Files

F: 1 Filegroup “Data” Files

G: 1 Filegroup “Index” Files

H: 1 Transaction Log Files


2d sql server 2000 steps28 l.jpg
2d - SQL Server 2000 Steps

3. Select Instance name or Default name

4. Set up account in which services run

5. Install Collation (language), sort order

6. Is Full Text Search and English Query Required?


2d sql server 2000 steps29 l.jpg
2d - SQL Server 2000 Steps

7. Determine Authentication Mode

8. Set up Networking Services

9. Develop backup / recovery strategy

10. Startup and shutdown databases

11. Option - Install OLAP, Data Mining Analysis Services


3 transactions record locking l.jpg
3 - Transactions / Record Locking

  • Rollback Segments / Redo Logs

  • Record Locks

  • T-SQL vs SQL-Plus Transactions


3a rollback segments redo logs l.jpg
3a - Rollback Segments / Redo Logs

Oracle

  • Undo Tablespace in 9i

  • Redo Logs

Note: DBA can still use Rollback Segments, if desired


3a rollback segments redo logs32 l.jpg
3a - Rollback Segments / Redo Logs

SQL Server

  • Transaction Log is present in each Database of each Instance

  • Equivalent of Oracle undo and redo logs


3b record locks l.jpg
3b - Record Locks

  • SQL Server more complex than Oracle

    • Caused by lack of rollback segments

    • Additional isolation levels to maintain read consistency

    • Possible for writers to block readers

    • Increased possibility for:

      • deadlocking

      • long-sustained blocking

    • Keep transactions as short as possible

  • Oracle never escalates locks


3b record locks34 l.jpg
3b - Record Locks

Lock Types

Resources

Status

  • INTENT (I)

  • SHARED (S)

  • UPDATE (U)

  • EXCLUSIVE (X)

  • SCHEMA (Sch-[M,S])

  • BULK UPDATE (BU)

  • RANGE[I]_[S,U,X,N]

  • RID

  • PAGE

  • EXT

  • TAB

  • IDX

  • FIL

  • KEY

  • DB

  • GRANT

  • WAIT

  • CNVRT


3b record locks35 l.jpg
3b - Record Locks

  • The transaction isolation levels are:

    SQL Server Oracle

    Read Uncommitted N/A

    Read Committed (default) Read Committed (default)

    Repeatable Read N/A

    Serializable Serializable

    N/A Read Only (ANSI)


3c t sql vs sql plus transactions l.jpg
3c - T-SQL vs SQL-Plus Transactions

  • T-SQL command:

    SET TRANSACTION ISOLATION LEVEL

    [ READ COMMITTED, READ UNCOMMITTED

    REPEATABLE READ, SERIALIZABLE ]

  • You can view the isolation level via the command: DBCC USEROPTIONS

  • SQL-Plus command:

    SET TRANSACTION ISOLATION LEVEL

    [READ COMMITTED , SERIALIZABLE]


3c t sql vs sql plus transactions37 l.jpg
3c - T-SQL vs SQL-Plus Transactions

Example:

set transaction isolation level serializable

begin transaction

select * from mytable

exec sp_lock @@spid

commit transaction


3c t sql vs sql plus transactions38 l.jpg
3c - T-SQL vs SQL-Plus Transactions


3c t sql vs sql plus transactions39 l.jpg
3c - T-SQL vs SQL-Plus Transactions

  • The key difference - inability for SQL Server to pause or re-start transactions as Oracle can

  • Can have savepoints like Oracle


4 performance tuning l.jpg
4 - Performance Tuning

  • TPC Benchmarks

  • Trace

  • Explain Plan

  • Performance Monitor

  • Indexing

  • Index Tuning

  • Stored Procedures


4 tpc benchmarks l.jpg
4 - TPC Benchmarks

  • TPC - Transaction Processing Performance Council www.tpc.org

  • TPC-C Benchmark - Order Entry

    • Established in 1992

    • 5 transaction types

      • New Order

      • Payment

      • Delivery

      • Order Status

      • Stock Level





4 oracle trace l.jpg
4 - Oracle Trace

PLUS: UTLBSTAT, UTLESTAT, STATSPACK


4 sql server profiler l.jpg
4 - SQL Server Profiler

  • GUI that allows you to trace:

    • Cursors

    • Database - data and log file changes

    • Errors and Warnings

    • Locks

    • Performance

    • Security audit - like password changes

    • Stored Procedures

    • Transactions

    • T-SQL Commands


4 oracle explain plan l.jpg
4 - Oracle Explain Plan

Rows Execution Plan

-------- ----------------------------------------------------

12 SORT AGGREGATE

2 SORT GROUP BY

76563 NESTED LOOPS

76575 NESTED LOOPS

19 TABLE ACCESS FULL CN_PAYRUNS_ALL

76570 TABLE ACCESS BY INDEX ROWID CN_POSTING_DETAILS_ALL

76570 INDEX RANGE SCAN (object id 178321)

76563 TABLE ACCESS BY INDEX ROWID CN_PAYMENT_WORKSHEETS_ALL

11432983 INDEX RANGE SCAN (object id 186024)



4 oracle performance manager l.jpg
4 - Oracle Performance Manager

Different from NT Performance Monitor


4 performance monitor perfmon exe l.jpg
4 - Performance Monitor (perfmon.exe)

  • Each SQL Server Instance installed has performance monitor counters

  • Statistics for:

    • CPU Usage

    • CPU Context Switches

    • Disk I/O

    • Memory and Paging

    • SQL Server Cache, Buffer Manager, Trans/Sec, Page Splits, Full Scans, Wait Times


4 sql clustered index l.jpg
4 - SQL Clustered Index

  • Table rows are sorted in same order as Index

  • A table can contain only one clustered index

  • UPDATE and DELETE operations are often accelerated

  • Creating and Modifying Index Values causes Table to be Reorganized on Disk

  • Good for:

    • Retrieving many records at once from table

    • Getting a range of values

    • Columns with limited number of values



4 oracle reverse key index l.jpg
4 - Oracle Reverse-Key Index

  • Reverses the bytes of each column indexed (except the rowid) while keeping the column order


4 sql server index and table fragmentation l.jpg
4 - SQL Server Index and Table Fragmentation

  • Database Console Commands to use in T-SQL:

  • DBCC SHOW_STATISTICS

  • DBCC

    UPDATEUSAGE([DBNAME,TABLE,INDEX])

  • DBCC

    SHOWCONTIG([DBNAME,TABLE,INDEX])

  • DBCC DBREINDEX

  • DBCC INDEXDEFRAG - run online


4 oracle index tuning wizard l.jpg
4 - Oracle Index Tuning Wizard

  • Use with Oracle cost-based optimizer

  • Part of Oracle Enterprise Manager Tuning Pack

  • Identifies tables in need of index changes

  • Presents its findings in reports

  • Allow you to automatically implement recommendations



4 stored procedures l.jpg
4 - Stored Procedures

  • Compiled Stored Procedures

    • Minimize Business Tier Activity

  • SQLServer2000 - Extended stored procedures (DLL’s)


5 database management l.jpg
5 - Database Management

  • Oracle RAC

  • SQL Server 2000 Failover Clusters

  • SQL Server 2000 Federated Databases

  • Partitioned Tables

  • Online Object Redefinition

  • Globalization Services

  • SQL Loader / DTS




5 sql server federated databases l.jpg
5 - SQL Server Federated Databases

  • Distributed Partitioned Views

  • Union All of distributed tables across DBs



5 oracle 9i online object redefinition l.jpg
5 - Oracle 9i Online Object Redefinition

  • New in Oracle 9i:

  • oracle redefinition PL/SQL package DBMS_REDEFINITITION


5 globalization services l.jpg
5 - Globalization Services

  • Oracle 9i - Globalization Services

    • Expanded National Language Support

  • SQL Server 2000 Collation


5 sql loader dts l.jpg
5 - SQL Loader / DTS

  • Oracle SQL Loader - command line

  • SQL Server - use DTS Data Transformation Service Import / Export Wizard, or

  • bulk copy BCP command line tool

  • BULK INSERT DML command


6 backup and recovery l.jpg
6 - Backup and Recovery

  • Backup

    • Hot / Online Backups

    • Differential Backups

    • Archived Log Backups

    • Log Shipping

  • Recovery


6a backup oracle vs sql server l.jpg
6a - Backup: Oracle vs SQL Server

ORACLE SQL SERVER

  • Hot Backup All full backups are HOT

  • Full Backups HOT Full Backup

  • Differential Increment Same as Oracle

    Backups

  • Archive Log Transaction Log

  • RMAN No Equivalent

  • Import/Export (full) Closest are: BCP

    Database Scripting


6a sql server recovery models l.jpg
6a - SQL Server Recovery Models

  • Simple

    • MASTER

    • MODEL

    • MSDB

    • TEMPDB

  • Full

  • Bulk Logged



6a differential backups l.jpg
6a - Differential Backups

SQL Server:

BACKUP DATABASE [mydb]

TO DISK = ‘e:\dbbackups\mydb\

mydb_20020624_full.bak’

WITH DIFFERENTIAL,

INIT,

NAME = 'Differential Backup of MYDB on 06/24/2002’


6a archived log backups l.jpg
6a - Archived Log Backups

  • SQL Server - called Transaction Log

    • 1 Log for each database in an instance

    • Database must be set up to be full or bulk-logged recovery mode

      BACKUP LOG [mydb] WITH

      TRUNCATE_ONLY



6b recovery l.jpg
6b - Recovery

  • Oracle

    • manual via sqlplus

    • via RMAN

    • Enterprise Manager

  • SQL Server

    • rebuildm.exe

    • Enterprise Manager

    • Query Analyzer

    • SQL Server Service Control Manager


7 cost and licensing l.jpg
7 - Cost and Licensing

  • Sql Server 2000

    • Standard Edition

    • Enterprise Edition

    • Cost

  • Oracle 9i

    • Standard Edition

    • Enterprise Edition

    • Cost

  • Total Cost of Ownership


7a sql server standard edition l.jpg
7a - SQL Server Standard Edition

  • SMP to 4 CPUs, 2GB of RAM

  • Core functionality of e-commerce, data warehousing, line-of-business solns

  • Data Transformation Services (DTS)

  • Replication - snapshot,transactional,merge

  • Full-text search

  • English Query

  • Stored Procedure development tools

  • SQL Profiling and Performance Analysis Tools


7a sql server enterprise edition l.jpg
7a - SQL Server Enterprise Edition

  • Enterprise Edition - includes:

    • 32-bit: 32 CPUs and 64 GB RAM

    • 64-bit: 64 CPUs and 512 GB RAM

    • Distributed Partitioned Views

    • Log Shipping

    • Fail over Clustering

    • Indexed Views

    • Multi-language Capability

    • OLAP

    • Direct SAN support


7a sql server pricing l.jpg
7a - SQL Server Pricing

  • Client Access License $146 per device or user

  • CPU License (Windows Server O/S license required):

    • Enterprise Edition $20,000 per processor

    • Standard Edition $5,000 per processor

      SQL Windows

      or Server 2003 Server

  • Server License requiring CALs: O/S

    • Enterprise w/ 25 CALs $11,099 $3,399

    • Standard w/ 5 CALs $1,489 $999

    • Standard w/ 10 CALs $2,249 $1,199

      The above prices are full retail

http://www.microsoft.com/sql/howtobuy/sqlserverlicensing.asp


7a sql server support l.jpg
7a - SQL Server Support

  • New Software Assurance Program

    • Based on License price of product covered

      • Costs 25% per year for each server product

      • Costs 29% per year for each desktop product

    • Linked to your Microsoft Volume License Agreement


7a sql server support79 l.jpg
7a - SQL Server Support

  • Features of Software Assurance Program

    • New version rights

    • Spread payments

    • Problem resolution support

    • Extended lifecycle hotfix support

    • Enterprise source licensing program

    • Corporate Error Reporting

    • eLearning


7b oracle standard db l.jpg
7b - Oracle Standard DB

  • Std One - 1 processor, min 5 named users

  • Standard - Up to 4 processors

  • Oracle Fail Safe

  • Flashback Query

  • Java, XML, Globalization Support

  • Oracle Enterprise Manager

  • Oracle Statistics Management

  • Oracle Analytic Functions

  • Oracle Text

  • Gateways to other systems / applications


7b oracle enterprise db l.jpg
7b - Oracle Enterprise DB

  • Minimum 25 named users +

    Available Add-Ons:

  • Oracle Data Guard

  • Oracle Transparent Application Failover

  • Virtual Private Database

  • Database Resource Manager

  • Oracle Streams

  • Oracle Partitioning

  • Oracle Real Application Clusters

  • Oracle Diagnostics Pack

  • Oracle Tuning Pack

  • Oracle OLAP

  • Oracle Data Mining


7b oracle 9i db pricing l.jpg
7b - Oracle 9i DB Pricing

CPU License

  • Enterprise Edition $40,000 per processor

  • Standard Edition $15,000 per proc (4 proc max)

  • Std Edition One $5,995 (1 processor only)

  • Licensing by Named Users w/ 1 processor:

    • Enterprise w/ min 25 NUs $20K ($800 * 25)

    • Standard w/ min 5 NUs $1,275 ($300 * 5)

    • Standard 1 w/ min 5 NUs $975 ($195 * 5)

  • Named User Price varies by Edition


  • 7b oracle 9i db pricing83 l.jpg
    7b - Oracle 9i DB Pricing

    • Enterprise Edition Options (per processor)

      • Real Application Clusters $20,000

      • OLAP $20,000

      • Partitioning $10,000

      • Data Mining $20,000

  • Enterprise Manager Options

  • Development Tools

  • InterConnect Adapters

  • Application Server


  • 7b oracle 9i support l.jpg
    7b - Oracle 9i Support

    • Upgrade - 15% of license

    • Support - 7% of license

    • Support won’t increase more than 4%/yr


    7c total cost of ownership l.jpg
    7c - Total Cost of Ownership

    • Software Licensing and Support Costs

    • Hardware Purchase and Support Costs

    • Operations

    • Administration

    • Indirect Costs of

      • End-User Operations

      • Downtime

    • Affected by

      • Assumptions made

      • Changing hardware / software mix


    7c total cost of ownership86 l.jpg
    7c - Total Cost of Ownership

    Example 1 - Magic Market Research, 2002

    • Assumptions:

      • 4-processor system

      • Only factor in known costs

      • 3 apps - Enterprise, Custom Web, and Business Intelligence

    • DB2 had lowest TOC

      • Then SQL Server 2000

      • Then Oracle


    7c total cost of ownership87 l.jpg
    7c - Total Cost of Ownership

    Example 2 - Input Market Research 2002

    • Assumptions:

      • Packaged application environment

      • SQL Server DBA serves 684 users

      • Oracle DBA serves 3000+ users

    • Oracle had lower TOC per user

      Cost Item Oracle SQLServer

      License $29.54 $6.05

      Consult/Impl 2.64 9.23

      Support/Maint 34.51 102.43

      Total $66.69 $117.71


    Forrester comparison bi sql server 2000 enterprise vs oracle 9i business intelligence l.jpg
    Forrester Comparison - BISQL Server 2000 Enterprise vsOracle 9i Business Intelligence

    SQL Server Oracle 9i


    The future l.jpg
    The Future

    • New releases in 2004

      • Microsoft Yukon

      • Oracle 10g

    • How you benefit


    What this means l.jpg
    What This Means

    • Stay aware of new developments

    • Be aware of strategic goals of your enterprise

    • Implement databases as part of entire IT integration strategy


    Bibliography l.jpg
    Bibliography

    SQL Server 2000 for the Oracle DBA by Christopher Kempster, 2003, Perth, Western Australia

    The Heterogeneous DBA by Chuck Sudowsky, 2003, Irvine, CA, Quest Software

    Database Software Trends Webcast, Oct 28, 2003, Irvine, CA, Quest Software

    Oracle 9i Database Release 2 New Features Oracle Whitepaper, May 2002

    Oracle 9i Database Release 2 on Windows: Development and Deployment, Oracle Whitepaper, May 2003

    Oracle 9I: Developing with Microsoft .NET, Oracle Whitepaper, April 2003

    DbForums - Oracle 9i vs MS SQL Server 2000 Technical Comparison by Kent Faulkner, USA, April 2003 (biased against MS)

    Technical Comparison of Oracle 9i Database and SQL Server 2000: Focus on Performance, Oracle Whitepaper, June 2002

    Database Architecture: Federated vs Clustered, Oracle Whitepaper, March 2002

    Technical Comparison of Oracle 9i Database vs SQL Server 2000: Focus on Manageability, Oracle Whitepaper, Nov 2002

    SQL Server 2000 vs Oracle9i - Alexander Chigrik Mar 30, 2003 - see link

    http://www.mssqlcity.com/Articles/Compare/sql_server_vs_oracle.htm

    Oracle9i Database Compared to SQL Server.htm Written by Oracle. Biased to Oracle.

    Forrester Scorecard of Oracle 9I vs MS Sql Server 2000 - BI - Nov 2003

    Microsoft SQL Server Web Pages - new features + pricing + Migrating Oracle Databases to SQL Server 2000

    Oracle Partner Info - Oracle pricing, partitioning by CPUs

    Oracle Pricing from Oracle 9i Partner e-page

    SearchWin2000Info directory - June 2002 interview with Oracle vp: 9i vs SQL Server

    + Interview with Microsoft vp: Oracle Isn’t All That Bad

    TPC benchmarks - tpc.org


    Questions and answers l.jpg
    Questions and Answers

    • Architecture

    • Installation

    • Transactions and Record Locking

    • Performance Tuning

    • Database Management

    • Backup and Recovery

    • Cost and Licensing Arrangements