{"id":73177,"date":"2015-01-13T14:47:06","date_gmt":"2015-01-13T14:47:06","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/uncategorized\/execution-plans-part-12-cardinality-feedback\/"},"modified":"2021-07-14T13:07:27","modified_gmt":"2021-07-14T13:07:27","slug":"execution-plans-part-12-cardinality-feedback","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/oracle-databases\/execution-plans-part-12-cardinality-feedback\/","title":{"rendered":"Execution Plans Part 12: Cardinality Feedback"},"content":{"rendered":"

In the previous instalment <\/a><\/strong>of this series I introduced three ways of accessing the run-time statistics for a query and described, for one of the methods, the basics of the information we can get and how we can use it. In this article I want to expand on the use of one method to show it can help use identify and resolve performance problems. The method is known (after Wolfgang Breitling’s exposition of it some 10 or more years ago) as Cardinality Feedback<\/a><\/strong>.<\/p>\n

Skewed Data<\/h2>\n

I have the following query \u2013 which I may not have analyzed properly before putting it into production:<\/p>\n

select\r\n\tt1.id,\r\n\tt2.small_vc\r\nfrom\r\n\tt1,\r\n\tt2\r\nwhere\r\n\tt1.date_ord >= trunc(sysdate) - 14\r\nand\tt1.supp_id = 1\r\nand\tt2.id = t1.id\r\norder by\r\n\tt1.id\r\n;<\/pre>\n
When I enable rowsource execution statistics by setting statistics_level<\/strong><\/em> to all<\/strong><\/em> (which increased the run time from 0.02 seconds to 0.25 seconds) I got the following information from the in-memory execution plan:<\/p>\n
select * from table(dbms_xplan.display_cursor(null,null,'iostats last +cost'));\r\n\r\n-----------------------------------------------------------------------------------------------------------------\r\n| Id  | Operation                      | Name    | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |\r\n-----------------------------------------------------------------------------------------------------------------\r\n|   0 | SELECT STATEMENT               |         |      1 |        |   206 (100)|   2800 |00:00:00.23 |     799 |\r\n|   1 |  SORT ORDER BY                 |         |      1 |      1 |   206   (2)|   2800 |00:00:00.23 |     799 |\r\n|   2 |   NESTED LOOPS                 |         |      1 |      1 |   205   (1)|   2800 |00:00:00.21 |     799 |\r\n|   3 |    NESTED LOOPS                |         |      1 |      1 |   205   (1)|   2800 |00:00:00.15 |     560 |\r\n|*  4 |     TABLE ACCESS BY INDEX ROWID| T1      |      1 |      1 |   202   (1)|   2800 |00:00:00.09 |     200 |\r\n|*  5 |      INDEX RANGE SCAN          | T1_DATE |      1 |  14013 |    41   (3)|  14000 |00:00:00.04 |      40 |\r\n|*  6 |     INDEX RANGE SCAN           | T2_I1   |   2800 |      1 |     2   (0)|   2800 |00:00:00.03 |     360 |\r\n|   7 |    TABLE ACCESS BY INDEX ROWID | T2      |   2800 |      1 |     3   (0)|   2800 |00:00:00.02 |     239 |\r\n-----------------------------------------------------------------------------------------------------------------\r\nPredicate Information (identified by operation id):\r\n---------------------------------------------------\r\n   4 - filter(\"T1\".\"SUPP_ID\"=1)\r\n   5 - access(\"T1\".\"DATE_ORD\">=TRUNC(SYSDATE@!)-14)\r\n   6 - access(\"T2\".\"ID\"=\"T1\".\"ID\")<\/pre>\n
Note that I\u2019ve included \u201c+cost\u201d<\/em> as an extra format parameter in the call to display_cursor()<\/strong><\/em>; conveniently this shows me where the optimizer thinks the resource costs are going to be \u2013 that little bit of information is very useful as an adjunct to the E-Rows<\/strong><\/em> information. I\u2019ve also used the \u201ciostats\u201d<\/em> option rather than the \u201callstats\u201d<\/em> option \u2013 I wouldn\u2019t normally do this but it\u2019s a way to eliminate the memory statistics which made the report much wider to show just a little extra information that the \u201csort order by\u201d<\/em> at operation 1 needed (and got) about 100KB of memory to complete as an optimal workarea operation.<\/p>\n
Before we even begin to do a comparison of estimates and actuals, we can note something that stands out as an efficiency threat in this example: the E-Rows<\/strong><\/em> in lines 5 and 4 tell us that Oracle expects to acquire 14,000 rowids from index t1_date<\/strong><\/em>\u00a0and discard all but one of the rows after using those rowids to visit table t1<\/strong><\/em> \u2013 and most of the predicted cost of the query (viz: 202 \u2013 41) comes from the work done visiting that table. The optimizer seems to think that it has to pick a very poor quality index as the best way of addressing this query. Looking at the access()<\/strong><\/em> predicate on line 5 and the filter()<\/strong><\/em> predicate on line 4, we might decide that (a) we need to extend the index that seems to exist on just\u00a0(date_ord)<\/strong><\/em> so that it becomes (date_ord, supp_id)<\/strong><\/em>, or (b) if there\u2019s an index already on (supp_id)<\/strong><\/em> we need to extend it to (supp_id, date_ord)<\/strong><\/em>, or maybe (c) create an index on (supp_id)<\/strong><\/em> \u2013 with or without date_ord<\/strong><\/em> \u2013 because it\u2019s missing and ought to exist.<\/p>\n
Having noted that we may not have the right indexes anyway, we can still go through the process of comparing actuals and estimates as this may help us towards an informed decision on the infrastructure anyway. (Sometimes you may decide that there\u2019s no point in chasing the execution statistics further until the indexing is corrected, of course.)<\/p>\n
The order of operation (i.e. the order in which rowsources are produced) for the plan is: 5, 4, 6, 3, 7, 2, 1 \u2013 so we\u2019ll consider the plan lines in that order, remembering that the E-Rows<\/strong><\/em> figure must be multiplied by the Starts<\/strong><\/em> figure before you compare it to the\u00a0A-Rows<\/strong><\/em> figure:<\/p>\n
5 \u2013 index range scan \u2013 estimated rows 14,013, starts 1; actual rows 14,000. Good prediction.
\n4 \u2013 table access by rowid \u2013 estimated rows 1, starts 1; actual rows 2,800. Bad prediction.<\/p>\n
There are two reasons why we have to worry about line 4: first, why is the prediction so bad? secondly will the prediction for this line result in a bad choice of execution plan (e.g. wrong type of joins from this point onwards or is the whole join order wrong because of this extreme low volume\/high cost prediction)?<\/p>\n
6 \u2013 index range scan \u2013 estimated rows 1, starts 2,800; actual rows 2,800. Good prediction!<\/p>\n
How did we get back to a good prediction after the terrible prediction for operation 4? The answer to that question is another question \u2013 \u201cwhy not?\u201d<\/em> Although we have to remember to multiply E-Rows<\/strong><\/em> by Starts<\/strong><\/em> before comparing it to A-Rows<\/strong><\/em>, it\u2019s not necessarily the case that a good match is a good thing. The line-by-line comparison between actuals and estimates is a local<\/strong> comparison, and a good local<\/strong> prediction isn\u2019t automatically indicative of a good\u00a0global<\/strong> strategy.<\/p>\n
In this case if we access table t2<\/strong><\/em> by the index on id<\/strong><\/em> we really will get one rowid and one one row per access with very few buffer visits \u2013 and that\u2019s what the prediction says, so it really is a good prediction. But the fact that we will have to execute (start) that operation 2,800 times is (probably) a bad thing.<\/p>\n
We don\u2019t have a column called \u201cE-Starts\u201d<\/strong><\/em> in the output, but if we did it would hold the value 1 (the value of E-Rows<\/strong><\/em> from the driving table of the nested loop join) and that would help us recognize where the important error in the prediction took place. When we look at the A-Rows<\/strong><\/em> column of a plan we need to know whether the volume of data comes from the number of rows per execution, or the number of executions.<\/p>\n
3 \u2013 nested loop \u2013 estimated rows 1, starts 1; actual rows 2,800. Bad prediction.
\n7 \u2013 table access by rowid \u2013 estimated rows 1, starts 2,800; actual rows 2,800. Good (local) prediction again.
\n2 \u2013 nested loop \u2013 estimated rows 1, starts 1; actual rows 2,800. Bad prediction.
\n1 \u2013 Sort order by \u2013 estimated rows 1, starts 1; actual rows 2,800. Bad prediction.<\/p>\n
If we summarize the state of this query plan, then: the E-Rows<\/strong><\/em> for lines 5 and 4 suggest that the optimizer knew that it didn\u2019t have a high-precision option for\u00a0accessing table t1<\/strong><\/em>. However when we consider the E-Rows<\/strong><\/em> and\u00a0A-Rows<\/strong><\/em> for line 4 we see that the optimizer had a bad<\/strong> idea of what the data in table t1<\/strong><\/em> looked like, and the entire plan from that point onwards was (probably) inappropriate because of that initial estimate.\u00a0Ignoring the initial error, every other step (join order and access method) that the optimizer dictated from that point on behaved as the optimizer expected \u2013 each of the later nested loop joins used a high precision index to identify and return a small volume of data per execution \u2013 but by that point we were already locked into something that was potentially a bad plan.\u00a0This leads us to two general points about using execution plans with rowsource execution statistics as a tool for highlighting root causes of performance problems.<\/p>\n