Oracle数据库数据恢复、性能优化»论坛 › Oracle › Oracle数据库管理 › sql优化(3)

351 积分	0 好友	8 主题

发消息

sql优化(3)

1^#

发表于 2012-5-14 10:49:10 | 查看: 5534| 回复: 4

SELECT DISTINCT v.fcompanyid, v.fperiodid
         FROM (SELECT vch.fcompanyid, vch.fperiodid, ve.faccountid,
                        ve.fcurrencyid,
                        SUM (ve.foriginalamount * ve.fentrydc) fdebitfor,
                        SUM (ve.foriginalamount * (1 - ve.fentrydc)
                           ) fcreditfor
                  FROM t_gl_voucherentry ve, t_gl_voucher vch
                  WHERE (vch.fid = ve.fbillid AND vch.fbizstatus = 5)
               GROUP BY vch.fcompanyid,
                        vch.fperiodid,
                        ve.faccountid,
                        ve.fcurrencyid) v,
            (SELECT forgunitid, fperiodid, faccountid, fcurrencyid,
                     fdebitfor, fcreditfor, fendbalancefor
               FROM t_gl_accountbalance
               WHERE fbaltype = :tp) a
      WHERE ( ( ( ( (1 = 1 AND a.forgunitid = v.fcompanyid)
                              AND a.fcurrencyid = v.fcurrencyid
                           )
                        AND a.fperiodid = v.fperiodid
                        )
                  AND a.faccountid = v.faccountid
                  )
               AND ( (a.fdebitfor <> v.fdebitfor)
                  OR (a.fcreditfor <> v.fcreditfor)
                  )
            );

这条语句执行需要30多秒，执行计划和sqlhc已经上传到附件，刘大帮忙看看如何优化？

sqlhc_hseas_gio139_10.2.0.4.0_8mcfgzdas2amj_20120514100213.html

28.84 KB, 下载次数: 666

plan.txt

9.2 KB, 下载次数: 736

分享0

收藏0 回复只看该作者道具举报

Maclean Liu(刘相兵

2^#

发表于 2012-5-14 15:32:23

action plan:

贴出以下SQL的输出

select count(*) from  t_gl_voucherentry ve;

select count(*) FROM t_gl_accountbalance WHERE fbaltype = :tp;             -- 你带入的绑定变量

select count(*) from t_gl_voucher  vch  where  vch.fbizstatus = 5

回复只看该作者道具举报

gdpr-dba

3^#

发表于 2012-5-14 16:18:07

谢谢刘大，以下是sql的结果：

select count(*) from  t_gl_voucherentry ve;  --4871035

select count(*) FROM t_gl_accountbalance WHERE fbaltype = :tp;  --9705345

select count(*) from t_gl_voucher  vch  where  vch.fbizstatus = 5; --1006934

回复只看该作者道具举报

Maclean Liu(刘相兵

4^#

发表于 2012-5-14 17:58:44

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID dspqhgc4aazr1, child number 0
-------------------------------------
SELECT /*+gather_plan_statistics*/
DISTINCT v.fcompanyid, v.fperiodid
FROM (SELECT vch.fcompanyid,
vch.fperiodid,
ve.faccountid,
ve.fcurrencyid,
SUM(ve.foriginalamount * ve.fentrydc) fdebitfor,
SUM(ve.foriginalamount * (1 - ve.fentrydc)) fcreditfor
FROM t_gl_voucherentry ve, t_gl_voucher vch
WHERE (vch.fid = ve.fbillid AND vch.fbizstatus = 5)
GROUP BY vch.fcompanyid,
vch.fperiodid,
ve.faccountid,
ve.fcurrencyid) v,
(SELECT forgunitid,
fperiodid,
faccountid,
fcurrencyid,
fdebitfor,
fcreditfor,
fendbalancefor
FROM t_gl_accountbalance
WHERE fbaltype = :tp) a
WHERE (((((1 = 1 AND a.forgunitid = v.fcompanyid)
Plan hash value: 2599172720
--------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------
| 1 | HASH UNIQUE | | 1 | 13662 | 0 |00:00:36.74 | 842K| 571 | 1236K| 1236K| |
|* 2 | HASH JOIN | | 1 | 2413K| 0 |00:00:36.74 | 842K| 571 | 130M| 7885K| 183M (0)|
| 3 | VIEW | | 1 | 4828K| 770K|00:00:20.16 | 208K| 0 | | | |
| 4 | HASH GROUP BY | | 1 | 4828K| 770K|00:00:16.31 | 208K| 0 | 154M| 7752K| 191M (0)|
|* 5 | HASH JOIN | | 1 | 4828K| 4835K|00:00:21.47 | 208K| 0 | 113M| 8285K| 135M (0)|
|* 6 | TABLE ACCESS FULL| T_GL_VOUCHER | 1 | 1007K| 1006K|00:00:04.03 | 62133 | 0 | | | |
| 7 | TABLE ACCESS FULL| T_GL_VOUCHERENTRY | 1 | 4869K| 4870K|00:00:14.61 | 146K| 0 | | | |
|* 8 | TABLE ACCESS FULL | T_GL_ACCOUNTBALANCE | 1 | 9682K| 9690K|00:00:48.45 | 633K| 571 | | | |
--------------------------------------------------------------------------------------------------------------------------------------------

复制代码

回复只看该作者道具举报

Maclean Liu(刘相兵

5^#

发表于 2012-5-14 18:01:37

三张大表做 join ，仅有的condition

vch.fbizstatus = 5 and t_gl_accountbalance .fbaltype = :tp

条件的选择性较差， oracle 选择使用 T_GL_VOUCHER * T_GL_VOUCHERENTRY * T_GL_ACCOUNTBALANCE 的 join order 是合理的， HASH JOIN的方式也是合理的。

整个执行计划并没有什么问题，如果希望调优该SQL 应当给出更多的 predicate 谓词条件。

回复只看该作者道具举报

返回列表

		自动登录	找回密码
密码			注册