There are at least two database vendors are trying to build self-learning SQL optimizers. The idea is to use actual statistics from executed SQL statements to rectify the future cost estimation of the same or similar SQL statements. It seems like a good idea, but, you will find that their existing self-learning SQL optimizer is either turned off by default or built as an individual tuning advisor. Of course, we cannot say that they will not provide a better and fully automatic solution in future. But the fact is that this technology is not mature enough to be turned on automatically today. Furthermore, database SQL optimizers have a lot of problems pending that still need to be solved. They should not just focus on the error of cost estimation without taking care of the small plan space problem.  

To be honest, a self-learning database SQL optimizer is still only a dream. Using actual statistics to rectify future cost estimation may solve some problems, but it definitely cannot solve every SQL costing problem and limited plan space problem. Furthermore, new features will cause new problems. To my understanding, what they are doing is like providing a patch to the existing cost estimation problem. It is not a fundamental solution to the database SQL optimizer problem.

I have an idea in mind a few years ago that to build a SQL Tuning Agent with a Query Base Statistics database which offloads the original SQL optimizer from real time optimization. The Agent should be running during non-peaks hours to review all executed SQL statements (or resource intensive SQL). For each SQL statement, the Agent should generate more execution plans than the database SQL optimizer generates since the real-time SQL optimizer cannot spend much execution time during real time optimization. For each execution plan it generated, the statistics can be collected by a test run or partial or fully test run of the query plan. Of course, this Agent based SQL optimizer still faces the same problems a real time SQL Optimizer is encountering today, but the beauty of a SQL Tuning Agent is that it has no response time limitation. Any complex cost estimation, plan space extension or test run algorithm can be built piece-by-piece in the agent.  In Oracle 11g, there is a new function called “SQL Plan Management”, I will discuss how it may evolve to this direction later in my blog.