Optimizing Oracle Server Performance on LINUX Tips for

Optimizing Oracle Server Performance on LINUX Tips for Maximizing Toad Productivity OOUG 2009 Columbus, OH July 16 th © 2008 Quest Software, Inc. ALL RIGHTS RESERVED.

Agenda • Apply “low hanging fruit” fixes to speed up Oracle performance on Linux • We’ll look at both database and operating system level modifications … • Goal is simple – how can we maximize Oracle performance on Linux servers!

Bert Scalzo … Database Expert & Product Architect for Quest Software Oracle Background: • Worked with Oracle databases for over two decades (starting with version 4) • Work history includes time at both “Oracle Education” and “Oracle Consulting” Academic Background: • Several Oracle Masters certifications • BS, MS and Ph. D in Computer Science • MBA (general business) • Several insurance industry designations Key Interests: • Data Modeling • Database Benchmarking • Database Tuning & Optimization • "Star Schema" Data Warehouses • Oracle on Linux – and specifically: RAC on Linux Articles for: • Oracle’s Technology Network (OTN) • Oracle Magazine, • Oracle Informant • PC Week (e. Week) Articles for: • Dell Power Solutions Magazine • The Linux Journal • www. linux. com • www. orafaq. com 3

Books by Bert … Coming in 2009 … 4

Couple of Questions (for both our benefit) • How many people using – Where using Linux • Production • Development • Personal Education – Which Linux Distribution • • Oracle Enterprise Linux Redhat Enterprise Server SUSE Linux Enterprise Other ? ? ?

LINUX Server Popularity • IDC – Linux Servers: – – Posted 12 th consecutive quarter of double-digit growth Year-over-year revenue growth of 45. 1% Unit shipments up 32. 1% Revenue exceeded $1. 4 billion quarterly (will reach 9. 1 billion by 2008) – HP was first with 24. 3% market share – IBM was second with 20. 3% market share – Customers continue to expand role of Linux servers into an ever increasing array of workloads in both commercial and technical segments of the market • Gartner – Linux Servers: – One of hottest applications for Linux is on RDBMS servers – Linux was the fastest growing platform for RDBMS past year – Enterprises turning to Linux as an alternative for older Unix’s

LINUX Web Popularity In Netcraft’s July 2000 survey of 18, 169, 498 web sites. #1 OS !!! www. netcraft. com/survey/index-200007. html

LINUX Web Popularity http: //survey. netcraft. com/index-200106. html

LINUX Web Popularity http: //news. netcraft. com/archives/2003/07/index. html

LINUX Gaining Momentum

LINUX Gaining Momentum http: //www. gartner. com/Display. Document? ref=g_search&id=414985

Popularity != Performance Some plausible reasons for this: • Relative newness of the LINUX OS in general • RISC UNIX sys admin unfamiliar with INTEL • INTEL UNIX sys admin unfamiliar with LINUX • Windows based sys admin unfamiliar with UNIX • Oracle, Oracle …

Where Can We Look What is Tunable: • Hardware • Operating System Linux Tuning • Database • Network • Application SQL Tuning Advice – focus on application first, and then database and possibly network. Linux Tuning Advice – focus on hardware, LINUX and Oracle database configurations.

Performance Pyramid Network Hardware OS DBMS Application

Our Main Goal To squeeze all the blood out of our LINUX turnip …

Which LINUX Distribution OS Product Status SLES-9 10 g. R 2 Certified Red Hat Enterprise AS/ES 4 10 g. R 2 Certified Red Hat Enterprise AS/ES 3 10 g. R 2 Certified SLES-9 10 g Certified SLES-8 10 g Certified Red Hat Enterprise Linux AS/ES 4 10 g Certified Red Hat Enterprise Linux AS/ES 3 10 g Certified Red Hat Enterprise Linux AS/ES 2. 1 10 g Certified Red Flag DC Server 4. 0 (China Only) 10 g Certified Monta Vista Carrier Grade Linux 3. 1 (Embedded) 10 g Certified Miracle Linux Standard Edition 2. 1 (Japan only) 10 g Certified Asianux 2. 0 10 g Certified Asianux 1. 0 10 g Certified United. Linux 1. 0 9. 2 Certified SLES-9 9. 2 Certified SLES-8 9. 2 Certified Red Hat Enterprise Linux AS/ES 4 9. 2 Certified Red Hat Enterprise Linux AS/ES 3 9. 2 Certified Red Hat Enterprise Linux AS/ES 2. 1 9. 2 Certified Red Flag DC Server 4. 0 (China Only) 9. 2 Certified Monta Vista Carrier Grade Linux 3. 0 (Embedded) 9. 2 Certified Miracle Linux Standard Edition 2. 1 (Japan only) 9. 2 Certified Asianux 1. 0 9. 2 Certified

