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.

One Response to SLOB Full table scans

  1. Pingback: Silly Little Oracle Benchmark – RPM edition | Dirty Cache

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: