Oracle RAC 10 g : Running Mixed Workloads

2. Session id: Oracle RAC 10g: Running Mixed Workloads Gordon SmithDirector, RAC Product Management Oracle Corporation

3. Agenda • Business Goals • Today’s Inadequate Solutions • Key Technical Elements of the Solution • Non-Blocking Queries • Real Application Clusters • Other Great Tools • Oracle 10g Enterprise Grids – Putting It All Together

4. Business Goals • Goals • Report off current live data • No impact on online performance • Simplicity • Low cost • Focusing on reporting • Not full blown Data Warehouse • DW a much more complex problem • Oracle offers good “On Time” solution

5. Today’s Inadequate Solutions Off-Hours Batch • Wait until off-hours when online systems aren’t being used • Problems • The Internet and 21st Century business changed online to 24 X 7 • Data not current

6. Today’s Inadequate Solutions Reporting DB Clones • Copy database to second system for reporting • Problems • Not current data • Complicated, multi-step process with many failure points • Poor hardware utilization • Data is growing and getting harder to copy • Cloning (or re-silvering) can impact performance

7. Today’s Inadequate Solutions Traditional DR Solutions • Why not just run reports against the data on my disaster recovery (DR) standby system • The standby is just sitting there underutilized • Data can be very current • Problems • Nearly all DR solutions don’t let you access data on standby system, e.g., • Remote disk mirroring systems • Oracle DataGuard (physical mode)

8. Key Technical Elements of the Solution • Non-Blocking Queries • Real Application Clusters • Other Great Tools • Automatic Undo Management • Resource Manager • Logical Data Guard

9. Non-Blocking Queries • Provides consistent reads • Never sees uncommitted “dirty” data • Never sees some changes from a transaction but not other changes • Bottom line: You Get The Right Answer • Doesn’t block updates • Doesn’t get blocked by updates

10. Non-Blocking Queries • For example, say you need to add up the balances for all accounts …

11. Non-Blocking Queries • For example, say you need to add up the balances for all accounts …

12. Non-Blocking Queries • For example, say you need to add up the balance for all accounts … 1,000 <- The Right Answer

13. Non-Blocking Queries • Now let’s run a transaction at the same time that transfers $50 from one account to another • Without using Non-Blocking Queries • Without using read locks

14. Non-Blocking Queries • Now let’s run a transaction at the same time that transfers $50 from one account to another • Without using Non-Blocking Queries • Without using read locks

15. Non-Blocking Queries • Now let’s run a transaction at the same time that transfers $50 from one account to another • Without using Non-Blocking Queries • Without using read locks -$50 +$50

16. Non-Blocking Queries • Now let’s run a transaction at the same time that transfers $50 from one account to another • Without using Non-Blocking Queries • Without using read locks 1,100 <- The Wrong Answer

17. Non-Blocking Queries • Now let’s try read locks

18. Non-Blocking Queries • Now let’s try read locks

19. Non-Blocking Queries • Now let’s try read locks -$50 Transaction Blocked!!!

20. Non-Blocking Queries • Now let’s try read locks • You get the right answer but update transactions are blocked • Similarly update transactions can block queries 1,000 <- The Right Answer

21. Non-Blocking Queries • Now let’s try non-blocking queries with read consistency

22. Non-Blocking Queries • Now let’s try non-blocking queries with read consistency

23. Non-Blocking Queries • Now let’s try non-blocking queries with read consistency -$50 +$50

24. Non-Blocking Queries • Now let’s try non-blocking queries with read consistency • Queries don’t block updates • Updates don’t block queries 150 1,000 <- The Right Answer

25. Runs all types of business applications on clusters Maximum availability Maximum scalability Lowest cost Efficient cache fusion technology Centralized Management Console Users Low Latency Interconnect VIA or Proprietary Cache Fusion High Speed Switch or Interconnect ClusteredDatabase Servers Shared Cache Storage Area Network Mirrored Disk Subsystem Hub or Switch Fabric Real Application Clusters

26. Oracle9i RAC SAP Scalability SD 3-Tier Parallel Benchmark Results Now Official 82% Scalability # Users Certified Running on HPQ AlphaServer Computers

27. Oracle9i RAC OLAP Scalability75x better than the nearest competitor World Record Performance 85,719 Analytic Queries Per Minute (AQM) 1,135 Can’t Say Source: DSS Lab, 2003

28. RAC Workloads Survey

