###############################
# file   : EventHist.r - Create proper histograms based on v$event_histogram from
#          a variety of common sources.
# author : Craig Shallahamer, craig@orapub.com
# orig   : 30-April-2015
# updt   : 25-May-2015
# usage  : Do the 3 steps outlined below. Do NOT just run this script.
###############################

# 1. Get the event histogram data
# 
#   A. OSM. swhist.sql  - enter either "Occurs" or "Occurs %"
#   B. OSM. swhistx.sql - enter either "Delta Occurs" or "Occurs %"
#   C. OSM. swEhAwrGet.sql - pulls data from AWR table, dba_hist_event_histogram
#   D. OSM. swEhVdGet.sql  - pulls data from v$event_histogram w/OUT DML/DDL
#   E. The below SQL    - enter the WAIT_COUNT values
#      select WAIT_TIME_MILLI, WAIT_COUNT
#      from   v$EVENT_HISTOGRAM 
#      where event like '&waitevent%' 
#      order by WAIT_TIME_MILLI; 

# 2. Enter your real data below: occurances or a percentage
#    Below, I have provided two sets of data as examples.
#    R will use the final data set, as it will simply overwrite
#    the first example below.

# Below is an output from on the above SQL. The values are the wait_count

eName = "db%file%seq%read"

count0to1          =320155223
count1to2          =391409083
count2to4          =358230130
count4to8          =83033302
count8to16         =17661179
count16to32        =2987466
count32to64        =632746
count64to128       =274189
count128to256      =67712
count256to512      =1881
count512to1024     =143
count1024to2048    =91
count2048to4096    =167
count4096to8192    =77
count8192to16384   =1
count16384to32768  =0
count32768to65536  =0
count65536to131072 =0
count131072to262144=0


# Below is output using percentages from an AWR html report
# Notice the values are percent values from the report, not event counts

eName = "log file sync"

count0to1          =16.7
count1to2          =11.2
count2to4          =4.1
count4to8          =4.2
count8to16         =33.7
count16to32        =23.5
count32to64        =5.7
count64to128       =0.7
count128to256      =0.3
count256to512      =0
count512to1024     =0
count1024to2048    =0
count2048to4096    =0
count4096to8192    =0
count8192to16384   =0
count16384to32768  =0
count32768to65536  =0
count65536to131072 =0
count131072to262144=0

# Below is output from swEhAwrGet.sql (OSM toolkit)

eName = "db%seq%read"

count0to1 = 2137447
count1to2 = 818604
count2to4 = 3201548
count4to8 = 703008
count8to16 = 61701
count16to32 = 6064
count32to64 = 1286
count64to128 = 170
count128to256 = 39
count256to512 = 3
count512to1024 = 2
count1024to2048 = 0
count2048to4096 = 0
count4096to8192 = 0
count8192to16384 = 0
count16384to32768 = 0
count32768to65536 = 0
count65536to131072 = 0
count131072to262144 = 0


# Below is output from a test system using the swEhVdGet.sql OSM script.

eName = "db%file%seq%read"

### There were 52984 sampling loops completed and 90002 samples collected.
###
count0to1 = 90013
count1to2 = 9
count2to4 = 1
count4to8 = 0
count8to16 = 14
count16to32 = 0
count32to64 = 0
count64to128 = 0
count128to256 = 0
count256to512 = 0
count512to1024 = 0
count1024to2048 = 0
count2048to4096 = 0
count4096to8192 = 0
count8192to16384 = 0
count16384to32768 = 0
count32768to65536 = 0
count65536to131072 = 0
count131072to262144 = 0


# Below is output from a production system using the swEhVdGet.sql OSM script.

eName = "log file sync"

count0to1 = 0 
count1to2 = 171 
count2to4 = 539 
count4to8 = 250 
count8to16 = 46 
count16to32 = 13 
count32to64 = 1 
count64to128 = 0 
count128to256 = 0 
count256to512 = 0 
count512to1024 = 0 
count1024to2048 = 0 
count2048to4096 = 0 
count4096to8192 = 0 
count8192to16384 = 0 
count16384to32768 = 0 
count32768to65536 = 0 
count65536to131072 = 0 
count131072to262144 = 0 

# Below is output from a production system using the swEhAwrGet.sql OSM script.

eName = "enq%TX%row%lock%"

count0to1 = 4888
count1to2 = 2354
count2to4 = 2052
count4to8 = 764
count8to16 = 347
count16to32 = 149
count32to64 = 5
count64to128 = 2
count128to256 = 3
count256to512 = 3
count512to1024 = 2
count1024to2048 = 1
count2048to4096 = 0
count4096to8192 = 0
count8192to16384 = 0
count16384to32768 = 3
count32768to65536 = 1
count65536to131072 = 2
count131072to262144 = 0

### START ### 

eName = "db%seq%read" 

count0to1 = 5704606 
count1to2 = 553873 
count2to4 = 172443 
count4to8 = 282957 
count8to16 = 333684 
count16to32 = 50484 
count32to64 = 5296 
count64to128 = 2818 
count128to256 = 354 
count256to512 = 25 
count512to1024 = 2 
count1024to2048 = 0 
count2048to4096 = 0 
count4096to8192 = 0 
count8192to16384 = 0 
count16384to32768 = 0 
count32768to65536 = 0 
count65536to131072 = 0 
count131072to262144 = 0 

eName = "db%seq%read" 

count0to1 = 6722450 
count1to2 = 1003400 
count2to4 = 269448 
count4to8 = 628325 
count8to16 = 786237 
count16to32 = 81117 
count32to64 = 7057 
count64to128 = 1625 
count128to256 = 916 
count256to512 = 1143 
count512to1024 = 2417 
count1024to2048 = 1516 
count2048to4096 = 29 
count4096to8192 = 53 
count8192to16384 = 0 
count16384to32768 = 0 
count32768to65536 = 0 
count65536to131072 = 0 
count131072to262144 = 0 

