有个纠结的问题

richto

newkid 发表于 2017-12-13 23:25
是的，但是有的SQL跑一下都要几十分钟，所以硬解析期间是不可能一一尝试的。ORACLE自己也做出一些努力比 ...

Oracle using cardinality feedback, adaptive plan and SQL plan directives to fix the execution plan selection. But it is still rely on cost estimation.  The cost estimation is theoretically impossible to be 100% correct. It is why Oracle is always have room to improve. A lot of cost estimation factors are based on assumption only and cannot fit different environments.
My tool is simple, test run all effective Hints combination for your SQL and find the best one in your environment.  You can download one from https://tosska.com/tosska-sql-tuning-expert-tse-oracle/
and give it a test and see whether it is worth to have one.  The first 30 alternatives SQL is for free.

newkid

richto 发表于 2017-12-14 10:28
Oracle using cardinality feedback, adaptive plan and SQL plan directives to fix the execution plan ...

这个不是很准确，cardinality feedback和 adaptive plan 都是会收集真正执行的结果的，并不是基于估计，而正是为了纠正估计值。
Oracle自己的evolve sql plan baseline, sql tuning advisor 等都是会真正去执行不同计划并且进行比较、选择的，也都是为了纠正它自己的估计误差。当然这些都是要耗费资源的。你的工具有什么优势？假设一个SQL执行要60分钟，你的优化过程大约要几分钟？

richto

newkid 发表于 2017-12-14 23:04
这个不是很准确，cardinality feedback和 adaptive plan 都是会收集真正执行的结果的，并不是基于估计， ...

Cardinality feedback is good for similar plans with same execution steps, if the plan is different, the new plan steps cardinalities are still estimated figures only. For example; if Plan1 has actual cardinality collected, it does not mean you know Plan2 actual cost. plan2 has some execution steps that not exists in Plan1.

If you want to tune a SQL with original execution time 60 minutes, and you know it is not reasonable and you want to save your time of using Tosska SQLL Tuning Expert, you can set a user define time to terminate all SQL alternatives that run longer than this user defined time.  For example, you expect your SQL can run and finish within 20 minutes, you can set 20 minutes as the termination time. So, the whole test run will speed up.

newkid

这个feedback功能有点类似于bind peeking, 它当然不可能每次都去窥视的，所以有时候plan2还更差，我们在实际中也多次碰到过。
不知道你的工具总共尝试几种hints? 即使仅仅是连接顺序、连接方式、数据访问路径(全表或索引)，这些组合起来也十分可观，这也是为什么CBO不可能穷尽其组合，那么你又是如何取舍的呢？很多时候计划劣化是因为CBO不掌握数据的特征，比如相关列之类的。你这个工具有没有试图建立自己的知识库，又是怎么收集的？

richto

newkid 发表于 2017-12-16 00:03
这个feedback功能有点类似于bind peeking, 它当然不可能每次都去窥视的，所以有时候plan2还更差，我们在实 ...

You are very good. There are more than 100 hints in Oracle. If we pick 3 out of 100, the permutation will be up to a million.  So, we will use most possible hints to your SQL with a patent pending algorithm.
Actually, SQL cost estimation problems are more than what you guys can imagine. We can discuss more later in this forum.