Blades or Rack-Mounted?

Blades have established themselves as suitable alternatives to the classical 19-inch rack-mounted server for many workloads. Blades are usually smaller than rack-mounted servers, but they also have fewer components and fewer possibilities for expansion. Some of the components you would normally find inside a rack-mounted server will be in the so-called blade enclosure. The small form factor for blades will allow a very high density, and when they were first introduced, blades seemed ideal to reduce the space required in the data center. However, with the high density comes a heightened requirement to cool the blades and enclosure to prevent them from overheating. With the introduction of chips capable of adjusting their clock rate depending on workload, cooling becomes even more important. Often the chips need to be in their comfort zone when it comes to their Thermal Design Power (TDP). As soon as the processor temperature rises too high, it will clock down and run at reduced speed to prevent damage to its components. Sufficient cooling of modern CPUs is therefore essential to stable CPU performance! Luckily the ever shrinking manufacturing process for processors allows for a reduction of cooling requirements. Processor vendors have realized that a power-efficient processor is a real necessity in the drive to cut cost in data centers.

There is no general industry standard blade enclosure, but you can expect most blade enclosures to contain power supply units, networking equipment to support storage and client connectivity, and other shared infrastructure. The introduction of Data Center Ethernet or Converged Ethernet allows vendors to use high speed Ethernet switches for network and storage traffic, potentially reducing the number of cables in the data center. The matured graphical management interfaces allow the administrator to perform lights-out management of his blades. Graphical representations of the blade enclosure tell the administrator to see which slots are free and which ones are occupied. Often warnings and alerts can be sent via SNMP (Simple Network Management Protocol) traps to monitoring solutions.

A blade enclosure would be of little use if it were not for the blades. The little, yet powerful computers can either be horizontally or vertically added into the enclosure. Depending on the model, they can either occupy a full slot or half a slot. The terms full-width and half-width (or height) have been coined for these. Blades are highly versatile and configurable. If your data center can accommodate them, they are definitely worth evaluating.

Rack-mounted servers have been the predominant form of servers and probably still are. The typical rack-mounted server is measured in unit height; the width is predetermined by the rack you are mounting the server to. The industry currently uses mainly 19-inch- and occasionally 23-inch-wide racks. The rack unit corresponds to 1.75 inches, and a typical 19-inch rack has support for 42 units. With the basic parameters set, the IT department is free to choose whatever hardware is 19-inch-wide and otherwise fits inside the rack. The benefit of rack-mounted servers is that you can mix and match hardware from different vendors in the same cage. Rack-mounted servers can usually take more memory and processors than their blade counterparts. Recent benchmark results available from the TPC website refer to high-end x86-64 servers with 5 and up to 7 unit height, taking a massive two to four terabyte of memory. The next generation of Xeon processors to be released in 2013/2014 will push that limit even further.

Blades seem well suited for clustered applications, especially if individual blades can boot from the SAN and generally have little static configuration on internal storage or the blade itself. Some systems allow the administrator to define a personality of the blade, such as network cards and their associated hardware addresses, the LUN(s) where the operating system is stored on, and other metadata defining the role of the blade. Should a particular blade in a chassis fail, the blade’s metadata can be transferred to another one, which can be powered up and resume the failed blade’s role. Total outage time, therefore, can be reduced, and a technician has a little more time to replace the failed unit.

Rack-mounted servers are very useful when it comes to consolidating older, more power-hungry hardware on the same platform. They also generally allow for better extension in form of available PCIe slots compared to a blade. To harness the full power of a 5U or even 7U server requires advanced features from the operating systems, such as support for the Non-Uniform Memory Architecture (NUMA) in modern hardware. You can read more about making best use of your new hardware in Chapter 4.

Regardless of which solution you decide to invest in for your future consolidation platform, you should consider answering the following questions about your data center:

• How much does the data center management charge you for space?
• How well can the existing air conditioning system cope with the heat?
• Is the raised floor strong enough to withstand the weight of another fully populated rack?
• Is there enough power to deal with peaks in demand?
• Can your new hardware be efficiently cooled within the rack?
• Is your supporting infrastructure, especially networking and fiber channel switches, capable of connecting the new systems the best possible way? You definitely do not want to end up in a situation where you bought 10Gbps Ethernet adapters, for example, and your switches cannot support more than 1 GBps.
• Does your network infrastructure allow for a sufficiently large pool of IP addresses to connect the system to its users?

There are many more questions to be asked, and the physical deployment of hardware is an art on its own. All vendors provide planning and deployment guides, and surely you can get vendor technicians and consultants to advise you on the future deployment of their hardware. You might even get the luxury of a site survey wherein a vendor technician inspects corridors, elevators, raised flooring, and power, among other things, to ensure that the new hardware fits physically when it is shipped.

Let’s not forget at this stage that the overall goal of the consolidation efforts is to reduce cost. If the evaluation of hardware is successful, it should be possible to benefit from economies of scale by limiting yourself to one hardware platform, possibly in a few different configurations to cater to the different demands of applications. The more standardized the environment, the easier it is to deliver new applications with a quick turnaround.

With the basic building block in sight, the next question is: what should be added as peripheral hardware? Which options do you have in terms of CPU, memory, and expansion cards? What storage option should you use, etc.? The following sections introduce some changes in the hardware world which have happened over the last few years.

Changes in the Hardware World

The world of hardware is changing at a very rapid pace. The core areas where most technological advance is visible are processor architecture, the explosion of available memory, storage, and networking infrastructure. Combined these changes present unique challenges not only to the operating system but also to the software running on it. Until not too long ago it was unthinkable to have 160 CPU threads exposed to the operating system in the x86–64 world, and scheduler algorithms had to be developed to deal with this large number. In addition to the explosion of the number of CPU cores and threads you also have the possibility of vast amounts of memory at your disposal. Commercial x86-64 servers can now address up to four terabytes. Non-uniform memory access (NUMA) has also become more important on x86-64 platforms. Although the NUMA factor is most relevant in systems with four NUMA nodes and more, understanding NUMA in Linux will soon become crucial when working with large servers.

Exadata was the first platform widely available running Oracle workloads and moving Remote Direct Memory Access into the focus. Most of us associate Infiniband with RDMA, but there are other noteworthy applications, such as iWARP and RoCEE (RDBMA over Converged Enhanced Ethernet), for example. Also, Infiniband is a lot more than you might think: it is a whole series of protocols for various different use cases, ranging from carrier for classic IP (“IP over IB”) to the low-latency Exadata protocol based on RDS or Reliable Datagram Sockets to transporting SCSI (“SRP,” the SCSI RDMA Protocol).

Changes to storage are so fundamental that they deserve a whole section of their own. Classic fiber channel arrays are getting more and more competition in form of PCI Express Flash Cards, as well as small-form factor flash-arrays connected via Infiniband, allowing for ultra-low latency. New vendors are challenging the established storage companies with new and interesting concepts that do not fit the model in which many vendors placed their products over the last decade. This trend can only be beneficial for the whole industry. Even if the new storage startups are quickly swallowed by the established players in the storage market their dynamics, products and ideas will live on. No one can ignore the changes flash memory brought to the way enterprises store data anymore.

