1 / 37

Optimizing Infrastructure for Oracle 9i Implementations

This presentation discusses the importance of optimizing infrastructure for Oracle 9i implementations, covering topics such as data placement, storage arrays, OS implications, evaluating current implementation, and trends. Presented by William Bataille, a Database/Unix System Engineer at Bristol-Myers Squibb.

dmorency
Download Presentation

Optimizing Infrastructure for Oracle 9i Implementations

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. Presentation 37007Optimizing Infrastructure for Oracle 9i ImplementationsWilliam BatailleBristol-Myers Squibb http://www.bms.com

  2. Introduction • Database / Unix System Engineer at Bristol-Myers Squibb • Responsible for large single instance SAP / Oracle Implementation • thousands of concurrent users • approaching 3 TB of data • Participant in Gartner Group SAP / Best Practices • Advance training from Oracle SAP Solution Center, Walldorf, Germany • Knowledge of HP-UX, SANs, and EMC

  3. Topics • Infrastructure, what is it, why should I care ? • Where can problems occur ? • Planning for data placement • Storage arrays uncovered • Oracle 9i multiple block sizes • OS implications (release and configuration) • Evaluating your current implementation • Trends and directions • Questions, comments, discussions

  4. Infrastructure, what is it, why should I care ? • The hardware, firmware, and operating system • The configuration of these environments • Infrastructure can affect recoverability • Infrastructure can affect scalability • Optimal Oracle Configuration requires knowledge of the environment

  5. Infrastructure Models • Simple, self contained • CPUs, memory and disk all in one server • Complex, separate disk storage • Network Attached Storage (NAS) • Storage Area Network (SAN) • Elaborate, partitioned • Hardware partition, separate disk storage • Virtual partition, floating memory, bound or unbound CPUs

  6. EMC Disk Array 32GB Cache 18 TB Raw 5.5 TB usable RAID 1 BCV/R1 16 native Fibre channel ports Complex Infrastructure Model • Storage Area Network • Shared Disk Array Gigabit Ethernet Standby DB server 16 CPUs, 16GB RAM Prod. DB server 16 CPUs, 16GB RAM 100BaseT Dedicated MC/SG Heartbeat 16 port FC Switch 165.89.31.96 10 fibre channel interfaces for Primary and Secondary cluster nodes 4 Fibre channel interfaces for BCV HP HASS High Availability Storage enclosures 32 GB disk (16 GB mirrored to 16 GB) Backup Server FC Switches

  7. Where can problems occur ? Location of Data Physical Logical Server memory / Oracle SGA Yes ? Server I/O buffer cache Yes ? Storage Network / Fabric Yes Yes Disk Array Cache Yes ? Logical Volume N / A Yes Physical Disk Yes N / A Data Block Yes Yes

  8. Top - Down Problem tracking • Oracle Alert / Trace log points to SGA • Logical: check MetaLink for known problems • Physical: run diagnostics on memory • System log points to I/O subsystem • Server’s fiber channel interface • Physical cables • Fiber Channel switches • Disk Array’s fiber channel ports • Disk Array’s cache • Physical disk

  9. Storage Focus • DBA’s concerned with data placement • recovery • performance • Hardware vendor interests • proprietary tools • other hardware solutions (e.g. cache) • RAID configurations • System Administrator • ease of configuration • ease of maintenance

  10. Data Placement • Redo Logs on separate disks • Segregate Data, Index, and Undo Tablespaces • Isolate Archive Logs • Document growth plan • Sounds good, however: • Disk hardware vendor doesn’t see the need • System Administrator doesn’t want to “waste storage” • Challenge: Is disk cheap ?

  11. Planning for JBOD • Just a Bunch Of Disk • How many disks on the system ? • Are the used only by Oracle or shared ? • What would happen if you lose a disk ? • Enough disks to mirror ? • Mirroring software available ? Plan to segregate by disk

  12. Sample Data Placement on JBOD /u01 - software, archive logs /u02 - SYSTEM, control 1, mirr redo /u03 - orig redo, RBS, control 2 /u04 - TEMP, control 3 /u05 - INDEX /u06 - DATA No data loss if one disk fails Protection against controller failure /u01 /u02 /u02 /u03 /u04 /u05

  13. Planning for Disk Array • How are the drives configured ? • Raid 0+1 for performance • Raid n for cost savings • Hardware Stripe And Mirror Everything (SAME) • Does the disk support revectoring ? • If yes, has bad block reallocation been disabled ? • What type of volume is presented to the server ? • Entire disk ? • Hyper Volume ? • Meta volume ? Plan to segregate by volume group

  14. Sample Data Placement on Disk Array Fiber Adapter 1a Fiber Adapter 1b Fiber Adapter 3a Fiber Adapter 3b Cache Disk Adapter 1a Disk Adapter 1b Disk Adapter 3a Disk Adapter 3b /u01 /u02 /u01 /u02 VG1 /u01 /u02 /u01 /u02 /u03 /u04 /u03 /u04 /u03 /u04 /u03 /u04 /u05 /u06 /u05 /u06 /u05 /u06 /u05 /u06 /u07 /u08 /u07 /u08 /u07 /u08 /u07 /u08 /u01 /u02 /u01 /u02 Mirror /u01 /u02 /u01 /u02 /u03 /u04 /u03 /u04 /u03 /u04 /u03 /u04 /u05 /u06 /u05 /u06 /u05 /u06 /u05 /u06 /u07 /u08 /u07 /u08 /u07 /u08 /u07 /u08

  15. Storage Terminology • Volume - physical disk, example 36GB • Hyper Volume - slice of a volume, ex. 9GB • Meta Volume - group of striped Hyper Volumes example: • 4 * 9GB hyper volumes@1MB stripesize = 36GB • Volume Groups - collection of Meta Volumes • Logical Volume - portion of a Volume Group • Striped Logical Volume

  16. Storage Relationships Divided Volume (disk) Hyper Volume Meta Volume Combined Into Into File System Logical Volume Divided Volume Group Mounted On Into

  17. Storage uncovered (Volumes) Fiber Adapter 1a Fiber Adapter 1b Fiber Adapter 3a Fiber Adapter 3b Cache Disk Adapter 1a Disk Adapter 1b Disk Adapter 3a Disk Adapter 3b V1 V2 V3 V4 V5 V6 V7 V8 V8 V7 V6 V5 Mirror V4 V3 V2 V1

  18. Storage uncovered (Hyper Vols) Fiber Adapter 1a Fiber Adapter 1b Fiber Adapter 3a Fiber Adapter 3b Cache Disk Adapter 1a Disk Adapter 1b Disk Adapter 3a Disk Adapter 3b H1 H5 H2 H6 H3 H7 H4 H8 H9 H13 H10 H14 H11 H15 H12 H16 H17 H21 H18 H22 H19 H23 H20 H24 H25 H29 H26 H30 H27 H31 H28 H32 H32 H28 H31 H27 Mirror H30 H26 H29 H25 H24 H20 H23 H19 H22 H18 H21 H17 H16 H12 H15 H11 H14 H10 H13 H9 H8 H4 H7 H3 H6 H2 H5 H1

  19. Storage uncovered (Meta Vols) Fiber Adapter 1a Fiber Adapter 1b Fiber Adapter 3a Fiber Adapter 3b Cache Disk Adapter 1a Disk Adapter 1b Disk Adapter 3a Disk Adapter 3b M1 M2 M1 M2 M1 M2 M1 M2 M3 M4 M3 M4 M3 M4 M3 M4 M5 M6 M5 M6 M5 M6 M5 M6 M7 M8 M7 M8 M7 M8 M7 M8 M1 M2 M1 M2 Mirror M1 M2 M1 M2 M3 M4 M3 M4 M3 M4 M3 M4 M5 M6 M5 M6 M5 M6 M5 M6 M7 M8 M7 M8 M7 M8 M7 M8

  20. Storage uncovered (Volume Groups) Fiber Adapter 1a Fiber Adapter 1b Fiber Adapter 3a Fiber Adapter 3b Cache Disk Adapter 1a Disk Adapter 1b Disk Adapter 3a Disk Adapter 3b M1 M2 M1 M2 VG1 M1 M2 M1 M2 M3 M4 M3 M4 M3 M4 M3 M4 M5 M6 M5 M6 M5 M6 M5 M6 M7 M8 M7 M8 M7 M8 M7 M8 M1 M2 M1 M2 Mirror M1 M2 M1 M2 M3 M4 M3 M4 M3 M4 M3 M4 M5 M6 M5 M6 M5 M6 M5 M6 M7 M8 M7 M8 M7 M8 M7 M8

  21. Disk Array Monitoring • Does an up-to-date schematic diagram exist? • Are there multiple access paths to the disk? • Is the I/O balanced across controller paths? • Do pvchange scripts exist? • How is performance monitored ? • Preemptive support agreement ? • Who is notified when maintenance occurs ?

  22. File System Configurations • Create one Logical Volume per meta volume • use Journaled File Systems (JFS) • Set filesystem blocksize = database objects • Consider 9i multiple blocksize capabilities • use large files only when necessary • some OS utilities still don’t work with > 2GB • Choose your JFS mount options wisely

  23. Journaled File Systems • Extent based allocation of disk • Fast file system recovery thanks to logging • Intent log holds completed “transactions” • Mount options control use of this log • Balance system integrity with performance • Does your disk supports bad block revectoring? • Internally in disk arrays • JBOD specified during pvcreate

  24. Choosing JFS mount options • Ultra conservative • full logging of all structural changes • do not store any data in log (nodatainlog) • Conservative methodology • full logging for Oracle Redo Logs • delayed logging, datainlog for non Redo • Moderate methodology • delayed logging, datainlog for all filesystems • Online JFS allows dynamic changes

  25. Oracle 9i Multiple Blocksizes • Useful in OLTP Environment to reduce block contention • Can free up I/O bandwidth • small blocksize where appropriate • Can remedy wrong initial choice of blocksize • Synchronize with OS block size Question: What is the best OS block size? 2K, 4K, 8K ? OS blocksize  tablespace blocksize ?

  26. Oracle Tablespace Blocksize vs OS Filesystem Blocksize Time in seconds to: Create 10 GB tablespace, Insert 50 million rows, Select all rows:

  27. Storage Summary • Know and document your hardware environment and Vendor contacts • Establish firmware upgrade policies • Keep Oracle and OS blocksizes the same Question: Do I still need to mirror redo logs? • Absolutely, doesn’t hurt performance and will save you when mistakes occur Question: What is the best I/O? • No I/O, found everything needed in SGA

  28. OS Implications • Patching strategy • Oracle specified OS patches • Oracle specified software • example JAVA SDK 1.3 • Kernel Configurations

  29. OS related installation problems • inadequate kernel resources • memory segments • file system handles • missing X-library symbolic links • missing software • c compiler • JDK (was version 1.3.1 July 2003) • PERL (was version 5.6.1 July 2003) • inadequate file system size • missing entry in /etc/hosts, pfs_mount hangs • SQLNet Session Data unit (SDU) set too high

  30. OS related performance problems • Dynamic file system buffer cache disabled • set bufpages = 0 and nbuf = 0 • I/O buffer set too high • default dbc_max_pct 50% • recommend value 8% • set dbc_min_pct = dbc_max_pct • Psuedoswap is disabled • set swapmem_on = 1 • unlockable memory set • set unlockable_mem = 0

  31. OS related CPu performance problem • You are CPU bound and don’t know why • You set up the server by applying a “Tuned Parameter Set” • Scheduling timeslice interval too low • forces a process to check for pending signals • templates set incorrectly to 1 • set timeslice = 10 (10 x 10 millisecond clicks)

  32. Evaluating your current environment • Is file system utilization > 95 percent ? • Control files may need to grow • JFS performance issues • potential restore issues • Check recoverability • Same disk used by multiple file systems • Backup on same disk as data • Review system log / diagnostic messages • power failed errors and / or retries • Review Oracle and OS performance stats

  33. Evaluating your current environment (cont.) • Check JFS mount options (more /etc/mnttab) • “nodatainlog” decreases write performance 50% • Check the clock time • syslog for network time protocol daemon recycle • Review oracle alert log for “checkpoint not complete” • Check if tablespace blocksize * multiblock read count is > OS capabilities (128K HP 11i)

  34. Trends and directions • Faster CPUs following Moore’s Law • Larger Disks and Cache • Raid to the nTH degree • Storage consolidation • SANs for everything, even boot disks • Server consolidation • “Virtualization”: pooled IT assets across storage systems, servers, networks...

  35. Discussion Q & Q u E S T I O N S A N S W E R S A

  36. Additional Resources Oracle Technology Network http://otn.oracle.com/deploy/availability http://otn.oracle.com/deploy/performance/content.html Optimal Storage Configuration Made Easy Diagnosing Performance using StatsPack, Part I, II Send an email william.bataille@bms.com

  37. Reminder – please complete the OracleWorld session surveyThank you.Presentation 37007

More Related