Parse Time: Hard vs Soft : No-Load Environment

Author: Craig Shallahamer (craig@orapub.com), Version 1a, 13-July-2012

Background and Purpose

The purpose of this notepad is to simply compare the differences in parse times from hard and soft parsing based on the execution number of a unique SQL statement... in a no-load enviroment

Experimental Data and Loading

Below is all the experimental data. The experiment was run on a Dell single six-core CPU, Oracle 11.2G. According to “cat /proc/version”: Linux version 2.6.32-300.3.1.el6uek.x86_64 (mockbuild@ca-build44.us.oracle.com) (gcc version 4.4.4 20100726 (Red Hat 4.4.4-13) (GCC) ) #1 SMP Fri Dec 9 18:57:35 EST 2011. There was a no outside load on the system; essentially a single user Linux system. For each simular yet unique 30 SQL statements the parse time was gatherd (based on a SQL trace file) when it was run seven times. Details are presented in the associated blog posting in early July of 2012.

The order of sample data simply the parse time in either only CPU or the total elapsed time (which includes CPU and Oracle wait time).

ssNum=7;
ssCpu[1]={22996,22997,22996,22996,22997,22996,22996,22997,22996,22996,22996,21997,22997,22997,22997,21996,22997,21997,22996,22997,22996,22997,22997,22997,22997,22996,22996,22997,21996,21997};
ssCpu[2]={0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0};
ssCpu[3]={0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0};
ssCpu[4]={0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0};
ssCpu[5]={0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0};
ssCpu[6]={0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0};
ssCpu[7]={0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0};
ssElp[1]={23374,23539,23438,23391,23474,23376,23495,23445,23401,23437,23462,23610,23469,23473,23516,23384,23572,23479,23434,23619,23478,23497,23506,23515,23527,23530,23614,23509,23532,23625};
ssElp[2]={126,132,128,132,127,131,126,127,126,128,136,126,127,129,133,130,134,129,132,132,137,132,132,129,133,129,128,161,128,128};
ssElp[3]={25,24,24,24,24,25,25,25,25,24,25,24,38,25,39,25,25,41,24,25,25,28,25,25,25,25,39,25,40,25};
ssElp[4]={18,18,18,29,17,28,17,28,18,18,28,28,17,29,17,28,17,22,17,19,22,18,18,17,18,18,18,29,17,18};
ssElp[5]={17,25,29,17,22,17,18,29,17,18,18,17,32,19,17,18,18,29,18,18,18,31,18,19,18,17,29,18,18,29};
ssElp[6]={17,18,18,23,17,17,29,18,17,17,17,17,18,18,17,18,18,18,29,18,17,18,17,29,18,17,18,17,18,18};
ssElp[7]={17,18,18,17,18,19,18,19,17,17,18,17,18,17,17,18,23,17,18,18,18,18,17,18,18,18,17,18,29,18};

Basic Numeric Comparision

No comments.

myData=Table[
{
ssidx,
N[Mean[ssElp[ssidx]/1000]],N[Round[Mean[ssElp[1]]/Mean[ssElp[ssidx]]]],
N[StandardDeviation[ssElp[ssidx]]/1000],DistributionFitTest[ssElp[ssidx]],Length[ssElp[ssidx]]
},{ssidx,1,ssNum}
];
toGrid=Prepend[myData,{
"Execution",
"Elapsed Time\nAvg (ms)","Elapsed Time\nX Times Faster","Elapsed Time\nStdev (ms)","Elapsed Time\nP-Value","Elapsed Time\nSamples"
}];
Grid[toGrid,Frame->All]

Sample Set Normality Tests

Before we can perform a standard t-test hypothesis tests on our data, we need to ensure it is normally distributed...because that is one of the underlying assumptions and requirements for properly performing a t-test.

Statistical  and vlsual normality test

Our alpha will be 0.05, so if the distribution fit test results in a value greater than 0.05 then we can assume the data set is indeed normally distributed.

The first test is just to double check to make sure my thinking is correct. Since I creating a normal distribution based on a mean and standard deviation (just happens to be based on the my sample set data), I would expect a p-value (the result) to greatly exceed 0.05. Notice that the more samples I have created (the final number), the closer the p-value approaches 1.0.

