I wrote a note a few years ago about SQL*Net compression (this will open in a new window so that you can read the posts concurrently), showing how the order of the data returned by a query could affect the amount of network traffic. An example in the note demonstrated, using autotrace statistics that the number of bytes transferred could change dramatically if you sorted your return data set. At the time I asked, and postponed answering, the question: “but how come the number of SQL*Net round trips has not changed ?”

A couple of weeks ago someone asked me if I had ever got around to answering this question – and I hadn’t. So I started by writing an answering comment, then decided it was getting a little long so transferred it to a separate blog note, and here it is.

We can start with the two sets of stats:

Statistics
----------------------------------------------------------
          4  recursive calls
          0  db block gets
        143  consistent gets
          0  physical reads
          0  redo size
     425479  bytes sent via SQL*Net to client
        494  bytes received via SQL*Net from client
         11  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      10000  rows processed

Statistics
----------------------------------------------------------
          4  recursive calls
          0  db block gets
        133  consistent gets
          0  physical reads
          0  redo size
      79287  bytes sent via SQL*Net to client
        494  bytes received via SQL*Net from client
         11  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      10000  rows processed

Basically, the answer comes from interpreting the names of the statistics correctly.

The “SQL*Net roundtrips to/from client” is the number of pairs of waits for “SQL*Net Message from client”/“SQL*Net Message to client” – which is controlled (in this example) very strongly by the array fetch size and the total number of rows (Note: we have arraysize = 1000 and rows returned = 10000 … the number of round trips is very close to 10,000 / 1,000.) This is exactly how the statistic is described in the SQL*Plus User’s Guide and Reference which says: “Total number of Oracle Net messages sent to and received from the client” – the trouble is, this description is easy to mis-understand.

If we enable extended SQL tracing (event 10046 / dbms_monitor) we would see that there is more activity going on than is reported by the autotrace statistics:
Here’s a sample of waits from the first query (retested on 11.2.0.2) with one blank line inserted for clarity:

WAIT #140199974133928: nam='SQL*Net message from client' ela= 319 driver id=1650815232 #bytes=1 p3=0 obj#=-40016366 tim=1370786699605359
WAIT #140199974133928: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=-40016366 tim=1370786699605402
WAIT #140199974133928: nam='SQL*Net more data to client' ela= 34 driver id=1650815232 #bytes=8155 p3=0 obj#=-40016366 tim=1370786699605478
WAIT #140199974133928: nam='SQL*Net more data to client' ela= 4 driver id=1650815232 #bytes=8148 p3=0 obj#=-40016366 tim=1370786699605528
WAIT #140199974133928: nam='SQL*Net more data to client' ela= 3 driver id=1650815232 #bytes=8148 p3=0 obj#=-40016366 tim=1370786699605565
WAIT #140199974133928: nam='SQL*Net more data to client' ela= 3 driver id=1650815232 #bytes=8148 p3=0 obj#=-40016366 tim=1370786699605603
WAIT #140199974133928: nam='SQL*Net more data to client' ela= 3 driver id=1650815232 #bytes=8148 p3=0 obj#=-40016366 tim=1370786699605640
FETCH #140199974133928:c=0,e=262,p=0,cr=7,cu=0,mis=0,r=1000,dep=0,og=1,plh=3617692013,tim=1370786699605654

WAIT #140199974133928: nam='SQL*Net message from client' ela= 319 driver id=1650815232 #bytes=1 p3=0 obj#=-40016366 tim=1370786699605993
WAIT #140199974133928: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=-40016366 tim=1370786699606024
WAIT #140199974133928: nam='SQL*Net more data to client' ela= 19 driver id=1650815232 #bytes=8155 p3=0 obj#=-40016366 tim=1370786699606082
WAIT #140199974133928: nam='SQL*Net more data to client' ela= 3 driver id=1650815232 #bytes=8148 p3=0 obj#=-40016366 tim=1370786699606127
WAIT #140199974133928: nam='SQL*Net more data to client' ela= 3 driver id=1650815232 #bytes=8148 p3=0 obj#=-40016366 tim=1370786699606163
WAIT #140199974133928: nam='SQL*Net more data to client' ela= 3 driver id=1650815232 #bytes=8148 p3=0 obj#=-40016366 tim=1370786699606199
WAIT #140199974133928: nam='SQL*Net more data to client' ela= 3 driver id=1650815232 #bytes=8148 p3=0 obj#=-40016366 tim=1370786699606234
FETCH #140199974133928:c=0,e=230,p=0,cr=7,cu=0,mis=0,r=1000,dep=0,og=1,plh=3617692013,tim=1370786699606247

And here’s the equivalent on the 2nd query:

WAIT #140199974133928: nam='SQL*Net message from client' ela= 1187 driver id=1650815232 #bytes=1 p3=0 obj#=-40016366 tim=1370786699689809
WAIT #140199974133928: nam='SQL*Net message to client' ela= 4 driver id=1650815232 #bytes=1 p3=0 obj#=-40016366 tim=1370786699689893
FETCH #140199974133928:c=0,e=540,p=0,cr=0,cu=0,mis=0,r=1000,dep=0,og=1,plh=2148421099,tim=1370786699690421

WAIT #140199974133928: nam='SQL*Net message from client' ela= 1096 driver id=1650815232 #bytes=1 p3=0 obj#=-40016366 tim=1370786699691585
WAIT #140199974133928: nam='SQL*Net message to client' ela= 3 driver id=1650815232 #bytes=1 p3=0 obj#=-40016366 tim=1370786699691666
FETCH #140199974133928:c=0,e=506,p=0,cr=0,cu=0,mis=0,r=999,dep=0,og=1,plh=2148421099,tim=1370786699692161

My session data unit (SDU) has defaulted to roughly 8KB and if you convert the driver id (1650815232) to hexadecimal you get 0×62657100, which you can recognise as BEQ, the local (bequeath) connection.

