ORA-00001: unique constraint (ORA.BLOG_TAGLINE_PK) violated

UPDATE 20140227: I am leaving this post here for historical reference, but the version of SLOB I used while writing on it is now fully deprecated. Please go directly to the distribution page for SLOB2 and use the information there to retrieve the latest version of SLOB and learn how to use it, as the usage and functionality have changed. For additional tips, consider Yury’s “SLOB2 Kick start“. Please do not attempt to use the scripts, instructions or techniques I have described here unless you are still using the old version of SLOB. If you are still using the old version, you should not be.

This post covers usage of my SLOB.R script, used to analyze SLOB results from repeated test runs. The contents of the SLOB.R script are at the bottom of this post, but first I will show the contents of a sample SLOB.R session, followed by how you can produce similar results.

A Sample SLOB.R Session

The first step is to start R. On Windows you’ll double-click an icon, on Linux you’ll just type R and hit enter. Once R has started, you will be in the interactive R interface and it will display some introductory text standard in R.

R version 2.15.2 (2012-10-26) -- "Trick or Treat"
Copyright (C) 2012 The R Foundation for Statistical Computing
ISBN 3-900051-07-0
Platform: i386-w64-mingw32/i386 (32-bit)
R is free software and comes with ABSOLUTELY NO WARRANTY.
You are welcome to redistribute it under certain conditions.
Type 'license()' or 'licence()' for distribution details.
Natural language support but running in an English locale
R is a collaborative project with many contributors.
Type 'contributors()' for more information and
'citation()' on how to cite R or R packages in publications.
Type 'demo()' for some demos, 'help()' for on-line help, or
'help.start()' for an HTML browser interface to help.
Type 'q()' to quit R.
>

Once you are in R, you need to load the SLOB.R script and then load your SLOB data. SLOB data is produced by running SLOB repeatedly, saving the awr.txt output in between each run, then running the SLOB/misc/awr_info.sh script to summarize the collected AWR reports. I have saved the SLOB.R script in my Cygwin home directory, and saved the awr_info.sh output in a file called “awrinfo” in the same directory.

Now you have the contents of your awr_info.sh output in an R variable called SLOB. You can call this variable anything you wish, but I used SLOB.

To use SLOB.R you need to tell it a little bit about your AWR data: specifically which variables you are trying to test and how many sessions you used. In this example I am comparing performance of the XFS and EXT3 filesystems using the SLOB physical read, redo, and physical write models. The variables to be tested (EXT3 and XFS) are embedded in the filenames I used when saving the awr.txt report between SLOB runs, and the session counts are the numbers I used when running SLOB’s runit.sh script to put load on the database. We tell SLOB.R about this by setting a few R variables containing this data. You can call them anything you wish, you just need to know their names.

As you can see, variable assignment in R uses the <- operator. I’m using the R built-in c() function (concatenate) to create vector variables that contain multiple values (you can think of them as like an array for now). The SLOB.sessions variable contains three integer values: 8, 16, and 32; the other three variables each contain two string values: ‘XFS’ and ‘EXT3’. For this demo I am only including two variables but it works fine with many more than that. I have been using about 7 of them. I am comparing filesystems, but you might be comparing storage vendor 1 vs storage vendor 2, or fibre channel vs dNFS, or Oracle 11.2.0.1 vs Oracle 11.2.0.3. As long as the variables are identifiable from your AWR filenames, they will work.

You can view the contents of any R variable just by typing its name.

> SLOB.sessions
[1] 8 16 32
> SLOB.read.vars
[1] "XFS" "EXT3"

With these variables set up you can now use the main SLOB.R driver functions to summarize the contents of your SLOB AWR reports. First I’ll call the SLOBreads() function to summarize physical read performance. This function summarizes the PREADS column from awr_info.sh output, by variable and session count. To produce a better average it discards the lowest value and highest value from each combination of variable and session count. Other SLOB.R driver functions are SLOBredo() and SLOBreadwrite().

So this indicates that for 8 reader sessions, the XFS filesystem gave me an average of 27,223.89 physical reads per second, while EXT3 gave me 30,076.77 physical reads per second. The columns for 16 and 32 sessions have the same meaning as the 8 session column. The ‘Overall’ column is an average of ALL the data points, regardless of session count.

You don’t have to use variables when calling the SLOB.R driver functions. You can specify the variables or session counts directly in the call. This is part of how R works. Another example, showing how you can receive the same output by calling it with a variable or not:

The numbers above would indicate that when storing my redo logs on XFS, SLOB could push 326,480,426 bytes of redo per second with 8 sessions. On EXT3 with 8 sessions I saw 304,188,977 bytes of redo per second. The 16, 32 and Overall columns have meanings similar to what I showed before.

The SLOBreadwrite() function reports the sum of physical reads and physical writes, with the columns all meaning the same as they do for the other functions.

How To Create Output For SLOB.R

SLOB.R is smart enough to figure out which of your runs are testing reads, which are testing redo, and which are testing readwrite performance. But for this to work you have to follow the naming convention defined in the SLOB/misc/README file when renaming your awr.txt files for processing by awr_info.sh: [whatever].[number of SLOB writers].[number of SLOB readers] — SLOB.R expects your variables to be uniquely identifiable strings in the ‘whatever‘ part.

I recommend using scripts to run SLOB repeatedly and save the awr.txt output in between. I provided some scripts in a prior post, but you can use your own scripts as long as your filenames match the required format.

Once you have your AWR output collected, run SLOB/misc/awr_info.sh on all the files, and save its output. This is the file you will load into R.