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

parrotao

还有下列条件的记录数
tprpacc.dt_end is null
tdelpnt.in_ocpl='2'
tbtchdt.batch_nb=269

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。
我会研究一下你推荐的软件，有了结果我会贴上来。

parrotao

TRY this
select /*+use_hash(taddrid, tacct, tbody, tprpacc, tprprty, tdelpnt) full(taddrid) full(tacct) full(tbody) full(tprpacc) full(tprprty) full(tdelpnt)*/
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

还是等了很久temp溢出。

parrotao

你返回的记录集合有多大?
100000条?

olive

上面那个语句里面batch_nb=269就已经限制了记录数不会超过457。

parrotao

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

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

DeepBlue

to Olive:

SQL Expert的优化模块能帮助你找出跟你源SQL语句等效的所有其它写法，并进行测试，找到最快的写法。

我觉得有两个途径解决你现在的问题:
1.快速的办法:
不作Analyze, 输入report的SQL 到SQL Expert,它会帮你找到等效的最快的SQL, 测试时可以指定只考虑运行时间在60分钟以下的来加快测试进度.找到后用最快的替换现在的SQL.

2.全面的办法:
作Analyze, 加大temp tablespace, 把report和Form的SQL都逐条输入SQL Expert, 找到最佳的,替换原来的SQL.

测试的策略：
建议你在晚上进行测试运行。
由于你原SQL的运行时间太长， 预期会有运行时间在1小时左右的SQL语句被找到，故不需要运行原SQL语句。具体办法如下：
a.输入report的SQL语句， 文件-->参数设置-->优化智能-->预定义10
b.开始优化，优化所需时间取决你pc和服务器的快慢
c.在生成等效的SQL语句后（可能有几百条等效的），选批运行
d.在批运行条件窗口，先选“终止条件”tab, 设用户自定义时间，60分钟。运行次序/重复测试tab, 选所有SQL运行一次。回到选择的SQL语句tab,不选“源”SQL, 开始测试。


SQL Expert 3.2.1 中文版下载地址我PM给你，或者你明天去www.leccotech.com下