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

olive

各位，我有一个实际中遇到的问题，比较头痛，请帮忙分析一下。
我们的数据库是AIX4.2上的oracle 734. 本来数据库正常运行，用户程序（主要是forms）的性能都正常。最近开发了一个report程序，也是在同一个数据库里面的某些表里取数据出一些report。这个report运行很慢，要几十个小时，经过试验发现问题出在其中的一个多表连接的SELECT，但是一时之间也找不到好的优化方法。有一次偶然发现，对那个SELECT涉及到的表作analyze estimate statistics，之后SELECT的速度就大大加快，整个report不到一个小时就运行完了。还没来得及高兴，最终用户反映他们对数据库的日常操作（就是前面提到通过forms进行的操作）变得很慢，做一个查询动作往往要等上几分钟。把analyze删掉，就恢复正常。
我听说这是因为对表作了analyze之后，oracle就会用不同的优化方法来执行SQL。我想这就是问题的根源。但是像我遇到的这样的问题，应该如何解决？能不能指定oracle在执行某些SQL的时候用某种优化方案？

olive

这是我从report程序里面拿出来的select，其中的变量我换成了常量（269），有analyze，几秒钟就出来，没有analyze，经过漫长的等待，最终结果是temp tablespace溢出（4G空间啊），所以我想这个语句可能有问题，大家帮我看看？
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, taddrid, tacct, tbody, tprpacc, tprprty, tdelpnt
where tbtchdt.batch_nb=269 and
       tbtchdt.sectn_cd=taddrid.sectn_cd and
       tbtchdt.blccl_nb = tacct.blccl_nb and
       tbtchdt.cstgrp_nb = tacct.cstgrp_nb and
       taddrid.cd=tbody.addrid_cd and
       tbody.nb =tacct.body_nb and
       tprpacc.body_nb =tacct.body_nb and
       tprpacc.acct_nb =tacct.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

olive

多发表一些意见，希望能从中得到一点启发。我现在临时的解决办法是在程序的开头加上analyze，结束处删掉analyze。我刚刚试过，这样整个程序只要28分钟就执行完。
现在问题的焦点在于，不加analyze的话，temp溢出，提示上面这个语句可能有问题，比如多余的连接等，正常情况下4G的临时空间不应该溢出。

olive

那么默认值是什么？
另外，经我试验，上述涉及到的表中，一定要某几个都analyze后，才有性能提高，比如我现在达到28分钟的执行时间，是analyze了其中四个表，少一个都不行。那oracle到底是怎么样选择优化方案的？

olive

那为什么我analyze少于四个表的时候，速度特别慢？和没有analyze的时候没有什么分别。
按照你的说法，只要有一个表有analyze，就是基于开销的啊。
按我的实验，必须要analyze四个表，速度才有突变。
谁能说说上面这个SELECT，应当怎样建立index。

olive

支持material view吗？怎么做？

olive

谢谢各位。
我应该遵循什么样的步骤来解决我的这个具体问题？

olive

以下是有关资料：
各表的记录数：
tbtchdt: 185756，其中batch_nb=269:457
taddrid: 255071
tacct: 245109
tbody: 245108
tprpacc: 270597，其中dt_end is null: 245104
tprprty: 245107
tdelpnt: 245106, 其中in_ocpl='2': 178478