Thoughts About the Storage Backend

Oak Table member James Morle has written a great paper titled “Sane SAN 2010.” He has also predicated changes to the way enterprise storage arrays are going to be built. With his statements he alluded to the flash revolution in the data center. Over the last decade the use of NAND flash has become prolific, and that for a reason. Flash storage can offer a lot more I/O operations per second in a single unit, while at the same time requiring a lot less space and cooling. Given the right transport protocol, it can also boost performance massively. The pace of development of flash memory outpaced the development for magnetic disk in the relatively short time it has existed. Advances in capacity for magnetic disks have been made consistently in the past, but the performance data of the disks have not increased at the same speed. Before starting a discussion about storage tiering and the flash revolution, a little bit of terminology must be explained.

What are typical ball-park figures to expect from your storage array? In terms of latency you should expect 6–8 milliseconds for single block random reads and 1 or 2 milliseconds for small sequential writes such as log writes. These figures are most likely not reads from physical disk, but rather are those reads affected by the caches in the arrays. With the wide variety of existing storage solutions and the progress made on an almost monthly basis, it is next to impossible to give a valid figure for expected throughput, which is why you do not find one here.

Non-Uniform Memory Architecture with Intel X86–64

The Non-Uniform Memory architecture has been mentioned in a number of places in this book, and this section is finally providing more information about it. You read earlier that x86–64-based servers have the capability to address huge amounts of memory compared to systems a few years ago. So what does that mean? In the days of the classic Symmetrical Multiprocessor (SMP) systems, memory access had to be shared by all processors on a common bus. The memory controller was located on the Front Side Bus, or FSB. Main memory was attached to a chip called the Northbridge on the mainboard. Needless to say, this approach did not scale well with increasing numbers of CPUs and their cores. Additionally, high speed I/O PCI cards could also be attached to the Northbridge, further adding to the scalability problem.

The memory-access-problem was addressed by moving the memory closer to the processor. New, modern CPUs have memory controllers on the chip, allowing them to address their local memory at very high speeds. Furthermore, there is no shared bus between CPUs; all CPU-to-CPU connections in the x86–64-architecture are point-to-point. In some configurations, however ,an additional hop is required to access remote memory. In such systems, which are not in scope of this section, CPU 1, for example, can only directly talk to CPU 2. If it needs to communicate with CPU 3, then it has to ask CPU 2 first, adding more latency. A typical dual-socket system is shown in Figure 3-2:

Current x86–64 processors have multiple DDR3-memory channels—individual memory channels, however, are not shown in this figure. The operating system has the task of masking the location of memory from its processes. The goal is to allow applications not having to be NUMA-aware to execute on NUMA hardware. However, they should be to take advantage of the local memory! Remember for the rest of the section that local memory access is faster, hence preferred. If local memory cannot be used, then remote memory needs to be used instead. This can happen if a process migrates (or has to migrate) from one CPU to another, in which case local and remote memory are reversed. The previously mentioned point-to-point protocols allow the CPU to access remote memory. This is where the most common NUMA-related problem lies. With every Oracle database it is important to have predictable performance. There are a number of reports from users who have experimented with NUMA: some processes executed a lot faster than without NUMA, but some others did not. The reason was eventually determined to be caused by access to remote memory in a memory-sensitive application. This had the following consequences:

• Oracle’s “oracle-validated” and “oracle-rdbms-server-12cR1-preinstall” RPMs disable NUMA entirely by adding numa=off to the boot loader command line.
• Multiple notes on My Oracle Support do not openly discourage the use of NUMA but make it very clear that enabling NUMA can cause performance changes. A change can be positive or negative; you are also told to test the impact of enabling NUMA first, but that almost goes without saying.

So how can you make use of NUMA? First you have to enable it—check if your boot loader doesn’t have the numa=off line appended as shown here:

kernel /boot/vmlinuz-2.6.xxx ro root=... rhgb quiet numa=off

It might additionally be necessary to enable NUMA in the BIOS or EFI of your machine. After a reboot your server is potentially NUMA aware. Linux uses the numactl package, among other software, to control NUMA. To list your NUMA nodes in the server, you use the following command:

[oracle@server1 ∼]$ numactl --hardware
available: 4 nodes (0-3)
node 0 cpus: 0 1 2 3 4 5
node 0 size: 8190 MB
node 0 free: 1622 MB
node 1 cpus: 6 7 8 9 10 11
node 1 size: 8192 MB
node 1 free: 41 MB
node 2 cpus: 12 13 14 15 16 17
node 2 size: 8192 MB
node 2 free: 1534 MB
node 3 cpus: 18 19 20 21 22 23
node 3 size: 8176 MB
node 3 free: 1652 MB
node distances:
node   0   1   2   3
  0:  10  16  16  16
  1:  16  10  16  16
  2:  16  16  10  16
  3:  16  16  16  10
[oracle@server1 ∼]$

In this example, you see a small system with four memory sockets. For each NUMA node, the number of its CPUs is reported, which are in fact cores. In addition to the cores per NUMA node, you get the size and utilization of the node’s memory. The previously shown server has 32GB available in total, with about 8 GB available per NUMA node. The node distance map is based on the ACPI SLIT—the Advanced Configuration and Power Interface’s System Locality Information Table. Don’t worry about the names and technology. What matters is that the numbers in the table show the relative latency to access memory from a particular node, normalized to a base value of 10. Additional information, similar to the /proc/meminfo output per NUMA node, can be found in /sys/devices/system/node/*/meminfo and related files in the same directory. If you have allocated large pages, then you will find more information about these in /sys/devices/system/node/*/hugepages/.

The numactl utility is not only used to expose the hardware configuration, but it can also actively influence how processes and their memory access are handled by applying what is called the NUMA policy. By default, the NUMA policy matches the one shown here:

[oracle@server1 ∼]$ numactl --show
policy: default
preferred node: current
physcpubind: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23
cpubind: 0 1 2 3
nodebind: 0 1 2 3
membind: 0 1 2 3

The default NUMA policy sets the current node as the preferred one and does not enforce binding processes to memory nodes or CPUs. If, for example, you want to override the default policy, you have plenty of options:

[root@server1 ∼]# numactl
usage: numactl [--interleave=nodes] [--preferred=node]
               [--physcpubind=cpus] [--cpunodebind=nodes]
               [--membind=nodes] [--localalloc] command args ...
       numactl [--show]
       numactl [--hardware]
       numactl [--length length] [--offset offset] [--shmmode shmmode]
               [--strict]
               [--shmid id] --shm shmkeyfile | --file tmpfsfile
               [--huge] [--touch]
               memory policy | --dump | --dump-nodes
 
