In previous posts in this series I explored the steps in writing an existing SQL query to instead run in Hadoop as a Map Reduce Job. These blog posts very much complement the slides from my session at Oracle Openworld 2012.

This series would not be complete however without mentioning other alternatives for achieving the same aim, notably Hive and Pig. here I will focus on Hive mainly as there is already detailed information on TPC-H queries including query 1 to run on Pig here. Pig provides a script based approach to writing Map Reduce jobs whereas Hive uses a SQL like interface and therefore for this particular example translating a SQL Query Hive is the most appropriate tool here without necessarily assuming that it would be better for all circumstances.

Hive automatically translates a language called HiveQL a subset of SQL into Map Reduce jobs enabling you to take advantage of your existing SQL skills, there are limitations however and Hive for example does not support all of the SQL functionality available in a relational databases such as correlated subqueries.

To use Hive you need to download and install the additional Hive package. When you run Hive you are presented with a Hive prompt with the first stage being to define the table to query. This example uses the data loaded in Pt1 of this blog series whilst noting that Hive does not support all of the data types available in an Oracle database.

CREATE EXTERNAL TABLE lineitem (
L_ORDERKEY INT
,L_PARTKEY INT
,L_SUPPKEY INT
,L_LINENUMBER INT
,L_QUANTITY INT
,L_EXTENDEDPRICE DOUBLE
,L_DISCOUNT DOUBLE
,L_TAX DOUBLE
,L_RETURNFLAG STRING
,L_LINESTATUS STRING
,L_SHIPDATE STRING
,L_COMMITDATE STRING
,L_RECEIPTDATE STRING
,L_SHIPINSTRUCT STRING
,L_SHIPMODE STRING
,L_COMMENT STRING) ROW FORMAT
DELIMITED FIELDS TERMINATED BY '|'
LINES TERMINATED BY '\n' 
STORED AS TEXTFILE
LOCATION '/user/input/tpch/256';

With the table defined you can then run your SQL at the Hive prompt. The main modification is the simplification of the l_shipdate field. Also knowing the characteristics of the Job we set the number of reducers to 1 before the query is run with the following example output.

hive> set hive.exec.reducers.max=1;
hive> select 
    >  l_returnflag, 
    >  l_linestatus, 
    >  sum(l_quantity) as sum_qty, 
    >  sum(l_extendedprice) as sum_base_price, 
    >  sum(l_extendedprice * (1 - l_discount)) as sum_disc_price, 
    >  sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge, 
    >  avg(l_quantity) as avg_qty, 
    >  avg(l_extendedprice) as avg_price, 
    >  avg(l_discount) as avg_disc, 
    >  count(*) as count_order 
    > from lineitem 
    > where l_shipdate <= '1998-08-11'
    > group by l_returnflag, l_linestatus 
    > order by l_returnflag, l_linestatus;
Total MapReduce jobs = 2
Launching Job 1 out of 2
...
Hadoop job information for Stage-1: number of mappers: 297; number of reducers: 1
2012-09-06 16:36:20,579 Stage-1 map = 0%,  reduce = 0%
2012-09-06 16:36:49,465 Stage-1 map = 1%,  reduce = 0%, Cumulative CPU 311.34 sec
...
2012-09-06 16:40:10,495 Stage-1 map = 100%,  reduce = 100%, Cumulative CPU 5565.64 sec
...
Launching Job 2 out of 2
...
Hadoop job information for Stage-2: number of mappers: 1; number of reducers: 1
2012-09-06 16:40:14,217 Stage-2 map = 0%,  reduce = 0%
...
2012-09-06 16:40:20,425 Stage-2 map = 100%,  reduce = 100%, Cumulative CPU 1.77 sec
...
MapReduce Jobs Launched: 
Job 0: Map: 297  Reduce: 1   Accumulative CPU: 5565.64 sec   HDFS Read: 0 HDFS Write: 0 SUCESS
Job 1: Map: 1  Reduce: 1   Accumulative CPU: 1.77 sec   HDFS Read: 0 HDFS Write: 0 SUCESS
...
OK
A F 3775127758 5.660776097194458E12 5.377736398183936E12 
5.59284742951593E12 25.499370423275426 38236.116984304950.050002243530958 148047881
N F 98553062 1.4777109838598007E11 1.4038496596503482E11 
1.4599979303277582E11 25.501556956882876 38237.1993888045250.049985284338051675 3864590
N O 7318871052 1.09746348095171E13 1.0425912729393264E13 
1.084295716582229E13 25.499840883624667 38236.9684354666460.04999754512223738 287016342
R F 3775724970 5.661603032745331E12 5.378513563915406E12 
5.593662252666915E12 25.50006628406532 38236.697258452910.050001304339683 148067261

