Example on how to read ASH data from Oracle into R and do scatter plots

ASH_plots.R

library(RJDBC)
#
# set up the JDBC connection 
# configure this for your env
#
drv <-JDBC("oracle.jdbc.driver.OracleDriver","/Users/grahn/code/jdbc/ojdbc6.jar")
conn<-dbConnect(drv,"jdbc:oracle:thin:@zulu.us.oracle.com:1521:orcl","grahn","grahn")

#
# import the data into a data.frame
# I use an ORDER BY to make it easy to eyeball data in the data.frame
#
lfs <-dbGetQuery(conn, "select SAMPLE_ID, TIME_WAITED from ashdump where EVENT='log file sync'           order by SAMPLE_ID")
lfpw<-dbGetQuery(conn, "select SAMPLE_ID, TIME_WAITED from ashdump where EVENT='log file parallel write' order by SAMPLE_ID")
dfsr<-dbGetQuery(conn, "select SAMPLE_ID, TIME_WAITED from ashdump where EVENT='db file sequential read' order by SAMPLE_ID")

#
# look at log file sync (lfs) and log file parallelwrite (lfpw) first
#
plot(lfs,    type="p", col="red",   pch=3, ann=TRUE, ylim=c(0,30000), xaxp=c(0,300,15))
points(lfpw, type="p", col="green", pch=15)
legend(200,31000, c("log file parallel write","log file sync"), pch=c(15,3), col=c("green","red"))
#
# save the plot to a file
#
dev.copy(png,'Rplot01.png',height=600,width=800,pointsize=16)
dev.off()

#
# reduce the y axis max to 3ms to zoom in on lfs and lfpw
#
plot  (lfs,  type="p", col="red",   pch=03, ann=TRUE, ylim=c(0,3000), xaxp=c(0,300,15))
points(lfpw, type="p", col="green", pch=15)
legend(200,3100, c("log file parallel write","log file sync"), pch=c(15,3), col=c("green","red"))
#
# save the plot to a file
#
dev.copy(png,'Rplot02.png',height=600,width=800,pointsize=16)
dev.off()

#
# zoom x axis to 1 minute of samples (each sample_id is from a given second, so a range of 60 sample_ids)
#
lfs2 <-subset(lfs,  SAMPLE_ID >= 130 & SAMPLE_ID <= 190)
lfpw2<-subset(lfpw, SAMPLE_ID >= 130 & SAMPLE_ID <= 190)
dfsr2<-subset(dfsr, SAMPLE_ID >= 130 & SAMPLE_ID <= 190)

#
# start with an empty graph and overlay each event
#
plot(NA, xlab="SAMPLE_ID", ylab="TIME_WAITED", main="", ylim=c(0,45000), xlim=c(130,190), xaxp=c(130,190,12))
legend(171,45000, c("log file parallel write","log file sync","db file sequential read"), pch=c(15,03,21), col=c("green","red","blue"))
dev.copy(png,'Rplot10.png',height=600,width=800,pointsize=16)
dev.off()
#
# add db file sequential read events and save the plot
#
points(dfsr2, type="p", col="blue", pch=21)
legend(171,45000, c("log file parallel write","log file sync","db file sequential read"), pch=c(15,03,21), col=c("green","red","blue"))
dev.copy(png,'Rplot11.png',height=600,width=800,pointsize=16)
dev.off()
#
# add log file sync events and save the plot
#
points(lfs2, type="p", col="red", pch=03)
legend(171,45000, c("log file parallel write","log file sync","db file sequential read"), pch=c(15,03,21), col=c("green","red","blue"))
dev.copy(png,'Rplot12.png',height=600,width=800,pointsize=16)
dev.off()
#
# add log file parallel write events and save the plot
#
points(lfpw2, type="p", col="green", pch=15)
legend(171,45000, c("log file parallel write","log file sync","db file sequential read"), pch=c(15,03,21), col=c("green","red","blue"))
dev.copy(png,'Rplot13.png',height=600,width=800,pointsize=16)
dev.off()