memory policy is --interleave, --preferred, --membind, --localalloc
nodes is a comma delimited list of node numbers or A-B ranges or all.
cpus is a comma delimited list of cpu numbers or A-B ranges or all
all ranges can be inverted with !
all numbers and ranges can be made cpuset-relative with +
the old --cpubind argument is deprecated.
use --cpunodebind or --physcpubind instead
length can have g (GB), m (MB) or k (KB) suffixes

The meaning of all these are well described in the manual page for numactl. However, there is a better way to control memory and process allocation described in the next section. Although the Oracle database has supported NUMA for quite a while, you should be very careful to set the necessary NUMA initialization parameters in an instance. I would like to encourage you to resort to Control Groups instead, unless you are on Oracle Linux 5 without the Unbreakable Enterprise Kernel. But even then it is important, just as the My Oracle Support notes say, to thoroughly test the implication of enabling NUMA support.

Control Groups

Control Groups, or cgroups for short, are an interesting new feature that made it into the latest enterprise Linux kernels. Control groups are available with Oracle’s Kernel UEK in Oracle Linux 5 or with Oracle Linux 6, where it does not matter which kernel you use. Control Groups are a great way to divide a powerful multi-core, NUMA-enabled server into smaller, more manageable logical entities. Some of the concepts you will read about in this section sound similar to Solaris projects, which is part of the Solaris resource management framework.

The current Linux implementation is not quite as advanced as its Solaris counterpart yet. But who knows, maybe the Linux community will get a very similar feature: development on LXC or Linux Containers has already started to be more serious.

You read in the previous section that modern computer systems have local and remote memory in a non-uniform memory architecture. Depending on your system’s micro architecture, accessing remote memory can be more costly, or a lot more costly than accessing local memory. It is a good idea to access local memory if possible, as you have seen in the previous section. Very large multi-processor systems with more than four sockets can easily be divided vertically into logical units matching a socket. In such a situation it is crucial to have enough local memory available! Assuming that your system has four NUMA nodes and 64 GB of memory, you would expect to see each NUMA node to have approximately 16 GB RAM.

Control Groups are based on grouping processes into subsystems, such as memory and CPU. All children of processes belonging to a control group will also automatically be associated with that cgroup. Cgroups are not limited to managing CPU and memory: further subsystems have been added to the framework. You can query your system to learn more about which subsystem is available and in use:

[root@server1 cgroup]# lssubsys -am
cpuset /cgroup/cpuset
cpu /cgroup/cpu
cpuacct /cgroup/cpuacct
memory /cgroup/memory
devices /cgroup/devices
freezer /cgroup/freezer
net_cls /cgroup/net_cls
blkio /cgroup/blkio

The preceding output shows the default configuration from a recent Oracle Linux 6 system with package libcgroup installed. If enabled, the cgconfig service, which is part of the package, will read the configuration file in /etc/cgconfig.conf and mount the subsystems accordingly. Interestingly, the control group hierarchy is not exposed in the output of the mount command but rather in /proc/mounts.

From a consolidation point of view, it makes sense to limit processes to CPU socket(s). The aim of this section is to create two databases and bind all their respective processes to an individual socket and its memory. Control Groups are externalized in userland similar to the sys file system sysfs in Linux by a set of virtual files. Figure 3-3 demonstrates the relationship between the different subsystems and Control Groups.

Thankfully for the administrator, the first step of defining mount points for each subsystem is already done for us with the installation of the libcgroup package. You read earlier that the cgroup mount point resembles the SYSFS mount, following the UNIX paradigm that everything is a file. The contents of the top level cpuset directory are shown here:

[root@server1 ∼]# ls /cgroup/cpuset/
cgroup.clone_children  cpuset.memory_migrate           cpuset.sched_relax_domain_level
cgroup.event_control   cpuset.memory_pressure          notify_on_release
cgroup.procs           cpuset.memory_pressure_enabled  release_agent
cpuset.cpu_exclusive   cpuset.memory_spread_page       tasks
cpuset.cpus            cpuset.memory_spread_slab
cpuset.mem_exclusive   cpuset.mems
cpuset.mem_hardwall    cpuset.sched_load_balance
[root@server1 ∼]#

This is the top-level hierarchy, or the hierarchy’s root. Some of the properties in this directory are read-write, but most are read-only. If you make changes to a property here, the change will be inherited by its siblings. The siblings are the actual control groups which need to be created next:

[root@server1 cgroup]# cgcreate -t oracle:dba -a oracle:dba -g cpuset:/db1
[root@server1 cgroup]# cgcreate -t oracle:dba -a oracle:dba -g cpuset:/db2

It is indeed that simple! The cgcreate command will create control groups db1 and db2 for the cpuset controller, as shown by the -g flag. The -a and -t flags allow the oracle account to administer and add tasks to the cpuset. Behind the scenes you will see that it creates two subdirectories in the cpuset directory, each with its own set of virtual configuration files. These control groups will inherit information from their parent, with the exception of the cpuset.memory_pressure_enabled and release_agent fields. From an Oracle point of view, all that remains to be done is to define which CPUs, and memory should be assigned to the cgroup. You use the cgset command to do so. There is a confusingly large number of attributes that can be set, but luckily the documentation is quite comprehensive. Following the example from the previous section, the first six “CPUs,” which are cores in reality, are mapped to db1; the other six will be set to db2. The same happens for memory:

[root@server1 cpuset]# cgset -r cpuset.cpus=0-5 db1
[root@server1 cpuset]# cgset -r cpuset.mems=0 db1
[root@server1 cpuset]# cgset -r cpuset.cpus=6-11 db2
[root@server1 cpuset]# cgset -r cpuset.mems=2 db2

Note that the memory is allocated by NUMA node; the CPU cores are enumerated the same way the operating system sees them. Further enhancements could include a decision on whether the CPUs should exclusively be useable by the processes in a cgroup. In that case, if cpuset.cpu_exclusive is set to 1 and no other processes can utilize the CPU—use this feature with care! The same applies for cpuset.mem_exclusive, but related to memory.

The manual way to start a process in a certain cgroup is called cgexec. Consider this example for starting an Oracle database:

[oracle@server1 ∼]$ cgexec -g cpuset:db1 /u01/app/oracle/product/12.1.0.1/dbhome_1/bin/sqlplus
 
SQL*Plus: Release 12.1.0.1.0 Production on Fri Jun 28 13:49:51 2013
 
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
 
Enter user-name: / as sysdba
Connected to an idle instance.
 
SQL> startup
ORACLE instance started.
...
Database opened.
SQL> exit

An alternative approach would be to add the PID of your current shell to the tasks pseudo-file in the control group directory. This way all the shell’s child processes automatically belong to the control group:

[oracle@server1 ∼]# echo $$ > /cgroups/oracle_cpuset/db1/tasks

Now start the Oracle database as normal. Since the oracle account has been specified as an administrator of the cgroup during its creation, this operation is valid.

