Running and Managing Oracle Databases on OpenVMS (Apr 2016) - PowerPoint PPT Presentation

Running and Managing Oracle Databases on OpenVMS (Apr ’ 2016) Maklee Engineering Christian Moser Chief Technology Officer cmos@maklee.com

Agenda • Oracle 11g on OpenVMS • Configuration and Tuning • Performance monitoring tools

Oracle 11g on OpenVMS • The long wait is finally over! • Oracle finally released 11.2.0.4 for OpenVMS – (Summer 2015) • Most customers we worked with saw 10% - 20% performance increase after upgrading from 10g to 11g • We decided to take it for a spin.... – We benchmarked Oracle 10.2.0.5 on OpenVMS V8.4 against Oracle 11.2.0.4 on OpenVMS V8.4-1H1 – We were looking at 2 common workload benchmarks, Swingbench and SLOB

Hardware Configuration – Oracle 10g • HP Integrity rx2800 i2 (1.33GHz/4.0MB) • 8 CPU Cores • 64GB Memory • OpenVMS V8.4 • Oracle 10.2.0.5 • 20GB SGA • 30GB Database

Hardware Configuration – Oracle 11g • HP Integrity rx2800 i4 (2.53GHz/32.0MB) • 16 CPU Cores (only 8 used for the benchmark) • 128GB Memory • OpenVMS V8.4-1H1 • Oracle 11.2.0.4 • 24GB SGA • 30GB Database

Swingbench • Swingbench is a free load generator (and benchmark) designed to stress test an Oracle database (10g, 11g, 12c) • Our test measured the amount of work that can be performed during a 15 minute run • 60 Users • Benchmark (according to database wait events) is about 50% CPU and 50% I/O • Order Entry Benchmark • Database has been bounced (to clear cache) between runs

Transactions Committed Per Minute Transactions Committed Per Minute (More is better)

New Order Latency Latency Per New Order (Less is better)

Order Entry Benchmark Results • Out of the box 11g was about 2.7X faster than 10g – Faster processor speed account for some of the improvement – The i4 processors have higher clock frequency 2.53GHz vs 1.33GHz – Oracle 11g is faster and more efficient than Oracle 10g – Most customers we work with see 10% - 20% improvement just by upgrading

Silly Little Oracle Benchmark (SLOB) • Benchmark is testing both CPU and I/O subsystem • 10 different user schemas run for 10 min – Avoid contention – Random SELECT and UPDATE statements – 30% update and 70% select queries – Table contains 10,000 blocks with 1 row per block • Results – SELECT is 2.5X better (11g vs 10g) – UPDATE is 10X better (11g vs 10g)

Silly Little Oracle Benchmark (SLOB)

Oracle on OpenVMS • Oracle is “just another” user mode application – (almost) O/S agnostic – Datafiles are compatible between platforms – Does not rely on OpenVMS specific mechanism (locking, ASTs, etc) – Exception is mapping to physical memory and keep memory resident – It is a large application, many processes, requires large quotas – Requires a good I/O subsystem – Use the available physical memory – More CPUs is a good thing

System Recommendations • General OpenVMS system tuning – Enough paged and non-paged pool – Smaller XFC cache (Oracle uses own buffer cache), no need to double cache – Correctly assign fastpath devices – Use site id if multi-site host-based shadowing – Disable caching for Oracle datafiles • $ set file /caching=no_caching <datafile>

Hyperthreading • According to Intel ’ s marketing numbers, HTs provide up to 25% performance increase • Not suitable for all applications • May degrade performance for some applications • Requires testing • Rule of thumb – If high CPU utilization and COMputable processes – it might help • No clear yes/no answer

TCPIP • TCPIP Tuning parameters – Enable local I/O post-processing for TCPIP – Increase socket parameter – Add to TCPIP$ETC:SYSCONFIGTAB.DAT net: ovms_iopost_local = 1 inet: pmtu_enabled = 0 tcbhashsize = 16384 tcbhashnum = 16 socket: somaxconn = 65535 sominconn = 65535

Storage Subsystem • Running some IOT tests to verify IO performance – Larger IO’s to measure throughput  1.5 GB/sec – Smaller IO’s to measure IO rate  30,000 IO/sec • Good 3PAR performance $ iot /count=50000 /op=read /pat=rand /size=512 /que=64 - $1$DGA8950 $1$dga8951 $1$dga8952 $1$dga8953 _$1$DGA8950: (DGX00 DG "3PARdata VV") 49632 xfer, 33.440 sec,1484.2 xfer/sec, 379958 Kb/sec _$1$DGA8951: (DGX00 DG "3PARdata VV") 49928 xfer, 33.440 sec,1493.1 xfer/sec, 382224 Kb/sec _$1$DGA8952: (DGX00 DG "3PARdata VV") 49743 xfer, 33.440 sec,1487.5 xfer/sec, 380808 Kb/sec _$1$DGA8953: (DGX00 DG "3PARdata VV") 50000 xfer, 33.440 sec,1495.2 xfer/sec, 382775 Kb/sec Total (p < 0.001)199303 xfer, 33.440 sec,5960.0 xfer/sec,1525765 Kb/sec $ iot /count=100000 /op=read /pat=rand /size=16 /que=64 - $1$DGA8950 $1$dga8951 $1$dga8952 $1$dga8953 _$1$DGA8950: (DGX00 DG "3PARdata VV") 98002 xfer, 13.150 sec,7452.6 xfer/sec, 59621 Kb/sec _$1$DGA8951: (DGX00 DG "3PARdata VV")100000 xfer, 13.150 sec,7604.6 xfer/sec, 60837 Kb/sec _$1$DGA8952: (DGX00 DG "3PARdata VV") 98276 xfer, 13.150 sec,7473.5 xfer/sec, 59788 Kb/sec _$1$DGA8953: (DGX00 DG "3PARdata VV") 99892 xfer, 13.150 sec,7596.3 xfer/sec, 60771 Kb/sec Total (p < 0.001)396170 xfer, 13.160 sec,30104.1 xfer/sec, 240833 Kb/sec

Memory Resident • Pin Oracle ’ s SGA to physical memory • Add or increase the reserved memory – Larger SGA size, requires system reboot • SYSMAN reserved memory, units is in MB – $ mc sysman reserve add ora_<sid>_sga /size=23000 /page /allo /zero – $ mc sysman reserve mod ora_<sid>_sga /size=30720 /page /allo /zero – Pick a ” round ” number to avoid multiple chunks – Oracle database parameter LOCK_SGA ignored on OpenVMS – Verify Oracle is using memory resident sections (page in use is non-zero) $ show mem/res System Memory Resources on 15-SEP-2015 04:39:23.49 Memory Reservations (pages): Group Reserved In Use Type ORA_ORA11_SGA SYSGBL 3840 0 Page Table ORA_ORA11_SGA SYSGBL 3670016 0 Allocated ORA_ORA11_SGA SYSGBL 262144 0 Allocated Total (30.02 GBytes reserved) 3936000 0

Memory Resident (cont’d) $ mc sysman res show %SYSMAN-I-OUTPUT, command execution on node UEHARA Reservation Name Group RAD Size (MB) Pages Pg In Use Attributes ORA_ORA11_SGA SYSGBL ANY 2875 2875 Allocated PageTables ORA_ORA11_SGA SYSGBL ANY 20480 2621440 2621440 Allocated ORA_ORA11_SGA SYSGBL ANY 2048 262144 2568 Allocated ORA_ORA11_SGA SYSGBL ANY 256 32768 2184 Allocated ORA_ORA11_SGA SYSGBL ANY 192 24576 2081 Allocated ORA_ORA11_SGA SYSGBL ANY 16 2048 545 Allocated ORA_ORA11_SGA SYSGBL ANY 8 1024 545 Allocated $ mc sysman res list %SYSMAN-I-OUTPUT, command execution on node UEHARA Reservation Name Group RAD Size (MB) Pages Attributes ORA_ORA11_SGA SYSGBL ANY 30720 3932160 Allocated Zeroed ORA_ORA11_SGA SYSGBL 3840 PageTables Allocated $ mc sysman res show %SYSMAN-I-OUTPUT, command execution on node UEHARA Reservation Name Group RAD Size (MB) Pages Pg In Use Attributes ORA_ORA11_SGA SYSGBL ANY 3840 3840 Allocated PageTables ORA_ORA11_SGA SYSGBL ANY 28672 3670016 3670016 Allocated ORA_ORA11_SGA SYSGBL ANY 2048 262144 245491 Allocated

Install Images Memory Resident • Most important OpenVMS related shareable images are already installed /resident • Make sure granularity hint region values are large enough – GH_RES_CODE larger than 512MB will result in SYSBOOT bugcheck and end up in XDelta • Install Oracle images resident – Large image, code section is 366MB for 11g – Modify INSORACLE.COM – $ install add ora_root:[bin]oracle.exe /open /head /share=addr /resident – Might not fit into 512MB with other resident installed images – Re-link and put code into P2 space and use S2 space for memory resident

Granularity Hint Regions • Enlarge GH_xxx parameters and reboot $ mc sysgen sh load_sys Parameter Name Current Default Min. Max. Unit Dynamic -------------- ------- ------- ------- ------- ---- ------- LOAD_SYS_IMAGES 3 7 0 31 Bitmask $ mc sysgen sh gh Parameter Name Current Default Min. Max. Unit Dynamic -------------- ------- ------- ------- ------- ---- ------- GH_EXEC_CODE 8192 4096 4096 65536 Pages GH_EXEC_DATA 2048 2048 1024 65536 Pages GH_RES_CODE 65536 5120 0 1048576 Pages GH_RES_DATA 65536 512 0 65536 Pages GH_RSRVPGCNT 0 0 0 -1 Pages GH_RES_CODE_S2 65536 0 0 1048576 Pages