It is interesting to observe that the job ran as 2 map reduce jobs instead of the one for the Job written by hand. Nevertheless the result was the same and the completion time as similar. However Hive significantly saved on the time it took to write the initial MapReduce Job to do the same thing. Consequently anyone looking to Hadoop  should certainly consider tools such as Hive and Pig for writing Map Reduce jobs especially when experienced with existing database environments.

In Pts 2 to 3 of this blog post I wrote the Map, Reduce and Combiner functions to process TPCH Query 1 in Hadoop. The final stage is to write the job control section and run the job to see how it performs in a test environment. An example job control section looks as follows, note that on earlier versions of Hadoop you need to replace Job.getInstance() with Job(new Configuration() to ensure that the Job will compile.

public static void main(String[] args) throws Exception {  
      Job job = Job.getInstance();  
      /* Job job = new Job(new Configuration()); */
      job.setNumReduceTasks(1);
      job.setJarByClass(TpchQ1.class);
      job.setJobName("tpchq1");  
      FileInputFormat.addInputPath(job, new Path(args[0]));  
      FileOutputFormat.setOutputPath(job, new Path(args[1]));  
      job.setMapOutputKeyClass(Text.class);  
      job.setMapOutputValueClass(DoubleArrayWritable.class);  
  
      job.setOutputKeyClass(Text.class);  
      job.setOutputValueClass(Text.class);  
  
      job.setMapperClass(Map.class);  
      job.setCombinerClass(Combine.class);
      job.setReducerClass(Reduce.class);  
  
System.exit(job.waitForCompletion(true) ? 0 : 1);
    }

Now we have the final Job and the full code is attached to the end of this blog post ready to compile and run. Copy this job and compile and package it as follows:

javac -classpath `./hadoop-2.0.0-cdh4.0.1/bin/hadoop classpath` -d tpchq1_classes/ TpchQ1.java
jar cvf ./tpchq1.jar -C tpchq1_classes/ .

The only remaining step is to run the job. Note that in the Job control section we specified an input and output path so these arguments are shown below with the input path being the path to the lineitem.tbl file loaded into HDFS in Pt1. You can track the job with your web browser by accessing the URL given in the job information as it runs.

./hadoop-2.0.0-cdh4.0.1/bin/hadoop jar tpchq1.jar org.myorg.TpchQ1 /user/input/tpch/lineitem256.tbl /user/output/tpch/TPCHQ1
...
INFO mapreduce.Job: The url to track the job: http://sandep1.example.com:8088/proxy/application_1344269167473_0011/
...
INFO mapreduce.Job: Job job_1344269167473_0011 running in uber mode : false
INFO mapreduce.Job:  map 0% reduce 0%
INFO mapreduce.Job:  map 1% reduce 0%
INFO mapreduce.Job:  map 2% reduce 0%
INFO mapreduce.Job:  map 3% reduce 0%
INFO mapreduce.Job:  map 4% reduce 0%
INFO mapreduce.Job:  map 5% reduce 0%
...
INFO mapreduce.Job:  map 96% reduce 29%
INFO mapreduce.Job:  map 97% reduce 30%
INFO mapreduce.Job:  map 99% reduce 30%
INFO mapreduce.Job:  map 100% reduce 30%
INFO mapreduce.Job:  map 100% reduce 100%
INFO mapreduce.Job: Job job_1344269167473_0011 completed successfully

When complete you can use either the command line tools or the web interface at default port 50070 to view the output file. For example (http://sandep1.example.com:50070). The output should resemble the following with which we have answered TPC-H Query 1.

A F  3.775127758E9 5.660776097194444E12 5.377736398183933E12 5.592847429515926E12 25.499370423275426 38236.116984304856 0.05000224353100122 1.48047881E8
N F  9.8553062E7 1.4777109838597998E11 1.403849659650348E11 1.4599979303277582E11 25.501556956882876 38237.199388804496 0.04998528433805351 3864590.0
N O  7.318871052E9 1.0974634809517127E13 1.0425912729393295E13 1.0842957165822328E13 25.499840883624667 38236.96843546674 0.04999754512239288 2.87016342E8
R F  3.77572497E9 5.661603032745334E12 5.378513563915411E12 5.593662252666924E12 25.50006628406532 38236.69725845293 0.05000130433972626 1.48067261E8

You might have observed that nowhere did we specify the ordering of the output  that is in the original SQL statement "order by l_returnflag, l_linestatus", this is because the ordering is the default behaviour of Hadoop so the output was sorted without being specified.

If you are interested in Job performance and tuning then come learn more at the Oracle Openworld 2012 Session "Optimal Oracle Systems for Big Data Performance and Scalability"

Session ID: CON4024
Session Title: Optimal Oracle Systems for Big Data Performance and Scalability
Moscone South Room: 236
Date: Wednesday 3rd October
Time: 10:15 - 11:15

/*
HADOOP MR Job to process TPCH Query 1
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
Query 1 (in Oracle Database Syntax)
select 
l_returnflag, 
l_linestatus, 
sum(l_quantity) as sum_qty, 
sum(l_extendedprice) as sum_base_price, 
sum(l_extendedprice * (1 - l_discount)) as sum_disc_price, 
sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge, 
avg(l_quantity) as avg_qty, 
avg(l_extendedprice) as avg_price, 
avg(l_discount) as avg_disc, 
count(*) as count_order from lineitem where l_shipdate <= date '1998-12-01' - interval '112' day (3) 
group by l_returnflag, l_linestatus 
order by l_returnflag, l_linestatus
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
Input data is file called linieitem.tbl generated by dbgen available from tpc.org
Example Row of expected input data is as follows:
1|15518935|768951|1|17|33203.72|0.04|0.02|N|O|1996-03-13|1996-02-12|1996-03-22|DELIVER IN PERSON|TRUCK|egular courts above the|
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
In this example l_shipdate in the where clause is <= 11-AUG-98 i.e.
SQL> select date '1998-12-01' - interval '112' day (3) from dual;
DATE'1998
---------
11-AUG-98
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
Example Output on SF100 Schema 
A F  3.775127758E9 5.660776097194444E12 5.377736398183933E12 5.592847429515926E12 25.499370423275426 38236.116984304856 0.05000224353100122 1.48047881E8
N F  9.8553062E7 1.4777109838597998E11 1.403849659650348E11 1.4599979303277582E11 25.501556956882876 38237.199388804496 0.04998528433805351 3864590.0
N O  7.318871052E9 1.0974634809517127E13 1.0425912729393295E13 1.0842957165822328E13 25.499840883624667 38236.96843546674 0.04999754512239288 2.87016342E8
R F  3.77572497E9 5.661603032745334E12 5.378513563915411E12 5.593662252666924E12 25.50006628406532 38236.69725845293 0.05000130433972626 1.48067261E8
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
Example compile and run using Cloudera Hadoop
javac -classpath `./hadoop-2.0.0-cdh4.0.1/bin/hadoop classpath` -d tpchq1_classes/ TpchQ1.java
jar cvf ./tpchq1.jar -C tpchq1_classes/ .
./hadoop-2.0.0-cdh4.0.1/bin/hadoop jar tpchq1.jar org.myorg.TpchQ1 /user/input/tpch/lineitem.tbl /user/output/tpch/TPCHQ1
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
*/
package org.myorg;  
import java.io.IOException;  
import java.util.*;  
import org.apache.hadoop.fs.Path;  
import org.apache.hadoop.io.*;  
import org.apache.hadoop.mapreduce.Mapper;  
import org.apache.hadoop.mapreduce.Reducer;  
import org.apache.hadoop.mapreduce.Job;  
import org.apache.hadoop.mapreduce.lib.input.FileInputFormat;  
import org.apache.hadoop.mapreduce.lib.output.FileOutputFormat;  
import org.apache.hadoop.util.*;  
  
public class TpchQ1 {  
  public static class Map extends Mapper <LongWritable, Text, Text, DoubleArrayWritable> {  
@Override 
      public void map(LongWritable key, Text value, Context context) throws IOException, InterruptedException {  
     Text tpchq1key = new Text();
     DoubleWritable[] outArray = new DoubleWritable[6];
     /* Set and clear date to use in the where clause as 11th August 1998 */
     Calendar wc = Calendar.getInstance();
     wc.clear();
     wc.set(1998,8,11,0,0,0);
     wc.set(Calendar.MILLISECOND, 0);
     /* Set and clear date to use for input */
     Calendar win = Calendar.getInstance();
     win.clear();
     win.set(1970,1,1,0,0,0);
     win.set(Calendar.MILLISECOND, 0);
/* Parse each line of lineitem.tbl and extract the 6 fields of interest for the SQL Query starting from field 5 */
     String line = value.toString();  
     StringTokenizer tokenizer = new StringTokenizer(line,"|");  
int i = 1;
double l_extendedprice = 0.0;
double l_discount = 0.0;
double l_tax = 0.0;
        while (tokenizer.hasMoreTokens()) {  
String token = tokenizer.nextToken();
if(i == 5) {
/* Fields 5 to 8 are the values */
/* L_QUANTITY */
outArray[0] = new DoubleWritable(Double.parseDouble(token)); 
}
if(i == 6) {
/* L_EXTENDEDPRICE and SUM_BASE_PRICE */
outArray[1] = new DoubleWritable(Double.parseDouble(token)); 
/* */
}
else if (i == 7) {
/* L_DISCOUNT and SUM_DISCOUNT */
outArray[2] = new DoubleWritable(Double.parseDouble(token)); 
}
else if ( i == 8 ) {
/* L_TAX */
l_tax = (Double.parseDouble(token)); 
}
/* Fields 9 and 10 are the GROUP BY expression in the SQL query and are therefore concatendated as the Key */
else if (i == 9) {
/* L_RETURNFLAG */
tpchq1key.set(tpchq1key.toString()+" "+token);
}
else if (i == 10) {
/* L_LINESTATUS */
tpchq1key.set(tpchq1key.toString()+" "+token);
}
else if (i == 11) {
/* L_SHIPDATE */
StringTokenizer wheredate = new StringTokenizer(token,"-");
win.set(Integer.parseInt(wheredate.nextToken()),Integer.parseInt(wheredate.nextToken()),Integer.parseInt(wheredate.nextToken()));
}
i++;
    }  
/* Implement the WHERE clause, only collect the output if L_SHIPDATE <= (1998,8,11) */
if(win.getTimeInMillis() <= wc.getTimeInMillis()) {
l_extendedprice = outArray[1].get();
l_discount = outArray[2].get();
/* Calculate Sum disc price and Sum Charge */
double sum_disc_price = (l_extendedprice * (1 - l_discount));
double sum_charge = (l_extendedprice * (1 - l_discount) * (1 + l_tax));
outArray[3] = new DoubleWritable(1.0);
outArray[4] = new DoubleWritable(sum_disc_price);
outArray[5] = new DoubleWritable(sum_charge);
DoubleArrayWritable da = new DoubleArrayWritable();
da.set(outArray);
context.write(tpchq1key, da);
  } 
   }  
}
public static class Combine extends Reducer <Text, DoubleArrayWritable, Text, DoubleArrayWritable> {   
@Override
public void reduce(Text key, Iterable<DoubleArrayWritable> values, Context context) throws IOException, InterruptedException {  
int i = 1;
DoubleWritable[] outArray = new DoubleWritable[6];
DoubleWritable[] x;
double sum_qty = 0.0;
double sum_base_price = 0.0;
double sum_discount = 0.0;
double count_star = 0.0;
double sum_disc_price = 0.0;
double sum_charge = 0.0;
/* In Combiner calculate interim sum values */
for (DoubleArrayWritable val : values) { 
x = (DoubleWritable[]) val.toArray();
sum_qty += x[0].get();
sum_base_price += x[1].get();
sum_discount += x[2].get();
count_star += x[3].get();
sum_disc_price += x[4].get();
sum_charge += x[5].get();
  }
outArray[0] = new DoubleWritable(sum_qty); 
outArray[1] = new DoubleWritable(sum_base_price); 
outArray[2] = new DoubleWritable(sum_discount); 
outArray[3] = new DoubleWritable(count_star); 
outArray[4] = new DoubleWritable(sum_disc_price);
outArray[5] = new DoubleWritable(sum_charge);
DoubleArrayWritable da = new DoubleArrayWritable();
da.set(outArray);
context.write(key, da);
     }  
}
public static class Reduce extends Reducer <Text, DoubleArrayWritable, Text, Text> {   
@Override
public void reduce(Text key, Iterable<DoubleArrayWritable> values, Context context) throws IOException, InterruptedException {  
String line;
int i = 1;
DoubleWritable[] x;
double sum_qty = 0.0;
double sum_base_price = 0.0;
double sum_discount = 0.0;
double sum_disc_price = 0.0;
double sum_charge = 0.0;
double avg_qty = 0.0;
double avg_price = 0.0;
double avg_disc = 0.0;
double count_star = 0.0;
/* In reducer calculate sum values and averages */
for (DoubleArrayWritable val : values) {
x = (DoubleWritable[]) val.toArray();
sum_qty += x[0].get();
sum_base_price += x[1].get();
sum_discount += x[2].get();
count_star += x[3].get();
sum_disc_price += x[4].get();
sum_charge += x[5].get();
        }
avg_qty = sum_qty/count_star;
avg_price = sum_base_price/count_star;
avg_disc = sum_discount/count_star;
/* Format and collect the output */
Text tpchq1redval = new Text(" "+sum_qty+" "+sum_base_price+" "+sum_disc_price+" "+sum_charge+" "+avg_qty+" "+avg_price+" "+avg_disc+" "+count_star);
       context.write(key, tpchq1redval);  
     }  
}
public static class DoubleArrayWritable extends ArrayWritable { 
public DoubleArrayWritable() { 
    super(DoubleWritable.class); 
} 
public DoubleArrayWritable(DoubleWritable[] values) { 
    super(DoubleWritable.class, values); 
} 
public DoubleWritable get(int idx) { 
    return(DoubleWritable) get()[idx]; 
} 
} 
  
    public static void main(String[] args) throws Exception {  
      Job job = Job.getInstance();  
      /* Job job = new Job(new Configuration()); */
      job.setNumReduceTasks(1);
      job.setJarByClass(TpchQ1.class);
      job.setJobName("tpchq1");  
      FileInputFormat.addInputPath(job, new Path(args[0]));  
      FileOutputFormat.setOutputPath(job, new Path(args[1]));  
      job.setMapOutputKeyClass(Text.class);  
      job.setMapOutputValueClass(DoubleArrayWritable.class);  
  
      job.setOutputKeyClass(Text.class);  
      job.setOutputValueClass(Text.class);  
  
      job.setMapperClass(Map.class);  
      job.setCombinerClass(Combine.class);
      job.setReducerClass(Reduce.class);  
  
System.exit(job.waitForCompletion(true) ? 0 : 1);
    }  
}

In Pt1 of this blog post I looked at a SQL Query and data set to run in Hadoop and in Pt2 wrote the Map function to extract the relevant fields from the data set to satisfy the query. At this point however we still have not implemented any of the aggregate functions and still have a large key and value intermediate data set. The only data eliminated so far has been the lines examined where the date was not less than or equal to 11-AUG-98. On the test data set out of the initial 600037902 lines of data we now have 586996074 lines remaining, to complete the query we now need to write the reduce phase.
The Reduce method will extend the Reducer class. This needs to accept the intermediate key value pairs output by the mapper and therefore will receive as input the key which is fields 9 and 10  concatenated and the DoubleArrayWritable containing the values. For every key we need to iterate through the values and calcuate the totals required for the SUM(), AVG() and COUNT() functions. Once these have been calculated we can format the output as text to be written to a file that will give us exactly the same result as if the query had been processed by a relational database. This reduce phase will look something as follows by simply adding all of the values in the array for the SUM() functions and then dividing by the COUNT() value to calculate the result of the AVG() functions.

for (DoubleArrayWritable val : values) {
x = (DoubleWritable[]) val.toArray();
sum_qty += x[0].get();
sum_base_price += x[1].get();
sum_discount += x[2].get();
count_star += x[3].get();
sum_disc_price += x[4].get();
sum_charge += x[5].get();
        }
avg_qty = sum_qty/count_star;
avg_price = sum_base_price/count_star;
avg_disc = sum_discount/count_star;
/* Format and collect the output */
Text tpchq1redval = new Text(" "+sum_qty+" "+sum_base_price+" "+sum_disc_price+" "+sum_charge+" "+avg_qty+" "+avg_price+" "+avg_disc+" "+count_star);
       context.write(key, tpchq1redval);  
     }  
}