29. CJIS FannieMae Job Partners Telenor Mobile University of Melbourne HealthPartners Cairns City Council Guangfu Securities Merrill Lynch STP Maisoning Vanderbilt University EPL China Unicom – Chonging Supply Solutions Gas Authority of India Ltd. Texas Tech University Lithonia Lighting SoftChoice Tata Teleservices AusRegistry POSCO Six Sigma Dongbu Insurance Ashok Leyland Ltd. WeightWatchers.com RAC Mixed Workload Customers • Gas Authority of India Ltd. • KITC • Nextel • Clearing Corp of India • Daegu universwity • Hite Brewery • US Army Medical Research & Material Command • Green Mountain Power • Holmesglen TAFE • Smartserv • Bharti Televentures Ltd.

30. Mix Workload Customer Example • Telenor Mobile in Norway • 2-nodes, 16 CPU & 4 CPU HP-UX • OLTP - 4 CPUs node • DSS on 16 CPUs • Fail OLTP to DSS but not other way

31. Other Great Tools • Resource Manager • Controls allocation of CPU to workloads • Limit execution times of long running operations • Limits # of active sessions per workload • Logical Data Guard • Allows queries to data on DR Standby • Allows updates to data only on Standby • Automatic Undo Management • Now fully automatic in Oracle9i

32. Enterprise Grid – Putting It All Together • Low Cost Computing • You can “just throw CPUs at it” • Automatic Storage Management • You can “just throw disks at it” • Workload Management • Powerful new ways to manage different workloads easily

33. Component Large RISC SMP Server RISC SMP Cluster Intel/Linux Cluster # Servers 1 8 8 Processors/Server 32 @1.3Ghz 4 @1.2Ghz 4 @2Ghz Memory/Server 64Gb 8Gb 8Gb Operating System UNIX UNIX RedHat AS Total Cost $1,250,000 $431,960 $232,664 Source: hardware vendor price lists Enterprise Grid - Low Cost Computing

34. SMP Electronic Arts ROI • Total IT costs: • Hardware costs • Software costs • H&S maintenance • DBA costs • SysAdmin costs 1st Year Big SMP is 35%+ More Expensive 5 Years Big SMP is 46%+ More Expensive

35. Enterprise Grid - Automatic Storage Management • Eliminates the complexity and cost of 3rd party volume and file managers • Completely automates I/O tuning • Eliminates all fragmentation • Optional mirroring Automatic Storage Management

36. Enterprise Grid - Automatic Storage Management • Eliminates the complexity and cost of 3rd party volume and file managers • Completely automates I/O tuning • Eliminates all fragmentation • Optional mirroring • Allows disks to be added or removed dynamically with fully automatic rebalancing Automatic Storage Management

37. Enterprise Grid - Automatic Storage Management • Eliminates the complexity and cost of 3rd party volume and file managers • Completely automates I/O tuning • Eliminates all fragmentation • Optional mirroring • Allows disks to be added or removed dynamically with fully automatic rebalancing Automatic Storage Management

38. Enterprise Grid - Workload Management • Hands-free assignment of servers to workloads based on rules • Automatic assignment of servers to workloads at startup • Re-assignment of servers to workloads as business needs change • On server failure, automatic re-assignment of surviving servers to workloads • Works across all platforms

39. Enterprise Grid – Workload Management Reporting Spare Order Entry Normal Server Allocation

40. Enterprise Grid – Workload Management Reporting Order Entry Christmas Season

41. Enterprise Grid – Workload Management Reporting Order Entry Post Christmas

42. Enterprise Grid – Workload Management Reporting Spare Order Entry Normal Server Allocation

43. Enterprise Grid – Workload Management Reporting Spare Order Entry Node Failure

44. Enterprise Grid – Workload Management Reporting Order Entry Recovery

45. Enterprise Grid – Workload Management Reporting Order Entry Another Node Failure

46. Enterprise Grid – Workload Management Reporting Order Entry Recovery

47. Conclusion A History of Innovation Enterprise Grids Automatic Storage Management Real Application Clusters (RAC) Workload Managment Data Guard Low-Cost Commodity Clusters Resource Manager Non-Blocking Queries OPS

49. Next Steps…. • Recommended Oracle RAC sessions • Oracle RAC 10g: Continuous Services for the Grid • Wednesday 11 am, Room 104 • Oracle RAC: Best Practices on Linux • Wednesday 1 pm, Room 104 • Oracle RAC: Customer Roundtable • Wednesday 4:30 pm, Room 104 • Real Application Cluster Guru Chat • Thursday 12 noon, Guru Lounge • See RAC demos in Database DEMOgrounds • Visit the RAC web sites for more information • http://otn.oracle.com/products/database/clustering/content.html

50. Q & Q U E S T I O N S A N S W E R S A