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) (click on video below)
  4. Aggregations (8:16)
  5. Summary (3:31)

Date: Sep 12, 2012

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


Star Transformations

Oracle SQL Tuning

3. Star Transformations


>> John:  Star transformations are – I have decided and I really did want when we were planning this little webinar, we really wanted to make sure that we didn’t cover any topics that you have to pay for and you can pay us, but nothing without the licensing implications. The classic star transformation is Enterprise Edition. But one thing I do want to show particularly is not only what a star transformation is in terms of equivalent SQLs but also how you can achieve the star transformation with Standard Edition. That’s what I really want to come down to and it’s down to the way you write your SQL. 


What are the concepts of the star transformation? You rewrite joins as subqueries. 


Trivial example, if I were to select ename from emp and again natural join dept where dname = sales. I want to pull out all the employees in the sales department. What happens? We get six employees back. I can rewrite that as an equivalent SQL which will be select ename from emp where deptno = select deptno from dept where dname = sales. 

That’s exactly an equivalent SQL, we get the same rows back. I can assure you I won’t even bother to prove it. I will go to the cost-based optimizer at this stage and that the optimizer will write those as exactly the same execution plan. Do your own checks if you want, but it’s not going get that rows. It’s not going to get that one wrong. 


As true star transformation takes that a lot further, because what I’ve done in effect here is I’ve got one facts table, a single facts table emp and a single dimension table which is dept. Not many data warehouses have a fact table with one dimension. They have many dimensions. 

What I’ve done to work up an example here is I’ve created a little star schema. What I have is a fact table and two dimension tables. I’ve created little table. I’ve done some advanced set time. I’ve done a little table called products and every product has a code, which is a primary key. I’ve created a table called buyers. Every buyer has a code, ends in a particular country so that’s my two dimensions – the products dimension, the buyer’s dimension, and I’ve got a fact table, sales which says that every sale was with a certain value of a certain products to a certain buyer. So that’s my star schema – a fact table and two dimension tables. 


I’ve already analyzed the tables, by the way, to build different statistics. Optimized statistics are of course critical. 

Star transformation requires bitmap indexes, which is why it needs Enterprise Edition. I’ll just create my bitmap indexes. Create bitmap index pi on sales (prod_code) – and we get my bitmap index. This of course is where we went straight to Enterprise Edition is the moment you create a bitmap index, you need to be Enterprise Edition licensed. This is taking a while by the way because there are a million rows in my sales table. I did want to get some sort of sensible figures out of it. I’ll create another bitmap index on my buyers which I actually bi. 

So I’ve got my bitmap indexes. If I run my query a little star query, first without a star transformation – I’ll now do it with the statistics level high – I’ve still got all set there. As I said, there’s a [4:31 inaudible] you can put in instead, but I’ll just leave that set at the session level which will make life a bit easier. The query I need to run will be a star query that joins the two dimensions across the fact table. 

For example, I could run select sum of my measure which is the column called value – the value in sales, some value from sales and then joining it to one dimension – natural join to products and then natural join to buyers. So I’m going across say the fact table to both dimensions. 

Let me put in the predicates. Say where product = books and country = Germany. So how many sales we had of books to Germany. 


It’s taking a fair few seconds to come up with the answer. 


Back come that answer. That took a while. How did Oracle actually run that? Look at the execution plan. 


Here we see what it did. It’s navigating down, it is a full scan of products applying the filter to get books – that gave you the product code of books. Then it did a full scan of buyers applying the filter which was buyers in Germany. And it’s joined them with Cartesian products and that’s fine because I analyze data. Oracle knows that this isn’t one row in each Cartesian product. 

Then the bad news. It’s done a hash join to sales. What that means is we’ve had to do a million rows as you can see. It has to do a million look ups to retrieve the matching rows. That’s the way Oracle would do it without a star transformation. 

Now we enable star transformations. By the way, I hope you all appreciate how brave I am to do this sort of thing. With these little demonstration systems, the tiniest environmental factor can cause the optimizer to [6:59 inaudible] with a totally different plan. But anyway, alter session set star.

>> Dave:  I’m impressed even if no one else is.

>> John:  Good. Everyone can laugh at me if this sort of thing doesn’t work. 

We set star transformation enabled = true. This parameter defaults to false. I never quite understood why and I can’t think why you ever would want it on false if you’ve bought your Enterprise Edition licenses. Just set it to true and then run the query again. 


Maybe that was quicker. Then let’s see what the execution plan was. Star transformation used. Whew, it works. 

