SQL Tuning [Electronic resources] نسخه متنی

B.3 Checking the Execution Plans

For this exercise, imagine that the base
development is performed on Oracle, with later testing to check that
the same SQL functions correctly and performs well on DB2 and SQL
Server. You learned of this SQL because it performed more slowly than
expected on Oracle, so you already suspect it leads to a poor
execution plan on at least that database. You will need to check
execution plans on the other databases, which have not yet been
tested.

B.3.1 Getting the Oracle Execution Plan

Place the
SQL in a file named tmp.sql and run the script
ex.sql, as described in Chapter 3. The result is as follows:

PLAN
--------------------------------------------------------------------------------
SELECT STATEMENT
SORT ORDER BY
NESTED LOOPS
NESTED LOOPS
NESTED LOOPS
NESTED LOOPS
NESTED LOOPS
TABLE ACCESS FULL 4*CUSTOMERS
TABLE ACCESS BY INDEX ROWID 1*ORDERS
INDEX RANGE SCAN ORDER_CUSTOMER_ID
TABLE ACCESS BY INDEX ROWID 2*ORDER_DETAILS
INDEX RANGE SCAN ORDER_DETAIL_ORDER_ID
TABLE ACCESS BY INDEX ROWID 5*SHIPMENTS
INDEX UNIQUE SCAN SHIPMENT_PKEY
TABLE ACCESS BY INDEX ROWID 6*ADDRESSES
INDEX UNIQUE SCAN ADDRESS_PKEY
TABLE ACCESS BY INDEX ROWID 3*PRODUCTS
INDEX UNIQUE SCAN PRODUCT_PKEY

You notice that your database is set up to use the rule-based
optimizer, so you switch to cost-based optimization, check that you
have statistics on the tables and indexes, and check the plan again,
finding a new result:

PLAN
--------------------------------------------------------------------------------
SELECT STATEMENT
SORT ORDER BY
HASH JOIN
TABLE ACCESS FULL 3*PRODUCTS
HASH JOIN
HASH JOIN
HASH JOIN
HASH JOIN
TABLE ACCESS FULL 4*CUSTOMERS
TABLE ACCESS FULL 1*ORDERS
TABLE ACCESS FULL 2*ORDER_DETAILS
TABLE ACCESS FULL 5*SHIPMENTS
TABLE ACCESS FULL 6*ADDRESSES

Neither execution plan is close to the optimum plan. Instead, both
the rule-based and the cost-based optimization plans drive from full
table scans of large tables. The database ought to reach the driving
table on a highly selective index, so you know that an improvement is
certainly both necessary and possible.

B.3.2 Getting the DB2 Execution Plan

Place the
SQL in tmp.sql and run the following command
according to the process described in Chapter 3:

cat head.sql tmp.sql tail.sql | db2 +c +p -t

The result is an error; DB2 complains that it sees inconsistent
column types in the condition on Phone_Number. You
discover that the Phone_Number column is of the
VARCHAR type, which is incompatible with the
number type of the constant 6505551212.

Unlike Oracle, DB2 does not implicitly convert character-type columns
to numbers when SQL compares inconsistent datatypes. This is just as
well, in this case, since such a conversion might deactivate an index
on Phone_Number, if there is one. You might even
suspect, already, that this is precisely what has caused poor
performance in the Oracle baseline development environment.

You fix the problem in the most obvious way, placing quotes around
the phone number constant to make it a character type:

SELECT C.Phone_Number, C.Honorific, C.First_Name, C.Last_Name, C.Suffix, 
C.Address_ID, A.Address_ID, A.Street_Addr_Line1, A.Street_Addr_Line2, 
A.City_Name, A.State_Abbreviation, A.ZIP_Code, OD.Deferred_Ship_Date, 
OD.Item_Count, P.Prod_Description, S.Shipment_Date 
FROM Orders O, Order_Details OD, Products P, Customers C, Shipments S, 
Addresses A
WHERE OD.Order_ID = O.Order_ID
AND O.Customer_ID = C.Customer_ID
AND OD.Product_ID = P.Product_ID
AND OD.Shipment_ID = S.Shipment_ID
AND S.Address_ID = A.Address_ID
AND C.Phone_Number = '6505551212'
AND O.Business_Unit_ID = 10
ORDER BY C.Customer_ID, O.Order_ID Desc, S.Shipment_ID, OD.Order_Detail_ID;

Placing this new version of the SQL in tmp.sql,
you again attempt to get the execution plan:

$ cat head.sql tmp.sql tail.sql | db2 +c +p -t
DB20000I  The SQL command completed successfully.
DB20000I  The SQL command completed successfully.
OPERATOR_ID TARGET_ID OPERATOR_TYPE OBJECT_NAME        COST
----------- --------- ------------- ------------------ -----------
1         - RETURN        -                          260
2         1 NLJOIN        -                          260
3         2 NLJOIN        -                          235
4         3 NLJOIN        -                          210
5         4 TBSCAN        -                          185
6         5 SORT          -                          185
7         6 NLJOIN        -                          185
8         7 NLJOIN        -                          135
9         8 FETCH         CUSTOMERS                   75
10         9 IXSCAN        CUST_PH_NUMBER              50
11         8 FETCH         ORDERS                      70
12        11 IXSCAN        ORDER_CUST_ID               50
13         7 FETCH         ORDER_DETAILS               75
14        13 IXSCAN        ORDER_DTL_ORD_ID            50
15         4 FETCH         PRODUCTS                    50
16        15 IXSCAN        PRODUCT_PKEY                25
17         3 FETCH         SHIPMENTS                   75
18        17 IXSCAN        SHIPMENT_PKEY               50
19         2 FETCH         ADDRESSES                   75
20        19 IXSCAN        ADDRESS_PKEY                50
20 record(s) selected.
DB20000I  The SQL command completed successfully.
$

That's more like it, just the execution plan you
chose when you analyzed the SQL top-down, except for the minor issue
of reaching Products before
Shipments, which will have virtually no effect on
the runtime. Since the type inconsistency involving
Phone_Number might require correcting on SQL
Server and Oracle, you need to try this modified version immediately
on the other databases.

B.3.3 Getting the SQL Server Execution Plan

Suspecting
that you already have the solution to slow performance for this
query, you fire up SQL Server's Query
Analyzer and use set showplan_text on to see a
concise view of the execution plan of the statement modified with
C.Phone_Number = '6505551212'
to correct the type inconsistency. A click on the Query
Analyzer's Execute-Query button results in the
following output:

StmtText                                        
-------------------------------------------------------------------------------
|--Bookmark Lookup(...(...[Products] AS [P]))
|--Nested Loops(Inner Join)
|--Bookmark Lookup(...(...[Addresses] AS [A]))
|    |--Nested Loops(Inner Join)
|         |--Sort(ORDER BY:([O].[Customer_ID] ASC, [O].[Order_ID] DESC, 
(wrapped line) [OD].[Shipment_ID] ASC, [OD].[Order_Detail_ID] ASC))
|         |    |--Bookmark Lookup(...(...[Shipments] AS [S]))
|         |         |--Nested Loops(Inner Join)
|         |              |--Bookmark Lookup(...(...[Order_Details] AS 
[OD]))
|         |              |    |--Nested Loops(Inner Join)
|         |              |         |--Filter(WHERE:([O].[Business_Unit_
ID]=10))
|         |              |         |    |--Bookmark Lookup(...(...
[Orders] AS [O]))
|         |              |         |         |--Nested Loops(Inner Join)
|         |              |         |              |--Bookmark Lookup(...
(...
(wrapped line) [Customers] AS [C]))
|         |              |         |              |    |--Index Seek(...
(...
(wrapped line) [Customers].[Customer_Phone_Number] 
(wrapped line) AS [C]), SEEK:([C].[Phone_Number]='6505551212') ORDERED)
|         |              |         |              |--Index Seek(...(...
(wrapped line) [Orders].[Order_Customer_ID] AS [O]), 
(wrapped line) SEEK:([O].[Customer_ID]=[C].[Customer_ID]) ORDERED)
|         |              |         |--Index Seek(...(...
(wrapped line) [Order_Details].[Order_Detail_Order_ID] 
(wrapped line) AS [OD]), SEEK:([OD].[Order_ID]=[O].[Order_ID]) ORDERED)
|         |              |--Index Seek(...(...[Shipments].[Shipment_PKey] 
(wrapped line) AS [S]), SEEK:([S].[Shipment_ID]=[OD].[Shipment_ID]) ORDERED)
|         |--Index Seek(...(...[Addresses].[Address_PKey] 
(wrapped line) AS [A]), SEEK:([A].[Address_ID]=[S].[Address_ID]) ORDERED)
|--Index Seek(...(...[Products].[Product_PKey] 
(wrapped line) AS [P]), SEEK:([P].[Product_ID]=[OD].[Product_ID]) ORDERED)
(19 row(s) affected)

Good news! The corrected SQL leads to exactly the optimum plan here.
Just out of curiosity, you check the execution plan for the original
SQL, and you find the same result! Evidently, SQL Server is doing the
data conversion on the constant, avoiding disabling the
index.