ITPUB论坛-中国最专业的IT技术社区

 
 注册
热搜:
楼主: richto

邀请高手测试一款机械式全自动SQL优化工具

[复制链接]
论坛徽章:
20
ITPUB元老
日期:2005-02-28 12:57:002012新春纪念徽章
日期:2012-02-13 15:10:582012新春纪念徽章
日期:2012-02-13 15:10:582012新春纪念徽章
日期:2012-02-13 15:10:582012新春纪念徽章
日期:2012-02-13 15:10:58马上有车
日期:2014-02-19 11:55:14马上有房
日期:2014-02-19 11:55:14马上有钱
日期:2014-02-19 11:55:14马上有对象
日期:2014-02-19 11:55:142012新春纪念徽章
日期:2012-02-13 15:10:58
41#
 楼主| 发表于 2018-1-12 10:42 | 只看该作者
本帖最后由 richto 于 2018-1-12 10:45 编辑
newkid 发表于 2018-1-11 00:07
关于不同SQL变种的相同计划,还有个问题要请教杜兄。
比方说在调优的时候,一个 index hint 起到良好的效 ...

Yes, you are correct. If the data volume or distribution are significant different from development database to production database, the execution plan may not be consistence across two databases. My strategy is using less hints to tune a SQL if it is possible and let Oracle SQL optimizer has freedom to select other execution steps. You should do performance test for that SQL with hints in production database first before deployment.

使用道具 举报

回复
论坛徽章:
486
秀才
日期:2015-09-09 10:33:01秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12状元
日期:2015-11-23 10:04:09举人
日期:2015-11-23 10:04:09秀才
日期:2016-01-06 14:10:21秀才
日期:2016-01-06 14:10:21秀才
日期:2016-01-06 14:10:21秀才
日期:2016-01-06 14:10:21秀才
日期:2016-01-06 14:10:21
42#
发表于 2018-1-12 23:51 | 只看该作者
你这个做法和我的想象完全相反。hints之所以难用,就是因为它的效果难以捉摸,有时候必须动用一大堆才能迫使它就范。因此,hints和计划匹配得越多,就越说明这个计划可以被这组hints稳定,而不是偶然得到的,这样搬到另一个环境的话,计划就更有可能保持不变(当然这个计划在另一环境可能不是最佳,但这又是另外一个话题了)。
以我上述为例,如果你用了use_nl, 计划也确实出现了nl join, 用了leading而且计划的join order也确实达到预期,那么这组就比另外的单个index hint更可靠。那单个index hint移到其他环境,可能就变成了hash join 和index fast full scan了。

使用道具 举报

回复
论坛徽章:
20
ITPUB元老
日期:2005-02-28 12:57:002012新春纪念徽章
日期:2012-02-13 15:10:582012新春纪念徽章
日期:2012-02-13 15:10:582012新春纪念徽章
日期:2012-02-13 15:10:582012新春纪念徽章
日期:2012-02-13 15:10:58马上有车
日期:2014-02-19 11:55:14马上有房
日期:2014-02-19 11:55:14马上有钱
日期:2014-02-19 11:55:14马上有对象
日期:2014-02-19 11:55:142012新春纪念徽章
日期:2012-02-13 15:10:58
43#
 楼主| 发表于 2018-1-15 11:10 | 只看该作者
有时候必须动用一大堆才能迫使它就范
Yes, it is the point. So, use hints is not an easy job, use computer algorithm to discover the potential plans is a breakthrough. My tool to tune SQL, not to fix the plan of the SQL. Most of SQL performance problem is can be fixed by two approaches.  The following is extracted from the article I am writing, not published yet:

Hints is not a programing language
Oracle Optimizer Hints is not a programing language that come with proper syntax check or error return. It means an invalid Hints instruction to a SQL statement that Oracle SQL optimizer will not return with error message. Furthermore, even if it is a valid Hints instruction that Oracle SQL optimizer cannot comply with, there is no error message will be returned too. So, users have to do a lot of trial and error to influence SQL optimizer to generate a better query plan.