In addition to the manual way of adding processes to cgroups, you can make use of the new initialization parameter processor_group available on Linux and Solaris. So instead of using cgexec, you simply set the initialization parameter to point to the existing cgroup:

SQL> show parameter processor_group_name
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
processor_group_name                 string      db1

There are a few ways to validate the cgroup mapping. The most common are probably these:

• The taskset command
• Checking the /proc/pid/cgroup pseudo file
• Using custom options to the ps command

Using the taskset approach, you need the process ID of your database first:

[oracle@server1 ∼]# ps –ef | grep CDB1
oracle    2149     1  0 14:24 ?        00:00:00 ora_pmon_CDB1
oracle    2151     1  0 14:24 ?        00:00:00 ora_psp0_CDB1
oracle    2153     1  1 14:24 ?        00:00:06 ora_vktm_CDB1
 
[root@server1 ∼]# taskset -cp 2149
pid 2149's current affinity list: 0-5

This confirms that the cgroup settings are indeed in place. If you want to know the name associated with the control group as well, check the /proc entry for the process:

[oracle@server1 ∼]# cat /proc/2149/cgroup | grep cpuset
1:cpuset:/db1

And finally, you can view the same information with the ps command as well. Feel free to remove fields you do not need:

[oracle@server1 ∼]# ps -ax --format uname,pid,cmd,cls,pri,rtprio,cgroup
...
oracle    2149 ora_pmon_CDB1                TS  19      - ... cpuset:/db1
oracle    2151 ora_psp0_CDB1                TS  19      - ... cpuset:/db1
oracle    2153 ora_vktm_CDB1                RR  41      1 ... cpuset:/db1
...

With that knowledge it should be relatively simple to track down shared pool and other memory related problems with the database.

Up to this point all the configuration about control groups are transient. In other words, if your server reboots, the control group configuration will all be gone. This will be a serious headache because it might prevent databases from starting if the processorgroup_name parameter is set, but the corresponding control group is not defined at the operating system level. Thankfully you can dump your configuration into a file to be read by the cgconfig service. Use the cgsnapshot utility to write the configuration to standard output and cut/paste the relevant bits into the main configuration file /etc/cgconfig.conf. This time the configuration will be read and applied during the system boot.

In addition to the processor_group_name parameter, a little bit of additional setup work needs to be performed. First of all, it is recommended to set another initialization parameter named use_dedicated_broker to true and also enable the new connection broker in the listener configuration file by setting DEDICATED_THROUGH_BROKER_listener_name to on, followed by a reload of the listener. Note that if you are using the new multi-threaded architecture, the use_dedicated_broker parameter is already set to true. You can read more about the multi-threaded architecture in Chapter 2.

FIO

FIO is an intelligent I/O testing platform written by Jens Axboe, whom we also have to thank for the Linux I/O schedulers and much more. Out of all the I/O benchmarks, I like FIO because it is very flexible and offers a wealth of output for the performance analyst. As with most of I/O related benchmarks, FIO works best in conjunction with other tools to get a more complete picture.

What is great about the tool is the flexibility, but it requires a little more time to learn all the ins and outs of it. However, if you take the time to learn FIO, you will automatically learn more about Linux as well. FIO benchmark runs are controlled by plain text files with instructions, called a job file. A sample file is shown here; it will be used later in the chapter:

[oracle@server1 fio-2.1]# cat rand-read-sync.fio
[random-read-sync]
rw=randread
size=1024m
bs=8k
directory=/u01/fio/data
ioengine=sync
iodepth=8
direct=1
invalidate=1
ioscheduler=noop

In plain English, the job named “random-read-sync” will use the random-read workload, creates a file of 1 GB in size in the directory /u01/fio/data/, and uses a block size of 8 kb, which matches Oracle’s standard database block size. A maximum of 8 outstanding I/O requests are allowed, and the Linux I/O scheduler to be used is the noop scheduler since the storage in this case is non-rotational. The use of direct I/O is also requested, and the page cache is invalidated first to avoid file system buffer hits for consistency with the other scripts in the test harness—direct I/O will bypass with buffer cache anyway, so strictly speaking, the directive is redundant.

Linux can use different ways to submit I/O to the storage subsystem—synchronous and asynchronous. Synchronous I/O is also referred to as blocking I/O, since the caller has to wait for the request to finish. Oracle will always use synchronous I/O for single block reads such as index lookups. This is true, for example, even when asynchronous I/O is enabled. The overhead of setting up an asynchronous request is probably not worth it for a single block read.

There are situations wherein synchronous processing of I/O requests does not scale well enough. System designers therefore introduced asynchronous I/O. The name says it all: when you are asynchronously issuing I/O requests, the requestor can continue with other tasks and will “reap” the outstanding I/O request later. This approach greatly enhances concurrency but, at the same time, makes tracing a little more difficult. As you will also see, the latency of individual I/O requests increases with a larger queue depth. Asynchronous I/O is available on Oracle Linux with the libaio package.

Another I/O variant is called direct I/O, which instructs a process to bypass the file system cache. The classic UNIX file system buffer cache is known as the page cache in Linux. This area of memory is the reason why users do not see free memory in Linux. Those portions of memory that are not needed by applications will be used for the page cache instead.

Unless instructed otherwise, the kernel will cache information read from disk in said page cache. This cache is not dedicated to a single process allowing other processes to benefit from it as well. The page cache is a great concept for the many Linux applications but not necessarily beneficial for the Oracle database. Why not? Remember from the preceding introduction that the result of regular file I/O is stored in the page cache in addition to being copied to the user process’s buffers. This is wasteful in the context of Oracle since Oracle already employs its own buffer cache for data read from disk. The good intention of a file-system page cache is not needed for the Oracle database since it already has its own cache to counter the relatively slow disk I/O operations.

There are cases in which enabling direct I/O causes performance problems, but those can easily be trapped in test and development environments. However, using direct I/O allows the performance analyst to get more accurate information from performance pages in Oracle. If you see a 1 ms response time from the storage array, you cannot be sure if that is a great response time from the array or rather a cached block from the operating system. If possible, you should consider enabling direct I/O after ensuring that it does not cause performance problems.

To demonstrate the difference between synchronous and asynchronous I/O, consider the following job file for asynchronous I/O.

[oracle@server1 fio-2.1]$ cat rand-read-async.fio
[random-read-async]
rw=randread
size=1024m
bs=8k
directory=/u01/fio/data
ioengine=libaio
iodepth=8
direct=1
invalidate=1
ioscheduler=noop

Let’s compare the two—some information has been removed in an effort not to clutter the output. First, the synchronous benchmark:

