SLOB Full table scans

I had to create a procedure to get full table scans on SLOB data. SLOB is designed to do random I/O (and lots of it 😉 but my test required FTS as well.

Here is the link to the SLOB full table scan program: slob-fulltablescan.sql

The program is nothing special, but you might find it useful to see what the data scan rate is in MB/s (instead of IOPS) without having to fall back to another testing tool.

Basically the program works as follows: for a random set of users (default all or you can set a number of users to scan for), do a select count(*) from CF1 (the SLOB table) while keeping track of per-user and total start/stop time and rows scanned. As each row in SLOB is 8K you can calculate how many megabytes were scanned (total # of rows) and as a result you can figure out the scan rate (per user and for the entire process).

Caveats:

• Single threaded – this procedure gives you the scan rate for a single thread, not the maximum for the system. I plan to add that in a future version (set parallel scan jobs). For now feel free to kick off a bunch of these at the same time to see if you can beef up total bandwidth.
• Read-only
• 8K blocksize used for the calculation (using other blocksizes will give incorrect bandwidth values)
• You might want to add awr_snap pre- and post actions so you can also collect AWR statistics for the duration of the run
• Need to enter # of users manually – should also be possible to parameterize (next version 😉

Maybe Kevin will put this small program (or an improved version) in the next SLOB package… 🙂

Update  2014-12-16 Removed scanning of USER0 as in SLOB 2.2 USER0 owns no data and the script failed.