Knowing the solution
It is a very common tuning practice that people are trying to find the best query plan for a bad performance SQL statement, it works like a human mimic Oracle SQL optimizer’s job to optimize a SQL statement with human’s knowledge. If the SQL statement is simple and Oracle SQL optimizer had made an obvious mistake, probably human intervention may works fine for this kind of simple SQL.
Here is an example:
Select * from A where A.KEY<’ABC’;
If Oracle SQL optimizer is failed to use index to retrieve records from table A and using index of KEY1 is actually a better solution. You can use Optimizer Hints to instruct Oracle to use index instead of full table scan for this SQL statement.
Select /*+ INDEX(KEY1 KEY1_INX) */ from A where A.KEY1<’ABC’ and A.KEY2<123 and A.KEY3<’C’;
Knowing the best solution is easy for simple SQL statements, but it is difficult for complex SQL statements. Since there is a lot of execution steps in the query plan for complex SQL, human is not able to estimate each step and workout a series of query steps to compose the best performance query plan. So, use Oracle Optimizer Hints to instruct SQL optimizer to generate specific query plan for complex SQL may not be easy even for an experienced SQL tuning expert.

Knowing the problem
Instead of knowing the solution of a SQL statement, it is relative easier for a human expert to find where the problem is in a complex query plan. The way to tell Oracle to bypass the problem is applying hints with prefix “NO_” such as NO_INDEX or NO_USE_HASH. It tell Oracle not to use the specified operation in the query plan and select another operation instead with lowest cost. This approach is not commonly adopted in the market due to people are normally bounded by solution oriented thinking.
For example:
Select /*+ NO_INDEX(KEY2 KEY2_INX) */ * from A where A.KEY1<’ABC’ and A.KEY2<123 and A.KEY3<’C’;
If you think the KEY2_INX is not a good index to retrieve records from table A, you can disable the KEY2 index by applying /*+ NO_INDEX(KEY2 KEY2_INX) */ and let Oracle to select other index to retrieve the records.

使用道具 举报

回复
论坛徽章:
486
秀才
日期:2015-09-09 10:33:01秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12状元
日期:2015-11-23 10:04:09举人
日期:2015-11-23 10:04:09秀才
日期:2016-01-06 14:10:21秀才
日期:2016-01-06 14:10:21秀才
日期:2016-01-06 14:10:21秀才
日期:2016-01-06 14:10:21秀才
日期:2016-01-06 14:10:21
44#
发表于 2018-1-16 00:42 | 只看该作者
你这个工具现在似乎缺少这个"Knowing the problem"的接口。我们有时候已经知道问题在哪里了,从计划的E-ROWS和A-ROWS的差距可以看出来,心理也知道该用什么路径来执行更好,只是不懂得用哪些HINTS,但是怎么把这些想法告诉这个工具呢?它还是会尝试很多无效的或者更差的写法。

使用道具 举报

回复
论坛徽章:
20
ITPUB元老
日期:2005-02-28 12:57:002012新春纪念徽章
日期:2012-02-13 15:10:582012新春纪念徽章
日期:2012-02-13 15:10:582012新春纪念徽章
日期:2012-02-13 15:10:582012新春纪念徽章
日期:2012-02-13 15:10:58马上有车
日期:2014-02-19 11:55:14马上有房
日期:2014-02-19 11:55:14马上有钱
日期:2014-02-19 11:55:14马上有对象
日期:2014-02-19 11:55:142012新春纪念徽章
日期:2012-02-13 15:10:58
45#
 楼主| 发表于 2018-1-17 16:28 | 只看该作者
newkid 发表于 2018-1-16 00:42
你这个工具现在似乎缺少这个"Knowing the problem"的接口。我们有时候已经知道问题在哪里了,从计划的E-ROW ...

Our tool is using computer time to solve the problem, it is not necessary to involve human effort. There is no universal method to tell what combination of hints will fix a specific SQL with specific query plan. Our algorithm eliminates a lot of useless plan already, what you have to do is wait and let the test run tells you which plan is better.

使用道具 举报

回复
论坛徽章:
486
秀才
日期:2015-09-09 10:33:01秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12状元
日期:2015-11-23 10:04:09举人
日期:2015-11-23 10:04:09秀才
日期:2016-01-06 14:10:21秀才
日期:2016-01-06 14:10:21秀才
日期:2016-01-06 14:10:21秀才
日期:2016-01-06 14:10:21秀才
日期:2016-01-06 14:10:21
46#
发表于 2018-1-18 00:48 | 只看该作者
richto 发表于 2018-1-17 16:28
Our tool is using computer time to solve the problem, it is not necessary to involve human effort. ...

