[intro music]
[theme music]
The final sort of discipline associated with
manipulation of data as a set, would be to produce aggregates
and aggregated structures, um, very often used for querying
for, ah, exporting small data marts or very often within the data warehouse or
for using reporting and those sort of things. So the generation of the aggregation
usually involves
reading a large amount of data potentially joining it to other
structures
and then segmenting, sorting, and grouping
the data according to some of the columns
that we've defined.
This process again takes CPU and
can be either done manually or we can use
you know, core parallelism within the database in multiple ways.
And I'm gonna do the same techniques. I'm going to
run some races and we're gonna bake them all off
the techniques against each other, and you know, we are going to run the race.
And this one a little bit more interesting compared to the other one,
um, in that you'll notice that the
the, um, way things are running
that the home-grown parallelism hasn't
actually done that well. You'll notice that the set-based processing
and took about 10 seconds, whereas an estimated time
for the row-by-row processing of about two and a half hours, and you'll notice
by the time we stop, the home-grown processing hadn't
really achieve much. Now this is one of the challenges you have with
trying to code your own parallelism in. Very often the startup costs,
and figuring out the date ranges and all that sort of stuff, has a fixed overhead
and so you have to to do a certain amount of work before, um,
the work can actually start in earnest to do the work in parallel.
So what would learned on this one is to actually is
to apply a little bit of a delay
such that what we do is we, if we re-run the race,
we'll actually wait 25 seconds before the set-based
parallelism present solution starts work,
and we'll let the others get to work. And you'll notice the homegrown
parallelism initially didn't do anything, and now it's starting to pull
quite a long way ahead, and it's certain
we haven't even started the set-based parallel and because we kept him waiting
25 seconds.
And you can see that the home-grown parallelism was
way ahead of the parallelism and the array processing,
but look set-base processing is starting to catch up and it looks like it is almost tied
nope, but it still wins, even if you can have given it a head start of
25 seconds to all the other techniques.
So again it teaches you another lesson about home-grown parallelism.
There's usually quite high start-up costs or configuration costs.
The characteristics of the system pretty much as I showed in the other cases
where the array and the row-by-row processing which is using a single call,
the home-grown parallelism very often using a lot more CPU, and
getting the machine a lot more busy than the set-based techniques
because is running effectively serial algorithems in parallel
rather than running parallel algorithms, which as you can see as soon as they
start their almost finish because they're over and done with.
So we aggregated a 101 million rows, you can see just happened so
quickly
in 10 seconds by the time it was up to full speed it was time to finish.
Whereas the other techniques as a home-grown parallelism it made the
machine look busy
but it was inefficient program running in parallel and again that's the other
big challenge we face
with people who effectively try to convert serial programs
into multi-threaded parallel programs
they still are not using good set-based mechanics or set-based algorithms
to execute the code, they are just using the inefficient row by row stuff,
it's just happening in parallel which means you're wasting a lot a CPU.
And the other thing we tend to see that is, again, sky high start-up costs
and as a fair chance that they are going to contend with each other. If this was
an operation that was updating common tables, you may find this contention in
those tables or contention
on the indexes on those tables as you manipulate them.
So you can see there are challenges with trying to implement your own parallelism.
So let's just have a quick look at the code and
just to do that. And we'll be very quick through those.
The row-by-row program, if you want to scroll through, it you can see
what it's actually doing is we've written a whole set up procedures
keep running totals inside the PL/SQL program
and an incrementing counters as we go through each
key. And so the counting is being done effectively in the application code
and this is potentially an inefficient way. But we tend
see people doing this sort of thing,
particularly if they're using conventional inserts and they're
worrying about the data sets becoming so big that they're gonna
cause rollback segments to explode and things like that.
So they actually break down the DML components or the inserts, into smaller
chunks,
and so by doing that they basically force themselves into doing running
totals inside their programs.
Very often these are programs that have been converted from old procedural code
that may have been working on flat files
and so the original versions of the programs
didn't even have the concept of relational operators and set-based techniques
in the language, and so we just inherited all these old counters and
basically they've just used the database to grab data and push it back.
I can describe and that is not much
to be seen, and from basically the programming
in the array techniques. Again we are using the array techniques to grab
and push the data away. We've been slightly different on the home-grown
program. Instead of using the ora-hash, well we have used the ora_hash but we design,
we are sending the rows, as we send them back and went pipe lining them through a
table function
to get the parallelism through a table function so that
that basically everything can be computed as a
series of streams, but really the one that's the most
interesting one most important one is a look at the simplicity of the set-based
solution
which is basically an insert of a query
and some GROUP BYs, um, where all the running totals are done for you.
And some of these grouping factions very often you need windowing functions to
segment the data in the form that you need,
but again we're pushing everything down into the core parallel engine
inside the database. And as you can see
there's huge performance gains.
Again it's it's all in, in each case we wrote less code,
it may be argued the code is harder to write
but look at the performance and that the performance of the situation speak for
itself.
We're talking ten seconds versus two hours. This is a huge performance
difference.
And again a good engineer, once they've learned to exploit the set-based
techniques,
and they see the performance gains, they can actually start to
innovate business processes to run
semi real-time, or much faster and get rid of these
ludicrous batch jobs that run for six or seven hours every night
and become really quite stressful for many people, because
if they fail in the middle of the night they may never catch up
the following night. And so, again, set-based techniques
not only are faster in terms of performance, using less system resources, and
more efficient,
but it makes your whole business process more robust,
and less frightening, and take the risk out of manipulating and managing
large data sets.
And it's really a case just understanding large data sets,
take specialist programming techniques, and when applied
effectively they can perform just as well as small data set problems.
It's a case of just learning the right tools to do the right job.