Dec 19

Written by: Richard To
Friday, December 19, 2008 12:16 PM 

Recently, I received an email from the presales support team. They asked me to help them tune three SQL statements for a benchmark database provided by a customer. Our presales support said that our SQL Optimizer was not providing any alternatives for two of these three complex SQL statements. I found two of these SQL statements were very complicated and I knew that there should be a lot of alternative SQL generated. Finally, we identified that the reason no SQL alternatives were generated was because the PC was running out of memory and our SQL Optimizer was terminating the optimization process during SQL alternative generation. So, no alternatives were provided after a few hours of SQL optimization. Here I would like to use the first SQL to illustrate how to use Quest SQL Optimizer to tune a complex SQL statement.

The First Workaround

The first workaround I recommended to the presales support was to lower the Intelligence Level down to 3. This setting will narrow down the number of SQL alternatives that are generated, save some memory, and therefore enable SQL Optimizer to provide at least some alternatives to test run. SQL Optimizer successfully generated a lot of alternative when the Intelligence Level was lowered to 3 from the default Intelligence Level of 4. But there was still another problem in tuning this SQL statement that needed to be addressed. The original SQL needed more than 8 hours to finish. Based on this run time, it could take multiple weeks to test run all the alternatives, but in this case, the benchmark report had to be submitted within one week. So, I had to rely on my human intelligence to figure out a way to shorten the testing time. I spent two days copying and installing the 4G database to see whether I could shorten the time of finding the best SQL alternatives.

A requirement of this benchmark test was that all the SQL alternatives would only be generated by Quest SQL Optimizer, but the selecting of which SQL alternatives to test run first could be subjected to the human wisdom of the tester.

The following SQL was created to mimic the original SQL statement for discussion and education only.

The Case SQL statement

SELECT a.apc_cdr,
   a.crfc_no,
        a.crfc_grp_type_cdr,
        c.pdc_cdr,
        e.ccr_grp_cdr,
        long_term,
        CASE long_term WHEN 'Y' THEN d.more_12_wgh_ftr_pc
                       WHEN 'N' THEN d.lss_12_wgh_ftr_pc
                       ELSE NULL END AS wgh_ftr_pc
   FROM tbp108 a,
        (SELECT a.ld_dt,
                a.ld_type_cdr,
                a.apc_cdr,
                a.crfc_no,
                CASE WHEN nbr_month = 12 THEN 'Y'
                     ELSE 'N' END AS long_term
           FROM tbp108 a,
                (SELECT apc_cdr,
                        crfc_no,
                        NVL(osd_am, 0),
                        COUNT(*) AS nbr_month
                   FROM tbp108
                  WHERE ld_type_cdr = 'M'
                    AND ld_dt >= ADD_MONTHS((SELECT MAX(ld_dt)
                                                FROM tbp104
                                               WHERE ld_type_cdr = 'M'
                                                 AND vsn_cdr <> '2'),
                        -11)
                  GROUP BY apc_cdr, crfc_no, NVL(osd_am, 0)
                 HAVING COUNT(*) = 12) b
          WHERE a.ld_dt = (SELECT MAX(ld_dt)
                             FROM tbp104
                            WHERE ld_type_cdr = 'M'
                              AND vsn_cdr <> '2')
            AND a.ld_type_cdr = 'M'
            AND a.apc_cdr = b.apc_cdr (+)
            AND a.crfc_no = b.crfc_no (+)
         UNION
         SELECT a.ld_dt,
                a.ld_type_cdr,
                a.apc_cdr,
                a.crfc_no,
                'N' AS long_term
           FROM tbp108 a
          WHERE a.ld_dt = to_date('02/10/2008', 'dd/mm/yyyy')
            AND a.ld_type_cdr = TRIM('D')
            AND a.cmt_ic = '1'
            AND a.eur_cmt_am - NVL(a.eur_osd_am, 0) > 0
            AND NOT EXISTS (SELECT *
                              FROM tbp108 p
                             WHERE ld_type_cdr = 'M'
                               AND ld_dt = (SELECT MAX(ld_dt)
                                              FROM tbp104
                                             WHERE ld_type_cdr = 'M'
                                               AND vsn_cdr <> '2')
                               AND p.apc_cdr = a.apc_cdr
                               AND p.crfc_no = a.crfc_no)) b,
        tbp109 c,
        tbp072 d,
        tbp069 e
  WHERE a.ld_dt = to_date('02/10/2008', 'dd/mm/yyyy')
    AND a.ld_type_cdr = TRIM('D')
    AND a.eur_cmt_am - NVL(a.eur_osd_am, 0) > 0
    AND a.cmt_ic = '1'
    AND a.apc_cdr = b.apc_cdr
    AND a.crfc_no = b.crfc_no
    AND a.ld_dt = c.ld_dt
    AND a.ld_type_cdr = c.ld_type_cdr
    AND a.apc_cdr = c.apc_cdr
    AND a.crfc_no = c.crfc_no
    AND a.ld_dt = d.ld_dt
    AND a.ld_type_cdr = d.ld_type_cdr
    AND a.rvv_cdr = d.rvv_cdr
    AND c.pdc_cdr = d.pdc_cdr
    AND c.spdc_cdr = d.spdc_cdr
    AND (d.dlt_cdr = 0
          OR d.dlt_cdr IS NULL)
    AND a.ld_dt = e.ld_dt
    AND a.ld_type_cdr = e.ld_type_cdr
    AND a.rvv_cdr = e.rvv_cdr
    AND c.pdc_cdr = e.pdc_cdr
    AND c.spdc_cdr = e.spdc_cdr
    AND (e.dlt_cdr = 0
          OR e.dlt_cdr IS NULL)
  ORDER BY a.apc_cdr, a.crfc_no, e.ccr_grp_cdr

