Ask The Expert

Size: px

Start display at page:

Download "Ask The Expert"

유진 강
5 years ago
Views:

3 Net Web Grid

4 CPU, Main Memory Hard Disk Hardware, DB Hyper Text WWW DB,.

7 Source :

10 Search for ExtraTerrestrial Intelligence

12 ROI

13 Applications Application Servers Database Servers Storage

14 Clustering ADDM/ASH/RAC Grid Control ASM/AWR

15 Storage Grid Database Grid Application Grid Grid Control (QoS)

16 /DW Spare

17 /DW

18 /DW

19 /DW Spare

20 /DW Spare

21 /DW

22 /DW

23 /DW

24 /DW Spare

27 CPU Oracle DBMS MS SQL Server 4 cpu 20% v 4 CPU 80% 4 CPU 1 % 4 CPU 99%

28 Enterprise Edition -, OLTP, Oracle Database 10g. Standard Edition - 4 processor Oracle Database 10g. Standard Edition One - 2 processor Oracle Database 10g. Personal Edition - Oracle Database, Lite Edition - (Windows, WinCE,PalmOS )

29 Database edition Max Processors Min Users Real Application Clusters Standard edtion one 2 5 N/A Standard edition 4 5 Included Enterprise edition Unlimited 25 Option Oracle Database 10g Rated "Excellent" by InfoWorld Excellent 8.8 criteria score weight Manageability 9 25% Performance 8 25% Availability 9 20% Scalability 9 20% Value 9 10%

30 Processor 2 Edition First Class Database, Economy Price Small businesses & departmental systems 400 DB Start small and grow as you needs grow SQL Server 2000 SE Excel, Access, Mysql Upsizing

31 No of Users 5 10 No of Processors 1 Oracle SE1 $745 $1,490 Oracle SE1 $4,995 SS SE $1,489 $2,249 SS SE $4,999 Component List Price Support Upgrades SE1 $4,995 $350 $750 1 $6,095 SS SE $4,999 $1,225 $1,250 $7,474 2 $9,990 $9,998 5 $10,490 $17,374

33 No of Users 5 10 No of Processors 3 4 Oracle Database 10g Standard Edition $1,500 $3,000 Oracle Database 10g Standard Edition $45,000 $60,000 SQL Server 2000 Enterprise Edition $7,112 $7,842 SQL Server 2000 Enterprise Edition $59,997 $79,996

34 ORACLE10g Standard Edition Down,,Disk,Upgrade Shared Disk Single instance ORACLE10g RAC MS Federated SQL Server A-Z A-F A-E G-L F-K M-R L-P S-Z Q-S T-Z

35 No price changes $40,000 per processor $800 per user (minimum 25 users per CPU) Same options, same prices RAC OLAP Advanced Security Partitioning Data Mining Label Security Enterprises of any size Mission Critical capabilities(, ) Data Warehousing capabilities(edw, BI) Consolidated/Grid environments

36 Database Edition EE & Options EE SE SE One Personal Lite Size of Business/Use Large (4+ CPU s) Large (4+ CPU s) Medium sized (4 CPU s) Small/departmental (1-2 CPU s) Laptop, Desktop PDA s, Mobile Devices No of Users > 1,000 > 1, ,000 <

38 SQL? SQL? SQL Workload DBA SQL DBA

39 1. SQL 2. SQL : 1. SQL Index MV 3. SQL : 1. SQL 2. Hints stored outlines index rebuild, 3. Index MV /. Oracle10g automates the SQL-tuning process

40 & SQL Database Management Built-in Intelligent Infrastructure Code instrumentation Automatic Workload Repository Automation of Routine Tasks Automatic disk-based Backup and Recovery Automatic optimizer statistics collection Automatic Memory Management Automatic Storage Management Tools to Empower the DBA Automatic Database Diagnostic Monitor Automatic Tuning Optimizer

41 BG BG FG FG In-memory statistics AWR Statistics SGA ASH MMON 7:00 a.m. 7:30 a.m. 8:00 a.m. 8:30 a.m. SYSAUX WR Schema Snapshot 1 Snapshot 2 Snapshot 3 Snapshot 4 ADDM finds top problems Seven days 8:30am DBA