Which LINUX Distribution http: //news. netcraft. com/archives/2005/12/05/strong_growth_for_debian. html

Test Method TPC benchmark (www. tpc. org) TPC Benchmark™ C (TPC-C) is an OLTP workload. It is a mixture of read-only and update intensive transactions that simulate the activities found in complex OLTP application environments. It does so by exercising a breadth of system components associated with such environments, which are characterized by: • The simultaneous execution of multiple transaction types that span a breadth of complexity • On-line and deferred transaction execution modes • Multiple on-line terminal sessions • Moderate system and application execution time • Significant disk input/output • Transaction integrity (ACID properties) • Non-uniform distribution of data access through primary and secondary keys • Databases consisting of many tables with a wide variety of sizes, attributes, and relationships • Contention on data access and update Excerpt from “TPC BENCHMARK™ C: Standard Specification, Revision 3. 5”

Test Platform Simulate 200 Users

Carpenter Needs Tools Between the hardware, LINUX and Oracle – there are far too many dependent variables for tuning to rely merely on human intuition and experience. Free is nice – but you often get what you pay for! If your customer or management are willing to buy more hardware for better performance, then there is obviously budget for tuning tools – press the issue! Golden Rule #1: Use OS & DB tuning tools! Golden Rule #2: Don’t rely on free tools only!

Tools Used This is not a sales pitch – I’m a DBA (a. k. a techno-nerd)! Benchmark Factory • Create, populate and index the test database (200 megs) • Simulate 200 concurrent users via a single or many PC’s LINUX Freebies • Command line utilities: • X-Windows utilities: kperfmeter, sar, mpstat, iostat, vmstat, linmon, ipcs, top, free, hdparm, linuxconf, slmon, … gtop, ktop, xload, xosview, gkrellmm, procmeter, gpowertweak, …

Benchmark Factory - GUI

Benchmark Factory - Agent

LINUX cmd tools sar –r 01: 00 01: 10: 00 01: 20: 00 01: 30: 00 01: 40: 03 01: 50: 01 02: 00 PM kbmemfree kbmemused PM 465132 48128 PM 463352 49908 PM 463356 49904 PM 1652 511608 PM 1604 511656 PM 376852 136408 Average: 295324 %memused kbmemshrd kbbuffers 9. 37 0 2708 9. 72 0 2784 99. 67 0 532 99. 68 0 768 26. 57 0 1120 217936 42. 46 0 1782 kbcached kbswpfree kbswpused 26136 525288 0 26732 525288 0 26736 525288 0 18216 447360 77928 17228 369024 156264 25692 503344 21944 23456 482598 vmstat procs r b 0 5 0 1 0 3 0 1 0 0 0 5 1 2 1 0 0 1 1 0 3 1 2 1 1 0 2 0 5 0 2 0 0 w 0 0 0 0 0 0 swpd 122308 124608 127132 128836 130428 132052 133240 134920 136800 138400 139764 140780 141984 143044 143944 144940 146092 146820 147580 148144 148652 free 1588 1592 1596 1600 1588 1592 1600 1588 1596 1588 1596 1588 memory buff cache 440 17492 440 16992 432 17140 440 17344 468 17024 460 16940 444 16508 452 16596 448 16500 432 16576 468 16516 456 16680 444 16756 456 16516 464 16468 464 16320 468 16588 460 16416 440 16284 444 15776 448 15480 swap si so 0 124 0 230 0 253 0 171 0 159 2 164 1 120 0 168 12 190 0 160 0 136 5 102 0 120 0 106 0 90 2 100 0 115 18 73 2 76 0 51 io bi 145 194 164 172 189 181 166 138 184 158 183 126 157 185 138 179 147 135 125 134 109 bo 81 97 155 88 143 131 124 122 104 128 135 65 93 137 115 133 82 111 84 89 47 system in cs 906 338 1114 435 992 373 987 394 1104 426 1059 407 1006 394 870 344 1074 419 991 394 1086 433 815 332 957 388 1097 441 899 348 1099 442 910 367 882 338 841 343 890 361 767 316 us 5 8 11 7 8 6 7 9 9 9 10 10 12 8 8 7 8 6 7 cpu sy id 5 90 6 86 5 84 5 87 7 82 5 87 7 86 6 84 8 83 9 81 7 82 8 84 7 86 6 87 5 89 6 87 42690 %swpused 0. 00 14. 83 29. 74 4. 17 8. 12