eName = "db%file%seq%read"
### There were 527 sampling loops completed and 50050 samples collected.
###
count0to1 = 12192
count1to2 = 21029
count2to4 = 14107
count4to8 = 2196
count8to16 = 572
count16to32 = 115
count32to64 = 29
count64to128 = 4
count128to256 = 0
count256to512 = 0
count512to1024 = 0
count1024to2048 = 0
count2048to4096 = 0
count4096to8192 = 0
count8192to16384 = 0
count16384to32768 = 0
count32768to65536 = 0
count65536to131072 = 0
count131072to262144 = 0


eName = "db%file%seq%read"
### There were 950 sampling loops completed and 50017 samples collected.
###
count0to1 = 12798
count1to2 = 20813
count2to4 = 13721
count4to8 = 2028
count8to16 = 611
count16to32 = 132
count32to64 = 26
count64to128 = 4
count128to256 = 0
count256to512 = 0
count512to1024 = 0
count1024to2048 = 0
count2048to4096 = 0
count4096to8192 = 0
count8192to16384 = 0
count16384to32768 = 0
count32768to65536 = 0
count65536to131072 = 0
count131072to262144 = 0

## from AWR
eName = "library%cache%lock"

count0to1 = 3241563
count1to2 = 709608
count2to4 = 113300
count4to8 = 39576
count8to16 = 14954
count16to32 = 6037
count32to64 = 2963
count64to128 = 1004
count128to256 = 0
count256to512 = 0
count512to1024 = 0
count1024to2048 = 0
count2048to4096 = 0
count4096to8192 = 0
count8192to16384 = 0
count16384to32768 = 0
count32768to65536 = 0
count65536to131072 = 0
count131072to262144 = 0

eName = "log%file%seq%read"


count0to1 = 17238
count1to2 = 5886
count2to4 = 8028
count4to8 = 20882
count8to16 = 7048
count16to32 = 1282
count32to64 = 231
count64to128 = 100
count128to256 = 85
count256to512 = 49
count512to1024 = 59
count1024to2048 = 11
count2048to4096 = 0
count4096to8192 = 0
count8192to16384 = 0
count16384to32768 = 0
count32768to65536 = 0
count65536to131072 = 0
count131072to262144 = 0


eName = "db%file%seq%read"

count0to1 = 12.2
count1to2 = 4.5
count2to4 = 9.1
count4to8 = 49.5
count8to16 = 18.9
count16to32 = 3.5
count32to64 = 1.9
count64to128 = 0.3
count128to256 = 0.1
count256to512 = 0
count512to1024 = 0
count1024to2048 = 0
count2048to4096 = 0
count4096to8192 = 0
count8192to16384 = 0
count16384to32768 = 0
count32768to65536 = 0
count65536to131072 = 0
count131072to262144 = 0



# 3. Copy and paste this *ENTIRE* file (above and below this) into R

#####
# Do NOT modify the below code unless you know what you're doing.
#####

actuals  <- c(count0to1,count1to2,count2to4,count4to8,count8to16,count16to32,count32to64,count64to128,count128to256,count256to512,count512to1024,count1024to2048,count2048to4096,count4096to8192,count8192to16384,count16384to32768,count32768to65536,count65536to131072,count131072to262144)
totcount <- sum(actuals)

binPct    = NULL
binValues = NULL
displaySamples=500000

binPct    <- c(binPct,actuals[1]/totcount)
binValues <- c(binValues,runif(round(binPct[1]*displaySamples),0,1))

for (i in 1:18) {
  binPct    <- c(binPct,actuals[i+1]/totcount)
  binValues <- c(binValues,runif(round(binPct[i+1]*displaySamples),0.0000001+2^(i-1),2^i))
}

ss50=subset(binValues, binValues<=50)
ss500=subset(binValues, binValues<=500)
ss1500=subset(binValues, binValues<=1500)
ss2500=subset(binValues, binValues<=2500)
ss5000=subset(binValues, binValues<=5000)

summary(binValues)
par(mfrow=c(2,2))
plot(density(binValues),main=paste("Oracle Wait Event Times (ms)\nevent name:", eName), xlim=c(0,1.1*max(binValues)))
hist(binValues,col="gray97",main=paste("Oracle Wait Event Times (ms)\nevent name:", eName))
hist(binValues,col="gray75",breaks=11,main=paste("Oracle Wait Event Times (ms)\nevent name:", eName, "\nmax bins: 11"))
hist(ss50,col="lightgreen",xlim=c(0,max(1.1*ss50)), main=paste("Oracle Wait Event Times (ms)\nevent name:", eName, "\nmax wait time shown: 50ms"))

dev.new()
par(mfrow=c(2,2))
hist(ss500,col="lightblue",xlim=c(0,max(1.1*ss500)), main=paste("Oracle Wait Event Times (ms)\nevent name:", eName, "\nmax wait time shown: 500ms"))
hist(ss1500,col="khaki",xlim=c(0,max(1.1*ss1500)), main=paste("Oracle Wait Event Times (ms)\nevent name:", eName, "\nmax wait time shown: 1.5s"))
hist(ss2500,col="lightyellow",xlim=c(0,max(1.1*ss2500)), main=paste("Oracle Wait Event Times (ms)\nevent name:", eName, "\nmax wait time shown: 2s"))
hist(ss5000,col="lightsalmon",xlim=c(0,max(1.1*ss5000)), main=paste("Oracle Wait Event Times (ms)\nevent name:", eName, "\nmax wait time shown: 5.0s"))