Introduction When it comes to tuning a badly-performing query, there are many things that need to be checked. There may be poor query design causing the query to run slowly. There could be an issue with the underlying hardware such as CPU or IO which is bringing the performance of the query down. There could be stale statistics or missing… Read more
In part 5 of this series I started working through a list of queries designed to demonstrate ways in which the optimizer can produce bad cardinality estimates. In this installment I’m going work through the remainder of the list. The outstanding queries are as follows: select count(*) from t1 where trunc_5 > ( select max(trunc_5) from t1 where mod_200 =… Read more
It’s been three months since I last published anything in this series on the Cost Based Optimizer, so a thumbnail sketch of the previous installments seems to be in order: In part 1 I gave an informal overview of how the optimizer would “think” about a simple query. In part 2 I created a data set, and ran a few… Read more
In the first installment of this series I gave an informal description of how the optimizer would consider the possibilities when choosing an execution path for a simple two-table join between an orders table and an order_lines table that I had described. In that installment I supplied the DDL for the tables and its indexes; in this installment I’m going… Read more
