Oracle SQL Query: the Role of MK-query-digest in Optimization
Every time a database administrator takes a look at an MK-query-digest report, they generally observe the queries causing the greatest effect. In other words, they look at the total execution time taken by the queries along with statements that contain the longest conditions.
Why do they look at these stats in particular? It is because finding this type of Oracle SQL query and working on optimizing them specifically goes a long way in boosting system capacity. This, in turn, positively affects the time to respond taken by the system for a vast range of statements.
Additionally, statements that have the greatest effect typically relate to either the most commonly used or the most sluggish transactions taking place in the system. This is why such types of queries are the most essential to consider.
The queries that take the longest time also end up having the worst response times. For instance, in the case of a statement that takes half a minute just to execute, somebody had to spend thirty seconds waiting for it to execute.
This isn’t acceptable from the perspective of an interactive query, and it is recommended to glance at 95 percentile “time” instead of looking for the longest time taken for the queries. Often, database administrators can spot exceedingly long times during SQL performance monitoring that are not always connected to a statement. Sometimes, the system could be overloaded or there may be table-level or row-level locks leading to unreasonable query response times.
However, if an interactive query isn’t the one that’s the slowest, then that isn’t something to worry about. This is true, assuming that the slowest query is not the reason behind system overload or interactive statements that have actual users waiting for their completion. Interactive statements are also the ones that need to be optimized quickly; they are on higher priority.
Since batch jobs facilitate the removal of such statements from analysis, it is rather helpful if they apply different users in comparison with the website. It is important to note here that neither of the two statement types mentioned just above ascertain application performance, especially when it comes to any transactions that we’re attempting to optimize.
The reason behind this is generally, such transactions end up appearing in one of these forms of query sets. In case you’re involved in the search to boost the response time of certain transactions, you need to understand a few things.
First, you will have to look at the Oracle SQL query, or queries, rather than aggregating data for the entire server. Second, you must comprehend which queries are being run with other interactions like remote operation and Memcache. These will further help you understand the particular reasons behind the performance problems of your database.
Also, if you have lagging statements from an interactive application, one or another user is having to wait for them to complete. It is still, quite probable to suffer from poor performance with MySQL being in charge of response time to a huge extent — without a single slow query.
Experts have observed pages that take thousands of queries to generate. In such cases, even statements that take a millisecond on an average end up consuming over twenty seconds to offer a response, on an average.
