Analyze the Explain Plan

Prathap G
A free video tutorial from Prathap G
Technical Enterprise Architect
4.4 instructor rating • 3 courses • 11,106 students

Learn more from the full course

Oracle SQL : Mastering Oracle SQL Performance Tuning

Oracle SQL Query Tuning and Architecture | Solve critical Oracle SQL query performance issues | Advanced SQL Tuning

06:26:10 of on-demand video • Updated October 2019

  • Step by Step approach on troubleshooting badly performing SQL queries.
  • Identify how internal Oracle components affects the performance of the SQL query.
  • Identify potential performance bottlenecks by reading the Explain plan of the queries.
  • Tips and Tricks to make the Oracle SQL query run faster.
  • Compare BAD vs GOOD Queries with Examples.
  • Understand how STATISTICS and CARDINALITY affects the performance of the SQL Query.
  • Implications of JOINS and ACCESS Methods on the performance of the Query.
  • Oracle Performance Tuning on both Oracle11g and Oracle12c Environment.
  • Advanced SQL Query Tuning Steps.
English Hey Guys, Welcome back In the last few lessons we learnt about fundamentals of EXPLAIN PLAN and also learnt how to view the EXPLAIN PLAN, but we never understood how to interpret the Explain PLAN. This is one of the important lesson, where we will learn how to interpret and analyze the Execution plan of the Query In this Lesson, I will uses this query as example. We can see from the query that I am joining 4 Tables namely SALES_BKP , CUSTOMERS , PRODUCTS and CHANNELS using EQUI Join conditions, also we see that that I have included filter predicates on SALES_BKP and CHANNEL tables,where I am filtering records with PRODID = 13 and CHANNEL_ID = 3. Here is an image of the execution plan for this Query. The operations you see in an execution plan can be categorized into 2 main types Those that access or retrieve data (Index Search , table Search , Filter Conditions), and those that manipulate data retrieved by another operation (Like Joins , SORT , GROUP BY Etc).. Although, we see the plan in a tabular form, an execution plan is actually a tree, which is traversed from top-down, left-to-right The image that you are seeing is a textual representation of the Execution Query But, the same could be displayed in a Tree structure as shown in the SQL DEVELOPER Session here Here is how this plan would look like if it was displayed as a tree As you probably deduced from the images,the operations are displayed in the opposite order of which they are performed In the case of this tree, the data is read from the appropriate indexes first (the leaves of the tree). In this case, data from the CHANNELS and PRODUCT are read using the INDEX CHANNELS_PK and PRODUCTS_PK, and when both result sets are ready, they are joined together. in this case it is performing NESTED LOOP JOIN operation. if you are wondering what is NESTED LOOP Join then don't worry, It is one of the JOIN type that the oracle has used to perform JOIN Operation We will look more into it in next few lessons In the case of the tabular representation, the operations more deeply indented are the ones executed first So, there is lot of importance on the indentation of the operators as they decide the order,in which the tasks were executed by the operators. So data that is most indented should be read first. In this case, data from the CHANNELS and PRODUCT are read using the INDEX CHANNELS_PK and PRODUCTS_PK, and when both result sets are ready, they are joined together. in this case it is performing NESTED LOOP JOIN operation. The first ROW in the PLAN is the final operator that is being evaluated, so the cost associated with the first row, is the is the total cost for the statement or ccumulative costof all the operators under it So, in this case the COST of the statement is 1651. and is marked to the TOP Operator namely SELECT in this case. In general, the cost displayed for each operation accounts for the sum of the costs of its children. In this example, as you can see from the image the TABLE ACCESS for the CHANNELS table and TABLE ACCESS for the PRODUCTS are the direct children of the NESTED LOOP, so, since the table access for Channel has a cost of 1 and table access for the Products has cost of 1, the NESTED LOOP has a cost of 2 This is very important guys, as most of the new comers and even some experienced Developers failed to understand this COST distribution Factor.i.e.COST of an operator is always the SUM of the COSTS of all the CHILD Operator's. If that CHILD OPERATOR do not have any CHILDERN further then the COST of the last operator or Leaf level operator is the cost of that last child element. I would recommend you to pause the Video for moment and Understand the concepts if it is very confusing. From the Tree View here, we see that some operations don't have any cost, they are just additional informational details about the operation they are part of (in the case of the plan in the image, the access and filter predicates are just details about the TABLE ACCESS operators. These do not incur any COST, but rather used by the OPTIMIZER to get the data it requires. The operations that access or retrieve data are those that have an associated object name, which is the source from where they retrieve the data (a table, an index, etc). In the case of the plan in the image, the objects from which data is being retrieved are the CHANNELS, PRODUCTS , SALES_BKP , CUSTOMERS tables, and the CHANNELS_PK and PRODUCTS_PK index. The cardinality column shows an estimation of the number of rows that will come out of each of the operations, so in this case, the optimizer estimated that 1 rows would be retrieved from the PRODUCTS and THE CHANNEL table, 3190 from the SALES_BKP table, 55500 from the CUTOMERS table, to produce a final result set of 3190 rows. So, if you are finding it difficult to Understand the Explain Plan of the query then don't worry. I will tell you an easy way of interpreting the Execution Plan. For that you need to have a Paper and a pen and then Execution Plan(Text Format) in front of you Let us look at the Execution Plan and we do TOP-DOWN ANAYSIS (But note this is not the order in which Oracle Executes the query). So what is the TOP MOST operation in the Execution PLAN...It is SELECT(As it is the Least Indented) and it is the first operator in the EP... So I will make a Note of it. Lets us now check the CHILD operators under SELECT operator. How do you check this? You have to find the next indented operators under the SELECT operator. In this case, we have only 1 Operator namely HASH JOIN. So place it under SELECT...Also note there isn't any other OPERATORS other than the HASH JOIN at the same level. So, there is only 1 operator HASH under Select. So, what is under HAS JOIN? Again using the same logic of indentation, we could see that we have two operators namely NESTED LOOPS and TABLE ACCESS FULL (CUSTOMERS) table... Let me list them down.... Since now we have two operations at the same level, I have to perform the same activity for both the Operators. So, first let me select TABLE ACCESS FULL on Customers, and you see that there isn't any operators under it. So, we can simply Ignore the BRANCH. Lets now look at the other Operator namely NL Join, again we see that it has 2 Operators under it and both of them are at same level (They ARE NESTED LOOP and TABLE ACCESS FULL on SALES_BKP ) table. Since now we have two operations at the same level, I have to perform the same activity for both the Operators... So, first let me select TABLE ACCESS FULL on SALES_BKP and you see that there isn't any operators under it. So, we can simply Ignore the BRANCH. Lets now look at the other Operator NL Join, we see that it has 2 Operators under it namely TABLE ACCESS BY INDEX ROWID (Channels table) and TABLE ACCESS BY INDEX ROWID (PRODUCTS table). let us Now look at the First Child TABLE ACCESS BY INDEX ROWID (Channels table) and we see that there is only 1 CHILD called INDEX UNIQUE SCAN (CHANNELS_PK) under it.. On the Other Branch we see that there is only 1 child under TABLE ACCESS BY INDEX ROWID (PRODUCTS table) and it is INDEX UNIQUE SCAN (PRODUCTS_PK). Let us now lookat the ID COLUMN in the Execution Plan... These are justUNIQUE_ID provided for each operator and it doesn't represent the order in which the QUERY is run. Keep this in Mind. I have seen people thinking it as the order in which the tasks are run by the OPTIMIZER. But it is NOT, it is just an Unique ID assigned to Each Operator. We will MARK the ID's in the TREE STRUCTURE by looking at the Query PLAN, We see here that SELECT STATEMENT HAS ID = 0 , so lets mark it above SELECT OPERATOR. Similarly , HASH JOIN hAS ID = 1 , so lets mark it above HASH JOIN. Within HASH JOIN, we see there are 2 OPERATORS and the ID's of it is 2 and 9. using the same Logic, i will now match ID's with all the OPERATORS. Now, let us Look the COST value of EACH operator and mark it in the same way, i marked for ID. we see here that SELECT STATEMENT HAS COST= 1651 , so lets mark it next to SELECT OPERATOR. Similarly , HASH JOIN hAS a cost of 1651, so lets mark it next to HASH JOIN. Within HASH JOIN, we see there are 2 OPERATORS and the cost of it is 1245 and 405. using the same Logic, i will now match COST with all the remaining OPERATORS. One important thing to NOTICE here is that COST of ANY NODE is the SUMMATION of the COST of its immediate Children + cost of the OPERATOR Itself. Lets Look at this example The COST of HASH JOIN in 1651 and its values are derive using the SUMMATION of the costs of its children, which is 1245 and 405. We have now seen how the COST factor works. let us now look at the NAME sections (Which is the object on which the operations was performed... It could be table name , Index name or any other oracle object). We see from the Explain pLan that these exists only on those operators where access data or FIlter data... So based on the ID COLUMN we can track them and assign to corresponding operator... Ex: If we see ID = 4, where we are accessing OBJECT NAME (CHANNELS, which is a table), lets mark it in a Graph. let us do the same for ID 5, where we access INDEX object namely CHANNELS_PK, Let me mark it this as well... let me Now mark all the Objects with the corresponding Operators. The Last section which we need to add is the PREDICATE INFORMATION that exists below the execution PLAN... There are 2 Types of PREDCICATES namely ACCESS predicate and FILTER predicate. 1) ACCESS PREDICATE: It is a Predicate that is used to access the corresponding data from the data base. Generally , we see this under INDEX and JOIn Operation. Where it tells how to access the data to perform the Operation. More importantly it has information about the ID that it used. In the EX, we see that ACCESS SH.CUST_ID = cust.CUST_ID (which is a JOIn condition) is used with the OPERATOR 1, and we see that the OPERATOR 1 is HASH JOIN and hence lets mark under it. If we look at the next ACCESS CAHNNEL_ID = 3 is used with OPERATOR 5, and we see that the OPERATOR 5 is INDEX UNIQUE SCAN on a INDEX on CHANNELS table.hence lets mark under it. SImilarly ,let me mark the 3rd access predicate. The NEXT predicate is FILTER predicate, which is used to filter the records and this is the stage at which the data will be filtered. In this example we see that the filter PROD_ID = 13 and CHANNE:_ID = 3 is used with the operator 8 . So, let me mark the data in the Graph. Guys, I now hope that you have understood how to read the Execution Plan in a TREE structure and how to attach all the elements to a tree. But, In reality the operations are performed in the reverse order that is using Bottom to TOP approach. Let us Look at the TREE stricture. First the data from the INDEX is scanned on the CHANNELS and the PRODUCTS table. Once the INDEX DATA is scanned all the other necessary column data that did not exist in the INDEX is fetched from the table using the Index ROWID on Both the CHANNELS and PRODUCT tables. The data from the CHANNELS and the PRODUCT tables is then JOINED together using an OPERATOR called NESTED LOOP JOIN and creates another dataset. Now the data from the SALES_BKP table will be read and this data will be joined with the dataset created in the Previous step using NESTED loop JOIN again, which again produces different data set. This DATASET is again joined with the TABLE CUSTOMER(from which the data is read). The JOIN operator used this time HASH JOIN. This HASH JOIN creates a data set. and FROM this DATASET the SELECT operator selects the data and provides the result set back to the CLIENT or the USER. and this is how you need to interpret the execution plan. Guys, I know that there are too many things here to digest, particularly if you are going through this for first time. So, if you feel that you need little bit more time to understand it then don't worry. I would request you the PAUSE down the Video and open a notebook and try to right down the TREE structure or your own, if you are stuck in between then play back the Video and clarify all the doubts. Understanding the EXECUTIOn PLAN is very important in debugging the PERFORMNACE issues of the QUERY. Since, now you understood how to interpret the Explain Plan, its time for us to understand the OPERATORS used by the OPTIMIZER. In the Next few lessons we will see various operators used in the EXECUTOIN PLAN and understand more about them. With that let me wrap this session and see you in next lesson.