perf_events : Off/On/Mixed CPU flamegraph extended with oracle wait events

Using  FlameGaphs  for investigating performance problem can be a valuable asset for quick resolution and identification of the root cause. This type of analysis may be needed when the traditional oracle instrumentation are not enough.

This post is based and inspired by the awesome work of Brendan Gregg ,Luca Canali and Frits Hoogland in this area. Please check the references at the end of the post for more info (Worth reading !)

What i will cover here is a tiny script i written for generating 3 types of extended flamegaph using the build in perf tool. I said extend because they actually include the oracle wait events.

  • Off cpu
  • On cpu
  • HOT/COLD flame graph

Continue reading

Run-time side effect of dropping a composite unused index

Unused Index although they seem not needed , dropping them must be carefully tested  as it can have some side effect. As explained by Jonathan Lewis, due to index sanity check cardinality miss estimate can happen after dropping the unused index.

“The reason for this is the “index sanity check”. When the optimizer is doing its cardinality estimates, if it see equality conditions on the set of columns that make up an index it can use the distinct_keys statistic from the index in the calculation rather than using the standard calculation of multiplying together the num_distinct of the separate columns. ”

There is also a good example of the impact this can have on the query execution plan(switching join method from hash join to nested loop due to the drop of the estimated number of rows returned).Link

In this post i will show another interesting case when this can have an impact on query performance.As Franck Pachot Said :

Continue reading