public
Last active

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

  • Download Gist
ASH_plots.R
R
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75
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()

Please sign in to comment on this gist.

Something went wrong with that request. Please try again.