Good new SQL tuning book

June 24th, 2015

People ask me from time to time what are some good SQL tuning books. It’s a tough question to answer. There are some seminal books such as “SQL Tuning” by Dan Tow and “Cost Based Optimizer Fundamentals” by Jonathan Lewis, but these are advanced books  that few I know have actually read from cover to cover. If you are looking for practical SQL tuning cookbooks you might want something a little less advanced.  For a practical approach, I love “Refactoring SQL Applications” by  Stephane Faroult which is an easy read, again, it’s not a straight forward SQL tuning book.

Finally there is a book that provides a pragmatic down to earth approach to tuning SQL queries : “Oracle Performance Tuning and Optimization : It’s all about the Cardinalities” by Kevin Meade.

I’ve been meaning to put together a general review but as that’s a lot to tackle I thought I’d go through some of the material in the book chapter by chapter.

Chapter 1 lays out “Driving Table and Join Order”

Meade lays out the 4 parts of a query as

  1. driving table
  2. join order
  3. access method
  4. join method

This is a great way to look at query optimization. As I’ve written about before with Visual SQL Tuning, based on Dan Tow’s work, the join order is the most important part of query optimization and the first step in join order is finding the driving table. The goal is

“Remove as many rows as possible as early as possible in query execution”

Big question is how do we remove as many row as possible as early as possible in the execution? We do that by finding the best driving table. The best driving table is the table who has the predicate filter that eliminates the most rows. To find the best predicate filter we have to go through the query and find which tables have predicate filters and then determine how many rows are returned after the predicate filter verses the rows in the table. Calculating these filter ratios can be a good bit of busy work. I like my busy work automated, so when I was at Embarcadero, I worked with our DB Optimizer tool team to do this automatically. It was pretty cool. Below is a diagram produced from a query that Jonathan Lewis put together for an article on how to  analyze a query visually.

jl_lewis_query

 

The blue numbers to the  bottom right of certain tables are the filter ratios. A low ratio means that a low percentage of the table is returned after applying the predicate filter.

How do you calculate these predicate filters in an automated way without DB Optimizer. One of my favorite things about Chapter 1 is a query that Kevin Meade wrote to automatically calculate predicate filter ratios.

For example if I run

SQL> explain plan for
SELECT /*+ gather_plan_statistics */ order_line_data
FROM           customers cus
    INNER JOIN orders ord ON ord.id_customer = cus.id
    INNER JOIN order_lines orl ON orl.id_order = ord.id
    INNER JOIN products prd1 ON prd1.id = orl.id_product
    INNER JOIN suppliers sup1 ON sup1.id = prd1.id_supplier
WHERE   cus.location = 'LONDON'
    AND ord.date_placed BETWEEN sysdate - 7
                        AND     sysdate
    AND sup1.location = 'LEEDS'
    AND EXISTS ( SELECT  NULL
                 FROM  alternatives    alt
                       INNER JOIN     products prd2
                         ON prd2.id = alt.id_product_sub
                       INNER JOIN     suppliers sup2
                         ON sup2.id = prd2.id_supplier
                 WHERE    alt.id_product = prd1.id
                       AND sup2.location != 'LEEDS' )
        ;

Then I run

SQL> @showplanfrpspreadsheetcode11g.sql

I’ll see some output like

with
      frp_data as (
                    select '   11' id,'DELPHIXDB' table_owner,'PRODUCTS' table_name,'PRD2' table_alias,1999 num_rows,count(*) rowcount,1999 cardinality,cast(null as number) filtered_cardinality from DELPHIXDB.PRODUCTS PRD2 union all
                    select '   17' id,'DELPHIXDB' table_owner,'CUSTOMERS' table_name,'CUS' table_alias,14576 num_rows,count(*) rowcount,49 cardinality,count(case when "CUS"."LOCATION"='LONDON' then 1 end) filtered_cardinality from DELPHIXDB.CUSTOMERS CUS union all
                    select '    2' id,'DELPHIXDB' table_owner,'SUPPLIERS' table_name,'SUP1' table_alias,99 num_rows,count(*) rowcount,50 cardinality,count(case when "SUP1"."LOCATION"='LEEDS' then 1 end) filtered_cardinality from DELPHIXDB.SUPPLIERS SUP1 union all
                    select '    8' id,'DELPHIXDB' table_owner,'SUPPLIERS' table_name,'SUP2' table_alias,99 num_rows,count(*) rowcount,50 cardinality,count(case when "SUP2"."LOCATION"<>'LEEDS' then 1 end) filtered_cardinality from DELPHIXDB.SUPPLIERS SUP2 union all
                    select '   14' id,'DELPHIXDB' table_owner,'PRODUCTS' table_name,'PRD1' table_alias,1999 num_rows,count(*) rowcount,1999 cardinality,cast(null as number) filtered_cardinality from DELPHIXDB.PRODUCTS PRD1 union all
                    select '   18' id,'DELPHIXDB' table_owner,'ORDERS' table_name,'ORD' table_alias,71604 num_rows,count(*) rowcount,17898 cardinality,count(case when "ORD"."DATE_PLACED">=SYSDATE@!-7 AND "ORD"."DATE_PLACED"<=SYSDATE@! then 1 end) filtered_cardinality from DELPHIXDB.ORDERS ORD union all
                    select '    9' id,'DELPHIXDB' table_owner,'SUPPLIERS' table_name,'SUP2' table_alias,99 num_rows,count(*) rowcount,99 cardinality,cast(null as number) filtered_cardinality from DELPHIXDB.SUPPLIERS SUP2 union all
                    select '   12' id,'DELPHIXDB' table_owner,'ALTERNATIVES' table_name,'ALT' table_alias,17900 num_rows,count(*) rowcount,17900 cardinality,cast(null as number) filtered_cardinality from DELPHIXDB.ALTERNATIVES ALT union all
                    select '   19' id,'DELPHIXDB' table_owner,'ORDER_LINES' table_name,'ORL' table_alias,286416 num_rows,count(*) rowcount,286416 cardinality,cast(null as number) filtered_cardinality from DELPHIXDB.ORDER_LINES ORL union all
                    select null,null,null,null,null,null,null,null from dual
                  )