Table Sizes

Table Name	Size	Number of Rows
TBP108	2.36G	1,777,110

Table Name	Size	Number of Rows
TBP104	230 Mb	31

Table Name	Size	Number of Rows
TBP072	230 Mb	3,497

Table Name	Size	Number of Rows
TBP069	230 Mb	5,180

Table Name	Size	Number of Rows
TBP109	525 Mb	3,753,720

After I installed the database, I quickly got the following execution plan. I did a test run for the original SQL in Quest SQL Optimizer and stepped through the query. I aborted the test run of the original SQL after twenty minutes, since I knew that I couldn’t wait 8 hours for it to finish since I only had 3 days to optimize these 3 SQL statements.

Since my machine is faster than the one that presales was originally using and has 2G memory, I decided to optimize the SQL with Intelligence Level set to 5. Quest SQL Optimizer generated 192 alternative SQL after 10 minutes of optimization. Knowing that the original SQL statement had to run at least 8 hours, if I test run all SQL alternatives, in the worst case I would have to spend 192 x 8 hours = 1536 hours = 64 days ~ 9 weeks to test them all. So, instead of using the execution time of the original SQL statement as the time to terminate the execution of each SQL alternative, I decided to use a user-defined time of 10 minutes as the termination time. I was testing to see if any of the SQL alternatives had a run time of less than 10 minutes that I could quickly spot.

Testing 192 alternative SQL using a termination time of 10 minutes, it took around 32 hours to finish. So after a day of test running, I had not had any luck at finding a faster alternative. I decided to stop the test running process. I only had 2 days left, so I went back to review the execution plan again. Suddenly there was a VIEW that caught my eye. This VIEW was in a nested-loop by other tables with return Cardinality=1, shown in the following:

Since a VIEW in the execution plan is working like a temp table, all subsequent query steps will process and contribute records to this VIEW. If it is a driving VIEW that executes once and then processes the other tables that would be fine. But this VIEW was in a nested-loop by other tables and it looked complicated. With the Cardinality = 1, the Oracle optimizer decided to use a nested-loop to loop thought this VIEW, but for a complex VIEW like this, I don’t think Oracle can easily do an accurate cost estimation for this piece of code.

Since the VIEW is actually processing the red (including the green) colored portion of the original SQL (see SQL statement above), I test ran only this portion of the SQL statement and found that it returned 66,614 records. So the problem was obviously that Oracle did an extremely bad estimation of this portion of SQL statements and chose a poor execution plan although all statistics were up-to-date. The performance problem for the original SQL statement was magnified by the Nested Loop join to this VIEW.

The First Solution to Case 1

In order to solve this problem, I optimized the original SQL statement and then I looked for some alternatives that process this VIEW with a Hash Join or Sort Merge. I selected Alt #161 and executed it. It was remarkable that Alt #161 finished within 12 minutes. With my first round of test running using 10 minutes as the termination time, I just missed by 2 minutes finding that this faster alternative Alt #161 is more than 40 times faster than the original SQL statement.

Note: Actually, the requirement for this SQL was to be tuned to less than 5 minutes in a HP UNIX machine with 4 GB RAM 64-bit 2 CPU. Since we don’t have this machine in our lab, I had to use a PC server to carry out the tuning, the original SQL took more than 30 minutes to execute in a HP machine, but in our PC server it took more than 8 hours to finish. So, I believe this solution will run less than 5 minutes in the HP machine.

The Final Solution

Although happy with the results of this tuning process, as a technical guy, I was curious to do more exploring. Since the SQL is very long and complex, I knew that our SQL Optimizer would spend the entire SQL Syntax Transformation quota (at Intelligence Level 5) in the outside layer of the SQL without reaching down to the sub-queries level to transform the syntax of the sub-queries. When a SQL statement is very complex, the way to optimize the inner level of the SQL statement is to take the sub-query from the SQL statement and optimize only the sub-query in Quest SQL Optimizer to find whether there are any better alternative rewrites at the sub-query level. In this case, I optimized and tested the red colored portion of the SQL statement first, since I knew it was the major resource consuming area that I needed to tune and as you can see I found a great improvement to 12 minutes. After reviewing the execution plan and executing the sub-query to find the actual number of records that are returned by the sub-query, I decided to repeat this process with only the green portion of the sub-query to see whether any better alternative could be found for this sub-query. A few seconds later, I had 5 alternatives to test with this sub-query. The second sub-query took around 9 minutes to finish and to my amazement the Alt#5 SQL only took 17 seconds to finish.

It is interesting to note that the Alt #5 disabled all indexes on table TBP108 and the Oracle cost estimation for this alternative is 10 times higher than its corresponding sub-query. But the performance of Alt #5 is 30 times faster than the original. This example illustrates that you should not trust those so called SQL tuning experts who always teach how to use indexes to tune SQL or rely only on the estimated cost to judge SQL performance. It is especially true when you are tuning a SQL statement that is very complex and accesses tables with large data volumes. I am not going to explain here why it is faster to disable the indexes; since it is a really clumsy mathematic explanation that is not suitable for this casual Blog.

So to finish the process of optimizing the original SQL statement, I combined this sub-query from Alt #5 with the tuned SQL statement that was running in 12 minutes and did another test run. The result was amazing! It took only 1 minute and 28 seconds to finish. This process was fun and most satisfying. I spent almost 2 days to tune a SQL from 8 hours down to 1:28 minutes with Quest SQL Optimizer; it is 320 times faster than the original SQL statement. If I had to do it manually, I believe I would have to spend another few weeks.

I will discuss how to tune another complex SQL in my coming blog, it is fun also.