public
Last active

Plot the average KB per read or write by Oracle ASM disk using ggplot2 and RJDBC: http://bommaritollc.com/2013/06/12/plotting-average-read-and-write-operation-size-by-asm-disk

  • Download Gist
plot_asm_average_operation_size.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
# Java and JDBC path configuration
jvmHome <- 'C:\\Program Files (x86)\\Java\\jre7\\'
ojdbcPath <- 'lib/ojdbc.jar'
 
# Load rJava library
Sys.setenv(JAVA_HOME=jvmHome)
options(java.parameters="-Xmx1g")
library(rJava)
 
# Output Java version
.jinit()
print(.jcall("java/lang/System", "S", "getProperty", "java.version"))
 
# Load RJDBC library
library(RJDBC)
 
# Create connection driver and open connection
jdbcDriver <- JDBC(driverClass="oracle.jdbc.OracleDriver", classPath=ojdbcPath)
dbHostname <- 'database.company.com'
dbPort <- 1521
dbService <- 'service.database.company.com'
dbUser <- 'scott'
dbPassword <- 'tiger'
jdbcConnection <- dbConnect(jdbcDriver, sprintf('jdbc:oracle:thin:@//%s:%d/%s', dbHostname, dbPort, dbService), dbUser, dbPassword)
# Query on the Oracle instance name.
asmDiskStats <- dbGetQuery(jdbcConnection, 'SELECT path, label, inst_id, (bytes_read / DECODE(reads, 0, 1, reads)) / 1024. AS kb_per_read,(bytes_written / DECODE(writes, 0, 1, writes)) / 1024. AS kb_per_write FROM gv$asm_disk_stat ORDER BY path, inst_id')
 
# Close connection
dbDisconnect(jdbcConnection)
 
# Load ggplot
library(ggplot2)
 
# Plot
ggplot(asmDiskStats, aes(x=factor(LABEL), y=KB_PER_READ, fill=factor(INST_ID))) +
geom_bar(stat="identity", position="dodge") +
scale_x_discrete("ASM Disk") +
scale_y_continuous("Average Read Operation (KB)") +
scale_fill_discrete("Instance ID") +
ggtitle("Average Read Operation Size by ASM Disk") +
coord_flip()
ggsave(filename="asm_read_operation_size_by_disk.png")
 
ggplot(asmDiskStats, aes(x=factor(LABEL), y=KB_PER_WRITE, fill=factor(INST_ID))) +
geom_bar(stat="identity", position="dodge") +
scale_x_discrete("ASM Disk") +
scale_y_continuous("Average Write Operation (KB)") +
scale_fill_discrete("Instance ID") +
ggtitle("Average Write Operation Size by ASM Disk") +
coord_flip()
ggsave(filename="asm_write_operation_size_by_disk.png")

Please sign in to comment on this gist.

Something went wrong with that request. Please try again.