With all this information, we can better understand how the optimizer is going to work with the objects created within the database in order to resolve a query, and in turn how we might tune that query to help the optimizer find a more efficent path to returning the data.Ĭonsider the query in listing 1, for example: SQL Server also assigns an estimated cost to each operation in the plan, allowing us to see at a glance the likely “hot spots”. The execution plan will show you exactly how, and if, those objects are in use. Most queries that involve any type of data manipulation could hit multiple objects and pieces of code. It will show how aggregations were performed, how calculated columns were derived, how and where foreign keys were accessed, and so on. It will show you which tables and indexes were accessed, in which order, how they were accessed (seek or scan), what types of joins were used, how much data was retrieved initially, and at what point filtering and sorting occurred.

It will reveal which objects a query uses, within the database, and how it uses them. The execution plan for a query is your view into the SQL Server query optimizer and query engine. In short, you’ll have more information to work with, and it will make you a better database developer. You’ll understand which objects are in use within the database you’re coding against. You’ll understand how SQL Server is dealing with your attempts at code reuse within T-SQL. If you can read and understand execution plans then you can better understand the code generated by your ORM tool. Frequently, any problems created by the query will be readily apparent within the execution plan. Most queries can be logically understood within the context of the execution plan. Sometimes, you will be surprised by what they reveal, even for the most innocuous-looking query. Why Developers Need to Understand Execution Plans - Simple TalkĪn execution plan is a visual representation of the operations performed by the database engine in order to return the data required by your query.

Pl sql developer explain plan