[oracle@server1 fio-2.1]$ ./fio rand-read-sync.fio
random-read-sync: (g=0): rw=randread, bs=8K-8K/8K-8K/8K-8K, ioengine=sync, iodepth=8
fio-2.1
Starting 1 process
Jobs: 1 (f=1): [r] [100.0% done] [39240KB/0KB/0KB /s] [4905/0/0 iops] [eta 00m:00s]
random-read-sync: (groupid=0, jobs=1): err= 0: pid=4206: Mon May 27 23:03:22 2013
  read : io=1024.0MB, bw=39012KB/s, iops=4876, runt= 26878msec
    clat (usec): min=71, max=218779, avg=203.77, stdev=605.20
     lat (usec): min=71, max=218780, avg=203.85, stdev=605.20
    clat percentiles (usec):
     |  1.00th=[  189],  5.00th=[  191], 10.00th=[  191], 20.00th=[  193],
     | 30.00th=[  195], 40.00th=[  197], 50.00th=[  205], 60.00th=[  207],
     | 70.00th=[  209], 80.00th=[  211], 90.00th=[  213], 95.00th=[  215],
     | 99.00th=[  225], 99.50th=[  233], 99.90th=[  294], 99.95th=[  318],
     | 99.99th=[  382]
    bw (KB  /s): min=20944, max=39520, per=100.00%, avg=39034.87, stdev=2538.87
    lat (usec) : 100=0.08%, 250=99.63%, 500=0.29%, 750=0.01%, 1000=0.01%
    lat (msec) : 4=0.01%, 20=0.01%, 250=0.01%
  cpu          : usr=0.71%, sys=6.17%, ctx=131107, majf=0, minf=27
  IO depths    : 1=100.0%, 2=0.0%, 4=0.0%, 8=0.0%, 16=0.0%, 32=0.0%, >=64=0.0%
     submit    : 0=0.0%, 4=100.0%, 8=0.0%, 16=0.0%, 32=0.0%, 64=0.0%, >=64=0.0%
     complete  : 0=0.0%, 4=100.0%, 8=0.0%, 16=0.0%, 32=0.0%, 64=0.0%, >=64=0.0%
     issued    : total=r=131072/w=0/d=0, short=r=0/w=0/d=0

Run status group 0 (all jobs):
   READ: io=1024.0MB, aggrb=39012KB/s, minb=39012KB/s, maxb=39012KB/s,
         mint=26878msec, maxt=26878msec

Disk stats (read/write):
  sda: ios=130050/20, merge=0/4, ticks=25188/12, in_queue=25175, util=94.12%

The important information here is that the test itself took 26,878 milliseconds to complete, and the storage device completed an average of 4,876 I/O operations per second for an average bandwidth of 39,012KB/s. Latencies are broken down into scheduling latency (not applicable for synchronous I/O— you will see it in the below output) and completion latency. The last number, recorded as “lat” in the output above, is the complete latency and should be the sum of scheduling plus completion latency.

Other important information is that out CPU has not been terribly busy when it executed the benchmark, but this is an average over all cores. The IO depths row shows the IO depth over the duration of the benchmark execution. As you can see, the use of synchronous I/O mandates an I/O depth of 1. The “issued” row lists how many reads and writes have been issued. Finally, the result is repeated again at the bottom of the output. Let’s compare this with the asynchronous test:

[oracle@server1 fio-2.1]$ ./fio rand-read-async.fio
random-read-async: (g=0): rw=randread, bs=8K-8K/8K-8K/8K-8K, ioengine=libaio, iodepth=8
fio-2.1
Starting 1 process
random-read-async: Laying out IO file(s) (1 file(s) / 1024MB)
Jobs: 1 (f=1): [r] [100.0% done] [151.5MB/0KB/0KB /s] [19.4K/0/0 iops] [eta 00m:00s]
random-read-async: (groupid=0, jobs=1): err= 0: pid=4211: Mon May 27 23:04:28 2013
  read : io=1024.0MB, bw=149030KB/s, iops=18628, runt=  7036msec
    slat (usec): min=5, max=222754, avg= 7.89, stdev=616.46
    clat (usec): min=198, max=14192, avg=408.59, stdev=114.53
     lat (usec): min=228, max=223110, avg=416.61, stdev=626.83
    clat percentiles (usec):
     |  1.00th=[  278],  5.00th=[  306], 10.00th=[  322], 20.00th=[  362],
     | 30.00th=[  398], 40.00th=[  410], 50.00th=[  414], 60.00th=[  422],
     | 70.00th=[  430], 80.00th=[  442], 90.00th=[  470], 95.00th=[  490],
     | 99.00th=[  548], 99.50th=[  580], 99.90th=[  692], 99.95th=[  788],
     | 99.99th=[ 1064]
    bw (KB  /s): min=81328, max=155328, per=100.00%, avg=149248.00, stdev=19559.97
    lat (usec) : 250=0.01%, 500=96.31%, 750=3.61%, 1000=0.06%
    lat (msec) : 2=0.01%, 20=0.01%
  cpu          : usr=3.80%, sys=21.68%, ctx=129656, majf=0, minf=40
  IO depths    : 1=0.1%, 2=0.1%, 4=0.1%, 8=100.0%, 16=0.0%, 32=0.0%, >=64=0.0%
     submit    : 0=0.0%, 4=100.0%, 8=0.0%, 16=0.0%, 32=0.0%, 64=0.0%, >=64=0.0%
     complete  : 0=0.0%, 4=100.0%, 8=0.1%, 16=0.0%, 32=0.0%, 64=0.0%, >=64=0.0%
     issued    : total=r=131072/w=0/d=0, short=r=0/w=0/d=0

Run status group 0 (all jobs):
   READ: io=1024.0MB, aggrb=149030KB/s, minb=149030KB/s, maxb=149030KB/s,
         mint=7036msec, maxt=7036msec

Disk stats (read/write):
  sda: ios=126931/0, merge=0/0, ticks=50572/0, in_queue=50547, util=95.22%

Apart from the fact that the test completed a lot faster—7,036msec vs. 26,878msec—you can see that bandwidth is a lot higher. The libaio benchmark provides a lot more IOPS as well: 18,628 vs. 4,876. A doubling of the IO depth yields better bandwidth with the asynchronous case at the expense of the latency of individual requests. You do not need to spend too much time trying to find the maximum I/O depth on your platform, since Oracle will transparently make use of the I/O subsystem, and you should probably not change any potential underscore parameter.

You should also bear in mind that maximizing I/O operations per second is not the only target variable when optimizing storage. A queue depth of 32, for example, will provide a large number of IOPS, but this number means little without the corresponding response time. Compare the output below, which has been generated with the same job file as before but a queue depth of 32 instead of 8:

[oracle@server1 fio-2.1]$ ./fio rand-read-async-32.fio
random-read-async: (g=0): rw=randread, bs=8K-8K/8K-8K/8K-8K, ioengine=libaio, iodepth=32
[...]
  read : io=1024.0MB, bw=196768KB/s, iops=24595, runt=  5329msec
[...]
    lat (usec) : 500=0.01%, 750=0.01%, 1000=0.01%
    lat (msec) : 2=99.84%, 4=0.13%

