{"id":73160,"date":"2015-06-25T15:16:41","date_gmt":"2015-06-25T15:16:41","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/uncategorized\/basics-of-the-cost-based-optimizer-part-3\/"},"modified":"2021-07-14T13:07:23","modified_gmt":"2021-07-14T13:07:23","slug":"basics-of-the-cost-based-optimizer-part-3","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/oracle-databases\/basics-of-the-cost-based-optimizer-part-3\/","title":{"rendered":"Basics of the Cost Based Optimizer – Part 3"},"content":{"rendered":"

In the second installment<\/a> of this series we looked at individual access paths for the tables in a simple join query to highlight an important flaw in the default model that the optimizer uses for indexes. Having taken advantage of a recent enhancement that addresses that flaw we are now ready to move onto the problems that appear with the query when taken as a whole.<\/p>\n

Reprise<\/h3>\n

Our query joins orders to order lines, and extracts data about orders placed in a given date range with order lines for a given product:<\/p>\n

select\r\n        trunc(ord.date_ordered),\r\n        count(*),\r\n        sum(orl.quantity),\r\n        sum(orl.quantity * orl.unit_price) total_value\r\nfrom\r\n        orders          ord,    \r\n        order_lines     orl\r\nwhere\r\n        ord.date_ordered >= trunc(sysdate) \u2013 7\r\nand     ord.date_ordered <  trunc(sysdate)\r\nand     orl.order_id     =  ord.order_id\r\nand     orl.product_id   =  101234\r\ngroup by\r\n        trunc(ord.date_ordered)\r\norder by\r\n        total_value desc\r\n;\r\n\r\n<\/pre>\n
We have worked out that we have about 20,000 orders in the week (though the optimizer has estimated 11,500) that are very well packed near the end of the orders<\/strong><\/em> table, and about 1,000 order lines for the requested product (and the optimizer agrees with our estimate) scattered widely through the order_lines<\/strong><\/em> table.<\/p>\n
By setting the table_cached_blocks<\/strong><\/em> table preference to an appropriate value (on both tables though, for our purposes, the orders<\/strong><\/em> table was the critical one) we have allowed the optimizer to realize that the orders are well clustered so it is happy to use the (date_ordered<\/strong><\/em>) index to select the orders for the week; and even without adjustment the optimizer was happy to use the (product_id<\/strong><\/em>) index on the order_lines<\/strong><\/em> table to select all the order lines for the given product.<\/p>\n
The best laid plans\u2026<\/h3>\n
So what happens when we try to join the tables and combine the two predicates? Here\u2019s the default plan on my system after (once again) setting the table_cached_blocks<\/strong><\/em> preference to a suitable value and collecting stats:<\/p>\n
------------------------------------------------------------------------------------------------\r\n| Id  | Operation                       | Name         | Rows  | Bytes | Cost (%CPU)| Time     |\r\n------------------------------------------------------------------------------------------------\r\n|   0 | SELECT STATEMENT                |              |  1083 | 33573 |  1775   (1)| 00:00:09 |\r\n|   1 |  SORT ORDER BY                  |              |  1083 | 33573 |  1775   (1)| 00:00:09 |\r\n|   2 |   HASH GROUP BY                 |              |  1083 | 33573 |  1775   (1)| 00:00:09 |\r\n|*  3 |    FILTER                       |              |       |       |            |          |\r\n|*  4 |     HASH JOIN                   |              |  1083 | 33573 |  1773   (1)| 00:00:09 |\r\n|   5 |      TABLE ACCESS BY INDEX ROWID| ORDER_LINES  |  1083 | 18411 |  1064   (1)| 00:00:06 |\r\n|*  6 |       INDEX RANGE SCAN          | ORL_FK_PRD   |  1083 |       |     4   (0)| 00:00:01 |\r\n|   7 |      TABLE ACCESS BY INDEX ROWID| ORDERS       | 11525 |   157K|   709   (1)| 00:00:04 \r\n|*  8 |       INDEX RANGE SCAN          | ORD_DATE_ORD | 11525 |       |    34   (3)| 00:00:01 |\r\n------------------------------------------------------------------------------------------------\r\n\r\nPredicate Information (identified by operation id):\r\n---------------------------------------------------\r\n   3 - filter(TRUNC(SYSDATE@!)>TRUNC(SYSDATE@!)-7)\r\n   4 - access(\"ORL\".\"ORDER_ID\"=\"ORD\".\"ORDER_ID\")\r\n   6 - access(\"ORL\".\"PRODUCT_ID\"=101234)\r\n   8 - access(\"ORD\".\"DATE_ORDERED\">=TRUNC(SYSDATE@!)-7 AND\r\n              \"ORD\".\"DATE_ORDERED\"<TRUNC(SYSDATE@!))\r\n\r\n<\/pre>\n
I\u2019m not going to try and explain every detail of the execution plans we\u2019re going to look at but, as you can see in operations 4 through to 8, the optimizer has basically decided to run the two simpler queries I showed you in the previous installment and then joined the two results using a hash join.<\/p>\n
To do a hash join, Oracle acquires the whole of the first data set (known as the \u201cbuild\u201d table) and builds an in-memory hash table for it based on generating hash values for the columns used in equality<\/strong><\/em> join predicates; then it accesses the second data set (known as the \u201cprobe\u201d table) one row at a time, applies the same hashing function to the equality join columns, and probes the in-memory hash table to see if there are any possible matches. If there is a matching hash value Oracle then checks the actual values of all the join predicates to see if it is a proper match or simply a \u201chash collision\u201d.<\/p>\n
General Note: in an execution plan the first child (operation 5 in the above) to a hash join operation always<\/strong> identifies the build data set, the second child (operation 7 in the above) always<\/strong> identifies the probe data set.<\/em><\/p>\n
I\u2019ve said that the hash table will be in-memory, but if the hash table is too big to fit in the available memory Oracle has a mechanism for saving it to disc in batches (called partitions) and saving the probe table to disc in matching batches, then doing the join piecewise between matching batches. (See Cost Based Oracle \u2013 Fundamentals<\/a>, Apress 2005, if you want a more detailed description).<\/p>\n
In our case Oracle has decided to use the order_lines<\/strong><\/em> data as the build table because it thinks that the total volume of data it will fetch will be only 18KB compared to 157KB for the orders<\/strong><\/em> table. (Note: the build table is chosen based on the number of bytes acquired, not the number of rows).<\/p>\n
From the arithmetic we can see that the optimizer assumes it will be able to keep that 18KB in memory: the cost of acquiring the data from order_lines<\/strong><\/em> is 1,064; the cost of acquiring the data from orders<\/strong><\/em> is 709, the total cost of the hash join is 1,773 \u2013 which is exactly 1064 + 709: the optimizer thinks that (to the nearest unit) the cost of doing the actual hash join is virtually free, i.e.: it\u2019s going to take a little CPU and a little memory, but no extra disc I\/O.<\/p>\n
Alternatives:<\/h3>\n
In the first installment we consider two other possible plans \u2013 a nested loop join starting with orders<\/strong><\/em>, or a nested loop join starting with order_lines<\/strong><\/em>. We can get these plans through some fairly simple hinting:<\/p>\n