Coupled with the Map phase and a Job Control section (this will be covered in the next post on running the job) this Job is now ready to run. However as we have noted previously just for our 100GB data set the map phase will output over 58 million lines of data which will involve a lot of network traffic and disk writes. We can make this more efficient by writing a Combiner.

The Combiner also extends the Reducer and in simple cases but not all (as we will cover in a moment) can be exactly the same as the Reducer. The aim of the combiner is to perform a Reducer type operation on the subset of data produced by each Mapper which will then minimise the amount of data that needs to be transferred throughout the cluster from Map to Reduce. The single most important thing about the Combiner is that there is no certainty that it will run. It is available as an optimization but for a particular Map output it might not run at all and there is no way to force it to run. From a development perspective this has important consequences, you should be able to comment out the line in the Job Control section that calls the Combiner and the result produced by the MapReduce Job stays exactly the same. Additionally the input fields for the Combiner must be exactly the same as expected by the Reducer to operate on the Map output and the Combiner output must also correspond to the input expected by the Reducer.  If you Combiner does not adhere to these restrictions your job may compile and run and you will not receive an error, however if not implemented correctly your results may change on each run from additional factors such as changing the imput block size. Finally the Combiner operation must be both commutative and associative. In other words the Combiner operation must ensure that both changing the order of the operands as well as the grouping of the operations you perform does not change the result. In our example the SUM() function is both commutative and associative, the numbers can be summed in any order and we can perform the sum operation on different groups and the result will always remain the same. AVG() on the other hand is commutative but not associative. We can calculate the average with the input data in any order, however we cannot take an average of smaller groups of values and then take the average of this intermediate data and expect the result to be the same. For this reason the Combiner can perform the SUM() operation but not the AVG() and can look as follows producing the intermediate sum values only for the Reducer.

