Every time, I work with databases the same set of questions raises again and again – Why the query is slow? why this query is faster than other? Does indexing solves the issues? For all this questions, I will be thinking how optimizer is behaving and what was the execution plan of the query. Because execution plans tells how a query is executed or how a query will be executed , so the primary goal is to capture the execution plan and troubleshoot the poorly performing query.
When we submit a query to the database server, a number of processes will run on the server for that query. Processes occurs in two engines, relational engine and storage engine.
In relational engine, the query will be parsed and then optimizer parses the query to generate a execution plan. The generated execution plan will be sent to the storage engine. In storage engine process like managing index, transactions, locking,… occurs. The execution plan will help a DBA to debug/troubleshoot poorly performing queries.
As mentioned in the previous section an optimizer is the one which creates a execution plan in relational engine.
There are two types of optimizer:
- Cost-based Optimizer
- Rule-based Optimizer
Rule-based Optimizer(RBO) is preferred long time back. Basically, RBO uses set of rules to create a execution plan. Say, if a table contains index, then every time execution plans utilizes the available indexes. But this didn’t work for a long time because there are some cases where indexes might affect performance. RBOs didn’t always gave good decisions since it decided based on set rules. The major problem with RBO is it won’t take data distribution into account and that is where Cost-Based Optimizer(CBO) came into picture.
CBO uses statistics about the table to make better decisions. It decides if the data can be accessed through indexes, what types of joins to use, data distributions, etc. Optimizer creates execution plan in terms of the required CPU processing and I/O, and it calculates the cost of the execution plan. CBOs generally creates multiple execution plan and chooses the plan which has lower cost.
If you have some more information about this topic, please comment your thoughts