Skip to content

Instantly share code, notes, and snippets.

@gregrahn
Created December 20, 2011 18:15
Show Gist options
  • Save gregrahn/1502596 to your computer and use it in GitHub Desktop.
Save gregrahn/1502596 to your computer and use it in GitHub Desktop.
Example on how to read ASH data from Oracle into R and do scatter plots
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()
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment