如何解决不同SQL之间的性能冲突？

olive

但是这种招数可能也只有电脑能想出来，人脑是很难想出来的。
虽然电脑的思想也是人脑赋予的，呵呵。

richto

Database optimizer cannot solve every problem for you, so human tuning is necessary, that is why database vendor are more open to user to influence their database optimizer.  If you cannot tuning your SQL statement, you can download a copy of SQL Expert from LECCOTECH.COM to see whether it can help you or not.
Remark: Cost-based approach can solve some problems but create another problems as well, that is why Oracle don't turn it on by default !

biti_rainy

怎么说曹操曹操就到啊
olive你就请教richto吧
他就是sql expert 的开发者
去LECCOTECH.COM ，他可能能帮你解决问题  ：）

parrotao

原来richto是高手高手高高高高高高手

最初由 biti_rainy 发布
[B]怎么说曹操曹操就到啊
olive你就请教richto吧
他就是sql expert 的开发者
去LECCOTECH.COM ，他可能能帮你解决问题  ：） [/B]

richto

Sorry, I just look at the first page and made my comment.  I am the Hong Kong guy, but I am not that old as what your guys described.  I am so busy in these few months, if not our friend told me, I am still don't know your guys are using our product to solve your problem, I am happy my product can help you to solve your problems, no matter your guys are using an illegal copy or not.   I am developing a new product call DB Expert V3, which not only improves optimization power, but recommends indexes as well.  A new Plan Version Tracker will help you to predict what SQL’s performance will be changed before database upgrade, migration, building new indexes, analyze statistics, etc.   I will let your guys knows more after I finish the development.

The A+0 or A||’’ is try to disable the index search for a predicates.  It makes Oracle Optimizer to pick up another execution plan.  Some SQL transformation will be executed in a recursive manner, that is why it eats up a lot of space and CPU, but is worth to do so, instead doing it by human. Your time should be used on more productive task! Right?

olive

I never thought my post could get so many feedbacks...
In fact, for the second run started this afternoon, I set rules search quota to 2000, hints quota ratio to 100, and table join permutation quota to 4000. It's now at 93%, seeming to be stuck because it has been quite a long time, but the memory usage is still growing so I think it's running. CPU occupation on my PC is low, but high on the server (about 50%).
Anyway I will keep it running. The only thing I worry is that the PC memory may overflow. It's now 350M and my limit is 500M...
If it fails eventually, either I will increase the virtual memory, or I will decrease the quota.
So keep your eyes on the post...

olive

I must clarify that I am using a legal copy of SQL EXPERT...
It's the trial version downloaded from the official web site... I hope I can get a good result before it expires...
So if you can tailor a special 'ITPUB Edition' for us... )

DeepBlue

最初由 olive 发布
[B]I never thought my post could get so many feedbacks...
In fact, for the second run started this afternoon, I set rules search quota to 2000, hints quota ratio to 100, and table join permutation quota to 4000. It's now at 93%, seeming to be stuck because it has been quite a long time, but the memory usage is still growing so I think it's running. CPU occupation on my PC is low, but high on the server (about 50%).
Anyway I will keep it running. The only thing I worry is that the PC memory may overflow. It's now 350M and my limit is 500M...
If it fails eventually, either I will increase the virtual memory, or I will decrease the quota.
So keep your eyes on the post... [/B]

这时候在向Server逐条拿plan进行比较,所以Local的CPU使用率不高,此时主要看Server的速度了.500M真不知道是否够, 我想看下你最终能得到多少种等效的写法

olive

After hours of wait it progresses 1%...
And the memory occupation is now 420M...

To be or not to be, this is a question ...

olive

终于在内存溢出之前做完优化了，好紧张...最后内存停留在危险的450M左右。值得一说的是在最后几个百分点的时候，总的内存占用不升反降，但是硬盘很忙。
结果在这里：
22000 Semantically equivalent SQL statement(s) investigated.
2753 Alternative execution plan(s) produced.
[19247 SQL statement(s) eliminated due to identical execution plan]
Note:
To accurately identify the best performing SQL use the Batch Run function.
Warning:
Total Hints Quota 20000 reached.
Table Join Permutation Quota 4000 reached.

所以现在要跑这2700多个语句了... 时间打算设成35秒。预计30个小时...

还有还有，我变成老会员了，果然是在战斗中成长，哈哈...