LINUX gui tools

Here We Go Work up “Performance Pyramid”: • Application (TPC – no mods) • Database • Operating System • Hardware Other general benchmarking advice: • Limit to one item per try • OS Low Hanging Fruit 1 st • DB Low Hanging Fruit 2 nd • Easy items before hard stuff

DB 1 - Initial Database Creation Database Block Size = 2 K SGA Buffer Cache = 64 M SGA Shared Pool = 64 M SGA Redo Cache = 4 M Redo Log Files = 4 M Tablespaces = Dictionary Test database created via Oracle’s “Database Configuration Assistant” Prior to 9 i, the tool’s default settings were ridiculously lower than these. A novice DBA or system admin user might use those lower default values and get much worse results!

DB 2 – Increase Buffer Cache & Shared Pool Database Block Size = 2 K SGA Buffer Cache = 128 M SGA Shared Pool = 128 M SGA Redo Cache = 4 M Redo Log Files = 4 M Tablespaces = Dictionary

DB 3 – Increase Redo Cache & Log Files Database Block Size = 2 K SGA Buffer Cache = 128 M SGA Shared Pool = 128 M SGA Redo Cache = 16 M Redo Log Files = 16 M Tablespaces = Dictionary

DB 4 – 4 K Block Size Database Block Size = 4 K SGA Buffer Cache = 128 M SGA Shared Pool = 128 M SGA Redo Cache = 16 M Redo Log Files = 16 M Tablespaces = Dictionary

DB 5 – Local Tablespaces Database Block Size = 4 K SGA Buffer Cache = 128 M SGA Shared Pool = 128 M SGA Redo Cache = 16 M Redo Log Files = 4 M Tablespaces = Local

DB 6 – 8 K Block Size Database Block Size = 8 K SGA Buffer Cache = 128 M SGA Shared Pool = 128 M SGA Redo Cache = 16 M Redo Log Files = 16 M Tablespaces = Local Be careful – for example on RAC setup choosing larger block size may cause interconnect overload / hot-spots

DB 7 – IO Slaves + Increase Redo Log Files Database Block Size = 8 K SGA Buffer Cache = 128 M SGA Shared Pool = 128 M SGA Redo Cache = 16 M Redo Log Files = 64 M Tablespaces = Local INIT. ORA session_cached_cursors=2 db_block_lru_latches=8 dbwr_io_slaves=4 lgwr_io_slaves=4

Oracle Modification Results

OS 1 – IPC + Monolithic Kernel = 2. 2. 14 -5 smp Linuxconf = monolithic Shared memory /usr/src/linux-2. 2. 16/include/asm/shmparam. h #define SHMMAX 0 x 13000000 Semaphors /usr/src/linux-2. 2. 16/include/linux/sem. h #define SEMMNI 100 #define SEMMSL 512 #define SEMMNS (SEMMNI*SEMMSL) #define SEMOPM 100 #define SEMVMX 32767 Monolithic Kernel - If you compile everything into the kernel to exactly match your hardware and thus make minimal use of modules.

OS 2 – Newer Minor Kernel Update Kernel = 2. 2. 16 -3 smp Linuxconf = monolithic Shared memory /usr/src/linux-2. 2. 16/include/asm/shmparam. h #define SHMMAX 0 x 13000000 Semaphors /usr/src/linux-2. 2. 16/include/linux/sem. h #define SEMMNI 100 #define SEMMSL 512 #define SEMMNS (SEMMNI*SEMMSL) #define SEMOPM 100 #define SEMVMX 32767

OS 3 – Newer Major Kernel Update Kernel = 2. 4. 1 smp Linuxconf = monolithic Shared memory /usr/src/linux-2. 4. 00/include/linux/shm. h #define SHMMAX 0 x 13000000 Semaphors /usr/src/linux-2. 4. 00/include/linux/sem. h #define SEMMNI 128 #define SEMMSL 512 #define SEMMNS (SEMMNI*SEMMSL) #define SEMOPM 128 #define SEMVMX 32767 Edit /etc/sysctl. conf kernel. shmmax = 2147483647 kernel. sem = 250 32000 128 The sem values are: • SEMMSL • SEMMNS • SEMOPM • SEMMNI To set w/out a reboot on Redhat: sysctl -p

