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 PlanPlace 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 PlanPlace 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 PlanSuspecting 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.  |