The number of IOPS has increased from 18,628 to 24,595, and the execution time is down to 5,329 milliseconds instead of 7,036, but this has come at a cost. Instead of microseconds, 99.84% of the I/O requests now complete in milliseconds.

When you are working with storage and Linux, FIO is a great tool to help you understand the storage solution as well as the hardware connected against it. FIO has a lot more to offer with regards to workloads. You can read and write sequentially or randomly, and you can mix these as well—even within a single benchmark run! The permutations of I/O type, block sizes, direct, and buffered I/O, combined with the options to run multiple jobs and use differently sized memory pages, etc., make FIO one of the best benchmark tools available. There is a great README as part of FIO, and its author has a HOWTO document, as well, for those who want to know all the detail: http://www.bluestop.org/fio/HOWTO.txt.

Oracle I/O numbers

The ORION tool has been available for quite some time, initially as a separate download from Oracle’s website and now as part of the database installation. This, in a way, is a shame since a lot of its attractiveness initially came from the fact that a host did not require a database installation. If you can live with a different, lower, version number, then you can still download ORION for almost all platforms from Oracle’s OTN website. At the time of this writing, the below URL allowed you to download the binaries: http://www.oracle.com/technetwork/topics/index-089595.html. The above link also allows you to download the ORION user’s guide, which is more comprehensive than this section. For 11.2 onwards you find the ORION documentation as part of the official documentation set in the “Performance Tuning Guide”. Instead of the 11.1 binaries downloadable as a standalone package, this book will use the ones provided with the Oracle server installation.

ORION works best during the evaluation phase of a new platform, without user-data on your logical unit numbers (LUNs). (As with all I/O benchmarking tools, write testing is destructive!) The package will use asynchronous I/O and large pages, where possible, for reads and writes to concurrently submit I/O requests to the operating system. Asynchronous I/O in this context means the use of libaio on Linux or equivalent library as discussed in the FIO benchmark section above. You can quite easily see that the ORION slave processes use io_submit and io_getevents by executing strace on them:

[root@server1 ∼]# strace -cp 28168
Process 28168 attached - interrupt to quit
^CProcess 28168 detached
% time     seconds  usecs/call     calls    errors syscall
------ ----------- ----------- --------- --------- ----------------
 97.79    0.323254           4     86925           io_submit
  2.11    0.006968           1     12586           io_getevents
  0.10    0.000345           0     25172           times
------ ----------- ----------- --------- --------- ----------------
100.00    0.330567                124683           total
[root@server1 ∼]#

The process with ID 28168, to which the strace session attached, was a running ORION session.

So what does ORION allow you to do? It’s easiest to start off with the way it generates I/O workloads. Internally ORION uses a two-dimensional matrix depicting small I/O and large I/O requests. Columns in the matrix represent small I/O requests; rows are large I/O requests. This matrix approach is illustrated in Table 3-3:

Small I/O requests are user-configurable but default to 8k, which equals the default block size of modern Oracle databases and most of the other databases. Large I/O requests can also be defined as an input parameter to the benchmark run; they default to 1MB, which is in line with the default maximum size of a single I/O on most platforms. Benchmarks generate load by increasing the concurrency of outstanding I/O requests, either with or without additional large I/O requests. For each element in the matrix, ORION records information and presents it later in text files. To use ORION, you need to pass an argument to the mandatory parameter “run.” It can take the following values:

• Simple: A simple run which tests random small 8k in isolation, i.e., uses row 0 in the above matrix: multiple concurrent small I/Os but no large ones. It then runs tests using multiple concurrent large I/Os, i.e., stays in column 0. It does not combine small and large I/Os.
• Normal: traverses the whole matrix and tests various combinations of small and large I/Os. This run can take a while to complete.
• DSS: Uniquely tests random large I/Os at increasing concurrency, stays in column 0 in the above matrix.
• OLTP: tests only small I/Os, stays in row 0 in the above matrix.
• Advanced: allows you define your own workload by picking rows, columns, or individual data points in the matrix. Advanced testing is out of scope of this chapter.

There are some concerns whether or not ORION really represents a true database workload. One argument against ORION is that it does not have to deal with a buffer cache and its maintenance and some other work an active Oracle instance has to perform. Despite the few downsides to ORION, you should assume that ORION is a good tool, albeit not 100 percent accurate. It certainly is good enough to get a better understanding of your storage subsystem when used as a baseline.

Before you can start a benchmark run with ORION, you need to define a set of targets you want to test against. These are usually LUNs provisioned from the storage array or a directly attached block device, maybe even via PCIe. The LUNs need to be listed in an input file and terminated with a new line. If you were testing a system with 5 LUNs for a future ASM disk group “DATA,” the file could have the following contents:

/dev/mapper/asm_data_disk001p1
/dev/mapper/asm_data_disk001p2
/dev/mapper/asm_data_disk001p3
/dev/mapper/asm_data_disk001p4
/dev/mapper/asm_data_disk001p5

Note that the devices have been partitioned in this setup. This was done to align the partition with the storage array block boundaries, based on a vendor recommendation. Partitioning a LUN also indicates that it is in use and thus prevents it from being accidentally deleted.

The LUNs need to be saved in a file called orion.lun. You can optionally pass a parameter “testname” to ORION, in which case the LUNs need to be saved as testname.lun. ORION will make use of large pages by default. If your system does not have large pages configured, ORION will error unless you specify the -hugenotneeded flag. To perform a simple, read-only OLTP-like test named “test1,” you need a file test1.lun containing the LUNs to test against. Make sure that the oracle account has access rights to these LUNs. You invoke ORION using its complete path. After the calibration run you can check the output of the “summary” file to get an idea about your storage subsystem:

ORION VERSION 12.1.0.1.0
 
Command line:
-run oltp -testname test1
 
These options enable these settings:
Test: test1
Small IO size: 8 KB
Large IO size: 1024 KB
IO types: small random IOs, large random IOs
Sequential stream pattern: RAID-0 striping for all streams
Writes: 0%
Cache size: not specified
Duration for each data point: 60 seconds
Small Columns:,      1,      2,      3,      4,      5,      6,      7,      8,      9,
10,     11,     12,     13,     14,     15,     16,     17,     18,     19,     20
Large Columns:,      0
Total Data Points: 21
 
Name: /dev/sdc  Size: 1073741824
1 files found.
 
Maximum Small IOPS=9899 @ Small=17 and Large=0
Small Read Latency: avg=1711 us, min=791 us, max=12316 us, std dev=454 us @ Small=17 and Large=0
 