42 , ADDM finds top problems

43 High-load SQL SQL Advisor Snapshots in Automatic Workload Repository Automatic Diagnostic Engine IO / CPU issues System Sizing Advice RAC issues Network + DB config Advice History 10 Top-Down (Symptoms) (root) CPU,, Lock,I/O, RAC SQL

44 Auto SQL Tuning Packaged Apps Custom Apps High-load SQL Automatic Tuning Optimizer Auto SQL Analysis Access Advisor Complete SQL Management Automated workload capture, identification of high-load SQL Automatic SQL Tuning Automatic SQL Analysis SQL Access Advisor Packaged Apps + SQL Profile Custom Apps + SQL Advice Custom Apps + Indexes & MVs Technical Track Well-tuned SQL

56 (Seed) ( 1 CD, 20 ) initsid.ora 90% ( < 30 ) GUI Database Upgrade Assistant (DBUA) Oracle 8i / 9i 111 steps! Oracle 10g 6 steps!

57 & SQL Database Management Intelligent Infrastructure Built-in Intelligent Infrastructure Code instrumentation Automatic Workload Repository Automation of Routine Tasks Automatic disk-based Backup and Recovery Automatic optimizer statistics collection Automatic Memory Management Automatic Storage Management Tools to Empower the DBA Automatic Database Diagnostic Monitor Automatic Tuning Optimizer

58 Oracle Database 10g Data Pump Oracle9i Export 60% Oracle9i Import 15 ~ 20 Export Transportable Tablespace

59 , Capacity Planning Server- Generated Alerts Online Segment Shrink (Shrink) (Shrink) ALTER TABLE SHRINK SPACE [COMPACT]; Data Data HWM HWM

60 Database Area Flash Recovery Area 2 Set and Forget Recovery Area (incremental) DB ( 20x )

61 Flashback Log (captures) Flashback Database to 2:05 PM Rewind button for the Database

62 Human Errors Other Downtime Flashback Database to 2:05 PM - Query

63 3 rd Party ASM DB

64 . Tables I/O Tablespace Files File System Logical Vol Disks Rebalancing Oracle ASM

65 DB + OS Monitor

66 Main

67 Parameter

68 Table

69 Tablespace

70 Datafile

71 User

72 Maintenance

75 SGA Oracle process Code Background and foreground threads SGA contains db buffers, log buffers shared pool, other memory allocations Each thread consists of PGA, stack, other memory allocations

76 64-bit file I/O Windows Oracle Database 10g Big file DB (2GB,4GB) Windows DB 4 petabytes Async file I/O Raw files File system

77 Windows NUMA 32-bit Windows 2000+, 64-bit Windows Multiplexing, connection pooling,rac

78 New for Oracle Database 10g: Fiber model support Threads Oracle scheduled fiber CPU cpu Large Page support Windows Server 2003 API PAGE RAM cpu overhead bit Windows

80 Component ORACLE Multi-version read consistency Non-Escalating row-level locking B-Tree indexes Index-organized Tables Bitmap indexes Bitmap Join Indexes Range,Hash, List and Composite Partitioning Local and global indexes SS SE Shared read locks or dirty reads Locks escalate RAC B-Tree indexes Clustered Indexes Not supported Not supported Not supported local indexes Queries,INSERT,DELETE,UPDATE Materilized Views MERGE Multi-table INSERT Pipelined table Functions QUERY Only Indexed Views Not supported Not supported Not supported

81 PLSQL up to 2x Table Scans up to 2x SQL Profile Tuning huge Floating point math up to 10x Bulk LOB Update up to 3x Import up to 15x Export up to 35% Incremental backup up 10x Parallel media recovery 2-4x Backup compression 17x Infiniband interconnect ½ latency, 2x block transfer TPC-C Best absolute and best on 4-way Intel +28% on same hardware

Oracle is 12% faster and 12% cheaper than Microsoft on identical 4 processor HP hardware Version Performance Price Performance Oracle 10g 136,110.98 $4.09 Microsoft 2000 121,065.13 $4.