check=DistributionFitTest[RandomVariate[NormalDistribution[Mean[ssCpu[1]],StandardDeviation[ssCpu[1]]],10000]];
Do[
pValueCpu=DistributionFitTest[ssCpu[i]];
pValueElp=DistributionFitTest[ssElp[i]];
Print["Sample set ",i," with ",Length[ssCpu[i]], " values. P-values: Cpu=",pValueCpu," Elp=",pValueElp];
cpu=Histogram[ssCpu[i],PlotLabel->"Occurances vs Cpu",AxesLabel->{"Sample value","Occurs"}];
elp=Histogram[ssElp[i],PlotLabel->"Occurances vs Elp",AxesLabel->{"Sample value","Occurs"}];
Print[cpu];
Print[elp];
Print["-------------------------------------------"];
,{i,1,ssNum}
];
Print["This number should be much greater than 0.05: ",check," If not try again by re-evaluating."];

Sample Comparison Tests (when normality exists)

Assuming our samples are normally distributed, now it’s time to see if they are significantly different. If so, then we know changing the number of latches and chains indeed makes a significant performance difference...at least statistically.

The null hypothesis is; there is no real difference between our samples sets. We need to statistically prove that any difference is the result of randomness; like we just happened to pick poor set of samples and it makes their difference look much worse than it really is.

A t-test will produce a statistic p. The p value is a probability, with a value ranging from zero to one. It is the answer to this question: If the populations really have the same mean overall, what is the probability that random sampling would lead to a difference between sample means larger than observed? 

For example, if the p value is 0.03 we can say a random sampling from identical populations would lead to a difference smaller than you observed in 97% of the experiments and larger than you observed in 3% of the experiments.

Said another way, suppose I have a single sample set and I copy it, resultling in two identical sample sets. Now suppose we perform a significance test on these two identical sample sets. The resuting p-value will be 1.0 because they are exactly the same. We are essentially doing the same thing here except we have to different sample sets... but we still want to see if they “like” each other..and in our case we hope they are NOT the like each other, which means the p-value will low... below our cut off value of 0.05.

For our analysis we choose alpha of 0.05. To accept that our two samples are statistically similar the p value would need to be less than 0.05 (our alpha).

Good reference about the P-Value and significance testing: http://www.graphpad.com/articles/pvalue.htm

Here we go (assuming our samples are normally distributed):

1. Our P value threshold is 0.05, which is our alpha.
2. The null hypothesis is the two populations have the same mean. (Remember we have to sample sets, which not the population.)
3. Do the statistical test to compute the P value.
4. Compare the result P value to our threshold alpha value. If the P value is less then our threshold, we will reject the null hypothesis and say the difference between our samples is significant. However, if the P value is greater than the threshold, we cannot reject the null hypothesis and any difference between our samples are not statistically significant.

Do[
pValueCpu=TTest[{ssCpu[i],ssCpu[i+1]}];
Print["Cpu: (",Length[ssCpu[i]]," values) pvalue between sample set ",i," and ",i+1," is ",pValueCpu];
pValueElp=TTest[{ssElp[i],ssElp[i+1]}];
Print["Elp: (",Length[ssElp[i]]," values) pvalue between sample set ",i," and ",i+1," is ",pValueElp];
,
{i,1,ssNum-1}
];

If the above T-Test results (p value) are less then our threshold we can say there is a significant difference between the two sample sets.

Sample Comparison Tests (when normality does NOT exist)

If our sample sets are not normally distributed, we can not perform a simple t-test. We can perform what are called location tests. I did some research on significance testing when non-normal distributions exists. I found a very nice reference:

http://www.statsoft.com/textbook/nonparametric-statistics

The paragraph below (which is from the reference above) is a key reference to what we’re doing here:

...the need is evident for statistical procedures that enable us to process data of “low quality,” from small samples, on variables about which nothing is known (concerning their distribution). Specifically, nonparametric methods were developed to be used in cases when the researcher knows nothing about the parameters of the variable of interest in the population (hence the name nonparametric). In more technical terms, nonparametric methods do not rely on the estimation of parameters (such as the mean or the standard deviation) describing the distribution of the variable of interest in the population. Therefore, these methods are also sometimes (and more appropriately) called parameter-free methods or distribution-free methods.