When there is little de-duplication available in the data order (first example) the 1,000 rows I fetch on each fetch call total about 48KB and I end up doing one “SQL*Net message to client” with five follow-up “SQL*Net more data to client”. (The bytes parameter for the “SQL*Net message to client” always shows 1 – Cary Millsap, I think, has pointed out that this is a long-standing error, so I’m assuming the call would be passing 8KB like all the other calls).

When I get maximum de-duplication in the data the entire 1,000 rows manages to compress down to less than the 8KB that’s available and all I see is the single “SQL*Net message to client”. with no calls for more data.

So – SQL*Net roundtrips isn’t measuring what you think – based on an informal reading of the description you and I might expect the first test to report far more round trips than the second, but that’s not the way that Oracle reports it, it’s literally counting the number of specific pairs of waits for “SQL*Net message to/from client” – the “more data” trips don’t get counted. As a result the same number of round-trips can report a hugely (and arbitrarily) different number of bytes.

Footnote:

I’ve only just noticed that I seem to be the only person that uses SQL*Plus. When I searched at tahiti.oracle.com to find the documented description of this statistics – I notice that the manual title was “SQL*Plus User’s Guide and Reference”; if there were more users it would be “SQL*Plus Users’ Guide and Reference” (check the apostrophe). Since I know that I use SQL*Plus that must mean no-one else does ;) I guess everyone else uses GUIs nowadays.

Hyperion is the first in the Hyperion Cantos series by Dan Simmons.

What a great Sci-Fi book! A group of seven travellers are on a pilgrimage to Hyperion. Six of the seven tell the stories of how they came to be there, with the sixth story kind-of linking things together. There is no real conclusion to the story as the next book carries on the story from the point the first one ends. It was definitely written as a series!

The timeline jumps around quite a bit through the book, but in a good way. It’s not done in a confusing way.

Definitely worth a look for any Sci-Fi readers out there.

Cheers

Tim…

PS. I put the authors name in the title, for fear of confusing people into thinking this was about work.

Ever wonder about Big Data and what exactly it means, especially if you are already an Oracle Database professional? Or, do you get lost in the jargon warfare that spews out terms like Hadoop, Map/Reduce and HDFS? In this post I will attempt to explain these terms from the perspective of a traditional database practitioner but getting wet on the Big Data issues by being thrown in the water. I was inspired to write this from the recent incident involving NSA scooping up Verizon call data invading privacy of the citizens.

It's not a news anymore that many people are reacting in different ways to the news of National Security Agency accessing the phone records of Verizon - cellphone and land lines - to learn who called who. But one thing is common - all these reactions are pretty intense. While the debate lingers on whether the government overstepped its boundaries in accessing the records of private citizens or whether it was perfectly justified in the context of the threats our country is facing right now - and there will be a debate for some time to come - I had a different thought of my own. I was wondering about the technological aspects of the case. The phone records must be massive. What kind of tools and technologies the folks in the "black room", a.k.a Room 641A must have used to collate and synthesize the records into meaningful intelligence - the very task NSA is supposed to gather and act upon? Along with anything it piques the interest from an engineering point of view.

And that brings the attention to the aspect of computation involving massive amounts of data. It's one thing to slice and dice a finite, reasonable amount of dataset; but in the case of the phone records, and especially collated with other records to identify criminal or pseudo-criminal activities such as financial records, travel records, etc., the traditional databases such as Oracle and DB2 likely will not scale well. But the challenge is not just in the realm of romanticized espionage; it's very much a concern for purely un-romantic corporations who, among other things, want to track customer behavior to fine tune their service and product offerings. Well, it's espionage of a slightly different kind. Perhaps the sources of data are different - website logs, Facebook feeds as opposed to phone records; but the challenges are the same - how to synthesize enormous amounts of seeming unrelated data into meaningful intelligence. This is the challenge of the "Big Data".

Meet the V's

Several years ago, Yahoo! faced the same issue - how to present the attractiveness the webpages it puts on its portal and analyze the pattern of clicking by users to attract advertisers to put relevant ads on their pages. Google had a similar challenge of indexing the entire World Wide Web in its servers so that it present search results very, very quickly. Both of these issues represent the issues that others probably didn't face earlier. Here are the relatively unique aspects of this data, which are known as the "Three V's of Big Data".

• Volume - the sheer mass of the data made it difficult, if not impossible, to sort through them
• Velocity - the data was highly transient. Website logs are relevant only for that time period; for a different period it was different
• Variety - the data was not pre-defined and not quite structured - at least not the way we think of structure when we think of relational databases

Both these companies realized they are not going to address the challenges using the traditional relational databases, at least not in the scale they wanted. So, they developed tools and technologies to address these very concerns. They took a page from the super-computing paradigm of divide and conquer. Instead of dissecting the dataset as a whole, they divided it into smaller chunks to be processed by hundreds, even thousands of small servers. This approach solved three basic, crippling problems:

1. There was no need to use large servers, which typically costs a lot more than small servers
2. There was a built-in data redundancy since the data was replicated between these small servers
3. But the most important, it could scale well, very well simply by adding more of those small servers

This is the fundamental concept that have rise to Hadoop. But before we cover that, we need to learn about another important concept.

Name=Value Pairs

A typical relational database works by logically arranging the data into rows and columns. Here is an example. You decide on a table design to hold your customers, named simply CUSTOMERS. It has the columns CUST_ID, NAME, ADDRESS, PHONE. Later, your organization decides to provide some incentives to the spouses as well and so you added another column - SPOUSE.

