AWR报告求分析---金蝶EAS财务产品

huqianhao 发表于 2013-12-4 14:49:13

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

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

Liu Maclean(刘相兵 发表于 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(刘相兵 发表于 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看一下

huqianhao 发表于 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;

北柏发表于 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

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

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

ADDM rpt：

Liu Maclean(刘相兵 发表于 2013-12-4 15:11:26

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

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

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

sqlhc：

Liu Maclean(刘相兵 发表于 2013-12-4 15:28:37

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

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

给一下

@?/rdbms/admin/sqltrpt

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

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

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

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

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

Liu Maclean(刘相兵 发表于 2013-12-4 15:57:35

need spool out & set linesize 200 pagesize 1400

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

Top Activity:

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

sqltrpt 报表:

Liu Maclean(刘相兵 发表于 2013-12-4 19:04:38

搞个索引看一下：

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

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

Liu Maclean(刘相兵发表于 2013-12-4 19:04 static/image/common/back.gif
搞个索引看一下：

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

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

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

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

查询2分36秒

Liu Maclean(刘相兵 发表于 2013-12-4 21:21:49

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

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

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

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

Liu Maclean(刘相兵发表于 2013-12-4 21:21 static/image/common/back.gif
加了索引后什么执行计划？给出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

huqianhao 发表于 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)

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

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

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

26856649 发表于 2013-12-5 09:57 static/image/common/back.gif
前面

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

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

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

Maclean Liu(刘相兵 发表于 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

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

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

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

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

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

执行完怎么操作？

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

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

Liu Maclean(刘相兵 发表于 2013-12-5 20:46:22

huqianhao 发表于 2013-12-5 12:01 static/image/common/back.gif
执行完怎么操作？

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

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

看看这个sql是否使用了baseline

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

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

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

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

页: [1]

Oracle数据库数据恢复、性能优化's Archiver

AWR报告求分析---金蝶EAS财务产品