{"id":16,"date":"2018-10-04T10:13:00","date_gmt":"2018-10-04T10:13:00","guid":{"rendered":""},"modified":"2018-12-10T14:09:58","modified_gmt":"2018-12-10T14:09:58","slug":"hammerdb-best-practice-for-oracle-performance-and-scalability","status":"publish","type":"post","link":"https:\/\/www.hammerdb.com\/blog\/uncategorized\/hammerdb-best-practice-for-oracle-performance-and-scalability\/","title":{"rendered":"HammerDB Best Practice for Oracle Performance and Scalability"},"content":{"rendered":"
With the move to HammerDB v3.0 as well as refactoring a lot of code the documentation moved to Docbook format. Previously at v2 there was an Oracle best practice document that is no longer included in the current documentation although a number of people have asked for it.\u00a0 Nevertheless lot of this information remains relevant to HammerDB v3.0 and v3.1 including gathering a number of links to older blog posts focusing on specific areas such as CPU, Memory and I\/O. Working through this base configuration information can give a significant head start in configuring an Oracle Database to get the best results from HammerDB. As an Intel employee (#IAMINTEL) the examples are taken from Oracle on Linux on Intel although the approach is the same for whatever system you are testing.<\/span><\/div>\n
CPU, Memory and I\/O<\/span><\/h6>\n
The key dependence of performance is hardware related with the CPU being the most important factor on the levels of performance available from the rest of the system. At the next level from the CPU is memory with the best levels of performance available from having sufficient memory to cache all of the test database. Finally I\/O performance is crucial with modern systems and CPUs available to drive high levels of throughput, In particular for OLTP workloads write performance to transaction logs is critical and often a major resource constraint.\u00a0 Solid State Disks (SSDs) are strongly recommended for both data areas and redo logs to provide the I\/O capabilities to match the CPU performance of up to date systems. <\/span><\/span><\/div>\n
BIOS Settings<\/span><\/h6>\n
Systems are shipped with default BIOS and are not necessarily optimized for database performance.\u00a0 BIOS settings should be checked and settings verified with the vendor to ensure that they are advantageous to Oracle Performance. A common error is to accept a default setting of \u201cHigh Performance\u201d that sets a subset of lower level BIOS settings without verifying what these are.\u00a0 A default setting of \u201cHigh Performance\u201d will often result in lower performance for a database environment. <\/span><\/span><\/div>\n
Power Saving<\/span><\/h6>\n
Modern CPUs and systems running Linux are designed to offer high levels performance whilst incorporating power saving features such as Turbo Boost and C-state and P-state management.\u00a0 Firstly you should ensure that your version of Linux paying particular attention to the kernel version is compatible with the CPU in your system. You should not assume full compatibility until verified.\u00a0 Once you have done this check the features of your CPU model and verify your configuration of the scaling governor and energy performance bias with tools such as PowerTop and turbostat. Full details on how to do this are provided in the blog post <\/span>How to Maximise CPU Performance for the Oracle Database on Linux<\/span><\/a><\/span>.<\/span><\/b><\/span> <\/b><\/div>\n
Verify Single Threaded Performance<\/span><\/h6>\n
Once you have set your BIOS settings and Power Saving settings verify that you achieve maximum CPU single threaded performance by creating and running the following stored procedure:<\/span><\/span><\/div>\n
SET SERVEROUTPUT ON <\/span>\r\nSET TIMING ON <\/span>\r\nDECLARE <\/span>\r\nn NUMBER := 0; <\/span>\r\nBEGIN <\/span>\r\nFOR f IN 1..10000000 <\/span>\r\nLOOP <\/span>\r\nn := MOD (n,999999) + SQRT (f); <\/span>\r\nEND LOOP; <\/span>\r\nDBMS_OUTPUT.PUT_LINE ('Res = '||TO_CHAR (n,'999999.99')); <\/span>\r\nEND; <\/span>\r\n\/ <\/span><\/pre>\n
More details on running this test are here <\/span>Testing C-State Settings and Performance with the Oracle Database on Linux<\/span><\/a><\/span>.<\/span>\u00a0<\/b>The test will produce a result such as follows where the elapsed time is important.<\/span><\/span><\/div>\n
Res = 873729.72<\/span>\r\nPL\/SQL procedure successfully completed.<\/span>\r\nElapsed: 00:00:07.88<\/span><\/span><\/pre>\n
Results will vary according to CPU model however typically a modern CPU will complete this test in 10 seconds or less according to configuration ensuring both system and Linux power saving settings are set optimally for Oracle performance.\u00a0<\/span><\/span><\/p>\n
Hyper-Threading<\/h6>\n
Previously you will have determined your CPU features. One CPU feature that may be available is Hyper-Threading.\u00a0 If this feature is available it should be enabled and under a correctly configured system will result in a performance gain.\u00a0 Details on the benefits of Hyper-Threading are available here Hyper-Threading On or Off for Oracle?<\/span><\/a><\/span><\/span><\/div>\n
Memory<\/span><\/h6>\n
Correctly configuring memory is essential to Oracle performance.\u00a0 On Linux you should ensure that you have your Oracle memory correctly configured for Huge Pages (Not transparent<\/u>huge pages). Follow the guide detailed here to ensure that your memory is correctly configured.\u00a0 <\/span>Configuring Memory for Oracle on Linux<\/span><\/a><\/span><\/span><\/div>\n
I\/O and SSDs<\/span><\/h6>\n
After correctly configuring memory your focus should be on I\/O or disk performance. This focus lies in 2 areas firstly the data area defined by the tablespace or tablespaces you create to configure your schema and secondly the redo logs.\u00a0 For both data and redo the best performance available is from good quality and correctly configured SSDs (solid state disks). Further information on SSDs is available in the post Should you put Oracle Database Redo on Solid State Disks (SSDs)?<\/a>.<\/b><\/span>\u00a0 <\/span>Configuring the data area should be done in conjunction with configuring memory for the buffer cache. With sufficient memory you can expect most of your data blocks to be cached and therefore interaction with the data area until a checkpoint occurs may be minimal. On the other hand an OLTP workload will continually write to the redo log file and throughput should be high and latency low (< 1ms) to achieve the highest transaction numbers.\u00a0\u00a0 With SSDs careful consideration should be given to setup a<\/span>nd Linux Database Performance on SSD 910 with Filesystem<\/span><\/a>detail how this should be done. <\/span><\/span><\/b><\/div>\n
Network Bandwidth<\/span><\/h6>\n
Oracle OCI is network efficient and issues should not be found with high throughput even on a typical Gigabit Ethernet network. If system network utilisation does exceed Gigabit capacity between load generation server and SUT to increase capacity you may use either use 10GbE or configure NIC teaming for multiple Gigabit Ethernet adapters on both the server and both the LACP compatible switch in LACP mode.\u00a0 \u00a0<\/span><\/a><\/span><\/div>\n
Oracle Parameters<\/span><\/span><\/h6>\n
Y<\/span>our Oracle parameters will vary according to the size and configuration of your system and therefore there is no single recommend Oracle parameter file.\u00a0 Nevertheless standard best practice includes sizing shared memory manually rather than automatically and disabling database features that are not currently in use. <\/span><\/span><\/span><\/div>\n
\u00a0As an example the following white paper shows a configuration file used for a large high performance system<\/span> <\/span>Mission-Critical Database Performance: Intel Xeon Processor E7 V2 Family vs. Ibm Power7+<\/span><\/span><\/span><\/a>.<\/span><\/b><\/span><\/span><\/span><\/div>\n
Database Creation<\/span><\/h6>\n
Bu<\/span>ilding the HammerDB schema directly on the database server will be quicker as you will be able to take advantage of more cores and not be required to pass all of the data across the network. You should already have your database software installed and Oracle running. \u00a0Before running the schema creation create the tablespace required for your data area.\u00a0 Ensure that this file is suitably large not to autoextend and to allow for sufficient table growth during tests.<\/span><\/span><\/div>\n
Schema Build and Configure<\/span><\/h6>\n
If you select a schema of more than 200 warehouses the option to partition the order line becomes available. When running on larger systems selecting this option will enable high levels of scalability. Additionally there is the option to select a separate order line tablespace from the tablespace where the other tables are located and therefore also to create this tablespace with a separate block size.\u00a0 Previously the Oracle parameter to create a separate cache for this block size will have been chosen. For example: <\/span><\/span><\/div>\n
db_16k_cache_size=32749125632<\/pre>\n
As the order line tablespace is an IOT (index organized table) for highly scalable systems this will benefit from choosing a larger blocksize for this tablespace such as 16k with a standard blocksize used of 8k. This has the effect of reducing the height of the IOT and lower the number of index block splits resulting in higher performance. <\/span><\/span><\/div>\n
<\/a><\/div>\n
Resize the Redo Log Files<\/span><\/h6>\n
The importance of correctly sizing the redo logs cannot be understated. The sizing of your redo logs is related to checkpointing activity and further details on Oracle checkpoints are described here <\/span>What’s the Point of Oracle Checkpoints?<\/span><\/a><\/span>.<\/span>\u00a0 <\/span><\/span><\/b><\/span>When your Oracle database checkpoints it will trigger the database writers to flush the modified data blocks from the buffer cache back to disk. \u00a0In particular if Oracle needs to wait to reassign a redo log containing changes not yet written to disk a message of \u201cCheckpoint not complete\u201d will be recorded in the alert log and the database will stop processing transaction until it is complete.\u00a0 It is recommended to set the following Oracle parameter to monitor all checkpoint activity in the alert log. <\/span><\/span><\/div>\n
log_checkpoints_to_alert=TRUE<\/span><\/span><\/pre>\n
Depending on your I\/O capacity this may impact performance. A checkpoint may occur as a result of a redo log switch depending on the contents of that redo log at the time. Recommended redo log file parameters to ensure this behaviour are: <\/span><\/span><\/div>\n
fast_start_mttr_target=0<\/span>\r\nlog_checkpoint_interval=0<\/span>\r\nlog_checkpoint_timeout=0<\/span><\/pre>\n
\u00a0Nevertheless redo log files (especially from an OLTP test) cannot be sized too large and an effective strategy is to use the data from Oracle to size 2 or more redo log files large enough to delay checkpointing until the end of the test.\u00a0 For example the following extract of a load profile from an AWR report shows a redo rate of 405MB per second.\u00a0 <\/span><\/span><\/div>\n
Load Profile\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Per Second\u00a0\u00a0 Per Transaction\u00a0 Per Exec\u00a0 Per Call<\/span>\r\n~~~~~~~~~~~~~~~\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>---------------\u00a0\u00a0 <\/span>--------------- --------- ---------<\/span>\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>DB Time(s):\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>0.0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>0.0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>0.00\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>0.00<\/span>\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>DB CPU(s):\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>0.0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>0.0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>0.00\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>0.00<\/span>\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 Redo size (bytes):\u00a0\u00a0\u00a0\u00a0 405,860,130.7\u00a0<\/b>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 5,345.2<\/span>\r\n\u00a0 <\/span>Logical read (blocks):\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>7,892,009.8\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>103.9<\/span>\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Block changes:\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2,377,433.9\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 31.3<\/span><\/span><\/pre>\n
Consequently depending on the contents of the other redo log files a log switch checkpoint could be triggered at the end of a 25GB redo log file every minute and therefore a redo log size of 250GB in size would be configured for a 10 minute test.\u00a0 Use the views v$LOG and V$LOGFILE to correctly size the redo logs.\u00a0 Note that if you select \u201cCheckpoint when complete\u201d in the Driver Options a full checkpoint and a redo log switch will be triggered after a test is complete, resetting Oracle to start the next test at the start of the next redo log file in sequence. <\/span><\/span><\/div>\n
<\/a><\/div>\n
As shown observe performance for an entire test to ensure that the transaction counter is level showing that Oracle performance is consistent without a drop in performance for checkpoints. <\/span><\/span><\/div>\n
<\/a><\/div>\n
Monitoring<\/span><\/h6>\n
HammerDB Metrics provides a reliable way to monitor CPU performance on every core in your system. Maximum performance will be achieved when every core is fully utilised as shown in Figure 4.\u00a0 However it is not necessarily the case that full utilisation means maximum performance has been achieved. It is possible for misconfiguration to mean that CPU utilisation is high yet throughput is low due to contention. <\/span><\/span><\/div>\n
<\/a><\/div>\n
HammerDB provides the facility to automatically generate AWR reports that correspond with the OLTP workload. Therefore these AWR reports should be your primary tuning tool to ensure that your Oracle environment is correctly configured. As shown the first point if reference should be the Top Foreground Events and you should aim for DB CPU as a percentage of time over 90% as shown to coincide with the OS report of CPU utilisation as shown in Figure 4 with HammerDB Metrics. <\/span><\/span><\/div>\n
\u00a0<\/span><\/div>\n
Top 10 Foreground Events by Total Wait Time <\/span><\/b><\/div>\n\n\n\n\n\n\n\n\n\n\n\n\n\n
\n
Event<\/span><\/span><\/b><\/div>\n<\/td>\n
\n
Waits<\/span><\/span><\/b><\/div>\n<\/td>\n
\n
Total Wait Time (sec)<\/span><\/span><\/b><\/div>\n<\/td>\n
\n
Wait Avg(ms)<\/span><\/span><\/b><\/div>\n<\/td>\n
\n
% DB time<\/span><\/span><\/b><\/div>\n<\/td>\n
\n
Wait Class<\/span><\/span><\/b><\/div>\n<\/td>\n<\/tr>\n
\n
DB CPU<\/span><\/span><\/div>\n<\/td>\n
\n
<\/div>\n<\/td>\n
\n
30.1K<\/span><\/span><\/div>\n<\/td>\n
\n
<\/div>\n<\/td>\n
\n
96.9<\/span><\/span><\/div>\n<\/td>\n
\n
<\/div>\n<\/td>\n<\/tr>\n
\n
library cache: mutex X<\/span><\/span><\/div>\n<\/td>\n
\n
936,935<\/span><\/span><\/div>\n<\/td>\n
\n
364.2<\/span><\/span><\/div>\n<\/td>\n
\n
0<\/span><\/span><\/div>\n<\/td>\n
\n
1.2<\/span><\/span><\/div>\n<\/td>\n
\n
Concurrency<\/span><\/span><\/div>\n<\/td>\n<\/tr>\n
\n
cursor: pin S<\/span><\/span><\/div>\n<\/td>\n
\n
210,234<\/span><\/span><\/div>\n<\/td>\n
\n
315.5<\/span><\/span><\/div>\n<\/td>\n
\n
2<\/span><\/span><\/div>\n<\/td>\n
\n
1.0<\/span><\/span><\/div>\n<\/td>\n
\n
Concurrency<\/span><\/span><\/div>\n<\/td>\n<\/tr>\n
\n
enq: TX – row lock contention<\/span><\/span><\/div>\n<\/td>\n
\n
212,383<\/span><\/span><\/div>\n<\/td>\n
\n
228.2<\/span><\/span><\/div>\n<\/td>\n
\n
1<\/span><\/span><\/div>\n<\/td>\n
\n
.7<\/span><\/span><\/div>\n<\/td>\n
\n
Application<\/span><\/span><\/div>\n<\/td>\n<\/tr>\n
\n
log file sync<\/span><\/span><\/div>\n<\/td>\n
\n
28,919<\/span><\/span><\/div>\n<\/td>\n
\n
82.3<\/span><\/span><\/div>\n<\/td>\n
\n
3<\/span><\/span><\/div>\n<\/td>\n
\n
.3<\/span><\/span><\/div>\n<\/td>\n
\n
Commit<\/span><\/span><\/div>\n<\/td>\n<\/tr>\n
\n
latch: In memory undo latch<\/span><\/span><\/div>\n<\/td>\n
\n
693,091<\/span><\/span><\/div>\n<\/td>\n
\n
82.3<\/span><\/span><\/div>\n<\/td>\n
\n
0<\/span><\/span><\/div>\n<\/td>\n
\n
.3<\/span><\/span><\/div>\n<\/td>\n
\n
Concurrency<\/span><\/span><\/div>\n<\/td>\n<\/tr>\n
\n
db file sequential read<\/span><\/span><\/div>\n<\/td>\n
\n
55,642<\/span><\/span><\/div>\n<\/td>\n
\n
46.4<\/span><\/span><\/div>\n<\/td>\n
\n
1<\/span><\/span><\/div>\n<\/td>\n
\n
.1<\/span><\/span><\/div>\n<\/td>\n
\n
User I\/O<\/span><\/span><\/div>\n<\/td>\n<\/tr>\n
\n
buffer busy waits<\/span><\/span><\/div>\n<\/td>\n
\n
222,183<\/span><\/span><\/div>\n<\/td>\n
\n
23.5<\/span><\/span><\/div>\n<\/td>\n
\n
0<\/span><\/span><\/div>\n<\/td>\n
\n
.1<\/span><\/span><\/div>\n<\/td>\n
\n
Concurrency<\/span><\/span><\/div>\n<\/td>\n<\/tr>\n
\n
latch: enqueue hash chains<\/span><\/span><\/div>\n<\/td>\n
\n
11,122<\/span><\/span><\/div>\n<\/td>\n
\n
14.1<\/span><\/span><\/div>\n<\/td>\n
\n
1<\/span><\/span><\/div>\n<\/td>\n
\n
.0<\/span><\/span><\/div>\n<\/td>\n
\n
Other<\/span><\/span><\/div>\n<\/td>\n<\/tr>\n
\n
SQL*Net message to client<\/span><\/span><\/div>\n<\/td>\n
\n
13,132,309<\/span><\/span><\/div>\n<\/td>\n
\n
12.1<\/span><\/span><\/div>\n<\/td>\n
\n
0<\/span><\/span><\/div>\n<\/td>\n
\n
.0<\/span><\/span><\/div>\n<\/td>\n
\n
Network<\/span><\/span><\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
If this is not achievable use the rest of the AWR report to diagnose performance, in particular as shown CPU time and elapsed time should be checked to be as close together as possible for maximum efficiency. If not then it is necessary to determine the reason why this is not the case ans what accounts for the additional elapsed time. <\/span><\/span><\/div>\n\n\n\n\n\n\n
\n
CPU Time (s)<\/span><\/span><\/b><\/div>\n<\/td>\n
\n
Executions <\/span><\/span><\/b><\/div>\n<\/td>\n
\n
CPU per Exec (s)<\/span><\/span><\/b><\/div>\n<\/td>\n
\n
%Total<\/span><\/span><\/b><\/div>\n<\/td>\n
\n
Elapsed Time (s)<\/span><\/span><\/b><\/div>\n<\/td>\n
\n
%CPU<\/span><\/span><\/b><\/div>\n<\/td>\n
\n
%IO<\/span><\/span><\/b><\/div>\n<\/td>\n
\n
SQL Id<\/span><\/span><\/b><\/div>\n<\/td>\n
\n
SQL Module<\/span><\/span><\/b><\/div>\n<\/td>\n
\n
SQL Text<\/span><\/span><\/b><\/div>\n<\/td>\n<\/tr>\n
\n
15,894.75<\/span><\/span><\/div>\n<\/td>\n
\n
5,704,467<\/span><\/span><\/div>\n<\/td>\n
\n
0.00<\/span><\/span><\/div>\n<\/td>\n
\n
52.73<\/span><\/span><\/div>\n<\/td>\n
\n
19,048.04<\/span><\/span><\/div>\n<\/td>\n
\n
83.45<\/span><\/span><\/div>\n<\/td>\n
\n
0.01<\/span><\/span><\/div>\n<\/td>\n
\n\n
wish8.6@wesep1.example.com (TNS V1-V3) <\/span><\/span><\/div>\n<\/td>\n
\n
begin neword(:no_w_id, :no_max…<\/span><\/span><\/div>\n<\/td>\n<\/tr>\n
\n
3,663.11<\/span><\/span><\/div>\n<\/td>\n
\n
571,496<\/span><\/span><\/div>\n<\/td>\n
\n
0.01<\/span><\/span><\/div>\n<\/td>\n
\n
12.15<\/span><\/span><\/div>\n<\/td>\n
\n
3,946.36<\/span><\/span><\/div>\n<\/td>\n
\n
92.82<\/span><\/span><\/div>\n<\/td>\n
\n
0.00<\/span><\/span><\/div>\n<\/td>\n
\n\n
wish8.6@wesep1.example.com (TNS V1-V3) <\/span><\/span><\/div>\n<\/td>\n
\n
BEGIN delivery(:d_w_id, :d_o_c…<\/span><\/span><\/div>\n<\/td>\n<\/tr>\n
\n
<\/div>\n<\/td>\n
\n
<\/div>\n<\/td>\n
\n
<\/div>\n<\/td>\n
\n
<\/div>\n<\/td>\n
\n
<\/div>\n<\/td>\n
\n
<\/div>\n<\/td>\n
\n
<\/div>\n<\/td>\n
\n
<\/div>\n<\/td>\n
\n
<\/div>\n<\/td>\n
\n
<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
On very high performance systems if contention is observed in the library cache then the markhot package can be used to reduce this.<\/span><\/span><\/div>\n
More posts are planned on advanced performance tuning.<\/span><\/span><\/div>\n