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

parrotao · 发表于 2002-4-26 14:40

try this , Please set autotrace on in sqlplus then put the result here
select
tacct.body_nb bodynb,
tacct.nb acctnb,
tacct.actype_ty,
tacct.blccl_nb blcclnb,
tacct.cstgrp_nb cstgrpnb,
tprprty.propty_cd proptycd,
tprprty.nb prprtynb,
tbtchdt.nb_walk_order nbwalkorder
from    tbtchdt ,  tdelpnt , tprprty, taddrid, tprpacc, tbody, tacct
where tbtchdt.batch_nb=269 and
tacct.nb = tprpacc.acct_nb  and
tacct.body_nb  = tbody.nb  and
tacct.blccl_nb = tbtchdt.blccl_nb and
tacct.cstgrp_nb = tbtchdt.cstgrp_nb  and
tacct.body_nb  = tprpacc.body_nb  and
taddrid.sectn_cd = tbtchdt.sectn_cd and
tbody.addrid_cd = taddrid.cd and
tprpacc.prprty_nb=tprprty.nb and
tprpacc.dt_end is null and
tprprty.nb=tdelpnt.prprty_nb and
tdelpnt.in_ocpl='2'
order by tacct.body_nb

parrotao · 发表于 2002-4-26 15:12

and this

select /*+use_hash(tacct,taddrid, tbody) full(tbody) full(taddrid) full(tacct)*/
tacct.body_nb bodynb,
tacct.nb acctnb,
tacct.actype_ty,
tacct.blccl_nb blcclnb,
tacct.cstgrp_nb cstgrpnb,
tprprty.propty_cd proptycd,
tprprty.nb prprtynb,
tbtchdt.nb_walk_order nbwalkorder
from  tdelpnt , tprprty, tprpacc, tbody, tacct, taddrid, tbtchdt
where tbtchdt.batch_nb=269 and
tbtchdt.blccl_nb = tacct.blccl_nb and
tbtchdt.cstgrp_nb = tacct.cstgrp_nb and
tbtchdt.sectn_cd = taddrid.sectn_cd and
taddrid.cd  = tbody.addrid_cd and
tacct.body_nb  = tbody.nb  and
tprpacc.body_nb = tacct.body_nb and
tacct.nb = tprpacc.acct_nb  and
tprpacc.prprty_nb=tprprty.nb and
tprpacc.dt_end is null and
tprprty.nb=tdelpnt.prprty_nb and
tdelpnt.in_ocpl='2'
order by tacct.body_nb

parrotao · 发表于 2002-4-26 16:21

我用的也是7.3.4
只要建立一个
create table PLAN_TABLE (
      statement_id varchar2(30),
      timestamp    date,
      remarks       varchar2(80),
      operation    varchar2(30),
      options       varchar2(30),
      object_node    varchar2(128),
      object_owner varchar2(30),
      object_name    varchar2(30),
      object_instance numeric,
      object_type    varchar2(30),
      optimizer    varchar2(255),
      search_columns  numeric,
      id             numeric,
      parent_id    numeric,
      position       numeric,
      cost          numeric,
      cardinality    numeric,
      bytes          numeric,
      other_tag    varchar2(255),
      other          long);
就可以用
autotrace了

如果是OLTP的话还是太慢了.

parrotao · 发表于 2002-4-28 08:28

好运

parrotao · 发表于 2002-4-28 09:58

to dblife
我做了你的实验的确发生了这个情况.
找了一下资料发现了下面的一段话
Note: If you want to collect statistics on both the table as a whole and on one or more
columns, be sure to generate the statistics for the table first, and then for the columns.
Otherwise, the table-only ANALYZE will overwrite the histograms generated by the
column ANALYZE. For example, issue the following statements:
ANALYZE TABLE emp ESTIMATE STATISTICS;
ANALYZE TABLE emp ESTIMATE STATISTICS FOR ALL COLUMNS;

我想你应该先做
ANALYZE TABLE emp ESTIMATE STATISTICS;
ANALYZE TABLE emp ESTIMATE STATISTICS FOR ALL indexes;

parrotao · 发表于 2002-4-28 10:29

最初由 parrotao 发布
[B]to dblife

Otherwise, the table-only ANALYZE will overwrite the histograms generated by the
column ANALYZE.

但是我想这不是你出问题的原因
但我先ANALYZE TABLE
然后再ANALYZE TABLE FOR ALL INDEXES
就OK了

好奇怪呀呀呀呀呀呀呀呀呀呀呀呀

parrotao · 发表于 2002-4-28 14:36

没有删过呀!????

parrotao · 发表于 2002-4-28 14:56

给个URL我也去看看

parrotao · 发表于 2002-4-29 15:03

谢谢大家了!又学了一招!
哈哈

parrotao · 发表于 2002-4-29 16:43

看他自动产生的SQL最后一句话AND tdelpnt.prprty_nb = tprpacc.prprty_nb + 0
和实际逻辑的tdelpnt.prprty_nb = tprprty.nb and
是不同的,咋一看好象有问题
其实已经有AND tprprty.nb = tprpacc.prprty_nb
所以prprty_nb 和tprprty.nb可以互相交换的
这样就有更多的sql组合了
优化时不必局限于逻辑