如果能够完全不需干预那当然好,但问题是耗不起时间啊!需要调优的有很多都是大型报表,假设理想目标是30秒,这工具有可能尝试5000个变种!
对于视图有什么处理思路?能够把hints推进去吗?12c的expand_sql_text用上了吗?

使用道具 举报

回复
论坛徽章:
20
ITPUB元老
日期:2005-02-28 12:57:002012新春纪念徽章
日期:2012-02-13 15:10:582012新春纪念徽章
日期:2012-02-13 15:10:582012新春纪念徽章
日期:2012-02-13 15:10:582012新春纪念徽章
日期:2012-02-13 15:10:58马上有车
日期:2014-02-19 11:55:14马上有房
日期:2014-02-19 11:55:14马上有钱
日期:2014-02-19 11:55:14马上有对象
日期:2014-02-19 11:55:142012新春纪念徽章
日期:2012-02-13 15:10:58
47#
 楼主| 发表于 2018-1-19 11:35 | 只看该作者
Yes, it takes time to tune long run time SQL, I have a customer before, they took one week to tune a SQL from hours down to minutes. I think it is still worth it do so. The point is that, a human don't have the time and knowledge to tune that specific SQL.
Yes, the hints will address down to view level without problem. Here the following is an example.

Original SQL:

select *
  from employee,
       v_dpt_min_salary
where emp_dept = dpt_id
   and emp_id < 600000


使用道具 举报

回复
论坛徽章:
486
秀才
日期:2015-09-09 10:33:01秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12状元
日期:2015-11-23 10:04:09举人
日期:2015-11-23 10:04:09秀才
日期:2016-01-06 14:10:21秀才
日期:2016-01-06 14:10:21秀才
日期:2016-01-06 14:10:21秀才
日期:2016-01-06 14:10:21秀才
日期:2016-01-06 14:10:21
48#
发表于 2018-1-20 01:53 | 只看该作者
richto 发表于 2018-1-19 11:35
Yes, it takes time to tune long run time SQL, I have a customer before, they took one week to tune a ...

这可真是奇妙。如果在视图里面还有再分QUERY BLOCK,或者视图里面又嵌套视图怎么办?

使用道具 举报

回复
论坛徽章:
20
ITPUB元老
日期:2005-02-28 12:57:002012新春纪念徽章
日期:2012-02-13 15:10:582012新春纪念徽章
日期:2012-02-13 15:10:582012新春纪念徽章
日期:2012-02-13 15:10:582012新春纪念徽章
日期:2012-02-13 15:10:58马上有车
日期:2014-02-19 11:55:14马上有房
日期:2014-02-19 11:55:14马上有钱
日期:2014-02-19 11:55:14马上有对象
日期:2014-02-19 11:55:142012新春纪念徽章
日期:2012-02-13 15:10:58
49#
 楼主| 发表于 2018-1-24 09:59 | 只看该作者
It works as what you said :

Here is an example:

CREATE OR REPLACE VIEW v1 AS
  SELECT *
    FROM employee
    WHERE emp_salary < 500000;

CREATE OR REPLACE VIEW v2 AS
  SELECT v1.emp_id emp_id, department.dpt_id dpt_id
    FROM v1, department
    WHERE v1.emp_dept = department.dpt_id;


使用道具 举报

回复
论坛徽章:
486
秀才
日期:2015-09-09 10:33:01秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12状元
日期:2015-11-23 10:04:09举人
日期:2015-11-23 10:04:09秀才
日期:2016-01-06 14:10:21秀才
日期:2016-01-06 14:10:21秀才
日期:2016-01-06 14:10:21秀才
日期:2016-01-06 14:10:21秀才
日期:2016-01-06 14:10:21
50#
发表于 2018-1-25 04:30 | 只看该作者
再给你出个难题,像这个帖子里面讲的 LEADING HINTS你能够处理吗?


使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则

TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档 |
  | | |
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 网站律师 隐私政策 知识产权声明
 北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表