for (DoubleArrayWritable val : values) { 
x = (DoubleWritable[]) val.toArray();
sum_qty += x[0].get();
sum_base_price += x[1].get();
sum_discount += x[2].get();
count_star += x[3].get();
sum_disc_price += x[4].get();
sum_charge += x[5].get();
  }
outArray[0] = new DoubleWritable(sum_qty); 
outArray[1] = new DoubleWritable(sum_base_price); 
outArray[2] = new DoubleWritable(sum_discount); 
outArray[3] = new DoubleWritable(count_star); 
outArray[4] = new DoubleWritable(sum_disc_price);
outArray[5] = new DoubleWritable(sum_charge);
DoubleArrayWritable da = new DoubleArrayWritable();
da.set(outArray);
context.write(key, da);
     }  

At this stage we have written the Mapper, Reducer and Combiner and in Pt4 will look at adding the Job Control section to produce the completed MapReduce job. We will then consider compiling and running the job and tuning for performance.

In Part I of this blog post on information security I investigated the performance of Oracle TDE with hardware acceleration using Intel AES-NI and saw significant performance gains. However, I subtly sidestepped the question of what happens when the data is cached? As we found during part I, Oracle does not cache the data at all, instead choosing to read from disk and decrypt every time the same query is run. Why does Oracle not cache the data after the first time the query is run?

Running a trace on the query and looking in the trace file helps answer the question by showing that the operation used to read the data was identified as a direct path read. This will be familiar to Oracle DBAs with Oracle parallel query experience. It means that the data is read directly into the user session's PGA (bypassing the SGA), instead of using a more familiar db file scattered read, where the data could potentially be cached but placed at the end of the LRU list and aged out more quickly if space in the buffer cache is required. Why does Oracle use a direct path read for a non-parallel query? The answer lies in Note: 793845.1 from My Oracle Support that says:

"There have been changes in 11g in the heuristics to choose between direct path reads or reads through buffer cache for serial table scans. In 10g, serial table scans for “large” tables used to go through cache (by default) which is not the case anymore. In 11g, this decision to read via direct path or through cache is based on the size of the table, buffer cache size and various other stats."

This makes a great deal of sense. Given the massive gains in platform bandwidth and latency, direct path read can be as fast as a db file scattered read and also improve scalability: There is no need to acquire a cache buffers chains latch to scan data buffered in memory to prevent that data being changed while it is in the process of being scanned. It is also beneficial in a RAC clustered environment where other nodes may be interested in the contents of the local buffer cache. As the My Oracle Support note mentions, the decision whether or not to cache the data is dependent on a number of factors. One of the most important is whether the table exceeds a size value based on the value determined by the hidden parameter _small_table_threshold. Instead of using this parameter, however, I granted the user a higher privilege and then used the event 10949 "Disable autotune direct path read for serial full table scan" to modify the default behaviour to observe its impact on clear text and encrypted data as follows:

SQL> alter session set events '10949 trace name context forever, level 1';

After doing this, re-running the same query on the clear text data does physical reads but takes slightly longer to cache.

Elapsed: 00:00:42.53

Subsequent runs shows that we have returned almost to previous performance, although not outperforming the direct path read.

Elapsed: 00:00:29.55

Now the data is cached in the buffer cache in the SGA and not read from disk.

 
Statistics
----------------------------------------------------------
...
0 physical reads
...

Tracing also showed that autotune direct path read was disabled and db file scattered read is being used.

I then tried setting the same event for the unencrypted data with hardware acceleration disabled. Running the query on the first occasion took slightly longer than before as the data was read from disk, decrypted, and cached in the buffer cache.

Elapsed: 00:02:25.97

However, on subsequent runs the difference was dramatic. The data was cached in clear text in memory and therefore ran considerably quicker.

Elapsed: 00:00:29.30

Similarly, with AES-NI enabled the initial read from disk and decryption took a similar length of time as before.

Elapsed: 00:00:45.30

Once the data was cached no decryption was required.

Elapsed: 00:00:29.49

To recap, the following are results when the query is cached in the SGA:

Clear Text Query cached = 00:00:29.55
Software only encryption cached as clear text = 00:00:29.30
AES-NI accelerated cached as clear text = 00:00:29.49 

In other words, the result is exactly the same and entirely expected from the TDE FAQ with cached data. Once the data is in the buffer cache it is in clear text and should therefore take a similar time to read irrespective of whether the tablespace is encrypted.

For Oracle encryption performance when running queries that use full table scans once the data is cached in memory (for tablespace as opposed to column encryption) it is in clear text and therefore hardware acceleration will not be used after the first read from disk. However, as we saw in Part I it would be wrong to assume that just because we size the buffer cache adequately that a table would necessarily be cached. Additionally, at this release of Oracle (11.2.0.2), whether the data is encrypted does not impact upon how full table scans are implemented. You have the manual intervention alternative and using unsupported underscore parameters to modify Oracle's behaviour. In these simple tests I have tested for only a single user, without considering the implications of scalability or clustering. If you do modify Oracle's behaviour you will need to retest that your assumptions are correct for each and every Oracle release.

What we want with Oracle database encryption as the name TDE implies is for it to be transparent without needing to modify practices at all just because we want the data to be encrypted. What these simple tests show is the best way to do this is by using Intel Xeon processors with AES-NI for Oracle database encryption acceleration.

Many of you who are interested in the subject of optimizing platforms for database performance will be familiar with Kevin Closson's blog on platforms, databases and storage. Kevin knows his subject incredibly well, so I was interested in the subject of a recent post discussing the topic of whether you should put your Oracle database redo logs on solid state disks (SSDs).  Kevin also refers to a post by Guy Harrison, where Guy's concludes, "I’m yet to see any evidence that putting redo logs on SSD is a good idea".  This got me really interested because for the last couple of years, I have put my Oracle redo logs or SQL Server transaction log on nothing but SSDs! Maybe I've been missing something here, and with the increase of customers testing their environments on SSDs, I decided to put it to the test.

In our labs we have a 4 socket Xeon E7 based server, which I hooked up to both SSD and regular Hard Disk Drive (HDD) storage. To be precise for the redo SSD storage, I configured an Intel® RAID Controller RS2BL080  with 8 x Intel X25-E Extreme SATA SSD (model SSDSA2SH064G1GC) in a RAID 0 configuration. For the HDD redo, I used a well-known brand of storage array configured with 15 x 15KRPM Seagate Cheetah FC 4Gb/s hard drives, the host was connected by an Emulex Zephyr Lighpulse Fibre Channel Host Adapter to the HDD storage array. Again, all 15 drives were configured as RAID 0.

Note that this is not a like-for-like comparison and there are some differences between them, obviously there were 15 HDDs compared to 8 SSDs but also for example the RS2BL080 has 512MB of cache compared to the 4GB on the storage array, however I was not aiming to definitively do a 'which is better' test, the aim was to see whether putting redo on SSD (as I have been doing to date) is a good idea or not using the HDD confugration as a control. Also the aim was not to prepare any special configuration, all the options for configuring the storage were taken at the default for every step of the way.

On to the test I used Oracle Linux 5.6 and Oracle 11.2.0.2, and created a database with the data storage area on ASM, the data storage area was also on duplicate SSD storage with the same hardware configuration as the SSD redo area. Then, I configured redo logs on both the SSD and HDD redo area, first with raw devices and then with ASM. I used Hammerora for the workload and configured the schema for the in-built OLTP test. After that, I created multiple sessions without keying and thinking time and ran 4 timed tests on SSD/RAW, HDD/RAW, SSD/ASM, HDD/ASM collecting the AWR reports as I went. As expected, the tests generated a significant amount of redo up to 50GB over a 5 minute period. The Oracle transaction rate was in the multiple tens of thousands per second (so in the millions per minute). The redo log files were configured large enough so there was no log file switches encountered during each test to interrupt the redo.

Looking at the AWR report for "IOStat by Filetype" section showed the following results:

Looking at the actual Iostat information recorded during the ASM tests showed the following (which corresponds to the ASM data):

SSD

HDD

So, the redo on the 8 x SSD drives is writing 1.28X more data per second and doing 6.4X the writes/second although the avgrq-sz shows that the HDD configuration is writing more data for each operation. However, the avwait, svctm and %util show the the HDD configuration is busier and responding slower. The log file sync times in the Top 5 Timed Events looked as follows:

SSD

HDD

It has to be said that both are pretty good but not surprisingly from this redo related data when using the redo on the 8 x SSD drives the system is processing 1.25X the number of transactions as it does when the redo is on the 15 x HDD drives for exactly the same high throughput workload.  ASM does have more impact on the SSD configuration than the HDD configuration but not significantly in this particular case with 11.2.0.2.

What about changing the redo block size?  You can check your disk sector size in (for my case the SSD disk as sdb)  /sys/block/sdb/queue. Note as this is a LUN this is presented by the RAID card although should be related to the physical disk sector size which in the case of the Intel X25-E as shown in the datasheet is 512 bytes

[root@wesex1 queue]# pwd
/sys/block/sdb/queue
[root@wesex1 queue]# cat physical_block_size
512
[root@wesex1 queue]# cat logical_block_size
512

Unless this is different from the standard 512 bytes Oracle will not permit you to change it and with the SSD configuration used the default options are the correct ones.

SQL> alter database add logfile group 1 '+REDO' size 90g blocksize 4096;
alter database add logfile group 1 '+REDO' size 90g blocksize 4096
*
ERROR at line 1:
ORA-01378: The logical block size (4096) of file +REDO is not compatible with
the disk sector size (media sector size is 512 and host sector size is 512)
SQL> alter database add logfile group 1 '+REDO' size 90g blocksize 512;
Database altered. 

Not too surprisingly, with the same system and disks very similar results are found with SQL Server on Windows and the Transaction Log on NTFS on SSDs - a different operating system and database but still excellent performance from exactly the same storage configuration.

In conclusion, should you put your database redo and transaction logs on SSDs a good idea? According to my findings yes. I suggest using the Intel X25-E Extreme SATA Solid-State Drive.

In this case, why does there seem to be a difference in opinion? Maybe I should rephrase my conclusion in that what I found precisely was that putting database redo and transaction logs on a RAID configuration of a small number of Intel X-25E SSDs gave me excellent database throughput. However, not all SSDs are the same and so I can't draw any conclusions about any other type of flash storage being suitable for redo or transaction logs from these tests. My recommendation is always to test your own setup.

It has been a while since my first blog post on Oracle database performance, so what better time to follow up again than my next appearance at Oracle Openworld 2011!

Where was I in 2010? I was presenting at Linuxcon in Boston. This year, I'm back at Openworld. Although the title is similar, so much has changed.

To start off, here are the logistics:

Oracle Database Performance and Scalability on Intel Xeon Platforms

Presenters:  Steve Shaw and Eric Wan
ID#: 31701
Track: Database
Date:  3 October 2011
Time: 11:00 - 12:00
Venue: Moscone South
Room: Room 300

We'll talk about defining both performance and scalability, and what is desirable. We'll also look at getting the basics right at the system and OS settings. Then, we'll take a walk through the platform, as we look at the CPU and see measurements of the impact of features such as turbo boost, hyper-threading and 4 and 8 socket scalability tested against an Oracle database.

We will also cover memory performance, including options such as NUMA settings. At the I/O level, we'll look at SSDs. Finally, we will show an approach to holistic performance testing and engineering the system as a whole to work together for optimal performance and scalability.

If you don't get a chance to stop by the presentation, I'll be at the Intel booth with Eric at 4.30pm to go in-depth on some of the topics. Even if you can't make it to Openworld 2011, I plan to follow up on Openworld topics and some of the areas we will speak about on this blog.