1 / 92

Twin Cities Oracle Users Group

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?

Michelle
Download Presentation

Twin Cities Oracle Users Group

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

  2. Introduction • Why Compare the databases? • We often run both databases at one site • SQL Server is growing up - size / installs

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

  4. Overview

  5. Overview Oracle Had 11% increase in DB sales in 2003 Microsoft had 18% increase in DB sales 2003

  6. 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%

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

  8. 1 - Architecture • Definition of Database / Instance • Memory Configuration • Threads • Background Processes • File Structure

  9. 1a - Instances • Details about this topic • Supporting information and examples • How it relates to your audience

  10. 1a - Instances

  11. 1b - Memory Configuration Oracle SQL Server

  12. 1c - Oracle Memory and Processes

  13. 1c - Threads

  14. 1c - Threads

  15. 1d - Background Processes Oracle

  16. 1e - File Structure

  17. 1e - File Structure SQL Server For Backup Information

  18. 2 - Installation • Release Notation • Product Types • Platforms • Install Comparison • Oracle • SQL Server 2000

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

  20. 2b - Product Types • Enterprise • Standard • Licensing: • Per Processor • Units • Oracle - Named User • SQL Server 2000 - Client Access License

  21. 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)

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

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

  24. 2d - SQL Server 2000 DB Creation • Installation CD • Installation wizard on the CD

  25. 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)

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

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

  28. 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?

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

  30. 3 - Transactions / Record Locking • Rollback Segments / Redo Logs • Record Locks • T-SQL vs SQL-Plus Transactions

  31. 3a - Rollback Segments / Redo Logs Oracle • Undo Tablespace in 9i • Redo Logs Note: DBA can still use Rollback Segments, if desired

  32. 3a - Rollback Segments / Redo Logs SQL Server • Transaction Log is present in each Database of each Instance • Equivalent of Oracle undo and redo logs

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

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

  35. 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)

  36. 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]

  37. 3c - T-SQL vs SQL-Plus Transactions Example: set transaction isolation level serializable begin transaction select * from mytable exec sp_lock @@spid commit transaction

  38. 3c - T-SQL vs SQL-Plus Transactions

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

  40. 4 - Performance Tuning • TPC Benchmarks • Trace • Explain Plan • Performance Monitor • Indexing • Index Tuning • Stored Procedures

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

  42. 4 - TPC-C Results as of May 9, 1997

  43. 4 - TPC-C Ver 5 Performance

  44. 4 - TPC-C Ver 5 Price Performance

  45. 4 - Oracle Trace PLUS: UTLBSTAT, UTLESTAT, STATSPACK

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

  47. 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)

  48. 4 - SQL Server Explain Plan

  49. 4 - Oracle Performance Manager Different from NT Performance Monitor

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

More Related