Back

Oracle SQL Tuning Free Tutorial

There are often several ways to get the same answer from your Oracle Database.  But which version of the equivalent SQL query is faster? We need to learn how to tune SQL statements that produce equivalent results.

Listen and watch as Oracle Master John Watson demonstrates why not all equivalent SQL statements are created equal.
John demonstrates how SQL statements that produce equivalent result sets can have radically different response times, why this happens and common solutions.

In this free video tutorial, listen and watch as Oracle Master John Watson demonstrates why equivalent SQL statements are not all created equal when it comes to performance.

John demonstrates how SQL statements that produce equivalent result sets can have radically different response times, why this happens and common tuning solutions.

This free training is segmented into several separate lessons:

  1. Introduction (including First Demonstration) (7:11)
  2. SubQuery Factoring (14:38)
  3. Star Transformations (15:55)
  4. Aggregations (8:16) (click on video below)
  5. Summary (3:31)

Date: Sep 12, 2012

NOTE: Some corporate firewalls will not allow videos hosted by YouTube.

Transcript

Aggregations

Oracle SQL Tuning 

4. Aggregations

[music]

>> John:  The next one I want to look at is aggregations. Aggregations can be a real problem particularly in data warehouse type environments. 

[pause]

Often the problems occur in each of several aggregations. Imagine you want to sum up your sales by customer, by city, and by country. You want to sum them up by day, by month, by year, by product, and by channel, and so on. All of these aggregations may mean a complete pass through the data, and that can be disastrous. If you want aggregate a multiple dimension, it’s multiple passes.

You can get around a lot of this from 9i onwards by using the Group by Rollup and Group by Rollup and Cube functions. I’m not going to talk about them now. Let’s talk about Rollup and Cube another day.

What I do want to show is that multiple aggregations, which people tend to be frightened of and justifiably so are not necessarily about the thing. Not necessarily about thing at all and as long as they’re done the correct way. That’s where – guess what – isn’t that your usual equivalent SQL may mean that aggregations are not a problem?

[pause]

The example I’m going to give, I just need to create a table.

[pause]

I’ll create a table with about 80,000 rows. Did I spell it correctly? That will be one table. I’m constructing a pretty artificial example here. Of course, that will do for the purpose of showing SQL equivalence and the effect of aggregations in execution plans.

I’ll create another table. That was foo. Now I’ll create a table called goo.

[pause]

That gives me two tables with about 80,000 rows each.

[pause]

First query might be – I’m not actually going to run it. I’ll save time now. The explain plan for – let’s say I simply want to count the rows, how do I count the rows of my tables? Count * from select * from foo union all select * from data table.

How many rows do I have in total? I’m not really interested but I’m interested in the execution plan.

[pause]

What did it do? It was able to scan our tables, union, sorted. Take an alternative and equivalent SQL.

[pause]

Let me explain plan for. Select say sum of something. Sum c from. Select count * from foo union all. Select c from our first table union to the second table. I’m sure you can see that’s an equivalent SQL. It’s going to get the same result.    

What execution plan has it come up with? It looks a lot worse, doesn’t it? That first plan looks really, really efficient. Okay. We have to scan the table. We have to count the results. Clearly, how else can you get the results? How else can you get the count?

You could get it this way. But it looks far worse. What I now have is I’ve scanned the table, sure. But then I’ve done a sort and aggregation. Scan the other table. It couldn’t get all that sort and aggregation.

The third one, this looks appalling. There are three sorts, three aggregations. Surely that’s going to be worse. Let’s actually run them and see if it is. If I run the statement – this was my simple statement. I’ll actually run it. It’s taking a few seconds to come through. There come the rows.

[pause]

Looking at the timings for that and how it actually works, there’s what happens as plan is used. And it took 2.5 seconds. Fair enough.

[pause]

Move on then to the other one. I have a variation of it, my equivalent SQL which did three aggregations, not one.

>> Dave:  John?

[pause]

>> John:  Yes?

>> Dave:  A quick question. I think it’s dynamic sampling. The answer, I’ll take a guess here. The question is – do you need to gather stats on these new tables for the optimizer tuning or I’ll have to run these queries correctly?

>> John:  In this particular case, I don’t because it has no choices. There are, in this case, no indexes. It really doesn’t make any difference. It’s interesting to note that the dynamic samplings in fact are not perfect. Those tables are identical. You can see that. The actual rows – the tables are identical. The dynamic sampling rather interestingly didn’t get the precise figures, but that will not be distorting this example. 

[pause]

>> Dave:  Perfect. Thank you, John.

>> John:  Running that one. That was quicker, wasn’t it? Well, that’s harder to prove. But let’s see this more complex one.

The previous one took 2.5 seconds. This took about half a second. The final complex plan with three aggregations in fact was four times as quick. I haven’t expected as good a benefit as that. My more complex plan, the equivalent SQL executed this in a quarter of the time.

If you’re interested in why, the answer is pretty straightforward. It’s because of the number of rows that had been passed back. At this stage I’m passing – as we see in the A-Rows – I’m passing 145,000 rows up through a view to be sorted. In this stage, I’m reducing 72,000 rows to one. So I’m passing a total of two rows back for the final aggregation. That’s why it’s so much faster. But Oracle was simply incapable of understanding that. 
  
What conclusion can we draw from this? Don’t be frightened of aggregations. Don’t be frightened of complex plans. In some cases, you’ll find that a more complex plan can in fact be far faster. Even if [8:08 inaudible] materializing views at various stages.  

Copyright SkillBuilders.com 2017

×
Free Online Registration Required

The tutorial session you want to view requires your registering with us.

It’s fast and easy, and totally FREE.

And best of all, once you are registered, you’ll also have access to all the other 100’s of FREE Video Tutorials we offer!

 

×