Being that I’m not a statistician but still need to determine if these sample sets are significant different, I let Mathematica determine the appropriate test. Notice that one of the above mentioned tests will probably be the test Mathematica chooses.

Note: If we run our normally distributed data through this analysis (speically, the “LocationEquivalenceTest”), Mathematica should detect this and use a more appropriate significant test, like a t-test.

Here we go with the hypothesis testing (assuming our sample sets are not normally distributed):

1. Our P value threshold is 0.05, which is our alpha.
2. The null hypotheses is the two populations have the same mean. (Remember we have to sample sets, which is not the population.)
3. Do the statistical test to compute the P value.
4. Compare the result P value to our threshold alpha value. If the P value is less then our threshold, we will reject the null hypothesis and say the difference between our samples is significant. (Which is what I’m hoping to see.) However, if the P value is greater than the threshold, we cannot reject the null hypothesis and any difference between our samples are not statistically significant; randomness, picked the “wrong” samples, etc.

myData=Table[
{
y,
N[Round[MannWhitneyTest[{ssElp[1],ssElp[y]}],1/1000]],
N[Round[MannWhitneyTest[{ssElp[2],ssElp[y]}],1/1000]],
N[Round[MannWhitneyTest[{ssElp[3],ssElp[y]}],1/1000]],
N[Round[MannWhitneyTest[{ssElp[4],ssElp[y]}],1/1000]],
N[Round[MannWhitneyTest[{ssElp[5],ssElp[y]}],1/1000]],
N[Round[MannWhitneyTest[{ssElp[6],ssElp[y]}],1/1000]],
N[Round[MannWhitneyTest[{ssElp[7],ssElp[y]}],1/1000]]
},{y,1,ssNum}
];

toGrid=Prepend[myData,{
"-","1","2","3","4","5","6","7"}
];
Grid[toGrid,Frame->All]

Do[
CpuHist=SmoothHistogram[{ssCpu[i],ssCpu[i+1]}];
CpuTest1=MannWhitneyTest[{ssCpu[i],ssCpu[i+1]}];
CpuTest2=LocationEquivalenceTest[{ssCpu[i],ssCpu[i+1]},{"TestDataTable","AutomaticTest"}];
Print["Cpu: (",Length[ssCpu[i]]," values) Between sample ",i," and ",i+1,". Test1=",CpuTest1," Test2=",CpuTest2];
Print[CpuHist];
Print["-------------------------------------------------------"];
ElptHist=SmoothHistogram[{ssElp[i],ssElp[i+1]}];
ElpTest1=MannWhitneyTest[{ssElp[i],ssElp[i+1]}];
ElpTest2=LocationEquivalenceTest[{ssElp[i],ssElp[i+1]},{"TestDataTable","AutomaticTest"}];
Print["Elp: (",Length[ssElp[i]]," values) Between sample ",i," and ",i+1,". Test1=",ElpTest1," Test2=",ElpTest2];
Print[ElptHist];
Print["---------------------------------------------------------------------------------------------------"];
,{i,1,ssNum-1}
];

Visually Comparing Sample Sets

I also wanted to get a nice visual picture of my sample sets...together. Sometimes I include all the sample sets and sometimes I don’t. It’s just based on what I want to convey. Sometimes you get a more appropriate view if all the data is not included.

SmoothHistogram[{ssCpu[1],ssCpu[2],ssCpu[3],ssCpu[4],ssCpu[5],ssCpu[6],ssCpu[7]},
PlotLabel->"Occurances vs Parse Time CPU(μs)",AxesLabel->{"CPU(μs)","Occurs"}]

SmoothHistogram[{ssElp[2],ssElp[3],ssElp[4],ssElp[5],ssElp[6],ssElp[7]},
PlotLabel->"Occurances vs Elapsed Soft Parse Time (μs)\nExecutions 2 to 7",AxesLabel->{"Time (μs)","Occurs"}]

SmoothHistogram[{ssElp[3],ssElp[4],ssElp[5],ssElp[6],ssElp[7]},
PlotLabel->"Occurances vs Elapsed Soft Parse Time (μs)\nExecutions 3 to 7",AxesLabel->{"Time (μs)","Occurs"}]