Well, I think I'm a little late to the party.. I know Greg Rahn did a great post on utilizing R to visual your ash data. I figure I would do a simple example of how to build something myself to show how easy it is to utilyze R to visualize query execution times..
Well first I stated by downloading R from cran.r-project.org.
Once I downloaded R, I went to one of my databases, and found a query that had different execution times I wanted to play with. I created an output file from the query.. Here is the script I used..
Well first I stated by downloading R from cran.r-project.org.
Once I downloaded R, I went to one of my databases, and found a query that had different execution times I wanted to play with. I created an output file from the query.. Here is the script I used..
set pagesize 10000
set feedback off
spool rtest.txt
select trunc((elapsed_time_delta/executions_delta)/1000000,4) avg_execution_time "AVG_EXECUTION_TIME",
PLAN_HASH_VALUE "PLAN_HASH_VALUE",
execution_date "EXECUTION_DATE"
from
(
select sum(elapsed_time_delta) elapsed_time_delta,
sum(executions_delta) executions_delta,
PLAN_HASH_VALUE,
to_char(trunc(end_interval_time),'mm/dd/yy') execution_date
from dba_hist_sqlstat a,
dba_hist_snapshot b
where sql_id='19sqmxkc58wqm'
and a.snap_id=b.snap_id
and a.instance_number=b.instance_number
--and executions_delta>0
group by plan_hash_value,to_char(trunc(end_interval_time),'mm/dd/yy')
)
where executions_delta > 0
order by execution_date;
spool off
This script created a file I brought over to my pc and cleaned up the format. Here is part of the file..
AVG_EXECUTION_TIME PLAN_HASH_VALUE execution_date
20.4368 566875892 01/01/12
50.3253 4009342004 01/01/12
21.4655 566875892 01/02/12
19.8312 4009342004 01/02/12
69.9299 4009342004 01/03/12
135.7153 4009342004 01/04/12
39.3972 4009342004 01/05/12
65.2833 4009342004 01/06/12
39.8093 4009342004 01/07/12
35.8615 4009342004 01/08/12
18.7553 566875892 01/09/12
134.7431 4009342004 01/09/12
76.2954 4009342004 01/10/12
115.8707 4009342004 01/11/12
60.0754 4009342004 01/12/12
102.6432 4009342004 01/13/12
22.2528 566875892 01/14/12
119.8541 4009342004 01/14/12
21.8552 566875892 01/15/12
18.5785 4009342004 01/15/12
19.3179 566875892 01/16/12
80.794 4009342004 01/16/12
67.0872 4009342004 01/17/12
107.1604 4009342004 01/18/12
28.9797 4009342004 01/19/12
I put this file into c:\r and named it query_performance .txt.
I then went into R and ran the following commands.
setwd("c:\\r")
query_data <- read.table("query_performance.txt",header=T)
max_num <- max(query_data$AVG_EXECUTION_TIME)
hist(query_data$AVG_EXECUTION_TIME,col=heat.colors(max_num),breaks=max_num,xlim=c(0,max_num),
right=F,main="Execution Time Histogram",las=1)
You can see I just ran a few simple commands...
setwd --- set the working directory to c:\r
read.table --- read in my space delimitted table (there is a read.csv for a comma separated file)
max_num --- is set to the maximum execution time in the file
hist -- creates a histogram of the execution times.. Check out below what comes out. Sweet !!
This was easy, and gives me a great picture of the variance in execution times.
I am going to work more with this file since it had 2 different plans I want to visual the differences.