Everything was well, until the time you discovered customer 1 and spouse are divorced and there is a new spouse now. However the company decides to keep the names of the ex-spouses as well, for marketing analytics. Like the right relational application, you decide to break SPOUSE away from the main table and create a new table - SPOUSES, which is a child of CUSTOMERS, joined by CUST_ID. This requires massive code and database changes; but you survive. Later you had the same issue with addresses (people have different addresses - work, home, vacation, etc.) and phone numbers (cell phone, home phone, work phone, assistant's phone, etc.). So you decide to break them into different tables as well. Again, code and database changes. But the changes did not stop there. You had to add various tables to record hobbies, associates, weights, dates of birth - the list is endless. Every thing you record requires a database change and a code change. But worse - not all the tables will be populated for every customer. In your company's quest to build a 360 degree view of the customer, you collect some information; but there is no guarantee that all the data points will be gathered. you are left with sparse tables. Now, suddenly, someone says there is yet another attribute required for the customer - professional associations. So, off you go - build yet another table, followed by change control, code changes to incorporate that.

If you look at the scenario above, you will find that the real issue is trying force a structure around a dataset that inherently unstructured - akin to a square peg in a round hole. The lack of structure of the data is what makes it agile and useful; but the lack of structure is also what makes it difficult in a relational database that demands structure. This is the primary issue if you wan tto capture social media data - Twitter feeds, Facebook updates, LinkedIn updates and Pinterest posts. It's impossible to predict in advance, at least accurately, the exact information you will expect to see in them. So, putting a structure around the data storage not only makes life difficult for everyone - the DBAs will constantly need to alter the structures and the developers/designers will constantly wait for the structure to be in the form they want - slowing down capture and analysis of data.

So, what is the solution. If you think about it, think about how we - human beings - process information. Do we parse information in form of rows in some table? Hardly. We process and store information by associations. For instance, let's say I have a friend John. I probably have nuggets of information like this:

#fff2cc;">Last Name = Smith

#fff2cc;">Lives at = 13 Main St, Anytown, USA

#fff2cc;">Age = 40

#fff2cc;">Birth Day = June 7th

#fff2cc;">Wife = Jane

#fff2cc;">Child = Jill

#fff2cc;">Jill goes to school = Top Notch Academy

#fff2cc;">Jill is in Grade = 3

... and so on. Suppose I meet another person - Martha- who tells me that her child also goes to Grade 3 in Top Notch Academy. My brain probably goes through a sequence like this:

#fce5cd;">Search for "Top Notch Academy"

#fce5cd;"> Found it. It's Jill

#fce5cd;"> Search for Jill.

#fce5cd;">   Found it. She is child of John

#fce5cd;">   Who is John's wife?

#fce5cd;">   Found it. It's Jane.

#fce5cd;">     Where do John and Jill live? ...

And finally, after this processing is all over, I say to Martha as a part of the coversation - "What a coincidence! Jill - the daughter of my friends John and Jane Smith goes there are well. Do you know them?". "Yes, I do," replies Martha. "In fact they are in the same class, that of Mrs Gillen-Heller".

Immediately my brain processed this new piece of information and filed the data as:

#fff2cc;">Jill's Teacher = Mrs. Gillen-Heller

#fff2cc;">Jane's Friend = Martha

#fff2cc;">Martha's Child Goes to = ...

Months later, I meet with Jane and mention to her that I met Martha whose child went to Mrs. Gillen-Heller's class - the same one as Jill. "Glad you met Martha,", Jane says. "Oh, Jill is no longer in that class. Now she is in Mr. Fallmeister's class."

Aha! My brain probably stored that information as:

#fff2cc;">Jill's former teacher = Mrs. Gillen-Heller

And it updated the already stored information:

#fff2cc;">Jill's Teacher = Mr. Fallmeister

This is called storing by a name=value pair. You see I stored the information as a pair of property and it value. As information goes on, I keep adding more and more pairs. When I need to retrieve information, I just get the proper property and by associations, I get all the data I need. But the storing of data by name=value pairs gives me enormous flexibility in storing all kinds of information without modifying any data structures I may currently have.

This is also how the Big Data is tamed for processing. Since the data coming of Twitter, Facebook, LinkedIn, Pinterest, etc. is impossible to categorize in advance, it will be practically impossible to put it all in the relational format. Therefore, a name=value pair type storage is the logical step in compiling and collating the data. the name is also known as "key"; so the model is sometimes called key-value pair. The value doesn't have to have a datatype. In fact it's probably a BLOB; so anything can go in there - booking amount, birth dates, comments, XML documents, pictures, audio and even movies. It provides an immense flexibility in capturing the information that is inherently unstructured.

NoSQL Database

Now that you know about name value pairs, the next logical question you may have is - how do we store these? Our thoughts about databases are typically colored by our long-standing association with relational databases, making them almost synonymous with the concept of database. Before relational databases were there, even as a concept, big machines called mainframes ruled the earth. The databases inside them were stored in hierarchical format. One such database from IBM was IMS/DB, which was hierarchical. Later, when relational databases were up and coming, another type of database concept - called a network database - was developed to compete against it. An example of that category was IDMS (now owned by Computer Associates) developed for mainframes. The point is, relational databases were not the answer to all the questions then; and it is clear that they are not now either.

This leads to the development a different type of database technologies based on the the key-value model. Relational database systems are queried by SQL language, which I am sure is familiar to almost anyone reading this blog post. SQL is a set-oriented language - it operates on sets of data. In the key-value pair mode, however, that does not work anymore. Therefore these key-value databases are usually known as NoSQL, to separate them from the relational SQL-based counterparts. Since their introduction, some NoSQL databases actually support SQL, which is why "NoSQL" is not a correct term anymore. Therefore sometimes it is referred to as "Not only SQL" databases. But the point is that their structure is not dependent on relational. But how exactly the data is stored is usually left to the implementer. Some examples are MongoDB, Dynamo, Big Table (from Google) etc.

I would stress here that almost any type of non-relational database can be classified as NoSQL; not just the name-value pair models. For instance, Object Store, an object database is also NoSQL. But for this blog post, I am assuming only key-value pair database as the NoSQL one.

Map/Reduce

Let's summarize what we have learned so far:

1. The key-value pair model in databases offer flexibility in data storage without the need for a predefined table structure
2. The data can be distributed across many machines where they are independently processed and then collated.

When the system gets a large chunk of data, e.g. a Facebook feed, the first task is to break it down to these keys and their corresponding values. After that the values may be collated for a summary result. The process of dividing the raw data into meaningful key-value pairs is known as "mapping". Later combining the values to form summaries, or just eliminating the noise from the data to extract meaningful information is known as "reducing". For instance you see "Name" and "Customer Name" in the keys. They mean the same thing; so you reduce them to a single key value - "Name". These are almost always used together; hence the operation is known as Map/Reduce.

Here is a very rudimentary but practical example of Map/Reduce. Suppose you get Facebook feeds and you are expected to find out the total of likes for our company's recent post. Facebook feed comes in the form of a massive dataset. The first task is to divide that among many servers - a principle described earlier to make the process scale well. Once the dataset is divided, each machine run some code to extract and collate the information and then present the data to some central coordinator. to collate for the final time. Here is a pseudo-code for the process for each server doing the processing on a subset of data:

begin
  get post
  while (there_are_remaining_posts) loop
     extract status of "like" for the specific post
     if status = "like" then 
         like_count := like_count + 1
     else
         no_comment := no_comment + 1
     end if
  end loop
end

Let's name this program counter(). Counter runs on all the servers, which are called Nodes. As shown in the figure, there are three nodes. The raw dataset is  divided into three sub-datasets which are then fed to each of the three Nodes. A copy of the subdataset is kept another server as well. That takes care of redundancy. Each node perform their computation, send their results to an intermediate result set where they are collated.

Map/Reduce Processing

How does this help? It does; in many ways. Let's see:

(1) First, since the data is stored in chunks and the copy of a chunk is stored in a different node, there is built-in redundancy. There is no need to protect the data being fed since there is a copy available elsewhere.
(2) Second, since the data is available elsewhere, if a node fails, all it needs to done is that some other nodes will pick up the slack. There is no need to reshuffle or restart the job.
(3) Third, since the nodes all perform task independently, when the datasize becomes larger, all you have to do is to add a new node. Now the data will be divided four ways instead of three and so will be processing load.

This is very similar to parallel query processes in Oracle Databases, with PQ servers being analogous to nodes.

There are two very important points to note here:

(1) The subset of data each node gets is not needed to be viewed by all the nodes. Each node gets its own set of data to be processed. A copy of the subset is maintained in a different node - making the simultaneous access to the data unnecessary. This means you can have the data in a local storage; not in expensive SANs. This is not only brings cost significantly down but may performs better as well due to a local access. As cost of Solid State Devices and flash-based storage plummets, it could also mean the that the storage cost per performance will be even better.
(2) The nodes need not be super fast. A relatively simple commodity class server is enough for the processing as opposed to a large server. Typically servers are priced for their use, e.g. a Enterprise class server with 32 CPUs is probably roughly equivalent in performance to eight 4-CPU blades. But the cost of the former is way more than 8 times the cost of the blade server. This model takes advantage of the cheaper computers by scaling horizontally; not vertically.

Hadoop

Now that you know how the processing data in parallel and using a concept called Map/Reduce allows you to shove in several compute intensive applications to dissect large amounts of data, you will often wonder - there are a lot of moving parts to be taken care of just to empower this process. In a monolithic server environment you just have to kick off multiple copies of the program. The Operating System does the job of scheduling these programs on the available CPUs, taking them off the CPU (paging) to roll in another process, prevent processes from corrupting each others' memory, etc. Now that these processes are occurring on multiple computers, there has to be all these manual processes to make sure they work. For instance, in this model you have to ensure that the jobs are split between the nodes reasonably equally, the dataset is split equitably, the queue for feeding data and getting data back from the Map/Reduce jobs are properly maintained, the jobs fail over in case of node failure, so on. In short, you need an operating system of operating systems to manage all these nodes as a monolithic processor.

What would you do if this operating procedures were already defined for you? Well, that would make things really easy, won't it? you can then focus on what you are good at - developing the procedures to slice and dice the data and derive intelligence from it. This "procedure", or the framework is available, and it is called Hadoop. It's an open source offering, similar to Mozilla and Linux; no single company has exclusive ownership of it. However, many companies have adopted it and evolved it into their offerings, similar to Linux distributions such as Red Hat, SuSe and Oracle Enterprise Linux. Some of those companies are Cloudera, Hortonworks, IBM, etc. Oracle does not have a Hadoop distribution. Instead it licenses the one from Cloudera for its own Big Data Appliance. The Hadoop framework runs on all the nodes of the cluster of nodes and acts as the coordinator.

A very important point to note here that Hadoop is just a framework; not the actual program that performs Map/Reduce. Compare that to the operating system analogy; an OS like Windows does not offer a spreadsheet. You will need to either develop or buy an off the shelf product such as Excel to have that functionality. Similarly, Hadoop offers a platform to run the Map/Reduce programs that you develop and you put that logic in the code what you "map" and how you "reduce".

Remember another important advantage you had seen in this model earlier - the ability to replicate data between multiple nodes so that the failure of a single node does not cause the processing to be abandoned. This is offered through a new type of filesystem called Hadoop Distributed Filesystem (HDFS). HDFS, which is a distributed (and not a clustered) filesystem, by default has 3 copies of data on three different nodes - two on the same rack and the third on a different rack. The nodes communicate to each other using a HDFS- specific protocol that is built on TCP/IP. The nodes are aware of the data present on the other nodes, which is precisely what allows Hadoop job scheduler to divide the work among the nodes. Oh, by the way, HDFS is not absolutely required for Hadoop; but as you can see, HDFS is the only way for Hadoop to know which node has what data for smart job scheduling. Without it, the division of labor will not be as efficient.

Hive

Now that you learned how Hadoop fills a major void for computations on a massive dataset, you can't help but see the significance for datawarehouses where massive datasets are common. Also common are jobs that churn through this data. However, there is a little challenge. Remember, NoSQL databases mentioned earlier? That means they do not support SQL. To get the data you have to write a program using the APIs the vendor supplies. This may reek of COBOL programs of yesteryear where you had to write a program to get the data out, making it inefficient and highly programmer driven (although it did strut up job-security, especially during the Y2K transition times). The inefficacy of the system gave rise to 4th Generation Languages like SQL, which brought the power of queries to common users, ripping the power away from programers. In other words, it brought the data and its users closer, reducing the role the middleman significantly. In datawarehouses, it was especially true since the power users issued queries to after getting the result from the previous queries. It was like a conversation - ask a question, get the answer, formulate your next question - and so on. If the conversation were dependent on writing programs, it would have been impossible to be effective.

With that in mind, consider the implications of lack of SQL in these databases highly suitable for datawarehouses. This requirement to write a program to get the data everytime would have taken it straight to the COBOL-days. Well, not to worry, Hadoop has another product that allows a SQL-like language called HiveQL. Just as users could query relational databases with SQL very quickly, HiveQL allows users to get the data for analytical processing directly. It was initially developed at Facebook.

Comparing to Oracle RAC

When we talk about clustering in Hadoop, you may not help wonder - shouldn't the same functionality be provided by Oracle Real Application Cluster? Well, the short answer is - a big resounding NO. Oracle RAC combines the power of multiple nodes in the cluster which communicate with one another and transfer data (cache fusion); but that's where the similarity ends. The biggest difference is the way the datasets are accessed. In RAC, the datasets are common, i.e. they must be visible to all the nodes of the cluster. In Hadoop, the datasets are specific to the individual nodes, which allows them to be local. The filesystems in RAC can't be local; they have to be clustered or available globally, either by a clustered filesystem, shared volumes, clustered volume managers (such as ASM)  or by NFS mounting. In Hadoop the local files are replicated to other nodes, which means there is no reason to create a RAID lever protection at the storage level. ASM does provide a software level mirroring, which may sound similar to Hadoop's replication; but remember, ASM's mirrors are not node specific. The mirrored copies of ASM must be visible to all the nodes. There is a preferred node concept in ASM; but that simply means that data is read by a specific node from one mirror copy. The mirror copies can't be local; they must be be globally visible.

Besides, Oracle RAC if for a relational database. The Hadoop cluster is not one. There are traits such as transnational integrity, multiple concurrent writes that are innate features of any modern database system. In Hadoop, these things are not present and not really necessarily. So while both are technically databases, a comparison may not be fair to either Hadoop or RAC. They are like apples and tomatoes. (Tomato is technically a fruit, just in case you are wondering about this analogy).

The Players

So, who are the players for this new branch of data processing? The major players are show below with small description. This list is by no means exhaustive. It simply is a collection of companies and products I have studied.

• Cloudera - they have their distribution called, what else, Cloudera Distribution for Hadoop (CDH). But perhaps the most impressive from them is Impala - a real-time SQL like interface to query data from the Hadoop cluster.
• Hortonworks - may of the folks who founded this company came from Google and Yahoo! where they built or added to the building blocks of Hadoop
• IBM - they have a suite called Big Insights which has their distribution of Hadoop. This is one of the very few companies who offer both the hardware and software. the most impressive feature from IBM is a product called Streams that can mine data frm a non-structured stream like Facebook in realtime and send alerts and data feeds to other systems.
• Dell - like IBM they also have a hardware/software combination running a licensed version of Cloudera, along with Pentaho Business Analytics
• EMC
• MapR

Conclusion

If the buzzing of the buzz-words surrounding any new technology annoy you and all you get is tons of websites on the topic but not a small consolidated compilation of terms, you are just like me. I was frustrated by the lack of information in a digestible form on these buzzwords that are too important to ignore but would take too much time to understand fully. This is my small effort to bridge that gap and get you going on your quest for more information. If you have 100's or 1000's of questions after reading this, I would congratulate myself - that is precisely what my objective was. For instance how HiveQL differs from SQL or how Map/Reduce jobs are written - these are questions that should be flying in your mind now. My future posts will cover them and some more topics like HBase, Zookeeper, etc. that will unravel the mysteries around the technology that is going to be commonplace in the very near future.

Welcome aboard. I wish you luck in learning. As always, your feedback will be highly appreciated.

An interesting little problem appeared on the Oracle-L mailing list earlier on this week – a query ran fairly quickly when statistics hadn’t been collected on the tables, but then ran rather slowly after stats collection even though the plan hadn’t changed, and the tkprof results were there to prove the point. Here are the two outputs (edited slightly for width – the original showed three sets of row stats, the 1st, avg and max, but since the query had only been run once the three columns showed the same results in each case):

 Rows (max)  Row Source Operation
 ----------  ---------------------------------------------------
          0  UPDATE  CXT_FAKT_PROVISIONSBUCHUNG (cr=2039813 pr=3010 pw=0 time=47745718 us)
      15456   TABLE ACCESS FULL CXT_FAKT_PROVISIONSBUCHUNG (cr=1328 pr=1325 pw=0 time=40734 us cost=370 size=880992 card=15456)
      11225   VIEW  CXV_HAUPT_VU_SPARTE (cr=2038477 pr=1684 pw=0 time=47297497 us cost=10 size=4293 card=1)
      11225    SORT UNIQUE (cr=2038477 pr=1684 pw=0 time=47284436 us cost=10 size=824 card=1)
     126457     VIEW  (cr=2038167 pr=1669 pw=0 time=27753402 us cost=9 size=824 card=1)
     126457      WINDOW SORT (cr=2038167 pr=1669 pw=0 time=27667835 us cost=9 size=853 card=1)
     126457       WINDOW SORT (cr=2038167 pr=1669 pw=0 time=26884699 us cost=9 size=853 card=1)
     126457        NESTED LOOPS  (cr=2038167 pr=1669 pw=0 time=26342292 us)
     126457         NESTED LOOPS  (cr=1995241 pr=1615 pw=0 time=26192173 us cost=7 size=853 card=1)
     141581          NESTED LOOPS  (cr=642683 pr=1066 pw=0 time=3039331 us cost=5 size=499 card=1)
      11225           TABLE ACCESS BY INDEX ROWID POP_INFO (cr=22473 pr=32 pw=0 time=109767 us cost=2 size=16 card=1)
      11225            INDEX UNIQUE SCAN PK_POP_INFO (cr=11248 pr=4 pw=0 time=51796 us cost=1 size=0 card=1)(object id 1790009)
     141581           TABLE ACCESS BY INDEX ROWID TMP_VU_SPARTE (cr=620210 pr=1034 pw=0 time=2889850 us cost=3 size=483 card=1)
    1732978            INDEX RANGE SCAN IDX_TMP_VU_SPARTE (cr=140952 pr=204 pw=0 time=2094982 us cost=2 size=0 card=1)(object id 1795724)
     126457          INDEX RANGE SCAN IDX_TMP_VU_SPARTE (cr=1352558 pr=549 pw=0 time=23078816 us cost=1 size=0 card=1)(object id 1795724)
     126457         TABLE ACCESS BY INDEX ROWID TMP_VU_SPARTE (cr=42926 pr=54 pw=0 time=94791 us cost=2 size=354 card=1)

 Rows (max)  Row Source Operation
 ----------  ---------------------------------------------------
          0  UPDATE  CXT_FAKT_PROVISIONSBUCHUNG (cr=89894995 pr=1701 pw=0 time=318766975 us)
      15456   TABLE ACCESS FULL CXT_FAKT_PROVISIONSBUCHUNG (cr=1328 pr=1031 pw=0 time=46975 us cost=370 size=880992 card=15456)
      11225   VIEW  CXV_HAUPT_VU_SPARTE (cr=89893656 pr=670 pw=0 time=1553653734 us cost=11 size=4293 card=1)
      11225    SORT UNIQUE (cr=89893656 pr=670 pw=0 time=1553640071 us cost=11 size=419 card=1)
     126457     VIEW  (cr=89893656 pr=670 pw=0 time=1533733864 us cost=10 size=419 card=1)
     126457      WINDOW SORT (cr=89893656 pr=670 pw=0 time=1533646166 us cost=10 size=155 card=1)
     126457       WINDOW SORT (cr=89893656 pr=670 pw=0 time=1532847028 us cost=10 size=155 card=1)
     126457        NESTED LOOPS  (cr=89893656 pr=670 pw=0 time=1532238656 us)
    3501658         NESTED LOOPS  (cr=89167767 pr=665 pw=0 time=1529652013 us cost=8 size=155 card=1)
    5300707          NESTED LOOPS  (cr=1339312 pr=480 pw=0 time=9657987 us cost=5 size=81 card=1)
      11225           TABLE ACCESS BY INDEX ROWID POP_INFO (cr=22473 pr=32 pw=0 time=119070 us cost=2 size=16 card=1)
      11225            INDEX UNIQUE SCAN PK_POP_INFO (cr=11248 pr=3 pw=0 time=54707 us cost=1 size=0 card=1)(object id 1790009)
    5300707           TABLE ACCESS BY INDEX ROWID TMP_VU_SPARTE (cr=1316839 pr=448 pw=0 time=8359987 us cost=3 size=65 card=1)
    5300707            INDEX RANGE SCAN IDX_TMP_VU_SPARTE (cr=140971 pr=87 pw=0 time=3603882 us cost=2 size=0 card=1)(object id 1795724)
    3501658          INDEX RANGE SCAN IDX_TMP_VU_SPARTE (cr=87828455 pr=185 pw=0 time=1518016475 us cost=2 size=0 card=1)(object id 1795724)
     126457         TABLE ACCESS BY INDEX ROWID TMP_VU_SPARTE (cr=725889 pr=5 pw=0 time=1829196 us cost=3 size=74 card=1)

As you can see, the first run took 48 seconds (time=47,745,718 us in the first line) while the second execution took 319 seconds (time=318766975 us). If you check the execution plans carefully they appear to be the same plan and, in fact, the trace file showed that the two plans had the same plan hash value. Clearly, though, they do vastly different amounts of work – the most eye-catching detail, perhaps, is the way the bad plan blows the row count up to 5 million before collapsing it back to 126,000). What do you have to do to get the change in performance (and it’s a totally reproducible change) – create or drop stats: if you have stats on the tables you get a slow execution, if you delete the stats you get the fast execution.

