[intro]
[music]
In a previous demo, I showed you the basics of
just step one of learning to learn about set based programming
and separate based programming techniques. And this was using an array
just to insert some data versus using
a set of independent Java threads all loading one row at time
and these were experiencing contention within the index or they're all
contending because they were all committing at the same time.
But let's take this
idea of set-base programming a little bit further and start thinking about what we
do with large data sets.
And we're gonna break it down into four blocks. We're gonna load
think about loading data because we all have the
requirements to ingest or load or insert
a large amount of data into the database at speed.
How will we best do that? Then we're gonna start thinking about
validating data. We get data from all disparate data sources. How do we
validate that is good data?
It's a unique easy unique, foreign keys a
foreign keys, orphan rows and things like that so we have
a means validating to see that the data is
correct from integrity point of view. The next level is
we'll likely to want to transform and modify data
because it probably isn't perfect or it came from lots of disparate systems that
may have
anomalies in the data like the tax code
the dates, formatting, and the time zones appropriate
different calendar systems, and things like that. And then we'll look to
aggregate data potentially for pushing on to smaller data mart systems
or just to provide easier querying on the data warehouse
for modelers who are quite happy working with aggregates. So we can see that we've
got this sort of pipeline of data
disciplines that we have to learn how to do with large datasets.
And what we're gonna do show through and we are going to pop up some code snippets in
some ways to show how to do it.
But we're gonna do follow the same techniques as we showed in the previous
demo is that are going to start racing these different programming techniques against
each other. They all got the same amount of hardware.
They all got the same data, but some just take longer than others
and it's all in the programming techniques that are adopted.
Some of the techniques will only ever use a single CPU. Some of them legally use
multiple
some of them use multiple CPUs more efficiently than
others. And these are the things we need to learn as we move forward
in this game. All those disciplines we described, let's look at the first one
which is loading.
And to load the data we are gonna use four different techniques. And we're gonna
see those techniques
over and over again. We're going to use a mixture of
row by row processing. We're going to use array techniques
as we showed earlier, and then we we are going to call what we call
home-grown parallelism, and I'm sure you've all seen that in somebody
who has got
a job that is taking too long and they've chop the job in half by doing
two different sets of data or something arbitrary like
even numbers and odd numbers or months of the year, and the segmented data up
and they basically taking this serial programs and running multiple copies on
it on different sets of data.
And then the last one
is going to be true set-based processing where we are able to exploit
core parallelism within the database to achieve both parallelism
and get the work done and and so
you can "bake off" and see which is probably going to be the best technique
for you. So in terms of data loading and
let's go and let's start race of data loading
using the four different techniques. I'm gonna start the thing
and you'll notice very quickly the set-based techniques are pulling
way ahead the other techniques and as you can see the red bar
shooting across the side. And we're going to be
loading I think it's about a hundred and one million rows in this case
and each of the techniques is using an individual node
in a half rack of vector data so they've all got the same amount of hardware
they all have got one node, but let's look at the difference in the performance.
Now the winner was racer number four which was the set-based technique and he
took 22 seconds. The homegrown parallelism technique
they took minute-and-a-half. The array interface technique
which is the first method we showed earlier, took forty
nearly fifteen minutes. And if you done it as a row by row program it would have taken three
hours
43 minutes and five seconds.
Again, we are demonstrating that difference in coding techniques can make
literally
orders of magnitude difference to the performance, and to get things down from
hours to seconds is always interesting because when things come down from
hours to seconds that means you are a thousand times faster
and that is a great objective to have if you are doing real-world performance. If you
make your target
a thousand times faster you actually start
to change the game. You make the business
different. Imagine jobs you are running in the middle of the night
for those taking hours to run. If you get those jobs from
hours to seconds, you are more likely to run them in real time
in phase with the business. You are able to make decisions in real time
and basically you bring performance engineering
is bringing an edge to your business. Instead of having performance
as something that is a problem, it is something you can be proactive on
and make the game a better game. So that was loading.
Well let's talk a little bit about the code techniques that were used
to load all this data. Well it's pretty simple
in it this time. We've coded things up in PL Sequel
but don't let the language distract you
the fact that we did it in PL Sequel just made our lives easier
but you can make all the same performance in half
since if you're coding mechanism is via Java
C++, C, even COBOL, if you are still writing COBOL programs,
the language doesn't matter. It's the algorithms and the techniques you adopt
is the important part to the game. So let's have a look at the row by row code
and as you can see it's a very simple PL Sequel program
basically a cursor loop that is going into a fetch
state and it is taking for every row that you fetch
it's inserting into a table. Pretty simple concept.
And if I had a dollar for every time I saw programs like this in the real world
I would be having a very high performance car in my garage.
So anyway, that's the way it goes. It is very simple
Now if we take it up to the array process, it's not
you won't be surprised to see much more
on that, but we're gonna see the similar sort of code construct
but the programmer in this case has used the PL Sequel
bulk operators to effect
an array insert.
That worked well, but remember you can only use a single core.
It was a lot faster than when you did it row by row. We're talking three and
three-quarters hours
down to quarter of an hour. So that's a big gain and you're just using the same
one CPU. But if you're looking to get this data 101 rows or
1,000,000 rows of data loaded in seconds,
we need to start taking up some parallel techniques.
Now the homegrown parallelism runs a pretty much the same program
but what we've actually made, the homegrown programmer as actually decided
to start cutting up the data
and handing off the work.
So in fact what we're doing is we're scanning the source data multiple times
and we're using the ora_hash function
to figure out how to divide the data
between all the parallel inserting slaves
and by this mechanism we're getting some manual parallelism
and manual diversion of data between query slaves.
And so we run 32 of those jobs all hashing by 32 on ora_hash
such that each slave loads a 32nd of the data.
But again you can see we fundamentally are running the same algorithm
as we ran in the serial program but we starting to take advantage of
chopping the data up, parallelism,
and the array interface--the bulk operators.
But if you wish come down and actually look, and you'll notice that
with all these elaborate techniques, the code seems to be getting more complicated
and more confusing. So let's look at the set-based programs
programming code.
Well that was a bit of a surprise, because we got a lot less code there than
all the other ones put together. Basically a simple
insert with an APPEND hint.
The result of the query from the
extend external table scan events table. This is the least amount
code, yet it is also the fastest.
Now the reasons why it's fast is that it is basically able to take advantage of core
Oracle parallelism to do the inset.
It is also taking advantage of the APPEND hint and
an APPEND hint is a pure set-based technique because it allows
us to do bulk data loading. But it can also be done
in a fast path search for avoiding generation
undue and redo. So we're cutting down the amount of CPU we are burning.
We are cutting out the amount of I/O we are doing, and it becomes an
extremely fast way of loading a large amount of data,
so much in fact that basically a program that was running in three in three
quarter hours
is now running in 22 seconds. And this is just taking advantage of
core Oracle database, set-based techniques
and technology that run in parallel has been designed
to handle large amounts of data.