{"id":73190,"date":"2014-08-19T12:00:56","date_gmt":"2014-08-19T12:00:56","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/uncategorized\/execution-plans-part-8-cost-time-etc\/"},"modified":"2021-07-14T13:07:30","modified_gmt":"2021-07-14T13:07:30","slug":"execution-plans-part-8-cost-time-etc","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/oracle-databases\/execution-plans-part-8-cost-time-etc\/","title":{"rendered":"Execution Plans Part 8: Cost, time, etc."},"content":{"rendered":"

It\u2019s time to move away from the shape<\/strong><\/em> of an execution plan and turn our attention to some of the numerical information we get from the plan. In this article we\u2019re going to look only at the predictions that the optimizer makes (explain plan<\/strong><\/em>), postponing any investigation of actual run-time figures (v$sql_plan_statistics_all<\/strong><\/em>) for future instalments.<\/span><\/p>\n

Getting Started<\/h2>\n

As a reference point we\u2019re going to start with a very simple query and plan (from an instance of 11.2.0.4):<\/p>\n

explain plan for\r\nselect\r\n        t1.id, t2.id\r\nfrom\r\n        t2, t1\r\nwhere\r\n        t2.n1 = 15\r\nand     t1.n1 = t2.n2\r\n;\r\n\r\nselect * from table(dbms_xplan.display(null, null, 'projection'));\r\n\r\n--------------------------------------------------------------------------------------\r\n| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |\r\n--------------------------------------------------------------------------------------\r\n|   0 | SELECT STATEMENT             |       |   225 |  4500 |    35   (6)| 00:00:01 |\r\n|*  1 |  HASH JOIN                   |       |   225 |  4500 |    35   (6)| 00:00:01 |\r\n|   2 |   TABLE ACCESS BY INDEX ROWID| T2    |    15 |   180 |    16   (0)| 00:00:01 |\r\n|*  3 |    INDEX RANGE SCAN          | T2_I1 |    15 |       |     1   (0)| 00:00:01 |\r\n|   4 |   TABLE ACCESS FULL          | T1    |  3000 | 24000 |    18   (6)| 00:00:01 |\r\n--------------------------------------------------------------------------------------\r\n\r\nPredicate Information (identified by operation id):\r\n---------------------------------------------------\r\n   1 - access(\"T1\".\"N1\"=\"T2\".\"N2\")\r\n   3 - access(\"T2\".\"N1\"=15)\r\n\r\nColumn Projection Information (identified by operation id):\r\n-----------------------------------------------------------\r\n   1 - (#keys=1) \"T2\".\"ID\"[NUMBER,22], \"T1\".\"ID\"[NUMBER,22]\r\n   2 - \"T2\".\"ID\"[NUMBER,22], \"T2\".\"N2\"[NUMBER,22]\r\n   3 - \"T2\".ROWID[ROWID,10]\r\n   4 - \"T1\".\"ID\"[NUMBER,22], \"T1\".\"N1\"[NUMBER,22]<\/pre>\n
If you’ve worked through the previous instalments in this series you’ve probably worked out very quickly that the run-time order of operation for this plan is 3, 2, 4, 1, 0: we do an index range scan (line 3) to get data from table t2<\/strong><\/em> (line 2) to build an in-memory hash table, then do a full scan of table t1<\/strong><\/em> (line 4) to probe the hash table for matching data (line 1) passing the result on to the end user (line 0). What we\u2019re going to spend time on in this article is interpreting the numeric data \u2013 the columns headed Rows, Bytes, Cost (%CPU), and Time.<\/p>\n
A key point to remember when looking at the predictions made by explain plan is that the numbers on a line of a plan describe one execution of that line (the same is not true when you look at the row source execution statistics from the view v$sql_plan_statistics_all); a second, equally important, point is that the Cost and Time figures accumulate upwards from child operation to parent operation.<\/p>\n
A final, less helpful, point is that there are plenty of defects that make it hard to produce an accurate interpretation of the numbers \u2013 rounding blurs the issue, some figures are missing, some numbers appear on the wrong line, some of the numbers in the plan are not consistent with the numbers you can find in the corresponding 10053 traces. For various reasons you\u2019re likely to find cases (and I will be showing you some very simple cases in later instalments) where the description I give seems to be broken around the edges.<\/p>\n
Note: In this example I’ve included the “projection” information from the plan table in the output; that\u2019s not something I usually do but I’ll be talking about it later on in the worked example.<\/p>\n
Basic explanation<\/h2>\n
Here\u2019s a quick summary of the columns:<\/p>\n\n\n\n\n\n\n
Rows<\/td>\nThe predicted number of rows supplied to its parent by one execution of this operation<\/td>\n<\/tr>\n
Bytes<\/td>\nThe predicted number of bytes (rows x expected row size) supplied to its parent by one execution of this operation<\/td>\n<\/tr>\n
Cost<\/td>\nThe prediction of resources required for one execution of this operation \u2013 including the resources required for every execution of every descendent of this operation that would be needed for this operation to complete once. The figure in parentheses (%CPU) is the fraction of the resource usage that can be attributed to CPU activity. As we will see below, there are two ways of interpreting the cost.<\/td>\n<\/tr>\n
Time<\/td>\nThe predicted elapsed time (hours:minutes:seconds) required to execute this operation just once. As with cost, the time for a single execution of the operation includes the time spent in all the executions of all the child operations needed to complete one execution of this operation.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
 <\/p>\n
I\u2019ve been a bit heavy-handed with my use of the word \u201cpredicted\u201d<\/em> and the phrase \u201cone execution\u201d<\/em>\u00a0but it\u2019s important to remember that predictions can be (often are) wrong; and that part of the prediction the optimizer makes – and it’s a part that is sometime hidden – is \u201chow many times will this operation execute\u201d<\/em>, so the figures for one execution of one operation become an important aspect of understanding the totals for the whole plan.<\/span><\/p>\n
I made the point that there are two ways of interpreting the \u201ccost\u201d column. I’ve argued for many years that the optimizer\u2019s cost figure represents the predicted time to run: (see, for example, \u201cCost is time\u201d<\/em><\/strong><\/a>). If you take the cost figure from the execution plan output, multiply it by the single block read time (sreadtim<\/strong><\/em>) from the system statistics table (aux_stats$<\/strong><\/em>) then \u2013 allowing for rounding \u2013 the answer you produce will be the time reported in the execution plan. Alternatively, if you don\u2019t want to believe that cost represents time, you could interpret the cost figure, informally, by saying \u201cthe resource impact of this query is equivalent to this many (real) single block random reads\u201d<\/em>.<\/span><\/p>\n
Example<\/h2>\n
With that background, let\u2019s apply what we’ve learned to the example at the start of the article. It\u2019s a particularly easy example to start with because each line of the plan executes exactly once. As we saw earlier, the order of operation is: line 3, line 2, line 4, line 1, line 0<\/span><\/p>\n