Presentation 37007 Optimizing Infrastructure for Oracle 9 i

Presentation 37007 Optimizing Infrastructure for Oracle 9 i Implementations William Bataille Bristol-Myers Squibb http: //www. bms. com

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

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

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

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

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

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

Top - Down Problem tracking Ÿ Oracle Alert / Trace log points to SGA – – Logical: check Meta. Link 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

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 –

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 ?

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

Sample Data Placement on JBOD /u 01 - software, archive logs /u 02 - SYSTEM, control 1, mirr redo /u 03 - orig redo, RBS, control 2 /u 04 - TEMP, control 3 /u 05 - INDEX /u 06 - DATA No data loss if one disk fails Protection against controller failure /u 01 /u 02 /u 03 /u 04 /u 05

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

Sample Data Placement on Disk Array Fiber Adapter 1 a Fiber Adapter 1 b Fiber Adapter 3 a Fiber Adapter 3 b Disk Adapter 3 a Disk Adapter 3 b Cache Disk Adapter 1 a Disk Adapter 1 b /u 01 /u 03 /u 05 /u 07 /u 02 /u 04 /u 06 /u 08 /u 01 /u 03 /u 05 /u 07 /u 02 /u 01 /u 02 /u 04 /u 03 /u 04 /u 06 VG 1 /u 05 /u 06 /u 08 /u 07 /u 08 /u 01 /u 03 /u 05 /u 07 /u 02 /u 04 /u 06 /u 08 /u 01 /u 03 /u 05 /u 07 /u 02 /u 04 /u 06 /u 08

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

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

Storage uncovered (Volumes) Fiber Adapter 1 a Fiber Adapter 1 b Fiber Adapter 3 a Fiber Adapter 3 b Disk Adapter 3 a Disk Adapter 3 b Cache Disk Adapter 1 a Disk Adapter 1 b V 1 V 2 V 3 V 4 V 5 V 6 V 7 V 8 V 7 V 6 V 5 V 4 V 3 V 2 V 1

Storage uncovered (Hyper Vols) Fiber Adapter 1 a Fiber Adapter 1 b Fiber Adapter 3 a Fiber Adapter 3 b Disk Adapter 3 a Disk Adapter 3 b Cache Disk Adapter 1 a Disk Adapter 1 b H 1 H 9 H 17 H 25 H 2 H 6 H 13 H 10 H 14 H 21 H 18 H 22 H 29 H 26 H 30 H 3 H 11 H 19 H 27 H 4 H 8 H 15 H 12 H 16 H 23 H 20 H 24 H 31 H 28 H 32 H 24 H 16 H 8 H 28 H 31 H 27 H 20 H 23 H 19 H 12 H 15 H 11 H 4 H 7 H 30 H 22 H 14 H 6 H 29 H 25 H 18 H 21 H 17 H 10 H 13 H 9 H 2 H 5 H 1

Storage uncovered (Meta Vols) Fiber Adapter 1 a Fiber Adapter 1 b Fiber Adapter 3 a Fiber Adapter 3 b Cache Disk Adapter 1 a Disk Adapter 1 b Disk Adapter 3 a Disk Adapter 3 b M 1 M 3 M 5 M 7 M 2 M 4 M 6 M 8 M 1 M 3 M 5 M 7 M 2 M 4 M 6 M 8

Storage uncovered (Volume Groups) Fiber Adapter 1 a Fiber Adapter 1 b Fiber Adapter 3 a Fiber Adapter 3 b Cache Disk Adapter 1 a Disk Adapter 1 b Disk Adapter 3 a Disk Adapter 3 b M 1 M 3 M 5 M 7 M 2 M 4 M 6 M 8 M 1 M 3 M 5 M 7 M 2 M 4 M 6 M 8 VG 1

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 ?

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

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

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

Oracle 9 i 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? 2 K, 4 K, 8 K ? OS blocksize tablespace blocksize ?

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

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

OS Implications ŸPatching strategy ŸOracle specified OS patches ŸOracle specified software – example JAVA SDK 1. 3 ŸKernel Configurations

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

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 –

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)

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

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 (128 K HP 11 i)

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

Q& A Discussion Qu. ESTIONS ANSWERS

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 Stats. Pack, Part I, II Send an email william. bataille@bms. com

Reminder – please complete the Oracle. World session survey Thank you. Presentation 37007