打开会计期慢的问题

goodhawk · 发表于 2009-10-23 14:04

从你的trace情况来看, 插入28w笔, cost是OK的.
我发现有如下的error:

error during execute of EXPLAIN PLAN statement
ORA-00904: "B"."SET_OF_BOOKS_ID": invalid identifier

sob id = 2002 , 有什么问题嘛?

在post的时候,你们做过什么客制没有?

remen · 发表于 2009-10-23 14:12

应该是有做客户化，oracle不会这么写的！

remen · 发表于 2009-10-23 14:27

刚查了，不是客户化！
ORA-00904: "B"."SET_OF_BOOKS_ID": invalid identifier 好像蛮普遍的！

liangxinf · 发表于 2009-10-23 15:53

ORA-00904: "B"."SET_OF_BOOKS_ID": invalid identifier错误应该是我在本机进行分析时出的错，我这里安装的是R12，所以没有set_of_books_id字段的。

这个应该没有关系。

liangxinf · 发表于 2009-10-23 15:55

原帖由 goodhawk 于 2009-10-23 14:04 发表
从你的trace情况来看, 插入28w笔, cost是OK的.
我发现有如下的error:

error during execute of EXPLAIN PLAN statement
ORA-00904: "B"."SET_OF_BOOKS_ID": invalid identifier

sob id = 2002 , 有什么问题嘛?

在post的时候,你们做过什么客制没有?

你意思插入28条记录到表中使用30-60分钟的时间是正常的？

remen · 发表于 2009-10-23 16:54

insert into gl_open_interim    (code_combination_id, period_name,
  set_of_books_id,    currency_code,    period_year,       period_num,
   period_type,    actual_flag, translated_flag,       period_net_dr,
      period_net_cr, period_net_dr_beq,    period_net_cr_beq,
  quarter_to_date_dr,  quarter_to_date_cr,    project_to_date_dr,
  project_to_date_cr,    begin_balance_dr, begin_balance_cr,
  begin_balance_dr_beq, begin_balance_cr_beq,    template_id) select
  b.code_combination_id,       decode(b.set_of_books_id ,             2002,
'2009-09-23', 'ERROR'),    b.set_of_books_id,    b.currency_code,
   decode(b.set_of_books_id,             2002, 2009 ,-1),
  decode(b.set_of_books_id,             2002, 266 ,-1),
  decode(b.set_of_books_id,             2002, '43' ,'ERROR'),
  b.actual_flag,decode(b.set_of_books_id,2002,decode(b.currency_code,'RMB',
  NULL,'STAT',NULL,'R'),0),    0 , 0,0,0,       decode(b.set_of_books_id
  ,             2002, nvl(b.quarter_to_date_dr,0) + nvl(b.period_net_dr,0) ,
  -1),       decode(b.set_of_books_id,             2002,
  nvl(b.quarter_to_date_cr,0) + nvl(b.period_net_cr,0) ,-1),
  nvl(b.project_to_date_dr,0) + nvl(b.period_net_dr,0),
  nvl(b.project_to_date_cr,0) + nvl(b.period_net_cr,0),
  nvl(b.begin_balance_dr,0) + nvl(b.period_net_dr,0),  nvl(b.begin_balance_cr,
  0) + nvl(b.period_net_cr,0), decode(b.set_of_books_id,2002,
  decode(b.currency_code,'STAT',0,nvl(b.begin_balance_dr_beq,0)
  +nvl(b.period_net_dr_beq,0)),0),decode(b.set_of_books_id,2002,
  decode(b.currency_code,'STAT',0,nvl(b.begin_balance_cr_beq,0)
  +nvl(b.period_net_cr_beq,0)),0),    b.template_id from gl_balances b,
   gl_sets_of_books sob  where  b.actual_flag    = 'A'  and  (
  b.currency_code in (sob.currency_code,'STAT')    or translated_flag='R' )
and b.set_of_books_id = sob.set_of_books_id  and b.period_name    =
  sob.latest_opened_period_name  and (sob.set_of_books_id = 2002 )

call    count    cpu elapsed    disk    query current       rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       1    0.02    0.01       0       0       0          0
Execute    1    15.42    123.94    88065    113785 1030900    284349
Fetch       0    0.00    0.00       0       0       0          0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       2    15.44    123.95    88065    113785 1030900    284349
他指的应该使这个
284349条记录，耗时123秒，不多

remen · 发表于 2009-10-23 16:54

这个问题我觉得你还是好好等sr的回复吧，他是个派生的程序，就算我们发现了问题也无法处理

moonsoft · 发表于 2009-10-24 21:49

goodhawk · 发表于 2009-10-25 21:14

原帖由 remen 于 2009-10-23 16:54 发表
insert into gl_open_interim    (code_combination_id, period_name,
  set_of_books_id,    currency_code,    period_year,       period_num,
   period_type,    actual_flag, translated_flag,       period_net_dr,
      period_net_cr, period_net_dr_beq,    period_net_cr_beq,
  quarter_to_date_dr,  quarter_to_date_cr,    project_to_date_dr,
  project_to_date_cr,    begin_balance_dr, begin_balance_cr,
  begin_balance_dr_beq, begin_balance_cr_beq,    template_id) select
  b.code_combination_id,       decode(b.set_of_books_id ,             2002,
'2009-09-23', 'ERROR'),    b.set_of_books_id,    b.currency_code,
   decode(b.set_of_books_id,             2002, 2009 ,-1),
  decode(b.set_of_books_id,             2002, 266 ,-1),
  decode(b.set_of_books_id,             2002, '43' ,'ERROR'),
  b.actual_flag,decode(b.set_of_books_id,2002,decode(b.currency_code,'RMB',
  NULL,'STAT',NULL,'R'),0),    0 , 0,0,0,       decode(b.set_of_books_id
  ,             2002, nvl(b.quarter_to_date_dr,0) + nvl(b.period_net_dr,0) ,
  -1),       decode(b.set_of_books_id,             2002,
  nvl(b.quarter_to_date_cr,0) + nvl(b.period_net_cr,0) ,-1),
  nvl(b.project_to_date_dr,0) + nvl(b.period_net_dr,0),
  nvl(b.project_to_date_cr,0) + nvl(b.period_net_cr,0),
  nvl(b.begin_balance_dr,0) + nvl(b.period_net_dr,0),  nvl(b.begin_balance_cr,
  0) + nvl(b.period_net_cr,0), decode(b.set_of_books_id,2002,
  decode(b.currency_code,'STAT',0,nvl(b.begin_balance_dr_beq,0)
  +nvl(b.period_net_dr_beq,0)),0),decode(b.set_of_books_id,2002,
  decode(b.currency_code,'STAT',0,nvl(b.begin_balance_cr_beq,0)
  +nvl(b.period_net_cr_beq,0)),0),    b.template_id from gl_balances b,
   gl_sets_of_books sob  where  b.actual_flag    = 'A'  and  (
  b.currency_code in (sob.currency_code,'STAT')    or translated_flag='R' )
and b.set_of_books_id = sob.set_of_books_id  and b.period_name    =
  sob.latest_opened_period_name  and (sob.set_of_books_id = 2002 )

call    count    cpu elapsed    disk    query current       rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       1    0.02    0.01       0       0       0          0
Execute    1    15.42    123.94    88065    113785 1030900    284349
Fetch       0    0.00    0.00       0       0       0          0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       2    15.44    123.95    88065    113785 1030900    284349
他指的应该使这个
284349条记录，耗时123秒，不多

对头,是那个时点oracle post 了 28万笔到 gl_balance , 我认为还行.

[原创] 打开会计期慢的问题