We\u2019ll start with a simple example \u2013 building a couple of tables, joining them, and then reviewing the questions we need answered when we examine an execution plan. So here\u2019s the data creation script:<\/p>\n
\r\n\r\ncreate table t1\r\nas\r\nwith generator as (\r\n\tselect\t--+ materialize\r\n\t\trownum id \r\n\tfrom dual \r\n\tconnect by \r\n\t\tlevel <= 1e4\r\n)\r\nselect\r\n\trownum\t\t\tid,\r\n\tmod(rownum,1000)\tn_1000,\r\n\tlpad(rownum,6,'0')\tv1,\r\n\trpad('x',100,'x')\tpadding\r\nfrom\r\n\tgenerator\r\n;\r\n\r\nalter table t1 add constraint t1_pk primary key(id);\r\ncreate index t1_i1 on t1(n_1000);\r\n\r\nbegin\r\n\tdbms_stats.gather_table_stats(\r\n\t\townname\t\t => user,\r\n\t\ttabname\t\t =>'T1',\r\n\t\tmethod_opt\t => 'for all columns size 1'\r\n\t);\r\nend;\r\n\/\r\n\r\n<\/pre>\nI\u2019ve created a table t2<\/strong><\/em>\u00a0in exactly the same way \u2013 so I won\u2019t repeat the code. I\u2019m going to use explain plan<\/strong><\/em>\u00a0with a simple \u201cliteral string\u201d SQL statement as my first example to introduce a couple of points about execution plans.<\/p>\nexplain plan for\r\nselect\r\n\tt1.v1, t2.v1\r\nfrom\r\n\tt1, t2\r\nwhere\r\n\tt1.n_1000 = 1\r\nand\tt2.id = t1.id\r\nand\tt2.n_1000 = 100\r\n;\r\n\r\nselect * from table(dbms_xplan.display);\r\n\r\nselect\r\n\tid, parent_id, position,\r\n\tdepth, level \u2013 1 old_depth,\r\n\trpad(' ',level - 1) ||\r\n\t\toperation || ' ' ||\r\n\t\tlower(options) || ' ' ||\r\n\t\tobject_name\t\t\ttext_line\r\nfrom\r\n\tplan_table\r\nstart with\r\n\tid = 0 \r\nconnect by \r\n\tparent_id = prior id\r\norder siblings by\r\n\tid, position\r\n;<\/pre>\nI\u2019ve used two different methods for producing the execution plan \u2013 a basic call to dbms_xplan.display()<\/strong><\/em>,\u00a0and a simplified version of the type of query we used to use on the plan table in version 9 and earlier. Here are the two sets of results:<\/p>\n--------------------------------------------------------------------------------------\r\n| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |\r\n--------------------------------------------------------------------------------------\r\n| 0 | SELECT STATEMENT | | 10 | 300 | 22 (0)| 00:00:01 |\r\n|* 1 | HASH JOIN | | 10 | 300 | 22 (0)| 00:00:01 |\r\n| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 10 | 150 | 11 (0)| 00:00:01 |\r\n|* 3 | INDEX RANGE SCAN | T1_I1 | 10 | | 1 (0)| 00:00:01 |\r\n| 4 | TABLE ACCESS BY INDEX ROWID| T2 | 10 | 150 | 11 (0)| 00:00:01 |\r\n|* 5 | INDEX RANGE SCAN | T2_I1 | 10 | | 1 (0)| 00:00:01 |\r\n--------------------------------------------------------------------------------------\r\n\r\nPredicate Information (identified by operation id):\r\n---------------------------------------------------\r\n 1 - access(\"T2\".\"ID\"=\"T1\".\"ID\")\r\n 3 - access(\"T1\".\"N_1000\"=1)\r\n 5 - access(\"T2\".\"N_1000\"=100)\r\n\r\n Id Par Pos DEPTH OLD_DEPTH TEXT_LINE\r\n---- ---- ---- ----- --------- --------------------------------------------------\r\n 0 22 0 0 SELECT STATEMENT\r\n 1 0 1 1 1 HASH JOIN\r\n 2 1 1 2 2 TABLE ACCESS by index rowid T1\r\n 3 2 1 3 3 INDEX range scan T1_I1\r\n 4 1 2 2 2 TABLE ACCESS by index rowid T2\r\n 5 4 1 3 3 INDEX range scan T2_I1<\/pre>\nThe reason I\u2019ve included the old-style approach to reporting an execution plan is to allow you to connect the visual presentation with some of the details in the plan table that are hidden by the modern approach. We always see execution plans with some sort of pattern of indentation, and this pattern is supposed to tell us something about the relationship between operations in the plan. What it\u2019s showing us is a visual impression of the relationship between the id<\/strong><\/em>, the parent_id<\/strong><\/em>, and the position<\/strong><\/em> columns.<\/p>\nEach operation in the plan has an id<\/strong><\/em>; this actually tells us the order in which lines should be reported. Each line may be the parent of one or more \u201cchild\u201d operations, and the parent_id<\/strong><\/em> column of each line will hold the id<\/strong><\/em> of its parent. In this example we see that lines 2 and 4 both have line 1 as their parent. Where a line has several child operations the position<\/strong><\/em> column will list the order of child operation within parent; looking again at lines 2 and 4 we see that line 2 is the first child of line 1, and line 4 is the second child of line 1. In the dbms_xplan<\/strong><\/em> presentation of an execution plan we don\u2019t see the parent_id<\/strong><\/em> and position<\/strong><\/em> columns, we have to infer the parent\/child relationships from the ordering and indentation of the various operations.<\/p>\nListing the lines in order of id<\/strong><\/em> has always given us the correct order of presentation of the plan (which is NOT the same as the order in which data is acquired and manipulated); but the method for calculating the level of indentation changed between 9i and 10g. Historically the derived column level<\/strong><\/em> from the hierarchical connect by<\/strong><\/em> query allowed us to add a suitably sized indent to the text \u2013 but when Oracle allowed us to access the in-memory version of execution plans (v$sql_plan<\/strong><\/em>) this coding strategy turned out to be horrendously inefficient so a pre-calculated level column (differing by 1 from the level<\/strong><\/em>, and called depth<\/strong><\/em>) was included in the dynamic performance view, and eventually added to the plan table, with its value being derived at the time of optimization. (Unfortunately there are still a few cases \u2013 even in 12c \u2013 where the generated value is incorrect so it\u2019s worth remembering how to write the connect by<\/strong><\/em> query.)<\/p>\nFirst Rule for Reading Plans<\/h2>\n
We\u2019re going to ignore the predicate section in this article \u2013 even though it\u2019s a very important part of an execution plan \u2013 and focus on how to walk through the body of the execution plan to understand the order in which Oracle will acquire and manipulate data.<\/p>\n
Each line of a plan represents an operation that generates a set of \u201crows\u201d \u2013 called a “rowsource”<\/em>. I have put the word rows in quotes because, for example, a \u201crow\u201d may be nothing more than a rowid<\/strong><\/em> fetched from an index. An operation takes some action to generate a rowsource and passes the rowsource to its parent. If a parent operation has multiple child operations, it will (optionally) call each of its children in turn to supply a rowsource and then do some work to combine those rowsources. One of the most important things you have to learn is what each operation does, and what it means for that operation to \u201ccombine\u201d rowsources. There\u2019s also the slight complication that although a parent calls its children \u201cin turn\u201d, it may call each child more than once, and the way in which the repeated calls are made varies with parent operation.<\/p>\nInevitably this description doesn\u2019t cover all the variations and anomalies, but if we ignore the special cases for a while the basic method for reading execution plans can be summed up in the soundbite: \u201cfirst child first, recursive descent\u201d<\/em>. Let\u2019s take a look at the plan for the hash join above to see how this works.<\/p>\nLine zero tells us that we have a select statement. To generate the rowsource for this select statement we have to identify its children and operate them in order.\u00a0The old-style code for reporting an execution plan tells us that line 1 is the first (and only) child of line 0. But if we didn\u2019t have the parent_id<\/strong><\/em> and position<\/strong><\/em> to help us we could use the visual approach: the first child of an operation is the next line in the plan (and it will be indented one step), and you can find any subsequent children \u2013 in order \u2013 from there by dropping a vertical line and picking up any lines at the same indentation until you hit the bottom of the plan or a line that has moved back towards the left margin.<\/p>\nLine 1 reports a hash join operation and at this point we may need to look at the manuals to discover what a hash join is, and how it works, before we start looking for the child operations – conveniently some of the graphic tools for generating execution plans will have a pop-up, or hover, option if you need to check. Again the old-style report makes it easy to see that lines 2 and 4 are the children of the hash join operation while the visual approach tells us that line 2 is the first child and a vertical drop from line 2 hits the T of \u201ctable access\u201d at line 4, identifying it as the second child. This is enough information to tell us that we\u2019re going to build an in-memory hash table from some rows from t1<\/strong><\/em> (first child) and probe that hash table with rows from t2<\/strong><\/em> (second child) to find matches then, when we find a match, construct a result row and return it to line zero (the parent of line 1). The serial hash join is a good example of why we have to consider the children in order \u2013 the physical ordering in the plan tells us which table is the build table and which the probe.<\/p>\nIt\u2019s worth noting at this point that we don\u2019t yet know (or care) how we manage to identify the rows from t1<\/strong><\/em> and t2<\/strong><\/em> \u2013 all we\u2019ve done is pick out part of the plan at the highest level as an initial step in understanding the total work load. We haven\u2019t yet got to a point where we can say: \u201cthis is the first data set that Oracle acquires \/ this is the first table that Oracle visits\u201d. But we can get to that point by repeating the approach we\u2019ve taken so far.<\/p>\nWe\u2019re going to build an in-memory hash table with the rowsource from line 2, and probe the hash table with the rowsource from line 4; so let\u2019s examine the first child first. Starting from line 2 we can identify the entire \u201csub-plan\u201d whose end-product is the rowsource we need; here it is:<\/p>\n
--------------------------------------------------------------------------------------\r\n| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |\r\n--------------------------------------------------------------------------------------\r\n| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 10 | 150 | 11 (0)| 00:00:01 |\r\n|* 3 | INDEX RANGE SCAN | T1_I1 | 10 | | 1 (0)| 00:00:01 |\r\n--------------------------------------------------------------------------------------<\/pre>\nViewed in isolation we can see that line 2 has a single child to call, and the operation in that child is an index range scan. Conveniently we realize that the rowsource produced from an index range scan is likely to contain rowids, and that line 2 is doing a table access by rowid – sanity checks like this that compare the demands of the parent with the supply from the child are very useful when trying to understand more subtle execution plans.<\/p>\n
Similarly we can look at the sub-plan that delivers the rowsource for line 4<\/p>\n
--------------------------------------------------------------------------------------\r\n| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |\r\n--------------------------------------------------------------------------------------\r\n| 4 | TABLE ACCESS BY INDEX ROWID| T2 | 10 | 150 | 11 (0)| 00:00:01 |\r\n|* 5 | INDEX RANGE SCAN | T2_I1 | 10 | | 1 (0)| 00:00:01 |\r\n--------------------------------------------------------------------------------------<\/pre>\nAgain we see it\u2019s a very simple plan, we call line 5 to do an index range scan that supplies the rowids that we use to visit the table in line 4.<\/p>\n
Putting all the pieces together we can number the steps of the plan as follows:<\/p>\n
------------------------------------------------------\r\n| Id | Operation | Name | Order |\r\n------------------------------------------------------\r\n| 0 | SELECT STATEMENT | | 6 |\r\n|* 1 | HASH JOIN | | 5 |\r\n| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 2 |\r\n|* 3 | INDEX RANGE SCAN | T1_I1 | 1 |\r\n| 4 | TABLE ACCESS BY INDEX ROWID| T2 | 4 |\r\n|* 5 | INDEX RANGE SCAN | T2_I1 | 3 |\r\n------------------------------------------------------<\/pre>\nWe read the plan as follows:<\/p>\n
\n- Line 0 calls line 1 (first child)<\/span><\/li>\n
- Line 1 calls line 2 (first_child)<\/span><\/li>\n
- Line 2 calls line 3 (first child)<\/span><\/li>\n
- Line 3 produces a row source \u2013 the first<\/strong> line to do so \u2013 by doing an index range scan, and passes it up to line 2<\/span><\/li>\n
- Line 2 uses the rowids to visit table t1<\/strong><\/em> producing a rowsource \u2013 the second<\/strong> line to do so \u2013 of columns from table t1<\/strong><\/em> and passes if up to line 1<\/span><\/li>\n
- Line 1 uses the rowsource to build an in-memory hash table (no new rowsource produced yet), then calls line 4 (second child) to start supplying a rowsource that can be used as the probe table.<\/span><\/li>\n
- Line 4 calls line 5 (first child)<\/span><\/li>\n
- Line 5 produces a row source \u2013 the third<\/strong> line to do so \u2013 by doing an index range scan, and passes it up to line 4<\/span><\/li>\n
- Line 4 uses the rowids to visit table t2<\/strong><\/em> producing a rowsource \u2013 the fourth<\/strong> line to do so \u2013 of columns from table t2 and passes if up to line 1<\/span><\/li>\n
- Line 1 probes the in-memory hash table, looking for matches, producing the fifth<\/strong> rowsource from any survivors, and passing them up to line 0 \u2013 which passes the result to the client program.<\/span><\/li>\n<\/ul>\n
There\u2019s more to the plan than this \u2013 in particular we need to think a little more about the detailed timing of operations: some are \u201cbulk\u201d-processing some are \u201csingle row\u201d-processing; and we need to bring in the predicate section and consider the relevance of the terms \u201caccess\u201d and \u201cfilter\u201d; and those details are what we’ll be looking at in the next article.<\/p>\n
Closing thoughts<\/h2>\n
I\u2019d like to emphasize the convenience of breaking complex execution plans down into simple pieces. Our example was so small that the potential benefit of handling it in pieces wasn\u2019t obvious; but think about how we took the whole plan, picked out the high-level view from the first few lines, and then examined a couple of sub-plans. We can do this with any plan, no matter how complex it seems to be, and examine small sections of a plan in isolation.<\/p>\n
Most (if not all) the various graphic tools that you can get to display execution plans help us in this approach; here, for example is a partial screenshot from an OEM display showing the execution plan from 11.2.0.4 for the query:<\/p>\n
select \r\n\towner, object_type, count(*)\r\nfrom\r\n\tdba_objects \r\ngroup by \r\n\towner, object_type \r\norder by \r\n\towner, object_type<\/pre>\nNote particularly the little triangular markers holding the minus signs at the left hand end of each line of the plan:<\/p>\n
<\/p>\n
![\"jpl_xplan_full\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/oracle\/2014\/04\/jpl_xplan_full.jpg\")
<\/a><\/p>\nDescending through the first child rule, we see that it\u2019s a select statement, that calls a sort group by, that calls a view (we\u2019ll discuss that operation in a future article), that calls a union-all that has three children that we will have to call in order. Click on the minus by the union-all and all its children and their descendants disappear, and the minus will turn to a plus; click on the plus and its child lines will re-appear (without their descendants), giving us this:<\/p>\n