各表现在所建的索引：
TABLE_NAME INDEX_NAME       COLUMN_NAME      COLUMN_POSITION
---------- ---------------- ---------------- ---------------
TACCT         ACCT_FRGN               ADDRID_CD                      1
TACCT         ACCT_FRGN1             ACTYPE_TY                      1
TACCT         ACCT_FRGN2             ACQUAL_CD                      1
TACCT         ACCT_FRGN3             CSTGRP_NB                      1
TACCT         ACCT_FRGN3             BLCCL_NB                       2
TACCT         ACCT_FRGN4             BODY_NB                        1
TACCT         ACCT_FRGN4             ACCT_NB_LINKED                 2
TACCT         ACCT_OTH1                DT_NEXT_REC_STEP               1
TACCT         ACCT_OTH2                IN_PPLAN                       1
TACCT         ACCT_OTH2                PMTTYP_TY                      2
TACCT         ACCT_OTH2                AM_STEP_UNPAID                 3
TACCT         ACCT_OTH2                BODY_NB                        4
TACCT         ACCT_OTH2                NB                             5
TACCT         ACCT_OTH2                PPLAN_NB_CURRENT               6
TACCT         ACCT_OTH2                PPSTEP_NB                      7
TACCT         ACCT_OTH3                CD_REF_EXTERNAL                1
TACCT         ACCT_PK_PRIM         BODY_NB                        1
TACCT         ACCT_PK_PRIM         NB                             2

TADDRID    ADDRID_FRGN          SECTN_CD                       1
TADDRID    ADDRID_FRGN          LB_PLACE                       2
TADDRID    ADDRID_FRGN          LB_STREET_NO                   3
TADDRID    ADDRID_FRGN2        FFADDR_CD                      1
TADDRID    ADDRID_PK_PRIM    CD                             1

TBODY         BODY_FRGN               ADDRID_CD                      1
TBODY         BODY_FRGN1             BODSEN_CD                      1
TBODY         BODY_FRGN2             BDYGRP_CD                      1
TBODY         BODY_FRGN1             BODSEN_CD                      1
TBODY         BODY_FRGN2             BDYGRP_CD                      1
TBODY         BODY_OTH                  LB_FIRST                       2
TBODY         BODY_PK_PRIM         NB                             1

TBTCHDT   BTCHDT_FRGN2       SECTN_CD                       1
TBTCHDT   BTCHDT_PK_PRIM   CSTGRP_NB                      1
TBTCHDT   BTCHDT_PK_PRIM   BLCCL_NB                       2
TBTCHDT   BTCHDT_PK_PRIM   BATCH_NB                       3
TBTCHDT   BTCHDT_PK_PRIM   SECTN_CD                       4

TDELPNT    DELPNT_FRGN1        NB_PRPRTY_MAST                 1
TDELPNT    DELPNT_FRGN1        NB_DELPNT_MAST                 2
TDELPNT    DELPNT_IDX1              LB_ADD_VALUE1                  1
TDELPNT    DELPNT_OTH              NB_PRPRTY_PREV                 1
TDELPNT    DELPNT_OTH              NB_DELPNT_PREV                 2
TDELPNT    DELPNT_PK_PRIM     PRPRTY_NB                      1
TDELPNT    DELPNT_PK_PRIM      NB                             2

TPRPACC    PRPACC_OTH               PRPRTY_NB                      1
TPRPACC    PRPACC_OTH               DT_START                       2
TPRPACC    PRPACC_PK_PRIM      BODY_NB                        1
TPRPACC    PRPACC_PK_PRIM      ACCT_NB                        2
TPRPACC    PRPACC_PK_PRIM      PRPRTY_NB                      3
TPRPACC    PRPACC_PK_PRIM      DT_START                       4

TPRPRTY    PRPRTY_FRGN1          PROPTY_CD                      1
TPRPRTY    PRPRTY_FRGN2          PRPRTY_NB_MAIN                 1
TPRPRTY    PRPRTY_OTH               ADDRID_CD                      1
TPRPRTY    PRPRTY_OTH2            TY_OCCUPANCY                   1
TPRPRTY    PRPRTY_PK_PRIM      NB                             1

olive

我现在是analyze之后速度加快，删掉analyze后则很慢且最终temp溢出。
最好是不要用analyze而是调整上面这个SELECT，以及建议适当的index并用适当的hint。
我会研究一下你推荐的软件，有了结果我会贴上来。

olive

还是等了很久temp溢出。