Oracle数据库数据恢复、性能优化»论坛 › Oracle › Oracle数据库管理 › AWR报告求分析---金蝶EAS财务产品

0 积分	1 好友	5 主题

发消息

[ORA-600/ORA-7445] AWR报告求分析---金蝶EAS财务产品

1^#

发表于 2013-12-4 14:49:13 | 查看: 5429| 回复: 31

每到月初，都头疼的性能问题。上传个AWR，各位大牛，有空的帮忙分析分析，指点指点。

TOP sql中主要就2类sql：
1、一个这个select tmp开头的，另外的就是insert VT表；
2、单个sql执行时间都是3分钟以上；

awr_report_38976_38977.html (2.56 MB, 下载次数: 1234)

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

Liu Maclean(刘相兵

2^#

发表于 2013-12-4 15:01:33

table fetch continued row 184,420,620 102,174.35 7,209.84

怀疑有大量 chained/migrated rows

table scans (long tables) 93 0.05 0.00  大表扫描不多

Logical reads:    576,478.5    40,678.7    每秒4500M 逻辑读

CPU Time (s) Executions CPU per Exec (s) %Total Elapsed Time (s) %CPU %IO    SQL Id SQL Module SQL Text
490.54 26 18.87 2.89 5,416.82 9.06 91.46 cs2dmhtkcyvkz    JDBC Thin Client SELECT TMP_SUBSELECT_ALIAS."ID...
380.08 1 380.08 2.24 405.40 93.75 6.01 82n2rxzsmxqqc    JDBC Thin Client SELECT SUM(A_Bal.FMonthPnLFor)...
379.68 1 379.68 2.24 403.71 94.05 5.87 6fvrfumup18zb    JDBC Thin Client SELECT SUM(A_Bal.FMonthPnLFor)...
318.40 15 21.23 1.88 3,064.59 10.39 91.11 b4ys64d3rc5x0    JDBC Thin Client SELECT TMP_SUBSELECT_ALIAS."ID...
223.55 0      1.32 1,824.02 12.26 90.02 6z6wy5mt2mxnq    JDBC Thin Client INSERT INTO VTZQGLVAQ1Q953OP88...
215.39 64 3.37 1.27 215.55 99.92 0.01 6z04kzbnxz9n6    JDBC Thin Client select * from (select rownum n...
210.64 82 2.57 1.24 211.34 99.67 0.01 3ydahmx1c4q7w    JDBC Thin Client SELECT "T0"."FID" "ID", "T0"."...
199.79 64 3.12 1.18 199.88 99.96 0.00 4xy92c5huy1k4    JDBC Thin Client select sum(claimAmount) as fid...
199.42 64 3.12 1.18 199.56 99.93 0.00 3j0vmc75bxftx    JDBC Thin Client select count(*) from t_claim_r...
142.93 59 2.42 0.84 143.01 99.94 0.00 5k6djq0mydgdg    JDBC Thin Client select * from (select rownum n...

回复只看该作者道具举报

Liu Maclean(刘相兵

3^#

发表于 2013-12-4 15:02:11

cs2dmhtkcyvkz 消耗大量CPU TIME SELECT TMP_SUBSELECT_ALIAS."ID", TMP_SUBSELECT_ALIAS."REQUISITIONENTRY.ID" FROM (SELECT * FROM (SELECT "REQUISITION".FID "ID", "REQUISITIONENTRY".FID "REQUISITIONENTRY.ID", "REQUISITION".FNumber "NUMBER" FROM T_CSL_Requisition "REQUISITION" INNER JOIN T_CSL_RequisitionEntry "REQUISITIONENTRY" ON "REQUISITION".FID = "REQUISITIONENTRY".FRequisitionID INNER JOIN T_ORG_Company "SRCCOMPANY" ON "REQUISITION".FSrcCompanyID = "SRCCOMPANY".FID INNER JOIN T_ORG_Company "COMPANY" ON "REQUISITION".FTargCompanyID = "COMPANY".FID INNER JOIN T_GL_Voucher "VOUCHER" ON "REQUISITION".FVoucherID = "VOUCHER".FID INNER JOIN T_ORG_CtrlUnit "CU" ON "REQUISITION".FControlUnitID = "CU".FID INNER JOIN T_BD_AccountView "ACCOUNT" ON "REQUISITIONENTRY".FAc countID = "ACCOUNT".FID INNER JOIN T_BD_Currency "CURRENCY" ON "REQUISITIONENTRY".FCurrencyID = "CURRENCY".FID INNER JOIN T_BD_Period "PERIOD" ON "VOUCHER".FPeriodID = "PERIOD".FID INNER JOIN T_PM_User "CREATOR" ON "VOUCHER".FCreatorID = "CREATOR".FID WHERE ((("P

拉一个 ADDMRPT看一下

回复只看该作者道具举报

4^#

发表于 2013-12-4 15:04:29

我格式化下sql：

SELECT TMP_SUBSELECT_ALIAS."ID", TMP_SUBSELECT_ALIAS."REQUISITIONENTRY.ID"
FROM (SELECT *
FROM (SELECT "REQUISITION".FID "ID",
"REQUISITIONENTRY".FID "REQUISITIONENTRY.ID",
"REQUISITION".FNumber "NUMBER"
FROM T_CSL_Requisition "REQUISITION"
INNER JOIN T_CSL_RequisitionEntry "REQUISITIONENTRY"
ON "REQUISITION".FID = "REQUISITIONENTRY".FRequisitionID
INNER JOIN T_ORG_Company "SRCCOMPANY"
ON "REQUISITION".FSrcCompanyID = "SRCCOMPANY".FID
INNER JOIN T_ORG_Company "COMPANY"
ON "REQUISITION".FTargCompanyID = "COMPANY".FID
INNER JOIN T_GL_Voucher "VOUCHER"
ON "REQUISITION".FVoucherID = "VOUCHER".FID
INNER JOIN T_ORG_CtrlUnit "CU"
ON "REQUISITION".FControlUnitID = "CU".FID
INNER JOIN T_BD_AccountView "ACCOUNT"
ON "REQUISITIONENTRY".FAccountID = "ACCOUNT".FID
INNER JOIN T_BD_Currency "CURRENCY"
ON "REQUISITIONENTRY".FCurrencyID = "CURRENCY".FID
INNER JOIN T_BD_Period "PERIOD"
ON "VOUCHER".FPeriodID = "PERIOD".FID
INNER JOIN T_PM_User "CREATOR"
ON "VOUCHER".FCreatorID = "CREATOR".FID
WHERE ((("PERIOD".FPeriodYear = :1 AND
"PERIOD".FPeriodNumber = :2) AND
"REQUISITIONENTRY".FStatus = :3) AND
"COMPANY".FID = :4)
ORDER BY "NUMBER" ASC)
WHERE ROWNUM <= 1000) TMP_SUBSELECT_ALIAS
WHERE ROWNUM <= 1000;

复制代码

回复只看该作者道具举报

5^#

发表于 2013-12-4 15:04:31

1.cs2dmhtkcyvkz 这个sql 开销比较大，看能否调优一下。
2.sga的大小还可以适当调整下，增加到60g左右
这是我理解，不知道是否有用，请大家拍砖！

SELECT TMP_SUBSELECT_ALIAS."ID", TMP_SUBSELECT_ALIAS."REQUISITIONENTRY.ID" FROM (SELECT * FROM (SELECT "REQUISITION".FID "ID", "REQUISITIONENTRY".FID "REQUISITIONENTRY.ID", "REQUISITION".FNumber "NUMBER" FROM T_CSL_Requisition "REQUISITION" INNER JOIN T_CSL_RequisitionEntry "REQUISITIONENTRY" ON "REQUISITION".FID = "REQUISITIONENTRY".FRequisitionID INNER JOIN T_ORG_Company "SRCCOMPANY" ON "REQUISITION".FSrcCompanyID = "SRCCOMPANY".FID INNER JOIN T_ORG_Company "COMPANY" ON "REQUISITION".FTargCompanyID = "COMPANY".FID INNER JOIN T_GL_Voucher "VOUCHER" ON "REQUISITION".FVoucherID = "VOUCHER".FID INNER JOIN T_ORG_CtrlUnit "CU" ON "REQUISITION".FControlUnitID = "CU".FID INNER JOIN T_BD_AccountView "ACCOUNT" ON "REQUISITIONENTRY".FAc countID = "ACCOUNT".FID INNER JOIN T_BD_Currency "CURRENCY" ON "REQUISITIONENTRY".FCurrencyID = "CURRENCY".FID INNER JOIN T_BD_Period "PERIOD" ON "VOUCHER".FPeriodID = "PERIOD".FID INNER JOIN T_PM_User "CREATOR" ON "VOUCHER".FCreatorID = "CREATOR".FID WHERE ((("P

回复只看该作者道具举报

6^#

发表于 2013-12-4 15:08:11

Liu Maclean(刘相兵发表于 2013-12-4 15:02
cs2dmhtkcyvkz 消耗大量CPU TIME SELECT TMP_SUBSELECT_ALIAS."ID", TMP_SUBSELECT_ALIAS."REQUISITIONENTR ...

ADDM rpt：

addm_86620.html (20.38 KB, 下载次数: 668)

回复只看该作者道具举报

Liu Maclean(刘相兵

7^#

发表于 2013-12-4 15:11:26

cs2dmhtkcyvkz 给一下 sqlhc 和 @?/rdbms/admin/sqltrpt

回复只看该作者道具举报

8^#

发表于 2013-12-4 15:18:30

Liu Maclean(刘相兵发表于 2013-12-4 15:11
cs2dmhtkcyvkz 给一下 sqlhc 和 @?/rdbms/admin/sqltrpt

sqlhc：

sqlhc_finance_lmsdb1_11.2.0.3.0_cs2dmhtkcyvkz_20131204145741.html (1.95 MB, 下载次数: 862)

回复只看该作者道具举报

Liu Maclean(刘相兵

9^#

发表于 2013-12-4 15:28:37

该SQL 有较多的执行计划包括
228267830、1367754604等

其中228267830 、 1367754604 等计划的物理读很高，考虑到是11g 且未关闭ACS 特性

给一下

@?/rdbms/admin/sqltrpt

回复只看该作者道具举报

10^#

发表于 2013-12-4 15:36:04

Liu Maclean(刘相兵发表于 2013-12-4 15:28
该SQL 有较多的执行计划包括
228267830、1367754604等

很慢，还没出来。稍等。多谢刘老大的耐心等待

回复只看该作者道具举报

11^#

发表于 2013-12-4 15:53:52

sqltrpt没有输出到文件里面，只搞了半截。看下有没有用。不行我再重搞下

51.91 KB, 下载次数: 1153

回复只看该作者道具举报

Liu Maclean(刘相兵

12^#

发表于 2013-12-4 15:57:35

need spool out & set linesize 200 pagesize 1400

回复只看该作者道具举报

13^#

发表于 2013-12-4 16:14:33

Top Activity:
飞信截图20131204161131.jpg

飞信截图20131204161131.jpg

回复只看该作者道具举报

14^#

发表于 2013-12-4 17:12:44

sqltrpt 报表:

261.59 KB, 下载次数: 1221

回复只看该作者道具举报

Liu Maclean(刘相兵

15^#

发表于 2013-12-4 19:04:38

搞个索引看一下：

create index DEPPON2011.IDX$$_152580001 on
DEPPON2011.T_BD_PERIOD("FPERIODNUMBER","FPERIODYEAR");

回复只看该作者道具举报

16^#

发表于 2013-12-4 21:08:34

Liu Maclean(刘相兵发表于 2013-12-4 19:04
搞个索引看一下：

create index DEPPON2011.IDX$$_152580001 on ...

建啦。不过这个表数据量很小。才134行。貌似没什么效果

回复只看该作者道具举报

17^#

发表于 2013-12-4 21:09:16

huqianhao 发表于 2013-12-4 21:08
建啦。不过这个表数据量很小。才134行。貌似没什么效果

查询2分36秒

回复只看该作者道具举报

Liu Maclean(刘相兵

18^#

发表于 2013-12-4 21:21:49

加了索引后什么执行计划？给出dbms_xplan的输出

回复只看该作者道具举报

19^#

发表于 2013-12-4 22:35:58

md，我们已经被这个东西折腾死了，看到就烦

回复只看该作者道具举报

20^#

发表于 2013-12-5 08:11:19

Liu Maclean(刘相兵发表于 2013-12-4 21:21
加了索引后什么执行计划？给出dbms_xplan的输出

explain plan for
SELECT TMP_SUBSELECT_ALIAS."ID", TMP_SUBSELECT_ALIAS."REQUISITIONENTRY.ID"
FROM (SELECT *
FROM (SELECT "REQUISITION".FID "ID",
"REQUISITIONENTRY".FID "REQUISITIONENTRY.ID",
"REQUISITION".FNumber "NUMBER"
FROM T_CSL_Requisition "REQUISITION"
INNER JOIN T_CSL_RequisitionEntry "REQUISITIONENTRY" ON "REQUISITION".FID = "REQUISITIONENTRY".FRequisitionID
INNER JOIN T_ORG_Company "SRCCOMPANY" ON "REQUISITION".FSrcCompanyID = "SRCCOMPANY".FID
INNER JOIN T_ORG_Company "COMPANY" ON "REQUISITION".FTargCompanyID = "COMPANY".FID
INNER JOIN T_GL_Voucher "VOUCHER" ON "REQUISITION".FVoucherID = "VOUCHER".FID
INNER JOIN T_ORG_CtrlUnit "CU" ON "REQUISITION".FControlUnitID = "CU".FID
INNER JOIN T_BD_AccountView "ACCOUNT" ON "REQUISITIONENTRY".FAccountID = "ACCOUNT".FID
INNER JOIN T_BD_Currency "CURRENCY" ON "REQUISITIONENTRY".FCurrencyID = "CURRENCY".FID
INNER JOIN T_BD_Period "PERIOD" ON "VOUCHER".FPeriodID = "PERIOD".FID
INNER JOIN T_PM_User "CREATOR" ON "VOUCHER".FCreatorID = "CREATOR".FID
WHERE ((("PERIOD".FPeriodYear = '2013' AND
"PERIOD".FPeriodNumber = '11') AND
"REQUISITIONENTRY".FStatus = '1') AND
"COMPANY".FID = '8ldqlc9GQ1qk5RM8BMum9sznrtQ=')
ORDER BY "NUMBER" ASC)
WHERE ROWNUM <= 1000) TMP_SUBSELECT_ALIAS
WHERE ROWNUM <= 1000;
select * from table(dbms_xplan.display());
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)|
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 48000 | | 157K (1)|
| 1 | COUNT STOPKEY | | | | | |
| 2 | VIEW | | 1000 | 48000 | | 157K (1)|
| 3 | COUNT STOPKEY | | | | | |
| 4 | VIEW | | 3397 | 159K| | 157K (1)|
| 5 | SORT ORDER BY STOPKEY | | 3397 | 2093K| 2280K| 157K (1)|
| 6 | NESTED LOOPS | | 3397 | 2093K| | 157K (1)|
| 7 | NESTED LOOPS | | 3397 | 1997K| | 153K (1)|
| 8 | NESTED LOOPS | | 3397 | 1897K| | 153K (1)|
| 9 | NESTED LOOPS | | 3397 | 1798K| | 153K (1)|
| 10 | NESTED LOOPS | | 3397 | 1648K| | 153K (1)|
| 11 | NESTED LOOPS | | 4228 | 1494K| | 141K (1)|
| 12 | HASH JOIN | | 4755 | 1541K| 15M| 141K (1)|
| 13 | HASH JOIN | | 95730 | 14M| | 116K (1)|
| 14 | NESTED LOOPS | | 1 | 66 | | 2 (0)|
| 15 | INDEX UNIQUE SCAN | PK_ORG_COMPANY | 1 | 30 | | 1 (0)|
| 16 | TABLE ACCESS BY INDEX ROWID| T_BD_PERIOD | 1 | 36 | | 1 (0)|
| 17 | INDEX RANGE SCAN | IDX_BD_PERIOD | 1 | | | 0 (0)|
| 18 | TABLE ACCESS FULL | T_GL_VOUCHER | 3319K| 278M| | 116K (1)|
| 19 | TABLE ACCESS BY INDEX ROWID | T_CSL_REQUISITION | 164K| 27M| | 22423 (1)|
| 20 | INDEX RANGE SCAN | IX_REQ_TARGCOMPANY | 164K| | | 1084 (1)|
| 21 | INDEX UNIQUE SCAN | PK_ORG_COMPANY | 1 | 30 | | 0 (0)|
| 22 | TABLE ACCESS BY INDEX ROWID | T_CSL_REQUISITIONENTRY | 1 | 135 | | 3 (0)|
| 23 | INDEX RANGE SCAN | T_CSL_REQUISITIONENTRY_INX | 1 | | | 2 (0)|
| 24 | INDEX UNIQUE SCAN | PK_BD_CURRENCY | 1 | 45 | | 0 (0)|
| 25 | INDEX UNIQUE SCAN | PK_ORG_CTRLUNIT | 1 | 30 | | 0 (0)|
| 26 | INDEX UNIQUE SCAN | PK_PM_USER | 1 | 30 | | 0 (0)|
| 27 | INDEX UNIQUE SCAN | PK_BD_ACCOUNTVIEW | 1 | 29 | | 1 (0)|
---------------------------------------------------------------------------------------------------------------------
Note
-----
- 'PLAN_TABLE' is old version

复制代码

回复只看该作者道具举报

21^#

发表于 2013-12-5 09:50:54

另外一个类似sql的healthcheck内容。也是SELECT TMP_SUBSELECT_ALIAS. 开头的sql。
T_GL_VOUCHER 这个大表走全表扫描，该表300W+。

healthcheck脚本提供的内容有：
1、Observations
2、SQL Text
3、Tables Summary
4、Indexes Summary
5、Current SQL Statistics (GV$SQL)
6、Historical SQL Statistics (DBA_HIST_SQLSTAT)
7、Current Execution Plans (last execution)

sqlhc_finance_lmsdb1_11.2.0.3.0_fzh31q4aav309_20131205091639.html (1.11 MB, 下载次数: 512)

回复只看该作者道具举报

22^#

发表于 2013-12-5 09:57:31

前面<=1000还可以理解为取千行
这儿就真的看不懂了
WHERE rownum <= 2147483647) tmp_subselect_alias
WHERE rownum <= 2147483647

回复只看该作者道具举报

23^#

发表于 2013-12-5 10:52:51

26856649 发表于 2013-12-5 09:57
前面

这就是坑人的sql啊。程序没限制好，把所有数据都取出来啦，没分页。

回复只看该作者道具举报

24^#

发表于 2013-12-5 11:17:36

哪儿的问题哪处理，这种明显应该改程序的问题就不要改语句了

回复只看该作者道具举报

Maclean Liu(刘相兵

25^#

发表于 2013-12-5 11:25:52

execute dbms_sqltune.create_sql_plan_baseline(task_name => 'TASK_86616',owner_name => 'SYS', plan_hash_value => 2038690022);

执行看一下，会启用SPM

回复只看该作者道具举报

26^#

发表于 2013-12-5 11:56:30

26856649 发表于 2013-12-5 11:17
哪儿的问题哪处理，这种明显应该改程序的问题就不要改语句了

主要是很多功能是标准产品，不是二开的。修改代码不容易啊。我现在是想看看能否从数据库的层面去解决些问题。

回复只看该作者道具举报

27^#

发表于 2013-12-5 12:01:17

Maclean Liu(刘相兵发表于 2013-12-5 11:25
execute dbms_sqltune.create_sql_plan_baseline(task_name => 'TASK_86616',owner_name => 'SYS', pla ...

执行完怎么操作？

回复只看该作者道具举报

28^#

发表于 2013-12-5 16:58:57

楼主，我也接触过金蝶的东西，真心有点小坑

回复只看该作者道具举报

Liu Maclean(刘相兵

29^#

发表于 2013-12-5 20:46:22

huqianhao 发表于 2013-12-5 12:01
执行完怎么操作？

后续观察这个SQL在用哪个执行计划

回复只看该作者道具举报

30^#

发表于 2013-12-5 20:47:34

看看这个sql是否使用了baseline

回复只看该作者道具举报

31^#

发表于 2013-12-5 22:43:28

O(∩_∩)O哈哈~，金蝶好强大

回复只看该作者道具举报

32^#

发表于 2013-12-6 12:17:14

oracle ebs 不也是各种毛病各种低效，都是人写的应用，只有50步100步的区别 :)

回复只看该作者道具举报

|手机版|Archiver|Oracle数据库数据恢复、性能优化

GMT+8, 2024-5-19 06:22 , Processed in 0.063746 second(s), 23 queries .

Powered by Discuz! X2.5

© 2001-2012 Comsenz Inc.

回顶部

TEL/電話+86 13764045638

Email service@parnassusdata.com

QQ 47079569