So what’s the problem ? Look carefully at the plan(s) – they’re not actually the same plan, but you can’t see the difference you can only see clues that they must be different. Notice in the last four lines that you access the same table (TMP_VU_SPARTE) twice using the same index (IDX_TMP_VU_SPARTE) – when you collect stats you access the two tables in the opposite order, and it makes a difference to the work you do.

To demonstrate the point I’ve created a simplified model of the problem, based on some extra information supplied in the mail thread. The model requires a correlated update, based on a view which joins a table to itself, and range-based predicates. Here’s the data generation:

execute dbms_random.seed(0)

create table t1
as
with generator as (
	select	--+ materialize
		rownum id
	from dual
	connect by
		level <= 1e4
)
select
	trunc(dbms_random.value(0,1e4)) contract,
	sysdate - 300 + trunc(dbms_random.value(0,300))	date_from,
	sysdate - 300 + trunc(dbms_random.value(0,300))	date_to,
	sysdate - 300 + trunc(dbms_random.value(0,300))	created,
	sysdate - 300 + trunc(dbms_random.value(0,300))	replaced
from
	generator	v1,
	generator	v2
where
	rownum <= 1e5
;

create index t1_i1 on t1(contract, date_from, date_to);

create or replace view v1
as
select
	t1a.*
from
	t1	t1a,
	t1	t1b