Minimum Small Latency=123.58 usecs @ Small=1 and Large=0
Small Read Latency: avg=124 us, min=41 us, max=4496 us, std dev=76 us @ Small=1 and Large=0
Small Read / Write Latency Histogram @ Small=17 and Large=0
        Latency:                # of IOs (read)          # of IOs (write)
        0 - 1           us:             0                       0
        2 - 4           us:             0                       0
        4 - 8           us:             0                       0
        8 - 16          us:             0                       0
       16 - 32          us:             0                       0
       32 - 64          us:             10                      0
       64 - 128         us:             364355                  0
      128 - 256         us:             90531                   0
      256 - 512         us:             4281                    0
      512 - 1024        us:             2444                    0
     1024 - 2048        us:             244                     0
     2048 - 4096        us:             38                      0
     4096 - 8192        us:             8                       0
     8192 - 16384       us:             0                       0
    16384 - 32768       us:             0                       0
    32768 - 65536       us:             0                       0
    65536 - 131072      us:             0                       0
   131072 - 262144      us:             0                       0
   262144 - 524288      us:             0                       0
   524288 - 1048576     us:             0                       0
  1048576 - 2097152     us:             0                       0
  2097152 - 4194304     us:             0                       0
  4194304 - 8388608     us:             0                       0
  8388608 - 16777216    us:             0                       0
 16777216 - 33554432    us:             0                       0
 33554432 - 67108864    us:             0                       0
 67108864 - 134217728   us:             0                       0
134217728 - 268435456   us:             0                       0a

This output has been produced from a testbed environment; the numbers are not to be used for comparison with a real storage backend.

Large I/Os are random by default but can be defined as sequential if desired. To add write testing to the benchmark, you need to tell ORION to do so. The -write flag indicates how many percent of I/Os should be writes, and according to the documentation the default is 0.

At the end of the benchmark, you are presented with a lot of information. All the result files are prefixed with the test name you chose with an added time stamp:

[oracle@server1 temp]# ls -l
total 104
-rw-r--r--. 1 oracle oracle 19261 Jul 22 10:13 test1_20130722_0953_hist.txt
-rw-r--r--. 1 oracle oracle   742 Jul 22 10:13 test1_20130722_0953_iops.csv
-rw-r--r--. 1 oracle oracle   761 Jul 22 10:13 test1_20130722_0953_lat.csv
-rw-r--r--. 1 oracle oracle   570 Jul 22 10:13 test1_20130722_0953_mbps.csv
-rw-r--r--. 1 oracle oracle  1859 Jul 22 10:13 test1_20130722_0953_summary.txt
-rw-r--r--. 1 oracle oracle 18394 Jul 22 10:13 test1_20130722_0953_trace.txt
-rw-r--r--. 1 oracle oracle    20 Jul 22 09:46 test1.lun
[oracle@server1 temp]#

The summary file is the first one to look at. It shows the command line parameters, I/O sizes, the I/O type among other information. From a storage-backend perspective, the matrix data points are most interesting:

Duration for each data point: 60 seconds
Small Columns:,      1,      2,      ...     20
Large Columns:,      0
Total Data Points: 21

Here you see that the OLTP test does not use large I/Os at all. The name and size of the LUNs used are also recorded just prior to the performance figures:

Maximum Small IOPS=9899 @ Small=17 and Large=0
Small Read Latency: avg=1711 us, min=791 us, max=12316 us, std dev=454 us @ Small=17 and Large=0
 
Minimum Small Latency=123.58 usecs @ Small=1 and Large=0
Small Read Latency: avg=124 us, min=41 us, max=4496 us, std dev=76 us @ Small=1 and Large=0
Small Read / Write Latency Histogram @ Small=17 and Large=0

Following that information, you are shown the same latency histogram you were presented at the end of the ORION run. In the above example, that’s the histogram for the data point of 17 small I/Os and 0 large I/Os. All other histograms can be found in the test1_20130722_0953_hist.txt file. The other files contain the information listed in Table 3-4.

The use of ORION should have given you a better understanding of the capabilities of your storage subsystem. Bear in mind that the figures do not represent a true Oracle workload due to the lack of the synchronization in the Oracle-shared memory structures. Furthermore, ORION does not use the pread/pwrite calls Oracle employs for single block I/O. However, the initial test of your storage subsystem should be a good enough approximation.

There is a lot more to ORION which could not be covered here, especially when it comes to testing I/O performance of multiple LUNs. It is possible to simulate striping and mirroring, and even to simulate log write behavior, by instructing the software to stream data sequentially.

Silly little Oracle Benchmark

Don’t be fooled by the name—SLOB is one of the most interesting and hotly discussed Oracle-related storage benchmarks you can get hold of. It has been released by Kevin Closson and has extensively been discussed on social media and weblogs. SLOB is designed to test the Oracle storage backend and is available in source code. It relies on a C-program’s control, the execution of the actual benchmark work defined in shell scripts and SQL. Since its initial release SLOB has been enhanced, and a new version of the benchmark has been released as SLOB 2. The following section is about SLOB2 unless stated otherwise.

Before you can actually use SLOB, you need to perform some setup work. The first step is to create a database, and then you load the test data. SLOB is made available here:

http://kevinclosson.wordpress.com/2013/05/02/slob-2-a-significant-update-links-are-here/

At the time of this writing, the May 5 release was current. Download and uncompress the file to a convenient location on the server you want to benchmark. Let’s assume you unzipped it to /home/oracle/SLOB.

SLOB:/u01/app/oracle/product/12.1.0.1/dbhome_1:N

db_create_file_dest = '/u01/oradata/'
db_name = SLOB
compatible=12.1.0.1.0
UNDO_MANAGEMENT=AUTO
db_block_size = 8192
db_files = 300
processes = 1000
sga_target=10G
filesystemio_options=setall

[oracle@server1 SLOB]$ cd wait_kit/
[oracle@server1 wait_kit]$ make
rm -fr *.o mywait trigger create_sem
cc     -c -o mywait.o mywait.c
cc -o mywait mywait.o
cc     -c -o trigger.o trigger.c
cc -o trigger trigger.o
cc     -c -o create_sem.o create_sem.c
cc -o create_sem create_sem.o
cp mywait trigger create_sem ../
rm -fr *.o
[oracle@server1 wait_kit]$

SQL> select sum(bytes/power(1024,2)) mb,segment_type
  2    from dba_segments
  3   where owner = 'USER1'
  4   group by segment_type
  5  /
 
        MB SEGMENT_TYPE
---------- ------------------
     .1875 INDEX
        80 TABLE

[oracle@server1 SLOB]$ ./setup.sh
FATAL: ./setup.sh args
Usage : ./setup.sh: <tablespace name> <optional: number of users>
[oracle@server1 SLOB]$ ./setup.sh IOPS 128
 
NOTIFY: Load Parameters (slob.conf):
 
LOAD_PARALLEL_DEGREE == 12
SCALE == 10000
ADMIN_SQLNET_SERVICE == ""
CONNECT_STRING == "/ as sysdba"
NON_ADMIN_CONNECT_STRING ==
 
NOTIFY: Testing connectivity to the instance to validate slob.conf settings.
NOTIFY: ./setup.sh: Successful test connection: "sqlplus -L / as sysdba"
 
