{"id":73144,"date":"2015-11-04T10:00:51","date_gmt":"2015-11-04T10:00:51","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/uncategorized\/basics-of-the-cost-based-optimizer-part-6\/"},"modified":"2021-07-14T13:07:19","modified_gmt":"2021-07-14T13:07:19","slug":"basics-of-the-cost-based-optimizer-part-6","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/oracle-databases\/basics-of-the-cost-based-optimizer-part-6\/","title":{"rendered":"Basics of the Cost Based Optimizer – part 6"},"content":{"rendered":"

In part 5 of this series<\/a> I started working through a list of queries designed to demonstrate ways in which the optimizer can produce bad cardinality estimates. In this installment I’m going work through the remainder of the list. The outstanding queries are as follows:<\/p>\n

select count(*) from t1 where trunc_5 > (\r\n\tselect max(trunc_5) from t1 where mod_200 = 0\r\n);\r\n\r\nselect count(*) from t1 where five_per_sec > sysdate - 60\/86400;\r\n\r\nexecute dbms_lock.sleep(10)\r\nselect count(*) from t1 where five_per_sec > sysdate - 10\/86400;\r\n\r\n\r\nvariable b1 number\r\nexec :b1 := 10\r\nselect count(*) from t1 where skewed = :b1;\r\n\r\nexec :b1 := 100\r\nselect count(*) from t1 where skewed = :b1;\r\n<\/pre>\n
The first query demonstrates the re-appearance of one of the optimizer\u2019s favourite guesses – 5% – which is frequently used as an estimate of the effect of a subquery. The next pair (with the 10 second sleep in between) demonstrates the potentially catastrophic side effects of statistics going out of date. The final pair introduces us to some of the problems of bind variables and skewed data distributions.<\/p>\n
Remember that I\u2019m running all these demonstrations on 11.2.0.4 and, so far, haven\u2019t generated histograms on any of the columns referenced by the queries.<\/p>\n
Subquery effects<\/h3>\n
Here\u2019s the default execution plan from the first query in our list:<\/p>\n
-----------------------------------------------------------------------------\r\n| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |\r\n-----------------------------------------------------------------------------\r\n|   0 | SELECT STATEMENT     |      |       |       |  1308 (100)|          |\r\n|   1 |  SORT AGGREGATE      |      |     1 |     5 |            |          |\r\n|*  2 |   TABLE ACCESS FULL  | T1   | 50000 |   244K|   658   (9)| 00:00:04 |\r\n|   3 |    SORT AGGREGATE    |      |     1 |     9 |            |          |\r\n|*  4 |     TABLE ACCESS FULL| T1   |  5000 | 45000 |   650   (8)| 00:00:04 |\r\n-----------------------------------------------------------------------------\r\n\r\nPredicate Information (identified by operation id):\r\n---------------------------------------------------\r\n   2 - filter(\"TRUNC_5\">)\r\n   4 - filter(\"MOD_200\"=0)\r\n<\/pre>\n
The query identified 195 rows, but the prediction was 50,000 \u2013 which is 5% of the starting 1,000,000.<\/p>\n
The error doesn\u2019t really matter in this simple example, but you can imagine that in a more complex query \u2013 one that joins the result set from line 2 to another table \u2013 an estimate of 50,000 would encourage the optimizer to do a hash join to the next table, while an estimate that was closer to the actual 195 might encourage the optimizer to do a nested loop.<\/p>\n
Although there are a few options available there isn\u2019t a nice way to address this problem because that 5% is hard coded into the optimizer.<\/p>\n
First, of course, you could simply work out an optimum plan and create a complete set of hints to enforce that plan (perhaps improving the robustness of the hinting by generating the equivalent SQL Plan Baseline after getting the plan you want). If the query were simple enough you might be able to get away with a suitably populated \/*+ leading() *\/<\/strong><\/em> hint in the main query block and the \/*+ no_unnest push_subq *\/<\/strong><\/em> pair in the subquery.<\/p>\n
Alternatively, if you\u2019re licensed for the Performance Pack, you could use the dbms_sqltune<\/strong><\/em> package (perhaps through the graphic OEM interface) to create an SQL Profile for the query. This will exercise the query and discover that the default 5% is a bad estimate and can create a set of \u201copt_estimate()\u201d<\/em><\/strong> hints to help the optimizer calculate better estimates, which ought to result in a better plan being selected.<\/p>\n
Finally, you may be lucky enough to be able to add a \u201csimple\u201d cardinality()<\/strong><\/em> hint to the SQL that gets the optimizer started with a suitable estimate and allows it to generate reasonable estimates for the rest of the plan. Unfortunately the cardinality()<\/strong><\/em> hint is not quite supported, not really documented, and is much more subtle (and therefore a lot harder to use) than the few notes available on the Internet might suggest.<\/p>\n
In my trivial case I could simply add the hint \/*+ cardinality(t1 195) *\/<\/strong><\/em> to the SQL:<\/p>\n
select\r\n        \/*+ cardinality(t1 195) *\/\r\n        count(*)\r\nfrom    t1\r\nwhere   trunc_5 > (\r\n                select max(trunc_5) from t1 where mod_200 = 0\r\n        )\r\n;\r\n-----------------------------------------------------------------------------\r\n| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |\r\n-----------------------------------------------------------------------------\r\n|   0 | SELECT STATEMENT     |      |       |       |  1308 (100)|          |\r\n|   1 |  SORT AGGREGATE      |      |     1 |     5 |            |          |\r\n|*  2 |   TABLE ACCESS FULL  | T1   |   195 |   975 |   658   (9)| 00:00:04 |\r\n|   3 |    SORT AGGREGATE    |      |     1 |     9 |            |          |\r\n|*  4 |     TABLE ACCESS FULL| T1   |  5000 | 45000 |   650   (8)| 00:00:04 |\r\n-----------------------------------------------------------------------------\r\n<\/pre>\n
This hasn\u2019t changed the cost of the query, but it has changed the cardinality estimate at line 2 as required. Oracle doesn\u2019t use the cardinality()<\/strong><\/em> internally \u2013 if you check the optimizer\u2019s trace file (event 10053) you\u2019ll see that the hint has been translated to an opt_estimate()<\/strong><\/em> hint of the form:<\/p>\n
opt_estimate(table t1@sel$1 rows=195.000000)\r\n<\/pre>\n
Like all \u201cmicro-management\u201d hinting, though, you typically need far more cardinality hints than you might first imagine. As it stands this hint is a \u201csingle table access\u201d hint but in more complex cases you may have to include lots of hints to say things like \u201cif you start at t1 it will give you 195 rows, but if you start at table t2 it will give you 5000 rows\u201d<\/em>, then you will probably have to use the \u201cjoin cardinality\u201d version of the hint to say things like: \u201cif you start t1 -> t2 that join will give you 800 rows, but t1 -> t3 will give you 650 rows\u201d<\/em>. Generally speaking it\u2019s a bit of a pain to try and work out all the cardinality hints you need to ensure that the plan you need will appear. It\u2019s best to avoid the technique and stick with more \u201ctraditional\u201d hinting.<\/p>\n
The passage of time<\/h3>\n
To run my test I created the data, gathered stats, then ran various queries and pulled their execution plans from memory. By the time I ran the query that compared column five_per_sec<\/strong><\/em> with “sysdate \u2013 60 seconds”<\/em><\/strong> a few seconds had already passed between creating the data and running the query. The time component on the highest value of five_per_sec<\/strong><\/em> was 17:15:17, while the time I ran the query had moved on to 17:15:31, a gap of 14 seconds \u2013 which meant there were 46 seconds of data in the table matching the predicate \u201cfive_per_sec > sysdate \u2013 60\/86400\u201d<\/em>. Here\u2019s the execution I got when running the query:<\/p>\n
---------------------------------------------------------------------------\r\n| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |\r\n---------------------------------------------------------------------------\r\n|   0 | SELECT STATEMENT   |      |       |       |   752 (100)|          |\r\n|   1 |  SORT AGGREGATE    |      |     1 |     8 |            |          |\r\n|*  2 |   TABLE ACCESS FULL| T1   |   230 |  1840 |   752  (21)| 00:00:04 |\r\n---------------------------------------------------------------------------\r\n<\/pre>\n
As you can see the optimizer\u2019s estimate is 230 rows \u2013 a perfect match for 46 seconds at five rows per second.<\/p>\n
What happens, though if I wait a further 10 seconds (time is now 17:15:41) and then query for sysdate \u2013 10 \/ 86400<\/strong><\/em>? I\u2019m querying for data later than 17:15:31, and there is no data in the table matching that predicate, but what does the optimizer expect to find:<\/p>\n
---------------------------------------------------------------------------\r\n| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |\r\n---------------------------------------------------------------------------\r\n|   0 | SELECT STATEMENT   |      |       |       |   752 (100)|          |\r\n|   1 |  SORT AGGREGATE    |      |     1 |     8 |            |          |\r\n|*  2 |   TABLE ACCESS FULL| T1   |     5 |    40 |   752  (21)| 00:00:04 |\r\n---------------------------------------------------------------------------\r\n<\/pre>\n
The optimizer’s estimate is 5 rows. A few extra tests will show you that this is the number of rows that match \u201ccolumn = constant\u201d \u2013 as soon as you execute a query with a range-based predicate that the optimizer can see is asking for data that is entirely outside the known low\/high range the arithmetic falls back to the arithmetic for \u201ccolumn = constant\u201d.<\/p>\n
The passage of time \u2013 case study<\/h3>\n
In my model the error was not too bad \u2013 if the data had carried on arriving at the same rate while I was doing my tests there would have been 50 rows in the last 10 seconds, so 5 wouldn\u2019t (necessarily) be a terrible estimate and might not have caused problems. But the basic behaviour can have a terrible impact, as the following production example shows.<\/p>\n
Data arrives at the rate of roughly 300 rows per minute (5 per second) and a very important polling job runs a query every few minutes to identify any data that has arrived in the last 15 minutes (and is still in a certain state) \u2013 typically identifying about 2,000 to 4,000 rows. The query is fairly simple and includes the time-critical predicate:<\/p>\n
where arrival_time > sysdate \u2013 1\/96\r\n<\/pre>\n
Because of the rate of data change the automatic stats collection job runs every night on this table so the statistics are as accurate as a simple, default, setup could make them.<\/p>\n
Every few days – apparently completely at random – the polling job hits a terrible performance problem and takes several minutes to run instead of a couple of seconds. Not surprisingly this has an impact on the business operation and makes a big difference to how the users feel about the system. What\u2019s going on?<\/p>\n
Just 15 minutes after the stats have been collected the optimizer\u2019s estimate for this query is going to drop from a few thousand to a mere 5 (because, on average, there are 5 rows for any give value of arrival_time<\/strong><\/em>). If this happens the plan changes catastrophically because the optimizer decides that it can find a really good strategy for dealing with 5 rows that turns out to be a very bad strategy for the several thousand rows that are really there.<\/p>\n
The query is executed every few minutes, so it usually gets re-optimised \u2013 producing a good execution plan – very shortly after the stats have just been collected. Most days the cursor then stays in memory and is constantly re-used for the rest of the day; so the optimizer never needs to re-optimize the statement so never has a chance to create the bad plan. Unfortunately something happens occasionally to create a huge demand for memory in the shared pool and the good plan is flushed from memory and the query is re-optimised several hours after the stats have been collected \u2013 producing the bad \u201conly 5 rows\u201d plan \u2013 and the query then runs badly until the next day or (as the client discovered one day) they gather stats on the table.<\/p>\n
Columns that are time (or sequence) based can introduce all sorts of odd problems \u2013 though you may have to be a little unlucky to suffer badly as a consequence; but if you have any code which is always looking for \u201cthe last few rows\u201d this problem is one to watch out for. There are three basic strategies you could follow if you see the problem:<\/p>\n