Jim Hannan (@HoBHannan), Principal Architect
This blog post covers the setup and configuration of Silly Little Oracle Benchmark (SLOB version 1.x) in part 1 and a complementary tool DB Time in part 2. SLOB is a unique tool; it is neither a pure benchmarking tool like Oracle Orion or Linux dd, nor a load-generating tool like Swingbench. It’s different from other low-level benchmarking tools, in that it uses the Oracle kernel to perform its test. It requires a schema and tables; by default this schema is named IOPS (you can change the schema name during setup, I’ll cover this in the setup section below). SLOB can be run in different configurations to benchmark different aspects of Oracle and the underlying supporting hardware. For example, SLOB is able to run the following tests:
Table 1 – SLOB Tests
|Oracle PIO Test||Physical I/O test, block reads off of disk. The primary wait event is db file sequential read.|
|Oracle LIO Read Test||Oracle logical reads, reading data (buffer gets) from the Oracle SGA.|
|Random single block writes||DBWR flushing capacity|
|Redo Generation||How fast Oracle LGWR can write redo logs.|
The PIO test will issue SQL read statements testing the ability of the I/O subsystem to service I/O request. This is accomplished by setting the SGA buffer cache low enough were the cache is aged out so quickly that the data has to be fetched off disk.
The LIO test is a simple test. It will push the workloads CPU and memory. Fetching most of the data blocks in memory. It is important that the Oracle SGA and buffer cache are set high enough to prevent disk reads.
The redo generation test writes sequential redo as fast as the disk can handle it making it a very good at sequential I/O testing.
Before we can run the test we need to do some setup, including creating the tablespace and setting up the kit. To setup SLOB we need to create the IOPS tablespace. To do so, follow these steps:
SLOB Setup Steps
- Download SLOB from: https://my.syncplicity.com/share/udnbexaskg/2013.05.05.slob2
- Untar the file.
- Be sure to set your Oracle environment or SLOB will not be able to find sqlplus, I’ve provided an example below:
[oracle@oel6-64-dnfs SLOB]$ . oraenv ORACLE_SID = [oracle] ? DNFS The Oracle base has been set to /opt/oracle
- Create the trigger tools.
$ cd <into your SLOB directory, probably just SLOB> $ cd wait_kit $ make all
- Create the tablespace IOPS (IOPS is the default name but you can specify any name. By doing so you will need to pass the new name as a parameter in the setup.sh script in step 5.)
SQL> create tablespace IOPS datafile ‘/oradata01/DNFS/iops.dbf’ size 500M autoextend on next 100M maxsize 12G;
- Next, execute the setup.sh script. This creates the tablespace IOPS with 128 users.
|sh ./setup.sh IOPS 128|
Note: 128 will create about 11 GB datafile.
This completes the setup of SLOB, we can now run a sample test and determine the appropriate number of sessions.
After the creation to the tablespace we can run our first test.
|$ ./runit.sh 8 0|
Take note of the 8 in the command above, this determines the number of sessions or the number of writers sessions (DML). The second number 0 in this case, is the number of readers (SELECT statements). This is the PIO test describe in the beginning of this blog, see table 1.
After the test is complete, we can be more scientific about our approach by determining the appropriate amount of sessions for our system. The author Kevin Closson recommends a for loop to determine the right amount of sessions. The reason for this is the system can become so overwhelm that little work is able to get done because of blocks and waits. Run the loop below to start. While the loop is running look for performance to tapper, for example Kevin recommends watching for a dip in PIOPS (Physical IOPS). In our example below we go to a maximum of 8. In our testing for the virtual machine anything beyond 8 caused a drip in PIO. The number of sessions can be set to maximum of 128.
Figure 1 – Kevin Closson’s Recommended For Loop
for cnt in 1 2 4 8 > do > sh ./runit.sh 0 $cnt > done
Note: Each run of slob (runit.sh) creates an AWR report with the name awr.txt. This can be used to determine if performance is tapering under a certain number of sessions. Because the name is not unique it also means it will be overwritten after each run.
I should tell you before going much further that I set my demo system with the following Oracle parameters:
sga_target = 200M sga_max_size = 200M
As you see above I am using ASMM (Automatic Shared Memory Management). You could set everything with manual memory management and then set the db_cache_size (buffer cache) to 8M. By setting buffer cache low enough you force Oracle to read off of disk, which is the desired behavior for testing PIO. Of course this would be suboptimal for any Oracle database under real load.
Note: Remember that by using ASMM and setting the db_cache_size to 8MB you are effectively telling Oracle the minimum size of the db_cache_size. If ASMM has memory available it will be given to the buffer cache.