只取291条记录,结果执行了五分多钟,求优化

yuantong

一条语句,只是取到291条记录,而且都走了索引,但是还需要322秒,这太慢了,请各位大虾指点一下

其中三个表,每一个表的数据量不超过一千万.

SELECT /*+ ordered index(b,I_hd_yjszsx_NSRNBM_ZSPM)*/ a.nsrnbm,c.nsrbm,c.nsr_mc,sbfs_dm,jkfs_dm,sz_dm zsxm_dm,pm_dm zspm_dm,jsje,sl,sbqx_dm,jkqx_dm,
nsqx_dm,zsfs zsfs_dm,hd_qsrq,hd_zzrq,sj_zzrq,dkdjbz,jyxm_dm,c.gljg_dm,c.dj_zt,c.zclx_dm,c.zgy,c.xzjd_dm,c.lxdh_dh
FROM db_ods.t_hd_yjszsx b,db_ods.t_hd_nshd a,db_ods.T_DJ_NSRXX c
WHERE a.nsrnbm=b.nsrnbm(+) AND a.nsrnbm=c.nsrnbm and c.GLJG_DM = '244060607' AND c.DJ_ZT = '20' AND b.sz_DM = '02' AND  b.pm_DM = '0705'

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch       21     25.47     322.64      68812      98016          0         291
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       23     25.47     322.65      68812      98016          0         291

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS

Rows     Row Source Operation
-------  ---------------------------------------------------
    291  HASH JOIN
  29622   TABLE ACCESS BY INDEX ROWID T_DJ_NSRXX
  29622    INDEX RANGE SCAN I_DJ_NSRXX_GLJG_DJZT (object id 317778)
   9115   NESTED LOOPS
   9115    TABLE ACCESS BY INDEX ROWID T_HD_YJSZSX
   9115     INDEX FULL SCAN I_HD_YJSZSX_NSRNBM_ZSPM (object id 317779)
   9115    TABLE ACCESS BY INDEX ROWID T_HD_NSHD
   9115     INDEX UNIQUE SCAN PK_T_HD_NSHD (object id 315873)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                      21        0.00          0.00
  db file sequential read                     68812        0.38        295.74
  buffer busy waits                               1        0.01          0.01
  SQL*Net message from client                    21       13.37         13.47
********************************************************************************

alter session set events '10046 trace name context off'

yyp2009

如果可以索引rebulid一下会如何？

maclean

a.nsrnbm=b.nsrnbm(+)  b.pm_DM = '0705'

ordered index(b,I_hd_yjszsx_NSRNBM_ZSPM)       ->   INDEX FULL SCAN I_HD_YJSZSX_NSRNBM_ZSPM (object id 317779)

try explain without hint

ZALBB

原帖由 yuantong 于 2011-7-8 16:55 发表
一条语句,只是取到291条记录,而且都走了索引,但是还需要322秒,这太慢了,请各位大虾指点一下

其中三个表,每一个表的数据量不超过一千万.

SELECT /*+ ordered index(b,I_hd_yjszsx_NSRNBM_ZSPM)*/ a.nsrnbm,c.nsrbm,c.nsr_mc,sbfs_dm,jkfs_dm,sz_dm zsxm_dm,pm_dm zspm_dm,jsje,sl,sbqx_dm,jkqx_dm,
nsqx_dm,zsfs zsfs_dm,hd_qsrq,hd_zzrq,sj_zzrq,dkdjbz,jyxm_dm,c.gljg_dm,c.dj_zt,c.zclx_dm,c.zgy,c.xzjd_dm,c.lxdh_dh
FROM db_ods.t_hd_yjszsx b,db_ods.t_hd_nshd a,db_ods.T_DJ_NSRXX c
WHERE a.nsrnbm=b.nsrnbm(+) AND a.nsrnbm=c.nsrnbm and c.GLJG_DM = '244060607' AND c.DJ_ZT = '20' AND b.sz_DM = '02' AND  b.pm_DM = '0705'

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch       21     25.47     322.64      68812      98016          0         291
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       23     25.47     322.65      68812      98016          0         291

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS

Rows     Row Source Operation
-------  ---------------------------------------------------
    291  HASH JOIN
  29622   TABLE ACCESS BY INDEX ROWID T_DJ_NSRXX
  29622    INDEX RANGE SCAN I_DJ_NSRXX_GLJG_DJZT (object id 317778)
   9115   NESTED LOOPS
   9115    TABLE ACCESS BY INDEX ROWID T_HD_YJSZSX
   9115     INDEX FULL SCAN I_HD_YJSZSX_NSRNBM_ZSPM (object id 317779)
   9115    TABLE ACCESS BY INDEX ROWID T_HD_NSHD
   9115     INDEX UNIQUE SCAN PK_T_HD_NSHD (object id 315873)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                      21        0.00          0.00
  db file sequential read                     68812        0.38        295.74
  buffer busy waits                               1        0.01          0.01
  SQL*Net message from client                    21       13.37         13.47
********************************************************************************

alter session set events '10046 trace name context off'

10046文件，怎么没有各步骤的耗时情况？

dingjun123

/*+ ordered index(b,I_hd_yjszsx_NSRNBM_ZSPM)*/

收集下统计信息，让CBO自己决定不好，你这个指定索引还有指定驱动表可能有问题

ps:你的+是多余的

yuantong

首先,表都是分析过的

如果不加hint,更难,超过10分钟

另外,9i的10046 是没有各个步骤耗时的

lwei_998

考虑一下从业务逻辑的角度，看有没有其它隐含的条件可以加

mkstone

第一次执行 disk reads 很大 ， 考虑一下SGA 和 IO 如何?

catchwo

SELECT /*+ ordered USE_NL(a, c) index(b,I_hd_yjszsx_NSRNBM_ZSPM)*/

INDEX FULL SCAN I_HD_YJSZSX_NSRNBM_ZSPM
走index full 这个索引建得有问题 得重建 应为range scan

[ 本帖最后由 catchwo 于 2011-7-9 12:47 编辑 ]

gaopengtttt

物理读好高啊。
能否给除如下信息：
加入/*+  gather_plan_statistics */提示运行   
然后输出这个结果select * from table(dbms_xplan.display_cursor(null,null,'iostats last'));
没有具体的执行计划和消耗的时间，不好确定哪一步出了问题。COST并不准确，我们需要基于时间来看。
ORACLE现在不是都进入了基于等待事件的时代？