SQL Execution Plan

sandeep negi's photo
sandeep negi
·

4 min read

  • this is the list of steps that the database plan to execute the query

  • since we are just telling the db to do something, we are not telling it how to do this thing.

  • usually execution plan is used for select query

what you can see with execution plan

  • The step that was taken

  • The order in the overall process

  • The cost of the step

  • The reads and writes involved in the step

Simple Query

SELECT  ma1.account_id   account_id, ad.account_name, ma1.merchant_id merchant_id, ad.active_account, ac.merchant_status
          FROM SCHEMA1.LOGIN__, SCHEMA2.USER_ACCOUNT ma1, SCHEMA1.ACC_DETAILS ad
         WHERE ac.merchant_id = ma1.merchant_id
           AND ma1.account_id = ad.account_id
           AND ( (ad.account_id = ac.merchant_id || '_acct') OR (ad.account_id = ac.merchant_id) )
           AND ac.parent_merchant_id = 'testmerchantid'
           AND ac.merchant_status IN ('live');
  • here we are checking the parent merchant id in acess table which is having all the merchants in our system and then we are checking its infromation in merchant-account table which is a relationship table and then account table which contains account details

There are several columns shown here:

  • Operation: the task that is performed, such as Hash Join or Nested Loops.

  • Object Name: the name of the object (table, index) that is used in this step.

  • Options: extra attributes for this step, such as the type of table access.

  • Cardinality: the number of rows in this step.

  • Cost: a number representing the cost of this step and all steps below it.

Create execution plan and store in plan table

  • here explain plain or execution plan is stored in the tale plan_table
explain plan set statement_id ='cost1' for
    select * from employee
;

select * from PLAN_TABLE where STATEMENT_ID='cost1' and ID=0;

Terms in SQL Server Execution Plan

Table Scan

This will read all rows and columns in the table. Also called a Full Table Scan. It’s an expensive operation.

Nested Loops

Joins two tables by getting the result from one table and matching it to the other table.

Improvements to Look Out For

SQL Server Management Studio often displays a suggested index at the top of the execution plan tab. This can help you identify what improvements can be made. It doesn’t always provide the best improvement, but it’s a good place to start.

Also, any step that performs a Table Scan should be avoided by either adding an index or updating the design of the query.

Take a look at the cost percentages of each step to see which steps are taking the most of the processing time.

Understanding CrossJoin

SELECT * FROM
  tableA, tableB, tableC;

the result is a cross product of all the rows in tables A, B, C. Then you apply the restriction WHERE tableA.id = tableB.a_id which will throw away a huge number of rows, then further ... AND tableB.id = tableC.b_id and you should then get only those rows you are really interested in.

DBMSs know how to optimise this SQL so that the performance difference to writing this using JOINs is negligible (if any). Using the JOIN notation makes the SQL statement more readable (IMHO, not using joins turns the statement into a mess). Using the cross product, you need to provide join criteria in the WHERE clause, and that's the problem with the notation. You are

  tableA.id = tableB.a_id 
AND tableB.id = tableC.b_id

which is only used to restrict the cross product. WHERE clause should only contain RESTRICTIONS to the resultset. If you mix table join criteria with resultset restrictions, you (and others) will find your query harder to read. You should definitely use JOINs and keep the FROM clause a FROM clause, and the WHERE clause a WHERE clause.

When does execution plan changes,

  • plan change can occur due for a variety of reasons including but not limited to the following types of changes occurring in the system: optimizer version, optimizer statistics, optimizer parameters, schema/metadata definitions, system settings, as well as SQL profile creation. Sometimes a change in the system (e.g. drop an index) causes an irreversible plan change. Not being able to guarantee a plan will change for the good has caused some customers to freeze their execution plans or their optimizer statistics.

  • when the data is change in db ->execution plan will change.

how to make sure, we always run the most cost effective execution plan

In Oracle Database 11g a new feature SQL Plan Management (SPM) is introduced, which provides controlled execution plan evolution. With SPM, the optimizer automatically manages execution plans and ensures only known or verified plans are used. When a new plan is found for a SQL statement it will not be used until it has been verified to have comparable or better performance than the current plan.

how does optimizer chooses the execution plan

  • it takes the parsed representation of SQL statements and Statistics to generate the exeuction plan with lowest cost.

  • during this optimzer generates multiple execution plan and compares them

  • Execution plan can be changed based on parsed sql statement and statistics

  • Even the schema and database is same but the Cost of the execution plan is different then also the optimizer can choose different execution plans.

on which parameters cost of sql query depends upon

how to gather statistics in db

Databasesexecution planoptimization