A totally different approach and what it’s done – if you navigate down here, in effect it’s done the rewrite I’ve described earlier. It’s rewritten it to a set of subqueries. What we’re doing is bitmap index range scan of my bitmap index on the product. 


So it’s gone to products first applying the filter books to identify the product code. Then it scanned the bitmap index to get the row IDs of all the sales for books. That in effect is saying select from sales where prod ID in select from products where product = books. 

Then it does the same thing with the buyers. It scans the buyers table to find the code for Germany and then it picks the bitmap index to get the relevant row. At this point it’s got two lists of interesting rows. Rows about sales of books, rows about sales off to Germany and it merges those two bitmaps with an AND, so in effect it’s getting the intersection of the two sets. Then it converts bitmap row IDs and constructs the five results set. Totally different write. The optimizer did that automatically, no need to rewrite the SQL. But you can only do that if you’ve got Enterprise Edition licenses. 

What if you don’t have Enterprise Edition licenses?

>> Dave:  John, before you start that, just to recap this. We need Enterprise Edition but I think you also said that that parameter star transformation enabled is set to be false by default?

>> John:  It defaults to false, yes. A strange default, but that’s the way it is. 

Now what I’ll do is in effect revert to Standard Edition. So I’ve got to get rid of my indexes. 


I drop my index on buyers, drop my index on products because they are now illegal. Let’s see if I can get the results within equivalent SQL. I will get timing at some point, by the way. We want to check the timings. 

Since we’re here my star transformation, for that the actual time is 2.79 seconds, whereas my previous without the star transformation, it was 9 seconds. So star transformation has improved things quite substantially, 9 seconds down to 2 seconds. 

Now I’ll try a manual rewriting of equivalent SQL. I can no longer use star transformations anymore. I don’t even have the indexes to do it. So I’ll select sum value from sales where prod_ID = select prod_code from products where product = books – that’s the first dimension – and buy_code = select buy_code from buyers where country = Germany. Cross my fingers and see what result we get. 


We get a missing right parenthesis. We will see a spelling mistake. 

>> Dave:  Missing a C in the select statement.

>> John:  Thank you. That’s probably the first of many mistakes. It’s not product ID, it’s product code. 


It’s product code and select. And that one looks okay. 


That’s seem reasonably quick, isn’t it? So I’ve got the same results. That really is an equivalent SQL. How is it actually executed and how long did it take? 

We’re going through 9 seconds to 2 seconds with the Enterprise Edition feature. With my rewrite


Good heavens. It was even quicker, barely half a second. So I’ve actually outperformed the optimizer. You can see what it’s done is rewritten the statements completely. 


My equivalent SQL – I did the rewrite has come with a totally different execution plan. What we’re doing now is we’re doing a scan of products to retrieve the interesting code then we’re doing a scan of buyers to then get to the interesting code and from then we could go restore it to sales and apply the appropriate filter which you see here. 

Note by the way, yes, earlier question. Note all three of these operations were off loaded to exadata. But best of all, even the filters are all off loaded to exadata. This entire query in exadata environment will be executed on the storage tier. That’s an unexpected side effect. I wasn’t expecting something quite so dramatic. But that’s a very nice example I think of how the equivalent SQL has outperformed astronomically even Enterprise Edition star transformations because that was done with Standard Edition features. All done with the code.

>> Dave:  Could you do us a favor and show us the equivalent SQL side by side there? The first one you did scroll up top.


>> John: Yes. That was me rewriting it as a star transformation. Now I’m going to scroll up. My original SQL was


There. Select sum value from sales, join products, join buyers where product = – this is intuitive, isn’t it? Many people will think this is much easier to understand, but the results are rubbish compared to my equivalent SQL. 


>> Dave:  Yeah. More and more we see customers are saving tens of thousands of dollars by going to Standard Edition. This is a great technique in the event you don’t have Enterprise Edition. So, John, maybe you better answer this one. Why don’t you use TOAD or PL/SQL?

>> John:  I happen to think that TOAD should be stamped on.

>> Dave:  Yeah. I’ll give you my opinion, because I don’t like it. But I’ll turn it over to you now, John.

>> John:  There’s nothing wrong with TOAD at all. Absolutely nothing. And I don’t use it firstly because you have to pay for it and Dave is chronically mean when it comes for paying for tools that will make my life easier. But much more importantly and because we work at many, many sites you have to work all the time with the lowest common denominator, and that’s SQL Plus. Now that’s really the reason why you use SQL Plus all the time and why I can’t use TOAD. Now, I can’t go on a customer site and say, “Hey, I can’t do this unless you buy a TOAD license.” But it’s a good product and if you’ve got the license, use it.

Copyright 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!