1 / 12

Know What EXPLAIN PLAN in SQL Performance Tuning

The EXPLAIN PLAN query is designed to show all the execution plans that the SQL optimizer picks. These plans are useful for SQL performance tuning and are picked for the four major data manipulation statements (SELECT, INSERT, UPDATE, and DELETE).

TosskaTechnologies
Download Presentation

Know What EXPLAIN PLAN in SQL Performance Tuning

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Tosska Technologies is a company that focuses in providing solutions for database and SQL related performance optimization and improvements. Our mission is to help users to smooth out the hurdle by our new technologies. Indeed Tosska is one of the very few companies in the world that focus in using artificial intelligence technology to solve various database performance problems. It is our goal to help our customers to reduce their hardware investment; increase their database applications service level. www.tosska.com

  2. Know What EXPLAIN PLAN means in SQL Performance Tuning www.tosska.com

  3. The EXPLAIN PLAN query is designed to show all the execution plans that the SQL optimizer picks. These plans are useful for SQL performance tuning and are picked for the four major data manipulation statements (SELECT, INSERT, UPDATE, andDELETE). A query execution plan is the series of tasks that the query requires to execute in the database. At the core ofit liesthe row source tree and the planning tablethat display certaindetails. www.tosska.com

  4. What Query Execution Plans Include in MySQL Database and SQL Given below is the list of all the information you can view in the row source tree for MySQL database and SQL: ❖A list of the tables the query includes mention of ❖Data processes such as sorting, filtering, and clustering ❖Join ops in the statement and their effects on the join method present in the table ❖The access techniques that are in use for every table the query references www.tosska.com

  5. www.tosska.com

  6. What Query Execution Plans Include in MySQL Database and SQL Aside from the row source tree data, there are also details the planning table has to offer. These include the following: ❖Optimization Ensuring that every operation takes minimal cost and cardinality ❖Partitioning: Details about the partitions the database is gaining access to ❖Parallel execution: In what manner is the database distributing the join inputs. www.tosska.com

  7. www.tosska.com The outcome of the EXPLAIN PLAN query enables the user to find out if the optimizer picks a plan in particular. Through these results, the user can also comprehend how it makes some choices during SQL performance tuning. For instance, you will know why it preferred a nested-loop join over a hash one.

  8. SQL Performance Tuning: Changes in Execution Plans Execution plans are capable of changing when the query optimizer inputs change. The results display the method using which the database would execute the SQL query. However, the plan you’re viewing can be different from the query’s actual execution plan. This is because there are nearly always some differences in the explain plan and execution environment. www.tosska.com

  9. Other Components that Lead to Changes in Execution Plans Include the Following: • Varying Expenses: The optimizer is likely to make changes in the execution plans it picks because of different costs. This remains true in spite of similar schemas. Given beloware certainfactorsthatbringchangesin the costs: 1. The volume of data 2. The level of parameterinitialization(sessionor global) 3. The types of bindvariablein use • Dissimilarities in Schemas: The EXPLAIN and Execution plans occur on separate databases. In other words, different users are explaining and executing the same query. Both of these could be indicating separate objects in the same MySQL database and SQL, leading to two separate execution plans. www.tosska.com

  10. Limitations of the EXPLAIN PLAN Query in SQL Performance Tuning The EXPLAIN PLAN statement is not supported for every type of query. For instance, the Oracle database doesn’t support it for queries that execute implicit conversion variables. This is because the result of this statement may not show the actual execution plan in the case of bind variables. of date bind www.tosska.com

  11. www.tosska.com

  12. Furthermore, TKPROF is unable to find out what type of bind variable is in use for the statement in question. It ends up assuming the bind variable type to be CHARACTER and shows an error message in case it isn’t. The user can overcome this issue by placing suitable type conversions in the query. www.tosska.com

More Related