where
	t1b.contract    = t1a.contract
and	t1b.date_from  <= t1a.date_from 
and	t1b.date_to    >  t1a.date_from
and	t1b.created    <  t1a.replaced 
and	t1b.replaced   >  t1a.created
;

rem
rem	We are going to update t2 from v1 using
rem	equality on all columns in the index
rem

create table t2
as
with generator as (
	select	--+ materialize
		rownum id
	from dual
	connect by
		level <= 1e4
)
select
	trunc(dbms_random.value(0,1e4)) 		contract,
	sysdate - 300 + trunc(dbms_random.value(0,300))	date_from,
	sysdate - 300 + trunc(dbms_random.value(0,300))	date_to,
	sysdate - 300 + trunc(dbms_random.value(0,300))	replaced
from
	generator	v1,
	generator	v2
where
	rownum <= 1e5 ; 

begin 	dbms_stats.gather_table_stats(
 		ownname		 => user,
		tabname		 =>'T1',
		method_opt	 =>'for all columns size 1'
	);

	dbms_stats.gather_table_stats(
		ownname		 => user,
		tabname		 =>'T2',
		method_opt	 =>'for all columns size 1'
	);

end;
/

I haven’t crafted my data particularly carefully and from the point of view of realism the content is a little bizarre (I’ve got “to” dates earlier than “from” dates, for example) – but all I’m interested in is getting the right sort of shape to demonstrate a point about the plan, I’m not trying to model the actual variation in activity.

