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

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个小时...

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

olive

我的机器内存256，虚拟也是256，我觉得平时足够了。加到2G恐怕windows都不去用物理内存了。
要是sql expert有在server上运行的版本就好了。现在我那台机器就不敢乱动了。
不然我明天用一台PowerEdge6400来试，内存不用虚拟就有2G。

olive

所以能不用我就尽量不用。
但是现在没有办法了。

olive

我现在处于一个比较尴尬的境地，因为sql expert正在运行，占用了大量内存，导致我无法使用别的一些应用程序。最后我如果等不及的话可能就只好把它停掉了。
我想sql expert可不可以这样改进：
1/在做完optimize，找到所有方案后，能不能把内存释放？找到的各种方案可以放在硬盘上。
2/batch run以及optimize都是漫长的过程，能不能做到中途可以暂停，让我有机会干些别的，比如重起机器什么的...
希望richto可以看到我的建议啦。

franking2000

最初由 olive 发布
[B]谢谢各位。
我应该遵循什么样的步骤来解决我的这个具体问题？ [/B]

My suggestion is:
   1、create index and re-create index regularly as possible;
   2、set OPTIMIZER_MODE=RULE(rule-based) and use hint ALL_ROWS in ur SQL statement because the hint can override the value of the initialization parameter.
   3、ANALYZE the tables involved in the statement regularly.

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 !

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?

richto

Hi guys,

1.  We have released most of memory once it is not used, but Window don't release it to public, may be it is Window's problem.  We will do more research on it.
2. It is a good idea, we will consider it in future.

Actually, your SQL statements involves 8 tables join, if SQL Expert consider all possible permutations, it have to try at least 8!=40320 combination(Permutaion quota).  If you statisfy with a good alternative SQL, you can stop at this stage, but you want to find the best, you must have a big and faster machine to try more combinations.  SQL Expert Version 3 still have some limitations to try every possible execution plan, the coming DB Expert V3(arround July 2002) will have a more powerful optimization capability.  I would like to invite some ITOUT members to beta our product arround JUNE.    I will let your guys know more by that time.  

Thanks for your guys' comments !

Richard To
Technical Director
LECCOTECH

forehead

把你的查询做成一个material view试试