NOTIFY: Creating and loading seed table.
 
Table created.
 
PL/SQL procedure successfully completed.
 
NOTIFY: Seed table loading procedure has exited.
NOTIFY: Setting up user   1  2  3  4  5  6  7  8  9  10  11  12
[...]
NOTIFY: Setting up user   121  122  123  124  125  126  127  128
 
Table dropped.
 
NOTIFY: ./setup.sh: Loading procedure complete (158 seconds). Please check
./cr_tab_and_load.out for any errors
 
[oracle@server1 SLOB]$

[oracle@server1 SLOB]$ cat slob.conf
 
UPDATE_PCT=0
RUN_TIME=60
WORK_LOOP=0
SCALE=10000
WORK_UNIT=256
REDO_STRESS=HEAVY
LOAD_PARALLEL_DEGREE=12
SHARED_DATA_MODULUS=0
 
# Settings for SQL*Net connectivity:
#ADMIN_SQLNET_SERVICE=slob
#SQLNET_SERVICE_BASE=slob
#SQLNET_SERVICE_MAX=2
#SYSDBA_PASSWD="change_on_install"
 
export UPDATE_PCT RUN_TIME WORK_LOOP SCALE WORK_UNIT LOAD_PARALLEL_DEGREE REDO_STRESS SHARED_DATA_MODULUS
 
[oracle@server1 SLOB]$

#!/bin/bash
#
# Martin Bach 2013
#
# runit_wrapper.sh is a wrapper to preserve the output of the supporting files from
# individual SLOB version 2 test runs
 
# list of files to preserve. Not all are necesarrily generated during a test run.
FILES="awr.txt awr.*html  awr*.gz iostat.out vmstat.out mpstat.out db_stats.out "
FILES="$FILES sqlplus.out slob.conf"
 
# setting up ...
[[ ! -f $ORACLE_HOME/bin/sqlplus || -z $ORACLE_SID ]] && {
        echo ERR: Your Oracle environment is not set correctly it appears.
        echo ERR: source oraenv into your session and try again
        exit
}
 
# Two command line parameters are needed
(( $# != 2 )) && {
        echo ERR: wrong number of command line parameters
        echo usage: $0 testname num_sessions
        exit
}
 
TESTNAME=$1
NUM_SESSIONS=$2
 
# The script creates a directory to hold the test results. That will fail
# if the directory already exists
[[ -d $TESTNAME ]] && {
        echo ERR: A test with name $TESTNAME has been found already.
        echo ERR: Please double check your parameters
        exit
}
 
echo INFO: preparing to preserve output for test $TESTNAME
mkdir $TESTNAME || {
        echo ERR: cannot create a directory to store the output for $TESTNAME
        exit
}
 
# set the automatic gathering of snapshots to a very high value.
# Note that turning it off (interval => 0) means you cannot even
# take manual snapshots.
echo INFO: increasing AWR snapshot interval
 
$ORACLE_HOME/bin/sqlplus / as sysdba > /dev/null <<EOF
spool $TESTNAME/awr_config.txt
select systimestamp as now from dual;
select * from DBA_HIST_WR_CONTROL;
exec DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(interval=>360)
exit
EOF
 
# finally run the benchmark. Keep the output too
./runit.sh $NUM_SESSIONS 2>&1 | tee ${TESTNAME}/runit.sh.log
 
# preserve the generated files in $TESTNAME
for FILE in $FILES; do
        [[ -f $FILE ]] && {
                echo INFO: moving file $FILE to directory $TESTNAME
                cp $FILE $TESTNAME 2> /dev/null
        }
done
 
# create a copy of awr.txt for the use with awr_info.sh
cp ${TESTNAME}/awr.txt ${TESTNAME}/awr.txt.${NUM_SESSIONS}
 
# finally preserve all initialisation parameters
$ORACLE_HOME/bin/sqlplus / as sysdba 2> /dev/null <<EOF
create pfile='$(pwd)/${TESTNAME}/init.ora' from memory;
exit
EOF
 
echo INFO: done

db_create_file_dest = '/u01/oradata/'
db_name = SLOB
compatible=12.1.0.1.0
UNDO_MANAGEMENT=AUTO
db_block_size = 8192
db_files = 300
processes = 500
control_files = /u01/oradata/SLOB/controlfile/o1_mf_812m3gj5_.ctl
 
shared_pool_size = 600M
large_pool_size = 16M
java_pool_size = 0
streams_pool_size = 0
db_cache_size=48M
cpu_count = 2
 
filesystemio_options=setall
parallel_max_servers=0
recyclebin=off
pga_aggregate_target=1G
workarea_size_policy=auto
...

SQL> select component,current_size/power(1024,2) mb
  2   from v$sga_dynamic_Components
  3  where current_size <> 0
  4  /
 
COMPONENT                                                                MB
---------------------------------------------------------------- ----------
shared pool                                                             600
large pool                                                               16
DEFAULT buffer cache                                                     48

IO Profile                  Read+Write/Second     Read/Second    Write/Second
∼∼∼∼∼∼∼∼∼∼                  ----------------- --------------- ---------------
            Total Requests:          22,138.9        22,132.9             6.0
         Database Requests:          22,132.8        22,128.0             4.8
        Optimized Requests:               0.0             0.0             0.0
             Redo Requests:               0.9             0.0             0.9
                Total (MB):             173.0           173.0             0.1
             Database (MB):             172.9           172.9             0.0
      Optimized Total (MB):               0.0             0.0             0.0
                 Redo (MB):               0.0             0.0             0.0
         Database (blocks):          22,133.8        22,128.3             5.5
 Via Buffer Cache (blocks):          22,133.8        22,128.3             5.5
           Direct (blocks):               0.0             0.0             0.0

#<----CPU[HYPER]-----><---------------Disks---------------->
#cpu sys inter  ctxsw KBRead  Reads Size KBWrit Writes Size
   2   1 42236  47425 182168  22771    8      0      0    0
   2   1 43790  47359 182272  22776    8     16      1   16
   2   1 42955  46644 179040  22380    8     24      3    8

SQL> select * from table(dbms_xplan.display_cursor('309mwpa4tk161'))
 
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
SQL_ID 309mwpa4tk161, child number 0
-------------------------------------
SELECT COUNT(C2) FROM CF1 WHERE CUSTID > ( :B1 - :B2 ) AND (CUSTID <
:B1 )
 
Plan hash value: 1497866750
 
------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |       |       |       |   259 (100)|          |
|   1 |  SORT AGGREGATE                        |       |     1 |   133 |            |          |
|*  2 |   FILTER                               |       |       |       |            |          |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED | CF1   |   256 | 34048 |     259 (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN                   | I_CF1 |   256 |       |       2 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter(:B1>:B1-:B2)
   4 - access("CUSTID">:B1-:B2 AND "CUSTID"<:B1)
 
23 rows selected.