OS 4 – Newer Minor Kernel Update After Major Kernel = 2. 4. 17 smp Linuxconf = monolithic Shared memory /usr/src/linux-2. 4. 00/include/linux/shm. h #define SHMMAX 0 x 13000000 Semaphors /usr/src/linux-2. 4. 00/include/linux/sem. h #define SEMMNI 128 #define SEMMSL 512 #define SEMMNS (SEMMNI*SEMMSL) #define SEMOPM 128 #define SEMVMX 32767 Edit /etc/sysctl. conf kernel. shmmax = 2147483647 kernel. sem = 250 32000 128 The sem values are: • SEMMSL • SEMMNS • SEMOPM • SEMMNI To set w/out a reboot on Redhat: sysctl -p

OS 5 – noatime file attribute The ext 2 file system normally records when a file was last modified and last accessed. We don’t need to know access time for Oracle files as background programs open and access the files until shutdown. chattr +A file_name Actually, this can be done on Windows server as well! HKEY_LOCAL_MACHINESy stemCurrent. Control. SetControl File. System chattr –R +A directory_name Ntfs. Disable. Last. Access. Update=1 Edit /etc/fstab /dev/sda 6 /dev/sda 1 /dev/cdrom /dev/md 0 /dev/fd 0 none /dev/sda 5 / /boot /mnt/cdrom /u 01 /mnt/floppy /proc /dev/pts swap ext 2 iso 9660 ext 2 auto proc devpts swap defaults, noatime noauto, owner, ro defaults, noatime noauto, owner defaults, noatime gid=5, mode=620 defaults, noatime 1 1 0 0 0 0 1 2 0 0 0 0

OS 6 – bdflush rate for VM The bdflush file is closely related to the operation of the virtual memory (VM) subsystem of the Linux kernel and also has a little influence on disk usage. Default = "40 500 64 256 500 3000 500 1884 2" Redhat 6. 1 echo “ 100 128 512 15 500 1884 2” > /proc/sys/vm/bdflush Redhat 6. 2 (edit /etc/sysctl. conf) vm. bdflush = 100 128 512 15 5000 500 1884 2 Restart daemon /etc/rc. d/init. d/network restart 1 st parm is max # of dirty buffers in cache. Higher = delayed disk writes. 2 nd parm is max # of dirty buffers per write. Higher = delayed, bursty I/O. 3 rd parm is # of buffers added to list of free buffers by refill_freelist. 4 th parm refill_freelist comes across more than nref_dirty buffers, it will wake up bdflush. 5 th parm is max time Linux waits before writing dirty buffers to disk for data blocks. 6 th parm is max time Linux waits before writing dirty buffers to disk for file system metadata.

OS 7 – Ext 3 File System Ext 3 is a journaling file system based upon ext 2 – benefits include: • both backward and forward compatible with ext 2 • easy to change from ext 2 to ext 3 and gain the benefits without reformatting • does not require a file system check even after an unclean system shutdown • recovery time does not depend on the size of the file system or the number of files • provide stronger guarantees about data integrity in case of an unclean system shutdown • faster (higher throughput) than ext 2 as ext 3's journaling optimizes hard drive head motion

LINUX Modification Results

Some Final Thoughts IDE Drives • Default IDE settings stink – they must be changed and generally yield 500% improvement!!! • Test speed via hdparm –Tt /dev/hda • Tune spped via hdparm –X 66 –d 1 –u 1 –m 16 –c 3 /dev/hda MTS • Problematic under LINUX, more than a few TAR’s registered with Oracle technical support Hardware RAID • LINUX driver support generally tier 2 and often provided by someone other than the vendor 4 K File System • mke 2 fs –b 4096 mount_point • LINUX default file system block size = 1 K, but max is 4 K • Typical improvement of 5% for each increase (i. e. 5% for 1 k–>2 k and 5% for 2 k–>4 k) PGCC Compiler • www. goof. com/pcg • PGCC is a version of the GNU C compiler (gcc) with special optimizations for Pentium CPU • The 30% improvement achieved by the Intel engineers is rare in the real world (don’t bank on it) • Typical improvement of 5% using intermediate optimization level like -O 3 and no fine-tuning • Recompile kernel – only small improvement since Oracle makes few calls to LINUX kernel • Recompile glibc – bigger improvement possible, but headache unless already know process

Questions and Answers … Thank You Presenters: Bert Scalzo: Bert. Scalzo@Quest. com Note: these slides should be available on OOUG web site, but we’ll also make sure to post them on our company’s web site: www. toadworld. com/Experts/Bert. Scalzos. Toad. Fanaticism/tabid/318/Default. aspx