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).
- 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.
- 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.