select frp_data.*,round(frp_data.filtered_cardinality/case when frp_data.rowcount = 0 then cast(null as number) else frp_data.rowcount end*100,1) actual_frp,decode(frp_data.filtered_cardinality,null,cast(null as number),round(frp_data.cardinality/case when frp_data.num_rows = 0 then cast(null as number) else frp_data.num_rows end*100,1)) plan_frp
from frp_data
where id is not null
order by frp_data.id
/

If I spool this to a file like kmo.sql (Kevin Meade out ) and run it I’ll get the filter ratios

@kmo.sql
ID    TABLE_OWN TABLE_NAME   TABL   NUM_ROWS   ROWCOUNT CARDINALITY FILTERED_CARDINALITY ACTUAL_FRP PLAN_FRP
----- --------- ------------ ---- ---------- ---------- ----------- -------------------- ---------- --------
    2 DELPHIXDB SUPPLIERS    SUP1	  99	     99 	 50		      49       49.5	50.5
    8 DELPHIXDB SUPPLIERS    SUP2	  99	     99 	 50		      50       50.5	50.5
   11 DELPHIXDB PRODUCTS     PRD2	1999	   1999        1999
   12 DELPHIXDB ALTERNATIVES ALT       17900	  17900       17900
   14 DELPHIXDB PRODUCTS     PRD1	1999	   1999        1999
   17 DELPHIXDB CUSTOMERS    CUS       14576	  14576 	 49		      49	 .3	  .3
   18 DELPHIXDB ORDERS	     ORD       71604	  71604       17898		    8941       12.5	25.0
   19 DELPHIXDB ORDER_LINES  ORL      286416	 286416      286416

  • NUM_ROWS – rows from table statistics
  • ROWCOUT – actual count(*)
  • CARDINALITY –  optimizer expected cardinality
  • ACTUAL_FRP – actual filter ratio
  • PLAN_FRP – expected filter ratio

From here I can see that the best filter ratio is on Customers and that’s where I should start my query.

Chapter 1 of Kevin Meade’s book is available online here.

A full set of scripts from his book are available here scripts.rar 

A short word doc on tuning from Kevin along with some examples is available at information needed in Tuning a SQL Query.docx 

And here is a thread on Orafaq from Kevin.


Uncategorized

  1. Trackbacks

  2. No trackbacks yet.
  1. Comments

  2. OracleMan Consulting
    | #1

    i cant find the book itself
    and the link to orafaq brings up a blank page

  3. khailey
    | #2

    yes bizzare. Not sure what the deal is.
    try pasting the links into a new browser tab – that works for me

  4. Kevin Meade
    | #3

    Kyle, thank you for embarking on this discussion of tuning, and in referencing my book as part of it. If any of your followers cannot find book elements for the book, then they should feel free to contact me via email @km133688@sbcglobal.net, and I will forward what ever they are looking for. You do the Oracle community a great service and I want to help that as much as possible. Your ability to weave together information from so many sources into a cohesive intelligible material is highly value added. Kevin Meade

  5. Jared
    | #4

    I would love to read this book. The problem is this; the only format available on Amazon is paperbook. I no longer buy paper copies of books. If this were available as a Kindle book I probably would have purchased it.

    Even better than Kindle would be epub, and/or watermarked PDF.

  6. Kevin Meade
    | #5

    Sadly I tried to do a kindle version, but none of the artifacts translate to that format well. All the QEPs, Diagrams, and code listings, get mangled to the point of being unusable. These are of course essential for understanding the book’s material so until I figure out how to get around that, I cannot port to kindle. I will try again. I will also look into the watermarked PDF thing as well. In the mean time, please feel free to contact me directly via my personal email km133688@sbcglobal.net and we can work something out for you. Though I have only recently discovered Kyle, I have grown a great respect for him and his blogs and advice, and so I want to go the extra mile for any of his readers wherever I can.

    Kevin Meade

  7. Andy Helm
    | #6

    Hi Kyle,
    Based on your example, isn’t the table with the best filter ratio the CUSTOMERS table, where the actual FRP is 0.3 , rather than ORDERS as stated ?

  8. Andy Helm
    | #7

    @Kevin Meade
    I have the Kindle version of the book, it will render on my iPad and Android Lollipop phone, though not in the Kindle cloud reader nor on an older Android tablet

  9. khailey
    | #8

    @Andy Helm – yes quite right. The starting table should be customers, not Orders. Correct the text above. Thanks!


four − 2 =