I’ve created a view with a self-join that starts with equality on first column of the index I’ve created, but uses range-based predicates on the other columns in the table – that’s probably quite important as far as the real-world performance was concerned – partly because of the nature of the data (interesting skews when comparing “valid to/from dates”) and partly because Oracle is going to have to use its 5% guess for join selectivities for range based selectivities.

And now the update – two versions with their execution plans; starting with the plan where the correlated subquery visits t1 aliased as t1a first:

explain plan for
update t2 set
	replaced = (
		select
			/*+ leading(v1.t1a v1.t1b) */
			max(replaced)
		from	v1
		where
			v1.contract = t2.contract
		and	v1.date_from = t2.date_from
		and	v1.date_to = t2.date_to
	)
;

select * from table(dbms_xplan.display(null,null,'alias'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
Plan hash value: 2328412027

-----------------------------------------------------------------------------------------
| Id  | Operation                       | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT                |       |   100K|  2734K|   500K (20)| 00:41:41 |
|   1 |  UPDATE                         | T2    |       |       |            |          |
|   2 |   TABLE ACCESS FULL             | T2    |   100K|  2734K|    64   (8)| 00:00:01 |
|   3 |   SORT AGGREGATE                |       |     1 |    72 |            |          |
|   4 |    NESTED LOOPS                 |       |       |       |            |          |
|   5 |     NESTED LOOPS                |       |     1 |    72 |     4   (0)| 00:00:01 |
|   6 |      TABLE ACCESS BY INDEX ROWID| T1    |     1 |    36 |     2   (0)| 00:00:01 |
|*  7 |       INDEX RANGE SCAN          | T1_I1 |     1 |       |     1   (0)| 00:00:01 |
|*  8 |      INDEX RANGE SCAN           | T1_I1 |     1 |       |     1   (0)| 00:00:01 |
|*  9 |     TABLE ACCESS BY INDEX ROWID | T1    |     1 |    36 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - UPD$1
   2 - UPD$1        / T2@UPD$1
   3 - SEL$F5BB74E1
   6 - SEL$F5BB74E1 / T1A@SEL$2
   7 - SEL$F5BB74E1 / T1A@SEL$2
   8 - SEL$F5BB74E1 / T1B@SEL$2
   9 - SEL$F5BB74E1 / T1B@SEL$2

Predicate Information (identified by operation id):
---------------------------------------------------
   7 - access("T1A"."CONTRACT"=:B1 AND "T1A"."DATE_FROM"=:B2 AND
              "T1A"."DATE_TO"=:B3)
   8 - access("T1B"."CONTRACT"=:B1 AND "T1B"."DATE_TO">:B2 AND
              "T1B"."DATE_FROM"<=:B3)
       filter("T1B"."DATE_TO">:B1 AND "T1B"."CONTRACT"="T1A"."CONTRACT" AND
              "T1B"."DATE_FROM"<="T1A"."DATE_FROM" AND "T1B"."DATE_TO">"T1A"."DATE_FROM")
   9 - filter("T1B"."CREATED"<"T1A"."REPLACED" AND 
              "T1B"."REPLACED">"T1A"."CREATED")

Notice that there’s nothing in the body of the plan that tells you which copy of t1 is visited first – you can’t tell unless you look carefully at the predicate information, or unless you’ve requested the alias section that lets you see very easily that the t1 at line 6 (the first one accessed) is aliased as t1a and the t1 at line 9 is aliased as t1b. While you’re at it, check the plan hash value from the top of the plan output.

Now the plan when we hint the two tables into the reverse order:

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
Plan hash value: 2328412027

-----------------------------------------------------------------------------------------
| Id  | Operation                       | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT                |       |   100K|  2734K|   600K (17)| 00:50:01 |
|   1 |  UPDATE                         | T2    |       |       |            |          |
|   2 |   TABLE ACCESS FULL             | T2    |   100K|  2734K|    64   (8)| 00:00:01 |
|   3 |   SORT AGGREGATE                |       |     1 |    72 |            |          |
|   4 |    NESTED LOOPS                 |       |       |       |            |          |
|   5 |     NESTED LOOPS                |       |     1 |    72 |     5   (0)| 00:00:01 |
|   6 |      TABLE ACCESS BY INDEX ROWID| T1    |     1 |    36 |     3   (0)| 00:00:01 |
|*  7 |       INDEX RANGE SCAN          | T1_I1 |     1 |       |     2   (0)| 00:00:01 |
|*  8 |      INDEX RANGE SCAN           | T1_I1 |     1 |       |     1   (0)| 00:00:01 |
|*  9 |     TABLE ACCESS BY INDEX ROWID | T1    |     1 |    36 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - UPD$1
   2 - UPD$1        / T2@UPD$1
   3 - SEL$F5BB74E1
   6 - SEL$F5BB74E1 / T1B@SEL$2
   7 - SEL$F5BB74E1 / T1B@SEL$2
   8 - SEL$F5BB74E1 / T1A@SEL$2
   9 - SEL$F5BB74E1 / T1A@SEL$2

Predicate Information (identified by operation id):
---------------------------------------------------
   7 - access("T1B"."CONTRACT"=:B1 AND "T1B"."DATE_TO">:B2 AND
              "T1B"."DATE_FROM"<=:B3) 
       filter("T1B"."DATE_TO">:B1)
   8 - access("T1B"."CONTRACT"="T1A"."CONTRACT" AND "T1A"."DATE_FROM"=:B1 AND
              "T1A"."DATE_TO"=:B2)
       filter("T1A"."CONTRACT"=:B1 AND "T1B"."DATE_FROM"<="T1A"."DATE_FROM" AND 
              "T1B"."DATE_TO">"T1A"."DATE_FROM")
   9 - filter("T1B"."CREATED"<"T1A"."REPLACED" AND 
              "T1B"."REPLACED">"T1A"."CREATED")

The plan hash value is the same – but if you look at the alias section you can see that the t1 we access first is the one with alias t1b.
Now compare the predicate sections, just for line 7 (the initial index access line):

First Plan
   7 - access("T1A"."CONTRACT"=:B1 AND "T1A"."DATE_FROM"=:B2 AND "T1A"."DATE_TO"=:B3)

Second Plan
   7 - access("T1B"."CONTRACT"=:B1 AND "T1B"."DATE_TO">:B2 AND "T1B"."DATE_FROM"<=:B3) 
       filter("T1B"."DATE_TO">:B1)

Although my model data is random garbage, it’s easy to imagine that with a large data set the selectivities of these two predicates could be dramatically different, and there is clearly some “real-world” meaning to the date_to/date_from columns for a given row that the optimizer is unlikely to recognise when looking at the individual column stats for the table. It’s not surprising that a plan with no stats (which results in dynamic sampling) could find a better plan than a table with stats that leaves the optimizer using its default “call it 5% and hope for the best” strategy for range-based joins.

Conclusion

When you reference a table more than once in an execution plan, make sure you look very carefully at the predicate section – or even call for the alias section – so that you know exactly which copy of the table appears at which point in the plan.

Footnote

Although the results don’t mean much for my example, here’s my output from tracing my queries – rather than report the whole query in each case, I’ve just given the hint to show the table order:

/*+ leading(v1.t1a v1.t1b) */

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      6.01       0.00          0     200722     204119      100000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      6.01       0.00          0     200722     204119      100000

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         0          0          0  UPDATE  T2 (cr=200773 pr=0 pw=0 time=0 us)
    100000     100000     100000   TABLE ACCESS FULL T2 (cr=459 pr=0 pw=0 time=0 us cost=64 size=2800000 card=100000)
    100000     100000     100000   SORT AGGREGATE (cr=200248 pr=0 pw=0 time=0 us)
         0          0          0    NESTED LOOPS  (cr=200248 pr=0 pw=0 time=0 us)
         0          0          0     NESTED LOOPS  (cr=200248 pr=0 pw=0 time=0 us cost=4 size=72 card=1)
         0          0          0      TABLE ACCESS BY INDEX ROWID T1 (cr=200248 pr=0 pw=0 time=0 us cost=2 size=36 card=1)
         0          0          0       INDEX RANGE SCAN T1_I1 (cr=200248 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 81082)
         0          0          0      INDEX RANGE SCAN T1_I1 (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 81082)
         0          0          0     TABLE ACCESS BY INDEX ROWID T1 (cr=0 pr=0 pw=0 time=0 us cost=2 size=36 card=1)

/*+ leading(v1.t1b v1.t1a) */

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      6.09       0.00          0     613372     200000      100000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      6.09       0.00          0     613372     200000      100000

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         0          0          0  UPDATE  T2 (cr=613372 pr=0 pw=0 time=1 us)
    100000     100000     100000   TABLE ACCESS FULL T2 (cr=459 pr=0 pw=0 time=0 us cost=64 size=2800000 card=100000)
    100000     100000     100000   SORT AGGREGATE (cr=612913 pr=0 pw=0 time=1 us)
         0          0          0    NESTED LOOPS  (cr=612913 pr=0 pw=0 time=1 us)
         0          0          0     NESTED LOOPS  (cr=612913 pr=0 pw=0 time=1 us cost=5 size=72 card=1)
    166078     166078     166078      TABLE ACCESS BY INDEX ROWID T1 (cr=368051 pr=0 pw=0 time=0 us cost=3 size=36 card=1)
    166078     166078     166078       INDEX RANGE SCAN T1_I1 (cr=202108 pr=0 pw=0 time=0 us cost=2 size=0 card=1)(object id 81082)
         0          0          0      INDEX RANGE SCAN T1_I1 (cr=244862 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 81082)
         0          0          0     TABLE ACCESS BY INDEX ROWID T1 (cr=0 pr=0 pw=0 time=0 us cost=2 size=36 card=1)

There isn’t a lot of difference in the run-time (which is mostly due to the tablescan) – but there’s an obvious difference in the number of buffer visits and the amount of data found for the first t1 access.
Just like the OP my plan varied with stats – though in my case I got the better plan when I had stats, and the worse plan when I deleted stats and the optimizer used dynamic sampling.