82 Oracle is 12% faster and 12% cheaper than Microsoft on identical 4 processor HP hardware Version Performance Price Performance Oracle 10g 136, $4.09 Microsoft , $4.79 Platform CPUs OS Date HP rx5670 HP rx Itanium 2 1.5GHz 4 Itanium 2 1.5GHz Red Hat Linux Windows AS /5/2003 4/24/2003 Source: Transaction Processing Council ( As of September 19, 2003: 1 HP Integrity rx5670, (Oracle Database 10g Standard Edition), 136, tpmc, $3.94/tpmC, available March 5, HP Integrity rx5670, (Microsoft SQL Server bit), 121, tpmc, $4.49/tpmC, available August 1, 2003.

83 Source: Transaction Processing Council (TPC) A N N O U N C E M E N T World Record TPC-C Benchmark The world record TPC-C non-clustered Linux result of 609,467 tpmc, $6.78/tpmC was achieved on a NEC Express5800/Xd server with 32 Intel Itanium 2 processors at 1.5 GHz running Oracle Database 10g with SUSE LINUX Enterprise Server 9. This result outperforms the previously published Microsoft SQL Server 2000 result on the same server and is the best TPC-C 32-way result on Intel processors.

84 Intel Processors TPC-C 32-way Database Software Oracle Microsoft Performance (tpmc) Price Performance ($/tpmc) Platform Number of CPUs CPU type OS

87 .NET DB. Oracle RAC, XML DB, grid, etc. Oracle DB ADO.NET provider Oracle Data Provider for.net (ODP.NET) ODP.NET.NET 100% No limitation to using.net functionality

88 Oracle Data Provider for.net (ODP.NET) Developed by Oracle - Best performing provider Exposes the most Oracle DB features Based upon Microsoft ADO.NET spec Migration from SQLClient is easy 3 rd party Oracle providers Microsoft.NET Data Provider for Oracle (MS) Connect for.net (DataDirect) Other Oracle data access methods OLE DB.NET via OLE DB ODBC.NET via ODBC

89 Available today in production for free Original release: Dec Can be used with Oracle8, Oracle8i, Oracle9i, and Oracle10g database servers Database server can be on Unix, Linux, Windows, etc. Database client on Windows Supports VS.NET 2002 and 2003 Supports.NET Framework 1.0 and 1.1

90 Full PL/SQL support Native Oracle data types Connection pooling Visual Studio.NET Integration Server Explorer Tables, views, and synonyms Query and view designer Stored procedures, functions, triggers, package bodies, package specifications Execute, create, delete Drag-and-drop visual controls from Data toolbox * Performance tip

91 XML Capability Oracle Microsoft IBM XML and SQL Duality Yes No No XML Schema Yes No No Integrated XML Repository Yes No No Scalable XML Processing Yes No No Flexible Storage Models Yes No No ODP.NET can take advantage of all these XML capabilities

92 ASP.NET VB.NET C# C++.NET Any.NET Client OLE DB.NET Oracle OLE DB ODBC.NET Oracle ODBC Oracle.NET Data Provider Oracle

93 Oracle Objects For OLE (OO4O) COM Oracle access OCI Native Oracle driver Oracle Provider for OLE DB Native OLE DB driver for Oracle Oracle Open Database Connectvity (ODBC) ODBC 3.51 compliant - Native Oracle ODBC driver Oracle Services for Microsoft Transaction Server (OraMTS) acts as a proxy between DTC and the Oracle DB MS Distributed Transaction Coordinator (DTC) Oracle No single point of failure

94 Active Server Pages Visual Basic C/C++ Applications MS Office Any COM Client ActiveX Data Objects (ADO) Oracle Objects for OLE (OO4O) Oracle Provider for OLE DB COM Automation Feature Oracle

Oracle Database 10g: Self-Managing Database DB TSC

Oracle Database 10g: Self-Managing Database DB TSC Agenda Overview System Resource Application & SQL Storage Space Backup & Recovery ½ Cost ? 6% 12 % 6% 6% 55% : IOUG 2001 DBA Survey ? 6% & 12 % 6% 6%